Le 6/07/2010 17:17, Tom Lane a écrit :
Arnaud Lesauvage<arnaud.listes@xxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
