[jira] [Commented] (PHOENIX-619) Support DELETE for immutable index with non PK columns

["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 commented on PHOENIX-619:
--------------------------------------

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 for immutable index with non PK columns
> ------------------------------------------------------
>
>                 Key: PHOENIX-619
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-619
>             Project: Phoenix
>          Issue Type: Task
>            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)