Обсуждение: RETURNING clause: how to specifiy column indexes?

I am attempting to implement (in a driver)(PG JDBC) support for
specifying which column indexes (that generated keys) to return, so I'm
searching for a way to get the server to return the values of the
columns by their index, not name. By name, it is simply to append the
RETURNING clause and column names to the query:

INSERT... RETURNING foo,bar

Does anyone know how (if) this is possible by index? A standard or
server-specific syntax is fine since this is being implemented in a
server-driver.

Something like?:

INSERT... RETURNING [1],[2] (obviously this will not work)

Would I otherwise need to?:

INSERT... RETURNING *

then extract the user-requested columns? This seems inefficient as it
returns all columns / non-key ones (blobs etc).

While the values of an API that specifies the table's columns by
ordinaility may seem dubious, it is an API that I think should be
implemented anyway.

Thanks,
Ken

Ken Johanson <pg-user@kensystem.com> writes:
> While the values of an API that specifies the table's columns by
> ordinaility may seem dubious, it is an API that I think should be
> implemented anyway.

Every few weeks we get a complaint from someone who thinks that it
should be easy to rearrange the logical order of table columns.
If that comes to pass, it would be a seriously bad idea to have
encouraged applications to rely on table column numbers.  And given
the number of votes for that (probably in the hundreds by now)
versus the number of votes for this idea (one), I think column
reordering is much more likely to get done ...

            regards, tom lane

On Wed, 12 Dec 2007, Tom Lane wrote:

> Every few weeks we get a complaint from someone who thinks that it
> should be easy to rearrange the logical order of table columns.
> If that comes to pass, it would be a seriously bad idea to have
> encouraged applications to rely on table column numbers.

I think the expectation is that:

CREATE TABLE t(a int, b int);
INSERT INTO t(b,a) VALUES (1,2) RETURNING *;

will return 1,2 instead of 2,1 as it does now.  In this case the op is
not expecting that the (potentially reorganized) table order is
driving the results, but the order that they've actually specified the
columns in creates the result.

Kris Jurka

Kris Jurka <books@ejurka.com> writes:
> I think the expectation is that:

> CREATE TABLE t(a int, b int);
> INSERT INTO t(b,a) VALUES (1,2) RETURNING *;

> will return 1,2 instead of 2,1 as it does now.

Hmm ... I see your point, but on what grounds could one argue that
a "*" targetlist here should return something different from what
"SELECT * FROM t" would return?

I'd say that an app that wants that should write

INSERT INTO t(b,a) VALUES (1,2) RETURNING b,a;

which is surely not that hard if you've got the code to produce
the "(b,a)" part.

In any case it's not clear this is the same thing Ken is complaining
about ...

            regards, tom lane

Tom Lane wrote:
> Kris Jurka <books@ejurka.com> writes:
>> I think the expectation is that:
>
>> CREATE TABLE t(a int, b int);
>> INSERT INTO t(b,a) VALUES (1,2) RETURNING *;
>
>> will return 1,2 instead of 2,1 as it does now.
>
> Hmm ... I see your point, but on what grounds could one argue that
> a "*" targetlist here should return something different from what
> "SELECT * FROM t" would return?
>
> I'd say that an app that wants that should write
>
> INSERT INTO t(b,a) VALUES (1,2) RETURNING b,a;
>
> which is surely not that hard if you've got the code to produce
> the "(b,a)" part.
>
> In any case it's not clear this is the same thing Ken is complaining
> about ...
>

I am only seeking to have the columns returned in the order they appear
naturally. JDBC says "This array contains the indexes of the columns in
the target table that contain the auto-generated keys that should be
made available."

For the record I was not "complaining", only citing in advance the fact
that while some consider selecting the keys by index to be dubious, it
nonetheless must be done because an API requires it. Casting my question
into a complaint is another topic.

Kris Jurka wrote:
>
> I think the expectation is that:
>
> CREATE TABLE t(a int, b int);
> INSERT INTO t(b,a) VALUES (1,2) RETURNING *;
>
> will return 1,2 instead of 2,1 as it does now.  In this case the op is
> not expecting that the (potentially reorganized) table order is driving
> the results, but the order that they've actually specified the columns
> in creates the result.
>

Kris, do you have pointers to a spec that says the named-columns should
specify the index, or that it should instead be the order in the table?
My interpretation from the JDBC spec was that the latter is true, I may
be wrong...

In the case where it is table-order, then I presume in PG that the
"natural" order of the columns (even if reordering is allowed at a alter
date) is specified by data in one of the pg_* tables (pg_class,
pg_index, etc). Does anyone know if this is true/false?

