Skip site navigation (1) Skip section navigation (2)

Re: pg_dump ordering in 8.1.3

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Sue Fitt <sue(at)inf(dot)ed(dot)ac(dot)uk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: pg_dump ordering in 8.1.3
Date: 2006-07-27 12:56:34
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Sue Fitt <sue(at)inf(dot)ed(dot)ac(dot)uk> writes:
> I am working on a database of gradually increasing complexity, with 
> several tables connected by foreign keys. Validity of some fields is 
> also checked by triggers etc, some in plpgsql and some in plperl. I now 
> have the situation where I have a table X with a field which uses a 
> domain 'word'. The characters possible in this domain (a-z plus a number 
> of others) are listed in table Y. I then have a plperl function 
> check_word which uses a query to get the characters from table Y, and 
> compiles these characters into a variable which is used to check the 
> contents of the domain.

The function is called by a check constraint I take it?  We really don't
support check constraints that look at anything except the current row
of the current table.  Anything else introduces dependencies that the
system does not know about and can't promise to honor, as you're finding
out :-(.  An example of the kind of problem you'll face is that changes
to the content of table Y will not result in rechecking the constraints
on other tables, even though changing Y might've caused those
constraints to fail.

I think you'd be best off hard-wiring the list of allowed characters
into a check constraint associated with the domain 'word', eg

	create domain word as text
		check (value ~ '^[a-zA-Z0-9_]+$');

The separate table listing the allowed characters might be good for some
things, but not this.

			regards, tom lane

In response to


pgsql-novice by date

Next:From: Michael SwierczekDate: 2006-07-27 13:08:11
Subject: Re: Novice! How to run pg_dump from within Java?
Previous:From: Tom LaneDate: 2006-07-27 12:39:35
Subject: Re: What order in primary key definition ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group