Re: Proposal: RETURNING primary_key()

On 11 March 2016 at 03:07, Igal @ Lucee.org <igal(at)lucee(dot)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.

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