On Tue, Aug 20, 2013 at 7:58 PM, Tom Lane <t...@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
mo...@neadwerx.com | www.neadwerx.com
"Quality is not an act, it is a habit." -- Aristotle
