Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: Move a row from a partition to another

    Hello,

    I have a table that is partitionned on a column of type integer.
    So the table is partitionned depending on the value of this integer that has a fixed range.
    My problem is that sometimes, for a given row, I need to update this integer. So, what I want in this case is to be able to move this row to the partition corresponding to the new value of this integer.
    The standard UPDATE does not work.
    So, my question : is it possible ? And if yes, how can I do this ?

    Thank you in advance for your answer !

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by marcofil
    Hello,

    I have a table that is partitionned on a column of type integer.
    So the table is partitionned depending on the value of this integer that has a fixed range.
    My problem is that sometimes, for a given row, I need to update this integer. So, what I want in this case is to be able to move this row to the partition corresponding to the new value of this integer.
    The standard UPDATE does not work.
    So, my question : is it possible ? And if yes, how can I do this ?

    Thank you in advance for your answer !
    In PostgreSQL, you have created a master table, and created partition tables which inherit all the table properties of the master table. Thus allowing each partition table to inhabit a different partition. Now, you should have set up rules or triggers to route the inserts from the master table into the appropriate partition table, as well as distinct individual constraints on each of the partition tables. I believe you'll need to add an update trigger on the master table, which begins a transaction, copies the old record to a new record (but changes the index field so that the new record is placed in the right partition) and which then deletes the original partition record and closes the transaction.
    Last edited by loquin; 08-22-06 at 17:55.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Aug 2006
    Posts
    3
    Hi,

    Thank you for your reply.
    I have done what you have suggested. So now the update is ok, but I have another problem now :-)
    I will take an example :
    Imagine I have table called 'MAIN' with two column : 'id_main', and 'town'.
    This table is partitionned based on the different values of the column 'town'.

    I have another table called 'PEOPLE' which has a foreign key on the 'id_main' of the MAIN table.

    Now, If I insert one record in table PEOPLE (VALUES (0, 'john')), it says that the id_main 0 does not exist in table MAIN. But in fact, it exists in one of the MAIN child table.

    So, what can I do to avoid that ?

    Thank you again !

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    That I don't know, marcofil.

    I hadn't tried settingup table partitions before; I checked the documentation, and it seemed as if it should work to set up the update trigger.

    It may make sense to use a surrogate key, rather than the partition ID, to use in defining the parent-child relationship.

    I would also suggest posting a question in the pg mailing list.


    Note - I just checked the mailing list. Per this post, foreign keys don't, as of yet (2/13/2006,) work with partitioned tables.

    So, you would need to build the equivalent of FK constraints into the child table triggers.

    Alao, this other responses had a potentially useful suggestion.

    And, here.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Aug 2006
    Posts
    3
    Thank you very much for your help.

    Best regards !

  6. #6
    Join Date
    Feb 2009
    Posts
    1

    update trigger code in Postgresql..

    Hi,
    Can u send me the code of update that u have ? I have 1 table having 3 distinct values and i partitioned it depend on those values.I have written code by using 'OLD' and 'NEW' like insert into 1st subpartition and then delete from corresponding subpartition.
    But whenever I fire the update it gives me the CHECK constraint error for subpartition and hence is not working.

    Appreciate if could provide your update trigger code.

    Thanks,
    Shrinivas.



    Quote Originally Posted by marcofil
    Hi,

    Thank you for your reply.
    I have done what you have suggested. So now the update is ok, but I have another problem now :-)
    I will take an example :
    Imagine I have table called 'MAIN' with two column : 'id_main', and 'town'.
    This table is partitionned based on the different values of the column 'town'.

    I have another table called 'PEOPLE' which has a foreign key on the 'id_main' of the MAIN table.

    Now, If I insert one record in table PEOPLE (VALUES (0, 'john')), it says that the id_main 0 does not exist in table MAIN. But in fact, it exists in one of the MAIN child table.

    So, what can I do to avoid that ?

    Thank you again !

Posting Permissions

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