Skip to content

error when using DELETE USING #111

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
jamatthews opened this issue Aug 8, 2017 · 3 comments
Closed

error when using DELETE USING #111

jamatthews opened this issue Aug 8, 2017 · 3 comments
Assignees
@ildus @funbringer
Labels
bug
Milestone
Release 1.4.3

Comments

@jamatthews
Copy link

After upgrading from 1.3.2 to 1.4.2 DELETE USING no longer works and throws an error:

BEGIN;
DELETE FROM table_1
USING table_2
WHERE table_1.partiton_column = 1 AND table_2.partition_column = 1 AND table_1.id = table_2.id;
ERROR: variable not found in subplan target lists
@funbringer funbringer added the bug label Aug 8, 2017
funbringer added a commit that referenced this issue Aug 8, 2017
@funbringer
fix problems with DELETE USING mentioned in issue #111
c1d59bd
@funbringer funbringer self-assigned this Aug 8, 2017
@funbringer
Copy link
Collaborator

We've created a hotfix branch called master_hotfix_delete_using, could you try it?

@funbringer funbringer added this to the Release 1.4.3 milestone Aug 14, 2017
@funbringer
Copy link
Collaborator

funbringer commented Aug 15, 2017 •
edited
Loading

I've just tested the following case (master branch, PostgreSQL 9.6.4):

create table test (id int not null, val float8);
select create_range_partitions('test', 'id', 1, 10, 10);

create table ids (id int);


explain (verbose) delete from test using ids where test.id = ids.id;                 
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Delete on public.test  (cost=0.01..1303.34 rows=20401 width=12)
   Delete on public.test
   Delete on public.test_1
   Delete on public.test_2
   Delete on public.test_3
   Delete on public.test_4
   Delete on public.test_5
   Delete on public.test_6
   Delete on public.test_7
   Delete on public.test_8
   Delete on public.test_9
   Delete on public.test_10
   ->  Hash Join  (cost=0.01..45.09 rows=1 width=12)
         Output: test.ctid, ids.ctid
         Hash Cond: (ids.id = test.id)
         ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
         ->  Hash  (cost=0.00..0.00 rows=1 width=10)
               Output: test.ctid, test.id
               ->  Seq Scan on public.test  (cost=0.00..0.00 rows=1 width=10)
                     Output: test.ctid, test.id
   ->  Hash Join  (cost=67.38..125.83 rows=2040 width=12)
         Output: test_1.ctid, ids.ctid
         Hash Cond: (test_1.id = ids.id)
         ->  Seq Scan on public.test_1  (cost=0.00..30.40 rows=2040 width=10)
               Output: test_1.ctid, test_1.id
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
   ->  Hash Join  (cost=67.38..125.83 rows=2040 width=12)
         Output: test_2.ctid, ids.ctid
         Hash Cond: (test_2.id = ids.id)
         ->  Seq Scan on public.test_2  (cost=0.00..30.40 rows=2040 width=10)
               Output: test_2.ctid, test_2.id
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
   ->  Hash Join  (cost=67.38..125.83 rows=2040 width=12)
         Output: test_3.ctid, ids.ctid
         Hash Cond: (test_3.id = ids.id)
         ->  Seq Scan on public.test_3  (cost=0.00..30.40 rows=2040 width=10)
               Output: test_3.ctid, test_3.id
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
   ->  Hash Join  (cost=67.38..125.83 rows=2040 width=12)
         Output: test_4.ctid, ids.ctid
         Hash Cond: (test_4.id = ids.id)
         ->  Seq Scan on public.test_4  (cost=0.00..30.40 rows=2040 width=10)
               Output: test_4.ctid, test_4.id
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
   ->  Hash Join  (cost=67.38..125.83 rows=2040 width=12)
         Output: test_5.ctid, ids.ctid
         Hash Cond: (test_5.id = ids.id)
         ->  Seq Scan on public.test_5  (cost=0.00..30.40 rows=2040 width=10)
               Output: test_5.ctid, test_5.id
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
   ->  Hash Join  (cost=67.38..125.83 rows=2040 width=12)
         Output: test_6.ctid, ids.ctid
         Hash Cond: (test_6.id = ids.id)
         ->  Seq Scan on public.test_6  (cost=0.00..30.40 rows=2040 width=10)
               Output: test_6.ctid, test_6.id
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
   ->  Hash Join  (cost=67.38..125.83 rows=2040 width=12)
         Output: test_7.ctid, ids.ctid
         Hash Cond: (test_7.id = ids.id)
         ->  Seq Scan on public.test_7  (cost=0.00..30.40 rows=2040 width=10)
               Output: test_7.ctid, test_7.id
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
   ->  Hash Join  (cost=67.38..125.83 rows=2040 width=12)
         Output: test_8.ctid, ids.ctid
         Hash Cond: (test_8.id = ids.id)
         ->  Seq Scan on public.test_8  (cost=0.00..30.40 rows=2040 width=10)
               Output: test_8.ctid, test_8.id
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
   ->  Hash Join  (cost=67.38..125.83 rows=2040 width=12)
         Output: test_9.ctid, ids.ctid
         Hash Cond: (test_9.id = ids.id)
         ->  Seq Scan on public.test_9  (cost=0.00..30.40 rows=2040 width=10)
               Output: test_9.ctid, test_9.id
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
   ->  Hash Join  (cost=67.38..125.83 rows=2040 width=12)
         Output: test_10.ctid, ids.ctid
         Hash Cond: (test_10.id = ids.id)
         ->  Seq Scan on public.test_10  (cost=0.00..30.40 rows=2040 width=10)
               Output: test_10.ctid, test_10.id
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
(111 rows)


