Обсуждение: 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
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] 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.GA447393@tornado.leadboat.com
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] http://www.postgresql.org/message-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
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] http://www.postgresql.org/message-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
Вложения
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