Generic trigger to restrict insertions based on co

问题:

Background

In a PostgreSQL 9.0 database, there are various tables that have many-to-many relationships. The number of those relationships must be restricted. A couple of example tables include:

CREATE TABLE authentication (
  id bigserial NOT NULL, -- Primary key
  cookie character varying(64) NOT NULL, -- Authenticates the user with a cookie
  ip_address character varying(40) NOT NULL -- Device IP address (IPv6-friendly)
)

CREATE TABLE tag_comment (
  id bigserial NOT NULL, -- Primary key
  comment_id bigint, -- Foreign key to the comment table
  tag_name_id bigint -- Foreign key to the tag name table
)

Different relationships, however, have different limitations. For example, in the authentication table, a given ip_address is allowed 1024 cookie values; whereas, in the tag_comment table, each comment_id can have 10 associated tag_name_ids.

Problem

Currently, a number of functions have these restrictions hard-coded; scattering the limitations throughout the database, and preventing them from being changed dynamically.

Question

How would you impose a maximum many-to-many relationship limit on tables in a generic fashion?

Idea

Create a table to track the limits:

CREATE TABLE imposed_maximums (
  id serial NOT NULL,
  table_name  character varying(128) NOT NULL,
  column_group character varying(128) NOT NULL,
  column_count character varying(128) NOT NULL,
  max_size INTEGER
)

Establish the restrictions:

INSERT INTO imposed_maximums
  (table_name, column_group, column_count, max_size) VALUES
  ('authentication', 'ip_address', 'cookie', 1024);
INSERT INTO imposed_maximums
  (table_name, column_group, column_count, max_size) VALUES
  ('tag_comment', 'comment_id', 'tag_id', 10);

Create a trigger function:

CREATE OR REPLACE FUNCTION impose_maximum()
  RETURNS trigger AS
$BODY$
BEGIN
  -- Join this up with imposed_maximums somehow?
  select
    count(1)
  from
    -- the table name
  where
    -- the group column = NEW value to INSERT;

  RETURN NEW;
END;

Attach the trigger to every table:

CREATE TRIGGER trigger_authentication_impose_maximum
  BEFORE INSERT
  ON authentication
  FOR EACH ROW
  EXECUTE PROCEDURE impose_maximum();

Obviously it won't work as written... is there a way to make it work, or otherwise enforce the restrictions such that they are:

• in a single location; and
• not hard-coded?

Thank you!

回答1:

I've been doing a similar type of generic triggers. The most tricky part is to get the value entry in the NEW record based on the column name.

I'm doing it the following way:

• convert NEW data into array;
• find the attnum of the column and use it as an index for the array.

This approach works as long as there're no commas in the data :( I don't know of other ways how to convert NEW or OLD variables into the array of values.

The following function might help:

CREATE OR REPLACE FUNCTION impose_maximum() RETURNS trigger AS $impose_maximum$
DECLARE
  _sql  text;
  _cnt  int8;
  _vals text[];
  _anum int4;
  _im   record;

BEGIN
 _vals := string_to_array(translate(trim(NEW::text), '()', ''), ',');

 FOR _im IN SELECT * FROM imposed_maximums WHERE table_name = TG_TABLE_NAME LOOP
  SELECT attnum INTO _anum FROM pg_catalog.pg_attribute a
    JOIN pg_catalog.pg_class t ON t.oid = a.attrelid
   WHERE t.relkind = 'r' AND t.relname = TG_TABLE_NAME
     AND NOT a.attisdropped AND a.attname = _im.column_group;

  _sql := 'SELECT count('||quote_ident(_im.column_count)||')'||
          ' FROM '||quote_ident(_im.table_name)||
          ' WHERE '||quote_ident(_im.column_group)||' = $1';

  EXECUTE _sql INTO _cnt USING _vals[_anum];

  IF _cnt > CAST(_im.max_size AS int8) THEN
    RAISE EXCEPTION 'Maximum of % hit for column % in table %(%=%)',
      _im.max_size, _im.column_count,
      _im.table_name, _im.column_group, _vals[_anum];
  END IF;
 END LOOP;

 RETURN NEW;
END; $impose_maximum$ LANGUAGE plpgsql;

This function will check for all conditions defined for a given table.

回答2:

Yes, there is a way to make it work.

In my personal opinion your idea is the way to go. It just needs one level of "meta". So, the table imposed_restrictions should have trigger(s), which is (are) fired after insert, update and delete. The code should then in turn create, modify or remove triggers and functions.

Take a look at execute statement of PL/pgSQL, which - essentially - allows you to execute any string. Needless to say, this string may contain definitions of triggers, functions, etc. Obviously, you have the access to OLD and NEW in the triggers, so you can fill in the placeholders in the string and you are done.

I believe you should be able to accomplish what you want with this answer. Please note that this is my personal view on the topic and it might not be an optimal solution - I would like to see a different, maybe also more efficient, approach.

Edit - Below is a sample from one of my old projects. It is located inside the function that is triggered before update (though now I get to think of it, maybe it should have been called after ;) And yes, the code is messy, as it does not use the nice $escape$ syntax. I was really, really young then. Nonetheless, the snipped demonstrates that it is possible to achieve what you want.

