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

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 |    1028files     | mode       | bit(32)                  | 59422343 |   1560 |
32files    | size       | bigint                   | 59422343 |     20 |      -1files     | uid        | integer
         | 59422343 |     23 |      -1files     | gid        | integer                  | 59422343 |     23 |
-1files    | createtime | timestamp with time zone | 59422343 |   1184 |      -1files     | modtime    | timestamp with
timezone | 59422343 |   1184 |      -1files     | device     | integer                  | 59422343 |     23 |
-1files    | inode      | integer                  | 59422343 |     23 |      -1files     | 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