Re: Roadmap for FE/BE protocol redesign - Mailing list pgsql-hackers

Dave Page wrote:
> I don't know about JDBC, but ODBC could use it, and it would save a heck
> of a lot of pain in apps like pgAdmin that need to figure out if a column
> in an arbitrary resultset might be updateable.
> At the moment there is some nasty code in pgAdmin II that attempts to
> parse the SQL statement to figure out if the the resultset is updateable
> by trying to figure out the number of relations in the query, whether any
> of them is a view or sequence, whether there are any function calls or
> expressions in the attribute list and so on. It then has to try to figure
> out if there is a complete pkey in the resultset that can be used for the
> update, or whether it should attempt an update based on all existing
> values. That code is just plain nasty in VB. In pgAdmin III we've already
> mentioned stealing bits of the PostgreSQL parser.

I will just add a "me to" here.  This would be very useful for JDBC as 
well.  We go through the same hoops to support the jdbc spec that Dave 
does.  The jdbc spec has two features that require this level of 
information:

1) For every result set you can ask for a ResultSetMetaData object. 
This object provides you with the following methods:

getColumnCount()
isAutoIncrement(int column)
isCaseSensitive(int column)
isSearchable(int column)
isNullable(int column)
getColumnDisplaySize(int column)
getColumnLabel(int column)
getColumnName(int column)
getSchemaName(int column)
getPrecision(int column)
getScale(int column)
getTableName(int column)
getColumnTypeName(int column)
isReadOnly(int column)
isWritable(int column)
isDefinitelyWritable(int column)

Now one can state the spec is broken and it doesn't make sense to ask 
this type of information about a query (and frankly I would agree with 
you), but that doesn't mean that I don't need to support it anyway.  So 
anything that the server can do to make this easier is greatly 
appreciated.  And I believe ODBC has almost the exact same issue since 
in general the JDBC spec was copied from the ODBC spec.

2) Updateable result sets.  The jdbc spec allows the user to declare any 
select statement to be updateable.  This means that as you scroll 
through the result (the ResultSet object) you can issue modify the data 
and expect the jdbc driver to reflect that change back to the base 
tables.  The following if from the JDBC API doc:
 * A set of updater methods were added to this interface * in the JDBC 2.0 API (JavaTM 2 SDK, * Standard Edition,
version1.2). The comments regarding parameters * to the getter methods also apply to parameters to the * updater
methods.* * The updater methods may be used in two ways: * * to update a column value in the current row.  In a
scrollable*     ResultSet object, the cursor can be moved backwards *     and forwards, to an absolute position, or to
aposition *     relative to the current row. *     The following code fragment updates the NAME column *     in the
fifthrow of the ResultSet object *     rs and then uses the method updateRow *     to update the data source table from
whichrs was derived. * * *       rs.absolute(5); // moves the cursor to the fifth row of rs *
rs.updateString("NAME","AINSWORTH"); // updates the *          // NAME column of row 5 to be AINSWORTH *
rs.updateRow();// updates the row in the data source * * * to insert column values into the insert row.  An updatable *
   ResultSet object has a special row associated with *     it that serves as a staging area for building a row to be
inserted.*     The following code fragment moves the cursor to the insert row, 
 
builds *     a three-column row, and inserts it into rs and into *     the data source table using the method
insertRow.* * *       rs.moveToInsertRow(); // moves cursor to the insert row *       rs.updateString(1, "AINSWORTH");
//updates the *          // first column of the insert row to be AINSWORTH *       rs.updateInt(2,35); // updates the
secondcolumn to be 35 *       rs.updateBoolean(3, true); // updates the third row to true *       rs.insertRow(); *
 rs.moveToCurrentRow(); *
 

Now application developers love this functionality.  It allows them to 
implement fairly complex apps with very little sql knowledge.  They only 
need to know how to do a simple select statement and that is it.  The 
jdbc driver handles the rest for them automatically (updates, inserts, 
deletes).  As a jdbc maintainer I personally hate this functionality as 
it is a real pain to implement, and can't work in any but the most 
simple select statements.  But is is part of the spec and needs to be 
supported in the best manner possible.

thanks,
--Barry