error detail when partition not found

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: error detail when partition not found
Date: 2017-02-21 01:58:47
Message-ID: 9f9dc7ae-14f0-4a25-5485-964d9bfc19bd@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Simon pointed out in a nearby thread [0] that the detail part of
partition-not-found error should show just the partition keys. I posted a
patch on that thread [1], but to avoid confusion being caused by multitude
of patches over there I'm re-posting it here.

* What the patch does:

Currently we show the whole row in the detail part of the error.

CREATE TABLE measurement_year_month (
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM
logdate));

# INSERT INTO measurement_year_month VALUES ('2016-12-02', 1, 1);
ERROR: no partition of relation "measurement_year_month" found for row
DETAIL: Failing row contains (2016-12-02, 1, 1).

Patch changes it look like the following:

# INSERT INTO measurement_year_month VALUES ('2016-12-02', 1, 1);
ERROR: no partition of relation "measurement_year_month" found for row
DETAIL: Partition key of the failing row contains
(date_part('year'::text, logdate), date_part('month'::text,
logdate))=(2016, 12).

It's similar to error detail shown when btree unique violation occurs:

-- just to be clear, using LIKE won't make measurement partitioned too
CREATE TABLE measurement (LIKE measurement_year_month);
CREATE UNIQUE INDEX ON measurement (EXTRACT(YEAR FROM logdate),
EXTRACT(MONTH FROM logdate))

# INSERT INTO measurement VALUES ('2016-12-02', 1, 1);
INSERT 0 1

# INSERT INTO measurement VALUES ('2016-12-02', 1, 1);
ERROR: duplicate key value violates unique constraint
"measurement_date_part_date_part1_idx"
DETAIL: Key (date_part('year'::text, logdate), date_part('month'::text,
logdate))=(2016, 12) already exists.

* Some of the implementation details of the patch here:

The rules about which columns to show or whether to show the DETAIL at all
are similar to those in BuildIndexValueDescription():

- if user has SELECT privilege on the whole table, simply go ahead

- if user doesn't have SELECT privilege on the table, check that they
can see all the columns in the key (no point in showing partial key);
however abort on finding an expression for which we don't try finding
out privilege situation of whatever columns may be in the expression

Thanks,
Amit

[0]
https://www.postgresql.org/message-id/CANP8%2BjJBpWocfKrbJcaf3iBt9E3U%3DWPE_NC8YE6rye%2BYJ1sYnQ%40mail.gmail.com
[1]
https://www.postgresql.org/message-id/2f8df068-9a49-d74a-30af-7cd17bdee181%40lab.ntt.co.jp

Attachment Content-Type Size
0001-Show-only-the-partition-key-upon-failing-to-find-a-p.patch text/x-diff 18.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2017-02-21 02:01:25 Re: Documentation improvements for partitioning
Previous Message Thomas Munro 2017-02-21 01:44:35 Re: delta relations in AFTER triggers