Re: Convert Existing Table to a Partition Table in PG10
On Sun, Jul 1, 2018 at 12:15 AM, Clifford Snow wrote: . > I also leaned that my range partition value I used on a timestamp needed to > have fractional seconds. I used a range of 2017-01-01 00:00:00 to > 2017-23:59:59 which failed when I attempted to add a record that had a > timestamp of 2017-23:59:59. Adding a fractional second to the range solved > the problem. Your problem probably comes from using closed intervals. Timestamps are like real numbers, partitions on real numbers are best done using half closed interval. You can conver the real line using non overlapping half open intervals, but you cannot do it with open or closed ones ( non ov. ). Assuming you are yearly range partitions ( 2017-23:59:59 should be 2017-12-31 23:59:59 ), to use closed interval you have to rely on "real" second numbers being stored in the computer with a finite precision ( so you can, say, add up to the microseconds, and pray it does not change to picoseconds in a future release ). If you use half open ( 2017-01-01 00:00:00 <= ts < 2018.01.01 00:00:00 ) the problem is much easier. You can even drop the HMS ( 2017-01-01 <= ts < 2018-01-01 ) and it will work, even if the systems peeks a different HMS value for each year, as you use the same value for an interval start as for the previous end. And, if timestamp supported defaulting the M and D like it does with HMS ( which it does not ) you could even drop them. And I think postgres does not use leap seconds, but If it did '2016-12-31 23:59:60.9" does not need to be remembered in the half-open style. This is a general problem, not a postgres or timestamp related one. Anything which has decimals is generally better partitioned with half-open intervals. With integer-like things ( like dates, which are countable ) it does not matter that much, I use half-open for easier upgrading if I need to, but you can translate open-closed-half. Francisco Olarte.
Re: Convert Existing Table to a Partition Table in PG10
On 1 July 2018 at 10:15, Clifford Snow wrote:
> I also leaned that my range partition value I used on a timestamp needed to
> have fractional seconds. I used a range of 2017-01-01 00:00:00 to
> 2017-23:59:59 which failed when I attempted to add a record that had a
> timestamp of 2017-23:59:59. Adding a fractional second to the range solved
> the problem.
Please be aware that with RANGE partitions the upper bound is
non-inclusive. The lower bound is inclusive.
If you want a 2017 partition, then FOR VALUES FROM ('2017-01-01') TO
('2018-01-01') will allow all 2017 timestamps and only 2017
timestamps.
You've no need to consider precision of the type and how many 9's you
add to anything here.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Re: Convert Existing Table to a Partition Table in PG10
David,
Thanks for the suggestion. That really simplifies creating the RANGE.
For all, I'm pretty much a postgresql novice, but I've tried to document
what I've learned in the hopes that it can help someone else.
You can read my blog post at
https://osm_seattle.snowandsnow.us/articles/Partitioning-Postgresql/
Clifford
On Sun, Jul 1, 2018 at 2:23 PM David Rowley
wrote:
> On 1 July 2018 at 10:15, Clifford Snow wrote:
> > I also leaned that my range partition value I used on a timestamp needed
> to
> > have fractional seconds. I used a range of 2017-01-01 00:00:00 to
> > 2017-23:59:59 which failed when I attempted to add a record that had a
> > timestamp of 2017-23:59:59. Adding a fractional second to the range
> solved
> > the problem.
>
> Please be aware that with RANGE partitions the upper bound is
> non-inclusive. The lower bound is inclusive.
>
> If you want a 2017 partition, then FOR VALUES FROM ('2017-01-01') TO
> ('2018-01-01') will allow all 2017 timestamps and only 2017
> timestamps.
>
> You've no need to consider precision of the type and how many 9's you
> add to anything here.
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
--
@osm_seattle
osm_seattle.snowandsnow.us
OpenStreetMap: Maps with a human touch
Re: Convert Existing Table to a Partition Table in PG10
On 02/07/18 01:43, Clifford Snow wrote: > David, > Thanks for the suggestion. That really simplifies creating the RANGE. > > For all, I'm pretty much a postgresql novice, but I've tried to document > what I've learned in the hopes that it can help someone else. > > You can read my blog post > at https://osm_seattle.snowandsnow.us/articles/Partitioning-Postgresql/ Please consider adding your PostgreSQL-related posts to Planet. https://planet.postgresql.org/add.html -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Re: Convert Existing Table to a Partition Table in PG10
Vic, I'd be happy to add my blog to planet.postgresql.org but my of my articles are not on postgresql. I'm using github pages for my blog and I do have tags for each article. Is there someone to filter on those tags? Clifford On Sun, Jul 1, 2018 at 5:13 PM Vik Fearing wrote: > On 02/07/18 01:43, Clifford Snow wrote: > > David, > > Thanks for the suggestion. That really simplifies creating the RANGE. > > > > For all, I'm pretty much a postgresql novice, but I've tried to document > > what I've learned in the hopes that it can help someone else. > > > > You can read my blog post > > at https://osm_seattle.snowandsnow.us/articles/Partitioning-Postgresql/ > > Please consider adding your PostgreSQL-related posts to Planet. > https://planet.postgresql.org/add.html > -- > Vik Fearing +33 6 46 75 15 36 > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support > -- @osm_seattle osm_seattle.snowandsnow.us OpenStreetMap: Maps with a human touch
Re: Call for Papers - PGConf.ASIA 2018
Hi PostgreSQL lovers, The call for papers for PGConf.ASIA 2018 will be closed on 31st July, 2018 (Japan time). I am looking forward to receiving your great proposals! -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp > Hi, > > PGconf.ASIA 2018 will be held on December 10 to 12 in Tokyo and we are > now accepting proposals for talks. > > Join us and other users and developers from around the world at the > home of the oldest and largest user group; Japan! > > (See http://www.pgconf.asia/EN/2017/program/ for last year's > conference program). > > - About the conference: > Location: Akihabara convention hall (Tokyo) > http://www.akibahall.jp/data/outline_eng.html > Targeted number of attendees: 700 in total > http://www.pgconf.asia/EN/2018/ > > - About the call for papers: > > - To submit a paper, please include the following details and send to: > pgconf-asia-2018-submission(at)pgconf(dot)asia > > Title > Abstract > Description > Language spoken during the talk: Japanese or English > Language of the talk material: Japanese and/or English > > - Submission deadline is midnight, 31st July, 2018 (Japan time). > > - Submissions should be sent in English, Japanese, or both. > Japanese-only submissions will be translated into English for > discussion within the program committee. > > - Presentation materials will be released the day after the conference > and will be made available to the public. The copyright of the > material will be retained by the author. > > We ask that you share your materials under a Creative Commons > license: https://creativecommons.org/licenses/by-nc-nd/4.0/ > > If your presentation material cannot be shared, please let us know. > > - Talks may be recorded or photographed. In both cases, the content > may be made public under a Creative Commons license: > https://creativecommons.org/licenses/by-nc-nd/4.0/. The copyright > of the material is retained by the speaker. > > - Speakers will be informed of the result of the selection by the end > of August. Speakers will be requested to submit brief biographies and > photos to be published in the conference program. > > - The exact length of each session is not decided yet (Last year it > was 40 minutes and we expect no big change for this year). > > Suggested topic areas include but not limited to: > > - Large-scale PostgreSQL deployments. > - Migrations from other databases to PostgreSQL > - Operations and administration > - Performance and feature implementation > - Replication, clustering, HA, sharding. > - Tools and utilities for PostgreSQL > - Benchmarking and hardware, tuning. > - PostgreSQL community and hacking. > - Studies, surveys on PostgreSQL ecosystem > - Asian PostgreSQL community & user groups > - Data warehousing > - Location-aware and mapping software with PostGIS > - Research and teaching with PostgreSQL > - Case studies, including but not limited to: IoT/Cloud, Healthcare, > Education and Academy > > - If you have any questions regarding the event, feel free to contact > the organization committee at > pgconf-asia-2018-submission(at)pgconf(dot)asia(dot) > > See you in Tokyo :) > > PGConf.ASIA 2018 Steering Committee > http://www.pgconf.asia/EN/2018/ > [Please feel free to redistribute this CFP] >
