Re: Index table in SYSTEM.CATALOG without DATA_TABLE_NAME and INDEX_TYPE

2018-02-06 Thread William Shen 
Thank you James!

On Tue, Feb 6, 2018 at 10:21 AM James Taylor  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 
> 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 
>> 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_SCHEMprod
>>>
>>> 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_SIZEnull
>>>
>>> DECIMAL_DIGITS null
>>>
>>> NULLABLE   null
>>>
>>> ORDINAL_POSITION   null
>>>
>>> SORT_ORDER null
>>>
>>> ARRAY_SIZE null
>>>
>>> VIEW_CONSTANT
>>>
>>> IS_VIEW_REFERENCED
>>>
>>> KEY_SEQ

Re: Index table in SYSTEM.CATALOG without DATA_TABLE_NAME and INDEX_TYPE

2018-02-06 Thread James Taylor 
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 
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 
> 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_SCHEMprod
>>
>> 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_SIZEnull
>>
>> DECIMAL_DIGITS null
>>
>> NULLABLE   null
>>
>> ORDINAL_POSITION   null
>>
>> SORT_ORDER null
>>
>> ARRAY_SIZE null
>>
>> VIEW_CONSTANT
>>
>> IS_VIEW_REFERENCED
>>
>> KEY_SEQnull
>>
>> LINK_TYPE  1
>>
>> TYPE_NAME
>>
>> REMARKS
>>
>> SELF_REFERENCING_COL_NAME
>>
>> REF_GENERATION
>>
>> BUFFER_LENGTH  null
>>
>> NUM_PREC_RADIX null
>>
>> COLUMN_DEF
>>
>>

Re: Index table in SYSTEM.CATALOG without DATA_TABLE_NAME and INDEX_TYPE

2018-02-06 Thread William Shen 
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 
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_SCHEMprod
>
> 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_SIZEnull
>
> DECIMAL_DIGITS null
>
> NULLABLE   null
>
> ORDINAL_POSITION   null
>
> SORT_ORDER null
>
> ARRAY_SIZE null
>
> VIEW_CONSTANT
>
> IS_VIEW_REFERENCED
>
> KEY_SEQnull
>
> 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_TIMESTAMPnull
>
> 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_SCHEMEnull
>
> QUALIFIER_COUNTER  null
>
>
> We actually have the normal-looking index

Index table in SYSTEM.CATALOG without DATA_TABLE_NAME and INDEX_TYPE

2018-02-02 Thread William Shen 
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_SCHEMprod

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_SIZEnull

DECIMAL_DIGITS null

NULLABLE   null

ORDINAL_POSITION   null

SORT_ORDER null

ARRAY_SIZE null

VIEW_CONSTANT

IS_VIEW_REFERENCED

KEY_SEQnull

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_TIMESTAMPnull

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_SCHEMEnull

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_SCHEMprod

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_NAMERULES

INDEX_STATEa

IMMUTABLE_ROWS false

VIEW_STATEMENT

DEFAULT_COLUMN_FAMILY

DISABLE_WALfalse

MULTI_TENANT   false

VIEW_TYPE  null

VIEW_INDEX_ID  null

DATA_TYPE  null

COLUMN_SIZEnull

DECIMAL_DIGITS null

NULLABLE   null

ORDINAL_POSITION   null

SORT_ORDER null

ARRAY_SIZE null

VIEW_CONSTANT

IS_VIEW_REFERENCED

KEY_SEQnull

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_TIMESTAMP0

STORE_NULLSfalse

BASE_COLUMN_COUNT  -1

IS_ROW_TIMESTAMP

TRANSACTIONAL  false

UPDATE_CACHE_FREQUENCY 0

IS_NAMESPACE_MAPPEDfalse

AUTO_PARTITION_SEQ

APPEND_ONLY_SCHEMA false

GUIDE_POSTS_WIDTH  null

COLUMN_QUALIFIER

IMMUTABLE_STORAGE_SCHEME   1

ENCODING_SCHEME2

QUALIFIER_COUNTER  null