Re: Selecting a constant question - Mailing list pgsql-hackers

For what its worth .. Your statement about why we are the first people
to mention this problem really got me thinking.  Anyone who would
attempt to write an ODBC driver for Postgres would run into the exact
same issue.   So I installed the official Postgres ODBC driver, and ran
the identical query and here are my results:

I probably should have looked at this first .... There is a whole
Postgres ODBC dialog dedicated to the very subject of this thread:
Handling of "unknown" data sizes.   The pgodbc driver is configured to
treat unknowns as varchar(255) by default,
As shown by my example below.  This can be configured up or down as
desired.

SQLExecDirect:
In: hstmt = 0x003C18E0, szSqlStr = "Select a,b,c, '123' ,
'123'::char(3), '123'::varchar(3) from...", cbSqlStr = -3
Return:    SQL_SUCCESS=0

Describe Column All:
icol, szColName, *pcbColName, *pfSqlType, *pcbColDef, *pibScale,
*pfNullable
1, a, 1, SQL_VARCHAR=12, 20, 0, SQL_NULLABLE=1
2, b, 1, SQL_CHAR=1, 10, 0, SQL_NULLABLE=1
3, c, 1, SQL_INTEGER=4, 10, 0, SQL_NULLABLE=1
4, ?column?, 8, SQL_VARCHAR=12, 255, 0, SQL_NULLABLE=1
5, bpchar, 6, SQL_CHAR=1, 3, 0, SQL_NULLABLE=1
6, varchar, 7, SQL_VARCHAR=12, 255, 0, SQL_NULLABLE=1

From psqlodbc.h

#define MAX_VARCHAR_SIZE        255    /* default maximum size
of                     * varchar fields (not
including null term) */

So I guess the bottom line is that we are not the first to encounter
this problem .. Its just been covered up by assigning
An arbitrary maximum size .. So I guess we will do the same and make it
configurable like the official postgres driver.

Thanks

lm

-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: Tuesday, June 12, 2007 10:43 AM
To: Larry McGhaw
Cc: Andrew Dunstan; Hannu Krosing; Tom Lane; Alvaro Herrera; Dann
Corbit; Gregory Stark; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Selecting a constant question

Hi,

Nobody is tring to attack anyone, but we're all surprised this is an
issue since you're the first person to have mentioned it. I have some a
query to test below:

On Tue, Jun 12, 2007 at 10:21:09AM -0700, Larry McGhaw wrote:
> We noticed inexplicably that when we used a constant with a postgres
> query, our records per second dropped From 60,000 records per second
> to 600 records per second, so we started digging into the issue.
>
> We discovered that libpq was not describing the metadata properly for
> the constant column, and it appears That the 3rd party grid control
> was relying on that metadata somehow  ..
> The bottom line is that there was
> A huge performance drag.

What I don't understand is *why* it's complaining about the constant
column and not, for example, any other variable length column. There are
a very small number of cases where a useful length is returned, 99% of
the time it doesn't, yet you're obviously not get any performance
problems there.

Just a quick test, does the problem go away if you do:

SELECT '1'::varchar FROM table;

If that fixes it then the bug is (probably) that the middleware thinks
that a length of -2 means it's 65534 bytes long. Note, in the test query
I gave, it will return -1 for the length. I don't want to blame the
middleware, but I want to make sure we're diagnosing the problem
correctly.

If that query has the same problem, then we really need to think of
something else.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability
to litigate.