Thread: 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 handlessimilar functionality. After looking into it a bit, it's more inline with what Tom's suggestion was regarding a queryfrom the operation rather than returning the values in the manner currently required. <br /><br />Here's DB2's syntax...does anyone have any familiarity with it?<br /><br />Simply put, it's sort-of like:<br /><br />SELECT * FROM (FINAL| NEW | OLD) TABLE (INSERT | UPDATE | DELETE)<br /><br clear="all" />I'd like to hear from anyone that's used it tosee if it really is better... logically it seems nicer, but I've never used it. <br /><br />-- <br />Jonah H. Harris, DatabaseInternals Architect<br />EnterpriseDB Corporation<br />732.331.1324<br />
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@fastcrypt.com> wrote:
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
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@pooteeweet.org> wrote:
What are you talking about?
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
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');
--
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@linuxworld.com.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 inSELECT ... 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@sss.pgh.pa.us> wrote:
Yes, I believe it supports SELECT .. FROM NEW TABLE (UPDATE .. WHERE ..) WHERE
I'll look up more.
--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324
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
http://www.isys.ucl.ac.be/vldb04/eProceedings/contents/pdf/IND1P1.PDF
--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324
On Mon, 13 Mar 2006, Tom Lane wrote: > Gavin Sherry <swm@linuxworld.com.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, WITHfoo 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