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