Re: DB2-style INS/UPD/DEL RETURNING

I was talking with Jonathan Gennick about the INS/UPD/DEL RETURNING stuff,
and he recommended looking into the way DB2 handles similar functionality.
After looking into it a bit, it's more inline with what Tom's suggestion was
regarding a query from the operation rather than returning the values in the
manner currently required.

Here's DB2's syntax... does anyone have any familiarity with it?

Simply put, it's sort-of like:

SELECT * FROM (FINAL | NEW | OLD) TABLE (INSERT | UPDATE | DELETE)

I'd like to hear from anyone that's used it to see if it really is better...
logically it seems nicer, but I've never used it.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

One of the purposes of this as I understand it is to allow clients to
get back the generated key(s). I don't see enough of the syntax to
see if this is possible with the DB2 syntax below.

Dave
On 12-Mar-06, at 11:11 AM, Jonah H. Harris wrote:

> I was talking with Jonathan Gennick about the INS/UPD/DEL RETURNING
> stuff, and he recommended looking into the way DB2 handles similar
> functionality. After looking into it a bit, it's more inline with
> what Tom's suggestion was regarding a query from the operation
> rather than returning the values in the manner currently required.
>
> Here's DB2's syntax... does anyone have any familiarity with it?
>
> Simply put, it's sort-of like:
>
> SELECT * FROM (FINAL | NEW | OLD) TABLE (INSERT | UPDATE | DELETE)
>
> I'd like to hear from anyone that's used it to see if it really is
> better... logically it seems nicer, but I've never used it.
>
> --
> Jonah H. Harris, Database Internals Architect
> EnterpriseDB Corporation
> 732.331.1324

On 3/13/06, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
> One of the purposes of this as I understand it is to allow clients to
> get back the generated key(s). I don't see enough of the syntax to
> see if this is possible with the DB2 syntax below.

I believe it would be something like

CREATE SEQUENCE test_id_seq;
CREATE TABLE test_tbl (test_id bigint not null default
nextval('test_id_seq'), test_name varchar(32), PRIMARY KEY(test_id));

To get the generated sequence:
SELECT test_id FROM NEW TABLE INSERT INTO test_tbl VALUES
(nextval('test_id_seq'), 'Joe Blow');
The reason for NEW is because there is no OLD version of the record.

In contrast, something similar to:
SELECT test_name FROM OLD TABLE UPDATE test_tbl SET test_name = 'John Doe'
WHERE test_id = 1;
would return "Joe Blow"

Whereas:
SELECT test_name FROM NEW TABLE UPDATE test_tbl SET test_name = 'John Doe'
WHERE test_id = 1;
would return "John Doe"

Again, I haven't really used it, but have read over the docs briefly. I'm
just wondering if anyone has used it and likes/dislikes it.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

Jonah H. Harris wrote:

> Again, I haven't really used it, but have read over the docs briefly.
> I'm just wondering if anyone has used it and likes/dislikes it.

I guess you could get the same effect from a transaction. If there is
much network overhead you could also write a stored procedure. This is
obviously "more direct". Due to caching I am not sure how much
performance improvement there is in doing the read/write in one statement.

IMHO this is only useful in fringe cases, can be implemented efficiently
with existing syntax and so just adds useless complexity.

regards,
Lukas

On 3/13/06, Lukas Smith <smith(at)pooteeweet(dot)org> wrote:

> I guess you could get the same effect from a transaction. If there is
> much network overhead you could also write a stored procedure. This is
> obviously "more direct". Due to caching I am not sure how much
> performance improvement there is in doing the read/write in one
> statement.

What are you talking about?

IMHO this is only useful in fringe cases, can be implemented efficiently
> with existing syntax and so just adds useless complexity.

Show me an example of how "efficiently" you can get this syntax. Let's see,
I know I could get it using our current syntax too...

SELECT test_id FROM insert_into_table('test_tbl',
array['nextval(\'test_id_seq\')','test']) AS t1(test_id BIGINT);

