From: | Jim Fulton <jim(at)jimfulton(dot)info> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Jim Fulton <jim(at)jimfulton(dot)info>, "pgsql-gener(dot)" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: When inserting from a SELECT with an ORDER BY, are the inserts (and associated triggers) applied in order? |
Date: | 2017-06-26 23:13:39 |
Message-ID: | CAPDm-Fhc4h818iuOKBkPO-L4U6jJ8JaRk9aZv=Oy0MDJtX7hig@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Jun 26, 2017 at 5:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jim Fulton <jim(at)jimfulton(dot)info> writes:
> > When inserting from a SELECT with an ORDER BY, are the inserts (and
> > associated triggers) applied in order?
>
> Yeah, I'd expect so. I'm not sure we'd promise that that will always
> remain true, but I can't think why it would be violated at the moment.
>
> > It looks like inserts aren't applied in order, and I'm wondering if this
> is
> > something I should expect.
>
> Hard to comment on that without seeing your test case.
>
Yup. This works as I'd expect in my test case. It's in the wild that I'm
having trouble. :(
At the risk of TMI (don't feel obliged to follow), I have a database with a
JSONB column that represents object data (http://newtdb.org) I have an
application in which the data are hierarchically organized. At the
(almost) top level are "communities". I want to be able to search by
community and I want the search to be indexed on community id. In this
application, ids for ancestor objects are always lower than ids of
descendents. While objects may rarely move around in the hierarchy, their
communities never change. Objects are sometimes created in the same
transaction as their parents. I use a trigger to find and copy community
ids into the JSONB data records and then index the JSONB properties.
A test Python script that simulates this:
https://gist.github.com/jimfulton/317e36e6f74c309ee9198f453c41ab59. Note
that objects are initially copied to a staging table and then copied in
mass to the data table. If the test script is run, all of the records have
"cid" properties set properly. If I remove the "order by" on line 80, then
some record end up without "cid" properties.
The non-test case is a bit more complicated. If you're curious:
- the insert logic:
https://github.com/newtdb/db/blob/master/src/newt/db/_adapter.py#L67
It uses upsert rather than deletion+insert to do updates.
- The trigger and function for finding community ids:
https://github.com/karlproject/karl/blob/master/karl/scripts/pgevolve.py#L327
Jim
--
Jim Fulton
http://jimfulton.info
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Danielsson | 2017-06-27 00:31:06 | LEFT JOIN, entry can not be referenced |
Previous Message | Tom Lane | 2017-06-26 22:36:56 | Re: Config for fast huge cascaded updates |