Open rafiss opened 4 years ago
As another data point for this issue, we are attempting to use Oracle Golden Gate replication to move data from an existing Oracle DB to CockroachDB. OGG uses the Progress ODBC driver which appears to issue a SET DateStyle='iso, ymd'
command when initiating a connection and isn't possible to disable. This appears to be a blocker for us that we can't work around.
we can't even pass datestyle as connection variable.
sh-4.4# cockroach sql --url 'postgresql://root@lb:26257?sslcert=%2Fcerts%2Fclient.root.crt&sslkey=%2Fcerts%2Fclient.root.key&sslmode=verify-full&sslrootcert=%2Fcerts%2Fca.crt&application_name=test&database=bank&datestyle=ISO, MDY'
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v20.2.5 (x86_64-unknown-linux-gnu, built 2021/02/16 12:52:58, go1.13.14) (same version as client)
# Cluster ID: 22c50ba7-1579-4b8b-8e8e-641a98b25f6e
#
# Enter \? for a brief introduction.
#
root@lb:26257/bank> \q
sh-4.4# cockroach sql --url 'postgresql://root@lb:26257?sslcert=%2Fcerts%2Fclient.root.crt&sslkey=%2Fcerts%2Fclient.root.key&sslmode=verify-full&sslrootcert=%2Fcerts%2Fca.crt&application_name=test&database=bank&datestyle=MDY, ISO'
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
ERROR: setting datestyle must be absent or ISO, MDY; got MDY, ISO
Failed running "sql"
@rafiss Can we revisit this for the May milestone?
I hope that we can get started on it, but this will have the same complexities as described in https://github.com/cockroachdb/cockroach/pull/62313#pullrequestreview-618881295 -- will need a potentially big refactor/new interfaces.
I started working on a library to work across all styles (ISO,Postgres,SQL,German), but it turns out we only need the order components {MDY,YMD,DMY} to work maintaining the ISO style. That's a lot simpler, I'll be doing that instead.
If anyone wants to embark on this for future work, I've started that work here https://github.com/cockroachdb/pgdatetime. I have basically attempted to copy Postgres's parse logic, I have the tokens, formatting and some parsing logic working. But there are many road bumps to go:
time.ParseInLocation(loc, ...)
then t.Location()
to parse the timezone and translate these shorthands correctly to resolve the, e.g., IST ambiguity.Support for ISO / MDY,DMY,YMD is complete. Other formats are not yet supported as they cannot be parsed, see previous comment.
heads up stuff like https://github.com/cockroachdb/cockroach/blob/d6b2ecb2d87bc1dd27a1c0915fff2859f6e71ec7/pkg/sql/roleoption/role_option.go#L68 needs to always needs to convert as ISO if we decide to support a non-ISO DateStyle.
full docs: https://www.postgresql.org/docs/9.5/runtime-config-client.html#GUC-DATESTYLE
Epic CRDB-2508
Jira issue: CRDB-6339