Message-ID: <4BCC272C.3020505@megafon.hr>
Date: Mon, 19 Apr 2010 11:49:32 +0200
From: Mario Splivalo <mario.splivalo@megafon.hr>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: CHECK constraints with plpgsql functions - check 'fires' BEFORE data
	modification?
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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