Hans Zeller created TRAFODION-1432:
--------------------------------------
Summary: 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
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)
