Thread: Behavior change of FK info query
Hi,
I am getting one failures in odbc application with 8.3 server which is rela=
ted to foreign key information.
In my application we are getting different results in 8.2=A0 and 8.3 server=
s. if we execute the query no 3 below after creating table even on psql.As =
psqlODBC drives executes query no 3 below to get foreign key information.if=
we want to reproduce this , we can executes the following queries on psql =
in sequence we are getting different results in 8.2=A0 and 8.3 .
1 .Create table dept321.
query_string [CREATE TABLE dept321(deptno CHAR(3) NOT NULL PRIMARY KEY, dep=
tname VARCHAR(32))]
2 .Create table emp321.
query_string [CREATE TABLE emp321(empno CHAR(7) NOT NULL PRIMARY KEY, deptn=
o CHAR(3) NOT NULL, sex CHAR(1), salary DECIMAL(7,2), CONSTRAINT check1 CHE=
CK(sex IN('M', 'F')), CONSTRAINT check2 CHECK(salary < 70000.00), CONSTRAIN=
T fk1 FOREIGN KEY (deptno) REFERENCES dept321(deptno));]
3. Query to extract information from catalog in psqlODBC.
query_string [SELECT=A0=A0=A0 pt.tgargs,=A0=A0=A0=A0=A0 pt.tgnargs,=A0=A0=
=A0=A0 pt.tgdeferrable,=A0=A0=A0=A0=A0=A0 pt.tginitdeferred,=A0=A0=A0=A0=A0=
=A0 pp1.proname,=A0=A0=A0 pp2.proname,=A0=A0=A0 pc.oid,=A0=A0=A0=A0=A0=A0=
=A0=A0 pc1.oid,=A0=A0=A0=A0=A0=A0=A0 pc1.relname,=A0=A0=A0 pt.tgconstrname,=
pn1.nspname FROM=A0=A0=A0=A0=A0=A0 pg_catalog.pg_class pc,=A0=A0=A0=A0=A0=
=A0=A0=A0 pg_catalog.pg_class pc1,=A0=A0=A0=A0=A0=A0=A0 pg_catalog.pg_proc =
pp,=A0 pg_catalog.pg_proc pp1,=A0=A0=A0=A0=A0=A0=A0=A0 pg_catalog.pg_proc p=
p2,=A0=A0=A0=A0=A0=A0=A0=A0 pg_catalog.pg_trigger pt,=A0=A0=A0=A0=A0=A0 pg_=
catalog.pg_trigger pt1,=A0=A0=A0=A0=A0 pg_catalog.pg_trigger pt2,=A0=A0=A0=
=A0=A0 pg_catalog.pg_namespace pn,=A0=A0=A0=A0 pg_catalog.pg_namespace pn1 =
WHERE=A0 pc.relname=3D'dept321'AND pn.nspname =3D 'public'=A0=A0=A0=A0=A0=
=A0=A0 AND pc.relnamespace =3D pn.oid=A0=A0=A0 AND pt.tgconstrrelid =3D pc.=
oid=A0=A0 AND pp.oid =3D pt.tgfoid=A0 AND pp.proname Like '%ins'=A0=A0=A0=
=A0=A0 AND pt1.tgconstrname =3D pt.tgconstrname=A0 AND pt1.tgconstrrelid =
=3D pt.tgrelid=A0=A0=A0=A0=A0 AND pt1.tgrelid =3D pc.oid=A0=A0=A0=A0=A0=A0=
=A0 AND
pc1.oid =3D pt.tgrelid=A0=A0=A0=A0=A0=A0=A0 AND pp1.oid =3D pt1.tgfoid=A0=
=A0=A0=A0=A0=A0=A0 AND pp1.proname like '%upd'=A0=A0=A0=A0 AND (pp1.proname=
not like '%check%')=A0=A0 AND pt2.tgconstrname =3D pt.tgconstrname=A0=A0 A=
ND pt2.tgconstrrelid =3D pt.tgrelid=A0=A0=A0=A0 AND pt2.tgrelid =3D pc.oid=
=A0=A0=A0=A0=A0=A0=A0=A0 AND pp2.oid =3D pt2.tgfoid=A0=A0=A0=A0=A0=A0=A0 AN=
D pp2.proname Like '%del'=A0=A0=A0=A0 AND pn1.oid =3D pc1.relnamespace=A0 o=
rder by pt.tgconstrname]
Result of query no 3 on 8.3 server
=A0tgargs | tgnargs | tgdeferrable | tginitdeferred |=A0=A0=A0=A0=A0=A0 pro=
name=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0 proname=A0=A0=A0=A0=A0=A0=A0 =
|=A0 oid=A0 |=A0 oid=A0 | relname | tgconstrname | nspname
--------+---------+--------------+----------------+----------------------+-=
---------------------+-------+-------+---------+--------------+---------
=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0 0 | f=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0 | f=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | RI_FKey_noaction_upd | =
RI_FKey_noaction_del | 44506 | 50258 | emp321=A0 | fk1=A0=A0=A0=A0=A0=A0=A0=
=A0=A0 | public
Result of query no 3 on 8.2 server
tgargs=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 | tgnargs | tgdeferrable | tginitdeferred |=A0=A0=A0=A0=
=A0=A0 proname=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0 proname=A0=A0=A0=A0=A0=A0=
=A0 |=A0 oid=A0 |=A0 oid=A0 | relname | tgconstrname | nspname
-----------------------------------------------------------------+---------=
+--------------+----------------+----------------------+-------------------=
---+-------+-------+---------+--------------+---------
=A0fk1\000emp321\000dept321\000UNSPECIFIED\000deptno\000deptno\000 |=A0=A0=
=A0=A0=A0=A0 6 | f=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | f=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0 | RI_FKey_noaction_upd | RI_FKey_noaction_del | 66289=
| 66315 | emp321=A0 | fk1=A0=A0=A0=A0=A0=A0=A0=A0=A0 | public
=A0
=A0Note:- values=A0 tgargs | tgnargs are different in 8.2 and 8.3 server.ps=
qlODBC driver expect the values of these columns and checks the number of a=
rguments from 'tgnargs' column.and extracts the foreign key name from the '=
tgargs' column.
=A0
=A0I have tried to investigate the behavior of this on server side.I can se=
e the code which adds the tgargs column data separated by '\\000' in Create=
Trigger() in src/backend/commands/trigger.c file.That code is available in =
8.3 server as well. That code path was being executed in 8.2 but is not bei=
ng executed in 8.3.Is this intentional?, coz my odbc application is break d=
ue to this change.
=A0
=A0
Thanks,
Zahid K.
=20=20=20=20=20=20=
Zahid Khan <khanzahid235@yahoo.com> writes:
> In my application we are getting different results in 8.2� and 8.3 servers. if we execute the query no 3 below after
creatingtable even on psql.As psqlODBC drives executes query no 3 below to get foreign key information.if we want to
reproducethis , we can executes the following queries on psql in sequence we are getting different results in 8.2� and
8.3.
8.3 does not store any foreign-key information in pg_trigger.tgargs
anymore. If psqlODBC is depending on this query then that's a psqlODBC
bug. It'd be better to look at pg_constraint.
regards, tom lane
Zahid Khan wrote: > > Hi, > > I am getting one failures in odbc application with 8.3 server which is > related to foreign key information. Which ODBC driver version are you using? Remember that newer drivers work with older database servers, but older drivers may not work with newer database servers. -- Craig Ringer
I am using 8.3.1 driver ,Is this changed in any new version ? --- On Tue, 11/4/08, Craig Ringer <craig@postnewspapers.com.au> wrote: From: Craig Ringer <craig@postnewspapers.com.au> Subject: Re: [BUGS] Behavior change of FK info query To: khanzahid235@yahoo.com Cc: pgsql-bugs@postgresql.org Date: Tuesday, November 4, 2008, 6:57 AM Zahid Khan wrote: > > Hi, > > I am getting one failures in odbc application with 8.3 server which is > related to foreign key information. Which ODBC driver version are you using? Remember that newer drivers work with older database servers, but older drivers may not work with newer database servers. -- Craig Ringer -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
I am using 8.3.1 driver ,Is this changed in any new version ? --- On Tue, 11/4/08, Craig Ringer <craig@postnewspapers.com.au> wrote: From: Craig Ringer <craig@postnewspapers.com.au> Subject: Re: [BUGS] Behavior change of FK info query To: khanzahid235@yahoo.com Cc: pgsql-bugs@postgresql.org Date: Tuesday, November 4, 2008, 6:57 AM Zahid Khan wrote: > > Hi, > > I am getting one failures in odbc application with 8.3 server which is > related to foreign key information. Which ODBC driver version are you using? Remember that newer drivers work with older database servers, but older drivers may not work with newer database servers. -- Craig Ringer -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Hi. > Zahid Khan <khanzahid235@yahoo.com> writes: >> In my application we are getting different results in 8.2\xA0 and 8.3 servers. if we execute the >> query no 3 below after creating table even on psql.As psqlODBC drives executes query no 3 below >> to get foreign key information.if we want to reproduce this , we can executes the following >> queries on psql in sequence we are getting different results in 8.2\xA0 and 8.3 . > > 8.3 does not store any foreign-key information in pg_trigger.tgargs > anymore. If psqlODBC is depending on this query then that's a psqlODBC > bug. It'd be better to look at pg_constraint. Ahh, About foreign key, it will solve by 08.03.0300. http://psqlodbc.projects.postgresql.org/release.html I think that it is solvable by 4.). It seems that I did what point of that mistake. We has forgotten the information of a release.....sorry. and, It seems that the test of UUID was not enough. http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/psqlodbc/psqlodbc/convert.c.diff?r1=1.173&r2=1.174 One more condition fell out. http://archives.postgresql.org/pgsql-odbc/2008-10/msg00010.php Furthermore, I think it good to use libpq by which 8.3.5 was released. So, it is better to pack up 08.03.0310.? to Dave and Inoue-san. What do you think? Regards, Hiroshi Saito
Zahid Khan wrote: > > > I am using 8.3.1 driver ,Is this changed in any new version ? Not as far as I know. There might be problems if you were using, say, an 8.2.x driver with an 8.3 server, though. -- Craig Ringer
2008/11/4 Hiroshi Saito <z-saito@guitar.ocn.ne.jp>: > Furthermore, I think it good to use libpq by which 8.3.5 was released. > So, it is better to pack up 08.03.0310.? > > to Dave and Inoue-san. > What do you think? 08.03.0400 sounds better to me. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Ok, agree. I will prepare it. Then, I need carefully packaging. ----- Original Message ----- From: "Dave Page" <dpage@pgadmin.org> > 2008/11/4 Hiroshi Saito <z-saito@guitar.ocn.ne.jp>: > >> Furthermore, I think it good to use libpq by which 8.3.5 was released. >> So, it is better to pack up 08.03.0310.? >> >> to Dave and Inoue-san. >> What do you think? > > 08.03.0400 sounds better to me. > > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > > -- > Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-odbc