Re: Allowing join removals for more join types

On Fri, Jun 6, 2014 at 11:44 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Noah Misch <noah(at)leadboat(dot)com> writes:
> > On Thu, Jun 05, 2014 at 02:12:33AM +0200, Andres Freund wrote:
> >> A bit more crazy, but how about trying trying to plan joins with a added
> >> one-time qual that checks the size of the deferred trigger queue? Then
> >> we wouldn't even need special case plans.
>
> > That, too, sounds promising to investigate.
>
> Not terribly. You can't actually do join removal in such a case, so it's
> not clear to me that there's much win to be had. The planner would be at
> a loss as to what cost to assign such a construct, either.
>
> Moreover, what happens if the trigger queue gets some entries after the
> query starts?
>
>
In the scripts below I've created a scenario (scenario 1) that the inner
query which I've put in a trigger function does see the the referenced
table before the RI triggers execute, so it gives 1 row in the SELECT j2_id
FROM j1 WHERE NOT EXISTS(SELECT 1 FROM j2 WHERE j2_id = j2.id) query. This
works and I agree it's a problem that needs looked at in the patch.

I'm also trying to create the situation that you describe where the RI
trigger queue gets added to during the query. I'm likely doing it wrong
somehow, but I can't see what I'm doing wrong.

Here's both scripts. I need help with scenario 2 to create the problem you
describe, I can't get my version to give me any stale non-cascaded records.

-- 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 = id) AND
update_j2((SELECT MIN(j2_id) FROM j1 ij1 WHERE ij1.j2_id <> j1.j2_id)) = 1;

Regards

David Rowley