> -----Original Message-----
> From: Peter Eisentraut [mailto:peter_e@gmx.net] 
> Sent: 12 March 2003 00:34
> To: Dave Page
> Cc: Tom Lane; PostgreSQL Development
> Subject: Re: [HACKERS] 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?

Well what I *really* need has been made quite clear in other posts, but,
when I say resultset in the same sentence as pgAdmin, I'm referring to
the ability to enter an arbitrary SQL query, have the results displayed
in a grid, which can then be editted. To do this pgAdmin needs to be
able to figure out enough info about the source of the data to generate
the required insert/update/delete statements.

It also happens that ODBC, JDBC etc, also need the same information to
meet their specs.

Regards, Dave.
Wed, 12 Mar 2003 14:43:22 -0000
"Dave Page" <dpage@vale-housing.co.uk>
Re: Roadmap for FE/BE protocol redesign
On Wed, 2003-03-12 at 12:46, Peter Eisentraut wrote:
> Dave Page writes:
> 
> > Well what I *really* need has been made quite clear in other posts, but,
> > when I say resultset in the same sentence as pgAdmin, I'm referring to
> > the ability to enter an arbitrary SQL query, have the results displayed
> > in a grid, which can then be editted. To do this pgAdmin needs to be
> > able to figure out enough info about the source of the data to generate
> > the required insert/update/delete statements.
> 
> Right.  But since you can't really write a literal SQL statement that does
> an update that refers to a previous query, you are already doing a fair
> amount of internal magic anyway, so if the meta-data is determined by
> magic as well, that seems consistent.
> 
> What you need is an updateable cursor on the server side.  It has all the
> facilities you need, including standardized ways to find out the
> updatability metadata.  Please concentrate on that and do not attempt to
> clutter the wire protocol with data that will not withstand a throrough
> investigation of semantics.

And I have offered to pay for this work to be done. Someone?
-- 
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting


12 Mar 2003 12:25:23 -0500
Dave Cramer <dave@fastcrypt.com>
Re: Roadmap for FE/BE protocol redesign
Dave Page writes:

> Well what I *really* need has been made quite clear in other posts, but,
> when I say resultset in the same sentence as pgAdmin, I'm referring to
> the ability to enter an arbitrary SQL query, have the results displayed
> in a grid, which can then be editted. To do this pgAdmin needs to be
> able to figure out enough info about the source of the data to generate
> the required insert/update/delete statements.

Right.  But since you can't really write a literal SQL statement that does
an update that refers to a previous query, you are already doing a fair
amount of internal magic anyway, so if the meta-data is determined by
magic as well, that seems consistent.

What you need is an updateable cursor on the server side.  It has all the
facilities you need, including standardized ways to find out the
updatability metadata.  Please concentrate on that and do not attempt to
clutter the wire protocol with data that will not withstand a throrough
investigation of semantics.

-- 
Peter Eisentraut   peter_e@gmx.net


Wed, 12 Mar 2003 18:46:14 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Roadmap for FE/BE protocol redesign
Peter Eisentraut <peter_e@gmx.net> writes:
> What you need is an updateable cursor on the server side.  It has all the
> facilities you need, including standardized ways to find out the
> updatability metadata.  Please concentrate on that and do not attempt to
> clutter the wire protocol with data that will not withstand a throrough
> investigation of semantics.

So are you voting against adding any attribute-ID info to
RowDescription?  While I'm not that thrilled with it myself, it seems
relatively harmless as long as we can keep the overhead down.  I'm
okay with attrelid/attnum, but would gripe about including more than that.

You're probably right that updatable cursors would be a nicer solution
in the long run, but I don't really see that happening in the next
release or two.

			regards, tom lane

Wed, 12 Mar 2003 13:21:51 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Roadmap for FE/BE protocol redesign 
Peter Eisentraut wrote:
> 
> Dave Page writes:
> 
> > Well what I *really* need has been made quite clear in other
> > posts, but, when I say resultset in the same sentence as
> > pgAdmin, I'm referring to the ability to enter an arbitrary
> > SQL query, have the results displayed in a grid, which can
> > then be editted. To do this pgAdmin needs to be able to
> > figure out enough info about the source of the data to generate
> > the required insert/update/delete statements.
> 
> Right.  But since you can't really write a literal SQL statement
> that does an update that refers to a previous query, you are
> already doing a fair amount of internal magic anyway, so if the
> meta-data is determined by magic as well, that seems consistent.

