Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On Sunday, October 21, 2012 08:45:31 PM Andrew Dunstan wrote:
> On 10/21/2012 01:40 PM, Andres Freund wrote:
> > Suppose you have something like
> >
> > CREATE TABLE positionlog(
> > id serial primary key,
> > timestamp timestamptz DEFAULT NOW(),
> > position geometry
> > );
> >
> > And you want to insert multiple values in one roundtrip *and* know their
> > ids in your application.
> >
> > INSERT INTO positionlog(position)
> > VALUES
> >
> > ('POINT(..., ...)'),
> > ('POINT(..., ...)')
> >
> > RETURNING id, timestamp, position
> > ;
> >
> > If you want to correlate re returned ids with data in your application
> > without relying on the ordering of INSERT ... VALUES... RETURNING you
> > would need to sort a postgis type in the same way the server does it.
>
> I see. Sorry, I should not have joined the thread late in the piece
> while I'm multitasking.
>
> I guess in such a case I'd be inclined to precompute the id values and
> then supply them in the values clause. That means two round trips rather
> than one.
Which will fail should we get upsert one day...
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On Sunday 21 October 2012, Andres Freund wrote:
> On Sunday, October 21, 2012 07:24:52 PM Andrew Dunstan wrote:
> > why does the client have to be involved, exactly?
> Suppose you have something like
>
> CREATE TABLE positionlog(
> ...
> And you want to insert multiple values in one roundtrip *and* know their
> ids in your application.
>
> INSERT INTO positionlog(position)
> VALUES
> ('POINT(..., ...)'),
> ('POINT(..., ...)')
> RETURNING id, timestamp, position
> ;
>
> If you want to correlate re returned ids with data in your application
> without relying on the ordering of INSERT ... VALUES... RETURNING you
> would need to sort a postgis type in the same way the server does it.
> Am I missing something here?
>
That's close enough to my case: you would have to guess from (timestamp,
position) the order they have with respect to your [(timestamp, pos),...]
input array. That's not always trivial to do client-side (what about duplicate
pairs? ), let alone the CPU needed to sort and match again.
--
Say NO to spam and viruses. Stop using Microsoft Windows!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On Sunday, October 21, 2012 07:24:52 PM Andrew Dunstan wrote:
> On 10/21/2012 12:36 PM, Andres Freund wrote:
> > On Sunday, October 21, 2012 06:30:14 PM Andrew Dunstan wrote:
> >> On 10/21/2012 12:20 PM, Abhijit Menon-Sen wrote:
> >>> At 2012-10-21 11:49:26 -0400, cbbro...@gmail.com wrote:
> If there is a natural sequence (e.g. - a value assigned by nextval()),
> that offers a natural place to apply the usual order-imposing ORDER BY
> that we are expected to use elsewhere.
> >>>
> >>> Note: "INSERT … RETURNING" doesn't accept an ORDER BY clause.
> >>
> >> No, but you can wrap the INSERT .. RETURNING in a CTE and order that.
> >
> > Personally I find that a not very practical suggestion. It means you need
> > the ability to sort the data equivalently on the clientside which isn't
> > always easy if you consider platform/locale and whatever differences.
>
> Er, what?
>
> with orig_inserts as
> (
> insert into table_1
> ...
> returning *
> ),
> ordered_inserts as
> (
> select * from orig_inserts
> order by ...
> )
> insert into table_2
> select * from ordered_inserts ...;
I am not sure I get the point of this.
> why does the client have to be involved, exactly?
Suppose you have something like
CREATE TABLE positionlog(
id serial primary key,
timestamp timestamptz DEFAULT NOW(),
position geometry
);
And you want to insert multiple values in one roundtrip *and* know their ids
in your application.
INSERT INTO positionlog(position)
VALUES
('POINT(..., ...)'),
('POINT(..., ...)')
RETURNING id, timestamp, position
;
If you want to correlate re returned ids with data in your application without
relying on the ordering of INSERT ... VALUES... RETURNING you would need to
sort a postgis type in the same way the server does it.
Am I missing something here?
Greetings,
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On Sunday, October 21, 2012 06:30:14 PM Andrew Dunstan wrote: > On 10/21/2012 12:20 PM, Abhijit Menon-Sen wrote: > > At 2012-10-21 11:49:26 -0400, cbbro...@gmail.com wrote: > >> If there is a natural sequence (e.g. - a value assigned by nextval()), > >> that offers a natural place to apply the usual order-imposing ORDER BY > >> that we are expected to use elsewhere. > > > > Note: "INSERT … RETURNING" doesn't accept an ORDER BY clause. > > No, but you can wrap the INSERT .. RETURNING in a CTE and order that. Personally I find that a not very practical suggestion. It means you need the ability to sort the data equivalently on the clientside which isn't always easy if you consider platform/locale and whatever differences. Suggesting nextval() doesn't strike me as very practical either because it means that you either need a separate roundtrip to the server to get a bunch of new ids which you then can assign to the to-be-inserted rows or you need the ability to match the returned rows to the inserted rows somehow. Thats not always easy. Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
I agree that it seems inappropriate to preserve order. That seems an inappropriate imposition, inconsistent with what SQL does elsewhere. If there is a natural sequence (e.g. - a value assigned by nextval()), that offers a natural place to apply the usual order-imposing ORDER BY that we are expected to use elsewhere. I suppose it is troublesome if there is no such natural sequence, but I wouldn't think it too meaningful to expect order without some visible source of order.
Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
2012/10/21 Albert Cervera i Areny : > A Dimecres, 17 d'octubre de 2012 19:13:47, Merlin Moncure va escriure: > >> On Wed, Oct 17, 2012 at 9:29 AM, Peter Geoghegan >> wrote: > >> > On 17 October 2012 14:53, Merlin Moncure wrote: > >> >> Is that defined in the standard? > >> > > >> > RETURNING isn't even defined in the standard. > >> > >> Right: Point being, assumptions based on implementation ordering are > >> generally to be avoided unless they are explicitly defined in the > >> standard or elsewhere. > > > > I don't see how one could use RETURNING if result is not ensured to be in > the same order as the tuples supplied. What's the use of RETURNING supplying > data in random order? you don't need a ORDER, you need data - and if you need a order, then you can use CTE and ORDER BY clause. Proposed feature can be too limited in future - when some better partitioning can be used or when paralel query processing will be supported Pavel > > > -- > > Albert Cervera i Areny > > http://www.NaN-tic.com > > Tel: +34 93 553 18 03 > > > > http://twitter.com/albertnan > > http://www.nan-tic.com/blog > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
A Dimecres, 17 d'octubre de 2012 19:13:47, Merlin Moncure va escriure: > On Wed, Oct 17, 2012 at 9:29 AM, Peter Geoghegan wrote: > > On 17 October 2012 14:53, Merlin Moncure wrote: > >> Is that defined in the standard? > > > > RETURNING isn't even defined in the standard. > > Right: Point being, assumptions based on implementation ordering are > generally to be avoided unless they are explicitly defined in the > standard or elsewhere. I don't see how one could use RETURNING if result is not ensured to be in the same order as the tuples supplied. What's the use of RETURNING supplying data in random order? -- Albert Cervera i Areny http://www.NaN-tic.com Tel: +34 93 553 18 03 http://twitter.com/albertnan http://www.nan-tic.com/blog
Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On Wed, Oct 17, 2012 at 9:29 AM, Peter Geoghegan wrote: > On 17 October 2012 14:53, Merlin Moncure wrote: >> Is that defined in the standard? > > RETURNING isn't even defined in the standard. Right: Point being, assumptions based on implementation ordering are generally to be avoided unless they are explicitly defined in the standard or elsewhere. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On Wednesday 17 October 2012, you wrote: > "P. Christeas" writes: > > It has been a fact that the RETURNING clause on an INSERT will return > > multiple rows with the same order as multiple VALUES have been fed. > > I don't believe this is a good idea in the slightest. Yeah, the current > implementation happens to act like that, but there is no reason that we > should make it guaranteed behavior. That's my point, to push you to decide on that "feature" and clarify it in the documentation. So far, it's very tempting for me to use this behavior, since I can avoid multiple INSERTs (=save bandwidth) and also the burden of figuring out which of the returned ids associates to which inserted row. Having a discussion (or argument or a vote) like this, I think, is useful. FYI, there is also a stack overflow question on this: http://stackoverflow.com/questions/5439293/is-insert-returning-guaranteed-to- return-things-in-the-right-order -- Say NO to spam and viruses. Stop using Microsoft Windows! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On 17 October 2012 14:53, Merlin Moncure wrote: > Is that defined in the standard? RETURNING isn't even defined in the standard. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
"P. Christeas" writes:
> It has been a fact that the RETURNING clause on an INSERT will return
> multiple rows with the same order as multiple VALUES have been fed.
> eg: INSERT INTO tbl1(code) VALUES ('abc'), ('def'), ('agh')
>RETURNING id, code;
> is expected to yield:
>id | code
> ---
> 1 | abc
> 2 | def
> 3 | agh
> Clarify that in the documentation, and also write a test case that will
> prevent us from breaking the rule in the future.
I don't believe this is a good idea in the slightest. Yeah, the current
implementation happens to act like that, but there is no reason that we
should make it guaranteed behavior. Nor is a regression test case going
to stop someone from changing it, anyway.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On Wed, Oct 17, 2012 at 7:38 AM, P. Christeas wrote: > It has been a fact that the RETURNING clause on an INSERT will return > multiple rows with the same order as multiple VALUES have been fed. Is that defined in the standard? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
It has been a fact that the RETURNING clause on an INSERT will return
multiple rows with the same order as multiple VALUES have been fed.
eg: INSERT INTO tbl1(code) VALUES ('abc'), ('def'), ('agh')
RETURNING id, code;
is expected to yield:
id | code
---
1 | abc
2 | def
3 | agh
Clarify that in the documentation, and also write a test case that will
prevent us from breaking the rule in the future.
---
doc/src/sgml/ref/insert.sgml | 17 +
src/test/regress/expected/insert.out |9 +
src/test/regress/sql/insert.sql |4
3 files changed, 30 insertions(+), 0 deletions(-)
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index a3930be..64cb41b 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -213,6 +213,11 @@ INSERT oid countRETURNING list, computed over the row(s) inserted by the
command.
+
+ If multiple rows are inserted by an INSERT ... RETURNING
commmand,
+ the order of the RETURNING rows is the same as that of the
inputs
+ to the INSERT command.
+
@@ -268,6 +273,18 @@ INSERT INTO films (code, title, did, date_prod, kind)
VALUES
+ This example inserts multiple rows and returns the corresponding ids
+ at the same order:
+
+
+INSERT INTO films(code, title) VALUES
+('B6717', 'Tampopo'),
+('HG120', 'The Dinner Game')
+RETURNING id, code;
+
+
+
+
This example inserts some rows into table
films from a table tmp_films
with the same column layout as films:
diff --git a/src/test/regress/expected/insert.out
b/src/test/regress/expected/insert.out
index 96c7f9e..081e4b9 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -80,4 +80,13 @@ select col1, col2, char_length(col3) from inserttest;
30 | 50 | 1
(8 rows)
+--- RETURNING order
+insert into inserttest(col1, col2) values(50, 10), (60, 8), (70, 23) RETURNING
col2;
+ col2
+--
+ 10
+8
+ 23
+(3 rows)
+
drop table inserttest;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index a0ae850..c7815dd 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -35,4 +35,8 @@ insert into inserttest values(30, 50, repeat('x', 1));
select col1, col2, char_length(col3) from inserttest;
+--- RETURNING order
+
+insert into inserttest(col1, col2) values(50, 10), (60, 8), (70, 23) RETURNING
col2;
+
drop table inserttest;
--
1.7.4.4
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
