Thanks Adrian and David. That all makes sense, and I gather the answer regarding the existing dumps is "no, they can't be restored." So be it. Here's a couple of follow-on comments::
--
![]()
Ideally figure out how to write an actual FK constraint - otherwise use triggers.
I can't really make this an FK. I can (and probably will) put this into a trigger. Although it seems like an extra layer of wrapping just to call a function. I'm curious if there's any conceptual reason why constraints couldn't (as an option) be restored after all the data is loaded, and whether there would be any negative consequences of that? I could see if your data still didn't pass the CHECKs, it's already loaded. But the constraint could then be marked not valid?
-1; pg_dump should not be trying to restore things. The core developers shouldn't really concern themselves with the various and sundry ways people might want to setup such a process. You have tools for dump, and tools for restore, and you can combine them in whatever fashion you deem useful. Or otherwise acquire someone else's ideas.
I get that as a general principle. OTOH, being able to restore your backups isn't just a random or inconsequential feature. I have access to the superuser and can create DBs, but users in more locked down scenarios might not be able to do so.
From the docs:
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.
I wonder if that should say "should not," or be followed by something like this:
n.b., In CHECK expressions, Postgres will not prevent you from calling functions that reference other rows or tables. However, doing so may have undesirable consequences, including the possible inability to restore from output created by pg_dump.
(Are there other possible pitfalls too, or is that the only one?)
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
