[jira] [Comment Edited] (HAWQ-304) Support update and delete on non-heap tables

["Michael Andre Pearce (IG) (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HAWQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15325523#comment-15325523 ] 

Michael Andre Pearce (IG) edited comment on HAWQ-304 at 6/10/16 11:51 PM:
--------------------------------------------------------------------------

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 have a bigserial incrementing as update_version 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 update_version for a key 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





was (Author: michael.andre.pearce):
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)