Problem with Serial Columns
От | Luiz K. Matsumura |
---|---|
Тема | Problem with Serial Columns |
Дата | |
Msg-id | 46A7B132.2080703@planit.com.br обсуждение исходный текст |
Ответы |
Re: Problem with Serial Columns
(Dave Page <dpage@postgresql.org>)
|
Список | pgadmin-support |
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 ELSE0 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");
В списке pgadmin-support по дате отправления:
Предыдущее
От: "Vishal Arora"Дата:
Сообщение: Re: Error installing PostgreSQL core, 8.2 (password short, not complex)