explain (verbose) delete from test using ids where test.id = ids.id and test.id < 10;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Delete on public.test_1  (cost=67.38..112.22 rows=680 width=12)
   ->  Hash Join  (cost=67.38..112.22 rows=680 width=12)
         Output: test_1.ctid, ids.ctid
         Hash Cond: (test_1.id = ids.id)
         ->  Seq Scan on public.test_1  (cost=0.00..35.50 rows=680 width=10)
               Output: test_1.ctid, test_1.id
               Filter: (test_1.id < 10)
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
(11 rows)


explain (verbose) delete from ids using test where test.id = ids.id;                        
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Delete on public.ids  (cost=67.38..651.88 rows=20400 width=16)
   ->  Hash Join  (cost=67.38..651.88 rows=20400 width=16)
         Output: ids.ctid, test_1.ctid, test_1.tableoid
         Hash Cond: (test_1.id = ids.id)
         ->  Append  (cost=0.00..304.00 rows=20400 width=14)
               ->  Seq Scan on public.test_1  (cost=0.00..30.40 rows=2040 width=14)
                     Output: test_1.ctid, test_1.id, test_1.tableoid
               ->  Seq Scan on public.test_2  (cost=0.00..30.40 rows=2040 width=14)
                     Output: test_2.ctid, test_2.id, test_2.tableoid
               ->  Seq Scan on public.test_3  (cost=0.00..30.40 rows=2040 width=14)
                     Output: test_3.ctid, test_3.id, test_3.tableoid
               ->  Seq Scan on public.test_4  (cost=0.00..30.40 rows=2040 width=14)
                     Output: test_4.ctid, test_4.id, test_4.tableoid
               ->  Seq Scan on public.test_5  (cost=0.00..30.40 rows=2040 width=14)
                     Output: test_5.ctid, test_5.id, test_5.tableoid
               ->  Seq Scan on public.test_6  (cost=0.00..30.40 rows=2040 width=14)
                     Output: test_6.ctid, test_6.id, test_6.tableoid
               ->  Seq Scan on public.test_7  (cost=0.00..30.40 rows=2040 width=14)
                     Output: test_7.ctid, test_7.id, test_7.tableoid
               ->  Seq Scan on public.test_8  (cost=0.00..30.40 rows=2040 width=14)
                     Output: test_8.ctid, test_8.id, test_8.tableoid
               ->  Seq Scan on public.test_9  (cost=0.00..30.40 rows=2040 width=14)
                     Output: test_9.ctid, test_9.id, test_9.tableoid
               ->  Seq Scan on public.test_10  (cost=0.00..30.40 rows=2040 width=14)
                     Output: test_10.ctid, test_10.id, test_10.tableoid
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
(29 rows)


explain (verbose) delete from ids using test where test.id = ids.id and test.id < 10;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Delete on public.ids  (cost=67.38..112.22 rows=680 width=16)
   ->  Hash Join  (cost=67.38..112.22 rows=680 width=16)
         Output: ids.ctid, test_1.ctid, test_1.tableoid
         Hash Cond: (test_1.id = ids.id)
         ->  Append  (cost=0.00..35.50 rows=680 width=14)
               ->  Seq Scan on public.test_1  (cost=0.00..35.50 rows=680 width=14)
                     Output: test_1.ctid, test_1.id, test_1.tableoid
                     Filter: (test_1.id < 10)
         ->  Hash  (cost=35.50..35.50 rows=2550 width=10)
               Output: ids.ctid, ids.id
               ->  Seq Scan on public.ids  (cost=0.00..35.50 rows=2550 width=10)
                     Output: ids.ctid, ids.id
(12 rows)

@funbringer
Copy link
Collaborator

We've just released 1.4.3.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@ildus ildus

@funbringer funbringer

Labels
bug
Projects
None yet
Milestone
Release 1.4.3
Development

No branches or pull requests
3 participants
@ildus @funbringer @jamatthews