Psqlodbc driver has to parse the queries in order to
implement driver side updatable cursors unwillingly.
I'm very suspicios if it should be the driver's job
because it's very hard and ineffective to parse and
analyze the queries in the same way as the backend does.
 
> What you need is an updateable cursor on the server side.
> It has all the facilities you need,

Really ? How did you confirm it ?

> including standardized ways to find out the
> updatability metadata.  Please concentrate on that and do not attempt to
> clutter the wire protocol with data that will not withstand a throrough
> investigation of semantics.

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

Thu, 13 Mar 2003 10:45:22 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Roadmap for FE/BE protocol redesign
On Wed, 2003-03-12 at 20:45, Hiroshi Inoue wrote:
> Peter Eisentraut wrote:
> > 
> > Dave Page writes:
> > 
> > > Well what I *really* need has been made quite clear in other
> > > posts, but, when I say resultset in the same sentence as
> > > pgAdmin, I'm referring to the ability to enter an arbitrary
> > > SQL query, have the results displayed in a grid, which can
> > > then be editted. To do this pgAdmin needs to be able to
> > > figure out enough info about the source of the data to generate
> > > the required insert/update/delete statements.
> > 
> > Right.  But since you can't really write a literal SQL statement
> > that does an update that refers to a previous query, you are
> > already doing a fair amount of internal magic anyway, so if the
> > meta-data is determined by magic as well, that seems consistent.
> 
> Psqlodbc driver has to parse the queries in order to
> implement driver side updatable cursors unwillingly.
> I'm very suspicios if it should be the driver's job
> because it's very hard and ineffective to parse and
> analyze the queries in the same way as the backend does.

jdbc has to do this too, and the backend is in a much better position to
do the parsing IMO as well.
>  
> > What you need is an updateable cursor on the server side.
> > It has all the facilities you need,
> 
> Really ? How did you confirm it ?
> 
> > including standardized ways to find out the
> > updatability metadata.  Please concentrate on that and do not attempt to
> > clutter the wire protocol with data that will not withstand a throrough
> > investigation of semantics.
> 
> regards,
> Hiroshi Inoue
> 	http://www.geocities.jp/inocchichichi/psqlodbc/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faqs/FAQ.html
-- 
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting


12 Mar 2003 21:09:05 -0500
Dave Cramer <dave@fastcrypt.com>
Re: Roadmap for FE/BE protocol redesign
Tom Lane writes:

> So are you voting against adding any attribute-ID info to
> RowDescription?  While I'm not that thrilled with it myself, it seems
> relatively harmless as long as we can keep the overhead down.  I'm
> okay with attrelid/attnum, but would gripe about including more than that.

At the beginning of this thread you raised a number of points where the
identity of the column of origin is not well-defined.  I haven't seen an
answer to that.  Whether the identity of the column is provided through
numbers or, as was originally requested, through names seems to be
irrelevant for those questions.

Now assume someone wanted to define a method to identify the column of
origin for a limited set of query types.  Would you align the definition
with what the current planner and executor structures can easily give you
or would you use a more "mathematical" definition?  And assuming it's the
latter, do you feel confident that that definition will not constrain
development of the planner and executor structures in the future?

-- 
Peter Eisentraut   peter_e@gmx.net


Sat, 15 Mar 2003 01:38:50 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Roadmap for FE/BE protocol redesign 
Peter Eisentraut <peter_e@gmx.net> writes:
> At the beginning of this thread you raised a number of points where the
> identity of the column of origin is not well-defined.  I haven't seen an
> answer to that.

Yes, Dave did answer --- basically, he's happy with not providing any
column identity data in the cases where it's not obvious what the answer
should be.  And in particular he doesn't want the mechanism to drill
down into view definitions (which is less than obviously right to me,
but if that's what he wants it sure eliminates a lot of definitional
issues).

Given that agreement I don't have a problem with providing the
functionality.  It will take a few more lines in the parser, but not an
unreasonable amount I think.

> Would you align the definition
> with what the current planner and executor structures can easily give you
> or would you use a more "mathematical" definition?  And assuming it's the
> latter, do you feel confident that that definition will not constrain
> development of the planner and executor structures in the future?

