On Mon, Jun 26, 2017 at 5:43 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
-- Jim Fulton <jim@xxxxxxxxxxxxxx> 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
