"default nextval()" loses schema-qualification in dump ?

Hi lists !

We ran into a problem after restoring a database dump.
Postgesql version is 8.4.3 on Win32.

The tables are linked with psqlODBC (v8.03.0400) and have SERIAL primary
keys (that's why I cross-posted to psql-odbc).
Before the restore, insertion in MSAccess was fine. After the restore,
insertions failed with a 'currval() not set' error.

After some research, we found in psqlODBC's log that before the restore
psqlODBC was getting the sequence's nextval with a schema qualified
call, and after the restore the call was not schema qualified.
I checked in pg_attrdef before and after the dump/restore, and indeed
the "default nextval()" on this problematic table loses it's schema
qualification in the process.

Why this doesn't matter in psql, I don't know (the schema in question is
not in the search_path), but this does break psqlODBC's handling of
"auto numbering" columns.

I tried a simple dump of the table structure, and indeed the restore
sets the search_path first and then creates the table without schema
qualification, neither for the table nore for the sequence.

Is this by design ? How can I work around this ?
I am not sure this is really normal, since the restored database's
strucure is not matching perfectly the original one's.

Thanks a lot for your thoughts and help on this matter.

Regards,
Arnaud Lesauvage

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: [GENERAL] "default nextval()" loses schema-qualification in dump ?

Arnaud Lesauvage writes:
> After some research, we found in psqlODBC's log that before the restore
> psqlODBC was getting the sequence's nextval with a schema qualified
> call, and after the restore the call was not schema qualified.
> I checked in pg_attrdef before and after the dump/restore, and indeed
> the "default nextval()" on this problematic table loses it's schema
> qualification in the process.

This is a pretty inadequate description of your problem. Let's see the
exact SQL you are dealing with.

Note that if the argument of nextval is a plain regclass constant, like
nextval('seq'::regclass)
then the constant is in fact a reference to a specific sequence.
Whether it's displayed with a schema name depends on whether that
sequence is visible in your search_path.

regards, tom lane

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: [GENERAL] "default nextval()" loses schema-qualification in dump?

Le 6/07/2010 16:22, Tom Lane a =E9crit :
> Arnaud Lesauvage writes:
>> After some research, we found in psqlODBC's log that before the resto=
re
>> psqlODBC was getting the sequence's nextval with a schema qualified
>> call, and after the restore the call was not schema qualified.
>> I checked in pg_attrdef before and after the dump/restore, and indeed
>> the "default nextval()" on this problematic table loses it's schema
>> qualification in the process.
>
> This is a pretty inadequate description of your problem. Let's see the
> exact SQL you are dealing with.

What is the exact information you want me to give ?
Everything I checked came from my analysis of psqlODBC's log.

I saw that in the first case (before the restore) a schema-qualified=20
nextval() was issued, and after the restore it was not schema qualified=20
anymore.

I looked further up in the log to see where the sequence name came from,=20
and it seemed that it came from pg_attrdef.adsrc.
I checked the value of this field in both databases, and it was different=
..

Maybe psqlODBC does the wrong thing when taking the sequence name from=20
this field, but my guess was that the problem came from here.

> Note that if the argument of nextval is a plain regclass constant, like
> nextval('seq'::regclass)
> then the constant is in fact a reference to a specific sequence.
> Whether it's displayed with a schema name depends on whether that
> sequence is visible in your search_path.

Displayed in pg_attrdef.adsrc ? It is not in the search_path, and it is=20
schema qualified before the dump/restore and not after.

As you have understood, I am not very savvy about postgresql's=20
internals, but from what you say my guess is that the problem is int the=20
psqlODBC is getting the default value of the sequence ?


Regards,
Arnaud Lesauvage

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: [GENERAL] "default nextval()" loses schema-qualification in dump ?

Arnaud Lesauvage 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.

regards, tom lane

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: [GENERAL] "default nextval()" loses schema-qualification in dump?

Le 6/07/2010 17:17, Tom Lane a =E9crit :
> Arnaud Lesauvage 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 =3D 0;
SET client_encoding =3D 'LATIN9';
SET standard_conforming_strings =3D off;
SET check_function_bodies =3D false;
SET client_min_messages =3D warning;
SET escape_string_warning =3D off;
SET search_path =3D myschema, pg_catalog;
SET default_tablespace =3D '';
SET default_with_oids =3D 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=20
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=3D1
BoolsAsChar=3D0
TextAsLongVarchar=3D1
Protocol=3D7.4-1
AB=3D0x2
Rowversionning=3D1
CommLog=3D1

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

[0.063]conn=3D095C4198, query=3D'SELECT "myschema"."mytable"."gid" FROM=20
"myschema"."mytable" '
[0.063] [ fetched 0 rows ]
[9.125]conn=3D095C4198, query=3D'INSERT INTO "myschema"."mytable" ("dat=
a")=20
VALUES (E'somedata')'
[9.125]conn=3D095C4198, query=3D'select n.nspname, c.relname, a.attname,=20
a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull,=20
c.relhasrules, c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then=20
t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c=20
inner join pg_catalog.pg_namespace n on n.oid =3D c.relnamespace and=20
c.relname =3D E'mytable' and n.nspname =3D E'myschema') inner join=20
pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and=20
a.attrelid =3D 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 =3D=
=20
a.attrelid and d.adnum =3D a.attnum order by n.nspname, c.relname, attnum=
'
[9.141] [ fetched 2 rows ]
[9.141]PGAPI_Columns: table=3D'mytable',field_name=3D'gid',type=3D23,name=
=3D'int4'
[9.141]PGAPI_Columns:=20
table=3D'mytable',field_name=3D'data',type=3D1043,name=3D'va rchar'
[9.141]conn=3D095C4198, query=3D'SELECT=20
currval('myschema.mytable_gid_seq'::regclass)'
[9.141] [ fetched 1 rows ]
[9.141]conn=3D095C4198, query=3D'COMMIT'
[9.141]conn=3D095C4198, query=3D'SELECT "gid","data" FROM=20
"myschema"."mytable" WHERE "gid" =3D 1'
[9.141] [ fetched 1 rows ]



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

[11.328]conn=3D09FC0048, query=3D'SELECT "myschema"."mytable"."gid" FROM=20
"myschema"."mytable" '
[11.328] [ fetched 0 rows ]
[15.438]conn=3D09FC0048, query=3D'INSERT INTO "myschema"."mytable"=20
("data") VALUES (E'somedata')'
[15.438]conn=3D09FC0048, query=3D'select n.nspname, c.relname, a.attname,=
=20
a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull,=20
c.relhasrules, c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then=20
t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c=20
inner join pg_catalog.pg_namespace n on n.oid =3D c.relnamespace and=20
c.relname =3D E'mytable' and n.nspname =3D E'myschema') inner join=20
pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and=20
a.attrelid =3D 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 =3D=
=20
a.attrelid and d.adnum =3D a.attnum order by n.nspname, c.relname, attnum=
'
[15.453] [ fetched 2 rows ]
[15.453]PGAPI_Columns: table=3D'mytable',field_name=3D'gid',type=3D23,nam=
e=3D'int4'
[15.453]PGAPI_Columns:=20
table=3D'mytable',field_name=3D'data',type=3D1043,name=3D'va rchar'
[15.453]conn=3D09FC0048, query=3D'SELECT currval('mytable_gid_seq'::regcl=
ass)'
[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 =BB n'existe pas'
[15.453]ERROR from backend during send_query: 'P16'
[15.453]ERROR from backend during send_query:=20
'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=3DSC_execute, desc=3D'(null)', errnum=3D7,=20
errmsg=3D'Error while executing the query'
[15.453]=20
------------------------------------------------------------
[15.453] hdbc=3D09FC0048, stmt=3D095CB3E0, result=3D095C9=
4F0
[15.453] prepare=3D2, internal=3D0
[15.469] bindings=3D00000000, bindings_allocated=3D0
[15.469] parameters=3D00000000, parameters_allocated=3D0
[15.469] statement_type=3D0, statement=3D'SELECT @@IDENTI=
TY'
[15.469] stmt_with_params=3D'SELECT=20
currval('mytable_gid_seq'::regclass)'
[15.469] data_at_exec=3D-1, current_exec_param=3D-1, put_=
data=3D0
[15.469] currTuple=3D-1, current_col=3D-1, lobj_fd=3D-1
[15.469] maxRows=3D0, rowset_size=3D1, keyset_size=3D0,=20
cursor_type=3D0, scroll_concurrency=3D1
[15.469] cursor_name=3D'SQL_CUR095CB3E0'
[15.469] ----------------QResult Info=20
-------------------------------
[15.469] fields=3D09FC2F58, backend_tuples=3D00000000,=20
tupleField=3D0, conn=3D00000000
[15.469] fetch_count=3D0, num_total_rows=3D0, num_fields=3D=
0,=20
cursor=3D'(NULL)'
[15.469] message=3D'ERREUR: la relation =AB mytable_gid_s=
eq=20
=BB n'existe pas', command=3D'(NULL)', notice=3D'(NULL)'
[15.469] status=3D7, inTuples=3D0
[15.469]CONN ERROR: func=3DSC_execute, desc=3D'(null)', errnum=3D110,=20
errmsg=3D'ERREUR: la relation =AB mytable_gid_seq =BB n'existe pas'
[15.469]=20
------------------------------------------------------------
[15.469] henv=3D095C2138, conn=3D09FC0048, status=3D1, num_stm=
ts=3D16
[15.469] sock=3D09FC3540, stmts=3D09FC3030, lobj_type=3D-999
[15.469] ---------------- Socket Info=20
-------------------------------
[15.469] socket=3D172, reverse=3D0, errornumber=3D0, errormsg=3D=
'(NULL)'
[15.469] buffer_in=3D157064440, buffer_out=3D157072160
[15.485] buffer_filled_in=3D6, buffer_filled_out=3D0,=20
buffer_read_in=3D6
[15.485]conn=3D09FC0048, query=3D'ROLLBACK'


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

Thanks !
Regards
Arnaud Lesauvage

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: [GENERAL] "default nextval()" loses schema-qualification in dump?

Le 6/07/2010 17:17, Tom Lane a =E9crit :
> Arnaud Lesauvage 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 =3D 0;
SET client_encoding =3D 'LATIN9';
SET standard_conforming_strings =3D off;
SET check_function_bodies =3D false;
SET client_min_messages =3D warning;
SET escape_string_warning =3D off;
SET search_path =3D myschema, pg_catalog;
SET default_tablespace =3D '';
SET default_with_oids =3D 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=20
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=3D1
BoolsAsChar=3D0
TextAsLongVarchar=3D1
Protocol=3D7.4-1
AB=3D0x2
Rowversionning=3D1
CommLog=3D1

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

[0.063]conn=3D095C4198, query=3D'SELECT "myschema"."mytable"."gid" FROM=20
"myschema"."mytable" '
[0.063] [ fetched 0 rows ]
[9.125]conn=3D095C4198, query=3D'INSERT INTO "myschema"."mytable" ("dat=
a")=20
VALUES (E'somedata')'
[9.125]conn=3D095C4198, query=3D'select n.nspname, c.relname, a.attname,=20
a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull,=20
c.relhasrules, c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then=20
t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c=20
inner join pg_catalog.pg_namespace n on n.oid =3D c.relnamespace and=20
c.relname =3D E'mytable' and n.nspname =3D E'myschema') inner join=20
pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and=20
a.attrelid =3D 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 =3D=
=20
a.attrelid and d.adnum =3D a.attnum order by n.nspname, c.relname, attnum=
'
[9.141] [ fetched 2 rows ]
[9.141]PGAPI_Columns: table=3D'mytable',field_name=3D'gid',type=3D23,name=
=3D'int4'
[9.141]PGAPI_Columns:=20
table=3D'mytable',field_name=3D'data',type=3D1043,name=3D'va rchar'
[9.141]conn=3D095C4198, query=3D'SELECT=20
currval('myschema.mytable_gid_seq'::regclass)'
[9.141] [ fetched 1 rows ]
[9.141]conn=3D095C4198, query=3D'COMMIT'
[9.141]conn=3D095C4198, query=3D'SELECT "gid","data" FROM=20
"myschema"."mytable" WHERE "gid" =3D 1'
[9.141] [ fetched 1 rows ]



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

