An error occurs after changing the column type symbol type of the monthly partitioning table to varchar

Community
1

this is my table

CREATE TABLE 'cps_test' (
  dst_ip SYMBOL,
  cps LONG,
  row_num SHORT,
  ts TIMESTAMP

) timestamp (ts) PARTITION BY MONTH WAL;
I changed dst_ip to varchar type in this table.
When I changed this, I got the following errors:

The first one was “the table is suspended”.
I solved this error through the following url: My Questdb table is suspended and I cannot resume it, how can I reset the errors?

The second error is “expected timestamp column but type is SYMBOL”.

INSERT INTO
  cps_dst_day
SELECT
  dst_ip,
  sum(cps) cps,
  row_num,
  to_timezone(ts, 'Asis/Seoul')
FROM cps_raw;

Can you tell me what caused the second error and what to do about it?

2

Hi @allen ,

What is the schema of cps_raw?

3

This is the source data of cps.
cps_test is structurally the same table as the table.

4

This is the source data of cps.
cps_test is structurally the same table as the table.

5

By creating a new table and renaming it, it is currently working fine with no errors. I just want to know why this happened.

6

We’d need to reproduce to find that out!

7

For tables that do time aggregation and daily aggregation, the column type was changed without a problem and there were no problems. The current problem occurred in the monthly aggregate table.

8 
create table network_traffic (
    org_id symbol,
    device_id symbol,
    ip symbol,
    domain symvol,
    up_byte long,
    down_byte long, 
    ts timestamp
) timestamp(ts) partition by hour;

This table accumulates about 100 million rows per day.
There are too many unique values ​​in ip that are not duplicated, so I changed ip to varchar.
When I changed the table, the order of ip changed to the back.
Is it possible that the order of columns changes when changing the type of a table column?
Could this be causing the problem above?

9 
alter table network_traffic alter column device_id type symbol;

When I changed the symbol with the query, the capacity was set to a value that was too large.
The org_id consists of 30 unique values.
The device_id consists of 200 unique values.

image
image1937×212 20.3 KB

10

Hi @allen ,

You’ve cut off the column names in that image. Can you confirm that the bottom row is device_id?

Did you subsequently run any DDLs? I guess you must have to add the index.

Also, please can you double check that after a database restart, the symbol capacity stays the same number?

11

this is orginal table, but
Originally, organization_id, device_id, dst_ip IDs were symbols, but in the case of varchar vs symbol, they were changed to test ilp performance.

image
image478×633 33.1 KB

this is testing table

image
image474×691 36.4 KB

This is how I worked:

CREATE TABLE 'traffic_dst_min_test' (
  organization_id VARCHAR,
  device_id VARCHAR,
  dst_ip VARCHAR,
  domain SYMBOL,
  lan_tcp_up_bytes LONG,
  lan_tcp_down_bytes LONG,
  lan_udp_up_bytes LONG,
  lan_udp_down_bytes LONG,
  wan_tcp_up_bytes LONG,
  wan_tcp_down_bytes LONG,
  wan_udp_up_bytes LONG,
  wan_udp_down_bytes LONG,
  ts TIMESTAMP
  
) timestamp (ts) PARTITION BY HOUR WAL;

insert into traffic_dst_min_test 
select
  organization_id,
  device_id,
  dst_ip,
  case
    when cast(dst_ip as ipv4) is null then dst_ip
    else null
  end domain,
  lan_tcp_up_bytes,
  lan_tcp_down_bytes,
  lan_udp_up_bytes,
  lan_udp_down_bytes,
  wan_tcp_up_bytes,
  wan_tcp_down_bytes,
  wan_udp_up_bytes,
  wan_udp_down_bytes,
  ts
from
  traffic_dst_min
where
  ts IN '2024-11-11';

alter table traffic_dst_min_test  alter column organization_id type symbol;
alter table traffic_dst_min_test  alter column organization_id add index;
alter table traffic_dst_min_test  alter column device_id type symbol;
alter table traffic_dst_min_test  alter column device_id add index;

After restarting, the symbol’s capacity was resolved.

image
image1979×981 90.7 KB

12

Okay great, I think this is just a visual bug. The UI is served by a cache which may not be given a consistent view of the symbol capacity. Since it is fixed on a restart, this means tha cache is re-hydrated by reading from disk, and the correct capacity is read.

This is something we can fix!