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