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
ora 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,
Pennsylvania19073