Reproduce https://github.com/Kinto/kinto/issues/1525

dummy.py

import time
import random
import psycopg2


def first():
    conn = psycopg2.connect("dbname=buildhub_copy")
    with conn.cursor() as cur:
        cur.execute("""
        delete from records where parent_id='peter'
        """)
    conn.commit()



def create(i):
    print('!', i)
    conn = psycopg2.connect("dbname=buildhub_copy")
    with conn.cursor() as cur:
        timestamp = 1523875992004
        cur.execute("""
        WITH create_record AS (
            INSERT INTO records (id, parent_id, collection_id, data, last_modified, deleted)
            VALUES ('firefox_beta_58-0b15rc1_linux-i686_cs2', 'peter',
                    'record', ('{}')::JSONB,
                    from_epoch(%(timestamp)s),
                    FALSE)
            ON CONFLICT (id, parent_id, collection_id) DO UPDATE
            SET last_modified = from_epoch(%(timestamp)s),
                data = ('{}')::JSONB,
                deleted = FALSE
            WHERE records.deleted = TRUE
            RETURNING id, data, last_modified
        )
        SELECT id, data, as_epoch(last_modified) AS last_modified, TRUE AS inserted
            FROM create_record
        UNION ALL
        SELECT id, data, as_epoch(last_modified) AS last_modified, FALSE AS inserted FROM records
        WHERE id = 'firefox_beta_58-0b15rc1_linux-i686_cs2' AND parent_id = 'peter' AND collection_id = 'record'
        LIMIT 1;
        """, {'timestamp': timestamp + i})
        fetched = cur.fetchone()
        # assert fetched is not None
        if i == 0:time.sleep(1)
        print('commit', i)
        conn.commit()
    return fetched



import concurrent.futures
from concurrent.futures import ThreadPoolExecutor, ProcessPoolExecutor

def run():

    first()
    time.sleep(1)

    with ThreadPoolExecutor() as executor:
    # with ProcessPoolExecutor() as executor:

        future_to_url = {executor.submit(create, i): i for i in range(2)}
        for future in concurrent.futures.as_completed(future_to_url):
            i = future_to_url[future]
            try:
                data = future.result()
            except Exception as exc:
                print('%r generated an exception: %s' % (i, exc))
                raise
            else:
                print('%r page data = %r' % (i, data))

run()

postgresql.log

2018-04-16 16:28:51.124 EDT [41629] LOG:  statement: BEGIN
2018-04-16 16:28:51.124 EDT [41629] LOG:  statement:
	        delete from records where parent_id='peter'

2018-04-16 16:28:51.125 EDT [41629] LOG:  statement: COMMIT
2018-04-16 16:28:52.135 EDT [41630] LOG:  statement: BEGIN
2018-04-16 16:28:52.136 EDT [41631] LOG:  statement: BEGIN
2018-04-16 16:28:52.136 EDT [41630] LOG:  statement:
	        WITH create_record AS (
	            INSERT INTO records (id, parent_id, collection_id, data, last_modified, deleted)
	            VALUES ('firefox_beta_58-0b15rc1_linux-i686_cs2', 'peter',
	                    'record', ('{}')::JSONB,
	                    from_epoch(1523875992004),
	                    FALSE)
	            ON CONFLICT (id, parent_id, collection_id) DO UPDATE
	            SET last_modified = from_epoch(1523875992004),
	                data = ('{}')::JSONB,
	                deleted = FALSE
	            WHERE records.deleted = TRUE
	            RETURNING id, data, last_modified
	        )
	        SELECT id, data, as_epoch(last_modified) AS last_modified, TRUE AS inserted
	            FROM create_record
	        UNION ALL
	        SELECT id, data, as_epoch(last_modified) AS last_modified, FALSE AS inserted FROM records
	        WHERE id = 'firefox_beta_58-0b15rc1_linux-i686_cs2' AND parent_id = 'peter' AND collection_id = 'record'
	        LIMIT 1;

2018-04-16 16:28:52.136 EDT [41631] LOG:  statement:
	        WITH create_record AS (
	            INSERT INTO records (id, parent_id, collection_id, data, last_modified, deleted)
	            VALUES ('firefox_beta_58-0b15rc1_linux-i686_cs2', 'peter',
	                    'record', ('{}')::JSONB,
	                    from_epoch(1523875992005),
	                    FALSE)
	            ON CONFLICT (id, parent_id, collection_id) DO UPDATE
	            SET last_modified = from_epoch(1523875992005),
	                data = ('{}')::JSONB,
	                deleted = FALSE
	            WHERE records.deleted = TRUE
	            RETURNING id, data, last_modified
	        )
	        SELECT id, data, as_epoch(last_modified) AS last_modified, TRUE AS inserted
	            FROM create_record
	        UNION ALL
	        SELECT id, data, as_epoch(last_modified) AS last_modified, FALSE AS inserted FROM records
	        WHERE id = 'firefox_beta_58-0b15rc1_linux-i686_cs2' AND parent_id = 'peter' AND collection_id = 'record'
	        LIMIT 1;

2018-04-16 16:28:52.142 EDT [41631] LOG:  statement: COMMIT