Re: [HACKERS] PG10 partitioning - odd behavior/possible bug

On Sun, Sep 3, 2017 at 5:28 PM, Joe Conway <mail(at)joeconway(dot)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