pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16249: Partition pruning blocks on exclusively locked table partition
3+ messages / 3 participants
[nested] [flat]

* BUG #16249: Partition pruning blocks on exclusively locked table partition
@ 2020-02-07 19:10 PG Bug reporting form <noreply@postgresql.org>
  2020-02-07 19:17 ` Re: BUG #16249: Partition pruning blocks on exclusively locked table partition Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: PG Bug reporting form @ 2020-02-07 19:10 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: boekewurm+postgres@gmail.com

The following bug has been logged on the website:

Bug reference:      16249
Logged by:          Matthias vd Meent
Email address:      boekewurm+postgres@gmail.com
PostgreSQL version: 11.5
Operating system:   Debian Stretch, RDS
Description:        

CREATE TABLE test (num bigint UNIQUE PRIMARY KEY) PARTITION BY RANGE
(num);
CREATE TABLE test_100 PARTITION OF test FOR VALUES FROM (0) TO (100);
CREATE TABLE test_200 PARTITION OF test FOR VALUES FROM (100) TO (200);

INSERT INTO test SELECT * FROM generate_series(0, 199, 1)

-- sql console 1

BEGIN;
REINDEX TABLE test_100;

-- sql console 2

BEGIN;
/* the following queries return immediately */
SELECT * FROM test_200 WHERE num between 101 AND 105;
\d+ test
LOCK TABLE test IN ACCESS SHARE MODE;

/* this does not return a result immediately: it blocks in the planning */
SELECT * FROM test WHERE num BETWEEN 101 AND 105;
/*
  When we finally commit / rollback in window 1, we get the following
result:

                                                  QUERY PLAN                
                                  
---------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..2.52 rows=5 width=8) (actual time=0.007..0.014 rows=5
loops=1)
   ->  Seq Scan on public.test_200  (cost=0.00..2.50 rows=5 width=8) (actual
time=0.007..0.013 rows=5 loops=1)
         Output: test_200.num
         Filter: ((test_200.num >= 101) AND (test_200.num <= 105))
         Rows Removed by Filter: 95
 Planning Time: 80872.004 ms
 Execution Time: 0.026 ms
(7 rows)
*/

In my humble opinion, this query should not be blocked during planning, as
the partition can be pruned directly from the plan with the partition
information that is available from the parent table: Partition information
can be updated  when the partition is locked, but through the parent
partitioned table. 

Examples have been tested on both Debian and Amazon RDS.

With regards,

Matthias



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #16249: Partition pruning blocks on exclusively locked table partition
  2020-02-07 19:10 BUG #16249: Partition pruning blocks on exclusively locked table partition PG Bug reporting form <noreply@postgresql.org>
@ 2020-02-07 19:17 ` Tom Lane <tgl@sss.pgh.pa.us>
  2020-02-07 20:26   ` Re: BUG #16249: Partition pruning blocks on exclusively locked table partition 0010203112132233 <boekewurm@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2020-02-07 19:17 UTC (permalink / raw)
  To: boekewurm+postgres@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org

PG Bug reporting form <noreply@postgresql.org> writes:
> In my humble opinion, this query should not be blocked during planning, as
> the partition can be pruned directly from the plan with the partition
> information that is available from the parent table: Partition information
> can be updated  when the partition is locked, but through the parent
> partitioned table. 

This case has been improved in PG v12.  It is operating as designed in
v11, however.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #16249: Partition pruning blocks on exclusively locked table partition
  2020-02-07 19:10 BUG #16249: Partition pruning blocks on exclusively locked table partition PG Bug reporting form <noreply@postgresql.org>
  2020-02-07 19:17 ` Re: BUG #16249: Partition pruning blocks on exclusively locked table partition Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-02-07 20:26   ` 0010203112132233 <boekewurm@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: 0010203112132233 @ 2020-02-07 20:26 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org

On Fri, 7 Feb 2020 at 20:17, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > In my humble opinion, this query should not be blocked during planning, as
> > the partition can be pruned directly from the plan with the partition
> > information that is available from the parent table: Partition information
> > can be updated  when the partition is locked, but through the parent
> > partitioned table.
>
> This case has been improved in PG v12.  It is operating as designed in
> v11, however.
>
>                         regards, tom lane

Ah, I indeed hadn't tested pg12 yet, but you are indeed correct. I
guess I'll then have to wait for pg12 to become generally available on
RDS.

Anyway, thanks for the help, and sorry for taking your time.

regards,

Matthias





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2020-02-07 20:26 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-07 19:10 BUG #16249: Partition pruning blocks on exclusively locked table partition PG Bug reporting form <noreply@postgresql.org>
2020-02-07 19:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-02-07 20:26   ` 0010203112132233 <boekewurm@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox