Обсуждение: Chaining inserts ... This would be cool

On 23 April 2012 21:49, Nick Apperson <apperson@gmail.com> wrote:
> There are obviously workarounds for this, but I'm wondering why the
> following query shouldn't work. It seems like it should. With MVCC already
> present on the back-end, I can't see any reason other than additional
> parsing routines that this couldn't work:
>
> INSERT INTO old_login_id_to_new_account_id(new_account_id, old_login_id)
> INSERT INTO accounts(id, username, password_hash, email) SELECT DEFAULT,
> username, password_hash, email FROM logins_old RETURNING id, logins_old.id;
>
> Anyway, I'm sure there are more important features for Postgres (like
> upserts, unique indexes on GIN, Gist and hash, fixed arrays, compact storage
> of enum arrays as bitfields, etc.) I just thought it was an interesting
> idea.

You should be able to use writeable common table expressions to
achieve a linking behaviour.

http://www.postgresql.org/docs/9.1/static/queries-with.html
http://www.depesz.com/index.php/2011/03/16/waiting-for-9-1-writable-cte/
http://thombrown.blogspot.de/2011/11/writeable-common-table-expressions.html

But I'm not sure the query you posted makes any sense.  Why would a
SELECT statement have a RETURNING clause?  And where do the values for
the first INSERT come from?
--
Thom

On Tue, Apr 24, 2012 at 6:49 AM, Nick Apperson <apperson@gmail.com> wrote:
> There are obviously workarounds for this, but I'm wondering why the
> following query shouldn't work. It seems like it should. With MVCC already
> present on the back-end, I can't see any reason other than additional
> parsing routines that this couldn't work:
>
> INSERT INTO old_login_id_to_new_account_id(new_account_id, old_login_id)
> INSERT INTO accounts(id, username, password_hash, email) SELECT DEFAULT,
> username, password_hash, email FROM logins_old RETURNING id, logins_old.id;

That's possible using WITH. I made a statement that creates an invoice
and its lines (with the lines all having a foreign-key reference to
the owning invoice) more or less the same way:

WITH inv AS (insert into ... returning id),
constants AS (values (...),(...),(...))
INSERT INTO invoicelines (columnlist) SELECT inv.id,constants.* FROM
inv,constants

Something like that. I do remember running into trouble with the
multi-row insert (can't use multiple rows of literals with SELECT, and
can't fetch data from a WITH expression with VALUES), so it had to go
to the extra level of structure. If you're inserting just one row into
each, this should be easy.

Of course, the question I never asked (never bothered to, really) was:
Is it really any better than simply doing the first insert and
retrieving the ID in my application? :)

ChrisA

On Tue, Apr 24, 2012 at 08:12:10AM +1000, Chris Angelico wrote:
> On Tue, Apr 24, 2012 at 6:49 AM, Nick Apperson <apperson@gmail.com> wrote:
> > There are obviously workarounds for this, but I'm wondering why
> > the following query shouldn't work. It seems like it should. With
> > MVCC already present on the back-end, I can't see any reason other
> > than additional parsing routines that this couldn't work:
> >
> > INSERT INTO old_login_id_to_new_account_id(new_account_id,
> > old_login_id) INSERT INTO accounts(id, username, password_hash,
> > email) SELECT DEFAULT, username, password_hash, email FROM
> > logins_old RETURNING id, logins_old.id;
>
> That's possible using WITH. I made a statement that creates an
> invoice and its lines (with the lines all having a foreign-key
> reference to the owning invoice) more or less the same way:
>
> WITH inv AS (insert into ... returning id), constants AS (values
> (...),(...),(...)) INSERT INTO invoicelines (columnlist) SELECT
> inv.id,constants.* FROM inv,constants
>
> Something like that. I do remember running into trouble with the
> multi-row insert (can't use multiple rows of literals with SELECT,
> and can't fetch data from a WITH expression with VALUES), so it had
> to go to the extra level of structure. If you're inserting just one
> row into each, this should be easy.
>
> Of course, the question I never asked (never bothered to, really)
> was: Is it really any better than simply doing the first insert and
> retrieving the ID in my application? :)

One crucial difference is the number of round trips to the database.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Tue, Apr 24, 2012 at 11:30 AM, David Fetter <david@fetter.org> wrote:
> On Tue, Apr 24, 2012 at 08:12:10AM +1000, Chris Angelico wrote:
>> Of course, the question I never asked (never bothered to, really)
>> was: Is it really any better than simply doing the first insert and
>> retrieving the ID in my application? :)
>
> One crucial difference is the number of round trips to the database.

Yeah, which gives it the potential to be faster, but I never actually
measured anything. Would be curious to know if it becomes better at X
table size, or something.

ChrisA