Thread

1. Re: Roadmap for FE/BE protocol redesign

   Dave Page <dpage@vale-housing.co.uk> — 2003-03-12T14:43:22Z

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

2. Re: Roadmap for FE/BE protocol redesign

   Dave Cramer <dave@fastcrypt.com> — 2003-03-12T17:25:23Z

   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
   
   
   

3. Re: Roadmap for FE/BE protocol redesign

   Peter Eisentraut <peter_e@gmx.net> — 2003-03-12T17:46:14Z

   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
   
   
   

4. Re: Roadmap for FE/BE protocol redesign

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-12T18:21:51Z

   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
   
   

5. Re: Roadmap for FE/BE protocol redesign

   Inoue, Hiroshi <inoue@tpf.co.jp> — 2003-03-13T01:45:22Z

   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/
   
   

6. Re: Roadmap for FE/BE protocol redesign

   Dave Cramer <dave@fastcrypt.com> — 2003-03-13T02:09:05Z

   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
   
   
   

7. Re: Roadmap for FE/BE protocol redesign

   Peter Eisentraut <peter_e@gmx.net> — 2003-03-15T00:38:50Z

   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
   
   
   

8. Re: Roadmap for FE/BE protocol redesign

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-15T00:49:37Z

   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
   
   

9. Re: Roadmap for FE/BE protocol redesign

   Peter Eisentraut <peter_e@gmx.net> — 2003-03-18T00:24:27Z

   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
   
   
   

10. Re: Roadmap for FE/BE protocol redesign

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-18T06:26:22Z

    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
    
    

11. Re: Roadmap for FE/BE protocol redesign

    Christof Petig <christof@petig-baender.de> — 2003-03-18T08:47:36Z

    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
    
    
    

12. Re: Roadmap for FE/BE protocol redesign

    Kevin Brown <kevin@sysexperts.com> — 2003-03-31T11:47:08Z

    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
    
    
    

13. Re: Roadmap for FE/BE protocol redesign

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-05-24T19:02:55Z

    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
    
    

14. Re: Roadmap for FE/BE protocol redesign

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-25T02:55:13Z

    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
    
    

15. Re: Roadmap for FE/BE protocol redesign

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-05-25T03:19:56Z

    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
    
    

16. Re: Roadmap for FE/BE protocol redesign

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-25T03:59:24Z

    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