Skip site navigation (1) Skip section navigation (2)

Behavior change of FK info query

From: Zahid Khan <khanzahid235(at)yahoo(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Behavior change of FK info query
Date: 2008-11-04 09:10:25
Message-ID: 653137.58942.qm@web54506.mail.re2.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-odbc
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.


      

Responses

pgsql-odbc by date

Next:From: Tom LaneDate: 2008-11-04 13:22:15
Subject: Re: Behavior change of FK info query
Previous:From: Andreas KretschmerDate: 2008-11-02 07:30:47
Subject: Re: Error in Adding All Tables

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-11-04 13:22:15
Subject: Re: Behavior change of FK info query
Previous:From: Kevin FieldDate: 2008-11-03 18:07:48
Subject: BUG #4509: array_cat's null behaviour is inconsistent

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group