Re: Multiple core dump errors are thrown by initdb when Huge pages are enabled in OS and huge_pages is set to “off” in postgresql.conf.sample in Kubernetes.

[Julien Rouhaud]

Hi,

On Tue, Mar 07, 2023 at 05:44:53AM +, M Tarkeshwar Rao wrote:
>
> We are facing following issue with postgres db with Kubernetes. Is huge pages
> not supported in Kubernetes environment?
> Multiple core dump errors are thrown by initdb when Huge pages are enabled in
> OS and huge_pages is set to “off” in postgresql.conf.sample in Kubernetes.
> In this case, the initdb utility exists with code, success message is also
> printed in its logs and DB server is started successfully.
>
> Queries:
> How can this issue be fixed?
> If no, can these errors be ignored or is there any repercussion of these
> errors, since DB is initialized & started successfully.
>
> NOTE: If we do not set huge_pages to “off”, and use its default value, then
> db is also not starting.

FTR this has already been reported a few weeks ago, see
https://www.postgresql.org/message-id/flat/17757-dbdfc1f1c954a6db%40postgresql.org.

You can also look at https://commitfest.postgresql.org/42/4143/ (and maybe
test) as a possible fix that could be backpatched to released versions.

Multiple core dump errors are thrown by initdb when Huge pages are enabled in OS and huge_pages is set to “off” in postgresql.conf.sample in Kubernetes.

[M Tarkeshwar Rao]

Hi,

We are facing following issue with postgres db with Kubernetes. Is huge pages 
not supported in Kubernetes environment?
Multiple core dump errors are thrown by initdb when Huge pages are enabled in 
OS and huge_pages is set to “off” in postgresql.conf.sample in Kubernetes.
In this case, the initdb utility exists with code, success message is also 
printed in its logs and DB server is started successfully.

Queries:
How can this issue be fixed?
If no, can these errors be ignored or is there any repercussion of these 
errors, since DB is initialized & started successfully.

NOTE: If we do not set huge_pages to “off”, and use its default value, then db 
is also not starting.

Regards
Tarkeshwar

Re: Idea: PostgreSQL equivalent to Oracle's KEEP clause

[David Rowley]

On Tue, 7 Mar 2023 at 16:11, David G. Johnston
 wrote:
>
> On Mon, Mar 6, 2023 at 7:51 PM David Rowley  wrote:
>> the transfn for bottom() would need to remember the city and the
>> population for the highest yet seen value of the 2nd arg.
>
>
> BOTTOM() remembers the highest value?

I was thinking in terms of a window with all values sorted in
ascending order. Maybe your mental modal differs from mine.  If Ben
wants to implement some new aggregate functions in an extension, then
he might think of better names.

> SELECT country, city,
>   rank() over (partition by country order by population desc),
>   count() OVER (partition by country)
> FROM cities
> WINDOW_HAVING count > 0 AND rank = 1;
>
> That would be, IMO, the idiomatic query form to perform ranking - not abusing 
> GROUP BY.  To add this encourages abusing GROUP BY.
>
> Though I suppose if there is a sufficient performance gain to be had under 
> GROUP BY the effort might make sense if further improvements to window 
> function processing cannot be found.

Ideally, we'd be able to just sort the top-1 value and not the entire
window by population desc.  Maybe SupportRequestWFuncMonotonic could
be extended to instruct WindowAgg to do that for certain functions.
Greg was talking about something like this in [1]. Likely that would
be easier for row_number() since any number of rows could have
rank==1.

David

[1] 
https://postgr.es/m/CAM-w4HN7D1wgTnKqUEnjie=e_6kjrc08cugtlqgsirfpo3k...@mail.gmail.com

Re: Idea: PostgreSQL equivalent to Oracle's KEEP clause

[David G. Johnston]

On Mon, Mar 6, 2023 at 7:51 PM David Rowley  wrote:

