Re: Selecting a constant question

I'm really frustrated by this process I'm not trying to attack anyone
here. I'm just surprised that no one will even entertain the idea that
this is an issue that needs to be addressed.

Instead nearly all of the responses have been attacking the applications
that rely on the metadata.

Let me back up and explain the situation.

This issue came to light for us when we were using a query tool to
examine performance of postgres queries.

We were not only measuring the performance of the database itself, but
also the TCP/IP transport,
And the rendering of the data .. Comparing SQL Server, Oracle, and
Postgres head to head with the same queries.

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.

* OK ... I agree that the memory handling in the grid control could be
better, but I would imagine that
this issue is not an isolated to this one particular control, and that
other applications and controls that rely
on resultset metadata may have this issue.

Bottom line, we only reported this problem because we thought you would
be interested in doing everything possible to make postgres more
mainstream and conform to SQL standards. In the past such suggestions
have been absorbed with zeal.

I have no vested interest in you improving the interface or not, and I'm
not going to "plead a case" for you
To do something that every other commercial database has done out of the
box.

It is in your hands now :)

Thanks

lm

-----Original Message-----
From: Andrew Dunstan [mailto:andrew(at)dunslane(dot)net]
Sent: Tuesday, June 12, 2007 4:50 AM
To: Larry McGhaw
Cc: Hannu Krosing; Tom Lane; Alvaro Herrera; Dann Corbit; Gregory Stark;
Martijn van Oosterhout; pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Selecting a constant question

Larry McGhaw wrote:
> Again, the issue is not our tool, but the deficiency in libpq/postgres

> ... even mysql gets its right .. why not Postgres?
>
> Its not hard for a database to report metadata properly.
>
> if I issue a sql statement:
> select '123' from <any table>
> the database should report that the maximum length of the 1st column
> in the resultset is 3 ... it cant be any more plain than that.
>
>
>

Making assertions like this does not make your case for you. If you
think it's that easy then send in a patch. I suspect that doing what you
want in the cases where it could be supported would require a protocol
change, with possibly an extra field in the RowDescription object. If
that's true you'd need to make a very good and compelling case indeed
for such a change. If this is so vital I'm curious to know why driver
authors haven't been screaming about it until now. I'm not dismissing
what you want, but just waving your hand and saying "it's not hard"
really won't do.

cheers

andrew