search

cockroachdb / cockroach

CockroachDB - the open source, cloud-native distributed SQL database.
https://www.cockroachlabs.com
Other
29.58k stars 3.71k forks source link

ERROR: unknown signature: timezone(timestamptz, string) #77460

Closed etraphagan closed 2 years ago

etraphagan commented 2 years ago

Describe the problem

Upgraded database from 20.1 to 20.2 to 21.1 to 21.2 from docker image. Most database tables that use timestamp data type is now unable to SHOW COLUMNS.

schema_name | table_name | type | owner | estimated_row_count | locality --------------+-----------------------------------------------+-------+-------+---------------------+----------- public | tablename | table | root | 261 | NULL

root@:26257/db> SHOW COLUMNS FROM tablename;
ERROR: unknown signature: timezone(timestamptz, string)
SQLSTATE: 42883

Expected behavior

Expect to see column data types, but instead get the SQL error:

ERROR: unknown signature: timezone(timestamptz, string) SQLSTATE: 42883

Environment:

Additional context

Developers are unable to view data via DBeaver JDBC 4.1 driver for PostgreSQL.

Jira issue: CRDB-13607

blathers-crl[bot] commented 2 years ago

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.

msbutler commented 2 years ago

@etraphagan this is a regression from https://github.com/cockroachdb/cockroach/pull/46748. The type you're using was deprecated. There's a manual way to fix this:

For each problematic column, run the following command:

ALTER TABLE [your_table]
ALTER COLUMN [your problematic column]
SET DEFAULT timezone('UTC':::STRING, current_timestamp(6:::INT8):::TIMESTAMPTZ)::TIMESTAMPTZ;

Could you try this on one table and let me know if SHOW COLUMNS works again?

etraphagan commented 2 years ago

@msbutler Do I have to change the type to TIMESTAMPTZ from TIMESTAMP?

ERROR: expected DEFAULT expression to have type timestamp, but 'timezone('UTC', current_timestamp(6))::TIMESTAMPTZ' has type timestamptz
SQLSTATE: 42804

Default is currently set to NULL:

