Selecting a constant question: A summary - Mailing list pgsql-hackers

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">First a comment:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">At CONNX Solutions Inc., we believe sincerely that we should do whatever is necessary to make our
customersprosper.  This means creation of excellent tools and being responsive to customer needs.  Secondly, we believe
thatwe should treat the customers the way that we want to be treated.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">I think that the PostgreSQL group has managed the first objective, but not the second.  Of course,
thatis only an opinion, but I think that success hinges on both factors.  Our objective in this issue has also been to
improvePostgreSQL so that it can become more useful to the end users and not to denigrate the work of the engineers
thathave toiled on it.  I will also admit that frustration has caused our tone to become sharp at times.  This is
clearlya mistake on our part and for this, I apologize.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Next, the problem:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">According to SQL/CLI and ODBC 3.5, we should bind the length of a character column.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Here are some references from the relevant documentation (SQL/CLI and ODBC are clones of one
another):</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">==============================================================================</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">ANSI/ISO/IEC 9075-3-1999</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">for Information Technology</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Database Language SQL </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Part 3: Call-Level Interface (SQL/CLI)</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">Section 6.5 BindCol</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Along with function SQLBindCol from the ODBC specification</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"><a
href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlbindcol.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlbindcol.asp</a></span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">This bit should be sufficient to explain what we are after:</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">“BufferLength [Input]</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Length of the *TargetValuePtr buffer in bytes.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The driver uses BufferLength to avoid writing past the end of the *TargetValuePtr buffer when
returningvariable-length data, such as character or binary data. Note that the driver counts the null-termination
characterwhen returning character data to *TargetValuePtr. *TargetValuePtr must therefore contain space for the
null-terminationcharacter or the driver will truncate the data.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">When the driver returns fixed-length data, such as an integer or a date structure, the driver
ignoresBufferLength and assumes the buffer is large enough to hold the data. It is therefore important for the
applicationto allocate a large enough buffer for fixed-length data or the driver will write past the end of the
buffer.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">SQLBindCol returns SQLSTATE HY090 (Invalid string or buffer length) when BufferLength is less than 0
butnot when BufferLength is 0. However, if TargetType specifies a character type, an application should not set
BufferLengthto 0, because ISO CLI-compliant drivers return SQLSTATE HY090 (Invalid string or buffer length) in that
case.”</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">==============================================================================</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Now, there are times when (according to the spec) we have to defer binding.  However, this causes
greatproblems for end user tools and should only be done in what is basically a dire emergency.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">In the case of a SELECT query that selects a fixed constant of any sort, it would be a definite
improvementfor PostgreSQL to give some sort of upper maximum.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">For example:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">SELECT “Eastern Division”, sum(Inventory_level), sum(Inventory_backorder), Manager_last_name FROM
<table_name>WHERE division_id = 9 GROUP BY Manager_last_name</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Will return 3 columns of data.  The first column is of unknown length.  Imagine if you are a
spreadsheetin OpenOffice:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><a href="http://www.openoffice.org/">http://www.openoffice.org/</a></span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">which happens to support ODBC connections.  You would like to fill out a report for the president of
yourcompany.  Unfortunately, the first column is of “unknown length”</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">That makes it a bit difficult to format this spreadsheet.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">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.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">Now, you might protest, 68 bytes might be much too large.  That is true, but I know that if I
allocate68 bytes we will not have data truncation.  It is no worse than a varchar(255) field that has a largest item 15
characterswide in it.  The grid will successfully bind and we will be able to produce the report.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">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.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">It is also true that it is possible for us to work around the problem.  We certainly can know the
exacttype information about the constants in our queries and reformat the PostgreSQL queries to decorate them with
thingslike:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">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</span></font><pclass="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 
12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size:
12.0pt">But it would be very nice if the database could provide a good estimate for us so that PostgreSQL could work
likeall of the other database systems.  Code full of kludges is harder to maintain.</span></font><p
class="MsoNormal"><fontface="Times New Roman" size="3"><span style="font-size: 
12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size:
12.0pt">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
ofthing 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.</span></font></div>