Re: Re: Debian Bug#859033: pg_dump: creates dumps that cannot be restored

On 03/31/2017 07:34 AM, Thorsten Glaser wrote:
> Hi *,
>
> while I’d still appreciate help on the bugreport (context is this…
> https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=859033 … one), I’ve
> found this… http://dba.stackexchange.com/a/75635/65843 … which says
> ① that using a CHECK constraint to check data from another table
> is wrong (but not why), and

Because that is a documented limitation:

https://www.postgresql.org/docs/9.6/static/sql-createtable.html

"Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row. The system column
tableoid may be referenced, but not any other system column."

Using a function as a 'cheat' gets you the below. You are asking
Postgres to dive into a function and figure out the embedded dependencies.

> ② that there’s no reason to not have a CHECK constraint in NOT VALID
> mode, as that’s how it operates anyway (when existent right from the
> time the table is created), and
> ③ that NOT VALID constraints are ordered below the data by pg_dump.
>
> So, now I have a workaround (although I still consider it a bug that
> pg_dump creates SQL that cannot ever be restored without manual editing
> and user intervention) requiring a minimal but application-wise (hope‐
> fully) compatible schema change:
>
> --- bugreport.cgi 2017-03-31 16:19:38.565969747 +0200
> +++ testcase.sql 2017-03-31 16:20:10.146336502 +0200
> @@ -22,11 +22,12 @@
> parent BIGINT NOT NULL REFERENCES things(pk),
> child BIGINT NOT NULL REFERENCES things(pk),
> arbitrary_data TEXT NOT NULL,
> - CONSTRAINT derived_things_check_child CHECK (check_derived_is_child(child)),
> - CONSTRAINT derived_things_check_parent CHECK (check_derived_is_parent(parent)),
> PRIMARY KEY (parent, child)
> );
>
> +ALTER TABLE derived_things ADD CONSTRAINT derived_things_check_child CHECK (check_derived_is_child(child)) NOT VALID;
> +ALTER TABLE derived_things ADD CONSTRAINT derived_things_check_parent CHECK (check_derived_is_parent(parent)) NOT VALID;
> +
> -- these will succeed
> INSERT INTO things VALUES (1, 'foo', TRUE);
> INSERT INTO things VALUES (2, 'bar', TRUE);
>
> I’ll see whether this can mitigate the most pressing issues with this.
>
>
> From a comment on http://stackoverflow.com/q/16323236/2171120,
> I also have a more generic suggestion to use an FK instead of a
> CHECK constraint, although I’m not sure that this wouldn’t require
> changes to the application code, and I *am* sure that VIEWs have
> penalties to the query optimiser (probably not a big issue here,
> though).
>
> I was thinking about…
>
> CREATE VIEW vw_things_parents AS SELECT * FROM things WHERE standalone=FALSE;
> CREATE VIEW vw_things_children AS SELECT * FROM things WHERE standalone=TRUE;
>
> DROP TABLE derived_things;
> CREATE TABLE derived_things (
> parent BIGINT NOT NULL REFERENCES vw_things_parents(pk),
> child BIGINT NOT NULL REFERENCES vw_things_children(pk),
> arbitrary_data TEXT NOT NULL,
> PRIMARY KEY (parent, child)
> );
>
> This, however, gives me:
> ERROR: referenced relation "vw_things_parents" is not a table
>
>
> So, I might be doing it wrong (or not?), but how do I solve
> this the best way?
>
> Thanks in advance,
> //mirabilos
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com