Thank you James!
On Tue, Feb 6, 2018 at 10:21 AM James Taylor <jamestay...@apache.org> wrote:
> 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 <wills...@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 <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
>>>
>>
>