CREATE TABLE public.tablename ( 
    columnname TIMESTAMP NULL,
    ....
msbutler commented 2 years ago

hm, I don't think you need to change columns that were originally type TIMESTAMP. Is the bug occurring on columns that were originally type TIMESTAMP?

After the bug gets fixed, It's also worth noting that we recommend using TIMESTAMPTZ over TIMESTAMP in general.

etraphagan commented 2 years ago

@msbutler Yes, the only time related columns are TIMESTAMP.

  table_name |                                                                                                       create_statement
-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  tablename  | CREATE TABLE public.tablename (
             |     redacted UUID NOT NULL,
             |     redacted UUID[] NULL,
             |     redacted UUID NULL,
             |     redacted UUID NULL,
             |     redacted TIMESTAMP NULL,
             |     redacted TIMESTAMP NULL,
             |     redacted STRING NULL,
             |     redacted STRING NULL,
             |     redacted STRING NULL,
             |     redacted FLOAT8 NOT NULL,
             |     redacted FLOAT8 NOT NULL,
             |     redacted STRING NULL,
             |     redacted STRING NOT NULL,
             |     redacted INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
             |     redacted BOOL NOT NULL DEFAULT true,
             |     redacted TIMESTAMP NULL,
             |     redacted UUID NULL,
             |     redacted BOOL NULL DEFAULT false,
             |     redacted UUID NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (redacted ASC),
             |     UNIQUE INDEX redacted (redacted ASC),
             |     FAMILY "primary" (redacted, redacted, redacted, redacted, redacted, redacted, redacted, redacted, redacted, redacted, redacted, redacted, redacted, redacted, redacted, redacted, redacted, redacted, redacted)
             | )
(1 row)
msbutler commented 2 years ago

ok, we could try (probably best to do this on an experimental table first in case this leads to data loss):

preserve the TIMESTAMP type:

ALTER TABLE [your_table]
ALTER COLUMN [your problematic column]
SET DEFAULT timezone('UTC':::STRING, current_timestamp(6:::INT8):::TIMESTAMP)::TIMESTAMP;

if that doesn't work, let's try converting it to TIMESTAMPTZ with:

ALTER TABLE [your_table]
ALTER COLUMN [your problematic column]
SET DEFAULT timezone('UTC':::STRING, current_timestamp(6:::INT8):::TIMESTAMP)::TIMESTAMPTZ;

Docs for this timezone function here.

etraphagan commented 2 years ago

@msbutler Running the alter table to add default for TIMESTAMP I still get:

> SHOW COLUMNS FROM tablename;
ERROR: unknown signature: timezone(timestamptz, string)
SQLSTATE: 42883

The other alter does not convert the type, should I do this first?

> ALTER TABLE tablename ALTER COLUMN redacted SET DATA TYPE TIMESTAMPTZ;
msbutler commented 2 years ago

Good idea. Try that first.

etraphagan commented 2 years ago

@msbutler Same thing. column data type is now TIMESTAMPTZ with DEFAULT timezone('UTC':::STRING, current_timestamp(6:::INT8):::TIMESTAMPTZ)::TIMESTAMPTZ

> SHOW COLUMNS FROM tablename;
ERROR: unknown signature: timezone(timestamptz, string)
SQLSTATE: 42883
etraphagan commented 2 years ago

@msbutler could it possibly be the database has metadata for the old timezone function that is depreciated? This error occurs on every single table on 1 database. Other databases on the same cockroach server do not have this same issue.

etraphagan commented 2 years ago 
> select * from crdb_internal.session_variables;
                        variable                        |                                          value                                          | hidden
--------------------------------------------------------+-----------------------------------------------------------------------------------------+---------
  allow_prepare_as_opt_plan                             | off                                                                                     |  true
  application_name                                      | $ cockroach sql                                                                         | false
  backslash_quote                                       | safe_encoding                                                                           | false
  bytea_output                                          | hex                                                                                     | false
  client_encoding                                       | UTF8                                                                                    | false
  client_min_messages                                   | notice                                                                                  | false
  crdb_version                                          | CockroachDB CCL v21.2.6 (x86_64-unknown-linux-gnu, built 2022/02/22 18:30:37, go1.16.6) | false
  database                                              | redacted                                                                                | false
  datestyle                                             | ISO, MDY                                                                                | false
  datestyle_enabled                                     | off                                                                                     | false
  default_int_size                                      | 8                                                                                       | false
  default_tablespace                                    |                                                                                         | false
  default_transaction_isolation                         | serializable                                                                            | false
  default_transaction_priority                          | normal                                                                                  | false
  default_transaction_read_only                         | off                                                                                     | false
  default_transaction_use_follower_reads                | off                                                                                     | false
  disable_partially_distributed_plans                   | off                                                                                     | false
  disallow_full_table_scans                             | off                                                                                     | false
  distsql                                               | auto                                                                                    | false
  distsql_workmem                                       | 64 MiB                                                                                  | false
  enable_copying_partitioning_when_deinterleaving_table | off                                                                                     | false
  enable_drop_enum_value                                | off                                                                                     | false
  enable_experimental_alter_column_type_general         | off                                                                                     | false
  enable_experimental_stream_replication                | off                                                                                     | false
  enable_implicit_select_for_update                     | on                                                                                      | false
  enable_insert_fast_path                               | on                                                                                      | false
  enable_multiregion_placement_policy                   | off                                                                                     | false
  enable_seqscan                                        | on                                                                                      | false
  enable_zigzag_join                                    | on                                                                                      | false
  escape_string_warning                                 | on                                                                                      | false
  experimental_computed_column_rewrites                 |                                                                                         |  true
  experimental_distsql_planning                         | off                                                                                     | false
  experimental_enable_auto_rehoming                     | off                                                                                     | false
  experimental_enable_hash_sharded_indexes              | off                                                                                     | false
  experimental_enable_implicit_column_partitioning      | off                                                                                     | false
  experimental_enable_temp_tables                       | off                                                                                     | false
  experimental_enable_unique_without_index_constraints  | off                                                                                     | false
  experimental_use_new_schema_changer                   | off                                                                                     | false
  extra_float_digits                                    | 2                                                                                       | false
  force_savepoint_restart                               | off                                                                                     | false
  foreign_key_cascades_limit                            | 10000                                                                                   | false
  idle_in_session_timeout                               | 0                                                                                       | false
  idle_in_transaction_session_timeout                   | 0                                                                                       | false
  integer_datetimes                                     | on                                                                                      | false
  intervalstyle                                         | postgres                                                                                | false
  intervalstyle_enabled                                 | off                                                                                     | false
  is_superuser                                          | on                                                                                      | false
  large_full_scan_rows                                  | 1000                                                                                    | false
  lc_collate                                            | C.UTF-8                                                                                 | false
  lc_ctype                                              | C.UTF-8                                                                                 | false
  lc_messages                                           | C.UTF-8                                                                                 | false
  lc_monetary                                           | C.UTF-8                                                                                 | false
  lc_numeric                                            | C.UTF-8                                                                                 | false
  lc_time                                               | C.UTF-8                                                                                 | false
  locality                                              |                                                                                         | false
  locality_optimized_partitioned_index_scan             | on                                                                                      | false
  lock_timeout                                          | 0                                                                                       | false
  max_identifier_length                                 | 128                                                                                     | false
  max_index_keys                                        | 32                                                                                      | false
  node_id                                               | 1                                                                                       | false
  on_update_rehome_row_enabled                          | on                                                                                      | false
  optimizer                                             | on                                                                                      | false
  optimizer_use_histograms                              | on                                                                                      | false
  optimizer_use_multicol_stats                          | on                                                                                      | false
  override_multi_region_zone_config                     | off                                                                                     | false
  prefer_lookup_joins_for_fks                           | off                                                                                     | false
  propagate_input_ordering                              | off                                                                                     | false
  reorder_joins_limit                                   | 8                                                                                       | false
  require_explicit_primary_keys                         | off                                                                                     | false
  results_buffer_size                                   | 16384                                                                                   | false
  role                                                  | none                                                                                    | false
  row_security                                          | off                                                                                     | false
  save_tables_prefix                                    |                                                                                         |  true
  search_path                                           | "$user", public                                                                         | false
  serial_normalization                                  | rowid                                                                                   | false
  server_encoding                                       | UTF8                                                                                    | false
  server_version                                        | 13.0.0                                                                                  | false
  server_version_num                                    | 130000                                                                                  | false
  session_authorization                                 | root                                                                                    |  true
  session_id                                            | 16da7564f138ec1b0000000000000001                                                        | false
  session_user                                          | root                                                                                    | false
  sql_safe_updates                                      | on                                                                                      | false
  ssl_renegotiation_limit                               | 0                                                                                       |  true
  standard_conforming_strings                           | on                                                                                      | false
  statement_timeout                                     | 0                                                                                       | false
  stub_catalog_tables                                   | on                                                                                      | false
  synchronize_seqscans                                  | on                                                                                      | false
  synchronous_commit                                    | on                                                                                      | false
  testing_vectorize_inject_panics                       | off                                                                                     | false
  timezone                                              | UTC                                                                                     | false
  tracing                                               | off                                                                                     | false
  transaction_isolation                                 | serializable                                                                            | false
  transaction_priority                                  | normal                                                                                  | false
  transaction_read_only                                 | off                                                                                     | false
  transaction_rows_read_err                             | 0                                                                                       | false
  transaction_rows_read_log                             | 0                                                                                       | false
  transaction_rows_written_err                          | 0                                                                                       | false
  transaction_rows_written_log                          | 0                                                                                       | false
  transaction_status                                    | NoTxn                                                                                   | false
  vectorize                                             | on                                                                                      | false
(100 rows)
etraphagan commented 2 years ago

@msbutler It is definitely database wide. I created a new table with a UUID and TIMESTAMPTZ and I get the same results with \d newtable; or SHOW COLUMNS FROM newtable;

ERROR: unknown signature: timezone(timestamptz, string)
SQLSTATE: 42883
etraphagan commented 2 years ago 
root@:26257/database> CREATE TABLE public.newtable (
    id UUID NOT NULL, 
    tabletime TIMESTAMPTZ NULL,
    UNIQUE INDEX newtable_id_key (id ASC),
    FAMILY "primary" (id, tabletime)
);
CREATE TABLE

Time: 11ms total (execution 11ms / network 0ms)

root@:26257/database> SHOW COLUMNS FROM newtable;
ERROR: unknown signature: timezone(timestamptz, string)
SQLSTATE: 42883
root@:26257/database> SELECT * FROM newtable;
  id | tabletime
-----+------------
(0 rows)

Time: 10ms total (execution 10ms / network 0ms)

root@:26257/database> DROP TABLE newtable;
DROP TABLE

Time: 74ms total (execution 74ms / network 0ms)

root@:26257/database> CREATE TABLE public.newtable (
    id UUID NOT NULL,
    UNIQUE INDEX newtable_id_key (id ASC),
    FAMILY "primary" (id)
);
CREATE TABLE

Time: 16ms total (execution 15ms / network 0ms)

root@:26257/database> \d newtable;
ERROR: unknown signature: timezone(timestamptz, string)
SQLSTATE: 42883
root@:26257/database> SHOW COLUMNS FROM newtable;
ERROR: unknown signature: timezone(timestamptz, string)
SQLSTATE: 42883
msbutler commented 2 years ago

huh. this is pretty weird. I'm going to ask the some colleagues for help. Hang tight!

rafiss commented 2 years ago

I believe the reason SHOW COLUMNS FROM newtable; is affected is because of how it's implemented internally. That command will scan the information_schema.columns virtual table, which leads to all columns (including computed columns or columns with default expressions) to be parsed and formatted.

Could you run SHOW CREATE ALL TABLES and share any results that use the timezone builtin?

etraphagan commented 2 years ago

@rafiss You were right, there was a table that was using the timezone function that I just had to drop default on to show columns on other tables. I will now have to modify the types to TIMESTAMPTZ and add a new default to use the new function. Thanks!

I couldn't SHOW CREATE ALL TABLES as the timezone function wouldn't allow it so I had to SHOW CREATE TABLE x one by one until I found one that error'd out and run a SELECT * FROM x LIMIT 1 on the data to see TIMESTAMP columns. Then I just ALTER TABLE x ALTER COLUMN y DROP DEFAULT on the TIMESTAMP columns.