pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16500: SQL Abend. select multi_key_columns_range_partition_table
5+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2020-07-28 13838740f6 cebe10a5f3 62c4a77295 bead29decf Fix some issues with step generation in partition pruning.
* BUG #16500: SQL Abend. select multi_key_columns_range_partition_table
@ 2020-06-18 09:35 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2020-06-18 09:35 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: hisanori.kobayashi.bp@nttdata.com
The following bug has been logged on the website:
Bug reference: 16500
Logged by: Kobayashi Hisanori
Email address: hisanori.kobayashi.bp@nttdata.com
PostgreSQL version: 12.2
Operating system: Windows10
Description:
I found that abend(crash) sql.
select ... from multi_key_columns_range_partition_table
where key_col1 <= xxx and key_col2 = yyy ;
Same for 12.2 and 12.3
I am not good at English, so I will send a reproduction script.
------------------------------
-- ***** execute from psql
--
-- ***** Create Test Table
drop table if exists pt_test01 ;
create table pt_test01
( kbn smallint not null
, nen char(4) not null
, mm char(2) not null
, cd char(3) not null
, val00 numeric(15, 3)
, usr varchar(10)
)
partition by range(kbn, nen) with (oids=false)
;
create table pt_test01_1_2019 partition of pt_test01 for values from (1,
'2019') to (1, '2020') ;
create table pt_test01_1_2020 partition of pt_test01 for values from (1,
'2020') to (1, '2021') ;
create table pt_test01_2_2019 partition of pt_test01 for values from (2,
'2019') to (2, '2020') ;
create table pt_test01_2_2020 partition of pt_test01 for values from (2,
'2020') to (2, '2021') ;
--
-- ***** Test SQL
select * from pt_test01 where kbn <= 1 and nen = '2020' ;
--
-- ***** Result:ABEND/Connection Closed
-- ***** Message(Japanese)
-- サーバとの接続が想定外にクローズされました
-- おそらく要求の処理前または処理中にサーバが異常終了
-- したことを意味しています。
-- サーバへの接続が失われました。リセットしています: 失敗。
-- ***** Google translation
-- The connection with the server was closed unexpectedly
-- Server crashed, probably before or during processing of the
request
-- It means that you have done it.
-- The connection to the server has been lost. Resetting: failed.
------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16500: SQL Abend. select multi_key_columns_range_partition_table
@ 2020-06-18 13:05 Etsuro Fujita <etsuro.fujita@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: Etsuro Fujita @ 2020-06-18 13:05 UTC (permalink / raw)
To: hisanori.kobayashi.bp@nttdata.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Thu, Jun 18, 2020 at 6:49 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
> I will send a reproduction script.
> drop table if exists pt_test01 ;
> create table pt_test01
> ( kbn smallint not null
> , nen char(4) not null
> , mm char(2) not null
> , cd char(3) not null
> , val00 numeric(15, 3)
> , usr varchar(10)
> )
> partition by range(kbn, nen) with (oids=false)
> ;
> create table pt_test01_1_2019 partition of pt_test01 for values from (1,
> '2019') to (1, '2020') ;
> create table pt_test01_1_2020 partition of pt_test01 for values from (1,
> '2020') to (1, '2021') ;
> create table pt_test01_2_2019 partition of pt_test01 for values from (2,
> '2019') to (2, '2020') ;
> create table pt_test01_2_2020 partition of pt_test01 for values from (2,
> '2020') to (2, '2021') ;
> select * from pt_test01 where kbn <= 1 and nen = '2020' ;
This caused a server crash in my environment as well. Maybe I'm
missing something, but ISTM that it's odd to allow partitions with
empty range bounds. I'll dig into this (and another one Kobayashi-san
reported).
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16500: SQL Abend. select multi_key_columns_range_partition_table
@ 2020-06-18 16:44 Dmitry Dolgov <9erthalion6@gmail.com>
parent: Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dmitry Dolgov @ 2020-06-18 16:44 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: hisanori.kobayashi.bp@nttdata.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
> On Thu, Jun 18, 2020 at 10:05:12PM +0900, Etsuro Fujita wrote:
> On Thu, Jun 18, 2020 at 6:49 PM PG Bug reporting form
> <noreply@postgresql.org> wrote:
> > I will send a reproduction script.
>
> > drop table if exists pt_test01 ;
> > create table pt_test01
> > ( kbn smallint not null
> > , nen char(4) not null
> > , mm char(2) not null
> > , cd char(3) not null
> > , val00 numeric(15, 3)
> > , usr varchar(10)
> > )
> > partition by range(kbn, nen) with (oids=false)
> > ;
> > create table pt_test01_1_2019 partition of pt_test01 for values from (1,
> > '2019') to (1, '2020') ;
> > create table pt_test01_1_2020 partition of pt_test01 for values from (1,
> > '2020') to (1, '2021') ;
> > create table pt_test01_2_2019 partition of pt_test01 for values from (2,
> > '2019') to (2, '2020') ;
> > create table pt_test01_2_2020 partition of pt_test01 for values from (2,
> > '2020') to (2, '2021') ;
>
> > select * from pt_test01 where kbn <= 1 and nen = '2020' ;
>
> This caused a server crash in my environment as well. Maybe I'm
> missing something, but ISTM that it's odd to allow partitions with
> empty range bounds. I'll dig into this (and another one Kobayashi-san
> reported).
Yep, I also can reproduce it easily. Can't say I'm familiar with this
code, but after looking through it a bit it seems something strange is
going on with a pruning step generated using prefix. Looks like it has
only expressions for the second attribute of the partition key, but
tries to compare with the full boundinfo. Without this pruning step the
query above doesn't crash for me.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-06-18 16:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-18 09:35 BUG #16500: SQL Abend. select multi_key_columns_range_partition_table PG Bug reporting form <noreply@postgresql.org>
2020-06-18 13:05 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2020-06-18 16:44 ` Dmitry Dolgov <9erthalion6@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