Re: Row data is reflected in DETAIL message when constraints fail on insert/update

On Sat, Jun 22, 2019 at 06:40:10PM +0200, Peter J. Holzer wrote:

> > How is it useful in a normally configured database to return row data in
> > error messages?
>
> This is extremely useful. It tells you what data didn't match your
> program's expectations. Otherwise you just get a vague "unique
> constraint violation"

Sure, except some argue that PG not send such information to
the *client* by *default*, which seems to have some merit
(the default should, however, keep logging such data to the
PG log)

This can lead to the following problem:

> and you can then search through a hundred million
> rows of data to find that violation.

which could be solved by passing to the client an identifier
instead of the row data which is also logged to the server
log alongside the row data. The combination of

%m or %n - timestamp
%c - session ID
%l - in-session log line idx
%e - SQLSTATE

would probably suffice if sent to the client, given it is
logged in the server log.

(not that I suggest any such thing as I certainly lack the
skills to provide a patch)

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B