Thread: multi-row check constraints?
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. Thank you, Mark
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 addressedand may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you arenot the intended recipient, you are notified that any dissemination, distribution or copying of this communication isstrictly 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? > 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
-----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
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
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