Re: Proposal: RETURNING primary_key()

On 3/11/2016 12:40 AM, Craig Ringer wrote:
>
> That's why (sorry, Igal) I'd like to see some more tests for cases
> other than identity columns. How is GENERATED ALWAYS handled, if
> supported? What about if it's on a UNIQUE column? How about a PRIMARY
> KEY whose value is assigned by a DEFAULT or by a trigger?
I was using Oracle 11g XE, GENERATED ALWAYS was not available. This is
the code I used for Oracle:

CREATE TABLE jdbc (j_name VARCHAR2(64) NOT NULL, j_id NUMBER(10) NOT
NULL);

CREATE SEQUENCE jdbc_seq;

CREATE OR REPLACE TRIGGER jdbc_seq_trigger
BEFORE INSERT ON jdbc
FOR EACH ROW
WHEN (new.j_id IS NULL)
BEGIN
SELECT jdbc_seq.NEXTVAL
INTO :new.j_id
FROM dual;
END;
/

For DB2 the type is indeed GENERATED ALWAYS AS IDENTITY:

j_id INT GENERATED ALWAYS AS IDENTITY

Originally the name was ID but when both DB2 and MS/jTDS returned a
column named "ID" I realized that it might come from the column name, so
I modified the column name. DB2 was indeed returning the column name,
while MS/jTDS returns a column named "ID" regardless of the actual
column name.

>
> Based on the rather funky behaviour Igal found I suspect the answer
> will be "nothing much" for all of those, i.e. it just doesn't work
> with other drivers/vendors. But I'd like to know.
I agree, but I can test it if you give me the SQL commands. I do want
to remove all of that horrible software from my workstation as soon as
possible, but it can wait if more testing is required.

> 2) Same for multicolumn keys: Pg just returns (col1, col2) ==
> (42, 146). Then client would be able to locate the row via "where
> col1=42 and col2=146
>
>
> Yeah, I was wondering about composite PKs. I think Igal focused only
> on generated synthetic keys, which are after all overwhelmingly common
> case when getting generated keys.
If you give me the code that you want to test I will test it.

>
> 3) If multiple unique keys present, it is fine if Pg returns one
> or the another depending on the phase of the moon. Yet more
> compact key would be preferable to save on bandwidth.
>
>
> I disagree there. Behavour must be well-defined and predictable unless
> it's really unavoidable.
I agree with Craig.

> I think naming the resulting column(s) like "generated_key" /
> "generated_keys" does not make much sense. Especially, for
> multi-column keys.
>
>
> Yeah. At least in PgJDBC where it's a separate resultset (IIRC), so
> you have metadata that means you don't have to guess column names etc.

I'm not sure how multi-column keys work. In both MySQL and SQL Server
for example, you can not have more than one SEQUENCE column, so perhaps
that's their "solution".

Igal