uj1scCtj

1drop schema if exists X cascade;
2create schema X;
3
4create domain X.an_illegal_regex as text check ( value ~ '(' );
5
6create table X.table_with_illegal_constraint (
7  a text,
8  constraint "column a must have a bogus value" check ( a::X.an_illegal_regex = a ) );
9
10select * from X.table_with_illegal_constraint;
11
12insert into X.table_with_illegal_constraint values
13  ( 'xxx' ),
14  -- ( 'xxx' ),
15  ( 'foo' ),
16  ( 'xyx' );
17
18⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔
19╔═══╗
20â•‘ a â•‘
21╠═══╣
22╚═══╝
23
24psql:db/experiments/pg-error-fail-illegal-regex.sql:17: ERROR:
25invalid regular expression: parentheses () not balanced
26⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔⛔
27
28FAILURE: the error is really in line 5 where a syntactically invalid
29RegEx is created; the fact that it is a RegEx and not a general
30string is obvious from the semantics of the `~` (tilde) operator at
31that point in time.
32
33FAILURE: the offending RegEx is not referred to and not quoted in the error
34message. As such, it could be anywhere in my many, many kLOCs big
35DB definition. I cannot even search the RegEx with a RegEx because all
36I know is some parenthesis is missing, somewhere: RegExes cannot match
37parentheses, and PG RegExes do not have a unique syntactic marker to them.
38
39FAILURE: before the `insert` statement, everything runs dandy. We could have
40built an entire data warehouse application on top of a table definition
41that can never be syntactically processed but which will only fail when
42someone accidentally tries to insert a line.
43
44FAILURE: I can select from a table with a syntactically invalid definition.