Re: pointer to feature comparisons, please - Mailing list pgsql-general

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Kevin Hunter
>Sent: woensdag 13 juni 2007 22:03
>To: Stefan Kaltenbrunner
>Cc: PostgreSQL General List
>Subject: Re: [GENERAL] pointer to feature comparisons, please
>
>At 3:26p -0400 on 13 Jun 2007, Stefan Kaltenbrunner wrote:
>>> The way that I currently know how to do this in Postgres is with
>>> PLpgSQL functions.  Then I add something like
>>>
>>> CONSTRAINT away_team_is_playing CHECK ( NOT teamIsPlaying (
>>> awayteamid, timeid ) )
>>>
>>> to the table schema.
>>
>> well doing it that way is usually not a good idea at all (you cannot
>> actually use arbitrary queries in a CHECK constraint in pg either -
>> using a function to hide that is cheating the database -
>oracle might
>> actually be more(!) clever here not less ...). this why you can get
>> into all kind of weird situations with losing the integrity of your
>> data or running into serious issues during dump/ restore for example.
>
>I was /hoping/ for a response like this!  Thanks!  Okay.  I'll bite.
>Why can't they be used in general?  Is it the same problem
>that the trigger has (below)?
>
>> What you need to do here is to use a trigger.
>
> From online docs regarding Oracle, this is not 100% safe either:
>
>(http://download-east.oracle.com/docs/cd/B14117_01/appdev.101/b10795/
>adfns_co.htm)
>'To enforce this rule without integrity constraints, you can
>use a trigger to query the department table and test that each
>new employee's department is valid. But this method is less
>reliable than the integrity constraint. SELECT in Oracle
>Database uses "consistent read", so the query might miss
>uncommitted changes from other transactions.'

For constraints, you don't want that to happen obviously...
In fact, if you run serializable the problems are even bigger.

In Oracle you should use SELECT FOR UPDATE for such constraints. They do
interfere with concurrency a bit, but you can in fact guarentee you
constraints (to a certain better point). It does require a lot of
thought nevertheless and its troublesome to get right.

In PostGreSQL there are more limitations to guarenteeing such
constraint. You can go a long with with SELECT FOR SHARE, but you can
run into problems when using serializable isolation. It's a bit better
on concurrency (it seems), but cannot enforce the constraint up to the
level Oracle can.

It's a tricky subject, it requires a lot of work for a single
constraint. Also you must be very aware of the limitations of such
constructs, since many are impossible to guarentee at this point in
time. In general, the world is less concerned with it.

>It seems to me that there are certain situations where,
>especially in a highly normalized data model, that you'd
>/have/ to have multiple checks of even other tables.  What
>theory am I missing if this is not the case?
>
>(I'm curious as well for another project on which I'm working
>that does use pg and currently uses a function in just this fashion.)

They should use triggers. Also sometimes it possible to transform the
database schema in a way that you can enforce the constraint with
build-in (foreign key) constraints.

The general problem with these type of constraints is that they are
assumed to be true at ALL times. However it is possible to violate the
constraint, contradicting the assumption we just made. For triggers
there do not exist such assumptions.
Unless the database is going to support constraints with subqueries
(which is very hard to achieve and quite involved), we cannot rely on
the assuption that constraints are always true. In addition, don't
expect this type of support anytime soon on any opensource/commercial
database.

- Joris