Selecting a constant question: A summary

First a comment:

At CONNX Solutions Inc., we believe sincerely that we should do whatever
is necessary to make our customers prosper. This means creation of
excellent tools and being responsive to customer needs. Secondly, we
believe that we should treat the customers the way that we want to be
treated.

I think that the PostgreSQL group has managed the first objective, but
not the second. Of course, that is only an opinion, but I think that
success hinges on both factors. Our objective in this issue has also
been to improve PostgreSQL so that it can become more useful to the end
users and not to denigrate the work of the engineers that have toiled on
it. I will also admit that frustration has caused our tone to become
sharp at times. This is clearly a mistake on our part and for this, I
apologize.

Next, the problem:

According to SQL/CLI and ODBC 3.5, we should bind the length of a
character column.

Here are some references from the relevant documentation (SQL/CLI and
ODBC are clones of one another):

========================================================================
======

ANSI/ISO/IEC 9075-3-1999

for Information Technology

Database Language SQL

Part 3: Call-Level Interface (SQL/CLI)

Section 6.5 BindCol

Along with function SQLBindCol from the ODBC specification

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/ht
m/odbcsqlbindcol.asp

This bit should be sufficient to explain what we are after:

"BufferLength [Input]

Length of the *TargetValuePtr buffer in bytes.

The driver uses BufferLength to avoid writing past the end of the
*TargetValuePtr buffer when returning variable-length data, such as
character or binary data. Note that the driver counts the
null-termination character when returning character data to
*TargetValuePtr. *TargetValuePtr must therefore contain space for the
null-termination character or the driver will truncate the data.

When the driver returns fixed-length data, such as an integer or a date
structure, the driver ignores BufferLength and assumes the buffer is
large enough to hold the data. It is therefore important for the
application to allocate a large enough buffer for fixed-length data or
the driver will write past the end of the buffer.

SQLBindCol returns SQLSTATE HY090 (Invalid string or buffer length) when
BufferLength is less than 0 but not when BufferLength is 0. However, if
TargetType specifies a character type, an application should not set
BufferLength to 0, because ISO CLI-compliant drivers return SQLSTATE
HY090 (Invalid string or buffer length) in that case."

========================================================================
======

Now, there are times when (according to the spec) we have to defer
binding. However, this causes great problems for end user tools and
should only be done in what is basically a dire emergency.

In the case of a SELECT query that selects a fixed constant of any sort,
it would be a definite improvement for PostgreSQL to give some sort of
upper maximum.

For example:

SELECT "Eastern Division", sum(Inventory_level),
sum(Inventory_backorder), Manager_last_name FROM <table_name> WHERE
division_id = 9 GROUP BY Manager_last_name

Will return 3 columns of data. The first column is of unknown length.
Imagine if you are a spreadsheet in OpenOffice:

http://www.openoffice.org/

which happens to support ODBC connections. You would like to fill out a
report for the president of your company. Unfortunately, the first
column is of "unknown length"

That makes it a bit difficult to format this spreadsheet.

Now, I will admit that we may not know a-priori if "Eastern Division" is
character or Unicode or MBCS. But in the worst case scenario it will be
(16 + 1) * element_width bytes in length. For some Unicode character
sets, element_width can be as much as 4, so that leaves 68 octets as an
upper possible maximum.

Now, you might protest, 68 bytes might be much too large. That is true,
but I know that if I allocate 68 bytes we will not have data truncation.
It is no worse than a varchar(255) field that has a largest item 15
characters wide in it. The grid will successfully bind and we will be
able to produce the report.

Generally speaking, grids are smart enough to automatically resize
themselves to max_length(grid_column_title, grid_column_data) and so the
report will look very nice.

It is also true that it is possible for us to work around the problem.
We certainly can know the exact type information about the constants in
our queries and reformat the PostgreSQL queries to decorate them with
things like:

SELECT "Eastern Division"::char(16),
sum(Inventory_level)::Numeric(16,4), sum(Inventory_backorder)
::Numeric(16,4), Manager_last_name FROM <table_name> WHERE division_id =
9 GROUP BY Manager_last_name

But it would be very nice if the database could provide a good estimate
for us so that PostgreSQL could work like all of the other database
systems. Code full of kludges is harder to maintain.

And so I hope that we can get off on a better foot this time. If the
answer is "No, the priority for this sort of thing is low, and we do not
consider it important for our customers." Then we will have to work
around it. Hopefully, at least, it will get put into a queue of future
enhancements.