Behavior change of FK info query

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.

Zahid Khan <khanzahid235(at)yahoo(dot)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 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 .

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(at)postnewspapers(dot)com(dot)au> wrote:
From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Subject: Re: [BUGS] Behavior change of FK info query
To: khanzahid235(at)yahoo(dot)com
Cc: pgsql-bugs(at)postgresql(dot)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

I am using 8.3.1 driver ,Is this changed in any new version ?

--- On Tue, 11/4/08, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> wrote:
From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Subject: Re: [BUGS] Behavior change of FK info query
To: khanzahid235(at)yahoo(dot)com
Cc: pgsql-bugs(at)postgresql(dot)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

Hi.

> Zahid Khan <khanzahid235(at)yahoo(dot)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 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 .
>
> 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(at)guitar(dot)ocn(dot)ne(dot)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(at)pgadmin(dot)org>

> 2008/11/4 Hiroshi Saito <z-saito(at)guitar(dot)ocn(dot)ne(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc

Hi,

There are some chance to implement also correction for this bug:

http://pgfoundry.org/tracker/index.php?func=detail&aid=1010303&group_id=1000125&atid=538
?

I attached a path for a change made on info.c of release 08.03.0300 source.
I tested it with postgres 8.0 , 8.1 , 8.2 and 8.3 server and works fine.

This is just a change on one query made by PGAPI_Columns function as
showed bellow

.
.
.
(line 2066)
/*
* Create the query to find out the columns (Note: pre 6.3 did not
* have the atttypmod field)
*/
op_string = gen_opestr(like_or_eq, conn);
if (conn->schema_support)
{
strncpy(columns_query,
"select n.nspname, c.relname, a.attname"
", case when t.typtype = 'd' then t.typbasetype else
a.atttypid end as atttypid"
", coalesce(bt.typname, t.typname ) AS typname, a.attnum,
a.attlen"
", case when t.typtype = 'd' then t.typtypmod else
a.atttypmod end as atttypmod"
", a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc"
" from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace",
sizeof(columns_query));
if (search_by_ids)
snprintf_add(columns_query, sizeof(columns_query), " and
c.oid = %u", reloid);
else
{
if (escTableName)
snprintf_add(columns_query, sizeof(columns_query), " and
c.relname %s'%s'", op_string, escTableName);
schema_strcat1(columns_query, " and n.nspname %s'%.*s'",
op_string, escSchemaName, SQL_NTS, szTableName, cbTableName, conn);
}
strcat(columns_query, ") inner join pg_catalog.pg_attribute a"
" on (not a.attisdropped)");
if (0 == attnum && (NULL == escColumnName || like_or_eq != eqop))
strcat(columns_query, " and a.attnum > 0");
if (search_by_ids)
{
if (attnum != 0)
snprintf_add(columns_query, sizeof(columns_query), " and
a.attnum = %d", attnum);
}
else if (escColumnName)
snprintf_add(columns_query, sizeof(columns_query), " and
a.attname %s'%s'", op_string, escColumnName);
strcat(columns_query,
" and a.attrelid = 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
= a.attrelid and d.adnum = a.attnum"
" left outer join pg_type bt on t.typtype = 'd' and
t.typbasetype = bt.oid" );
strcat(columns_query, " order by n.nspname, c.relname, attnum");
}

I will be really happy if this can be fixed.

Best Regards

Hiroshi Saito wrote:
> Ok, agree. I will prepare it.
> Then, I need carefully packaging.
>
> ----- Original Message ----- From: "Dave Page" <dpage(at)pgadmin(dot)org>
>
>
>> 2008/11/4 Hiroshi Saito <z-saito(at)guitar(dot)ocn(dot)ne(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-odbc
>

--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.

Hi Luiz-san.

Sorry, very late reaction....
Release timing was being fixed.

It seems that your proposal needs a complicated verification. I consider like an
improvement rather than a bug. then, there are some worries. it is lo type etc..
However, It seems that Inoue-san review passes. but, it is next release.

I appreciate your perseverance. thanks!

Regards,
Hiroshi Saito

----- Original Message -----
From: "Luiz K. Matsumura" <luiz(at)planit(dot)com(dot)br>

> Hi,
>
> There are some chance to implement also correction for this bug:
>
> http://pgfoundry.org/tracker/index.php?func=detail&aid=1010303&group_id=1000125&atid=538
> ?
>
> I attached a path for a change made on info.c of release 08.03.0300 source.
> I tested it with postgres 8.0 , 8.1 , 8.2 and 8.3 server and works fine.
>
>
> This is just a change on one query made by PGAPI_Columns function as
> showed bellow
>
> .
> .
> .
> (line 2066)
> /*
> * Create the query to find out the columns (Note: pre 6.3 did not
> * have the atttypmod field)
> */
> op_string = gen_opestr(like_or_eq, conn);
> if (conn->schema_support)
> {
> strncpy(columns_query,
> "select n.nspname, c.relname, a.attname"
> ", case when t.typtype = 'd' then t.typbasetype else
> a.atttypid end as atttypid"
> ", coalesce(bt.typname, t.typname ) AS typname, a.attnum,
> a.attlen"
> ", case when t.typtype = 'd' then t.typtypmod else
> a.atttypmod end as atttypmod"
> ", a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc"
> " from (((pg_catalog.pg_class c inner join
> pg_catalog.pg_namespace n on n.oid = c.relnamespace",
> sizeof(columns_query));
> if (search_by_ids)
> snprintf_add(columns_query, sizeof(columns_query), " and
> c.oid = %u", reloid);
> else
> {
> if (escTableName)
> snprintf_add(columns_query, sizeof(columns_query), " and
> c.relname %s'%s'", op_string, escTableName);
> schema_strcat1(columns_query, " and n.nspname %s'%.*s'",
> op_string, escSchemaName, SQL_NTS, szTableName, cbTableName, conn);
> }
> strcat(columns_query, ") inner join pg_catalog.pg_attribute a"
> " on (not a.attisdropped)");
> if (0 == attnum && (NULL == escColumnName || like_or_eq != eqop))
> strcat(columns_query, " and a.attnum > 0");
> if (search_by_ids)
> {
> if (attnum != 0)
> snprintf_add(columns_query, sizeof(columns_query), " and
> a.attnum = %d", attnum);
> }
> else if (escColumnName)
> snprintf_add(columns_query, sizeof(columns_query), " and
> a.attname %s'%s'", op_string, escColumnName);
> strcat(columns_query,
> " and a.attrelid = 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
> = a.attrelid and d.adnum = a.attnum"
> " left outer join pg_type bt on t.typtype = 'd' and
> t.typbasetype = bt.oid" );
> strcat(columns_query, " order by n.nspname, c.relname, attnum");
> }
>
>
> I will be really happy if this can be fixed.
>
> Best Regards
>
>
> Hiroshi Saito wrote:
>> Ok, agree. I will prepare it.
>> Then, I need carefully packaging.
>>
>> ----- Original Message ----- From: "Dave Page" <dpage(at)pgadmin(dot)org>
>>
>>
>>> 2008/11/4 Hiroshi Saito <z-saito(at)guitar(dot)ocn(dot)ne(dot)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(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-odbc
>>
>
> --
> Luiz K. Matsumura
> Plan IT Tecnologia Inform?ica Ltda.
>
>

--------------------------------------------------------------------------------

> 2074,2077c2074,2079
> < "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 from (((pg_catalog.pg_class c"
> < " inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace",
> ---
>> "select n.nspname, c.relname, a.attname"
>> ", case when t.typtype = 'd' then t.typbasetype else a.atttypid end as atttypid"
>> ", coalesce(bt.typname, t.typname ) AS typname, a.attnum, a.attlen"
>> ", case when t.typtype = 'd' then t.typtypmod else a.atttypmod end as atttypmod"
>> ", a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc"
>> " from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace",
> 2099,2101c2101,2104
> < " and a.attrelid = 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 = a.attrelid and d.adnum = a.attnum");
> ---
>> " and a.attrelid = 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 = a.attrelid and d.adnum = a.attnum"
>> " left outer join pg_type bt on t.typtype = 'd' and t.typbasetype = bt.oid" );
>

--------------------------------------------------------------------------------

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