From: | Sjon Hortensius <sjon(at)hortensius(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: INSERT INTO .. SELECT nextval() ORDER BY - returns unexpectedly ordered values |
Date: | 2015-09-03 16:01:37 |
Message-ID: | CAK_tfub78Gc7fgToha=BwfoiFQNSZ27nb7d9BZqrak6Eg-v1yA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Thanks, that makes sense!
On Thu, Sep 3, 2015 at 2:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Sjon Hortensius <sjon(at)hortensius(dot)net> writes:
> > INSERT INTO test2 SELECT name, nextval('tmp'), id FROM test ORDER BY id
> ASC;
>
> > I have worked around this by clustering the old table on the new id
> before
> > SELECTing but this behavior doesn't seem to be documented, is this a bug?
>
> No. You're assuming that the nextval() happens after the row ordering,
> but this is not necessarily so --- indeed, a strict reading of the SQL
> standard would imply that it should *never* be so, because the standard
> execution model is that ORDER BY happens after computing the SELECT list.
> (Without that, locutions like "ORDER BY 1" would make no sense.) It might
> accidentally work if the ORDER BY were done via an indexscan rather than
> an explicit sort step, but that's not possible in your test case.
>
> Try something like this to force the evaluation order:
>
> INSERT INTO test2
> SELECT name, nextval('tmp'), id FROM
> (SELECT name, id FROM test ORDER BY id ASC) ss;
>
> regards, tom lane
>
From | Date | Subject | |
---|---|---|---|
Next Message | Gerdan Rezende dos Santos | 2015-09-03 19:48:00 | Re: error on online backup using pg_basebackup tool |
Previous Message | Terje Elde | 2015-09-03 15:55:00 | Re: INSERT INTO .. SELECT nextval() ORDER BY - returns unexpectedly ordered values |