On 09/03/2017 02:28 PM, Joe Conway wrote: > I was playing around with partitioning and found an oddity that is best > described with the following reasonably minimal test case:
<snip>
> 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?
This does not require partitioning to reproduce -- sorry for the false
accusations ;-)
8<---------------
CREATE TABLE timetravel
(
id int8,
f1 text not null,
tr tstzrange not null default tstzrange(now(), 'infinity', '[]')
);
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 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 < 4;
END LOOP;
END
$$;
NOTICE: loop = 1
NOTICE: OLD.tr = ["2017-09-03 15:17:31.598734-07",infinity]
NOTICE: tsr = ["2017-09-03 15:17:31.598734-07","2017-09-03
15:17:31.608018-07")
NOTICE: NEW.tr = ["2017-09-03 15:17:31.608018-07",infinity]
NOTICE: OLD.tr = ["2017-09-03 15:17:31.598734-07",infinity]
NOTICE: tsr = ["2017-09-03 15:17:31.598734-07","2017-09-03
15:17:31.608018-07")
NOTICE: NEW.tr = ["2017-09-03 15:17:31.608018-07",infinity]
NOTICE: OLD.tr = ["2017-09-03 15:17:31.598734-07",infinity]
NOTICE: tsr = ["2017-09-03 15:17:31.598734-07","2017-09-03
15:17:31.608018-07")
NOTICE: NEW.tr = ["2017-09-03 15:17:31.608018-07",infinity]
NOTICE: loop = 2
NOTICE: OLD.tr = ["2017-09-03 15:17:31.598734-07","2017-09-03
15:17:31.608018-07")
NOTICE: tsr = ["2017-09-03 15:17:31.598734-07","2017-09-03
15:17:31.608018-07")
NOTICE: NEW.tr = ["2017-09-03 15:17:31.608018-07",infinity]
NOTICE: OLD.tr = ["2017-09-03 15:17:31.608018-07",infinity]
NOTICE: tsr = empty
NOTICE: NEW.tr = ["2017-09-03 15:17:31.608018-07",infinity]
NOTICE: OLD.tr = ["2017-09-03 15:17:31.598734-07","2017-09-03
15:17:31.608018-07")
NOTICE: tsr = ["2017-09-03 15:17:31.598734-07","2017-09-03
15:17:31.608018-07")
NOTICE: NEW.tr = ["2017-09-03 15:17:31.608018-07",infinity]
NOTICE: OLD.tr = ["2017-09-03 15:17:31.608018-07",infinity]
NOTICE: tsr = empty
NOTICE: NEW.tr = ["2017-09-03 15:17:31.608018-07",infinity]
NOTICE: OLD.tr = ["2017-09-03 15:17:31.598734-07","2017-09-03
15:17:31.608018-07")
NOTICE: tsr = ["2017-09-03 15:17:31.598734-07","2017-09-03
15:17:31.608018-07")
NOTICE: NEW.tr = ["2017-09-03 15:17:31.608018-07",infinity]
NOTICE: OLD.tr = ["2017-09-03 15:17:31.608018-07",infinity]
NOTICE: tsr = empty
NOTICE: NEW.tr = ["2017-09-03 15:17:31.608018-07",infinity]
DO
8<---------------
Notice that tsr is not empty at all on the first loop, but on the second
loop it is empty every second time the trigger fires.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
signature.asc
Description: OpenPGP digital signature
