Thread: pgdump of schema...

Hello,

When I do a "pgdump --schema=someschema somedatabase > something.dump",
the results of the dump file look like this:


REVOKE ALL ON SCHEMA someschema FROM PUBLIC;
GRANT ALL ON SCHEMA someschema TO PUBLIC;

SET search_path = someschema, pg_catalog;

CREATE SEQUENCE emailtemplate_email_templat_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


CREATE TABLE emailtemplates (
    email_template_id integer DEFAULT
nextval('"emailtemplate_email_templat_seq"'::text) NOT NULL,
    template_name character varying(16) NOT NULL,
    subject character varying(80) NOT NULL,
    plain_text text,
    html_block text,
    entered_dt timestamp with time zone,
    updated_dt timestamp with time zone
);


... This seems *extremely* problematic to me because unless explicitly
set search_path, it is not possible to insert data (in this case into
emailtemplates) because the sequence does not have the schema
specified....   Am I using schemas wrong?.. Should I be to do "INSERT
INTO someschema.emailtemplates" and expect it to work after restoring a
database in this fashion?...

I was really surprised to see this after having gone though extensive/
painstaking work to ensure that all of this was correct in my database
conversion to have pgdump essentially undo all of this -- yikes....  So I
am beginning to think that I must doing something wrong here....

- Greg

"Net Virtual Mailing Lists" <mailinglists@net-virtual.com> writes:
> When I do a "pgdump --schema=someschema somedatabase > something.dump",
> the results of the dump file look like this:

> CREATE TABLE emailtemplates (
>     email_template_id integer DEFAULT
> nextval('"emailtemplate_email_templat_seq"'::text) NOT NULL,

Hmm.  What you've apparently got here is a serial column that you've
carried forward from an old (pre 7.3 at least) database.  Had the serial
default been created in 7.3 or later then it would be a fully qualified
name (ie nextval('someschema.emailtemplate_email_templat_seq')) and
there would be no issue.  For that matter, had the SERIAL column been
created in 7.3 or later, pg_dump would know to say

    CREATE TABLE emailtemplates (
        email_template_id SERIAL,
        ...

instead of what it did say.  Now it is surely not pg_dump's charter
to editorialize on default expressions that were supplied by the user
(which this was, as far as the current database knows).  So this isn't a
pg_dump bug.  What it is is a deficiency in the upgrade process that we
had from pre-7.3 to 7.3 databases.  You might want to consider running
contrib/adddepend against your database to fix things up.  (But note
that it's just a contrib script and is not guaranteed; so keep a prior
dump around ...)

            regards, tom lane

Actually this database has been carried forward since the "postgres95"
days, so you are definitely right in your analysis..

Would another (perhaps safer?) way of doing this is to remove the "CREATE
SEQUENCE" and "SELECT pg_catalog.setval", and replace the "DEFAULT
nextval" with "SERIAL" then restore that?...

Thanks as always!

- Greg



>Hmm.  What you've apparently got here is a serial column that you've
>carried forward from an old (pre 7.3 at least) database.  Had the serial
>default been created in 7.3 or later then it would be a fully qualified
>name (ie nextval('someschema.emailtemplate_email_templat_seq')) and
>there would be no issue.  For that matter, had the SERIAL column been
>created in 7.3 or later, pg_dump would know to say
>
>    CREATE TABLE emailtemplates (
>        email_template_id SERIAL,
>        ...
>
>instead of what it did say.  Now it is surely not pg_dump's charter
>to editorialize on default expressions that were supplied by the user
>(which this was, as far as the current database knows).  So this isn't a
>pg_dump bug.  What it is is a deficiency in the upgrade process that we
>had from pre-7.3 to 7.3 databases.  You might want to consider running
>contrib/adddepend against your database to fix things up.  (But note
>that it's just a contrib script and is not guaranteed; so keep a prior
>dump around ...)