pg_extension_config_dump() with a sequence

Moshe Jacobson <moshe@neadwerx.com> writes:

In searching for a solution I found an old message where Tom suggested
marking the sequence as a config table in the same way. This seems to work
OK when I use pg_dump, but for some reason, pg_dumpall generates the
following error:

It's pretty hard to believe that that would work in pg_dump but not
pg_dumpall. You sure the error references a database that you fixed the
sequence definition in? (You'd probably have to drop and recreate the
extension to fix it in an existing database.) If so, what PG version are
we talking about exactly, and what's the whole pg_dumpall command line?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Aug 20, 2013 at 4:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

It's pretty hard to believe that that would work in pg_dump but not
pg_dumpall. You sure the error references a database that you fixed the
sequence definition in? (You'd probably have to drop and recreate the
extension to fix it in an existing database.) If so, what PG version are
we talking about exactly, and what's the whole pg_dumpall command line?

Thanks for the response, Tom.

You're right, pg_dump fails as well:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: cannot copy from sequence
"sq_pk_audit_data_type"
pg_dump: The command was: COPY auditlog.sq_pk_audit_data_type TO stdout;

I can pg_dump our prod database and pg_restore it onto our dev server with
zero errors, but once I try to pg_dump the dev copy again, that's when I
receive the errors above.

I didn't drop & recreate the extension to fix it on prod -- I just created
a new version of it and updated it.

Any ideas?

--
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com

"Quality is not an act, it is a habit." -- Aristotle

Moshe Jacobson <moshe@neadwerx.com> writes:

I can pg_dump our prod database and pg_restore it onto our dev server with
zero errors, but once I try to pg_dump the dev copy again, that's when I
receive the errors above.
I didn't drop & recreate the extension to fix it on prod -- I just created
a new version of it and updated it.

Well, I think you did it wrong, or else you're using a PG version that
predates some necessary fix, because it works for me. I made a simple
extension containing

CREATE TABLE mytable (data text, id serial primary key);
SELECT pg_catalog.pg_extension_config_dump('mytable', '');
SELECT pg_catalog.pg_extension_config_dump('mytable_id_seq', '');

and did

tseq=# create extension myext ;
CREATE EXTENSION
tseq=# \dx+ myext
Objects in extension "myext"
Object Description
-------------------------
sequence mytable_id_seq
table mytable
(2 rows)

tseq=# insert into mytable values ('foo');
INSERT 0 1
tseq=# insert into mytable values ('bar');
INSERT 0 1

and now pg_dump gives me

---------------

--
-- Name: myext; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS myext WITH SCHEMA public;

--
-- Name: EXTENSION myext; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION myext IS 'testing 1,2,3,4';

SET search_path = public, pg_catalog;

--
-- Data for Name: mytable; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY mytable (data, id) FROM stdin;
foo 1
bar 2
\.

--
-- Name: mytable_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('mytable_id_seq', 2, true);

---------------

which is what I'd expect.