[11.328]conn=3D09FC0048, query=3D'SELECT "myschema"."mytable"."gid" FROM=20
"myschema"."mytable" '
[11.328] [ fetched 0 rows ]
[15.438]conn=3D09FC0048, query=3D'INSERT INTO "myschema"."mytable" ("dat=
a")=20
VALUES (E'somedata')'
[15.438]conn=3D09FC0048, query=3D'select n.nspname, c.relname, a.attname,=
=20
a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull,=20
c.relhasrules, c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then=20
t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c=20
inner join pg_catalog.pg_namespace n on n.oid =3D c.relnamespace and=20
c.relname =3D E'mytable' and n.nspname =3D E'myschema') inner join=20
pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and=20
a.attrelid =3D 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 =3D=
=20
a.attrelid and d.adnum =3D a.attnum order by n.nspname, c.relname, attnum=
'
[15.453] [ fetched 2 rows ]
[15.453]PGAPI_Columns: table=3D'mytable',field_name=3D'gid',type=3D23,nam=
e=3D'int4'
[15.453]PGAPI_Columns:=20
table=3D'mytable',field_name=3D'data',type=3D1043,name=3D'va rchar'
[15.453]conn=3D09FC0048, query=3D'SELECT currval('mytable_gid_seq'::regcl=
ass)'
[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 =BB n'existe pas'
[15.453]ERROR from backend during send_query: 'P16'
[15.453]ERROR from backend during send_query:=20
'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=3DSC_execute, desc=3D'(null)', errnum=3D7,=20
errmsg=3D'Error while executing the query'
[15.453] ------------------------------------------------------------
[15.453] hdbc=3D09FC0048, stmt=3D095CB3E0, result=3D095C9=
4F0
[15.453] prepare=3D2, internal=3D0
[15.469] bindings=3D00000000, bindings_allocated=3D0
[15.469] parameters=3D00000000, parameters_allocated=3D0
[15.469] statement_type=3D0, statement=3D'SELECT @@IDENTI=
TY'
[15.469] stmt_with_params=3D'SELECT=20
currval('mytable_gid_seq'::regclass)'
[15.469] data_at_exec=3D-1, current_exec_param=3D-1, put_=
data=3D0
[15.469] currTuple=3D-1, current_col=3D-1, lobj_fd=3D-1
[15.469] maxRows=3D0, rowset_size=3D1, keyset_size=3D0,=20
cursor_type=3D0, scroll_concurrency=3D1
[15.469] cursor_name=3D'SQL_CUR095CB3E0'
[15.469] ----------------QResult Info=20
-------------------------------
[15.469] fields=3D09FC2F58, backend_tuples=3D00000000,=20
tupleField=3D0, conn=3D00000000
[15.469] fetch_count=3D0, num_total_rows=3D0, num_fields=3D=
0,=20
cursor=3D'(NULL)'
[15.469] message=3D'ERREUR: la relation =AB mytable_gid_s=
eq=20
=BB n'existe pas', command=3D'(NULL)', notice=3D'(NULL)'
[15.469] status=3D7, inTuples=3D0
[15.469]CONN ERROR: func=3DSC_execute, desc=3D'(null)', errnum=3D110,=20
errmsg=3D'ERREUR: la relation =AB mytable_gid_seq =BB n'existe pas'
[15.469] ------------------------------------------------------------
[15.469] henv=3D095C2138, conn=3D09FC0048, status=3D1, num_stm=
ts=3D16
[15.469] sock=3D09FC3540, stmts=3D09FC3030, lobj_type=3D-999
[15.469] ---------------- Socket Info=20
-------------------------------
[15.469] socket=3D172, reverse=3D0, errornumber=3D0, errormsg=3D=
'(NULL)'
[15.469] buffer_in=3D157064440, buffer_out=3D157072160
[15.485] buffer_filled_in=3D6, buffer_filled_out=3D0,=20
buffer_read_in=3D6
[15.485]conn=3D09FC0048, query=3D'ROLLBACK'


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

Thanks !
Regards
Arnaud Lesauvage

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: [GENERAL] "default nextval()" loses schema-qualification in dump?

On 07/07/10 07:47, Arnaud Lesauvage wrote:
> Le 6/07/2010 17:17, Tom Lane a =E9crit :
>> Arnaud Lesauvage 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 ?

> [9.125]conn=3D095C4198, query=3D'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 =3D c.relnamespace and
> c.relname =3D E'mytable' and n.nspname =3D E'myschema') inner join
> pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and
> a.attrelid =3D c.oid) inner join pg_catalog.pg_type t on t.oid =3D
> a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid =3D
> a.attrelid and d.adnum =3D a.attnum order by n.nspname, c.relname, attn=
um'

