Re: Allowing join removals for more join types - Mailing list pgsql-hackers
| From | David Rowley |
|---|---|
| Subject | Re: Allowing join removals for more join types |
| Date | |
| Msg-id | CAApHDvpLOWusUPObco-K3KK8ZWxk8+XYpYmGELSNY7f15-sf6g@mail.gmail.com Whole thread Raw |
| In response to | Re: Allowing join removals for more join types (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-hackers |
On Fri, Jun 6, 2014 at 11:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Noah Misch <noah@leadboat.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 addedNot terribly. You can't actually do join removal in such a case, so it's
>> 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 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
pgsql-hackers by date: