Hello, The attached patch changes the location of the dumpUserConfig call in the dumpRoles function of pg_dumpall.
This is related to this thread: http://archives.postgresql.org/pgsql-hackers/2011-02/msg02359.php Currently if you use 'ALTER ROLE rolename SET ROLE', pg_dumpall will dump an 'ALTER ROLE' out right after the 'CREATE ROLE' statement. Sometimes this will cause a conflict when a dependent role is not yet created: -- -- Roles -- CREATE ROLE a; ALTER ROLE a WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION; ALTER ROLE a SET role TO 'b'; CREATE ROLE b; ALTER ROLE b WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION; CREATE ROLE postgres; ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION; As you can see, role a is set to role b before role b is created. This patch moves the call to dumpUserConfig to after the loop where all the roles are created. This produces output like the this: -- -- Roles -- CREATE ROLE a; ALTER ROLE a WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION; CREATE ROLE b; ALTER ROLE b WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION; CREATE ROLE postgres; ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION; ALTER ROLE a SET role TO 'b'; Now this dump will succeed upon restore. This passed all regression tests. Thanks.
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c new file mode 100644 index b5f64e8..ee597d5 *** a/src/bin/pg_dump/pg_dumpall.c --- b/src/bin/pg_dump/pg_dumpall.c *************** dumpRoles(PGconn *conn) *** 804,814 **** buf, "ROLE", rolename); fprintf(OPF, "%s", buf->data); - - if (server_version >= 70300) - dumpUserConfig(conn, rolename); } PQclear(res); fprintf(OPF, "\n\n"); --- 804,815 ---- buf, "ROLE", rolename); fprintf(OPF, "%s", buf->data); } + if (server_version >= 70300) + for (i = 0; i < PQntuples(res); i++) + dumpUserConfig(conn, PQgetvalue(res, i, i_rolname)); + PQclear(res); fprintf(OPF, "\n\n");
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
