> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
> Sent: 10 March 2003 18:51
> To: pgsql-hackers@postgresql.org; pgsql-interfaces@postgresql.org
> Subject: [HACKERS] Roadmap for FE/BE protocol redesign
> 
> 
> This is an attempt to lay out a road map for updating the 
> frontend/backend protocol in 7.4.  I don't at this point want 
> to get into details on any one of the TODO items, just get 
> consensus that this is the set of tasks to be tackled.  Are 
> there any areas that I've missed (that require protocol changes)?

What about the addition of pg_attribute.attrelid &
pg_attribute.attname/attnum in RowDesription messages to identify the
underlying attribute (where appropriate)?

I just looked at an issue in the ODBC driver where this would help - it
seems we return incorrect column metadata from query results, I guess (I
haven't look at the code yet) because it's somewhat tricky to figure
out.

Regards, Dave.
Tue, 11 Mar 2003 00:12:19 -0000
"Dave Page" <dpage@vale-housing.co.uk>
Re: Roadmap for FE/BE protocol redesign
"Dave Page" <dpage@vale-housing.co.uk> writes:
> What about the addition of pg_attribute.attrelid &
> pg_attribute.attname/attnum in RowDesription messages to identify the
> underlying attribute (where appropriate)?

Well, we can talk about it, but I still think that any frontend that
relies on such information is broken by design.  (And if that means the
JDBC spec is broken, then the JDBC spec is broken.)

Just to start with, if I do "SELECT * FROM view", am I going to see the
info associated with the view column, or with the hypothetical
underlying table column?  (Actually, didn't I already make a list of a
bunch of ways in which this concept is underspecified?  AFAIR, you
didn't suggest answers to any of those questions ... but we need answers
to all of them if we are going to implement the feature.)

			regards, tom lane

Mon, 10 Mar 2003 19:48:41 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Roadmap for FE/BE protocol redesign 
Tom Lane wrote:
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > What about the addition of pg_attribute.attrelid &
> > pg_attribute.attname/attnum in RowDesription messages to identify the
> > underlying attribute (where appropriate)?
> 
> Well, we can talk about it, but I still think that any frontend that
> relies on such information is broken by design.  (And if that means the
> JDBC spec is broken, then the JDBC spec is broken.)
> 
> Just to start with, if I do "SELECT * FROM view", am I going to see the
> info associated with the view column, or with the hypothetical
> underlying table column?  (Actually, didn't I already make a list of a
> bunch of ways in which this concept is underspecified?  AFAIR, you
> didn't suggest answers to any of those questions ... but we need answers
> to all of them if we are going to implement the feature.)

I was willing to add a hack to enable default column labels to be
"table.column" --- that seemed like the least obtrusive.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 10 Mar 2003 21:27:06 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Roadmap for FE/BE protocol redesign
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I was willing to add a hack to enable default column labels to be
> "table.column" --- that seemed like the least obtrusive.

Most of the definitional issues still apply: which table name are you
going to insert, and under what conditions?

If we're revising the protocol, there's no reason to hack up the column
label to carry two pieces of info; it'd be cleaner to provide a separate
slot in the T message to carry the table name.  I just want to see a
reasonably complete spec for what the feature is supposed to do, before
we buy into it ...

			regards, tom lane

Mon, 10 Mar 2003 21:37:45 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Roadmap for FE/BE protocol redesign 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I was willing to add a hack to enable default column labels to be
> > "table.column" --- that seemed like the least obtrusive.
> 
> Most of the definitional issues still apply: which table name are you
> going to insert, and under what conditions?
> 
> If we're revising the protocol, there's no reason to hack up the column
> label to carry two pieces of info; it'd be cleaner to provide a separate
> slot in the T message to carry the table name.  I just want to see a
> reasonably complete spec for what the feature is supposed to do, before
> we buy into it ...

I don't think we can get a complete spec, and hence the _hack_ idea.  :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 10 Mar 2003 21:44:01 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Roadmap for FE/BE protocol redesign
Tom Lane wrote:
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> 
>>What about the addition of pg_attribute.attrelid &
>>pg_attribute.attname/attnum in RowDesription messages to identify the
>>underlying attribute (where appropriate)?
> 
> Well, we can talk about it, but I still think that any frontend that
> relies on such information is broken by design.  (And if that means the
> JDBC spec is broken, then the JDBC spec is broken.)
> 
> Just to start with, if I do "SELECT * FROM view", am I going to see the
> info associated with the view column, or with the hypothetical
> underlying table column?  (Actually, didn't I already make a list of a
> bunch of ways in which this concept is underspecified?  AFAIR, you
> didn't suggest answers to any of those questions ... but we need answers
> to all of them if we are going to implement the feature.)

The problem Dave is suggesting this as a first attempt at a solution for 
is that with ODBC, a frontend (i.e. OpenOffice) asks the ODBC driver 
which columns are NULLable, etc.  And the ODBC driver is getting the 
info wrong, then passing back the incorrect info.

So, when a person goes to insert a row into a table with a 
SERIAL/SEQUENCE based column, OpenOffice has been told the column isn't 
NULLable and forces the user to enter a value.  Voila, it doesn't work 
with sequences.  :(

It's likely possible to add to the ODBC driver some way of getting the 
info right, but Dave is also looking for a way of making this easier 
into the future for similar problems.  i.e. Let the database explicitly 
have info about what each column can do.

That's my understanding of it anyway.

:-)

Regards and best wishes,

Justin Clift


> 			regards, tom lane


-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi


Tue, 11 Mar 2003 13:17:04 +1030
Justin Clift <justin@postgresql.org>
Re: Roadmap for FE/BE protocol redesign
It's rumoured that Tom Lane once said:
> "Dave Page" <dpage@vale-housing.co.uk> writes:
>> What about the addition of pg_attribute.attrelid &
>> pg_attribute.attname/attnum in RowDesription messages to identify the
>> underlying attribute (where appropriate)?
>
> Well, we can talk about it, but I still think that any frontend that
> relies on such information is broken by design.  (And if that means the
> JDBC spec is broken, then the JDBC spec is broken.)

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.
The addition of the base column identifier (the pg_attribute.oid would
have sufficed, but I can live with attrelid, attname, or even nspnam,
relname & attname or similar) would make this trivil, and allow interfaces
like ODBC, JDBC, OLE-DB and Npgsql to gain easy access to any metadata
they might require.
> Just to start with, if I do "SELECT * FROM view", am I going to see the
> info associated with the view column, or with the hypothetical
> underlying table column?

The view. We don't care where the data originally came from, only where it
came from as far as the query creating the resultset is concerned. Of
course, updateable views would make this irrelevant anyway...
(Actually, didn't I already make a list of a
> bunch of ways in which this concept is underspecified?  AFAIR, you
> didn't suggest answers to any of those questions ... but we need
> answers to all of them if we are going to implement the feature.)

I guess I must have missed that thread.

Regards, Dave



Tue, 11 Mar 2003 09:22:18 -0000 (GMT)
"Dave Page" <dpage@vale-housing.co.uk>
Re: Roadmap for FE/BE protocol redesign
It's rumoured that Bruce Momjian once said:
> Tom Lane wrote:
>> "Dave Page" <dpage@vale-housing.co.uk> writes:
>> > What about the addition of pg_attribute.attrelid &
>> > pg_attribute.attname/attnum in RowDesription messages to identify
>> > the underlying attribute (where appropriate)?
>>
>> Well, we can talk about it, but I still think that any frontend that
>> relies on such information is broken by design.  (And if that means
>> the JDBC spec is broken, then the JDBC spec is broken.)
>>
>> Just to start with, if I do "SELECT * FROM view", am I going to see
>> the info associated with the view column, or with the hypothetical
>> underlying table column?  (Actually, didn't I already make a list of a
>> bunch of ways in which this concept is underspecified?  AFAIR, you
>> didn't suggest answers to any of those questions ... but we need
>> answers to all of them if we are going to implement the feature.)
>
> I was willing to add a hack to enable default column labels to be
> "table.column" --- that seemed like the least obtrusive.

That would help, but not in the cases that cause the most grief - for
example when the column has been aliased in the original query - that
should override the label of course, but then we still need to parse the
SQL at the client to figure out what's going on.
Regards, Dave.



Tue, 11 Mar 2003 09:25:07 -0000 (GMT)
"Dave Page" <dpage@vale-housing.co.uk>
Re: Roadmap for FE/BE protocol redesign
It's rumoured that Bruce Momjian once said:
> Tom Lane wrote:
>> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> > I was willing to add a hack to enable default column labels to be
>> > "table.column" --- that seemed like the least obtrusive.
>>
>> Most of the definitional issues still apply: which table name are you
>> going to insert, and under what conditions?
>>
>> If we're revising the protocol, there's no reason to hack up the
>> column label to carry two pieces of info; it'd be cleaner to provide a
>> separate slot in the T message to carry the table name.  I just want
>> to see a reasonably complete spec for what the feature is supposed to
>> do, before we buy into it ...
>
> I don't think we can get a complete spec, and hence the _hack_ idea.
> :-)

Well, what would constitute a complete spec? I think I've told the group
what I would like to be able to do, what unanswered questions can I
(hopefully :-) ) answer?
Regards, Dave.



Tue, 11 Mar 2003 09:34:33 -0000 (GMT)
"Dave Page" <dpage@vale-housing.co.uk>
Re: Roadmap for FE/BE protocol redesign
"Dave Page" <dpage@vale-housing.co.uk> writes:
> Well, what would constitute a complete spec? I think I've told the group
> what I would like to be able to do, what unanswered questions can I
> (hopefully :-) ) answer?

I'm still unclear on exactly what your needs are.  In the first place,
are you expecting to obtain data from arbitrary SELECT statements, or
only from statements of the form "SELECT * FROM single_table"?  You've
also been confusing as to whether you want transparency of views (ie,
does a select from a view return data about the view's nominal columns
or about the underlying base table columns?).  What about cases
involving aggregates or grouping --- there may be simple Vars in the
target list, but they can hardly be thought to represent updatable values.

Also, you muttered something about inferring primary keys and number of
relations in query; seems like this feature isn't solving your problem
as far as that goes, because the set of attributes visible in the target
list isn't much help for either.

			regards, tom lane

Tue, 11 Mar 2003 10:06:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Roadmap for FE/BE protocol redesign 
Tom Lane wrote:
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > Well, what would constitute a complete spec? I think I've told the group
> > what I would like to be able to do, what unanswered questions can I
> > (hopefully :-) ) answer?
> 
> I'm still unclear on exactly what your needs are.  In the first place,
> are you expecting to obtain data from arbitrary SELECT statements, or
> only from statements of the form "SELECT * FROM single_table"?  You've
> also been confusing as to whether you want transparency of views (ie,
> does a select from a view return data about the view's nominal columns
> or about the underlying base table columns?).  What about cases
> involving aggregates or grouping --- there may be simple Vars in the
> target list, but they can hardly be thought to represent updatable values.

