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:
MySQL DB2 SQL Server (MS) SQL Server (jTDS) Oracle Returned Type SET SET ROW ROW ROW Column Name GENERATED_KEY [name of identity col] GENERATED_KEYS ID ROWID Column Type Unknown (numeric) integer numeric numeric ROWID Value Each inserted value to identity column Each inserted value to identity column Last inserted value to identity column Last inserted value to identity column internal 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.
--
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.
pgsql-hackers by date: