pg_dump 9.6 doesn't honour pg_extension_config_dump for sequences

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

Responses

Browse pgsql-bugs by date

  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