[jira] [Comment Edited] (PHOENIX-619) Support DELETE over table with immutable index when possible

["James Taylor (JIRA)" <ji...@apache.org>]

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

James Taylor edited comment on PHOENIX-619 at 8/11/14 7:18 PM:
---------------------------------------------------------------

The plan is to mark an *index* as immutable (see PHOENIX-1160) rather than specifying this as a table-wide property. We can support deletion of rows in an immutable index iff only columns contained by all immutable indexes are used to filter on in the DELETE statement. In this case, we can re-write the DELETE statement to run it against each immutable index as well to keep it in sync with the data table. Otherwise, we'll throw an exception.

On the UPSERT side of things, we'll still rely on the user to *not* mutate a column that is in an immutable index (indexed or covered). We cannot really validate this without losing the performance benefit we get from immutable indexes, so this should be considered an *advanced* feature.


was (Author: jamestaylor):
The plan is to mark an *index* as immutable rather than specifying this as a table-wide property. We can support deletion of rows in an immutable index iff only columns contained by all immutable indexes are used to filter on in the DELETE statement. In this case, we can re-write the DELETE statement to run it against each immutable index as well to keep it in sync with the data table. Otherwise, we'll throw an exception.

On the UPSERT side of things, we'll still rely on the user to *not* mutate a column that is in an immutable index (indexed or covered). We cannot really validate this without losing the performance benefit we get from immutable indexes, so this should be considered an *advanced* feature.

> Support DELETE over table with immutable index when possible
> ------------------------------------------------------------
>
>                 Key: PHOENIX-619
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-619
>             Project: Phoenix
>          Issue Type: Improvement
>            Reporter: Artur Denysenko
>              Labels: enhancement
>
> I try to delete rows in this table:
> ```sql
> CREATE TABLE test.date2(
>     id BIGINT NOT NULL PRIMARY KEY, 
>     d  DATE   NOT NULL
> ) IMMUTABLE_ROWS=true;
> CREATE INDEX idx_d ON test.date2(d);
> DELETE FROM test.date2;
> ```
> I do not understand why we have this limitation.
> DELETE works fine in tables with "IMMUTABLE_ROWS=false" and no "PK column in index".
> It's must be also reflected in "Phoenix Language Reference".



--
This message was sent by Atlassian JIRA
(v6.2#6252)