To debug, you might try looking in pg_extension to see if the extconfig
entry for your extension includes the OID of the sequence. If not, you
messed up somehow in updating the extension. If so, you must need a
newer version of pg_dump (you did not answer the question what version
you're using).

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Aug 20, 2013 at 7:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Well, I think you did it wrong, or else you're using a PG version that
predates some necessary fix, because it works for me.

To debug, you might try looking in pg_extension to see if the extconfig
entry for your extension includes the OID of the sequence. If not, you
messed up somehow in updating the extension. If so, you must need a
newer version of pg_dump (you did not answer the question what version
you're using).

Sorry for the delayed response. I am using postgres 9.1.4 with pg_dump of
the same version.

I did basically the same thing as you, and it didn't work for me:

I created a simple extension myext as follows:

CREATE SEQUENCE sq_pk_myitem;
CREATE TABLE tb_myitem
(
myitem integer primary key default nextval('sq_pk_myitem'),
data text
);

SELECT pg_catalog.pg_extension_config_dump('tb_myitem', '');
SELECT pg_catalog.pg_extension_config_dump('sq_pk_myitem', '');

Then I created a database for it and installed it:

postgres@moshe=>devmain:postgres=# create database mydb;
CREATE DATABASE
postgres@moshe=>devmain:postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
postgres@moshe=>devmain:mydb=# create extension myext;
CREATE EXTENSION
postgres@moshe=>devmain:mydb=# \d tb_myitem
Table "public.tb_myitem"
Column | Type | Modifiers
--------+---------+----------------------------------------------------
myitem | integer | not null default nextval('sq_pk_myitem'::regclass)
data | text |
Indexes:
"tb_myitem_pkey" PRIMARY KEY, btree (myitem)
postgres@moshe=>devmain:mydb=# \dx+ myext
Objects in extension "myext"
Object Description
-----------------------
sequence sq_pk_myitem
table tb_myitem
(2 rows)

postgres@moshe=>devmain:mydb=# \q

Then I tried to pg_dump it:

(0)(0j)[jehsom@moshe ~]$ pg_dump -U postgres mydb
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

--
-- Name: hstore; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA public;

--
-- Name: EXTENSION hstore; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION hstore IS 'data type for storing sets of (key, value)
pairs';

--
-- Name: myext; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS myext WITH SCHEMA public;

--
-- Name: EXTENSION myext; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION myext IS 'my extension';

SET search_path = public, pg_catalog;

--
-- Data for Name: sq_pk_myitem; Type: TABLE DATA; Schema: public; Owner:
postgres
--

COPY sq_pk_myitem FROM stdin;
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: cannot copy from sequence
"sq_pk_myitem"
pg_dump: The command was: COPY public.sq_pk_myitem TO stdout;
(1)(0j)[jehsom@moshe ~]$

And I got the error here. I'm not sure why this happens because it doesn't
happen on another server here. Any help would be appreciated.

Thanks!

--
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com

"Quality is not an act, it is a habit." -- Aristotle

Moshe Jacobson <moshe@neadwerx.com> writes:

On Tue, Aug 20, 2013 at 7:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Well, I think you did it wrong, or else you're using a PG version that
predates some necessary fix, because it works for me.

Sorry for the delayed response. I am using postgres 9.1.4 with pg_dump of
the same version.

Ah. I think that you are missing this 9.1.7 fix:

commit 5110a96992e508b220a7a6ab303b0501c4237b4a
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Oct 26 12:12:53 2012 -0400

In pg_dump, dump SEQUENCE SET items in the data not pre-data section.

Represent a sequence's current value as a separate TableDataInfo dumpable
object, so that it can be dumped within the data section of the archive
rather than in pre-data. This fixes an undesirable inconsistency between
the meanings of "--data-only" and "--section=data", and also fixes dumping
of sequences that are marked as extension configuration tables, as per a
report from Marko Kreen back in July. The main cost is that we do one more
SQL query per sequence, but that's probably not very meaningful in most
databases.

Back-patch to 9.1, since it has the extension configuration issue even
though not the --section switch.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thank you very much, Tom. That was it. Our other server is running 9.1.9
and that's why it worked there.

On Tue, Aug 27, 2013 at 10:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Moshe Jacobson <moshe@neadwerx.com> writes:

On Tue, Aug 20, 2013 at 7:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Well, I think you did it wrong, or else you're using a PG version that
predates some necessary fix, because it works for me.

Sorry for the delayed response. I am using postgres 9.1.4 with pg_dump of
the same version.

Ah. I think that you are missing this 9.1.7 fix:

commit 5110a96992e508b220a7a6ab303b0501c4237b4a
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Oct 26 12:12:53 2012 -0400

In pg_dump, dump SEQUENCE SET items in the data not pre-data section.

Represent a sequence's current value as a separate TableDataInfo
dumpable
object, so that it can be dumped within the data section of the archive
rather than in pre-data. This fixes an undesirable inconsistency
between
the meanings of "--data-only" and "--section=data", and also fixes
dumping
of sequences that are marked as extension configuration tables, as per
a
report from Marko Kreen back in July. The main cost is that we do one
more
SQL query per sequence, but that's probably not very meaningful in most
databases.

Back-patch to 9.1, since it has the extension configuration issue even
though not the --section switch.

regards, tom lane

--
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com

"Quality is not an act, it is a habit." -- Aristotle