From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning by month causing an error?
Date: Sat, 21 Dec 2019 00:43:19 -0600
Message-ID: <bfa54aee-b2b5-5bc0-60c1-c0fe33c939c7@gmail.com> (raw)
In-Reply-To: <27088.1576904173@sss.pgh.pa.us>
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>
<27088.1576904173@sss.pgh.pa.us>
On 12/20/19 10:56 PM, Tom Lane wrote:
> 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'?
The datatype only extends to centiseconds. Naturally, if it extended to
tenths of milliseconds, then I'd have added another two nines to the range
limits.
> 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.
It works *perfectly* on *discreet* datatypes. Honestly... experience has
demonstrated it.
Not so much on floats, but I think that's daft anyway.
>> 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
--
Angular momentum makes the world go 'round.
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: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning by month causing an error?
In-Reply-To: <bfa54aee-b2b5-5bc0-60c1-c0fe33c939c7@gmail.com>
* 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