Re: Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message

Hi José and Robert, thanks for your time and a review. Comments below.

On 11/10/11 03:47, Robert Haas wrote:
> It does this already, without this patch. This patch is about CHECK
> constraints, not UNIQUE ones.

That's right. This is how to check what the patch changes:

jkt=> CREATE TABLE tbl (name TEXT PRIMARY KEY, a INTEGER CHECK (a>0));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"tbl_pkey" for table "tbl"
CREATE TABLE
jkt=> INSERT INTO tbl (name, a) VALUES ('x', 10);
INSERT 0 1
jkt=> UPDATE tbl SET a = -a;
ERROR: new row for relation "tbl" violates check constraint "tbl_a_check"
DETAIL: New row with data (x, -10) violates check constraint "tbl_a_check".

The last line, the detailed error message, is added by the patch.

> I believe we've previously rejected patches along these lines on the
> grounds that the output could realistically be extremely long.
> Imagine that you have a table with an integer primary key column and a
> text column. The integer column has a check constraint on it. But
> the text column might contain a kilobyte, or a megabyte, or even a
> gigabyte worth of text, and we don't necessarily want to spit that all
> out on an error. For unique constraints, we only emit the values that
> are part of the constraint, which in most cases will be relatively
> short (if they're more than 8kB, they won't fit into an index block);
> but here the patch wants to dump the whole tuple, and that could be
> really big.

That's an interesting thought. I suppose the same thing is an issue with
unique keys, but they tend to not be created over huge columns, so it
isn't really a problem, right?

Would you object to a patch which outputs just the first 8kB of each
column? Having at least some form of context is very useful in my case.

(And as a side note, I'm not really familiar with Postgres' internals,
so it took me roughly six hours to arrive to a working patch from the
very start. I'd therefore welcome pointers about the best way to achieve
that with Postgres' string stream interface.)

With kind regards,
Jan

--
Trojita, a fast e-mail client -- http://trojita.flaska.net/