[
https://issues.apache.org/jira/browse/PHOENIX-3056?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15365909#comment-15365909
]
James Taylor commented on PHOENIX-3056:
---------------------------------------
[~lhofhansl] - I see we're both on the same timezone. :-) Both deletes and
upserts are fine while an index is being built and it shouldn't make any
difference if an index is built asynchronously or not (except my comment above
- for tests, where MR facilities are not available, we have no mechanism to
build an index asynchronously until PHOENIX-2966 makes it in - but this isn't a
blocker IMHO).
The one thing that is different about deletes for immutable indexes is that
there are cases where they are disallowed. The case outlined in this JIRA
should be fine, though, as the index involves only PK columns. Here's a case
that would be legitimately disallowed, though:
{code}
CREATE TABLE t (k INTEGER PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)
IMMUTABLE_ROWS=true;
CREATE INDEX i1 ON t (v1);
CREATE INDEX i2 ON t (v2);
DELETE FROM t WHERE v1 = 'foo';
{code}
The reason this can't be handled is that we cannot issue a delete statement for
{{i2}} because the rows have no information {{v1}} in the index. This is an
edge case and it's possible in the future that a join between the data table
and the index table could be done to issue the deletes.
[~rangent] - would you mind trying to create the index in the following way
instead?
{code}
CREATE INDEX TEST_INDEX ON TEST_TABLE (pk3, pk2) INCLUDE (pk1) ASYNC;
{code}
In reality, {{pk1}} is in the index, but perhaps our check for the legitimate
case we're not able to handle is getting triggered for this case that should be
able to be handled.
> Incorrect error message when deleting a record from table with async index
> creation in progress
> -----------------------------------------------------------------------------------------------
>
> Key: PHOENIX-3056
> URL: https://issues.apache.org/jira/browse/PHOENIX-3056
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.7.0
> Reporter: Brian Esserlieu
> Priority: Minor
> Fix For: 4.8.0
>
>
> Repro:
> DROP TABLE IF EXISTS TEST_TABLE;
> CREATE TABLE IF NOT EXISTS TEST_TABLE (
> pk1 VARCHAR NOT NULL,
> pk2 VARCHAR NOT NULL,
> pk3 VARCHAR
> CONSTRAINT PK PRIMARY KEY
> (
> pk1,
> pk2,
> pk3
> )
> ) MULTI_TENANT=true,IMMUTABLE_ROWS=true;
> CREATE INDEX TEST_INDEX ON TEST_TABLE (pk3, pk2) ASYNC;
> upsert into TEST_TABLE (pk1, pk2, pk3) values ('a', '1', 'value1');
> upsert into TEST_TABLE (pk1, pk2, pk3) values ('a', '2', 'value2');
> select * from test_table;
> delete from TEST_TABLE where pk1 = 'a';
> When I run the above I get the following error on the delete statement:
> "Error: ERROR 1027 (42Y86): All columns referenced in a WHERE clause must be
> available in every index for a table with immutable rows. tableName=TEST_TABLE
> SQLState: 42Y86
> ErrorCode: 1027
> Error occurred in:
> delete from TEST_TABLE where pk1 = 'a'"
> Notice the SQL works simply by removing the ASYNC keyword from the index
> creation statement.
> The error message should reflect that the index is being created so deletes
> are blocked until that completes.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
