Subject: Re: Partitioning by month causing an error?
To: pgsql-admin@lists.postgresql.org
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>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <bfa54aee-b2b5-5bc0-60c1-c0fe33c939c7@gmail.com>
Date: Sat, 21 Dec 2019 00:43:19 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.9.0
MIME-Version: 1.0
In-Reply-To: <27088.1576904173@sss.pgh.pa.us>
Content-Type: multipart/alternative;
 boundary="------------1C8B02B4771138C3D9F8F06B"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------1C8B02B4771138C3D9F8F06B
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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.

--------------1C8B02B4771138C3D9F8F06B
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    On 12/20/19 10:56 PM, Tom Lane wrote:<br>
    <blockquote type="cite" cite="mid:27088.1576904173@sss.pgh.pa.us">
      <pre class="moz-quote-pre" wrap="">Ron <a class="moz-txt-link-rfc2396E" href="mailto:ronljohnsonjr@gmail.com">&lt;ronljohnsonjr@gmail.com&gt;</a> writes:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">On 12/20/19 10:10 PM, Tom Lane wrote:
</pre>
        <blockquote type="cite">
          <pre class="moz-quote-pre" wrap="">This rule breaks fewer things than a rule of A &lt;= X &lt;= B would.
</pre>
        </blockquote>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">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',
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
And what happens with '2019-10-31 23:59:59.9999'? </pre>
    </blockquote>
    <br>
    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.<br>
    <br>
    <blockquote type="cite" cite="mid:27088.1576904173@sss.pgh.pa.us">
      <pre class="moz-quote-pre" wrap=""> 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.</pre>
    </blockquote>
    <br>
    It works <b>perfectly</b> on <b>discreet</b> datatypes. 
    Honestly... experience has demonstrated it.<br>
    <br>
    Not so much on floats, but I think that's daft anyway.<br>
    <br>
    <blockquote type="cite" cite="mid:27088.1576904173@sss.pgh.pa.us">
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">Of course, I've also never partitioned a table on a float, thinking that's 
utterly daft.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
[ shrug... ]  Works fine as long as you have a non-daft partitioning rule.

			regards, tom lane
</pre>
    </blockquote>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------1C8B02B4771138C3D9F8F06B--