Deadlock postgresql upsert.

deadlock-postgresql-upsert.sh

#!/bin/bash                                                                                                                                                                                                                                                                                                                                                               

#CREATE TABLE poky (                                                                                                                                                                                                                                                                                                                                                      
    #bucket varchar(256) NOT NULL,
    #key varchar(1024) NOT NULL,
    #data text,
    #created_at timestamptz NOT NULL DEFAULT NOW() CONSTRAINT created_at_utc_check CHECK (EXTRACT(TIMEZONE FROM created_at) = '0'),
    #modified_at timestamptz NOT NULL DEFAULT NOW() CONSTRAINT modified_at_utc_check CHECK (EXTRACT(TIMEZONE FROM modified_at) = '0'),
    #PRIMARY KEY (bucket, key) /* USING INDEX TABLESPACE poky */
#) /* TABLESPACE poky */;

#CREATE OR REPLACE FUNCTION upsert_kv_data(b TEXT, k TEXT, d TEXT, m timestamptz DEFAULT NULL) RETURNS TEXT AS
#$$
#DECLARE
#BEGIN
    #BEGIN
        #IF (m IS NOT NULL) THEN
            #INSERT INTO poky (bucket, key, data, modified_at) VALUES (b, k, d, m);.
        #ELSE
            #INSERT INTO poky (bucket, key, data) VALUES (b, k, d);.
        #END IF;.

        #RETURN 'inserted';
    #EXCEPTION WHEN unique_violation THEN
        #IF (m IS NOT NULL) THEN
            #UPDATE poky SET data = d, modified_at = m WHERE key = k AND bucket = b;
        #ELSE
            #UPDATE poky SET data = d WHERE key = k AND bucket = b;
        #END IF;.

        #IF (FOUND) THEN
            #RETURN 'updated';
        #ELSE
            #RETURN 'rejected';
        #END IF;.
    #END;
#END;
#$$
#LANGUAGE plpgsql;


echo "****** starting first A ****** "
(for i in {1..50}; do
  psql poky_flat -c "select upsert_kv_data(b, k, v, t)
    FROM (VALUES
          ('test', '0', '0', '2013-09-06 00:19:50.864638+00'::timestamptz),
          ('test', '1', '1', '2013-09-06 00:19:50.864638+00'::timestamptz)
         ) AS data (b, k, v, t);"
done > /tmp/out.1) &


echo "****** starting first B ****** "
(for i in {1..50}; do
  psql poky_flat -c "select upsert_kv_data(b, k, v, t)
    FROM (VALUES
          ('test', '1', '1', '2013-09-06 00:19:50.864638+00'::timestamptz),
          ('test', '0', '0', '2013-09-06 00:19:50.864638+00'::timestamptz)
         ) AS data (b, k, v, t);"
done > /tmp/out.2) &

iiiiiinteresting. fascinating that it's this easy to produce the deadlock. So... do we start making a single call to upsert_kv_data per row (removing the FROM (VALUES)?

No, you just order your values when inserting them.