These questions can't possibly be unique to PG -- other database
vendors must have answered these questions for their implementations
of ODBC/JDBC too, or their databases would give ODBC and JDBC client
authors the same kinds of trouble.

So ... how have these questions been answered by other database
vendors?

The spec may be underspecified on these points, but since the entire
purpose of ODBC and JDBC is to provide a database-independent
framework for application authors to use, it follows that
compatibility with other implementations is highly desirable.  So
doing more or less what other database vendors have done in response
to these underspecified issues is probably a sensible course of action
when there's no other obviously better answer.


-- 
Kevin Brown					      kevin@sysexperts.com

Tue, 11 Mar 2003 14:55:23 -0800
Kevin Brown <kevin@sysexperts.com>
Re: Roadmap for FE/BE protocol redesign
Dave Page writes:

> 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.

Strictly speaking, result sets are never updatable, because there's no way
you can refer to a result set and tell the system to update it.  So let's
hear what you *really* need and then consider interfaces for *that*.
Maybe updatable views or updatable cursors?

-- 
Peter Eisentraut   peter_e@gmx.net


Wed, 12 Mar 2003 01:34:15 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Roadmap for FE/BE protocol redesign
Kevin Brown <kevin@sysexperts.com> writes:
> doing more or less what other database vendors have done in response
> to these underspecified issues is probably a sensible course of action
> when there's no other obviously better answer.

