Looks like it is just how Phoenix 4.10 behaves different when creating an
index...? Is there a place where we document the difference in how table
metadata is handled for each release?
Thanks in advance for your help!
0: jdbc:phoenix:labs-darth-journalnode-lv-101> create index test_users
on "func11".users("cstId");
69 rows affected (7.345 seconds)
0: jdbc:phoenix:labs-darth-journalnode-lv-101> select * from
system.catalog where TABLE_TYPE = 'i' AND column_family = 'TEST_USERS'
OR TABLE_NAME = 'TEST_USERS';
+------------+--------------+-------------+--------------+----------------+----------------+-------------+----------+---------------+---------------+------------------+--------------+-----------------+-----------------+-----------------------+
| TENANT_ID | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME |
COLUMN_FAMILY | TABLE_SEQ_NUM | TABLE_TYPE | PK_NAME |
COLUMN_COUNT | SALT_BUCKETS | DATA_TABLE_NAME | INDEX_STATE |
IMMUTABLE_ROWS | VIEW_STATEMENT | DEFAULT_COLUMN_FAMILY |
+------------+--------------+-------------+--------------+----------------+----------------+-------------+----------+---------------+---------------+------------------+--------------+-----------------+-----------------+-----------------------+
| | func11 | TEST_USERS | |
| 0 | i | | 3 | 2
| USERS | a | false |
| |
| | func11 | TEST_USERS | :cstId |
| null | | | null | null
| USERS | | |
| |
| | func11 | TEST_USERS | :id |
| null | | | null | null
| USERS | | |
| |
| | func11 | USERS | | TEST_USERS
| 15 | i | | null | null
| | | |
| |
+------------+--------------+-------------+--------------+----------------+----------------+-------------+----------+---------------+---------------+------------------+--------------+-----------------+-----------------+-----------------------+
4 rows selected (0.288 seconds)
On Fri, Feb 2, 2018 at 1:23 PM William Shen <wills...@marinsoftware.com>
wrote:
> Hi everyone,
>
> I am investigating a strange looking entry in our SYSTEM.CATALOG table.
> The row is an index table (TABLE_TYPE = i) but it does not contain any
> other index information (no DATA_TABLE_NAME and INDEX_TYPE, etc.).
>
> Has anyone encountered similar situation, or is there any other way to
> investigate how the entry was created?
>
> By the way, is there any documentation available on the SYSTEM.CATALOG,
> that I can check to make I am understanding the information in this table
> correctly?
>
> Thanks!
>
> We are running Phoenix 4.10 (upgraded previous from 4.8 and from 4.6)
> Here is a few more details on this strange "index":
>
> SELECT * from system.catalog where table_schem = 'prod' and
> data_table_name is null and TABLE_TYPE = 'i';
>
> TENANT_ID
>
> TABLE_SCHEM prod
>
> TABLE_NAME RULES
>
> COLUMN_NAME
>
> COLUMN_FAMILY IDX_ID_RULES
>
> TABLE_SEQ_NUM 0
>
> TABLE_TYPE i
>
> PK_NAME
>
> COLUMN_COUNT null
>
> SALT_BUCKETS null
>
> DATA_TABLE_NAME
>
> INDEX_STATE
>
> IMMUTABLE_ROWS
>
> VIEW_STATEMENT
>
> DEFAULT_COLUMN_FAMILY
>
> DISABLE_WAL
>
> MULTI_TENANT
>
> VIEW_TYPE null
>
> VIEW_INDEX_ID null
>
> DATA_TYPE null
>
> COLUMN_SIZE null
>
> DECIMAL_DIGITS null
>
> NULLABLE null
>
> ORDINAL_POSITION null
>
> SORT_ORDER null
>
> ARRAY_SIZE null
>
> VIEW_CONSTANT
>
> IS_VIEW_REFERENCED
>
> KEY_SEQ null
>
> LINK_TYPE 1
>
> TYPE_NAME
>
> REMARKS
>
> SELF_REFERENCING_COL_NAME
>
> REF_GENERATION
>
> BUFFER_LENGTH null
>
> NUM_PREC_RADIX null
>
> COLUMN_DEF
>
> SQL_DATA_TYPE null
>
> SQL_DATETIME_SUB null
>
> CHAR_OCTET_LENGTH null
>
> IS_NULLABLE
>
> SCOPE_CATALOG
>
> SCOPE_SCHEMA
>
> SCOPE_TABLE
>
> SOURCE_DATA_TYPE null
>
> IS_AUTOINCREMENT
>
> INDEX_TYPE null
>
> INDEX_DISABLE_TIMESTAMP null
>
> STORE_NULLS
>
> BASE_COLUMN_COUNT null
>
> IS_ROW_TIMESTAMP
>
> TRANSACTIONAL
>
> UPDATE_CACHE_FREQUENCY null
>
> IS_NAMESPACE_MAPPED
>
> AUTO_PARTITION_SEQ
>
> APPEND_ONLY_SCHEMA
>
> GUIDE_POSTS_WIDTH null
>
> COLUMN_QUALIFIER
>
> IMMUTABLE_STORAGE_SCHEME null
>
> ENCODING_SCHEME null
>
> QUALIFIER_COUNTER null
>
>
> We actually have the normal-looking index IDX_ID_RULES created for the
> RULES table, here:
>
> SELECT * from system.catalog where table_schem = 'prod' and
> data_table_name = 'RULES' and TABLE_TYPE = 'i';
>
> TENANT_ID
>
> TABLE_SCHEM prod
>
> TABLE_NAME IDX_ID_RULES
>
> COLUMN_NAME
>
> COLUMN_FAMILY
>
> TABLE_SEQ_NUM 0
>
> TABLE_TYPE i
>
> PK_NAME
>
> COLUMN_COUNT 4
>
> SALT_BUCKETS 255
>
> DATA_TABLE_NAME RULES
>
> INDEX_STATE a
>
> IMMUTABLE_ROWS false
>
> VIEW_STATEMENT
>
> DEFAULT_COLUMN_FAMILY
>
> DISABLE_WAL false
>
> MULTI_TENANT false
>
> VIEW_TYPE null
>
> VIEW_INDEX_ID null
>
> DATA_TYPE null
>
> COLUMN_SIZE null
>
> DECIMAL_DIGITS null
>
> NULLABLE null
>
> ORDINAL_POSITION null
>
> SORT_ORDER null
>
> ARRAY_SIZE null
>
> VIEW_CONSTANT
>
> IS_VIEW_REFERENCED
>
> KEY_SEQ null
>
> LINK_TYPE null
>
> TYPE_NAME
>
> REMARKS
>
> SELF_REFERENCING_COL_NAME
>
> REF_GENERATION
>
> BUFFER_LENGTH null
>
> NUM_PREC_RADIX null
>
> COLUMN_DEF
>
> SQL_DATA_TYPE null
>
> SQL_DATETIME_SUB null
>
> CHAR_OCTET_LENGTH null
>
> IS_NULLABLE
>
> SCOPE_CATALOG
>
> SCOPE_SCHEMA
>
> SCOPE_TABLE
>
> SOURCE_DATA_TYPE null
>
> IS_AUTOINCREMENT
>
> INDEX_TYPE 1
>
> INDEX_DISABLE_TIMESTAMP 0
>
> STORE_NULLS false
>
> BASE_COLUMN_COUNT -1
>
> IS_ROW_TIMESTAMP
>
> TRANSACTIONAL false
>
> UPDATE_CACHE_FREQUENCY 0
>
> IS_NAMESPACE_MAPPED false
>
> AUTO_PARTITION_SEQ
>
> APPEND_ONLY_SCHEMA false
>
> GUIDE_POSTS_WIDTH null
>
> COLUMN_QUALIFIER
>
> IMMUTABLE_STORAGE_SCHEME 1
>
> ENCODING_SCHEME 2
>
> QUALIFIER_COUNTER null
>
