Обсуждение: 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

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

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