pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedimplementing (something like) UNIQUE constraint using PL/pgSQL
3+ messages / 3 participants
[nested] [flat]
* implementing (something like) UNIQUE constraint using PL/pgSQL
@ 2007-01-26 09:41 Tomas Vondra <tv@fuzzy.cz>
2007-01-27 07:55 ` Re: implementing (something like) UNIQUE constraint using PL/pgSQL Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 3+ messages in thread
From: Tomas Vondra @ 2007-01-26 09:41 UTC (permalink / raw)
To: pgsql-sql
Hello,
in our application we need to implement a constraint that enforces 'at
most N rows with this value', that is we have a table with 'flag' column
and for each value there should be at most 10 rows (for example, the
exact number does not matter).
I'm trying to implement a PL/pgSQL trigger to enforce this constraint,
but I'm not sure my solution is 'the best one possible'. The first
solution I came up with is this:
=====================================================================
CREATE OR REPLACE FUNCTION at_most() RETURNS trigger AS $$
DECLARE
v_cnt INTEGER;
p_cnt INTEGER;
BEGIN
IF TG_NARGS >= 1 THEN
p_cnt := TG_ARGV[0]::integer;
ELSE
p_cnt := 1;
END IF;
SELECT COUNT(*) INTO v_cnt FROM my_table WHERE flag = NEW.flag;
IF v_cnt > p_cnt THEN
RAISE EXCEPTION 'Too many rows with this flag!'
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER at_most AFTER INSERT OR UPDATE ON my_table FOR EACH ROW
EXECUTE PROCEDURE at_most(10);
=====================================================================
But that obviously does not work as two sessions can reach the SELECT
INTO statement at the same time (or until one of them commits). Thus
there could be more than 'cnt' rows with the same value.
Then I used a 'FOR UPDATE' lock on a separate 'lock table' that already
contains all possible values of 'flag' (in reality the trigger tries to
insert that value and catches the 'duplicate key' exception but that's
not important here). The trigger is then
=====================================================================
CREATE OR REPLACE FUNCTION at_most() RETURNS trigger AS $$
DECLARE
v_cnt INTEGER;
p_cnt INTEGER;
BEGIN
IF TG_NARGS >= 1 THEN
p_cnt := TG_ARGV[0]::integer;
ELSE
p_cnt := 1;
END IF;
PERFORM flag FROM lock_table WHERE flag = NEW.flag FOR UPDATE;
SELECT COUNT(*) INTO v_cnt FROM my_table WHERE flag = NEW.flag;
IF v_cnt > p_cnt THEN
RAISE EXCEPTION 'Too many rows with this flag!';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER at_most AFTER INSERT OR UPDATE ON my_table FOR EACH ROW
EXECUTE PROCEDURE at_most(10);
=====================================================================
This works (af far as I know), but I'm not sure it's the best possible
solution - for example I'd like to remove the lock table. Is there some
'more elegant' way to do this?
Tomas
PS: Is there some up to date 'best practices' book related to PL/pgSQL?
All books I've found on Amazon are pretty old (about 5 years) or are
related to 'beginners' or different areas of development (PHP, etc.)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: implementing (something like) UNIQUE constraint using PL/pgSQL
2007-01-26 09:41 implementing (something like) UNIQUE constraint using PL/pgSQL Tomas Vondra <tv@fuzzy.cz>
@ 2007-01-27 07:55 ` Bruno Wolff III <bruno@wolff.to>
2007-01-27 11:58 ` Re: implementing (something like) UNIQUE constraint using PL/pgSQL Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 3+ messages in thread
From: Bruno Wolff III @ 2007-01-27 07:55 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-sql
On Fri, Jan 26, 2007 at 10:41:26 +0100,
Tomas Vondra <tv@fuzzy.cz> wrote:
>
> in our application we need to implement a constraint that enforces 'at
> most N rows with this value', that is we have a table with 'flag' column
> and for each value there should be at most 10 rows (for example, the
> exact number does not matter).
Another approach is to add a instance number column and constrain that
value to be between 1 and 10. And make value and instance number unique.
You'll need to do a bit more work when inserting new rows than normal
(to find a free instance number). This should be very robust against
getting in a bad state.
If you go with enforcing the condition with a trigger you need to be careful
about simultaneous inserts and visibility.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: implementing (something like) UNIQUE constraint using PL/pgSQL
2007-01-26 09:41 implementing (something like) UNIQUE constraint using PL/pgSQL Tomas Vondra <tv@fuzzy.cz>
2007-01-27 07:55 ` Re: implementing (something like) UNIQUE constraint using PL/pgSQL Bruno Wolff III <bruno@wolff.to>
@ 2007-01-27 11:58 ` Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Glaesemann @ 2007-01-27 11:58 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Tomas Vondra <tv@fuzzy.cz>; pgsql-sql
On Jan 27, 2007, at 16:55 , Bruno Wolff III wrote:
> On Fri, Jan 26, 2007 at 10:41:26 +0100,
> Tomas Vondra <tv@fuzzy.cz> wrote:
>>
>> in our application we need to implement a constraint that enforces
>> 'at
>> most N rows with this value', that is we have a table with 'flag'
>> column
>> and for each value there should be at most 10 rows (for example, the
>> exact number does not matter).
>
> Another approach is to add a instance number column and constrain that
> value to be between 1 and 10. And make value and instance number
> unique.
> You'll need to do a bit more work when inserting new rows than normal
> (to find a free instance number). This should be very robust against
> getting in a bad state.
> If you go with enforcing the condition with a trigger you need to
> be careful
> about simultaneous inserts and visibility.
This may have already been mentioned upthread, but this sounds like a
special case of the SQL queues I saw on Greg Sabino Mullane's blog:
http://people.planetpostgresql.org/greg/index.php?/archives/89-
Implementing-a-queue-in-SQL-Postgres-version.html
Perhaps you could tweak that to serve your needs.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-01-27 11:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-26 09:41 implementing (something like) UNIQUE constraint using PL/pgSQL Tomas Vondra <tv@fuzzy.cz>
2007-01-27 07:55 ` Bruno Wolff III <bruno@wolff.to>
2007-01-27 11:58 ` Michael Glaesemann <grzm@seespotcode.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox