Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2014
    Posts
    3

    Unanswered: Partitioning, Triggers and Check Constraints...

    Hi all!

    I'm new here and this is my first post. I hope to be able to contribute in the future but for now I'll have to start asking for help. I come from the Oracle world but being a consultant, I currently work in a projekt where Postgres is the DB of choice.

    We have a huge table, scoring_result, that will grow with hundrets of thousands of records a day. Then again, there's a flag in it (boolean datatype) which is called is_current_row and which is true only for one row per scored object. So, like this:
    Code:
    object_id | is_current_row | several columns of scoring information
    ----------------------------------------------------------------
         1    |        t       | <something>
         1    |        f       | <something else>
         1    |        f       | <you get it>
         2    |        t       | <...>
         2    |        f       | <...>
    In most cases, we only need the current row and since we have some performance problems, I checked for different options and think that partitioning is the way to go. Partition pruning (as it's called in Oracle) will make that from all these millions of records, only the 400.000 that have is_current_row = TRUE will be used. So I implemented the thing as the doc says and it looks promising. Until a new row comes in and the previous gets set to FALSE (and thus must be moved to the other partition).

    Here's the trigger I wrote together with the procedure doing the move:
    Code:
    CREATE OR REPLACE FUNCTION scoring_result_update_trg ()
    RETURNS TRIGGER AS $$
    BEGIN
        IF (NOT NEW.is_current_row) THEN
            INSERT INTO pdl.scoring_result_history VALUES (NEW.*);
            
            DELETE FROM pdl.scoring_result_current
            WHERE  scoring_result_id = NEW.scoring_result_id;
        ELSE
            INSERT INTO pdl.scoring_result_current VALUES (NEW.*);
            
            DELETE FROM pdl.scoring_result_history
            WHERE  scoring_result_id = NEW.scoring_result_id;
        END IF;
        
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
    
    CREATE TRIGGER trg_scoring_result_update
        BEFORE UPDATE ON pdl.scoring_result
        FOR EACH ROW
        WHEN (OLD.is_current_row IS DISTINCT FROM NEW.is_current_row)
        EXECUTE PROCEDURE scoring_result_update_trg ();
    Worth mentioning that the 2 tables that inherit table scoring_result (the "partitions") have a check constraint on column is_current_row:
    • "CHECK (is_current_row)" for the small partition with current data
    • "CHECK (NOT is_current_row)" for the huge history-partition


    When updating a row from is_current_row to NOT is_current_row, I get the following error:
    ERROR: new row for relation "scoring_result_current" violates check constraint "scoring_result_current_is_current_row_check"

    This comes unexpected since the documentation states "The trigger can be specified to fire before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE, or DELETE is attempted); or [...]". (source: http://www.postgresql.org/docs/9.3/s...tetrigger.html)
    Logging in the procedure (inserting into a table) showed me even that the procedure doesn't get invoked, so I guess the order is CHECK-constraint first, Trigger then.

    Does anyone here have further information or a good workaround? I could simply remove the CHECK-constraints I guess but that would be somewhat "quick and easy", wouldn't it?
    I hope no one gets offended by this long post, I'm simply trying to give all the information.

    Cheers,
    Daniel

  2. #2
    Join Date
    Oct 2014
    Posts
    3
    Hej!

    No one who can help me with this problem?
    I've been looking a bit more into this and it looks like the trigger is not run at all?!? I removed the check constraint from table scoring_result_current to see if it worked then. Now I can update a row from is_current to NOT is_current and it works fine but the row stays in table scoring_result_current. I created a table "test" that gets inserted from within the trigger-procedure as a help for debugging and it remains empty. So, something with my trigger must be wrong but I can't figure out what it may be.

  3. #3
    Join Date
    Oct 2014
    Posts
    3

    Exclamation Solution

    I found the solution!

    Just thought I should share it with you and not leave this thread open.
    The problem was that the trigger was on the parent table and not the 2 tables inheriting it. So, the following definition was wrong:
    Code:
    CREATE TRIGGER trg_scoring_result_update
        BEFORE UPDATE ON pdl.scoring_result
        FOR EACH ROW
        WHEN (OLD.is_current_row IS DISTINCT FROM NEW.is_current_row)
        EXECUTE PROCEDURE scoring_result_update_trg ();
    Instead, there should be 2 triggers, one on each child-table:
    Code:
    CREATE TRIGGER trg_scoring_result_current_update
        BEFORE UPDATE OF is_current_row ON pdl.scoring_result_current
        FOR EACH ROW
        WHEN (OLD.is_current_row IS DISTINCT FROM NEW.is_current_row)
        EXECUTE PROCEDURE scoring_result_current_update_trg ();
    
    -- and one on the other table:
    CREATE TRIGGER trg_scoring_result_history_update
        BEFORE UPDATE OF is_current_row ON pdl.scoring_result_history
        FOR EACH ROW
        WHEN (OLD.is_current_row IS DISTINCT FROM NEW.is_current_row)
        EXECUTE PROCEDURE scoring_result_history_update_trg ();
    Or - trying to paint a picture - something like this:
    Code:
               parent_table             <-- INSERT-trigger that distributes rows to the child tables
               |          |
    child_table_1       child_table_2   <-- 1 UPDATE-trigger each that moves rows when their key gets updated so that they belong to another partition
    Hope this helps someone sometime. Have a good one!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •