Thread: pg_dumpall and check constraints
Hello.
Here is an extract from my database definition:
CREATE TABLE information (
fieldName text NOT NULL CHECK (fieldName <> ''),
code char NOT NULL CHECK (code <> ''),
label text,
PRIMARY KEY (fieldName, code)
);
-- Filling the table "information"
COPY information FROM stdin;
station.type H hydrological
parameter.type Q discharge
...
\.
-- Function that checks a field value is in the table "information".
CREATE FUNCTION checkInfo(text, char) RETURNS bool AS
'SELECT $2::char IN (SELECT code FROM information WHERE fieldName = $1::text)'
LANGUAGE 'sql';
-- A table using checkInfo
CREATE TABLE station (
code text NOT NULL,
type char NOT NULL,
name text,
longitude text,
latitude text,
altitude float,
PRIMARY KEY (code),
CONSTRAINT stationCodeNotEmpty
CHECK (code <> ''),
CONSTRAINT existingStationType
CHECK (verifierInfo('station.type', type))
);
As you can guess, the definition order is an important point.
But when I use pg_dumpall, it produces a script that don't respect the order. So I can't use this script to restore the database.
And the question is: Is there a better way to do what I want (checking field values from different tables against data in one table) that allow pg_dumpall to works ?
Thanks.
Guillaume Perréal - Stagiaire MIAG
Cemagref (URH), Lyon, France
Tél: (+33) 4.72.20.87.64
At 09:59 30/06/00 +0200, Guillaume Perréal wrote:
>>>>
<excerpt>
And the question is: Is there a better way to do what I want (checking
field values from different tables against data in one table) that allow
pg_dumpall to works ?
</excerpt><<<<<<<<
I'm not sure what to suggest, but a FOREIGN KEY constraint might help.
You would need to do one of two things:
1. add fieldname to the definition of station, then use
FOREIGN KEY ("fieldname", "type") references "information"
(fieldName, code),
OR
2. create a view:
create view "station_fields" as select * from information where
fieldname = 'station.type';
then use:
FOREIGN KEY ( "type") references "station_fields" ( code),
You'd need to assess the permance issues associated with each choice -
some DB systems don't do views very well, and I have absolutely no
experience with big views under PG.
FWIW, I have just finished writing a modified pg_dump which restores
things in a variety of possibl orders, and works with the example you
quoted.
I'm just waiting on volunteers to test it...
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
Philip Warner wrote:
At 09:59 30/06/00 +0200, Guillaume Perréal wrote:Well, I don't think adding the same value to each row of "station" is the better solution. But it's a solution.
>>>>And the question is: Is there a better way to do what I want (checking field values from different tables against data in one table) that allow pg_dumpall to works ?
<<<<
I'm not sure what to suggest, but a FOREIGN KEY constraint might help. You would need to do one of two things:
1. add fieldname to the definition of station, then use
FOREIGN KEY ("fieldname", "type") references "information" (fieldName, code),
I tried : it doesn't work. It seems that view rows don't have OID, which are used in foreign key. (I deduce that from the error messages I've got when I tried).
OR2. create a view:
create view "station_fields" as select * from information where fieldname = 'station.type';then use:
FOREIGN KEY ( "type") references "station_fields" ( code),
"information" won't be a big view : less than one hundred tuples.You'd need to assess the permance issues associated with each choice - some DB systems don't do views very well, and I have absolutely no experience with big views under PG.
Why not? But I can't promise you to do full testing as I've got a lot of work these days.FWIW, I have just finished writing a modified pg_dump which restores things in a variety of possibl orders, and works with the example you quoted.
I'm just waiting on volunteers to test it...
Thanks.
-- Guillaume Perréal - Stagiaire MIAG Cemagref (URH), Lyon, France Tél: (+33) 4.72.20.87.64
At 15:35 30/06/00 +0200, Guillaume Perréal wrote:
<excerpt><excerpt>>>>>
</excerpt></excerpt><excerpt><excerpt>1. add fieldname to the definition of station, then use
FOREIGN KEY ("fieldname", "type") references "information" (fieldName,
code),
</excerpt>Well, I don't think adding the same value to each row of
"station" is the better solution. But it's a solution.
</excerpt><<<<<<<<
I agree.
<excerpt><excerpt>>>></excerpt></excerpt><excerpt><excerpt>
FOREIGN KEY ( "type") references "station_fields" ( code),
</excerpt>I tried : it doesn't work. It seems that view rows don't have OID, which are used in foreign key. (I deduce
thatfrom the error messages I've got when I tried).
<excerpt>
</excerpt></excerpt><<<<<<<<
Sorry, I assumed if I could define it (under 7.0.2), then it would work. But, as you say, it dies when you do an
insert.
The only other alternative is to create a real table instead of a view (ie. break 'information' into multiple tables).
Orjust do what you have been doing...
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
Guillaume Perréal wrote:
> >
> > OR
> >
> > 2. create a view:
> > create view "station_fields" as select * from information where fieldname = 'station.type';
> >
> > then use:
> >
> > FOREIGN KEY ( "type") references "station_fields" ( code),
>
> I tried : it doesn't work. It seems that view rows don't have OID, which are used in foreign key. (I deduce that from
theerror messages I've got when I tried).
>
For gods sake they don't have. And I'm uncertain that it
should ever work.
The reason is that an FK constraint not only has to be
checked at INSERT/UPDATE time of the referencing table. It
must further ensure that later modifications to the
referenced table don't violate existing references. Now in
the case of a view that is a join over 3 tables, we setup a
multicolumn FK constraint over columns coming from different
base tables (or computed ones). How should a RESTRICT or ON
DELETE CASCADE work in that scenario?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
At 17:56 30/06/00 +0200, Jan Wieck wrote:
>
> For gods sake they don't have. And I'm uncertain that it
> should ever work.
Sorry...I'm the one to blame for the suggestion. My only defense is it was
late, and I was misled by the parser...never the less...
> How should a RESTRICT or ON
> DELETE CASCADE work in that scenario?
Perhaps as Check constraints on all tables in the view...for the most part
I would not expect complex views to be used in this way, but since this is
what the user would have to do anyway, why not do it for them?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
> > How should a RESTRICT or ON > > DELETE CASCADE work in that scenario? > > Perhaps as Check constraints on all tables in the view...for the most part > I would not expect complex views to be used in this way, but since this is > what the user would have to do anyway, why not do it for them? I'd say that for right now, way to complicated... Any non-trivial view would be an incredible pain. Although, once we properly insist on there being a unique constraint on the columns referenced, it would probably be easier (although can you actually put a unique constraint on a view?) Take, for example, create view x as select a.id, c.name from a,b,c where a.id=b.id and a.type=c.type and b.customerid=c.id and c.name < 'Smith'; So, what it the deletion constraint on table c? It's something like, you can't delete a row in c that has a name < 'Smith' and who matches up with an a,b row based on the other constraints and has a referencing row in the table you made the reference from, but I'm not even 100% sure of that. And heaven help you if there are subqueries. And of course, the cascade, set null and set default are even stranger.
Philip Warner wrote:
> At 17:56 30/06/00 +0200, Jan Wieck wrote:
> >
> > For gods sake they don't have. And I'm uncertain that it
> > should ever work.
>
> Sorry...I'm the one to blame for the suggestion. My only defense is it was
> late, and I was misled by the parser...never the less...
Philip, I'm far from blaming anyone for an idea or question.
> > How should a RESTRICT or ON
> > DELETE CASCADE work in that scenario?
>
> Perhaps as Check constraints on all tables in the view...for the most part
> I would not expect complex views to be used in this way, but since this is
> what the user would have to do anyway, why not do it for them?
Was late for me too, and maybe the answer was too lazy. So
let me give you an example of what I meant:
CREATE TABLE t1 (
a integer,
b integer
);
CREATE TABLE t2 (
a integer,
c integer
);
CREATE VIEW v1 AS SELECT t1.a, t1.b + t2.c AS d
FROM t1, t2 WHERE t1.a = t2.a;
Not that complex so far. Now we create a reference as
CREATE TABLE t3 (
x integer,
y integer,
z integer,
FOREIGN KEY (y,z) REFERENCES v1 (a,d)
);
The first problem arising from it is that we are unable to
create a UNIQUE constraint for v1(a,d), which is a
requirement of referential integrity as of the SQL specs. The
system doesn't check if a UNIQUE constraint exists up to now,
even for real tables, but that's another story.
The second problem is how should the system check on a
DELETE FROM t2 WHERE c < 10;
if this would cause any referenced key to disappear from v1?
Can it do anything else than a sequential scan on t3 and then
evaluating v1 for each row found?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
At 11:33 1/07/00 +0200, Jan Wieck wrote:
>
> Was late for me too, and maybe the answer was too lazy. So
> let me give you an example of what I meant:
>
About 5 mins after I hit the send button on my last message I realized the
error in my ways (again). There are probably limitations one could place on
such views, but the effort would be high, and the rewards low.
But, at the risk of yet another ill conceived plan being laid bare, and to
satisfy the original posters requirements, could FOREIGN KEY be extended to
allow:
FOREIGN KEY({<field>|<literal>}...) references <table>({<field>}...)
This seems like a very convenient feature...if it's not too hard.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
Philip Warner wrote:
> At 11:33 1/07/00 +0200, Jan Wieck wrote:
> >
> > Was late for me too, and maybe the answer was too lazy. So
> > let me give you an example of what I meant:
> >
>
> About 5 mins after I hit the send button on my last message I realized the
> error in my ways (again). There are probably limitations one could place on
> such views, but the effort would be high, and the rewards low.
>
> But, at the risk of yet another ill conceived plan being laid bare, and to
> satisfy the original posters requirements, could FOREIGN KEY be extended to
> allow:
>
> FOREIGN KEY({<field>|<literal>}...) references <table>({<field>}...)
>
> This seems like a very convenient feature...if it's not too hard.
The only reason why someone wants to put a <literal> into the
foreign key seems to me as a referencing table identifier. So
that multiple referencing tables would all have their own
possible values in one big primary key table.
First this is already possible by adding such a table
identifier field to the referencing tables and having a
BEFORE trigger enforcing the correct value.
Second it's allways good practice to keep things separate
that are separate.
Thus I don't see the need to add non SQL standard features to
FOREIGN KEY.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #