Re: 'default nextval()' loses schema-qualification in dump ? - Mailing list pgsql-general

Le 6/07/2010 17:17, Tom Lane a écrit :
> Arnaud Lesauvage<arnaud.listes@codata.eu>  writes:
>>  As you have understood, I am not very savvy about postgresql's
>>  internals, but from what you say my guess is that the problem is int the
>>  psqlODBC is getting the default value of the sequence ?
>
> I have no idea, because you haven't showed us what's happening, only
> your oversimplified description of what's happening.  We really need to
> see the exact SQL used to define the table (copy that from your dump,
> perhaps) as well as the exact SQL used in the misbehaving insert
> commands.

OK, here's the SQL.
First the creation of the table :

CREATE TABLE myschema.mytable
(
   gid serial NOT NULL,
   data character varying(255),
   CONSTRAINT pkey_mytable PRIMARY KEY (gid)
);


Then the dump :

SET statement_timeout = 0;
SET client_encoding = 'LATIN9';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = myschema, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE mytable (
     gid integer NOT NULL,
     data character varying(255)
);
ALTER TABLE myschema.mytable OWNER TO postgres;
CREATE SEQUENCE mytable_gid_seq
     START WITH 1
     INCREMENT BY 1
     NO MAXVALUE
     NO MINVALUE
     CACHE 1;
ALTER TABLE myschema.mytable_gid_seq OWNER TO postgres;
ALTER SEQUENCE mytable_gid_seq OWNED BY mytable.gid;
ALTER TABLE mytable ALTER COLUMN gid SET DEFAULT
nextval('mytable_gid_seq'::regclass);
ALTER TABLE ONLY mytable
     ADD CONSTRAINT pkey_mytable PRIMARY KEY (gid);


The tables are linked via PsqlODBC with the following options :
TrueIsMinus1=1
BoolsAsChar=0
TextAsLongVarchar=1
Protocol=7.4-1
AB=0x2
Rowversionning=1
CommLog=1

The PsqlODBC log for the insert before the dump/restore (i.e. the good
one) :

[0.063]conn=095C4198, query='SELECT "myschema"."mytable"."gid" FROM
"myschema"."mytable" '
[0.063]    [ fetched 0 rows ]
[9.125]conn=095C4198, query='INSERT INTO  "myschema"."mytable"  ("data")
VALUES (E'somedata')'
[9.125]conn=095C4198, query='select n.nspname, c.relname, a.attname,
a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull,
c.relhasrules, c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then
t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c
inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and
c.relname = E'mytable' and n.nspname = E'myschema') inner join
pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and
a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid =
a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid =
a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum'
[9.141]    [ fetched 2 rows ]
[9.141]PGAPI_Columns: table='mytable',field_name='gid',type=23,name='int4'
[9.141]PGAPI_Columns:
table='mytable',field_name='data',type=1043,name='varchar'
[9.141]conn=095C4198, query='SELECT
currval('myschema.mytable_gid_seq'::regclass)'
[9.141]    [ fetched 1 rows ]
[9.141]conn=095C4198, query='COMMIT'
[9.141]conn=095C4198, query='SELECT "gid","data"  FROM
"myschema"."mytable"  WHERE "gid" = 1'
[9.141]    [ fetched 1 rows ]



The PsqlODBC log for the insert after the dump/restore (i.e. the bad one) :

[11.328]conn=09FC0048, query='SELECT "myschema"."mytable"."gid" FROM
"myschema"."mytable" '
[11.328]    [ fetched 0 rows ]
[15.438]conn=09FC0048, query='INSERT INTO  "myschema"."mytable"
("data") VALUES (E'somedata')'
[15.438]conn=09FC0048, query='select n.nspname, c.relname, a.attname,
a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull,
c.relhasrules, c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then
t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c
inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and
c.relname = E'mytable' and n.nspname = E'myschema') inner join
pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and
a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid =
a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid =
a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum'
[15.453]    [ fetched 2 rows ]
[15.453]PGAPI_Columns: table='mytable',field_name='gid',type=23,name='int4'
[15.453]PGAPI_Columns:
table='mytable',field_name='data',type=1043,name='varchar'
[15.453]conn=09FC0048, query='SELECT currval('mytable_gid_seq'::regclass)'
[15.453]ERROR from backend during send_query: 'SERREUR'
[15.453]ERROR from backend during send_query: 'C42P01'
[15.453]ERROR from backend during send_query: 'Mla relation «
mytable_gid_seq » n'existe pas'
[15.453]ERROR from backend during send_query: 'P16'
[15.453]ERROR from backend during send_query:
'F.\src\backend\catalog\namespace.c'
[15.453]ERROR from backend during send_query: 'L276'
[15.453]ERROR from backend during send_query: 'RRangeVarGetRelid'
[15.453]STATEMENT ERROR: func=SC_execute, desc='(null)', errnum=7,
errmsg='Error while executing the query'
[15.453]
------------------------------------------------------------
[15.453]                 hdbc=09FC0048, stmt=095CB3E0, result=095C94F0
[15.453]                 prepare=2, internal=0
[15.469]                 bindings=00000000, bindings_allocated=0
[15.469]                 parameters=00000000, parameters_allocated=0
[15.469]                 statement_type=0, statement='SELECT @@IDENTITY'
[15.469]                 stmt_with_params='SELECT
currval('mytable_gid_seq'::regclass)'
[15.469]                 data_at_exec=-1, current_exec_param=-1, put_data=0
[15.469]                 currTuple=-1, current_col=-1, lobj_fd=-1
[15.469]                 maxRows=0, rowset_size=1, keyset_size=0,
cursor_type=0, scroll_concurrency=1
[15.469]                 cursor_name='SQL_CUR095CB3E0'
[15.469]                 ----------------QResult Info
-------------------------------
[15.469]                 fields=09FC2F58, backend_tuples=00000000,
tupleField=0, conn=00000000
[15.469]                 fetch_count=0, num_total_rows=0, num_fields=0,
cursor='(NULL)'
[15.469]                 message='ERREUR: la relation « mytable_gid_seq
» n'existe pas', command='(NULL)', notice='(NULL)'
[15.469]                 status=7, inTuples=0
[15.469]CONN ERROR: func=SC_execute, desc='(null)', errnum=110,
errmsg='ERREUR: la relation « mytable_gid_seq » n'existe pas'
[15.469]
------------------------------------------------------------
[15.469]            henv=095C2138, conn=09FC0048, status=1, num_stmts=16
[15.469]            sock=09FC3540, stmts=09FC3030, lobj_type=-999
[15.469]            ---------------- Socket Info
-------------------------------
[15.469]            socket=172, reverse=0, errornumber=0, errormsg='(NULL)'
[15.469]            buffer_in=157064440, buffer_out=157072160
[15.485]            buffer_filled_in=6, buffer_filled_out=0,
buffer_read_in=6
[15.485]conn=09FC0048, query='ROLLBACK'


Is this enough ? The log was quite big so I removed the parts I thought
were not useful, but if you need more information from the log (or from
elsewhere), just says so.

Thanks !
Regards
Arnaud Lesauvage