Re: Proposal: RETURNING primary_key() - Mailing list pgsql-hackers

From Craig Ringer
Subject Re: Proposal: RETURNING primary_key()
Date
Msg-id CAMsr+YH3jA=NfNpbBE6DqPPG2vpVzKvdU7+Wehq-JUt_qWF=6Q@mail.gmail.com
Whole thread Raw
In response to Re: Proposal: RETURNING primary_key()  ("Igal @ Lucee.org" <igal@lucee.org>)
Responses Re: Proposal: RETURNING primary_key()
Re: Proposal: RETURNING primary_key()
Re: Proposal: RETURNING primary_key()
List pgsql-hackers
On 11 March 2016 at 03:07, Igal @ Lucee.org <igal@lucee.org> wrote:

I noticed that you usually don't put html in the emails here, but I think that it's appropriate here to show the information in a clear way (also, according to my computer it's 2016).

Pretty sure we have at least one person here using mailreader software that's old enough to vote in most countries, but I tend to share the sentiment. At least when there's actually a functional reason like this :)

Thanks so much for doing this testing.
 
  I hope that it will be rendered properly:


MySQLDB2SQL Server (MS)SQL Server (jTDS)Oracle
Returned TypeSETSETROWROWROW
Column NameGENERATED_KEY[name of identity col]GENERATED_KEYSIDROWID
Column TypeUnknown (numeric)integernumericnumericROWID
ValueEach inserted value to identity columnEach inserted value to identity columnLast inserted value to identity columnLast inserted value to identity columninternal address location that does not change on UPDATE
Example(1), (2)(1), (2)(2)(2)AAAE5nAABAAALCxAAM

Some notes and observations:

It's the Wild West!  Each implementation does something completely different.

I honestly didn't expect that. I knew Oracle returned ROWID, but I have to admit I thought the others would probably just return the key column(s).

When you supply the column type, does that (with the exception of Oracle) match the column type of the generated key col?

Did you try GENERATED ALWAYS cols (where supported), UNIQUE columns with DEFAULTs, composite columns, etc?  Part of the question for Pg is what exactly we should and should not be returning.
  
(Side note:  This was my first, and hopefully my last, experience with Oracle database, and it's been a real PITA.  If I had tried it out some 20 years ago then the experience would have probably led me to sell the stock short, which would have probably ended with my bankruptcy.  Go figure...)

I rather less than fondly recall my own attempts to get Oracle Express installed and running for some test or another a while ago. Amazing that it can be that fiddly. MS-SQL on the other hand "just worked" and dropped me into the most gorgeously wonderful admin tool and SQL editor ever. 

I wonder if any of these drivers have extension options and compat flags that you have to turn on to get better behaviour like returning a set? Or if they're just that limited?

Anyway, from the sounds of this we have a fair bit of freedom to define what we want at both the Pg and driver level so long as we satisfy the basic constraint that we should return a set of generated keys in the case where a statement does an insert that adds rows to a table with a SERIAL (or an owned SEQUENCE). Seems like we could do pretty much whatever we want for multiple-generated-columns cases etc.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

pgsql-hackers by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.
Next
From: Tom Lane
Date:
Subject: Re: WIP: Upper planner pathification