This is psqlODBC getting the sequence name (if you run this query it's=20
the adsrc column). If I remember correctly, that's supposed to be the=20
human-readable version of an expression and preserved *as entered by the=20
user* (or pg_restore in your case).

If you start psql with the "-E" option and do \d myschema.mytable you'll=20
be able to see how it gets the sequence-name. About half-way down the=20
list of queries it runs you'll see a reference to pg_get_expr(...) -=20
that turns an internal representation into a useful usable one.

I don't know why psqlODBC isn't using that. The function has been around=20
for a while. Hmm - it's present back in 7.4 although it's not used in \d=20
- that does reference adsrc directly.

Just grabbed the source download for the latest version and it still=20
looks like it's using adsrc (I just searched for that and pg_get_expr).=20
There should probably be a change in info.c around line 2091 to add a=20
check for a recent version of PG (8+) and use pg_get_expr. Check on the=20
odbc mailing-list - there may be an updated version available for you to=20
test.

--=20
Richard Huxton
Archonet Ltd

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Re: [GENERAL] "default nextval()" loses schema-qualificationin dump ?

Le 7/07/2010 9:41, Richard Huxton a =E9crit :
> On 07/07/10 07:47, Arnaud Lesauvage wrote:
>> Le 6/07/2010 17:17, Tom Lane a =E9crit :
>>> Arnaud Lesauvage 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 in=
t the
>>>> psqlODBC is getting the default value of the sequence ?
>
>> [9.125]conn=3D095C4198, query=3D'select n.nspname, c.relname, a.attna=
me,
>> 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' the=
n
>> t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c
>> inner join pg_catalog.pg_namespace n on n.oid =3D c.relnamespace and
>> c.relname =3D E'mytable' and n.nspname =3D E'myschema') inner join
>> pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum> 0 an=
d
>> a.attrelid =3D c.oid) inner join pg_catalog.pg_type t on t.oid =3D
>> a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid=
=3D
>> a.attrelid and d.adnum =3D a.attnum order by n.nspname, c.relname, at=
tnum'
>
> This is psqlODBC getting the sequence name (if you run this query it's
> the adsrc column). If I remember correctly, that's supposed to be the
> human-readable version of an expression and preserved *as entered by th=
e
> user* (or pg_restore in your case).

