Re: Problems with Error Messages wrt Domains, Checks - Mailing list pgsql-hackers

---------- Forwarded message ----------
From: john frazer <johnfrazer783@gmail.com>
Date: Sat, Mar 17, 2018 at 6:28 PM
Subject: Re: Problems with Error Messages wrt Domains, Checks
To: "David G. Johnston" <david.g.johnston@gmail.com>

>     As such, it could be anywhere in my many, many kLOCs big DB definition. I cannot even search the RegEx with a RegEx because all I know is some parenthesis is missing, somewhere:
>
> ​Well, the error does point to the first statement in the chain of issues - working backward a couple of steps is possible.

In this particular case that is possible, and I did manage to do it. The point is that in the
general case the faulty regular expression could be anywhere, and there's no clue given at all.

>     FAILURE: before the insert statement, everything runs dandy. We could have built an entire data warehouse application on top of a table definition that can never be syntactically processed but which will only fail when someone accidentally tries to insert a line.
>
> ​Since this is going to fail every single time you add a record I'm lacking sympathy here.  "Accidentally tries to insert a line" - if the table wasn't meant to be used why does it exist in the first place?​  And if it is intended to be used then functional testing should quickly point out something like this.

But there clearly can be tables that are used only now and then and might get checked
for absence of rows. But regardless, I think the point stands that ideally you shouldn't
be able to succesfully declare nonsensical objects and only be told so some kinds of
usage patterns by runtime errors (with defective contexts), and in most cases, pgSQL
does keep that promise.

> ​I suppose the best practice when dealing with a lack of information in the error handle code flows is to limit the amount of context that is in scope by unit testing.  And while they are absolutely short-comings overcoming them has cost in terms of both developer effort and, more importantly, runtime performance.

I'm afraid no amount of unit testing of the DDL code can do this for me. Yes,
in the first reported cases (the invalid RegExp), I can make sure I use each
expression at least once so unsyntactic ones will make themselves shown. But
in the other two cases, well, the production environment in which this came up
has an insert statement that takes data from a largish source into the target table
(20k rows of altogether >2m rows), and I *can't* unit test that data.

FWIW the workaround that IÄve found is this:

create table X.table_with_constraints (
  my_column text,
  constraint "my_column must start with 'x'"  check ( Q.starts_with_x( my_column ) ),
  constraint "my_column must have 3 chrs"     check ( Q.has_3_characters( my_column ) ) );

In other words, I dispense with domains and use (small, boolean) functions (defined as `select` one-liners)
because only then do I get told what piece of data comes doen the wrong way and where.
It's a shame because this is essentially what I expect to do in a language like
JavaScript.