PostgreSQL: 1: 1 forced use for additional plugin

Question

Our product has one main module and several additional plugins.

At the core is a database table called ticket_type .

An additional plugin extends the ticket_type table through a 1: 1 ratio. This table is called myplugin_ticket_type_extension .

For each line in myplugin_ticket_type_extension there is a line in ticket_type . This is enforced through ForeignKey. Still up to the problems :-)

Now the tricky part: how to ensure that the line myplugin_ticket_type_extension has a line in each line in ticket_type ?

The hard part: myplugin is an optional plugin. The product core should not know anything about this plugin.

Answer 1

The easiest way to enforce this is to add a second foreign key to ticket_type , which refers to the extension table.

The difficulty with this circular dependency is that INSERT in any table will violate the foreign key constraint before you can create another record. You can avoid this by using pending restrictions that will delay the verification of the foreign key until the transaction is completed:

 CREATE TABLE ticket_type (id INT PRIMARY KEY); CREATE TABLE myplugin_ticket_type_extension ( id INT PRIMARY KEY, ticket_type_id INT UNIQUE NOT NULL FOREIGN KEY REFERENCES ticket_type (id) DEFERRABLE INITIALLY DEFERRED ); ALTER TABLE ticket_type ADD FOREIGN KEY (id) REFERENCES myplugin_ticket_type_extension (ticket_type_id) DEFERRABLE INITIALLY DEFERRED; BEGIN; INSERT INTO ticket_type VALUES (1); INSERT INTO myplugin_ticket_type_extension VALUES (1,1); COMMIT; 

An alternative approach worth considering is to use table inheritance :

 CREATE TABLE ticket_type (id INT PRIMARY KEY); CREATE TABLE myplugin_ticket_type_extension (extension_field INT) INHERITS (ticket_type); INSERT INTO myplugin_ticket_type_extension (id, extension_field) VALUES (1,1); 

Entries inserted in the extension table will be displayed when ticket_type requested, so your main module should not be affected. You can prevent inserts directly into the ticket_type table by adding a trigger that can either completely block the inserts (by creating an exception) or automatically redirect new entries to the extension table:

 CREATE FUNCTION ticket_type_trg() RETURNS TRIGGER AS $$ BEGIN INSERT INTO myplugin_ticket_type_extension (id) VALUES (new.id); RETURN NULL; END $$ LANGUAGE plpgsql; CREATE TRIGGER ticket_type_trg BEFORE INSERT ON ticket_type FOR EACH ROW EXECUTE PROCEDURE ticket_type_trg(); 

Answer 2

+1 for pending limit. There is one thing in this answer, and you can run SET CONSTRAINTS IMMEDIATE to force PostgreSQL to run checks, rather than wait for a commit. This is usually best for debugging, etc., if your application knows that there are limitations.

In this case, your queries are as follows:

 BEGIN; INSERT INTO ticket_type VALUES (1); INSERT INTO myplugin_ticket_type_extension VALUES (1,1); SET CONSTRAINTS ALL IMMEDIATE; -- Do other work here, knowing that the above foreign key is -- already enforced COMMIT; 

Please note that the above example gives you a problem if you have several pending restrictions. In such cases, you want to do this by name ( SET CONSTRAINTS name1, name2 IMMEDIATE )

Answer 3

My approach is myplugin_ticket_type_extension, which creates insert, update, and delete triggers in the ticket_type table to control this. Then you will have fine-grained control over these operations and can guarantee this.

Since this is a type table, and these tables are usually not very large, you can even perform a full table scan to ensure that all records have correspondents and populate the table accordingly.

Another approach would be to perform this full scan and update the table in the function called by the application at the right time.