Lists: | pgsql-bugs |
---|
From: | "Gabriele Bartolini" <g(dot)bartolini(at)comune(dot)prato(dot)it> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #3224: Dump: missing schema name for sequence in a "DEFAULT nextval" specification |
Date: | 2007-04-13 10:22:04 |
Message-ID: | 200704131022.l3DAM4Lv050864@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 3224
Logged by: Gabriele Bartolini
Email address: g(dot)bartolini(at)comune(dot)prato(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Gabriele Bartolini" <g(dot)bartolini(at)comune(dot)prato(dot)it> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #3224: Dump: missing schema name for sequence in a "DEFAULT nextval" specification |
Date: | 2007-04-13 15:15:45 |
Message-ID: | 19419.1176477345@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Gabriele Bartolini" <g(dot)bartolini(at)comune(dot)prato(dot)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
From: | "Gabriele Bartolini" <g(dot)bartolini(at)comune(dot)prato(dot)it> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | R: BUG #3224: Dump: missing schema name for sequence in a "DEFAULT nextval" specification |
Date: | 2007-04-13 23:19:11 |
Message-ID: | 910CF843580B3C40A25CD0D04B3908E209C589@exchange4.comune.prato.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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 [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Inviato: ven 13/04/2007 17.15
A: Gabriele Bartolini
Cc: pgsql-bugs(at)postgresql(dot)org
Oggetto: Re: [BUGS] BUG #3224: Dump: missing schema name for sequence in a "DEFAULT nextval" specification
"Gabriele Bartolini" <g(dot)bartolini(at)comune(dot)prato(dot)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
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Gabriele Bartolini <g(dot)bartolini(at)comune(dot)prato(dot)it> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: R: BUG #3224: Dump: missing schema name for sequence in a "DEFAULT nextval" specification |
Date: | 2007-04-15 04:27:35 |
Message-ID: | 20070415042735.GD4537@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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