If true, my next idea would be to derive the column name using a
subquery in the returning clause. But it sounds like this may have
potential security contraints (will any INSERT query always have read
access to the PG tables?). And no guarantee of the order matching in the
long term.

Is there a more elegant approach, like?:

INSERT... RETURNING (PG_LIST_KEYS(tblname))

I looked but did not find such a utility. It seems that such function
would be best implemented in the server instead of in a driver (eg.
having hardcoded subquery to the schema).

Ken

On Wed, 12 Dec 2007, Ken Johanson wrote:

> Kris, do you have pointers to a spec that says the named-columns should
> specify the index, or that it should instead be the order in the table? My
> interpretation from the JDBC spec was that the latter is true, I may be
> wrong...

No, I was actually misremembering what the JDBC spec said, although I
think it's an interesting case to consider regardless of any specs.

> In the case where it is table-order, then I presume in PG that the "natural"
> order of the columns (even if reordering is allowed at a alter date) is
> specified by data in one of the pg_* tables (pg_class, pg_index, etc). Does
> anyone know if this is true/false?

pg_attribute.attnum stores column order at the moment.  If/when
reordering is allowed, there will be another column indicating the
logical order of the colums.

> If true, my next idea would be to derive the column name using a subquery in
> the returning clause. But it sounds like this may have potential security
> contraints (will any INSERT query always have read access to the PG tables?).
> And no guarantee of the order matching in the long term.

There is no requirement that insert permission on a user table implies
read access to pg_catalog.  Still many clients will break if they can't
read pg_catalog.  For example, all of the JDBC driver's MetaData results
need to query pg tables, updatable ResultSets need to query pg tables to
know what the primary key is and so on.  So if this functionality required
access to pg_catalog that would neither be unprecedented nor unreasonable.

> Is there a more elegant approach, like?:
>
> INSERT... RETURNING (PG_LIST_KEYS(tblname))
>

You can't dynamically derive the returning clause for the same reason you
can't say "INSERT INTO (SELECT myfunc()) VALUES (...)", using myfunc to
determine the table name at runtime.  The planner needs to know all the
tables/columns/other database parts up front before executing anything.

Kris Jurka

>
>> If true, my next idea would be to derive the column name using a
>> subquery in the returning clause. But it sounds like this may have
>> potential security contraints (will any INSERT query always have read
>> access to the PG tables?). And no guarantee of the order matching in
>> the long term.
>
> There is no requirement that insert permission on a user table implies
> read access to pg_catalog.  Still many clients will break if they can't
> read pg_catalog.  For example, all of the JDBC driver's MetaData results
> need to query pg tables, updatable ResultSets need to query pg tables to
> know what the primary key is and so on.  So if this functionality
> required access to pg_catalog that would neither be unprecedented nor
> unreasonable.
>

So it sounds like this may be the best approach, do you agree? I'll try
and find the cycles to code this up although the limited value of
getGeneratedKeys by index makes me think my time would be better spent
elsewhere on the JDBC driver. For now at least. If you can respond to my
earlier query (5 Dec) about what robustness improvements are needed,
I'll start there..

Thanks,
Ken

Kris and all,

Here is the query I will call to the get the name of columns by ordinal
position. Do you see any compatibility drivers will older server
versions, or other issues?

SELECT column_name
FROM information_schema.columns
WHERE table_catalog=? AND table_schema=? AND table_name=?
ORDER BY ordinal_position

Ken

On Thu, 13 Dec 2007, Ken Johanson wrote:

> Here is the query I will call to the get the name of columns by ordinal
> position. Do you see any compatibility drivers will older server versions, or
> other issues?
>
> SELECT column_name
> FROM information_schema.columns
> WHERE table_catalog=? AND table_schema=? AND table_name=?
> ORDER BY ordinal_position
>

Using pg_catalog tables is better than using information_schema because of
the way permissions work.  For information_schema you must be the table
owner, while people who only have permissions to access a table will most
likely be able to read pg_catalog.

Kris Jurka

Kris Jurka wrote:

>
> Using pg_catalog tables is better than using information_schema because
> of the way permissions work.  For information_schema you must be the
> table owner, while people who only have permissions to access a table
> will most likely be able to read pg_catalog.
>

Do you have an equivalent query/join handy that will get the catalog and
schema and table and column names frm the pg tables?

SELECT column_name
FROM information_schema.columns
WHERE table_catalog=? AND table_schema=? AND table_name=?
ORDER BY ordinal_position