From: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs <pgsql-bugs(at)postgresql(dot)org> |
Subject: | pg_dump 9.6 doesn't honour pg_extension_config_dump for sequences |
Date: | 2017-01-18 12:59:10 |
Message-ID: | CA+mi_8ZmxQM7+nZ7pJ8uyfxc9V3o=UAG14dVqvftdmvw8OJ3gQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
pg_extension_config_dump() doesn't appear working on sequences; the
resulting dump doesn't contain the state of the sequence. Tested on pg
9.6.1 on Ubuntu. This seems a regression from pg 9.5.
In order to reproduce: create an extension:
piro(at)risotto:~$ cat /usr/share/postgresql/9.6/extension/testseq.control
# testseq extension
comment = 'test extension for sequence dump'
default_version = '1.0'
module_pathname = '$libdir/testseq'
relocatable = true
piro(at)risotto:~$ cat /usr/share/postgresql/9.6/extension/testseq--1.0.sql
create table testseq (id serial primary key, data text);
SELECT pg_catalog.pg_extension_config_dump('testseq', '');
SELECT pg_catalog.pg_extension_config_dump('testseq_id_seq', '');
Create the extension in a database and populate some rows:
piro(at)risotto:~$ sudo -u postgres psql -p 54396
psql (9.6.1)
Type "help" for help.
postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create schema testseq;
CREATE SCHEMA
test=# create extension testseq with schema testseq;
CREATE EXTENSION
test=# insert into testseq.testseq (data) values ('foo');
INSERT 0 1
Create a second database and copy the data:
piro(at)risotto:~$ sudo -u postgres psql -p 54396 -c "create database test2"
CREATE DATABASE
piro(at)risotto:~$ sudo -u postgres /usr/lib/postgresql/9.6/bin/pg_dump
-p 54396 test | sudo -u postgres /usr/lib/postgresql/9.6/bin/psql -p
54396 test2
SET
SET
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
SET
COPY 1
The table data was copied but the sequence is in an inconsistent state:
piro(at)risotto:~$ sudo -u postgres psql -p 54396 -c "insert into
testseq.testseq (data) values ('bar');" test2
ERROR: duplicate key value violates unique constraint "testseq_pkey"
DETAIL: Key (id)=(1) already exists.
-- Daniele
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2017-01-18 14:08:46 | Re: BUG #14263: Query planner is slow to plan UPDATE on a table with many partitions |
Previous Message | Joel Sanger | 2017-01-18 09:58:06 | BUG #14263: Query planner is slow to plan UPDATE on a table with many partitions |