Thread: 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