Hi Richard, thanks for your help.
Yes, that's how I interpreted it too.

> If you start psql with the "-E" option and do \d myschema.mytable you'l=
l
> be able to see how it gets the sequence-name. About half-way down the
> list of queries it runs you'll see a reference to pg_get_expr(...) -
> that turns an internal representation into a useful usable one.
>
> I don't know why psqlODBC isn't using that. The function has been aroun=
d
> for a while. Hmm - it's present back in 7.4 although it's not used in \=
d
> - that does reference adsrc directly.

I think that the handling of "auto numbering" fields in PsqlODBC is=20
quite new, so maybe this is still a not very stable feature.



> Just grabbed the source download for the latest version and it still
> looks like it's using adsrc (I just searched for that and pg_get_expr).
> There should probably be a change in info.c around line 2091 to add a
> check for a recent version of PG (8+) and use pg_get_expr. Check on the
> odbc mailing-list - there may be an updated version available for you t=
o
> test.

I tested with the latest release of PsqlODBC (8.04.0200), and it fails=20
at the same point.


Regards
Arnaud Lesauvage

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: "default nextval()" loses schema-qualification in dump?

Le 7/07/2010 9:41, Richard Huxton a =E9crit :
> On 07/07/10 07:47, Arnaud Lesauvage wrote:
>> Le 6/07/2010 17:17, Tom Lane a =E9crit :
>>> Arnaud Lesauvage 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 in=
t the
>>>> psqlODBC is getting the default value of the sequence ?
>
>> [9.125]conn=3D095C4198, query=3D'select n.nspname, c.relname, a.attna=
me,
>> 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' the=
n
>> t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c
>> inner join pg_catalog.pg_namespace n on n.oid =3D c.relnamespace and
>> c.relname =3D E'mytable' and n.nspname =3D E'myschema') inner join
>> pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum> 0 an=
d
>> a.attrelid =3D c.oid) inner join pg_catalog.pg_type t on t.oid =3D
>> a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid=
=3D
>> a.attrelid and d.adnum =3D a.attnum order by n.nspname, c.relname, at=
tnum'
>
> This is psqlODBC getting the sequence name (if you run this query it's
> the adsrc column). If I remember correctly, that's supposed to be the
> human-readable version of an expression and preserved *as entered by th=
e
> user* (or pg_restore in your case).
>
> If you start psql with the "-E" option and do \d myschema.mytable you'l=
l
> be able to see how it gets the sequence-name. About half-way down the
> list of queries it runs you'll see a reference to pg_get_expr(...) -
> that turns an internal representation into a useful usable one.
>
> I don't know why psqlODBC isn't using that. The function has been aroun=
d
> for a while. Hmm - it's present back in 7.4 although it's not used in \=
d
> - that does reference adsrc directly.
>
> Just grabbed the source download for the latest version and it still
> looks like it's using adsrc (I just searched for that and pg_get_expr).
> There should probably be a change in info.c around line 2091 to add a
> check for a recent version of PG (8+) and use pg_get_expr. Check on the
> odbc mailing-list - there may be an updated version available for you t=
o
> test.
>