A good point, indeed.  Who wants to do the legwork to check up on this?

			regards, tom lane

Wed, 12 Mar 2003 01:01:06 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Roadmap for FE/BE protocol redesign 
Peter Eisentraut wrote:
> 
> Dave Page writes:
> 
> > 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.
> 
> Strictly speaking, result sets are never updatable,
> because there's no way you can refer to a result set
> and tell the system to update it.
> So let's hear what you *really* need and then consider
> interfaces for *that*. Maybe updatable views or
> updatable cursors?

The word *result set* is used variously
and *updatable cursors* has its meaning
in some middleware applications.

The word *cursor* is used variously too.
For example, ODBC cursors don't necessarily
mean(correspond to) the dbms cursors.
If there's a suitable cursor, the ODBC driver
may conveniently use it to implement ODBC
cursors. Otherwise the ODBC driver may implement
ODBC cursors by itself. PostgreSQL has paid
attention to the cursor support little and I
don't expect much of server-side cursors.

regards,
Hiroshi Inoue
	http://www.geocities.jp/inocchichichi/psqlodbc/

Wed, 12 Mar 2003 17:02:19 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Roadmap for FE/BE protocol redesign


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, version 1.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 a position
  *     relative to the current row.
  *     The following code fragment updates the NAME column
  *     in the fifth row of the ResultSet object
  *     rs and then uses the method updateRow
  *     to update the data source table from which rs 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 second column 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



Wed, 12 Mar 2003 00:43:48 -0800
Barry Lind <blind@xythos.com>
Re: Roadmap for FE/BE protocol redesign
Barry Lind kirjutas K, 12.03.2003 kell 10:43:
> 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:
...

> 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.

And IIRC they both are mostly the same as ANSI/ISO SQL/CLI spec. The CLI
being Call Level Interface.

> 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:
...
> 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.

Updatable cursors should probably come out as a side benefit when
backend gains the ability to have updatable views. If you have done some
work towards implementing it in JDBC driver, some it will probably be
handy for anyone who is going to implement updatable views.

-----------------
Hannu


12 Mar 2003 13:08:28 +0200
Hannu Krosing <hannu@tm.ee>
Re: Roadmap for FE/BE protocol redesign
Justin Clift wrote:

[ ... ]

> The problem Dave is suggesting this as a first attempt at a solution for 
> is that with ODBC, a frontend (i.e. OpenOffice) asks the ODBC driver 
> which columns are NULLable, etc.  And the ODBC driver is getting the 
> info wrong, then passing back the incorrect info.

And that's probably why OpenOffice doesn't allow you to update a view, 
event if there are rules allowing this from psql ...

This, in my book is an EPITA for end users ...

> So, when a person goes to insert a row into a table with a 
> SERIAL/SEQUENCE based column, OpenOffice has been told the column isn't 
> NULLable and forces the user to enter a value.  Voila, it doesn't work 
> with sequences.  :(
> 
> It's likely possible to add to the ODBC driver some way of getting the 
> info right, but Dave is also looking for a way of making this easier 
> into the future for similar problems.  i.e. Let the database explicitly 
> have info about what each column can do.

I'd second that, even if it takes time ...

					Emmanuel Charpentier

-- 
Emmanuel Charpentier


Sat, 15 Mar 2003 09:27:13 +0100
Emmanuel Charpentier <charpent@bacbuc.dyndns.org>
Re: Roadmap for FE/BE protocol redesign