feat(binder): support `DEFAULT current_timestamp` for `timestamp without time zone`

[xiangjinwu]

This issue acknowledges it is not supported, and why we may want to keep it unsupported.

The following works in PostgreSQL:

test=# show timezone;
    TimeZone    
----------------
 Asia/Singapore
(1 row)

test=# create table aware_or_naive (id varchar, aware timestamptz default current_timestamp, naive timestamp default current_timestamp);
CREATE TABLE

test=# insert into aware_or_naive(id) values ('a');
INSERT 0 1

test=# select * from aware_or_naive;
 id |             aware             |           naive            
----+-------------------------------+----------------------------
 a  | 2024-09-12 17:01:04.398326+08 | 2024-09-12 17:01:04.398326
(1 row)

test=# set timezone = 'UTC';
SET

test=# select * from aware_or_naive;
 id |             aware             |           naive            
----+-------------------------------+----------------------------
 a  | 2024-09-12 09:01:04.398326+00 | 2024-09-12 17:01:04.398326
(1 row)

test=# insert into aware_or_naive(id) values ('b');
INSERT 0 1

test=# select * from aware_or_naive;
 id |             aware             |           naive            
----+-------------------------------+----------------------------
 a  | 2024-09-12 09:01:04.398326+00 | 2024-09-12 17:01:04.398326
 b  | 2024-09-12 09:01:50.825673+00 | 2024-09-12 09:01:50.825673
(2 rows)

Similar behavior for MySQL datetime

mysql> SET time_zone = 'Asia/Singapore';
Query OK, 0 rows affected (0.01 sec)

mysql> create table aware_or_naive (id text, aware timestamp default current_timestamp, naive datetime default current_timestamp);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into aware_or_naive(id) values ('a');
Query OK, 1 row affected (0.01 sec)

mysql> select * from aware_or_naive;
+------+---------------------+---------------------+
| id   | aware               | naive               |
+------+---------------------+---------------------+
| a    | 2024-09-12 17:29:09 | 2024-09-12 17:29:09 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SET time_zone = 'UTC';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from aware_or_naive;
+------+---------------------+---------------------+
| id   | aware               | naive               |
+------+---------------------+---------------------+
| a    | 2024-09-12 09:29:09 | 2024-09-12 17:29:09 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into aware_or_naive(id) values ('b');
Query OK, 1 row affected (0.01 sec)

mysql> select * from aware_or_naive;
+------+---------------------+---------------------+
| id   | aware               | naive               |
+------+---------------------+---------------------+
| a    | 2024-09-12 09:29:09 | 2024-09-12 17:29:09 |
| b    | 2024-09-12 09:29:37 | 2024-09-12 09:29:37 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)

That is, if you expect current_timestamp to grow monotonically, you should never store it in a timestamp without time zone column! Use timestamptz instead.

Furthermore, we can see that PostgreSQL follows the session timezone settings at the time of INSERT, rather than at the time of CREATE TABLE. However, RisingWave may continuously ingest rows in the background without an interactive user session, and streaming jobs already follow the timezone at the time of create materialized view. So even if we want to support such misuse for the sake of more comprehensive PostgreSQL-compatibility, it is either infeasible (INSERT) or incompatible (CREATE).

[xiangjinwu]

But we may want to improve the error message & relevant documents:

dev=> create table aware_or_naive (id varchar, aware timestamptz default current_timestamp, naive timestamp default current_timestamp);
ERROR:  Failed to run the query

Caused by these errors (recent errors listed first):
  1: Expr error
  2: Unsupported! function: cast(timestamp with time zone) -> timestamp without time zone