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 17:38:19 GMT
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