query:=''CREATE FUNCTION '' || NEW.function_name || ''('';
IF NEW.parameter=''t'' THEN
  query:=query || ''integer'';
END IF;
query:=query || '') RETURNS setof '' || type_name || '' AS'' || chr(39);
query:=query || '' DECLARE list '' || type_name || ''; '';
query:=query || ''BEGIN '';
query:=query || '' FOR list IN EXECUTE '' || chr(39) || chr(39);
query:=query || temp_s || '' FROM '' || NEW.table_name;
IF NEW.parameter=''t'' THEN
  query:=query || '' WHERE id='' || chr(39) || chr(39) || ''||'' ||  chr(36) || ''1'';
ELSE
  query:=query || '';'' || chr(39) || chr(39);
END IF;
query:=query || '' LOOP  RETURN NEXT list; '';
query:=query || ''END LOOP; RETURN; END; '' || chr(39);
query:=query || ''LANGUAGE '' || chr(39) || ''plpgsql'' || chr(39) || '';'';
EXECUTE query;

回答3:

These function + trigger could be used as a template. If You combine them with @Sorrow 's technique of dynamically generating the functions + triggers, this could solve the OP's problem. Please note that, instead of recalculating the count for every affected row (by calling the COUNT() aggregate function), I maintain an 'incremental' count. This should be cheaper.

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path='tmp';

CREATE TABLE authentication
  ( id bigserial NOT NULL -- Primary key
  , cookie varchar(64) NOT NULL -- Authenticates the user with a cookie
  , ip_address varchar(40) NOT NULL -- Device IP address (IPv6-friendly)
  , PRIMARY KEY (ip_address, cookie)
);

CREATE TABLE authentication_ip_count (
    ip_address character varying(40) NOT NULL
      PRIMARY KEY -- REFERENCES authentication(ip_address)
    , refcnt INTEGER NOT NULL DEFAULT 0
    --
    -- This is much easyer:
    --  keep the max value inside the table
    --        + use a table constraint
    -- , maxcnt INTEGER NOT NULL DEFAULT 2 -- actually 100
    -- , CONSTRAINT no_more_cookies CHECK (refcnt <= maxcnt)
        );

CREATE TABLE imposed_maxima
  ( id serial NOT NULL
  , table_name  varchar NOT NULL
  , column_group varchar NOT NULL
  , column_count varchar NOT NULL
  , max_size INTEGER NOT NULL
  , PRIMARY KEY (table_name,column_group,column_count)
);
INSERT INTO imposed_maxima(table_name,column_group,column_count,max_size)
              VALUES('authentication','ip_address','cookie', 2);

CREATE OR REPLACE FUNCTION authentication_impose_maximum()
  RETURNS trigger AS
$BODY$
DECLARE
        dummy INTEGER;
BEGIN
  IF (TG_OP = 'INSERT') THEN
        INSERT INTO authentication_ip_count (ip_address)
        SELECT sq.*
        FROM ( SELECT NEW.ip_address) sq
        WHERE NOT EXISTS (
                SELECT *
                FROM authentication_ip_count nx
                WHERE nx.ip_address = sq.ip_address
                );

        UPDATE authentication_ip_count
        SET refcnt = refcnt + 1
        WHERE ip_address = NEW.ip_address
                ;
        SELECT COUNT(*) into dummy -- ac.refcnt, mx.max_size
        FROM authentication_ip_count ac
        JOIN imposed_maxima mx ON (1=1) -- outer join
        WHERE ac.ip_address =  NEW.ip_address
        AND mx.table_name  = 'authentication'
        AND mx.column_group = 'ip_address'
        AND mx.column_count = 'cookie'
        AND ac.refcnt > mx.max_size
                ;
        IF FOUND AND dummy > 0 THEN
                RAISE EXCEPTION 'Cookie moster detected';
        END IF;


  ELSIF (TG_OP = 'DELETE') THEN

        UPDATE authentication_ip_count
        SET refcnt = refcnt - 1
        WHERE ip_address = OLD.ip_address
                ;
        DELETE FROM authentication_ip_count ac
        WHERE ac.ip_address = OLD.ip_address
        AND ac.refcnt <= 0
                ;
  -- ELSIF (TG_OP = 'UPDATE') THEN
  -- (Only needed if we allow updates of ip-address)
  -- otherwise the count stays the same.

  END IF;

  RETURN NEW;

END;

$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER trigger_authentication_impose_maximum
  BEFORE INSERT OR UPDATE OR DELETE
  ON authentication
  FOR EACH ROW
  EXECUTE PROCEDURE authentication_impose_maximum();

        -- Test it ...
INSERT INTO authentication(ip_address, cookie) VALUES ('1.2.3.4', 'Some koekje' );
INSERT INTO authentication(ip_address, cookie) VALUES ('1.2.3.4', 'kaakje' );
INSERT INTO authentication(ip_address, cookie) VALUES ('1.2.3.4', 'Yet another cookie' );

RESULTS:

INSERT 0 1
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
INSERT 0 1
ERROR:  Cookie moster detected