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