Task Detail
View Subprojects | To Do | Gantt Chart | Import/Export CSV
Submitted by:
Cristiano Duarte (cunha17) |
Permalink:
http://pgfoundry.org/pm/t_follow.php/1000105 |
Category
None |
Task Detail Information (JSON):
application/json
or
text/plain
|
Percent Complete:
100%
|
Priority:
5 |
 Subscribe to task |
Start Date:
2006-07-12 |
End Date:
2006-07-19 |
Task Summary:
Support "on prebuilt table" |
Original Comment:
Supporting "on prebuilt table" enables the creation of snapshots that allows inserts and deletes on new records. Those records can be inserted and managed like regular table records. When the snapshot is refreshed, the records inserted on the table are kept and only the "real" snapshot records are refreshed.
This feature can prevent the use of UNIONS on distributed tables. |
|
|
|
Hours
36 |
Status
Closed |
Tasks That Depend on This Task
| Task Id |
Task Summary |
| 1000111 |
Stress test the ON PREBUILT TABLE feature |
|
Related Tracker Items
|
Followups
| Comment |
Date |
By |
Refresh modification done, including FAST, COMPLETE and FORCE.
Fixed some bugs on create/drop snapshot modifications.
|
2006-06-26 19:30 |
cunha17 |
Create snapshot modifications done.
Drop snapshot modifications done.
-> Needs to implement refresh modifications |
2006-06-26 00:13 |
cunha17 |
I thought of two approaches:
1. Additional table for snapshot data
1.1. Inheritance + Trigger + Rule
1.2. Union View + Rule
2) Modify the base table adding a "hidden" column that stores whether the data is "local" or from the "snapshot".
#1 advantage
1) No need to change the structure of the base table
#1 disadvantages:
1) We must duplicate all constraints and indexes of the base table to the snapshot table;
2) We must put a trigger on the base table to check whether the inserted/updated data already exists on the snapshot table, since Pg inheritance can't deal with a single constraint between inherited tables.
3) We have to create rules on the snapshot table to disable insert, delete and updates.
4) We have to create a rule for selects on the base table to, instead, do a "ALL" select on the snapshot table
#2 advantages
1) We don't need to duplicate the constraints, indexes, triggers, etc., we can use them all without modifications.
#2 disadvantages
1) We have to change the structure of the base table
For higher performance and lower complexity, I'm for #2.
So what I thought was:
1) On CREATE SNAPSHOT ON PREBUILT TABLE
- ADD COLUMN "PBT$" if it does not already exists issuing a warning when it does
- Initialize all NULL values on the column with a [LOCAL] value
- Create the TRIGGER on each row of I,U,D
* Disable I,U,D on [REMOTE]
* Force [LOCAL] for insert/updates
2) On REFRESH SNAPSHOT
- Disable the trigger
- Add/delete [REMOTE] rows based on the refresh method (placing the [REMOTE] value on the column)
- Enable the trigger
3) On DROP SNAPSHOT
- Remove [REMOTE] rows of the current snapshot
- Drop "PBT$" column if there is no other snapshot on this prebuilt table
[REMOTE] can be the snapshot id
[LOCAL] can be zero
|
2006-06-12 15:09 |
cunha17 |
|
Task Change History
| Field |
Old Value |
Date |
By |
| dependent_on_id | 100 |
2007-10-11 13:20 |
cunha17 |
| assigned_to_id | 100 |
2007-10-11 13:20 |
cunha17 |
| dependent_on_id | 100 |
2007-09-18 13:13 |
cunha17 |
| status_id | 1 |
2007-09-18 13:13 |
cunha17 |
| percent_complete | 95 |
2006-06-26 19:33 |
cunha17 |
| dependent_on_id | 100 |
2006-06-26 19:33 |
cunha17 |
| percent_complete | 20 |
2006-06-26 19:30 |
cunha17 |
| dependent_on_id | 100 |
2006-06-26 19:30 |
cunha17 |
| dependent_on_id | 100 |
2006-06-26 19:30 |
cunha17 |
| dependent_on_id | 100 |
2006-06-26 00:13 |
cunha17 |
| percent_complete | 0 |
2006-06-19 01:02 |
cunha17 |
| priority | 3 |
2006-06-19 01:02 |
cunha17 |
| dependent_on_id | 100 |
2006-06-19 01:02 |
cunha17 |
| group_project_id | 141 |
2006-06-19 01:02 |
cunha17 |
| dependent_on_id | 100 |
2006-06-12 15:09 |
cunha17 |
|
