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

On 2010-04-19, Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr> wrote:
> The 'data integrity' rule for database I'm designing says that any
> subject we're tracking (persons, companies, whatever) is assigned an
> agreement that can be in several states: 'Approved', 'Unapproved' or
> 'Obsolete'. One subject can have only one (or none) 'Approved' or
> 'Unapproved' agreement, and as many (or none) 'Obsolete' agreements.

...

> 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';

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

no.

> CREATE FUNCTION check_agreements_onlyone_approved(a_subject_id integer)
> RETURNS boolean AS
> $$
> SELECT
> CASE COUNT(agreement_id)
> WHEN 0 THEN true
> WHEN 1 THEN true
> ELSE false
> END FROM agreements WHERE subject_id = $1 AND agreement_state = 'approved';
> $$ LANGUAGE 'sql';
>
> 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.

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

...
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.

Newsgroups: gmane.comp.db.postgresql.sql
From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
Subject: Re: CHECK constraints with plpgsql functions - check 'fires' BEFORE data modification?
References: <4BCC272C(dot)3020505(at)megafon(dot)hr>
Organization: Dis (not Dat) Organisation
Followup-To:
X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID(at)6{uf8s;a+M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;(T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6{T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$(at)f%(dot)kl^:7X8rQWd[NTc"P"u6nkisze/Q;8"9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^

On 2010-04-19, Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr> wrote:
> The 'data integrity' rule for database I'm designing says that any
> subject we're tracking (persons, companies, whatever) is assigned an
> agreement that can be in several states: 'Approved', 'Unapproved' or
> 'Obsolete'. One subject can have only one (or none) 'Approved' or
> 'Unapproved' agreement, and as many (or none) 'Obsolete' agreements.
>
> I was thinking on employing the CHECK constraint on agreements table
> that would check that there is only one 'Approved' state per subject.
>
> My (simplified) schema looks like this:
>
> CREATE TYPE enum_agreement_state AS ENUM
> ('unapproved',
> 'approved',
> 'obsolete');
>
> CREATE TABLE subjects
> (
> subject_id serial NOT NULL,
> subject_name character varying NOT NULL,
> CONSTRAINT subjects_pkey PRIMARY KEY (subject_id)
> );
>
> CREATE TABLE agreements
> (
> agreement_id serial NOT NULL,
> subject_id integer NOT NULL,
> agreement_state enum_agreement_state NOT NULL,
> CONSTRAINT agreements_pkey PRIMARY KEY (agreement_id),
> CONSTRAINT agreements_subject_id_fkey FOREIGN KEY (subject_id)
> REFERENCES subjects (subject_id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,

> CONSTRAINT check_agreements_onlyone_approved CHECK
> (check_agreements_onlyone_approved(subject_id))
> );
>
> CREATE FUNCTION check_agreements_onlyone_approved(a_subject_id integer)
> RETURNS boolean AS
> $$
> SELECT
> CASE COUNT(agreement_id)
> WHEN 0 THEN true
> WHEN 1 THEN true
> ELSE false
> END FROM agreements WHERE subject_id = $1 AND agreement_state = 'approved';
> $$ LANGUAGE 'sql';
>
> 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.
>
> Now, I know I could use triggers to achieve desired functionality but I
> try to use triggers as seldom as I can. Often ETL scripts disable
> triggers so I could end up with data integrity broken.
>
> 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?
>
> Does anyone has better suggestion on how to employ the data-integrity
> rules I have?
>
> And, wouldn't it be better to have CHECK constraints check the data
> AFTER data-modification? I also found no reference on using CHECK
> constraints with user-defined functions on postgres manual - there
> should be a mention of the way the CHECK constraint works - that is,
> function referenced by CHECK constraint is fired BEFORE the actual data
> modification occur. The error message is also misleading, for instance,
> when I run the before mentioned UPDATE:
>
> constraint_check=# update agreements set agreement_state = 'obsolete'
> where subject_id = 1 and agreement_state = 'approved';
> ERROR: new row for relation "agreements" violates check constraint
> "check_agreements_onlyone_approved"
>
> Mario
>