Hi,
I am getting one failures in odbc application with 8.3 server which is related
to foreign key information.
In my application we are getting different results in 8.2 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 and 8.3 .
1 .Create table dept321.
query_string [CREATE TABLE dept321(deptno CHAR(3) NOT NULL PRIMARY KEY,
deptname VARCHAR(32))]
2 .Create table emp321.
query_string [CREATE TABLE emp321(empno CHAR(7) NOT NULL PRIMARY KEY, deptno
CHAR(3) NOT NULL, sex CHAR(1), salary DECIMAL(7,2), CONSTRAINT check1 CHECK(sex
IN('M', 'F')), CONSTRAINT check2 CHECK(salary < 70000.00), CONSTRAINT fk1
FOREIGN KEY (deptno) REFERENCES dept321(deptno));]
3. Query to extract information from catalog in psqlODBC.
query_string [SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable,
pt.tginitdeferred, pp1.proname, pp2.proname, pc.oid,
pc1.oid, pc1.relname, pt.tgconstrname, pn1.nspname FROM
pg_catalog.pg_class pc, pg_catalog.pg_class pc1,
pg_catalog.pg_proc pp, pg_catalog.pg_proc pp1, pg_catalog.pg_proc
pp2, pg_catalog.pg_trigger pt, pg_catalog.pg_trigger pt1,
pg_catalog.pg_trigger pt2, pg_catalog.pg_namespace pn,
pg_catalog.pg_namespace pn1 WHERE pc.relname='dept321'AND pn.nspname =
'public' AND pc.relnamespace = pn.oid AND pt.tgconstrrelid = pc.oid
AND pp.oid = pt.tgfoid AND pp.proname Like '%ins' AND pt1.tgconstrname =
pt.tgconstrname AND pt1.tgconstrrelid = pt.tgrelid AND pt1.tgrelid =
pc.oid AND
pc1.oid = pt.tgrelid AND pp1.oid = pt1.tgfoid AND pp1.proname
like '%upd' AND (pp1.proname not like '%check%') AND pt2.tgconstrname =
pt.tgconstrname AND pt2.tgconstrrelid = pt.tgrelid AND pt2.tgrelid =
pc.oid AND pp2.oid = pt2.tgfoid AND pp2.proname Like '%del'
AND pn1.oid = pc1.relnamespace order by pt.tgconstrname]
Result of query no 3 on 8.3 server
tgargs | tgnargs | tgdeferrable | tginitdeferred | proname
| proname | oid | oid | relname | tgconstrname | nspname
--------+---------+--------------+----------------+----------------------+----------------------+-------+-------+---------+--------------+---------
| 0 | f | f | RI_FKey_noaction_upd |
RI_FKey_noaction_del | 44506 | 50258 | emp321 | fk1 | public
Result of query no 3 on 8.2 server
tgargs | tgnargs | tgdeferrable | tginitdeferred
| proname | proname | oid | oid | relname |
tgconstrname | nspname
-----------------------------------------------------------------+---------+--------------+----------------+----------------------+----------------------+-------+-------+---------+--------------+---------
fk1\000emp321\000dept321\000UNSPECIFIED\000deptno\000deptno\000 | 6 |
f | f | RI_FKey_noaction_upd | RI_FKey_noaction_del |
66289 | 66315 | emp321 | fk1 | public
Note:- values tgargs | tgnargs are different in 8.2 and 8.3 server.psqlODBC
driver expect the values of these columns and checks the number of arguments
from 'tgnargs' column.and extracts the foreign key name from the 'tgargs'
column.
I have tried to investigate the behavior of this on server side.I can see the
code which adds the tgargs column data separated by '\\000' in CreateTrigger()
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 being executed in
8.3.Is this intentional?, coz my odbc application is break due to this change.
Thanks,
Zahid K.
