Thread: Re: [BUGS] 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
creatingtable even on psql.As psqlODBC drives executes query no 3 below to get foreign key information.if we want to
reproducethis , 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
Hi. > Zahid Khan <khanzahid235@yahoo.com> writes: >> In my application we are getting different results in 8.2\xA0 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\xA0 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
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> > 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
Bug fix request for 08.03.0400 ( was [BUGS] Behavior change of FK info query)
From
"Luiz K. Matsumura"
Date:
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@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. 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" );
Re: Bug fix request for 08.03.0400 ( was [BUGS] Behavior change of FK info query)
From
"Hiroshi Saito"
Date:
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&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@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" ); > -------------------------------------------------------------------------------- > > -- > Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-odbc >