PG10 partitioning - odd behavior/possible bug

From: Joe Conway <mail(at)joeconway(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: PG10 partitioning - odd behavior/possible bug
Date: 2017-09-03 21:28:44
Message-ID: ac7fa348-f2d9-271d-913c-55fb5679d6a8@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2017-09-03 22:20:17 Re: [bug fix] Savepoint-related statements terminates connection
Previous Message Daniel Gustafsson 2017-09-03 20:50:09 Re: adding the commit to a patch's thread