[jira] [Assigned] (TRAFODION-1432) Base table insert/update/delete not allowed while populating an index on the table

["Suresh Subbiah (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-1432?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Suresh Subbiah reassigned TRAFODION-1432:
-----------------------------------------

    Assignee: Suresh Subbiah

> Base table insert/update/delete not allowed while populating an index on the table
> ----------------------------------------------------------------------------------
>
>                 Key: TRAFODION-1432
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-1432
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-general
>    Affects Versions: 1.0 (pre-incubation)
>         Environment: any
>            Reporter: Hans Zeller
>            Assignee: Suresh Subbiah
>
> Right now there is a serious restriction and possible cause for data corruption when creating indexes: When a user creates an index, the index is populated with a Trafodion insert/select statement and then enabled.
> There is currently no way to lock a Trafodion table to prevent concurrent inserts, updates or deletes on the base table. We hope to fix that in the near future (JIRA for that work is TBD).
> For now, it is required to prevent any concurrent updates (inserts/updates/deletes) on a base table while performing one of the following:
> * CREATE INDEX
> * LOAD
> * POPULATE INDEX
> * PURGEDATA
> Some flavors of these statements should be ok with concurrent updates, such as
> * LOAD and PURGEDATA for tables that don't have user or system-created indexes
> * CREATE INDEX ... NO POPULATE (not documented)
> * LOAD ... NO POPULATE INDEXES
> * LOAD (for cases where we can perform incremental bulk load into the index, this requires several conditions to be true)
> What happens if concurrent updates occur during one of these operations? The index will be out of sync with the base tables and will have missing, wrong or extra rows, depending on whether an insert, update or delete operation happened on the base table. To fix the problem, drop and recreate the index.
> Suresh also mentioned that he is considering a utility to verify the consistency of an index. This can also be done manually by joining the index and the base table and finding any missing or extra rows. For example, these two counts should match:
> {quote}
> select count( * ) from tab1;
> set parserflags 1;  -- need to log on as DB__ROOT for this
> select count( * ) from tab1 natural join table(index_table tab1ix) tab1ix;
> {quote}
> Another note: The SQL Reference Manual mentions an "online" option for POPULATE INDEX. This is not currently supported.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)