Postgres 12

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 t ( txn_type VARCHAR(255),
                 txn_id VARCHAR(255)
               );

CREATE TABLE

CREATE FUNCTION my_check (text, text)
RETURNS BOOLEAN
AS $$
SELECT NOT EXISTS ( SELECT NULL
                    FROM t
                    WHERE t.txn_id = $2 AND t.txn_type != $1 );
$$ 
LANGUAGE SQL;

CREATE FUNCTION

ALTER TABLE t 
   ADD CONSTRAINT my_constraint CHECK (my_check(txn_type, txn_id));

ALTER TABLE

INSERT INTO t VALUES ('asd', 'qwe');

INSERT 0 1

INSERT INTO t VALUES ('asd', 'qwe');

INSERT 0 1

INSERT INTO t VALUES ('zxc', 'qwe');

ERROR:  new row for relation "t" violates check constraint "my_constraint"
DETAIL:  Failing row contains (zxc, qwe).

SELECT * FROM t;

txn_type
txn_id

asd
qwe

asd
qwe

SELECT 2