multi-row check constraints?

On Tue, 2007-03-20 at 13:21 -0700, Angva wrote:

Dear Postgres fans,

Hi, I was wondering what is the best way to achieve a multi-row check
constraint. For example, you have a table with two columns: ID and
percent, no primary key. The goal is to enforce that all values of
percent, per ID, add up to exactly 100%. I come from an Oracle
background, and what you would probably do on Oracle is create a
materialized view with the sum(percent) grouped by ID, then put a
constraint on the sum column. This problem is also solvable using
triggers, but it's messy and imposes a lot of serialization. Not to
mention easy to get wrong.

So, I've come across this problem in Postgres and was hoping someone
could steer me in the right direction.

Your Oracle solution is interesting, and can indeed be implemented in
PostgreSQL in exactly the same way. Look at materialized views here:

http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html

Another way to do it without using an entire materialized view is to
obtain a row level lock on the ID using SELECT ... WHERE id=123 FOR
UPDATE. To do this you need to have a table that contains all the IDs
and where id has a unique index to prevent race conditions when adding
new IDs.

What are you trying to do exactly? Why does the table have no primary
key?

Regards,
Jeff Davis

Greetings

Select COLUMN FROM TABLE WHERE (some condition) for UPDATE OF COLUMN
is not supported?

what would happen in a Table Deadlock scenario???

M-
---------------------------------------------------------------------------
This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this communication is strictly prohibited.
---------------------------------------------------------------------------
Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire indiqué et peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce document, nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire.
----- Original Message -----
From: "Jeff Davis" <pgsql@j-davis.com>
To: "Angva" <angvaw@gmail.com>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, March 22, 2007 8:09 PM
Subject: Re: [GENERAL] multi-row check constraints?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Hi, I was wondering what is the best way to achieve a multi-row check
constraint. For example, you have a table with two columns: ID and
percent, no primary key. The goal is to enforce that all values of
percent, per ID, add up to exactly 100%. I come from an Oracle
background, and what you would probably do on Oracle is create a
materialized view with the sum(percent) grouped by ID, then put a
constraint on the sum column. This problem is also solvable using
triggers, but it's messy and imposes a lot of serialization. Not to
mention easy to get wrong.

I don't think the triggers solution is that bad. The only trick is using
an intermediate table so that we don't have to recheck the entire table
at the end of the statement:

CREATE TABLE hundred (
id INTEGER NULL,
percent FLOAT NOT NULL
);

CREATE TABLE tracker (
trackid INTEGER
);

CREATE FUNCTION percent_one() RETURNS TRIGGER
LANGUAGE plpgsql
AS $_$
BEGIN
IF TG_OP <> 'INSERT' THEN
INSERT INTO tracker VALUES (OLD.id);
END IF;
IF TG_OP <> 'DELETE' THEN
INSERT INTO tracker VALUES (NEW.id);
END IF;
RETURN NULL;
END;
$_$;

CREATE FUNCTION percent_two() RETURNS TRIGGER
LANGUAGE plpgsql
AS $_$
DECLARE
myrec RECORD;
badids INTEGER = 0;
BEGIN
FOR myrec IN
SELECT id, sum(percent) AS pc FROM hundred
WHERE EXISTS (SELECT 1 FROM tracker WHERE trackid = id)
GROUP BY id
HAVING sum(percent) <> 100
ORDER BY id
LOOP
RAISE WARNING 'Percentage on id % sums to %, not 100', myrec.id, myrec.pc;
badids = badids + 1;
END LOOP;
TRUNCATE TABLE tracker;
IF badids>=1 THEN
RAISE EXCEPTION 'Number of ids not summing to 100: %', badids;
END IF;
RETURN NULL;
END;
$_$;

CREATE TRIGGER percent_one AFTER INSERT OR UPDATE OR DELETE ON hundred
FOR EACH ROW EXECUTE PROCEDURE percent_one();

CREATE TRIGGER percent_two AFTER INSERT OR UPDATE OR DELETE ON hundred
FOR EACH STATEMENT EXECUTE PROCEDURE percent_two();

- -- Fails:
INSERT INTO hundred
SELECT 1,25 UNION ALL SELECT 1,25 UNION ALL SELECT 2,33;

- -- Works:
INSERT INTO hundred
SELECT 1,45 UNION ALL SELECT 1,55;

- -- Works:
UPDATE hundred SET id=2 where id=1;

- -- Fails:
UPDATE hundred SET percent=55.5 WHERE percent = 55;

- -- Works:
INSERT INTO hundred
SELECT 3,33.5 UNION ALL SELECT 3,55.5 UNION ALL SELECT 3,11.0;

- -- Fails:
DELETE FROM hundred WHERE percent = 55.5;

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200703222156
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFGAzQ1vJuQZxSWSsgRA9WIAKCXf3t3MkSj2xoXLoScx3lu0aBwQQCfUiTW
is9ZKyAPuzaAvnkMjP0dXEc=
=BeQC
-----END PGP SIGNATURE-----

On Thu, 2007-03-22 at 20:48 -0400, Martin Gainty wrote:

Greetings

Select COLUMN FROM TABLE WHERE (some condition) for UPDATE OF COLUMN
is not supported?

Yes, it is supported.

what would happen in a Table Deadlock scenario???

The deadlock detector detects the deadlock, causing one transaction to
be aborted with an error.

What kind of deadlock scenario are you concerned about?

Regards,
Jeff Davis

Sorry for the late reply, but thanks for your input, Jeff and Greg.

Greg, the trigger percent_two works for each statement. Would it be
possible to defer any check until the end of the transaction? For
example, I may need to insert into hundred values(1,25), followed by
(1,75), without the 25 immediately bombing.

Also is this solution safe for concurrent DML? I recently read an
Oracle solution to a similar problem in this blog entry's comments -
http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html
- and they said you need to lock before and after the validation.

Thanks,
Mark

On Mar 22, 8:09 pm, p...@j-davis.com (Jeff Davis) wrote:

On Tue, 2007-03-20 at 13:21 -0700, Angva wrote:

Your Oracle solution is interesting, and can indeed be implemented in
PostgreSQL in exactly the same way. Look at materialized views here:

http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html

Another way to do it without using an entire materialized view is to
obtain a row level lock on the ID using SELECT ... WHERE id=123 FOR
UPDATE. To do this you need to have a table that contains all the IDs
and where id has a unique index to prevent race conditions when adding
new IDs.

What are you trying to do exactly? Why does the table have no primary
key?

Thanks for that info. The materialized view functions look a bit
unwieldy for this one purpose...Your other idea might be simpler to
implement.

What I'm trying to accomplish...I'm dealing with financial accounts,
each of which has a purchase limit of $x, and one or more sub-
accounts. Each sub-account is allocated a percentage of this limit. So
account A could have sub-accounts 1, 2 and 3, with 50%, 30% and 20%
respectively allocated. Or there could be just one sub-account with
exactly 100%.

The "real" table actually does have a primary key - I was using a
simplified example to make my point.

Thanks for your input,
Mark