Hi William,
The system catalog table changes as new features are implemented. The API
that you can count on being stable is JDBC and in particular for metadata,
our DatabaseMetaData implementation. To understand how the system catalog
changes from release to release you'd need to keep an eye on any JIRA that
may impact it.
Thanks,
James
On Tue, Feb 6, 2018 at 9:38 AM, William Shen <willshen@marinsoftware.com>
wrote:
> 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 <willshen@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
>>
>
|