Re: Re: CHECK constraints with plpgsql functions - check 'fires' BEFORE data modification?

Jasen Betts wrote:
> ...
>
>> The 'proper' way to do this (as suggested by earlier posts on this
>> mailing list) is to use partial UNIQUE indexes, but I have problem with
>> that too: indexes are not part of DDL (no matter that primary key
>> constraints and/or unique constraints use indexes to employ those
>> constraints), and as far as I know there is no 'partial unique
>> constraint' in SQL?
>
> huh?
>
> create unique index agreements_approved_onlyone on agreements(subject_id)
> where agreement_state='approved';

That will create unique index - the way I'm doing it now. The difference
is just semantic - indexes are not part of DDL, they are RDBMS's aids on
improving database performance.

>> And, wouldn't it be better to have CHECK constraints check the data
>> AFTER data-modification?
>
> no.

Why not?

>> Now, the above does not work because CHECK function is fired BEFORE
>> actuall data modification takes place so I can end up with two rows with
>> 'approved' state for particular subject_id. If I change the CASE...WHEN
>> conditions so that function returns TRUE only when there is 0 rows for
>> the state = 'approved' then I have problems with UPDATEing:
>>
>> UPDATE agreements SET agreement_state = 'obsolete' where subject_id =
>> <whatever> AND agreement_state = 'approved'
>>
>> That update will fail because the CHECK function is fired before the
>> actuall update, and there is allready a row with state = 'approved' in
>> the table.
>
> fix the check so that it knows what the new state will be. then it test
> if the proposed new state is compatible with the old state.

Yes, but for that I need to know weather DML command is INSERT or
UPDATE, and function called by check constraint has no way of knowing
that. Yes, I know that I could use trigger, even constraint trigger, but
I prefer not to use triggers if I don't have to.

> (but seriously, first explain why the index doesn't work)

It does work, and the more I look at it that seems to be the only proper
way of doing what I want. But, as I've said, indexes are not DDL, and
strictly my data-integrity rule needs to be done within DDL, not
indexes. Since there is no other way I will, of course, use indexes.

(Similarly I'd encourage one to use UNIQUE CONSTRAINT over UNIQUE INDEX
because CONSTRAINTS are part of DDL, while indexes are not. It doesn't
matter that postgres, and any other RDBMS for that matter, will empower
UNIQUE CONSTRAINT using indexes.)

>
> ...
> CONSTRAINT check_agreements_onlyone_approved CHECK
> (check_agreements_onlyone_approved(subject_id,agreement_id,agreement_state))
> );
>
> CREATE OR REPLACE FUNCTION check_agreements_onlyone_approved(the_subject_id
> integer, the_pkey integer, the_new_state enum_agreement_state)
> RETURNS boolean AS
> $$
> SELECT
> CASE COUNT(agreement_id)
> WHEN 0 THEN true
> WHEN 1 THEN $3 != 'approved'
> ELSE false
> END FROM agreements WHERE subject_id = $1 AND agreement_state
> = 'approved' and agreement_id != $2
> $$ LANGUAGE 'sql';
>
> still not perfect: if you need to change the agreement_id this will
> block you from doing that on approved agreements.

Yes, this seems more and more as a bad idea :)

Mario