Re: pg_extension_config_dump() with a sequence

On Tue, Aug 20, 2013 at 7:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)moshe=>devmain:postgres=# create database mydb;
CREATE DATABASE
postgres(at)moshe=>devmain:postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
postgres(at)moshe=>devmain:mydb=# create extension myext;
CREATE EXTENSION
postgres(at)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(at)moshe=>devmain:mydb=# \dx+ myext
Objects in extension "myext"
Object Description
-----------------------
sequence sq_pk_myitem
table tb_myitem
(2 rows)

postgres(at)moshe=>devmain:mydb=# \q

Then I tried to pg_dump it:

(0)(0j)[jehsom(at)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(at)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(at)neadwerx(dot)com | www.neadwerx.com

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