Wednesday, May 04, 2011

PostgreSQL Inhertance and Constraints problem solved!

Warning: Using stored procedures and triggers in PostgreSQL is addictive!

    I've been playing around with stored procedures using PLpgSQL language, just to try performance improvements and they are just addictive!

    For example, PostgreSQL has the ability to inherit all data from one or more tables to other table, but unfortunately, any constraint will be inherited as of 9.0.4, and thus, you can not make a FOREIGN KEY constraint agains parent table.

    Fortunately, you can workaround this issue with a little performance impact by using stored procedures, triggers and a separate table for that constraint.

    For example, imagine this scenario:
CREATE TABLE account (
    id SERIAL,
    username VARCHAR(50) UNIQUE NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE employee (
    salary MONEY(6,2) NOT NULL
) INHERITS (account);

CREATE TABLE problematic (
    id SERIAL,
    account_id INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (account_id) REFERENCES account(id)
);

    Suppose we have some data:

INSERT INTO employee(username,salary) VALUES ('foo','1280.90'),('bar','3460.75');

    We will have account filled with those usernames as well as employee filed with the rest of values but the problem will come with a:

INSERT INTO problematic(account_id) VALUES(1);

    Since inheritance does not cover constraints, this last INSERT will fail because it will not find corresponding id on account table, despite it is really there.

    But there is NOT all lost! Fortunately we can place triggers on employee to fill and maintain other intermediary table values for simulating that constraint.

    Solution will be to create that table:

CREATE TABLE account_constraint (
    id INTEGER NOT NULL,
    PRIMARY KEY (id)
);

    And to change employee's FOREIGN KEY constraint this way:

FOREIGN KEY (account_id) REFERENCES account_constraint(id);


    And now... how to keep account_constraint table up to date always and in a safe way? The solution rather than code it by yourself in all clients is to use a procedure and place triggers which will fire BEFORE INSERT, UPDATE and DELETE.

    This could be an example of this procedure:

CREATE OR REPLACE FUNCTION account_id_constraint_handler() RETURNS trigger
AS $$
    BEGIN
       IF (TG_OP = 'INSERT') THEN
          INSERT INTO account_constraint(id) VALUES (NEW.id);
       ELSE
          IF (TG_OP = 'UPDATE') THEN
             UPDATE account_constraint SET id=NEW.id WHERE id=OLD.id;
          ELSE
             DELETE FROM account_constraint WHERE id=OLD.id;
             RETURN OLD;
          END IF;
       END IF;
       RETURN NULL;
    END;
$$
Language 'plpgsql';

    And the last thing to do is to set triggers this way:

CREATE TRIGGER insert_accountID
AFTER INSERT ON user_data
FOR EACH ROW
EXECUTE PROCEDURE account_id_constraint_handler();
CREATE TRIGGER update_accountID
AFTER UPDATE ON user_data
FOR EACH ROW
EXECUTE PROCEDURE account_id_constraint_handler();
CREATE TRIGGER delete_accountID
AFTER DELETE ON user_data
FOR EACH ROW
EXECUTE PROCEDURE account_id_constraint_handler();


    You can now alter any data safely maintaining FOREIGN KEY CONSTRAINT!
And the best: database will do all work needed with only a little impact, since PostgreSQL stores procedures in a precompiled state to improve performance a bit.

    Remember that, once you start using stores procedures (or functions) you will not stop as stated in the warning above!