I haven't found a version more recent than the one I am using.
Is this thread enough for a bug report, or should a new one be opened ?

I don't think I know enough of either psqlODBC's internals to explain=20
the problem to the developers, and I am not sure this thread will get=20
their attention.

Regards
Arnaud Lesauvage

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: [GENERAL] "default nextval()" loses schema-qualification in dump ?

Richard Huxton writes:
> If you start psql with the "-E" option and do \d myschema.mytable you'll
> be able to see how it gets the sequence-name. About half-way down the
> list of queries it runs you'll see a reference to pg_get_expr(...) -
> that turns an internal representation into a useful usable one.

> I don't know why psqlODBC isn't using that. The function has been around
> for a while. Hmm - it's present back in 7.4 although it's not used in \d
> - that does reference adsrc directly.

> Just grabbed the source download for the latest version and it still
> looks like it's using adsrc (I just searched for that and pg_get_expr).
> There should probably be a change in info.c around line 2091 to add a
> check for a recent version of PG (8+) and use pg_get_expr. Check on the
> odbc mailing-list - there may be an updated version available for you to
> test.

Seems like psqlODBC shouldn't be fetching the default value at all, or
at least should not be trying to turn around and pass it back to the
server (if that's what's really happening). It would be far easier and
safer to omit the target column from the INSERT statement, or write
DEFAULT if that's inconvenient. Even if you collect the correct
expression by using pg_get_expr(), what if someone ALTERs the default
while you're connected?

