Behavior change of FK info query

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 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@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��� 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&amp;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>

Hi,

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

http://pgfoundry.org/tracker/index.php?func=detail&amp;aid=1010303&amp;group_id=1000125&amp;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@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

--
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@planit.com.br>

Hi,

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

http://pgfoundry.org/tracker/index.php?func=detail&amp;aid=1010303&amp;group_id=1000125&amp;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@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

--
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" );

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