SCM

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.
Assigned to
cunha17
Dependent on task
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

Artifact Summary Tracker Open Date Remove Relation
Support ON PREBUILT TABLE Feature Requests 2006-06-12 15:59

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_id100 2007-10-11 13:20 cunha17
assigned_to_id100 2007-10-11 13:20 cunha17
dependent_on_id100 2007-09-18 13:13 cunha17
status_id1 2007-09-18 13:13 cunha17
percent_complete95 2006-06-26 19:33 cunha17
dependent_on_id100 2006-06-26 19:33 cunha17
percent_complete20 2006-06-26 19:30 cunha17
dependent_on_id100 2006-06-26 19:30 cunha17
dependent_on_id100 2006-06-26 19:30 cunha17
dependent_on_id100 2006-06-26 00:13 cunha17
percent_complete0 2006-06-19 01:02 cunha17
priority3 2006-06-19 01:02 cunha17
dependent_on_id100 2006-06-19 01:02 cunha17
group_project_id141 2006-06-19 01:02 cunha17
dependent_on_id100 2006-06-12 15:09 cunha17
Powered By FusionForge