I'm suspicious that this coding technique is a hangover from back when
you couldn't write "insert into foo values(default, default)", which is
to say PG 7.2 or before.

regards, tom lane

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: "default nextval()" loses schema-qualification in dump?

Arnaud Lesauvage wrote:
> Le 7/07/2010 9:41, Richard Huxton a =E9crit :
>> On 07/07/10 07:47, Arnaud Lesauvage wrote:
>>> Le 6/07/2010 17:17, Tom Lane a =E9crit :
>>>> Arnaud Lesauvage 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=20
>>>>> int the
>>>>> psqlODBC is getting the default value of the sequence ?
>>
>>> [9.125]conn=3D095C4198, query=3D'select n.nspname, c.relname, a.attn=
ame,
>>> 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' th=
en
>>> t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c
>>> inner join pg_catalog.pg_namespace n on n.oid =3D c.relnamespace and
>>> c.relname =3D E'mytable' and n.nspname =3D E'myschema') inner join
>>> pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum> 0 a=
nd
>>> a.attrelid =3D c.oid) inner join pg_catalog.pg_type t on t.oid =3D
>>> a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adreli=
d =3D
>>> a.attrelid and d.adnum =3D a.attnum order by n.nspname, c.relname,=20
>>> attnum'
>>
>> This is psqlODBC getting the sequence name (if you run this query it's
>> the adsrc column). If I remember correctly, that's supposed to be the
>> human-readable version of an expression and preserved *as entered by t=
he
>> user* (or pg_restore in your case).
>>
>> If you start psql with the "-E" option and do \d myschema.mytable you'=
ll
>> be able to see how it gets the sequence-name. About half-way down the
>> list of queries it runs you'll see a reference to pg_get_expr(...) -
>> that turns an internal representation into a useful usable one.
>>
>> I don't know why psqlODBC isn't using that. The function has been arou=
nd
>> for a while. Hmm - it's present back in 7.4 although it's not used in =
\d
>> - that does reference adsrc directly.
>>
>> Just grabbed the source download for the latest version and it still
>> looks like it's using adsrc (I just searched for that and pg_get_expr)=
..
>> There should probably be a change in info.c around line 2091 to add a
>> check for a recent version of PG (8+) and use pg_get_expr. Check on th=
e
>> odbc mailing-list - there may be an updated version available for you =
to
>> test.
>>
>=20
>=20
> I haven't found a version more recent than the one I am using.
> Is this thread enough for a bug report, or should a new one be opened ?
>=20
> I don't think I know enough of either psqlODBC's internals to explain=20
> the problem to the developers, and I am not sure this thread will get=20
> their attention.

Could you please try the drivers on testing for 8.4.0201 at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?

regards,
Hiroshi Inoue


--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: "default nextval()" loses schema-qualification in dump?

