MIME-Version: 1.0
References: <e247c261-e3fb-4810-81e0-a65893170e94@dunslane.net>
 <CAN4CZFOE0w4YwuVA74G39BMoKCSvVjCOJ+7X+XiSiaBaH4LXmA@mail.gmail.com>
 <5b21d39b-47fe-4a27-86bd-0cc6b924c8f0@app.fastmail.com>
In-Reply-To: <5b21d39b-47fe-4a27-86bd-0cc6b924c8f0@app.fastmail.com>
From: Zsolt Parragi <zsolt.parragi@percona.com>
Date: Fri, 20 Mar 2026 22:29:29 +0000
Message-ID: 
 <CAN4CZFOfrWKSLAxCsdUXmsHk0xwB6B29B61JN_SFLa_+jVQyCQ@mail.gmail.com>
Subject: Re: pg_get__*_ddl consolidation
To: Euler Taveira <euler@eulerto.com>
Cc: Andrew Dunstan <andrew@dunslane.net>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAN4CZFOfrWKSLAxCsdUXmsHk0xwB6B29B61JN_SFLa_%2BjVQyCQ%40mail.gmail.com>
Precedence: bulk

> I couldn't reproduce the failure. The non-fixed DateStyle is by design. It
> mimics pg_dumpall.

Sorry, I made a mistake while only copying the relevant parts of the
test case here. This is the correct test case:

SET DateStyle TO 'SQL, DMY'; -- change from default MDY to DMY
CREATE ROLE regress_datestyle_test VALID UNTIL '2030-12-31 23:59:59+00';
SELECT * FROM pg_get_role_ddl('regress_datestyle_test');
DROP ROLE regress_datestyle_test;
SET DateStyle TO 'SQL, MDY'; -- go back to default, or open a new
connection to use the generated command
-- try to run statement generated by pg_get_role_ddl

I'm not saying that this is necessarily wrong, but perhaps it's worth
mentioning somewhere?

> Since I don't know if you will use the testrole role to create testdb
> database or even if the testrole exists in the cluster, it shouldn't return the
> ALTER DATABASE testdb SET work_mem TO '512MB' (because that property belongs to
> testrole role).

Sorry, I wasn't specific previously, it returns the role specific
role, without the role specific condition:

CREATE DATABASE testdb;
CREATE ROLE testrole;
ALTER DATABASE testdb SET work_mem TO '256MB';
ALTER ROLE testrole IN DATABASE testdb SET work_mem TO '512MB';
SELECT pg_get_database_ddl('testdb');
                                                pg_get_database_ddl
-------------------------------------------------------------------------------------------------------------------
 CREATE DATABASE testdb WITH TEMPLATE = template0 ENCODING = 'UTF8'
LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';
 ALTER DATABASE testdb OWNER = dutow;
 ALTER DATABASE testdb SET work_mem TO '256MB';
 ALTER DATABASE testdb SET work_mem TO '512MB';


> one way is to
> compare the output with pg_dump(all). Another point is that these functions can
> be used by a dump tool.)

I did my previous testing with this in mind.

Both the role specific database options and the role memberships are
related: I understand if you say that this is a design decision /
limitation. On the other hand if the goal is that users should be able
to replicate dump(all) with these functions,  then we should have a
way to also get that information, either by providing two different
outputs, or by specific additional getter functions.