Re: Automatically Updatable Foreign Key Views

[David Rowley <david.rowley@xxxxxxxxxxxxxxx>]

On 24 September 2015 at 13:32, Raymond Brinzer <ray.brinzer@xxxxxxxxx> wrote:

Any thoughts on this would be welcome.  This is something which I
would personally find exceptionally valuable; if there are problems
with the idea, I'd like to know.  As well, if my description isn't
clear enough I'd be happy to explain.

I think the problem with this is that you cannot be certain at any point in time that a query such as:

SELECT u.name, p.number FROM phone_numbers p JOIN users u ON p.user_id = u.id;

will never eliminate tuples that don't match the join condition, *even if there is a foreign key defined on the join condition*.

The reason for this is that referenced tables are not updated immediately, they're only updated by triggers at the end of the statement, or transaction, depending if the foreign key is DEFERRED or not.

The problem is that an UPDATE/DELETE could take place which causes the foreign key to be violated and you may try and perform an UPDATE to the view before the foreign key is cascaded by the trigger at end of statement/transaction. Remember that a statement could execute inside of a volatile function being called by some outer query.

If foreign keys were updated immediately, like indexes normally are, then this wouldn't be an issue.

I've attached a file with 2 examples of when this can happen.

Regards

David Rowley

 

--
 David Rowley                   http://www.2ndQuadrant.com/

 PostgreSQL Development, 24x7 Support, Training & Services

 

-- Scenario 1: Outer command causes a foreign key trigger to be queued 
--             and this results in a window of time where we have records
--             in the referencing table which don't yet exist in the
--             referenced table.

DROP TABLE IF EXISTS j1;
DROP TABLE IF EXISTS j2;
DROP TABLE IF EXISTS records_violating_fkey;

CREATE TABLE j2 (id INT NOT NULL PRIMARY KEY);
CREATE TABLE j1 (
  id INT PRIMARY KEY,
  j2_id INT NOT NULL REFERENCES j2 (id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO j2 VALUES(10),(20);
INSERT INTO j1 VALUES(1,10),(2,20);

-- create a table to store records that 'violate' the fkey.
CREATE TABLE records_violating_fkey (j2_id INT NOT NULL);

CREATE OR REPLACE FUNCTION j1_update() RETURNS TRIGGER AS $$
BEGIN
  RAISE notice 'Trigger fired';
  INSERT INTO records_violating_fkey SELECT j2_id FROM j1 WHERE NOT EXISTS(SELECT 1 FROM j2 WHERE j2_id = j2.id);
  RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER j1_update_trigger BEFORE UPDATE ON j2 FOR EACH ROW EXECUTE PROCEDURE j1_update();

UPDATE j2 SET id = id+1;

-- returns 1 row.
SELECT * FROM records_violating_fkey;


------------------------------------------------------------------------------
-- Scenario 2: Inner command causes a foreign key trigger to be queued.

DROP TABLE IF EXISTS j1;
DROP TABLE IF EXISTS j2;

CREATE TABLE j2 (id INT NOT NULL PRIMARY KEY);

CREATE TABLE j1 (
  id INT PRIMARY KEY,
  j2_id INT NOT NULL REFERENCES j2 (id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO j2 VALUES(10),(20);
INSERT INTO j1 VALUES(1,10),(2,20);

CREATE OR REPLACE FUNCTION update_j2(p_id int) RETURNS int AS $$
BEGIN
  RAISE NOTICE 'Updating j2 id = % to %', p_id, p_id + 1;
  UPDATE j2 SET id = id + 1 WHERE id = p_id;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

-- try and get some records to be returned by causing an update on the record that is not the current record.
SELECT * FROM j1 WHERE NOT EXISTS(SELECT 1 FROM j2 WHERE j2_id = j2.id) AND update_j2((SELECT MIN(j2_id) FROM j1 ij1 WHERE ij1.j2_id <> j1.j2_id)) = 1;



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general