Where insert_into_table is a general function that takes inputs, builds the
insert statement, executes the insert statement, builds a selection, and
returns the row. Of course, you could have a *custom* function that only
works for the test_tbl, that would make it easy to return the next sequence
id... or wait, you could write the general function to go lookup the table
definition, find out for itself to do the nextval, and do more craziness ad
nauseum.

In the end, how is this more efficient or easy than:

INSERT INTO test_tbl VALUES (nextval('test_id_seq'), 'John Doe') RETURNING
test_id;
OR
SELECT test_id FROM NEW TABLE INSERT INTO test_tbl VALUES
(nextval('test_id_seq'), 'John Doe');

Based on your statement, this should be really easy. It's easy to make
generalized statements, so let's see an example to compare.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

Jonah H. Harris wrote:

> In the end, how is this more efficient or easy than:
>
> INSERT INTO test_tbl VALUES (nextval('test_id_seq'), 'John Doe')
> RETURNING test_id;
> OR
> SELECT test_id FROM NEW TABLE INSERT INTO test_tbl VALUES
> (nextval('test_id_seq'), 'John Doe');
>
> Based on your statement, this should be really easy. It's easy to make
> generalized statements, so let's see an example to compare.

Ah, I was just not clear on what you were comparing this against. So
nevermind.

regards,
Lukas

On Sun, 2006-03-12 at 11:11 -0500, Jonah H. Harris wrote:
> I was talking with Jonathan Gennick about the INS/UPD/DEL RETURNING
> stuff, and he recommended looking into the way DB2 handles similar
> functionality. After looking into it a bit, it's more inline with
> what Tom's suggestion was regarding a query from the operation rather
> than returning the values in the manner currently required.
>
> Here's DB2's syntax... does anyone have any familiarity with it?
>
> Simply put, it's sort-of like:
>
> SELECT * FROM (FINAL | NEW | OLD) TABLE (INSERT | UPDATE | DELETE)
>
> I'd like to hear from anyone that's used it to see if it really is
> better... logically it seems nicer, but I've never used it.

Hmmm...well, IMHO either syntax is fairly contrived, but the DB2 syntax
does seem a more meaningful way of doing this. It is pretty obscure
though...most DB2 people don't know the above syntax because its new in
DB2 8.1

The DB2 syntax allows you to more easily do things like a simultaneous
copy-and-delete from a holding table into a main table, e.g.

INSERT INTO MAINTABLE
SELECT * FROM NEW TABLE (DELETE FROM HOLDINGTABLE WHERE ...)

Thats quite a nice performance trick I've used to save doing separate
INSERT and DELETE tasks on a busy table.

The Oracle syntax reads less well for that type of task.

Best Regards, Simon Riggs

On Sun, 12 Mar 2006, Jonah H. Harris wrote:

> I was talking with Jonathan Gennick about the INS/UPD/DEL RETURNING stuff,
> and he recommended looking into the way DB2 handles similar functionality.
> After looking into it a bit, it's more inline with what Tom's suggestion was
> regarding a query from the operation rather than returning the values in the
> manner currently required.
>
> Here's DB2's syntax... does anyone have any familiarity with it?
>
> Simply put, it's sort-of like:
>
> SELECT * FROM (FINAL | NEW | OLD) TABLE (INSERT | UPDATE | DELETE)
>
> I'd like to hear from anyone that's used it to see if it really is better...
> logically it seems nicer, but I've never used it.

It works well for cases where you want to pass the result of an
insert/delete/update to another query. There was a paper on IBM developer
works on how they got the 7 or so queries in an order transaction in TPC-C
down to 3 queries and increased throughput impressively.

This doesn't solve the generated keys problem that the Java and probably
.NET interfaces have. Mind, RETURNING doesn't solve anything either.

I prefer this syntax to RETURNING. Then again, Oracle is a bigger target
than DB2 so... I'm not sure.

Thanks,

Gavin

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> On Sun, 12 Mar 2006, Jonah H. Harris wrote:
>> SELECT * FROM (FINAL | NEW | OLD) TABLE (INSERT | UPDATE | DELETE)

