Behavior change of FK info query - Mailing list pgsql-bugs

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=