Re: Partitioning by month causing an error?

Ron <[email protected]> Fri, 20 Dec 2019 22:31:35 -0600
Newsgroups	gmane.comp.db.postgresql.admin
Message-ID	<[email protected]>
This is a multi-part message in MIME format.
--------------B25D12BABC7E71286F45551B
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 12/20/19 10:10 PM, Tom Lane wrote:
> Ron <[email protected]> writes:
>> On 12/20/19 8:59 PM, Tom Lane wrote:
>>> The rule is that a range partition from A to B covers values A <= X < B.
>> If I were to ask to count *from* 1 *to* 100, you'd count from 1 to 100,
>> *not* 1 to 99.  Who decided to arbitrarily break a grammatical rule we
>> (native English speakers, and I bet everyone else, too) all learn as
>> children, causing all sorts of needless confusion and breakage?
> This rule breaks fewer things than a rule of A <= X <= B would.  In
> particular, for more-or-less-continuous values like float8s or timestamps,
> you really really don't want to have to write down
> the-last-possible-value-before-the-start-of-the-next-partition.

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',
IN ETC_TRAN_DETAIL_201911_UA WITH LIMIT OF '2019-11-30 23:59:59.99',
IN ETC_TRAN_DETAIL_201912_UA WITH LIMIT OF '2019-12-31 23:59:59.99',
IN ETC_TRAN_DETAIL_202001_UA WITH LIMIT OF '2020-01-31 23:59:59.99',
IN ETC_TRAN_DETAIL_202002_UA WITH LIMIT OF '2020-02-28 23:59:59.99',
etc, etc, etc

Of course, I've also never partitioned a table on a float, thinking that's 
utterly daft.


-- 
Angular momentum makes the world go 'round.

--------------B25D12BABC7E71286F45551B
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:10 PM, Tom Lane wrote:<br>
    <blockquote type="cite" cite="mid:[email protected]">
      <pre class="moz-quote-pre" wrap="">Ron <a class="moz-txt-link-rfc2396E" href="mailto:[email protected]">&lt;[email protected]&gt;</a> writes:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">On 12/20/19 8:59 PM, Tom Lane wrote:
</pre>
        <blockquote type="cite">
          <pre class="moz-quote-pre" wrap="">The rule is that a range partition from A to B covers values A &lt;= X &lt; B.
</pre>
        </blockquote>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">If I were to ask to count *from* 1 *to* 100, you'd count from 1 to 100, 
*not* 1 to 99.  Who decided to arbitrarily break a grammatical rule we 
(native English speakers, and I bet everyone else, too) all learn as 
children, causing all sorts of needless confusion and breakage?
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
This rule breaks fewer things than a rule of A &lt;= X &lt;= B would.  In
particular, for more-or-less-continuous values like float8s or timestamps,
you really really don't want to have to write down
the-last-possible-value-before-the-start-of-the-next-partition.</pre>
    </blockquote>
    <br>
    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 <b>never</b> had a problem:<br>
    IN ETC_TRAN_DETAIL_201909_UA WITH LIMIT OF '2019-09-30 23:59:59.99',<br>
    IN ETC_TRAN_DETAIL_201910_UA WITH LIMIT OF '2019-10-31 23:59:59.99',<br>
    IN ETC_TRAN_DETAIL_201911_UA WITH LIMIT OF '2019-11-30 23:59:59.99',<br>
    IN ETC_TRAN_DETAIL_201912_UA WITH LIMIT OF '2019-12-31 23:59:59.99',<br>
    IN ETC_TRAN_DETAIL_202001_UA WITH LIMIT OF '2020-01-31 23:59:59.99',<br>
    IN ETC_TRAN_DETAIL_202002_UA WITH LIMIT OF '2020-02-28 23:59:59.99',<br>
    etc, etc, etc<br>
    <br>
    Of course, I've also never partitioned a table on a float, thinking
    that's utterly daft.<br>
    <br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------B25D12BABC7E71286F45551B--