> On Tue, 7 Mar 2023 at 12:40, Tom Lane  wrote:
> >
> > Ben Clements  writes:
> > > As shown above, the following calculated column can bring in the city
> name,
> > > even though the city name isn't in the GROUP BY:
> > >max(city) keep (dense_rank first order by population desc)
> >
> > You haven't really explained what this does, let alone why it can't
> > be implemented with existing features such as FILTER and ORDER BY.
>
> (It wasn't clear to me until I watched the youtube video.)


> Likely KEEP is more flexible than just the given example but I think
> that something similar to the example given could be done by inventing
> a TOP() and BOTTOM() aggregate. Then you could write something like:
>
> select
>country,
>count(*),
>max(population),
>bottom(city, population)
> from
>cities
> group by
>country
> having
>count(*) > 1
>
> the transfn for bottom() would need to remember the city and the
> population for the highest yet seen value of the 2nd arg.


BOTTOM() remembers the highest value?


> Where this wouldn't work would be if multiple columns were
> required to tiebreak the sort.
>

TOP(city, ROW(population, land_area)) ?

I'd assume since the whole thing can be done with
> a subquery that the entire point of having special syntax for this
> would be because we don't want to pay the price of looking at the
> table twice, i.e. performance must matter, so the ability to have
> parallel aggregates here seems good.
>

SELECT country, city,
  rank() over (partition by country order by population desc),
  count() OVER (partition by country)
FROM cities
WINDOW_HAVING count > 0 AND rank = 1;

That would be, IMO, the idiomatic query form to perform ranking - not
abusing GROUP BY.  To add this encourages abusing GROUP BY.

Though I suppose if there is a sufficient performance gain to be had under
GROUP BY the effort might make sense if further improvements to window
function processing cannot be found.

David J.

Re: How does Postgres store a B-Tree on disk while using the OS file system?

[Siddharth Jain]

thanks Christophe. will try to go through it. its bit difficult to grasp.

On Mon, Mar 6, 2023 at 5:08 PM Christophe Pettus  wrote:

>
>
> > On Mar 6, 2023, at 16:24, Siddharth Jain  wrote:
> > My question: How can it then store a B Tree on disk? I would think
> storing a B Tree requires storing disk offset addresses and so on (for a
> node to navigate to another etc.). For this, one would need to write
> directly to the disk using low-level disk access functions and not use file
> system API.
>
> All of PostgreSQL's relations (tables and indexes) are stored in files.
> (They're actually stored in a set of files if it's larger than 1GB, but
> each relation is treated as one logical file.)  The "pointers" in this case
> are just offsets from the start of that file.
>
> There's some additional information here:
>
> https://www.postgresql.org/docs/current/storage.html
>
> and here:
>
> https://www.postgresql.org/docs/current/btree-implementation.html

Re: Idea: PostgreSQL equivalent to Oracle's KEEP clause

[David Rowley]

On Tue, 7 Mar 2023 at 12:40, Tom Lane  wrote:
>
> Ben Clements  writes:
> > As shown above, the following calculated column can bring in the city name,
> > even though the city name isn't in the GROUP BY:
> >max(city) keep (dense_rank first order by population desc)
>
> You haven't really explained what this does, let alone why it can't
> be implemented with existing features such as FILTER and ORDER BY.

(It wasn't clear to me until I watched the youtube video.)

Likely KEEP is more flexible than just the given example but I think
that something similar to the example given could be done by inventing
a TOP() and BOTTOM() aggregate. Then you could write something like:

select
   country,
   count(*),
   max(population),
   bottom(city, population)
from
   cities
group by
   country
having
   count(*) > 1

the transfn for bottom() would need to remember the city and the
population for the highest yet seen value of the 2nd arg.  The
combinefn would need to find the aggregate state with the highest 2nd
arg value, the finalfn would just spit out the column that's stored in
the state.  Where this wouldn't work would be if multiple columns were
required to tiebreak the sort.

You could at least parallelize the aggregation this way. If there were
to be some form of ORDER BY in the aggregate then no parallelization
would be possible.  I'd assume since the whole thing can be done with
a subquery that the entire point of having special syntax for this
would be because we don't want to pay the price of looking at the
table twice, i.e. performance must matter, so the ability to have
parallel aggregates here seems good.

I can't quite think of a way to have parallel query and an arbitrarily
long list of columns to sort on...

For Ben, we do tend to shy away from copying other RDBMS's extensions
to the SQL language.  The problem is that copying these can cause
problems in the future if/when the standard adopts that syntax with
variations or invents something else that conflicts with the grammar
that we've added.  One example of something we didn't do was Oracle's
CONNECT BY.  Eventually, the SQL standard got WITH RECURSIVE to allow
queries on hierarchical data. Of course, we do have many of our own
extensions to the standard, so we certainly do make exceptions
sometimes. So, don't be too surprised that there's some discussion of
other methods which might make this work which don't involve copying
what someone else has done.

David

Re: How does Postgres store a B-Tree on disk while using the OS file system?

[Christophe Pettus]

> On Mar 6, 2023, at 16:24, Siddharth Jain  wrote:
> My question: How can it then store a B Tree on disk? I would think storing a 
> B Tree requires storing disk offset addresses and so on (for a node to 
> navigate to another etc.). For this, one would need to write directly to the 
> disk using low-level disk access functions and not use file system API.

All of PostgreSQL's relations (tables and indexes) are stored in files.  
(They're actually stored in a set of files if it's larger than 1GB, but each 
relation is treated as one logical file.)  The "pointers" in this case are just 
offsets from the start of that file.

There's some additional information here:

https://www.postgresql.org/docs/current/storage.html

and here:

https://www.postgresql.org/docs/current/btree-implementation.html

Re: How does Postgres store a B-Tree on disk while using the OS file system?

[Ron]

On 3/6/23 18:24, Siddharth Jain wrote:
I am trying to sharpen my understanding of Postgres. As I understand, 
Postgres does not write directly to disk blocks. It uses the file system 
provided by the OS:

https://dba.stackexchange.com/questions/80036/is-there-a-way-to-store-a-postgresql-database-directly-on-a-block-device-not-fi
My question: How can it then store a B Tree on disk? I would think storing 
a B Tree requires storing disk offset addresses and so on (for a node to 
navigate to another etc.). For this, one would need to write directly to 
the disk using low-level disk access functions and not use file system API.


Disk offsets... *file* offsets... one is just a step up on the abstraction 
layer.


How can that be? The OS only knows about Logical Block Addresses, which are 
a huge list of block numbers (just like userland software sees files as long 
lists of bytes).  It hasn't see cylinders, heads and platters in almost 
FORTY (more, probably, for SCSI and mainframe systems) years.


--
Born in Arizona, moved to Babylonia.

How does Postgres store a B-Tree on disk while using the OS file system?

[Siddharth Jain]

I am trying to sharpen my understanding of Postgres. As I understand,
Postgres does not write directly to disk blocks. It uses the file system
provided by the OS:
https://dba.stackexchange.com/questions/80036/is-there-a-way-to-store-a-postgresql-database-directly-on-a-block-device-not-fi
My question: How can it then store a B Tree on disk? I would think storing
a B Tree requires storing disk offset addresses and so on (for a node to
navigate to another etc.). For this, one would need to write directly to
the disk using low-level disk access functions and not use file system API.

Re: Idea: PostgreSQL equivalent to Oracle's KEEP clause

[Tom Lane]

Ben Clements  writes:
> As shown above, the following calculated column can bring in the city name,
> even though the city name isn't in the GROUP BY:
>max(city) keep (dense_rank first order by population desc)

You haven't really explained what this does, let alone why it can't
be implemented with existing features such as FILTER and ORDER BY.

regards, tom lane

Idea: PostgreSQL equivalent to Oracle's KEEP clause

[Ben Clements]

I have an idea/request for enhancement to PostgreSQL (I'm new to PostgreSQL
and this mailing list).

Idea:

There's a technique in Oracle SQL that can be used to simplify aggregation
queries:
*Aggregate on a particular column, but get information from a different
column, using a simple calculated column in the SELECT list.*

--Oracle
--For a given country, what city has the highest population? (where the
country has more than one city)
--Include the city name as a column.
select
   country,
   count(*),
   max(population),
   max(city) keep (dense_rank first order by population desc)
from
   cities
group by
   country
having
   count(*) > 1

As shown above, the following calculated column can bring in the city name,
even though the city name isn't in the GROUP BY:
   max(city) keep (dense_rank first order by population desc)

There are a number of ways to achieve that kind of thing using PostgreSQL.
I want a solution that lets me do it in a calculated column -- all within a
single SELECT query (no subqueries, joins, WITH, etc.).

Could that functionality be added to PostgreSQL?

Related:

   - YouTube - The KEEP clause will KEEP your SQL queries SIMPLE (Oracle)
   
   - Stack Overflow - Explanation of KEEP in Oracle FIRST/LAST
   

   - DBA Stack Exchange - PostgreSQL equivalent to Oracle's MAX(...) KEEP
   (DENSE_RANK FIRST/LAST ORDER BY ...)
   

Thanks,

-Ben

Re: garbage data back

[Adrian Klaver]

On 3/6/23 1:27 PM, Brad White wrote:

In that picture, it's clear what is happening.
Here, again, is the result that had me stumped.
image.png
What threw me was that the field I was looking for had the format of 
6d-4d and this field appeared to have the same format.
But once you expand it, you can see that it has a 6d-7d format and isn't 
the field I'm looking for at all.  I was trying to take a shortcut by 
finding the data in the table instead of digging in and looking up what 
field was used in the report. And it appeared at first that I had found 
it.  ¯\_(?)_/¯


psql is your friend.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: garbage data back

[Brad White]

In that picture, it's clear what is happening.
Here, again, is the result that had me stumped.
[image: image.png]
What threw me was that the field I was looking for had the format of 6d-4d
and this field appeared to have the same format.
But once you expand it, you can see that it has a 6d-7d format and isn't
the field I'm looking for at all.  I was trying to take a shortcut by
finding the data in the table instead of digging in and looking up what
field was used in the report. And it appeared at first that I had found
it.  ¯\_(?)_/¯

On Mon, Mar 6, 2023 at 2:54 PM David G. Johnston 
wrote:

> On Mon, Mar 6, 2023 at 1:48 PM Brad White  wrote:
>
>> LOL
>>
>> Joke's on me.
>>
>> Here's the relevant part of the view
>>
>> SELECT ...
>> "Order Items"."ID" AS "OrderItemID",
>> ...
>> (("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"
>>
>> Wait, then why are the Item IDs different?
>>
>> Here are the results again, with the FileKey field expanded a bit.
>> [image: image.png]
>>
>
> I don't understand what you are questioning...FileKey is a hyphenated
> concatenation of the ORDER Id and the ORDER ITEM Id, while OrderItemID is
> just the later - and the suffix of the former matches the later.
>
> David J.
>

Re: Fwd: garbage data back

[Adrian Klaver]

On 3/6/23 12:48 PM, Brad White wrote:

LOL

Joke's on me.

Here's the relevant part of the view

SELECT ...
     "Order Items"."ID" AS "OrderItemID",
     ...
     (("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"\


Because "Orders"."ID"  is different then "Order Items"."ID"?



Wait, then why are the Item IDs different?



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: garbage data back

[David G. Johnston]

On Mon, Mar 6, 2023 at 1:48 PM Brad White  wrote:

> LOL
>
> Joke's on me.
>
> Here's the relevant part of the view
>
> SELECT ...
> "Order Items"."ID" AS "OrderItemID",
> ...
> (("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"
>
> Wait, then why are the Item IDs different?
>
> Here are the results again, with the FileKey field expanded a bit.
> [image: image.png]
>

I don't understand what you are questioning...FileKey is a hyphenated
concatenation of the ORDER Id and the ORDER ITEM Id, while OrderItemID is
just the later - and the suffix of the former matches the later.

David J.

Fwd: garbage data back

[Brad White]

LOL

Joke's on me.

Here's the relevant part of the view

SELECT ...
"Order Items"."ID" AS "OrderItemID",
...
(("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"

Wait, then why are the Item IDs different?

Here are the results again, with the FileKey field expanded a bit.
[image: image.png]

On Mon, Mar 6, 2023 at 2:23 PM Adrian Klaver 
wrote:

> On 3/6/23 12:17, Brad White wrote:
> > I was doing a SELECT * FROM view WHERE field LIKE pattern
> > and getting garbage data back.
> >
> > Turns out it is filtering on a different field.
> >
> > As you can see here, when attempting to filter on the FileKey, it is
> > actually filtering on the Order Item ID field.
> >
> > select "FileKey", "OrderItemID" from "vw_rptInvc_Permits"
> > where "FileKey" LIKE '%1317'
> >
> > image.png
> > I'm stumped. Any guesses?
>
>
> What is the view definition?
>
> Is there a table named vw_rptInvc_Permits?
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: garbage data back

[David G. Johnston]

On Mon, Mar 6, 2023 at 1:18 PM Brad White  wrote:

>
> As you can see here, when attempting to filter on the FileKey, it is
> actually filtering on the Order Item ID field.
>
> select "FileKey", "OrderItemID" from "vw_rptInvc_Permits"
> where "FileKey" LIKE '%1317'
>
> I'm stumped. Any guesses?
>

Agreed, what you've shown doesn't make sense.

Try:
EXPLAIN ANALYZE
REINDEX (on any underlying table indexes)

Show the version you are running.

David J.

Re: garbage data back

[Adrian Klaver]

On 3/6/23 12:17, Brad White wrote:

I was doing a SELECT * FROM view WHERE field LIKE pattern
and getting garbage data back.

Turns out it is filtering on a different field.

As you can see here, when attempting to filter on the FileKey, it is 
actually filtering on the Order Item ID field.


select "FileKey", "OrderItemID" from "vw_rptInvc_Permits"
where "FileKey" LIKE '%1317'

image.png
I'm stumped. Any guesses?



What is the view definition?

Is there a table named vw_rptInvc_Permits?

--
Adrian Klaver
adrian.kla...@aklaver.com

garbage data back

[Brad White]

I was doing a SELECT * FROM view WHERE field LIKE pattern
and getting garbage data back.

Turns out it is filtering on a different field.

As you can see here, when attempting to filter on the FileKey, it is
actually filtering on the Order Item ID field.

select "FileKey", "OrderItemID" from "vw_rptInvc_Permits"
where "FileKey" LIKE '%1317'

[image: image.png]
I'm stumped. Any guesses?

Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?

[Dominique Devienne]

On Mon, Mar 6, 2023 at 4:31 PM Tom Lane  wrote:

> Dominique Devienne  writes:
> > On Mon, Mar 6, 2023 at 4:06 PM Tom Lane  wrote:
> >> DDL commands in Postgres are
> >> transactional unless their man page explicitly says they're not.
>
> > Could you point to where in the doc this is stated?
>
> For example, for CREATE DATABASE the first "Note" para in [1] is
>
> CREATE DATABASE cannot be executed inside a transaction block.
>
> I don't think we are entirely consistent about whether this is
> mentioned in "Description" or "Notes", but it's there somewhere.
>

Yes, I noticed that, once I read Erik's email (went to GMail's SPAM
folder...).

Still, my little brain didn't leap to the conclusion that other DDLs were
implicitly transactional,
because missing of a note another DDL's doc has. All I'm saying it's not as
obvious as you
experienced PostgreSQL folks seems to think it is. FWIW. Thanks again, --DD

Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?

[Dominique Devienne]

On Mon, Mar 6, 2023 at 3:02 PM Erik Wienhold  wrote:

> > On 06/03/2023 14:19 CET Dominique Devienne  wrote:
> > Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs and
> > GRANTs transactional?
>
> Have you tried?


Nope. I thought about it, but then I could have convinced myself on an
invalid demo.
So I preferred to ask the experts.


> DDL is transactional unless stated otherwise (cf. CREATE DATABASE,
> CREATE INDEX CONCURRENTLY, CREATE TABLESPACE).
>

I now see (and fully appreciate) the Note below:
- CREATE DATABASE cannot be executed inside a transaction block.

I didn't realize the fact CREATE ROLE didn't have that note was significant.

Run the following psql script:
>

Thanks for the demo. Appreciated.

> Since I'm creating many ROLEs and making many GRANTs, based info I read
> from
> > PostgreSQL itself (in pg_catalog and elsewhere), should everything be in
> a
> > single transaction?
>
> If it should be atomic and the commands are allowed in transactions, then
> yes,
> use transactions.
>

Thanks again. --DD

Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?

[Tom Lane]

Dominique Devienne  writes:
> On Mon, Mar 6, 2023 at 4:06 PM Tom Lane  wrote:
>> DDL commands in Postgres are
>> transactional unless their man page explicitly says they're not.

> Could you point to where in the doc this is stated?

For example, for CREATE DATABASE the first "Note" para in [1] is

CREATE DATABASE cannot be executed inside a transaction block.

I don't think we are entirely consistent about whether this is
mentioned in "Description" or "Notes", but it's there somewhere.

regards, tom lane

[1] https://www.postgresql.org/docs/current/sql-createdatabase.html

Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?

[Dominique Devienne]

On Mon, Mar 6, 2023 at 4:06 PM Tom Lane  wrote:

> Dominique Devienne  writes:
> > Hi. Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs
> > and GRANTs transactional?
>
> Your expectation is set wrongly.


Thanks for the precision Tom.

Although I'm not sure where you read my expectectation was wrong. But
nevermind.


> DDL commands in Postgres are
> transactional unless their man page explicitly says they're not.
>

OK, didn't know that. Glad to read it.

Could you point to where in the doc this is stated?

It's hard to be sure about something the doc does *not* mention,
when what is *implied* is hard to find, or at a distance. Especially
at least another well known RDBMS differ in that department.
I much prefer the way PostgreSQL handles DDL, still it's not obvious.

My $0.02. Thanks, --DD

Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?

[Tom Lane]

Dominique Devienne  writes:
> Hi. Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs
> and GRANTs transactional?

Your expectation is set wrongly.  DDL commands in Postgres are
transactional unless their man page explicitly says they're not.

regards, tom lane

Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?

[Erik Wienhold]

> On 06/03/2023 14:19 CET Dominique Devienne  wrote:
>
> Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs and
> GRANTs transactional?

Have you tried?  DDL is transactional unless stated otherwise (cf. CREATE 
DATABASE,
CREATE INDEX CONCURRENTLY, CREATE TABLESPACE).

Run the following psql script:

drop role if exists alice, bob;

\du

begin;
create role alice;
\du
rollback;

\du

begin;
create role alice;
create role bob;
commit;

\du

begin;
grant alice to bob;
\du
rollback;

\du

begin;
drop role alice;
\du
rollback;

\du

Output:

DROP ROLE
   List of roles
 Role name | Attributes 
| Member of

---++---
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS 
| {}

BEGIN
CREATE ROLE
   List of roles
 Role name | Attributes 
| Member of

---++---
 alice | Cannot login   
| {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS 
| {}

ROLLBACK
   List of roles
 Role name | Attributes 
| Member of

---++---
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS 
| {}

BEGIN
CREATE ROLE
CREATE ROLE
COMMIT
   List of roles
 Role name | Attributes 
| Member of

---++---
 alice | Cannot login   
| {}
 bob   | Cannot login   
| {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS 
| {}

BEGIN
GRANT ROLE
   List of roles
 Role name | Attributes 
| Member of

---++---
 alice | Cannot login   
| {}
 bob   | Cannot login   
| {alice}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS 
| {}

ROLLBACK
   List of roles
 Role name | Attributes 
| Member of

---++---
 alice | Cannot login   
| {}
 bob   | Cannot login   
| {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS 
| {}

BEGIN
DROP ROLE
   List of roles
 Role name | Attributes 
| Member of

---++---
 bob   | Cannot login   
| {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS 
| {}

ROLLBACK
   List of roles
 Role name | Attributes 
| Member of

---++---
 alice | Cannot login   
| {}
 bob   | Cannot login   
| {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS 
| {}

> Since I'm creating many ROLEs and making many GRANTs, based info I read from
> PostgreSQL itself (in pg_catalog and elsewhere), should everything be in a
> single transaction?

If it should be atomic and the commands are allowed in transactions, then yes,
use transactions.

--
Erik

CREATE/DROP ROLE transactional? GRANT/REVOKE?

[Dominique Devienne]

Hi. Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs
and GRANTs transactional?

Since I'm creating many ROLEs and making many GRANTs, based info I read
from PostgreSQL itself (in pg_catalog and elsewhere), should everything be
in a single transaction?

FWIW, I come from Oracle (where DDL is non-transactional and an implicit
COMMIT), and SQLite (where ROLEs and GRANTs don't apply), thus this perhaps
silly question.

I'm already aware that SCHEMAs, TABLEs, etc... are transactionally created.
But given that ROLEs are cluster-wide, and the doc on DDLs say nothing, I
prefer to ask.

Thanks, --DD

[1]: https://www.postgresql.org/docs/current/sql-droprole.html

Re: Row Level Security Policy Name in Error Message

[Daniel Gustafsson]

> On 6 Mar 2023, at 01:18, Louis Tian  wrote:

> Wondering whether there is a way to get the row-level security policy name in 
> the error message when it's violated. 
> I am only getting a more generic error message like this.
> ERROR:  new row violates row-level security policy for table "table_name" 

If memory serves me right.  The name will be included in the error message in
case the operation violated a specific restrictive policy.  If no permissive
policy was found that would allow the operation then the error message will be
what you saw without a policy name (since none was explicitly violated).

--
Daniel Gustafsson

Row Level Security Policy Name in Error Message

[Louis Tian]

Hi All,

Wondering whether there is a way to get the row-level security policy name in 
the error message when it's violated.
I am only getting a more generic error message like this.
ERROR:  new row violates row-level security policy for table "table_name"

Thanks for your help.

Regards,
Louis Tian