If Not True Then False

Create PostgreSQL Table Partitioning (Part 1) - Comment Page: 1

PostgreSQL supports partitioning via table inheritance. So the partitioning is made in such a way that every child table inherits single parent table. Parent table is empty and it exists just to describe the whole data set. PostgreSQL partitioning can be implemented in range partitioning or list partitioning. Range partitioning can be done for example by ID ranges (like 0-100 000, 100 001-200...
Categories:

8 Comments

Leave a Comment

Your email address will not be published. Required fields are marked *

Input your comment.
help

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Input your name.

Erick

Great, very clear article.

Question: will the INSERT trigger be enough, or do I also need to create similar UPDATE and DELETE triggers?

Thanks

reply Reply
JR

Hi Erick,

Glad to hear that you like this article.

You don’t need UPDATE and DELETE triggers, INSERT trigger is enough. You could test it easily, just create test tables, INSERT, UPDATE and DELETE rows. And you can of course use EXPLAIN to see what is happening. :)

reply Reply
didik prasetyo

for this query i have problem if im update with enter grade out of range will error
example im insert
INSERT INTO impressions_by_day(
advertiser_id, “day”, impressions)
VALUES (1234, ‘2009-01-02′, 756);

and im update

UPDATE impressions_by_day
SET advertiser_id=1234, day=’2008-01-02′, impressions=756
WHERE day=’2009-01-02’;

then error
ERROR: new row for relation “impressions_by_day_y2009m1ms2” violates check constraint “impressions_by_day_y2009m1ms2_day_check”
SQL state: 23514

except im upgrade with range day ( NEW.DAY >= DATE ‘2009-01-01’ AND NEW.DAY < DATE '2009-03-01' )

how to solution with this case

Thanks

reply Reply
dmhr

Hi , I tried partition scripts and could see the insert on master table goes to the right child table whereas the delete operation is not happening.Please look in to this issue

reply Reply
Nikolay

@didik prasetyo … a have xtly the same problem … did you find any solutions !?

reply Reply
JR

Hi Nikolay,

Could you post more info about your update/insert command and your trigger function(s)?

This simple example is mainly storing and querying data, like logging something. If you have just insert trigger, then update fails, when you try to update row outside of your table check range.

If we check didik example:

First insert:


INSERT INTO impressions_by_day(advertiser_id, “day”, impressions)
VALUES (1234, ‘2009-01-02′, 756);

Then update:


UPDATE impressions_by_day
SET advertiser_id=1234, day=’2008-01-02′, impressions=756
WHERE day=’2009-01-02′;

Now your update is out of range ( NEW.DAY >= DATE ‘2009-01-01′ AND NEW.DAY < DATE '2009-03-01' ). So just update command won't move that row to another table (from impressions_by_day_y2009m1ms2 table to impressions_by_day_y2008m1ms2 table). You have to create another trigger function to handle update situations, where updated rows are inserted right place and old rows are deleted from old table.

reply Reply