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
In-reply-to: <20170524024550.29935.14396@wrigleys.postgresql.org>
References: <20170524024550.29935.14396@wrigleys.postgresql.org>
Comments: In-reply-to tianbing@highgo.com
	message dated "Wed, 24 May 2017 02:45:50 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <32323.1496008409.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 28 May 2017 17:53:29 -0400
Message-ID: <32324.1496008409@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-bugs-owner@postgresql.org

tianbing@highgo.com writes:
> When I use the money type as the key to create the partition table as
> follows:

> postgres=3D# create table test(m money) partition by list(m);
> CREATE TABLE
> postgres=3D# 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 :listdat=
ums (
{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 ]}) :lowe=
rdatu
ms <> :upperdatums <>}

in the case at hand you'd get

 test2p                              | {PARTITIONBOUND :strategy l :listdat=
ums (
{FUNCEXPR :funcid 3811 :funcresulttype 790 :funcretset false :funcvariadic =
false
 :funcformat 2 :funccollid 0 :inputcollid 0 :args ({CONST :consttype 23 :co=
nstty
pmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :loc=
ation
 54 :constvalue 4 [ 10 0 0 0 0 0 0 0 ]}) :location -1}) :lowerdatums <> :up=
perda
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


--=20
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs