Re: pg_extension_config_dump() with a sequence

[Moshe Jacobson <moshe@xxxxxxxxxxxx>]

On Tue, Aug 20, 2013 at 7:58 PM, Tom Lane <tgl@xxxxxxxxxxxxx> 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@xxxxxxxxxxxx | www.neadwerx.com

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