Le 9/07/2010 5:31, Hiroshi Inoue a =E9crit :
> Arnaud Lesauvage wrote:
>> Le 7/07/2010 9:41, Richard Huxton a =E9crit :
>>> On 07/07/10 07:47, Arnaud Lesauvage wrote:
>>>> Le 6/07/2010 17:17, Tom Lane a =E9crit :
>>>>> Arnaud Lesauvage 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 ?
>>>
>>>> [9.125]conn=3D095C4198, query=3D'select n.nspname, c.relname, a.at=
tname,
>>>> a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnu=
ll,
>>>> 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 =3D c.relnamespace a=
nd
>>>> c.relname =3D E'mytable' and n.nspname =3D E'myschema') inner join
>>>> pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum> =
0 and
>>>> a.attrelid =3D c.oid) inner join pg_catalog.pg_type t on t.oid =3D
>>>> a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adre=
lid =3D
>>>> a.attrelid and d.adnum =3D a.attnum order by n.nspname, c.relname,
>>>> attnum'
>>>
>>> This is psqlODBC getting the sequence name (if you run this query it=
's
>>> the adsrc column). If I remember correctly, that's supposed to be th=
e
>>> human-readable version of an expression and preserved *as entered by=
the
>>> user* (or pg_restore in your case).
>>>
>>> If you start psql with the "-E" option and do \d myschema.mytable yo=
u'll
>>> be able to see how it gets the sequence-name. About half-way down th=
e
>>> list of queries it runs you'll see a reference to pg_get_expr(...) -
>>> that turns an internal representation into a useful usable one.
>>>
>>> I don't know why psqlODBC isn't using that. The function has been ar=
ound
>>> for a while. Hmm - it's present back in 7.4 although it's not used i=
n \d
>>> - that does reference adsrc directly.
>>>
>>> Just grabbed the source download for the latest version and it still
>>> looks like it's using adsrc (I just searched for that and pg_get_exp=
r).
>>> There should probably be a change in info.c around line 2091 to add =
a
>>> check for a recent version of PG (8+) and use pg_get_expr. Check on =
the
>>> odbc mailing-list - there may be an updated version available for yo=
u to
>>> test.
>>>
>>
>>
>> I haven't found a version more recent than the one I am using.
>> Is this thread enough for a bug report, or should a new one be opened=
?
>>
>> I don't think I know enough of either psqlODBC's internals to explain
>> the problem to the developers, and I am not sure this thread will get
>> their attention.
>
> Could you please try the drivers on testing for 8.4.0201 at
> http://www.geocities.jp/inocchichichi/psqlodbc/index.html
> ?


It works fine Hiroshi, thanks a lot !
Do you know when a release as an msi installer will be available ? We=20
deploy this driver through an active directory policy.

Thanks again !

Regards,
Arnaud Lesauvage

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: [GENERAL] "default nextval()" loses schema-qualificationin dump ?

Tom Lane wrote:
> Richard Huxton writes:
>> If you start psql with the "-E" option and do \d myschema.mytable you'll
>> be able to see how it gets the sequence-name. About half-way down the
>> list of queries it runs you'll see a reference to pg_get_expr(...) -
>> that turns an internal representation into a useful usable one.
>
>> I don't know why psqlODBC isn't using that. The function has been around
>> for a while. Hmm - it's present back in 7.4 although it's not used in \d
>> - that does reference adsrc directly.
>
>> Just grabbed the source download for the latest version and it still
>> looks like it's using adsrc (I just searched for that and pg_get_expr).
>> There should probably be a change in info.c around line 2091 to add a
>> check for a recent version of PG (8+) and use pg_get_expr. Check on the
>> odbc mailing-list - there may be an updated version available for you to
>> test.
>
> Seems like psqlODBC shouldn't be fetching the default value at all, or
> at least should not be trying to turn around and pass it back to the
> server (if that's what's really happening). It would be far easier and
> safer to omit the target column from the INSERT statement, or write
> DEFAULT if that's inconvenient.

The driver calls currval() not nextval() so as to get the latest
inserted serial value. Though we can call lastval() for the purpose,
it causes a trouble unfortunately
(http://archives.postgresql.org/pgsql-odbc/2007-05/msg00016. php)
..

regards,
Hiroshi Inoue

> Even if you collect the correct
> expression by using pg_get_expr(), what if someone ALTERs the default
> while you're connected?
>
> I'm suspicious that this coding technique is a hangover from back when
> you couldn't write "insert into foo values(default, default)", which is
> to say PG 7.2 or before.
>
> regards, tom lane
>


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc