Re: Removing INNER JOINs

On 11 December 2017 at 17:33, Jim Finnerty <jfinnert@amazon.com> wrote:
> FK constraint enforcement may be deferred, but only to the end of the DML
> transaction.  Can someone provide an example where a SELECT statement that
> is not in that DML transaction and that can view only committed data can see
> a FK-side row that doesn't join with any PK-side row?
>
> I don't think I understand why the volatile function scenario.  Can a
> volatile function somehow circumvent FK enforcement?  If not, then why is
> the volatile function scenario different than any other DML with respect to
> the enforcement of FK constraints?

It would have to be in the same transaction as the DML to get wrong
results, other transactions won't see the changes until the DML
transaction commits.

Here's an example of finding unmatched rows when a join is removed in
the same transaction as the DML.

create table t1 (id int primary key);
create table t2 (value int not null);

alter table t2 add constraint t2_t1_id_fkey foreign key (value)
references t1 match full on update cascasde on delete cascade;

create index on t2 (value);

create or replace function update_t1(p_id int, p_newid int) returns bigint as $$
declare c bigint;
begin
update t1 set id = p_newid where id = p_id;
-- count would always be 0 if foreign key was cascaded immediately
select count(*) into c from t2 where not exists(select 1 from t1 where
t1.id = t2.value);
return c;
end;
$$ language plpgsql volatile;

insert into t1 values(1),(2);
insert into t2 values(1),(2);

select *,update_t1(id, id + 2) from t1 order by id;


If we removed the anti-join in the count(*) into c query, we'd incorrectly get:

# select *,update_t1(id, id + 2) from t1 order by id;
 id | update_t1
----+-----------
  1 |         0
  2 |         0
(2 rows)

when we should get:

# select *,update_t1(id, id + 2) from t1 order by id;
 id | update_t1
----+-----------
  1 |         1
  2 |         2
(2 rows)

Yes, this is an ANTI-JOIN and not an INNER JOIN as per $subject, but
the same applies. This is just a more simple example to give.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services