Postgres Pro
Downloads
Home   >   mailing lists

Re: BUG #15737: Unexpectedly Deleting full table when referring CTE(With Clause ) data,in a Subquery in another CTE - Mailing list pgsql-bugs

From David G. Johnston
Subject Re: BUG #15737: Unexpectedly Deleting full table when referring CTE(With Clause ) data,in a Subquery in another CTE
Date
Msg-id CAKFQuwZ_Wm7Uj8VBGuT0Gd=_32PKkz6b9+LL4cL1Q2=xomsbKQ@mail.gmail.com
Whole thread Raw
In response to BUG #15737: Unexpectedly Deleting full table when referring CTE (With Clause ) data,in a Subquery in another CTE  (PG Bug reporting form <noreply@postgresql.org>)
Responses Re: BUG #15737: Unexpectedly Deleting full table when referring CTE(With Clause ) data,in a Subquery in another CTE  (David Rowley <david.rowley@2ndquadrant.com>)
List pgsql-bugs
Tree view
On Thu, Apr 4, 2019 at 1:08 PM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      15737
Logged by:          Chandan Ahuja
Email address:      chandanahuja7@gmail.com
PostgreSQL version: 11.1
Operating system:   RHEL
Description:       

 ....
 

-- I am referring contract_id column here from deletedata but it does not
exist
delete from core.contract where contract_id in ( select distinct contract_id
from deletedata) -- returning contract_id

No bugs; its just the hard (but common) way to really learn the difference between a correlated subquery and an independent one; and to test your deletion queries thoroughly before running them live.

Since the contract_id column has not been provided a specific table qualifier the planner is free to choose any contract_id column it can find.  Since core.contract has a contract_id column that one is chosen; and so you've created a correlated subquery that is the the equivalent of:

delete from core.contract where contract_id = contract_id;

Which you should agree deletes every row.

Writing the following would provoke the error you are expecting:

delete from core.contract where contract_id in (select distinct deletedata.contract_id from deletedata);

Then, since you implemented ON CASCADE DELETE (which is good, why are you bothering to perform a manual cascade per the above?), the deletion from core.contract cascades to core.contractcomponent.

David J.

pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #15737: Unexpectedly Deleting full table when referring CTE (With Clause ) data,in a Subquery in another CTE
Next
From: David Rowley
Date:
Subject: Re: BUG #15737: Unexpectedly Deleting full table when referring CTE(With Clause ) data,in a Subquery in another CTE