I'm not too concerned about it given the before-view-expansion proviso.
Once the rewriter and planner go into action, the contents of the query
tree do start to look rather implementation-dependent, but what the
parser does is pretty well constrained by the SQL spec.

			regards, tom lane

Fri, 14 Mar 2003 19:49:37 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Roadmap for FE/BE protocol redesign 
Tom Lane writes:

> Yes, Dave did answer --- basically, he's happy with not providing any
> column identity data in the cases where it's not obvious what the answer
> should be.  And in particular he doesn't want the mechanism to drill
> down into view definitions (which is less than obviously right to me,
> but if that's what he wants it sure eliminates a lot of definitional
> issues).

I don't get it.  Say I execute SELECT a, b, c FROM foo;.  In order to
update that query, the application needs to create some update statement,
say UPDATE foo SET a = entered_value;.  So the application already knows
that "foo" is the table and "a" is the column.  So if the application
wants to know about details on the column "a", it can execute

SELECT whatever FROM pg_attribute, pg_class WHERE relname = 'foo' AND attname = 'a';

With this proposed change, it can replace that with

SELECT whatever FROM pg_attribute, pg_class WHERE oid = X AND attnum = Y;

With the difference that the first version always works and the second
version sometimes works, and when that sometimes is is determined by the
rule that it should be "obvious".  That doesn't seem right to me.

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 18 Mar 2003 01:24:27 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Roadmap for FE/BE protocol redesign 
Peter Eisentraut <peter_e@gmx.net> writes:
> ... So the application already knows
> that "foo" is the table and "a" is the column.  So if the application
> wants to know about details on the column "a", it can execute
> SELECT whatever FROM pg_attribute, pg_class WHERE relname = 'foo' AND attname = 'a';
> With this proposed change, it can replace that with
> SELECT whatever FROM pg_attribute, pg_class WHERE oid = X AND attnum = Y;

Dave will correct me if I'm wrong --- but I think the issue here is that
the client-side library (think ODBC or JDBC) needs to gain this level of
understanding of a query that is presented to it as an SQL-source
string.  So no, it doesn't already know that "foo" is the table and "a"
is the column.  To find that out, it has to duplicate a lot of backend
code.

			regards, tom lane

Tue, 18 Mar 2003 01:26:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Roadmap for FE/BE protocol redesign 
Peter Eisentraut wrote:
> I don't get it.  Say I execute SELECT a, b, c FROM foo;.  In order to
> update that query, the application needs to create some update statement,
> say UPDATE foo SET a = entered_value;.  So the application already knows
> that "foo" is the table and "a" is the column.  So if the application
> wants to know about details on the column "a", it can execute
> 
> SELECT whatever FROM pg_attribute, pg_class WHERE relname = 'foo' AND attname = 'a';

Once the statement uses aliases for the result columns this can go 
wrong. And to determine the correct table is difficult at best.

> With this proposed change, it can replace that with
> 
> SELECT whatever FROM pg_attribute, pg_class WHERE oid = X AND attnum = Y;
> 
> With the difference that the first version always works and the second
> version sometimes works, and when that sometimes is is determined by the
> rule that it should be "obvious".  That doesn't seem right to me.

I have the impression that the first solution sometimes works (and other 
times gets it really wrong, you can't tell without parsing the query) 
and the second solution works when possible (and gives no answer 
elsewhere). The only cases I expect the second one to fail are: natural 
joins (where the table is ambigious), expressions and function calls. Of 
course you can't expect a sane result in these cases.

On a second thought I do not want the column name (or alias) to get 
discarded (because otherwise you can't get the alias), but *DBC 
desperately needs attrelid and attnum.

    Christof


Tue, 18 Mar 2003 09:47:36 +0100
Christof Petig <christof@petig-baender.de>
Re: Roadmap for FE/BE protocol redesign
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > ... So the application already knows
> > that "foo" is the table and "a" is the column.  So if the application
> > wants to know about details on the column "a", it can execute
> > SELECT whatever FROM pg_attribute, pg_class WHERE relname = 'foo' AND attname = 'a';
> > With this proposed change, it can replace that with
> > SELECT whatever FROM pg_attribute, pg_class WHERE oid = X AND attnum = Y;
> 
> Dave will correct me if I'm wrong --- but I think the issue here is that
> the client-side library (think ODBC or JDBC) needs to gain this level of
> understanding of a query that is presented to it as an SQL-source
> string.  So no, it doesn't already know that "foo" is the table and "a"
> is the column.  To find that out, it has to duplicate a lot of backend
> code.

Perhaps, rather than changing the protocol to include attrelid/attnum
information for the query, we should instead implement a command that
would yield the query's result information directly:

fileinfo=> QUERY RESULTS SELECT * from files;
 classname |  attname   |         atttype          | classid  | typeid | typemod 
-----------+------------+--------------------------+----------+--------+---------
 files     | filename   | character varying(1024)  | 59422343 |   1043 |    1028
 files     | mode       | bit(32)                  | 59422343 |   1560 |      32
 files     | size       | bigint                   | 59422343 |     20 |      -1
 files     | uid        | integer                  | 59422343 |     23 |      -1
 files     | gid        | integer                  | 59422343 |     23 |      -1
 files     | createtime | timestamp with time zone | 59422343 |   1184 |      -1
 files     | modtime    | timestamp with time zone | 59422343 |   1184 |      -1
 files     | device     | integer                  | 59422343 |     23 |      -1
 files     | inode      | integer                  | 59422343 |     23 |      -1
 files     | nlinks     | integer                  | 59422343 |     23 |      -1
(10 rows)

Each tuple result of the QUERY RESULTS command (some other name for it
could be selected, this is just an example) would describe a column
that would be returned by the query being examined, and the tuples
would be sent in the left-to-right order that the columns they
describe would appear (or, alternatively, another column like attnum
could be sent that numbers the columns, starting with 1).

When a particular piece of information is unavailable, a NULL is sent
in its place -- just as you'd expect.  An example of such a column
would be:

fileinfo=> QUERY RESULTS SELECT CAST(1 AS integer), CAST(2 AS bigint);
 classname | attname | atttype | classid | typeid | typemod 
-----------+---------+---------+---------+--------+---------
           | int4    | integer |         |     23 |      -1
           | int8    | bigint  |         |     20 |      -1
(2 rows)


(psql shows NULLs as no value, so that's what I'm showing above).



Anyway, it's just a thought, but it's something that could be used by
literally everything.  And, of course, QUERY RESULTS should be able to
operate recursively, thus "QUERY RESULTS QUERY RESULTS ... SELECT ..."
(which could be made a special case if necessary).

The downside of this is that client libraries that wanted information
about what a query would return would have to send two queries through
the parser.  But the upside is that you take that hit only if you need
the information.  And if you plan to issue a particular query a lot,
you can issue the above command once and you're done.

I have no idea how hard this would be to implement.  I'm assuming that
EXPLAIN goes through a lot of the same code paths that this does, so
it may make sense to make this a variant of EXPLAIN (e.g., EXPLAIN
RESULTS SELECT...).




-- 
Kevin Brown					      kevin@sysexperts.com


Mon, 31 Mar 2003 03:47:08 -0800
Kevin Brown <kevin@sysexperts.com>
Re: Roadmap for FE/BE protocol redesign

I like this idea because it used our existing query API to return result
information.

Added to TODO:

* Allow clients to get data types, typmod, schema.table.column names from
  result sets, either via the backend protocol or a new QUERYINFO command



---------------------------------------------------------------------------

Kevin Brown wrote:
> Tom Lane wrote:
> > Peter Eisentraut <peter_e@gmx.net> writes:
> > > ... So the application already knows
> > > that "foo" is the table and "a" is the column.  So if the application
> > > wants to know about details on the column "a", it can execute
> > > SELECT whatever FROM pg_attribute, pg_class WHERE relname = 'foo' AND attname = 'a';
> > > With this proposed change, it can replace that with
> > > SELECT whatever FROM pg_attribute, pg_class WHERE oid = X AND attnum = Y;
> > 
> > Dave will correct me if I'm wrong --- but I think the issue here is that
> > the client-side library (think ODBC or JDBC) needs to gain this level of
> > understanding of a query that is presented to it as an SQL-source
> > string.  So no, it doesn't already know that "foo" is the table and "a"
> > is the column.  To find that out, it has to duplicate a lot of backend
> > code.
> 
> Perhaps, rather than changing the protocol to include attrelid/attnum
> information for the query, we should instead implement a command that
> would yield the query's result information directly:
> 
> fileinfo=> QUERY RESULTS SELECT * from files;
>  classname |  attname   |         atttype          | classid  | typeid | typemod 
> -----------+------------+--------------------------+----------+--------+---------
>  files     | filename   | character varying(1024)  | 59422343 |   1043 |    1028
>  files     | mode       | bit(32)                  | 59422343 |   1560 |      32
>  files     | size       | bigint                   | 59422343 |     20 |      -1
>  files     | uid        | integer                  | 59422343 |     23 |      -1
>  files     | gid        | integer                  | 59422343 |     23 |      -1
>  files     | createtime | timestamp with time zone | 59422343 |   1184 |      -1
>  files     | modtime    | timestamp with time zone | 59422343 |   1184 |      -1
>  files     | device     | integer                  | 59422343 |     23 |      -1
>  files     | inode      | integer                  | 59422343 |     23 |      -1
>  files     | nlinks     | integer                  | 59422343 |     23 |      -1
> (10 rows)
> 
> Each tuple result of the QUERY RESULTS command (some other name for it
> could be selected, this is just an example) would describe a column
> that would be returned by the query being examined, and the tuples
> would be sent in the left-to-right order that the columns they
> describe would appear (or, alternatively, another column like attnum
> could be sent that numbers the columns, starting with 1).
> 
> When a particular piece of information is unavailable, a NULL is sent
> in its place -- just as you'd expect.  An example of such a column
> would be:
> 
> fileinfo=> QUERY RESULTS SELECT CAST(1 AS integer), CAST(2 AS bigint);
>  classname | attname | atttype | classid | typeid | typemod 
> -----------+---------+---------+---------+--------+---------
>            | int4    | integer |         |     23 |      -1
>            | int8    | bigint  |         |     20 |      -1
> (2 rows)
> 
> 
> (psql shows NULLs as no value, so that's what I'm showing above).
> 
> 
> 
> Anyway, it's just a thought, but it's something that could be used by
> literally everything.  And, of course, QUERY RESULTS should be able to
> operate recursively, thus "QUERY RESULTS QUERY RESULTS ... SELECT ..."
> (which could be made a special case if necessary).
> 
> The downside of this is that client libraries that wanted information
> about what a query would return would have to send two queries through
> the parser.  But the upside is that you take that hit only if you need
> the information.  And if you plan to issue a particular query a lot,
> you can issue the above command once and you're done.
> 
> I have no idea how hard this would be to implement.  I'm assuming that
> EXPLAIN goes through a lot of the same code paths that this does, so
> it may make sense to make this a variant of EXPLAIN (e.g., EXPLAIN
> RESULTS SELECT...).
> 
> 
> 
> 
> -- 
> Kevin Brown					      kevin@sysexperts.com
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

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

Sat, 24 May 2003 15:02:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Roadmap for FE/BE protocol redesign
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Added to TODO:

> * Allow clients to get data types, typmod, schema.table.column names from
>   result sets, either via the backend protocol or a new QUERYINFO command

Uh ... this is already done, at least as far as the protocol is
concerned.  We do need to backfill support in libpq, etc.

			regards, tom lane

Sat, 24 May 2003 22:55:13 -0400
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:
> > Added to TODO:
> 
> > * Allow clients to get data types, typmod, schema.table.column names from
> >   result sets, either via the backend protocol or a new QUERYINFO command
> 
> Uh ... this is already done, at least as far as the protocol is
> concerned.  We do need to backfill support in libpq, etc.

Oh, really.  That is great.  I didn't realize you had handled that. I
will update the TODO:

* Update clients to use data types, typmod, schema.table.column names of
  result sets using new query protocol

I assume we will need new libpq functions?

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

Sat, 24 May 2003 23:19:56 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Roadmap for FE/BE protocol redesign
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Uh ... this is already done, at least as far as the protocol is
>> concerned.  We do need to backfill support in libpq, etc.

> I assume we will need new libpq functions?

Yeah.

			regards, tom lane

Sat, 24 May 2003 23:59:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Roadmap for FE/BE protocol redesign 