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(at)gmx(dot)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(at)sysexperts(dot)com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

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