FAQ
The following bug has been logged online:

Bug reference: 3224
Logged by: Gabriele Bartolini
Email address: g.bartolini@comune.prato.it
PostgreSQL version: 8.2.3
Operating system: GNU/Linux ( 2.6.9-42.0.2.ELsmp )
Description: Dump: missing schema name for sequence in a "DEFAULT
nextval" specification
Details:

Here is the example. I have a schema called 'dimensions' where I have a
hosts_2006_seq sequence and a hosts_2006 table, defined as follows:

CREATE SCHEMA dimensions;
CREATE SEQUENCE dimensions.hosts_2006_seq;
CREATE TABLE dimensions.hosts_2006
(
id_host integer NOT NULL DEFAULT
nextval('dimensions.hosts_2006_seq'::regclass),
host character varying(255) NOT NULL DEFAULT ''::character varying,
CONSTRAINT hosts_2006_pkey PRIMARY KEY (id_host)
);

As you can see I want the 'nextval' function to get the sequence value from
the proper table in the 'dimensions' schema, and that works fine.

However, when I issue a pg_dump or pg_dumpall command, here is what I get:

SET search_path = dimensions, pg_catalog;
CREATE SEQUENCE hosts_2006_seq;
CREATE TABLE hosts_2006 (
id_host integer DEFAULT nextval('hosts_2006_seq'::regclass) NOT NULL,
host character varying(255) DEFAULT ''::character varying NOT NULL,
);

This causes the 'nextval()' to go and look for the proper sequence according
to the search path and not in an absolute way.

I don't know whether this can be considered a bug, but it definitely created
a few problems on my scenario.

Thank you.

Ciao,
Gabriele

Search Discussions

  • Tom Lane at Apr 13, 2007 at 3:16 pm

    "Gabriele Bartolini" <g.bartolini@comune.prato.it> writes:
    However, when I issue a pg_dump or pg_dumpall command, here is what I get:
    SET search_path = dimensions, pg_catalog;
    CREATE SEQUENCE hosts_2006_seq;
    CREATE TABLE hosts_2006 (
    id_host integer DEFAULT nextval('hosts_2006_seq'::regclass) NOT NULL,
    host character varying(255) DEFAULT ''::character varying NOT NULL,
    );
    This is not a bug: the regclass constant will be recreated the same as
    it was before.

    regards, tom lane
  • Gabriele Bartolini at Apr 13, 2007 at 11:23 pm
    Hi Tom,

    thanks for the answer. However, I still cannot understand why the 'dimensions' schema has disappeared from the dump of the sequence. I will do further investigation and let you know.

    Ciao,
    Gabriele

    P.S.: Would you think of coming to the Italian PostgreSQL Day in July? I am one of the main organisers of the event.

    -----Messaggio originale-----
    Da: Tom Lane
    Inviato: ven 13/04/2007 17.15
    A: Gabriele Bartolini
    Cc: pgsql-bugs@postgresql.org
    Oggetto: Re: [BUGS] BUG #3224: Dump: missing schema name for sequence in a "DEFAULT nextval" specification

    "Gabriele Bartolini" <g.bartolini@comune.prato.it> writes:
    However, when I issue a pg_dump or pg_dumpall command, here is what I get:
    SET search_path = dimensions, pg_catalog;
    CREATE SEQUENCE hosts_2006_seq;
    CREATE TABLE hosts_2006 (
    id_host integer DEFAULT nextval('hosts_2006_seq'::regclass) NOT NULL,
    host character varying(255) DEFAULT ''::character varying NOT NULL,
    );
    This is not a bug: the regclass constant will be recreated the same as
    it was before.

    regards, tom lane
  • Alvaro Herrera at Apr 15, 2007 at 4:27 am

    Gabriele Bartolini wrote:
    Hi Tom,

    thanks for the answer. However, I still cannot understand why the
    'dimensions' schema has disappeared from the dump of the sequence.
    I will do further investigation and let you know.
    It disappeared because it's not necessary. The search_path setting just
    above makes sure that it is restored correctly.


    --
    Alvaro Herrera http://www.CommandPrompt.com/
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedApr 13, '07 at 10:22a
activeApr 15, '07 at 4:27a
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase