![]() measurement_inheritance_y2006m02 DROP TABLE IF EXISTS dbo. measurement_inheritance_insert_trigger () DROP TABLE IF EXISTS dbo. measurement_inheritance DROP FUNCTION IF EXISTS dbo. Our application should have remained fully functional and blissfully unaware of the changes we have been making under the hood while we were making them.DROP TRIGGER IF EXISTS insert_measurement_trigger ON dbo. ![]() BEGIN ĪLTER SEQUENCE orig_table_id_seq OWNED BY part_table.id ĪLTER TABLE part_table RENAME TO orig_table ![]() In a separate transaction (because it can take some time and it’s not critical) we finally drop the old non-partitioned table. Once there are no more rows left in the original table, we can replace the view with the fully partitioned table. If speed is an issue a slightly more complex piece of SQL can be used which avoids calling the trigger. This program can be safely interrupted if necessary. If ($rows_done != 0) # it will be 0e0 which is 0 but true My $dbh = DBI->connect("dbi:Pg:dbname=tpart host=/tmp port=5711", Here is the sample program I used – it’s written in Perl but should be pretty easy for most readers to follow even if not Perl-savvy. What we need is a looping program that selects a small number of old table rows to move and updates them so that they are moved. We’re going to use that fact to move all the old rows in batches. Note that all inserts and updates are steered to the partitioned table even if the row being updated is from the old table. INSTEAD OF INSERT OR UPDATE OR DELETE on orig_table BEGIN ĪLTER TABLE orig_table RENAME TO old_orig_table Īutovacuum_enabled = false, tovacuum_enabled = false Since we won’t be adding new tuples to the old non-partitioned table any more, we disable autovacuum on it. Then we can move to the transitional setup in one quick transaction. ![]() UPDATE part_table SET id = NEW.id, data = NEW.data INSERT INTO part_table VALUES(NEW.id, NEW.data) ĭELETE FROM part_table WHERE id = OLD.id ĭELETE FROM old_orig_table WHERE id = OLD.id create or replace function part_v_trigger() But before that, we’ll need a trigger function to handle all the insert, update and delete operations for the view. We’re going to rename the original table and then create a view with that name which is a union of the rows in the new partitioned table and the old non-partitioned table. (like orig_table including defaults including indexes including constraints) In this case we’re going to use four ranges on the data field: create table part_table Now we’re going to set up the partitioning structure. It won’t necessarily work for every situation, particularly tables with very heavy write loads, but it could work for many.įirst let’s set up our sample table and populate it with some data, 10 million rows in this case: create table orig_tableĬreate index orig_data_index on orig_table(data) Ĭreate index orig_id_index on orig_table(id) Here is a recipe for dealing with the problem. ![]() But you can’t take the table offline to create a new partitioned version of the table, which would take a great deal of time since this is a huge table. It’s got so big that managing it without partitioning it is getting increasingly difficult. Let’s say you have an application that has a huge table and that needs to be available all the time. PostgreSQL 9 Cookbook – Chinese Edition.PostgreSQL Server Programming Cookbook – 2nd Edition.PostgreSQL 9 Administration Cookbook – 3rd Edition.PostgreSQL High Availability Cookbook – 2nd Edition. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |