Primary Key

Hi,
I want to know how i can find out if a particular field in a
resultset is a primary key. Also is the OID automatically retrieved
as part of every select query?
thanks
raghu

--------------------------------------------------------------------------
Global Internet phone calls, voicemail, fax, e-mail and instant messaging.
Sign-up today at http://www.hotvoice.com
>From pgsql-jdbc-owner(at)postgresql(dot)org Wed Mar 27 23:37:14 2002
Received: from barry.xythos.com (h-64-105-36-191.SNVACAID.covad.net [64.105.36.191])
by postgresql.org (Postfix) with ESMTP id C9A8D4758DC
for <pgsql-jdbc(at)postgresql(dot)org>; Wed, 27 Mar 2002 23:36:53 -0500 (EST)
Received: from xythos.com (localhost.localdomain [127.0.0.1])
by barry.xythos.com (8.11.6/8.11.6) with ESMTP id g2S2FIE01786;
Wed, 27 Mar 2002 18:15:18 -0800
Message-ID: <3CA27CB5(dot)1080002(at)xythos(dot)com>
Date: Wed, 27 Mar 2002 18:15:17 -0800
From: Barry Lind <barry(at)xythos(dot)com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.9) Gecko/20020310
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: =?ISO-8859-1?Q?Jo=E3o_Paulo_Ribeiro?= <jp(at)mobicomp(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: One little tip for int8 and decimal :)
References: <3CA200D9(dot)10100(at)mobicomp(dot)com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Archive-Number: 200203/336
X-Sequence-Number: 3574

Joo,

This has been discussed before on the mailing list and rejected as the
wrong way to fix the problem. The correct way to fix this problem is to
fix the problem in the backend, not to work around the problem in each
of the front ends.

--Barry

Joo Paulo Ribeiro wrote:
> Hi!
>
> We are working with java and postgresql for a while.
>
> In our experiences we have seen the problem with int8 and decimal:
> postgres dont convert this types easyli and because of this the result
> sometimes wil not be the expected.
>
> A simple example:
> We have this table:
>
> create table test(
>
> data int8 NOT NULL PRIMARY KEY
>
> );
>
>
> we put n lines (n> 70000) :)
>
> if we try to make query withou explicit cast the postgres will not use
> the index.
> Example:
>
> pvs=# explain select * from test where data=12345;
>
> NOTICE: QUERY PLAN:
>
> Seq Scan on test (cost=0.00..22.50 rows=1 width=8)
>
> EXPLAIN
>
> pvs=#
>
>
> but with a explicit cast:
>
> pvs=# explain select * from test where data=12345::int8;
>
> NOTICE: QUERY PLAN:
>
> Index Scan using test_pkey on test (cost=0.00..4.82 rows=1 width=8)
>
> EXPLAIN
>
> pvs=#
>
> another aproach is to force the postgresql to evaluate and transform the
> value to the desired datatype using quotes '
>
> pvs=# explain select * from test where data='12345';
>
> NOTICE: QUERY PLAN:
>
> Index Scan using test_pkey on test (cost=0.00..4.82 rows=1 width=8)
>
> EXPLAIN
>
> pvs=#
>
>
> This problem is well known for the postgres user.
> But the problem go further when you use JDBC to access the postgresql.
> Using the same table.
> We have a little program that make a simple query:
> ...
>
> DBConnection con = someKindOfDbPool.allocateConnection();
>
> PreparedStatement ps = con.prepareStatement("Select * from user2
> where obid=?");
>
> ps.setlong(1,123456);
>
> ps.executeQuery();
>
> ...
>
> This query will never use the index because of the problem explained above.
> We can use setBigDecimal and problem will persist.
>
> I use DODs with Enhydra and the data layer generated by the DODs have
> this problem.
>
> What we propose is to change the prepared statment to force postgres to
> correctly use the index and the result will be the expected. :)
> For example, at the office we made a little change to the setLong and
> setBigDecimal from PreparedStatement class.
>
> The orginal look like:
>
> public void setBigDecimal(int parameterIndex, BigDecimal x) throws
> SQLException
> {
> if (x == null)
> setNull(parameterIndex, Types.OTHER);
> else
> set(parameterIndex, x.toString());
> }
>
>
> public void setLong(int parameterIndex, long x) throws SQLException {
> set(parameterIndex, (new Long(x)).toString());
> }
>
>
> and we changed de set(...) to setString(..) and its look like:
>
>
> public void setBigDecimal(int parameterIndex, BigDecimal x) throws
> SQLException {
> if (x == null)
> setNull(parameterIndex, Types.OTHER);
> else
> setString(parameterIndex, x.toString());
> }
>
>
> public void setLong(int parameterIndex, long x) throws SQLException {
> setString(parameterIndex, (new Long(x)).toString());
>
> }
>
> With this change when we use the setBigdecimal or the setLong in a query
> and we expect that a index will be used, it will really be used. :)
>
> This has been working in a production database for couple of month and
> is really working fine.
>
>
> Regards.
>
> Joo Paulo Ribeiro & Marco Leal
>
>