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 rows)
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';
classid | relname | objid | relname | relkind | refclassid |
relname | refobjid
---------+----------+-------+-----------+---------+------------+--------------+----------
1259 | pg_class | 32216 | t1 | r | 3079 | pg_extension
| 32226
1259 | pg_class | 32214 | t1_c1_seq | S | 3079 | pg_extension
| 32226
1259 | pg_class | 32222 | t2 | r | 3079 | pg_extension
| 32226
1259 | pg_class | 32227 | t3_c1_seq | S | 3079 | pg_extension
| 32226
1259 | pg_class | 32229 | t3 | r | 3079 | pg_extension
| 32226
(5 rows)
So we now have 3 tables having a serial column:
- t1 explicitely added to the extension, with its sequence
- t2 explicitely added to the extension, but without its sequence
- t3 directly created inside the extensione
sequences dumped by pg_dump (pg_dump |grep 'CREATE SEQUENCE')
---------------------------
CREATE SEQUENCE t1_c1_seq
CREATE SEQUENCE t2_c1_seq
CREATE SEQUENCE t3_c1_seq
=> 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.
cleanup
-------
DROP EXTENSION
DROP SCHEMA
I will be off during the 2 coming weeks. So I will only see any reply to
this thread ... soon.
Best regards.
Philippe Beaudoin.
On Fri, Jul 22, 2016 at 6:27 PM, Philippe BEAUDOIN <phb.emaj@free.fr> 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
On Sat, Jul 23, 2016 at 01:40:01PM +0900, Michael Paquier wrote:
On Fri, Jul 22, 2016 at 6:27 PM, Philippe BEAUDOIN <phb.emaj@free.fr> 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]/messages/by-id/20160527025039.GA447393@tornado.leadboat.com 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]: /messages/by-id/20160527025039.GA447393@tornado.leadboat.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jul 26, 2016 at 4:50 PM, Noah Misch <noah@leadboat.com> 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] /messages/by-id/20160527025039.GA447393@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
Michael,
* Michael Paquier (michael.paquier@gmail.com) wrote:
On Tue, Jul 26, 2016 at 4:50 PM, Noah Misch <noah@leadboat.com> 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] /messages/by-id/20160527025039.GA447393@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
On Wed, Jul 27, 2016 at 8:07 AM, Stephen Frost <sfrost@snowman.net> 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
Attachments:
pgdump-extension-seq.patchapplication/x-patch; name=pgdump-extension-seq.patchDownload
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;
On Wed, Jul 27, 2016 at 2:24 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Wed, Jul 27, 2016 at 8:07 AM, Stephen Frost <sfrost@snowman.net> 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.
Stephen, is this still on your list of things for today? Please
provide a status update.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Michael,
(dropping -general, not sure why that list was included...)
* Michael Paquier (michael.paquier@gmail.com) wrote:
On Wed, Jul 27, 2016 at 8:07 AM, Stephen Frost <sfrost@snowman.net> 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.
Right, it should be set to the same value as the table which is being
dumped rather than DUMP_COMPONENT_ALL. Moreover, the
owning_tab->dobj.dump check should explicitly check against
DUMP_COMPONENT_NONE, but that's just to be neat since that's '0'.
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.
This isn't correct as the dump components which are independent of the
extension (ACLs, security labels, policies) won't be dumped.
Consider, for example, what happens if the user runs:
GRANT USAGE ON SEQUENCE <extension_sequence> TO role;
This wouldn't get dumped out with your patch, since we would decide that,
because the sequence is a member of the extension, that nothing about it
should be dumped, which isn't correct.
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.
If the sequence is dropped from the extension then the sequence will be
ignored by checkExtensionMembership() during selectDumpableObject() and
the regular selectDumpableObject() code will handle marking the sequence
appropriately.
What we need to be doing here is combining the set of components that
the sequence has been marked with and the set of components that the
table has been marked with, not trying to figure out if the sequence is
a member of an extension or not and changing what to do in those cases,
that's checkExtensionMembership()'s job, really.
Attached is a patch implementing this and which passes the regression
tests you added and a couple that I added for the non-extension case.
I'm going to look at adding a few more regression tests and if I don't
come across any issues then I'll likely push the fix sometime tomorrow.
Comments welcome, of course.
Thanks!
Stephen
Attachments:
fix_pg_dump_ext_seq_v1.patchtext/x-diff; charset=us-asciiDownload
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
new file mode 100644
index 08c2b0c..333e628
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** getOwnedSeqs(Archive *fout, TableInfo tb
*** 6035,6048 ****
if (!OidIsValid(seqinfo->owning_tab))
continue; /* not an owned sequence */
! if (seqinfo->dobj.dump & DUMP_COMPONENT_DEFINITION)
! continue; /* no need to search */
owning_tab = findTableByOid(seqinfo->owning_tab);
! if (owning_tab && owning_tab->dobj.dump)
! {
seqinfo->interesting = true;
- seqinfo->dobj.dump = DUMP_COMPONENT_ALL;
- }
}
}
--- 6035,6061 ----
if (!OidIsValid(seqinfo->owning_tab))
continue; /* not an owned sequence */
!
owning_tab = findTableByOid(seqinfo->owning_tab);
!
! /*
! * We need to dump the components that are being dumped for the table
! * and any components which the sequence is explicitly marked with.
! *
! * We can't simply use the set of components which are being dumped for
! * the table as the table might be in an extension (and only the
! * non-extension components, eg: ACLs if changed, security labels, and
! * policies, are being dumped) while the sequence is not (and therefore
! * the definition and other components should also be dumped).
! *
! * If the sequence is part of the extension then it should be properly
! * marked by checkExtensionMembership() and this will be a no-op as the
! * table will be equivilantly marked.
! */
! seqinfo->dobj.dump = seqinfo->dobj.dump | owning_tab->dobj.dump;
!
! if (seqinfo->dobj.dump != DUMP_COMPONENT_NONE)
seqinfo->interesting = true;
}
}
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
new file mode 100644
index 1d82bfd..5a042b8
*** a/src/bin/pg_dump/t/002_pg_dump.pl
--- b/src/bin/pg_dump/t/002_pg_dump.pl
*************** my %tests = (
*** 361,366 ****
--- 361,416 ----
only_dump_test_schema => 1,
only_dump_test_table => 1,
test_schema_plus_blobs => 1, }, },
+ 'ALTER SEQUENCE test_table_col1_seq' => {
+ regexp => qr/^
+ \QALTER SEQUENCE test_table_col1_seq OWNED BY test_table.col1;\E
+ /xm,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_dbprivs => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1, },
+ unlike => {
+ exclude_test_table => 1,
+ exclude_dump_test_schema => 1,
+ pg_dumpall_globals => 1,
+ pg_dumpall_globals_clean => 1,
+ section_post_data => 1, }, },
+ 'ALTER SEQUENCE test_third_table_col1_seq' => {
+ regexp => qr/^
+ \QALTER SEQUENCE test_third_table_col1_seq OWNED BY test_third_table.col1;\E
+ /xm,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ pg_dumpall_dbprivs => 1,
+ schema_only => 1,
+ section_pre_data => 1, },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ pg_dumpall_globals_clean => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1, }, },
'ALTER TABLE ONLY test_table ADD CONSTRAINT ... PRIMARY KEY' => {
regexp => qr/^
\QALTER TABLE ONLY test_table\E \n^\s+
diff --git a/src/test/modules/test_pg_dump/t/001_base.pl b/src/test/modules/test_pg_dump/t/001_base.pl
new file mode 100644
index fd9c37f..fc93e1b
*** a/src/test/modules/test_pg_dump/t/001_base.pl
--- b/src/test/modules/test_pg_dump/t/001_base.pl
*************** my %tests = (
*** 223,232 ****
schema_only => 1,
section_pre_data => 1,
section_post_data => 1, }, },
'CREATE TABLE regress_pg_dump_table' => {
regexp => qr/^
\QCREATE TABLE regress_pg_dump_table (\E
! \n\s+\Qcol1 integer,\E
\n\s+\Qcol2 integer\E
\n\);$/xm,
like => { binary_upgrade => 1, },
--- 223,274 ----
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 TABLE regress_pg_dump_table' => {
regexp => qr/^
\QCREATE TABLE regress_pg_dump_table (\E
! \n\s+\Qcol1 integer NOT NULL,\E
\n\s+\Qcol2 integer\E
\n\);$/xm,
like => { binary_upgrade => 1, },
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
new file mode 100644
index 5fe6063..93de2c5
*** 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,13 ****
\echo Use "CREATE EXTENSION test_pg_dump" to load this file. \quit
CREATE TABLE regress_pg_dump_table (
! col1 int,
col2 int
);
GRANT SELECT ON regress_pg_dump_table TO regress_dump_test_role;
GRANT SELECT(col1) ON regress_pg_dump_table TO public;
--- 4,15 ----
\echo Use "CREATE EXTENSION test_pg_dump" to load this file. \quit
CREATE TABLE regress_pg_dump_table (
! 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;
On Sat, Jul 30, 2016 at 5:47 AM, Stephen Frost <sfrost@snowman.net> wrote:
What we need to be doing here is combining the set of components that
the sequence has been marked with and the set of components that the
table has been marked with, not trying to figure out if the sequence is
a member of an extension or not and changing what to do in those cases,
that's checkExtensionMembership()'s job, really.
OK, thanks for the confirmation. I have been playing a bit with your
patch and it is correctly dumping ACLs and policies that are modified
after creating an extension. So that looks good to me.
Attached is a patch implementing this and which passes the regression
tests you added and a couple that I added for the non-extension case.
I'm going to look at adding a few more regression tests and if I don't
come across any issues then I'll likely push the fix sometime tomorrow.
+ * table will be equivilantly marked.
s/equivilantly/equivalently/.
By the way, I noticed 3 places in dumpProcLang and 1 place in
dumpSequence where dobj.dump is used in a test but it is not directly
compared with DUMP_COMPONENT_NONE. Perhaps you'd want to change that
as well..
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Michael,
* Michael Paquier (michael.paquier@gmail.com) wrote:
On Sat, Jul 30, 2016 at 5:47 AM, Stephen Frost <sfrost@snowman.net> wrote:
What we need to be doing here is combining the set of components that
the sequence has been marked with and the set of components that the
table has been marked with, not trying to figure out if the sequence is
a member of an extension or not and changing what to do in those cases,
that's checkExtensionMembership()'s job, really.OK, thanks for the confirmation. I have been playing a bit with your
patch and it is correctly dumping ACLs and policies that are modified
after creating an extension. So that looks good to me.Attached is a patch implementing this and which passes the regression
tests you added and a couple that I added for the non-extension case.
I'm going to look at adding a few more regression tests and if I don't
come across any issues then I'll likely push the fix sometime tomorrow.+ * table will be equivilantly marked.
s/equivilantly/equivalently/.By the way, I noticed 3 places in dumpProcLang and 1 place in
dumpSequence where dobj.dump is used in a test but it is not directly
compared with DUMP_COMPONENT_NONE. Perhaps you'd want to change that
as well..
Thanks again for the help on this item, I've pushed the fix and updated
the open items wiki.
Stephen