From: Tom Lane <tgl@sss.pgh.pa.us>
To: tianbing@highgo.com
Cc: pgsql-bugs@postgresql.org
Subject: Re: BUG #14666: Question on money type as the key of partitioned table
Date: Sun, 28 May 2017 17:53:29 -0400
Message-ID: <32324.1496008409@sss.pgh.pa.us> (raw)
In-Reply-To: <20170524024550.29935.14396@wrigleys.postgresql.org>
References: <20170524024550.29935.14396@wrigleys.postgresql.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-bugs>
tianbing@highgo.com writes:
> When I use the money type as the key to create the partition table as
> follows:
> postgres=# create table test(m money) partition by list(m);
> CREATE TABLE
> postgres=# create table test_1 partition of test for values in (10);
> CREATE TABLE
> Partition bounds without apostrophe can be createed, but it store the null
> value, not '10' value.
That's not actually what it's doing. A look into pg_class shows that
while, for an integer partitioning column, you'd get something like this
for relpartbound:
test1p | {PARTITIONBOUND :strategy l :listdatums (
{CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true
:constisnull false :location 54 :constvalue 4 [ 10 0 0 0 0 0 0 0 ]}) :lowerdatu
ms <> :upperdatums <>}
in the case at hand you'd get
test2p | {PARTITIONBOUND :strategy l :listdatums (
{FUNCEXPR :funcid 3811 :funcresulttype 790 :funcretset false :funcvariadic false
:funcformat 2 :funccollid 0 :inputcollid 0 :args ({CONST :consttype 23 :constty
pmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location
54 :constvalue 4 [ 10 0 0 0 0 0 0 0 ]}) :location -1}) :lowerdatums <> :upperda
tums <>}
that is, what we have is a run-time coercion of integer to money.
The partitioning code utterly fails to consider that what it might
get from the partition list syntax is not a constant --- but since
casts are not required to be immutable, it might not.
This is exacerbated by the fact that subsequent code naively assumes
that the elements of PartitionBoundSpec.listdatums are Consts, without
any checking. It's a wonder you don't get runtime crashes. (You might
if the partition column type is pass-by-ref, I suspect.) And I'm
unimpressed by the fact that this assumption is nowhere documented, too.
What we need to do here (at least in the short term) is throw an error
if we don't get a simple Const out of const-simplification. I'm not
sure if we need a separate error message for that case, or if we can
get away with just re-using the existing text about "specified value
cannot be cast to type ...". The point here would be that the cast
exists but is not immutable. Maybe use the same primary message
but explain that in an errdetail?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, tianbing@highgo.com
Subject: Re: BUG #14666: Question on money type as the key of partitioned table
In-Reply-To: <32324.1496008409@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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