On Sun, Sep 3, 2017 at 5:28 PM, Joe Conway <m...@joeconway.com> wrote: > I was playing around with partitioning and found an oddity that is best > described with the following reasonably minimal test case:
I can reproduce this without partitioning, just by creating two independent tables with the same schema and tweaking a few things from your test case to refer to the correct table rather than relying on tuple routing: create table timetravel_current (id int8, f1 text not null, tr tstzrange not null default tstzrange(now(), 'infinity', '[]'), primary key (id, tr) deferrable); create table timetravel_history (id int8, f1 text not null, tr tstzrange not null default tstzrange(now(), 'infinity', '[]'), primary key (id, tr) deferrable); create function modify_timetravel() RETURNS trigger LANGUAGE plpgsql 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_history 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; $$; CREATE TRIGGER timetravel_audit BEFORE DELETE OR UPDATE ON timetravel_current FOR EACH ROW EXECUTE PROCEDURE modify_timetravel(); INSERT INTO timetravel_current(id, f1) SELECT g.i, 'row-' || g.i::text FROM generate_series(1,10) AS g(i); Then: rhaas=# DO $$ DECLARE i int; BEGIN FOR i IN 1..2 LOOP RAISE NOTICE 'loop = %', i; UPDATE timetravel_current SET f1 = f1 || '-r' || i where id < 2; END LOOP; END $$; NOTICE: loop = 1 NOTICE: OLD.tr = ["2017-11-28 16:28:46.117239-05",infinity] NOTICE: tsr = ["2017-11-28 16:28:46.117239-05","2017-11-28 16:28:50.700763-05") NOTICE: NEW.tr = ["2017-11-28 16:28:50.700763-05",infinity] NOTICE: loop = 2 NOTICE: OLD.tr = ["2017-11-28 16:28:50.700763-05",infinity] NOTICE: tsr = empty NOTICE: NEW.tr = ["2017-11-28 16:28:50.700763-05",infinity] DO There's no error here because I didn't bother putting constraints on the table, but that tsr = empty bit is still happening. I think the problem is that you're updating the same row twice in the same transaction, and now() returns the same value both times because that's how now() works, so the second time the range ends up with the lower and endpoints that are equal. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company