> This doesn't solve the generated keys problem that the Java and probably
> .NET interfaces have. Mind, RETURNING doesn't solve anything either.

Why not? AFAICS, either one lets you get at generated keys.

It's quite unclear to me what the difference is between "FINAL" and
"NEW" ... any clarification there?

The "OLD" idea is cute but I'm not sure how useful it really is. They
seem to have missed a bet anyway: if I understand how this works, you
can't get values from both new and old row states in the UPDATE case.
The classification seems bogus for both INSERT and DELETE, too; neither
of them have more than one row state to deal with.

Also, is the front SELECT allowed to have its own WHERE, or is it
constrained to return exactly one row per inserted/updated/deleted row?
If it can have a WHERE then there's a syntactic ambiguity in
SELECT ... FROM NEW TABLE UPDATE ... WHERE ...

More generally, this syntax is problematic in that it's syntactically
possible to use SELECT FROM NEW TABLE ... as a sub-query, which seems
like a truly horrid idea from both semantics and implementation
perspectives.

regards, tom lane

On 3/13/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Also, is the front SELECT allowed to have its own WHERE, or is it
> constrained to return exactly one row per inserted/updated/deleted row?
> If it can have a WHERE then there's a syntactic ambiguity in
> SELECT ... FROM NEW TABLE UPDATE ... WHERE ...

Yes, I believe it supports SELECT .. FROM NEW TABLE (UPDATE .. WHERE ..)
WHERE

IBM's paper, "Returning Modified Rows--SELECT Statements with Side Effects"
is here:
http://www.isys.ucl.ac.be/vldb04/eProceedings/contents/pdf/IND1P1.PDF

I'll look up more.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

On Mon, 13 Mar 2006, Tom Lane wrote:

> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> > On Sun, 12 Mar 2006, Jonah H. Harris wrote:
> >> SELECT * FROM (FINAL | NEW | OLD) TABLE (INSERT | UPDATE | DELETE)
>
> > This doesn't solve the generated keys problem that the Java and probably
> > .NET interfaces have. Mind, RETURNING doesn't solve anything either.
>
> Why not? AFAICS, either one lets you get at generated keys.

There are a few different ways to get at generated keys from JDBC at
least. The case we cannot trivially deal with is when the code executes a
statement and then wants a result set of all generated keys. That is, it
doesn't register which generated keys it wants returned before the query
is executed.

>
> It's quite unclear to me what the difference is between "FINAL" and
> "NEW" ... any clarification there?

NEW returns the representation of the data which the statement creates;
FINAL is the final representation of the data, after AFTER triggers have
been applied.

>
> The "OLD" idea is cute but I'm not sure how useful it really is. They
> seem to have missed a bet anyway: if I understand how this works, you
> can't get values from both new and old row states in the UPDATE case.
> The classification seems bogus for both INSERT and DELETE, too; neither
> of them have more than one row state to deal with.

Right, it's not as useful as our OLD.*, NEW.*.

>
> Also, is the front SELECT allowed to have its own WHERE, or is it
> constrained to return exactly one row per inserted/updated/deleted row?
> If it can have a WHERE then there's a syntactic ambiguity in
> SELECT ... FROM NEW TABLE UPDATE ... WHERE ...

That's definately ambiguous. The manual doesn't clarify and I do not have
DB2 installed locally.

>
> More generally, this syntax is problematic in that it's syntactically
> possible to use SELECT FROM NEW TABLE ... as a sub-query, which seems
> like a truly horrid idea from both semantics and implementation
> perspectives.

I cannot see any reference to whether this is allowed in DB2. The DB2
manual and other IBM apps use it extensively in named expressions. Ie,

WITH
foo as (SELECT FROM NEW TABLE(...)),
bar as (SELECT FROM OLD TABLE(...))
SELECT ... FROM foo, bar

It does say that a 'data change table reference' is simply a type of table
reference so I suppose it can occur in a sub query. The ability to have
INSERT ... RETURNING in a from clause would achieve most of this, I think.

Thanks,

Gavin