Hi all,

I'm using Postgres 8.2.4 with pgAdmin 1.6.3 (but this occur with 1.8.0
beta 1 too)

When we create something like

CREATE SCHEMA example;

CREATE TABLE example.teste
(
id serial NOT NULL ,
dsitem character varying(30),
CONSTRAINT pk_teste PRIMARY KEY (id)
);

All works fine, but if we do a backup using pg_dump and restore it,
pgAdmin doesn't recognize id as a serial anymore, presenting now the
table definition bellow

CREATE TABLE example.teste
(
id integer NOT NULL DEFAULT nextval('teste_id_seq'::regclass),
dsitem character varying(30),
CONSTRAINT pk_teste PRIMARY KEY (id)
);

This isn't a big problem, but make the things confusing, since at first
impression, we can think that the sequence teste_id_seq
wasn't dependent of column teste.id. But if we drop the table, the
sequence is dropped too (as we expect in a serial column).

I research pg_dump script and see that pg_dump recreate the table with
the commands bellow

CREATE SCHEMA example;

SET search_path = example, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;

CREATE TABLE teste (
id integer NOT NULL,
dsitem character varying(30)
);

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

ALTER SEQUENCE teste_id_seq OWNED BY teste.id;
ALTER TABLE teste ALTER COLUMN id SET DEFAULT
nextval('teste_id_seq'::regclass);

ALTER TABLE ONLY teste
ADD CONSTRAINT pk_teste PRIMARY KEY (id);


Well, I found in pgColumn.cpp that default string expected is
"nextval('example.teste_id_seq'::regclass)", but pg_dump set this value
to "nextval('teste_id_seq'::regclass)".
If we change the default value of column to
"nextval('example.teste_id_seq'::regclass)", then all work's fine again.

In pg_dump, the adstr column that contains the default value for the
column is retrieved using the function
pg_catalog.pg_get_expr(adbin,adrelid), that will return the string
"nextval('example.teste_id_seq'::regclass)",
But ONLY IF the schema ISN'T in the search_path. I suppose that
pgadmin don't alter the search_path, so this can resolve the problem for
a while.
The atacched diff file modify this (reference is the source of 1.6.3),
but I don't have sufficient skill to compile the source and I'm not a
C/C++ programmer to do a better change.

I think that a better test to serial columns, may be to verify primarily
if there are a dependence between the column and the sequence, and then
verify if the default value of the column is a nextval of the this sequence.

PS.: With the schema "public" all works fine

--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.



*** pgColumn.cpp 2007-03-23 13:11:43.000000000 -0300
--- pgColumn2.cpp 2007-07-25 15:33:39.000000000 -0300
***************
*** 270,276 ****
systemRestriction = wxT("\n AND attnum > 0");

wxString sql=
! wxT("SELECT att.*, def.*, CASE WHEN attndims > 0 THEN 1 ELSE 0 END AS isarray, format_type(ty.oid,NULL) AS typname, tn.nspname as typnspname, et.typname as elemtypname,\n")
wxT(" cl.relname, na.nspname, att.attstattarget, description, cs.relname AS sername, ns.nspname AS serschema,\n")
wxT(" (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup, indkey");

--- 270,276 ----
systemRestriction = wxT("\n AND attnum > 0");

wxString sql=
! wxT("SELECT att.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS adsrc, CASE WHEN attndims > 0 THEN 1 ELSE 0 END AS isarray, format_type(ty.oid,NULL) AS typname, tn.nspname as typnspname, et.typname as elemtypname,\n")
wxT(" cl.relname, na.nspname, att.attstattarget, description, cs.relname AS sername, ns.nspname AS serschema,\n")
wxT(" (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup, indkey");



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Luiz K. Matsumura wrote:
> The atacched diff file modify this (reference is the source of 1.6.3),
> but I don't have sufficient skill to compile the source and I'm not a
> C/C++ programmer to do a better change.

Thanks - I've applied a fix along these lines. It'll be in beta 3.

> I think that a better test to serial columns, may be to verify primarily
> if there are a dependence between the column and the sequence, and then
> verify if the default value of the column is a nextval of the this
> sequence.

Yeah, that's been on the todo list for a while but noone got round to it
yet:

- detect serial columns from pg_depend, not column default (observe
deviating serial name)

Thanks, Dave

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate