From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron <ronljohnsonjr@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning by month causing an error?
Date: Fri, 20 Dec 2019 23:56:13 -0500
Message-ID: <27088.1576904173@sss.pgh.pa.us> (raw)
In-Reply-To: <9fe4b503-4be4-4ed5-4299-d7c49c8921b4@gmail.com>
References: <CAOC+FBW=zSLyHWg2XDTp2eeEAmwzkzZLQPBqFx7_Cso=WMK3ew@mail.gmail.com>
<9226.1576897186@sss.pgh.pa.us>
<8ba56626-3b91-6ac8-34a9-ab2c9ca2b8c6@gmail.com>
<19102.1576901426@sss.pgh.pa.us>
<9fe4b503-4be4-4ed5-4299-d7c49c8921b4@gmail.com>
Ron <ronljohnsonjr@gmail.com> writes:
> On 12/20/19 10:10 PM, Tom Lane wrote:
>> This rule breaks fewer things than a rule of A <= X <= B would.
> Why not? For 20+ years (not on Postgres) I've been specifying partition
> limits where the upper limits are the edge of timestamps (octaword integers
> counting the number of 100ns ticks since November 17, 1858), and have
> *never* had a problem:
> IN ETC_TRAN_DETAIL_201909_UA WITH LIMIT OF '2019-09-30 23:59:59.99',
> IN ETC_TRAN_DETAIL_201910_UA WITH LIMIT OF '2019-10-31 23:59:59.99',
And what happens with '2019-10-31 23:59:59.9999'? I don't know about
your other database, but in Postgres that value is legal and it's larger
than '2019-10-31 23:59:59.99', but still less than '2019-11-01 00:00:00'.
The main point here is that even if that works, it requires a whole
lot more assumptions about the behavior of the datatype (specifically,
its precision limit) than the other way. As soon as you look at cases
where the precision limit isn't clearly defined, it just breaks.
> Of course, I've also never partitioned a table on a float, thinking that's
> utterly daft.
[ shrug... ] Works fine as long as you have a non-daft partitioning rule.
regards, tom lane
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning by month causing an error?
In-Reply-To: <27088.1576904173@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