Обсуждение: BUG #3015: libpq: PQftype() on a lo type result column returns Oid of type oid instead of Oid of type lo.
The following bug has been logged online: Bug reference: 3015 Logged by: Email address: churi@roguewave.com PostgreSQL version: 8.2.3 Operating system: Windows XP Description: libpq: PQftype() on a lo type result column returns Oid of type oid instead of Oid of type lo. Details: === Environment === PostgreSQL server version: 8.2.3 PostgreSQL libpq C library version: 8.2.3 CPU (client and server): Intel Pentium 4 Operating System (client & server): Windows XP SP2 Compiler: Visual C++ 2005 32-bit === Problem === I am migrating my product from using PostgreSQL 8.0.4 to PostgreSQL 8.2.3. I am migrating both server as well as libpq client. If a SELECT statement is querying a lo type column of a table, PQftype() call on that column used to return Oid of type lo in PostgreSQL 8.0.4. It now returns Oid of type oid in PostgreSQL 8.2.3. === Test Case === == SQLs == qe1=> select typname, oid from pg_catalog.pg_type where typname = 'lo' OR typnam e = 'oid'; typname | oid ---------+------- oid | 26 lo | 17230 (2 rows) qe1=> create table testtable(col1 lo, col2 oid); CREATE TABLE qe1=> \d testtable Table "public.testtable" Column | Type | Modifiers --------+------+----------- col1 | lo | col2 | oid | == libpq program == #include <stdio.h> #include <libpq-fe.h> void fetchQuerySchema(PGconn* dbc) { PGresult *res; char *colName; int colType, size, mod; printf("\n\nFetching Select Query Schema.....\n"); res = PQexec(dbc, "select * from testtable"); if (res == 0) { printf("!!!!! Error.\n"); } for (int i = 0; i < PQnfields(res); ++i) { colName = PQfname(res, i); colType = PQftype(res, i); size = PQfsize(res, i); mod = PQfmod(res, i); printf("Schema for Column %d: \n", i+1); printf("\tName: %s\n\tType: %d\n\tSize: %d\n\tMod: %d\n\n", colName, colType, size, mod); } PQclear(res); } int main() { PGconn *dbc; dbc = PQconnectdb( "host = hostname user = user password = pass dbname = qe1"); if(dbc == 0 || PQstatus(dbc) == CONNECTION_BAD) { printf("!!!!! Failed to establish connection.\n"); } fetchQuerySchema(dbc); PQfinish(dbc); return 0; } === Output on 8.2.3 === Fetching Select Query Schema..... Schema for Column 1: Name: col1 Type: 26 Size: 4 Mod: -1 Schema for Column 2: Name: col2 Type: 26 Size: 4 Mod: -1 === Output on 8.0.4 === Fetching Select Query Schema..... Schema for Column 1: Name: col1 Type: 17230 Size: 4 Mod: -1 Schema for Column 2: Name: col2 Type: 26 Size: 4 Mod: -1 The 8.0.4 server correctly returns the Oid for lo column as the Oid of lo type. The 8.2.3 server on the other hand returns Oid of oid type. Thank you. Your help will be greatly appreciated.
"" <churi@roguewave.com> writes: > Description: libpq: PQftype() on a lo type result column returns Oid > of type oid instead of Oid of type lo. This is an expected change: type "lo" isn't an independent datatype anymore, just a domain over OID. regards, tom lane
Thanks Tom. I used to rely on the PQftype() to determine if the OIDs stored in that result field are pointing to large objects or not. If they are large objects, I used to fetch them using lo_* API. Now since PQftype() returns type oid for type lo columns, how will I identify if a result column contains large objects or not? One way I can think of is to use PQftable() and PQftablecol() and then query the type of the table column from pg_attribute. Would this be the correct way? Is there a better and easier way to identify the data to be lo? Thanking you in advance. Unmesh -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Thursday, February 15, 2007 12:55 PM To: Unmesh Churi Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #3015: libpq: PQftype() on a lo type result column returns Oid of type oid instead of Oid of type lo.=20 "" <churi@roguewave.com> writes: > Description: libpq: PQftype() on a lo type result column returns Oid > of type oid instead of Oid of type lo. This is an expected change: type "lo" isn't an independent datatype anymore, just a domain over OID. regards, tom lane
"Unmesh Churi" <churi@roguewave.com> writes: > Thanks Tom. I used to rely on the PQftype() to determine if the OIDs > stored in that result field are pointing to large objects or not. If > they are large objects, I used to fetch them using lo_* API. > Now since PQftype() returns type oid for type lo columns, how will I > identify if a result column contains large objects or not? Do you need to identify that? What other use-case has your application got for fetching OID columns? regards, tom lane
Tom, My product is a library on top of the libpq C Library. The product API provides our users flexibility to run any SQL. The results of the execution are processed generically based on the result schema returned by libpq. I can think of these use cases on the top of my head which would need to distinguish oid from lo: 1. Fetching OID of a user defined type. It could be used for other operations on the type, such as parameter binding, result field type identification, etc. 2. Querying system tables. Tables such as pg_class, pg_attribute, etc return many fields which are of type OID. 3. Querying tableoid or oid columns of a user table. In all the above cases, OIDs are to be returned as integers. OTOH, for an lo object the object must be fetched and returned. Thank you. Unmesh -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Thursday, February 15, 2007 10:57 PM To: Unmesh Churi Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #3015: libpq: PQftype() on a lo type result column returns Oid of type oid instead of Oid of type lo.=20 "Unmesh Churi" <churi@roguewave.com> writes: > Thanks Tom. I used to rely on the PQftype() to determine if the OIDs > stored in that result field are pointing to large objects or not. If > they are large objects, I used to fetch them using lo_* API. > Now since PQftype() returns type oid for type lo columns, how will I > identify if a result column contains large objects or not? Do you need to identify that? What other use-case has your application got for fetching OID columns? regards, tom lane
Well, we could change type 'lo' back to an independent type as of 8.3, but I'm not sure that will help you --- 8.1 and 8.2 are doing it as a domain and we can't retroactively change that situation. regards, tom lane
I guess the only option left for 8.2.x then is to PQftable() and PQftablecol() on the result and then query the pg_attribute to figure out the type. The penalty would be an additional server round-trip and query execution per OID type fetched, which may be considerable. Changing 'lo' back to the independent type for 8.3.x, will help significantly in increasing performance and reducing complexity. Thank you. Unmesh -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Sunday, February 18, 2007 2:59 PM To: Unmesh Churi Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #3015: libpq: PQftype() on a lo type result column returns Oid of type oid instead of Oid of type lo.=20 Well, we could change type 'lo' back to an independent type as of 8.3, but I'm not sure that will help you --- 8.1 and 8.2 are doing it as a domain and we can't retroactively change that situation. regards, tom lane