phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From William Shen <wills...@marinsoftware.com>
Subject Re: Index table in SYSTEM.CATALOG without DATA_TABLE_NAME and INDEX_TYPE
Date Tue, 06 Feb 2018 18:36:43 GMT
Thank you James!

On Tue, Feb 6, 2018 at 10:21 AM James Taylor <jamestaylor@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 <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
>>>
>>
>

Mime
View raw message