implementing (something like) UNIQUE constraint using PL/pgSQL

Поиск
Список
Период
Сортировка
От Tomas Vondra
Тема implementing (something like) UNIQUE constraint using PL/pgSQL
Дата
Msg-id 45B9CCC6.4000109@fuzzy.cz
обсуждение исходный текст
Ответы Re: implementing (something like) UNIQUE constraint using PL/pgSQL
Список 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
manyrows 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
manyrows 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.)
 


В списке pgsql-sql по дате отправления:

Предыдущее
От: Achilleas Mantzios
Дата:
Сообщение: Re: Intersection of two date interval
Следующее
От: Mario Splivalo
Дата:
Сообщение: Re: Using Temporary Tables in postgres functions