By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE prop_type(name text PRIMARY KEY);
INSERT INTO prop_type(name) VALUES ('foo'); -- only 'foo', not 'jargon'
CREATE TABLE prop (
id serial PRIMARY KEY,
prop_type text NOT NULL REFERENCES prop_type(name),
norm text NOT NULL,
hash text NOT NULL UNIQUE,
symbols jsonb
);
CREATE TABLE
PREPARE upsert_prop (text, text, text, jsonb) AS
WITH ins AS (
INSERT INTO prop
(prop_type, norm, hash, symbols)
VALUES ($1 , $2 , $3 , $4)
ON CONFLICT (hash) DO UPDATE
SET prop_type = NULL -- never executed, still locks the row
WHERE false
RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM prop WHERE hash = $3;
PREPARE
EXECUTE upsert_prop ('jargon', 'j2', 'lXWkZSmoSE0mZ+n4xpWB', '[]'); -- raises EXCEPTION!
ERROR: insert or update on table "prop" violates foreign key constraint "prop_prop_type_fkey" DETAIL: Key (prop_type)=(jargon) is not present in table "prop_type".
EXECUTE upsert_prop ('foo', 'j2', 'lXWkZSmoSE0mZ+n4xpWB', '[]'); -- always returns a value
| id |
|---|
| 2 |
SELECT 1
EXECUTE upsert_prop ('foo', 'j2', 'lXWkZSmoSE0mZ+n4xpWB', '[]'); -- always returns a value
| id |
|---|
| 2 |
SELECT 1