Re: multi-row check constraints?

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(at)j-davis(dot)com>
To: "Angva" <angvaw(at)gmail(dot)com>
Cc: <pgsql-general(at)postgresql(dot)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
>