Re: [GENERAL] pg_dumping extensions having sequences with 9.6beta3
On Wed, Jul 27, 2016 at 8:07 AM, Stephen Frost wrote:
> That'd be great. It's definitely on my list of things to look into, but
> I'm extremely busy this week. I hope to look into it on Friday, would
> be great to see what you find.
Sequences that are directly defined in extensions do not get dumped,
and sequences that are part of a serial column in an extension are
getting dumped. Looking into this problem, getOwnedSeqs() is visibly
doing an incorrect assumption: sequences owned by table columns are
dumped unconditionally, but this is not true for sequences that are
part of extensions. More precisely, dobj->dump is being enforced to
DUMP_COMPONENT_ALL, which makes the sequence definition to be dumped.
Oops.
The patch attached fixes the problem for me. I have added as well
tests in test_pg_dump in the shape of sequences defined in an
extension, and sequences that are part of a serial column. This patch
is also able to work in the case where a sequence is created as part
of a serial column, and gets removed after, say with ALTER EXTENSION
DROP SEQUENCE. The behavior for sequences and serial columns that are
not part of extensions is unchanged.
Stephen, it would be good if you could check the correctness of this
patch as you did all this refactoring of pg_dump to support catalog
ACLs. I am sure by the way that checking for (owning_tab->dobj.dump &&
DUMP_COMPONENT_DEFINITION) != 0 is not good because of for example the
case of a serial column created in an extension where the sequence is
dropped from the extension afterwards.
--
Michael
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 08c2b0c..0278995 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -6037,6 +6037,8 @@ getOwnedSeqs(Archive *fout, TableInfo tblinfo[], int numTables)
continue; /* not an owned sequence */
if (seqinfo->dobj.dump & DUMP_COMPONENT_DEFINITION)
continue; /* no need to search */
+ if (seqinfo->dobj.ext_member)
+ continue; /* member of an extension */
owning_tab = findTableByOid(seqinfo->owning_tab);
if (owning_tab && owning_tab->dobj.dump)
{
diff --git a/src/test/modules/test_pg_dump/t/001_base.pl b/src/test/modules/test_pg_dump/t/001_base.pl
index fd9c37f..9caee93 100644
--- a/src/test/modules/test_pg_dump/t/001_base.pl
+++ b/src/test/modules/test_pg_dump/t/001_base.pl
@@ -226,7 +226,7 @@ my %tests = (
'CREATE TABLE regress_pg_dump_table' => {
regexp => qr/^
\QCREATE TABLE regress_pg_dump_table (\E
- \n\s+\Qcol1 integer,\E
+ \n\s+\Qcol1 integer NOT NULL,\E
\n\s+\Qcol2 integer\E
\n\);$/xm,
like => { binary_upgrade => 1, },
@@ -241,6 +241,48 @@ my %tests = (
schema_only=> 1,
section_pre_data => 1,
section_post_data => 1, }, },
+ 'CREATE SEQUENCE regress_pg_dump_table_col1_seq' => {
+ regexp => qr/^
+ \QCREATE SEQUENCE regress_pg_dump_table_col1_seq\E
+ \n\s+\QSTART WITH 1\E
+ \n\s+\QINCREMENT BY 1\E
+ \n\s+\QNO MINVALUE\E
+ \n\s+\QNO MAXVALUE\E
+ \n\s+\QCACHE 1;\E
+ $/xm,
+ like => { binary_upgrade => 1, },
+ unlike => {
+ clean => 1,
+ clean_if_exists=> 1,
+ createdb => 1,
+ defaults => 1,
+ no_privs => 1,
+ no_owner => 1,
+ pg_dumpall_globals => 1,
+ schema_only=> 1,
+ section_pre_data => 1,
+ section_post_data => 1, }, },
+ 'CREATE SEQUENCE regress_pg_dump_seq' => {
+ regexp => qr/^
+ \QCREATE SEQUENCE regress_pg_dump_seq\E
+ \n\s+\QSTART WITH 1\E
+ \n\s+\QINCREMENT BY 1\E
+ \n\s+\QNO MINVALUE\E
+ \n\s+\QNO MAXVALUE\E
+ \n\s+\QCACHE 1;\E
+ $/xm,
+ like => { binary_upgrade => 1, },
+ unlike => {
+ clean => 1,
+ clean_if_exists=> 1,
+ createdb => 1,
+ defaults => 1,
+ no_privs => 1,
+ no_owner => 1,
+ pg_dumpall_globals => 1,
+ schema_only=> 1,
+ section_pre_data => 1,
+ section_post_data => 1, }, },
'CREATE ACCESS METHOD regress_test_am' => {
regexp => qr/^
\QCREATE ACCESS METHOD regress_test_am TYPE INDEX HANDLER bthandler;\E
diff --git a/src/test/modules/test_pg_dump/test_pg_dump--1.0.sql b/src/test/modules/test_pg_dump/test_pg_dump--1.0.sql
index 5fe6063..93de2c5 100644
--- a/src/test/modules/test_pg_dump/test_pg_dump--1.0.sql
+++ b/src/test/modules/test_pg_dump/test_pg_dump--1.0.sql
@@ -4,10 +4,12 @@
\echo Use "CREATE EXTENSION test_pg_dump" to load this file. \quit
CREATE TABLE regress_pg_dump_table (
- col1 int,
+ col1 serial,
col2 int
);
+CREATE SEQUENCE regress_pg_dump_seq;
+
GRANT SELECT ON regress_pg_dump_table TO regress_dump_test_role;
GRANT SELECT(col1) ON regress_pg_dump_table TO public;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dumping extensions having sequences with 9.6beta3
Michael, * Michael Paquier (michael.paqu...@gmail.com) wrote: > On Tue, Jul 26, 2016 at 4:50 PM, Noah Misch wrote: > > [Action required within 72 hours. This is a generic notification.] > > > > The above-described topic is currently a PostgreSQL 9.6 open item. Stephen, > > since you committed the patch believed to have created it, you own this open > > item. If some other commit is more relevant or if this does not belong as a > > 9.6 open item, please let us know. Otherwise, please observe the policy on > > open item ownership[1] and send a status update within 72 hours of this > > message. Include a date for your subsequent status update. Testers may > > discover new open items at any time, and I want to plan to get them all > > fixed > > well in advance of shipping 9.6rc1. Consequently, I will appreciate your > > efforts toward speedy resolution. Thanks. > > > > [1] > > http://www.postgresql.org/message-id/20160527025039.ga447...@tornado.leadboat.com > > I am not sure what's Stephen's status on this item, but I am planning > to look at it within the next 24 hours. That'd be great. It's definitely on my list of things to look into, but I'm extremely busy this week. I hope to look into it on Friday, would be great to see what you find. Thanks! Stephen signature.asc Description: Digital signature
Re: [GENERAL] pg_dumping extensions having sequences with 9.6beta3
On Tue, Jul 26, 2016 at 4:50 PM, Noah Misch wrote: > [Action required within 72 hours. This is a generic notification.] > > The above-described topic is currently a PostgreSQL 9.6 open item. Stephen, > since you committed the patch believed to have created it, you own this open > item. If some other commit is more relevant or if this does not belong as a > 9.6 open item, please let us know. Otherwise, please observe the policy on > open item ownership[1] and send a status update within 72 hours of this > message. Include a date for your subsequent status update. Testers may > discover new open items at any time, and I want to plan to get them all fixed > well in advance of shipping 9.6rc1. Consequently, I will appreciate your > efforts toward speedy resolution. Thanks. > > [1] > http://www.postgresql.org/message-id/20160527025039.ga447...@tornado.leadboat.com I am not sure what's Stephen's status on this item, but I am planning to look at it within the next 24 hours. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dumping extensions having sequences with 9.6beta3
On Sat, Jul 23, 2016 at 01:40:01PM +0900, Michael Paquier wrote: > On Fri, Jul 22, 2016 at 6:27 PM, Philippe BEAUDOIN wrote: > > I am currently playing with extensions. And I found a strange behaviour > > change with 9.6beta2 and 3 when pg_dumping a database with an extension > > having sequences. This looks like a bug, ... unless I did something wrong. > > [...] > > => as expected, with latest minor versions of postgres 9.1 to 9.5, the > > sequences associated to the t1.c1 and t1.c3 columns are not dumped, > >while the sequence associated to t2.c1 is dumped. > > => with 9.6beta3 (as with beta2), the 3 sequences are dumped. > > Thanks for the report! I haven't looked at the problem in details yet, > but my guess is that this is owned by Stephen Frost. test_pg_dump does > not cover sequences yet, it would be visibly good to get coverage for > that. I am adding an open item as well. [Action required within 72 hours. This is a generic notification.] The above-described topic is currently a PostgreSQL 9.6 open item. Stephen, since you committed the patch believed to have created it, you own this open item. If some other commit is more relevant or if this does not belong as a 9.6 open item, please let us know. Otherwise, please observe the policy on open item ownership[1] and send a status update within 72 hours of this message. Include a date for your subsequent status update. Testers may discover new open items at any time, and I want to plan to get them all fixed well in advance of shipping 9.6rc1. Consequently, I will appreciate your efforts toward speedy resolution. Thanks. [1] http://www.postgresql.org/message-id/20160527025039.ga447...@tornado.leadboat.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dumping extensions having sequences with 9.6beta3
On Fri, Jul 22, 2016 at 6:27 PM, Philippe BEAUDOIN wrote: > I am currently playing with extensions. And I found a strange behaviour > change with 9.6beta2 and 3 when pg_dumping a database with an extension > having sequences. This looks like a bug, ... unless I did something wrong. > [...] > => as expected, with latest minor versions of postgres 9.1 to 9.5, the > sequences associated to the t1.c1 and t1.c3 columns are not dumped, >while the sequence associated to t2.c1 is dumped. > => with 9.6beta3 (as with beta2), the 3 sequences are dumped. Thanks for the report! I haven't looked at the problem in details yet, but my guess is that this is owned by Stephen Frost. test_pg_dump does not cover sequences yet, it would be visibly good to get coverage for that. I am adding an open item as well. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dumping extensions having sequences with 9.6beta3
Hi all, I am currently playing with extensions. And I found a strange behaviour change with 9.6beta2 and 3 when pg_dumping a database with an extension having sequences. This looks like a bug, ... unless I did something wrong. Here is a test case (a simple linux shell script, that can be easily customized to reproduce). # pg_dump issue in postgres 9.6beta2 when dumping sequences linked to extensions # export PGBIN="/usr/local/pg96beta3/bin" #export PGBIN="/usr/local/pg952/bin" export EXTDIR="/tmp" export PGDIR="/usr/local/pg96beta3/share/postgresql/extension" #export PGDIR="/usr/local/pg952/share/postgresql/extension" export PGPORT=5496 #export PGPORT=5495 export PGDATABASE='postgres' echo "##" echo " " echo "psql: prepare the initial environment: 1 schema + 2 tables with 1 serial column in each" echo "---" $PGBIN/psql -a <<*END* select version(); -- cleanup DROP EXTENSION IF EXISTS myextension; DROP SCHEMA IF EXISTS myextension CASCADE; -- create CREATE SCHEMA myextension; CREATE TABLE myextension.t1 (c1 SERIAL); CREATE TABLE myextension.t2 (c1 SERIAL); *END* echo "create first files for extension management" echo "---" cat >$EXTDIR/myextension.control <<*END* default_version= '1' comment= 'test' directory= '$EXTDIR' superuser= true schema= 'myextension' relocatable= false *END* sudo ln -s $EXTDIR/myextension.control $PGDIR/myextension.control cat >$EXTDIR/myextension--unpackaged--1.sql <<*END* -- for t1, the table and the sequence is added to the extension ALTER EXTENSION myextension ADD TABLE myextension.t1; ALTER EXTENSION myextension ADD SEQUENCE myextension.t1_c1_seq; -- for t2, the associated sequence is not added to the extension for now ALTER EXTENSION myextension ADD TABLE myextension.t2; -- create a new t3 table CREATE TABLE t3 (c1 SERIAL); *END* echo "psql: create the extension from unpackaged" echo "--" $PGBIN/psql -a <<*END* -- create CREATE EXTENSION myextension FROM unpackaged; -- check \dx SELECT classid, c1.relname, objid, c2.relname, c2.relkind, refclassid, r.relname, refobjid FROM pg_depend, pg_class c1, pg_class r, pg_class c2 WHERE deptype = 'e' AND classid = c1.oid AND refclassid = r.oid AND objid = c2.oid AND c1.relname = 'pg_class'; *END* echo " " echo "So we now have 3 tables having a serial column:" echo " - t1 explicitely added to the extension, with its sequence" echo " - t2 explicitely added to the extension, but without its sequence" echo " - t3 directly created inside the extensione" echo " " echo "sequences dumped by pg_dump (pg_dump |grep 'CREATE SEQUENCE')" echo "---" $PGBIN/pg_dump |grep 'CREATE SEQUENCE' echo " " echo "=> as expected, with latest minor versions of postgres 9.1 to 9.5, the sequences associated to the t1.c1 and t1.c3 columns are not dumped," echo " while the sequence associated to t2.c1 is dumped." echo "=> with 9.6beta3 (as with beta2), the 3 sequences are dumped." echo " " echo "cleanup" echo "---" $PGBIN/psql <<*END* DROP EXTENSION IF EXISTS myextension; DROP SCHEMA IF EXISTS myextension CASCADE; *END* sudo rm $PGDIR/myextension.control rm $EXTDIR/myextension* And its output result: ## psql: prepare the initial environment: 1 schema + 2 tables with 1 serial column in each --- select version(); version - PostgreSQL 9.6beta3 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 32-bit (1 row) -- cleanup DROP EXTENSION IF EXISTS myextension; NOTICE: extension "myextension" does not exist, skipping DROP EXTENSION DROP SCHEMA IF EXISTS myextension CASCADE; NOTICE: schema "myextension" does not exist, skipping DROP SCHEMA -- create CREATE SCHEMA myextension; CREATE SCHEMA CREATE TABLE myextension.t1 (c1 SERIAL); CREATE TABLE CREATE TABLE myextension.t2 (c1 SERIAL); CREATE TABLE create first files for extension management --- psql: create the extension from unpackaged -- -- create CREATE EXTENSION myextension FROM unpackaged; CREATE EXTENSION -- check \dx List of installed extensions Name | Version | Schema| Description -+-+-+-- myextension | 1 | myextension | test plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 r
