Hi, When a CHECK constraint is violated, PostgreSQL reports the constraint name and the failing row, but not the constraint expression:
ERROR: new row for relation "staff_sales" violates check constraint
"staff_age_check"
DETAIL: Failing row contains (1, 15).
Users need to inspect the table with \d to find the violated condition.
Is there a reason why the expression is not included in the error detail?
(For example, having such information appear in logs could pose a
security risk.)
Including it in DETAIL would avoid this extra step:
DETAIL: Failing row contains (1, 15). Check constraint expression: (age >=
18).
I have attached a concept patch to illustrate the idea. Please note that
this is a work in progress.
For the implementation, I wrote the expression generation by referring to
pg_get_constraintdef_worker().
Regarding security, I have not yet added any permission checks. I plan to
investigate whether they are needed at all, and if so what form they should
take, using the existing handling of the failing-row detail and the
previous proposal that was already committed as references:
<https://www.postgresql.org/message-id/flat/9f9dc7ae-14f0-4a25-5485-964d9bfc19bd%40lab.ntt.co.jp>
If this proposal is considered worthwhile, I would like to continue
developing the patch myself.
Regards,
Taiki Koshino<[email protected]>
SRA OSS K.K.
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/
v1-0001-Report-CHECK-expression-in-constraint-violation-d.patch
Description: v1-0001-Report-CHECK-expression-in-constraint-violation-d.patch
