phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: Index table in SYSTEM.CATALOG without DATA_TABLE_NAME and INDEX_TYPE
Date Tue, 06 Feb 2018 18:21:02 GMT
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