I was playing around with partitioning and found an oddity that is best
described with the following reasonably minimal test case:
8<---------
CREATE TABLE timetravel
( id int8, f1 text not null, tr tstzrange not null default tstzrange(now(), 'infinity', '[]')
) PARTITION BY RANGE (upper(tr));
CREATE TABLE timetravel_current PARTITION OF timetravel
( primary key (id, tr) DEFERRABLE
) FOR VALUES FROM ('infinity') TO (MAXVALUE);
CREATE INDEX timetravel_current_tr_idx ON timetravel_current USING GIST
(tr);
CREATE TABLE timetravel_history PARTITION OF timetravel
( primary key (id, tr) DEFERRABLE
) FOR VALUES FROM (MINVALUE) TO ('infinity');
CREATE INDEX timetravel_history_tr_idx ON timetravel_history USING GIST
(tr);
CREATE OR REPLACE FUNCTION modify_timetravel()
RETURNS TRIGGER AS $$ DECLARE tsr tstzrange; BEGIN RAISE NOTICE 'OLD.tr = %', OLD.tr;
tsr := tstzrange(lower(OLD.tr), now(), '[)'); RAISE NOTICE 'tsr = %', tsr;
OLD.tr = tsr; INSERT INTO timetravel VALUES (OLD.*); IF (TG_OP = 'UPDATE') THEN tsr := tstzrange(now(),
'infinity','[]'); RAISE NOTICE 'NEW.tr = %', tsr; NEW.tr = tsr; RETURN NEW; ELSIF (TG_OP = 'DELETE') THEN
RETURN OLD; END IF; END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER timetravel_audit BEFORE DELETE OR UPDATE
ON timetravel_current FOR EACH ROW EXECUTE PROCEDURE modify_timetravel();
INSERT INTO timetravel(id, f1)
SELECT g.i, 'row-' || g.i::text
FROM generate_series(1,10) AS g(i);
DO $$ DECLARE i int; BEGIN FOR i IN 1..2 LOOP RAISE NOTICE 'loop = %', i; UPDATE timetravel SET f1 = f1 ||
'-r'|| i where id < 2; END LOOP; END
$$;
NOTICE: loop = 1
NOTICE: OLD.tr = ["2017-09-03 14:15:08.800811-07",infinity]
NOTICE: tsr = ["2017-09-03 14:15:08.800811-07","2017-09-03
14:18:48.270274-07")
NOTICE: NEW.tr = ["2017-09-03 14:18:48.270274-07",infinity]
NOTICE: loop = 2
NOTICE: OLD.tr = ["2017-09-03 14:18:48.270274-07",infinity]
NOTICE: tsr = empty
ERROR: no partition of relation "timetravel" found for row
DETAIL: Partition key of the failing row contains (upper(tr)) = (null).
CONTEXT: SQL statement "INSERT INTO timetravel VALUES (OLD.*)"
PL/pgSQL function modify_timetravel() line 11 at SQL statement
SQL statement "UPDATE timetravel SET f1 = f1 || '-r' || i where id < 2"
PL/pgSQL function inline_code_block line 7 at SQL statement
8<---------
Notice that in the first loop iteration tsr is calculated from OLD.tr
correctly. But in the second loop iteration it is not, and therefore no
partition can be found for the insert.
I have not dug too deeply into this yet, but was wondering if this
behavior is sane/expected for some reason I am missing?
Thanks,
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
