On Mon, Mar 6, 2023 at 3:02 PM Erik Wienhold <ewie@ewie.name> wrote:
> On 06/03/2023 14:19 CET Dominique Devienne <ddevienne@gmail.com> 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.