[Pkg-postgresql-public] Bug#859033: [GENERAL] Debian Bug#859033: pg_dump: creates dumps that cannot be restored

[David G. Johnston]

On Fri, Mar 31, 2017 at 8:21 AM, Thorsten Glaser <t.glaser at tarent.de> wrote:

> On Fri, 31 Mar 2017, Adrian Klaver wrote:
>
> > > ① 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."
>
> Ah, okay. So, …
>
> > > 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
>
> … this would be the proper fix, but…
>
> > > 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
>
> … this.
>
> Can you suggest a better way to do this?


​The usual solution is to add a validation trigger on the derived_things
table.  It is functionally the same as your CHECK constraint.  You could
couple it with a FK "child REFERENCES things (thing_id)".

If going down the trigger route you could consider adding an insert trigger
on things which will also insert a record into a "child_things" table
containing on the PK value in a one-to-one relationship.  An FK on that
table could be define ON UPDATE/ON DELETE CASCADE.  The derived_things
table could then point to child_things table for it FK.  In effect you've
just created two (materialized) views of the existing things view just like
you tried above but this one works because its indexes and physical and so
can be the target of a FK.  Because of those properties is just requires a
bit of trigger-driven maintenance.


> Caveat: I cannot split the “things” table into two.
>

​The original table stays so this shouldn't apply - you do need to be able
to create new tables and triggers.

David J.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.alioth.debian.org/pipermail/pkg-postgresql-public/attachments/20170331/19b22f70/attachment.html>