odd behavior/possible bug (Was: Re: PG10 partitioning - odd behavior/possible bug)

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