[
https://issues.apache.org/jira/browse/HAWQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Michael Andre Pearce (IG) updated HAWQ-304:
-------------------------------------------
Attachment: mutable_table.sql
Hi Guys,
Whilst we wait for this feature, we've made a very rudimentary work around that
manages to give the effect/simulate a mutable table (update and delete).
Ive attached a sample so others can re-use if they wish.
For simulating UPDATE we just append and simply re have a bigserial
incrementing as update_version and for each row key. At query time we select
the latest version by key.
For simulating DELETE we have a field that marks if the latest version is
actually a delete. At query time we remove deleted fields from the select.
For clean-ness we wrap the above in a view which hides the extra columns and
keeps normal SELECT queries clean from the logic a little.
We also have a compacting phase we run separately a CTAS which re-creates the
table using a SELECT on the view, essentially giving the latest and removing
previous iterations and change depth.
It seems this works relatively ok, and for updates/deletes works quite fast too.
Issues we have is that as no way to lock during the compaction if a query
occurs when we run that, we can get undesired effects. We manage this by
running at a quiet period. Any idea's how we could make this safer? And or
faster/less intensive?
Also we wonder if there is a more efficient way of doing the select in the view
to get latest row version for a key?
Cheers
Mike
> Support update and delete on non-heap tables
> --------------------------------------------
>
> Key: HAWQ-304
> URL: https://issues.apache.org/jira/browse/HAWQ-304
> Project: Apache HAWQ
> Issue Type: New Feature
> Components: Storage
> Reporter: Lei Chang
> Fix For: 3.0.0
>
> Attachments: mutable_table.sql
>
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
