sql: support optional TIMESTAMP precision
knz opened this issue · 26 comments
Is your feature request related to a problem? Please describe.
CREATE TABLE t(x TIMESTAMP(6)) should work
See: https://www.postgresql.org/docs/11/static/datatype-datetime.html#DATATYPE-TIMEZONES
time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6.
Describe the solution you'd like
MVP: An optional precision of 6 should be recognized and ignored (this is the default in CockroachDB)
Optionally: support for other precisions than 6.
Workaround
If the desired precision is 6, then the precision can be omitted altogether to obtain equivalent behavior.
Note: when we support this feature we can re-introduce nanosecond precision (eg via timestamp(9)) and simply keep 6 as default for pg compat.
Double check #16349 when working on this. Verify whether current_timestamp also needs to accept an optional precision.
I ran into this issue on Symfony4/Doctrine, CREATE TABLE fails because of TIMESTAMP precision.
Symfony4 log
In AbstractPostgreSQLDriver.php line 79:
An exception occurred while executing 'CREATE TABLE migration_versions (version VARCHAR(14) NOT NULL, executed_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, PRIMARY KEY(version))':
SQLSTATE[0A000]: Feature not supported: 7 ERROR: unimplemented at or near "zone"
DETAIL: source SQL:
CREATE TABLE migration_versions (version VARCHAR(14) NOT NULL, executed_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, PRIMARY KEY(version))
^
HINT: See: https://github.com/cockroachdb/cockroach/issues/32098
I installed the latest CockroachDB v2.1.4 (and later v2.2.0-alpha.20190114 also), set up a Symfony4/Doctrine project, configured it with pdo_pgsql for CockroachDB, created a database with php bin/console doctrine:database:create, then created an entity as described on https://symfony.com/doc/current/doctrine.html, and ran the migration with php bin/console make:migration at which point it gave the above errors.
If the SQL is changed to CREATE TABLE migration_versions (version VARCHAR(14) NOT NULL, executed_at TIMESTAMP WITHOUT TIME ZONE NOT NULL, PRIMARY KEY(version));, without specifying TIMESTAMP precision, it works.
This is related to #16349 too because Symfony4/Doctrine specifically ask for TIMESTAMP(0), but at the moment it is not accepting any kind of precision value.
In Symfony4/Doctrine this issue can be worked around by deleting all the precision entries from the vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php, after which the migration creates the database tables and subsequently throws the following error:
In AbstractPostgreSQLDriver.php line 79:
An exception occurred while executing 'SELECT
a.attnum,
quote_ident(a.attname) AS field,
t.typname AS type,
format_type(a.atttypid, a.atttypmod) AS complete_type,
(SELECT t1.typname FROM pg_catalog.pg_type t1 WHERE t1.oid = t.typbasetype) AS domain_type,
(SELECT format_type(t2.typbasetype, t2.typtypmod) FROM
pg_catalog.pg_type t2 WHERE t2.typtype = 'd' AND t2.oid = a.atttypid) AS domain_complete_type,
a.attnotnull AS isnotnull,
(SELECT 't'
FROM pg_index
WHERE c.oid = pg_index.indrelid
AND pg_index.indkey[0] = a.attnum
AND pg_index.indisprimary = 't'
) AS pri,
(SELECT pg_get_expr(adbin, adrelid)
FROM pg_attrdef
WHERE c.oid = pg_attrdef.adrelid
AND pg_attrdef.adnum=a.attnum
) AS default,
(SELECT pg_description.description
FROM pg_description WHERE pg_description.objoid = c.oid AND a.attnum = pg_description.objsubid
) AS comment
FROM pg_attribute a, pg_class c, pg_type t, pg_namespace n
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'backward_dependencies' AND n.nspname = 'crdb_internal'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
AND n.oid = c.relnamespace
ORDER BY a.attnum':
SQLSTATE[XX000]: Internal error: 7 ERROR: could not decorrelate subquery
This one leaves me utterly clueless, but I saw this specific query was mentioned by @Freeaqingme on #3288.
Thanks for your report!
I extracted the request about the correlated subquery into a separate issue #34776
On Elixir side of things, Ecto 3 actually specifies a precision of 0 for the following types time utc_datetime naive_datetime.
One can use one of time_usec utc_datetime_usec naive_datetime_usec as a workaround so that no precision is being set.
The issue though is there is one table that is created automatically by Ecto to track migration and that table uses naive_datetime so it triggers the following error:
** (Postgrex.Error) ERROR 0A000 (feature_not_supported) syntax error: unimplemented: unimplemented at or near ","
query: CREATE TABLE IF NOT EXISTS "schema_migrations" ("version" bigint, "inserted_at" timestamp(0), PRIMARY KEY ("version"))
hint: See: https://github.com/cockroachdb/cockroach/issues/32098
Thanks for your input @tlvenn - I was looking at scheduling this work for sometime soon, as it's been coming up a bunch lately.
Just in case someone from Elixir/Ecto lands on this issue, I have created an Ecto adaptor for CockroachDB to workaround this issue:
This fix has not made it into cockroachdb/cockroach:v19.1.6 / cockroachdb/cockroach:v2.1.10 or cockroachdb/cockroach:latest - all of which are producing the same error message :
** (Postgrex.Error) ERROR 0A000 (feature_not_supported) syntax error: unimplemented: unimplemented at or near ","
query: CREATE TABLE IF NOT EXISTS "schema_migrations" ("version" bigint, "inserted_at" timestamp(0), PRIMARY KEY ("version"))
hint: See: https://github.com/cockroachdb/cockroach/issues/32098
source SQL:
CREATE TABLE IF NOT EXISTS "schema_migrations" ("version" bigint, "inserted_at" timestamp(0), PRIMARY KEY ("version"))
^
(ecto_sql) lib/ecto/adapters/sql.ex:629: Ecto.Adapters.SQL.raise_sql_call_error/1
(elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2
(ecto_sql) lib/ecto/adapters/sql.ex:716: Ecto.Adapters.SQL.execute_ddl/4
(ecto_sql) lib/ecto/migrator.ex:633: Ecto.Migrator.verbose_schema_migration/3
(ecto_sql) lib/ecto/migrator.ex:477: Ecto.Migrator.lock_for_migrations/4
(ecto_sql) lib/ecto/migrator.ex:401: Ecto.Migrator.run/4
(ecto_sql) lib/ecto/migrator.ex:142: Ecto.Migrator.with_repo/3
(ecto_sql) lib/mix/tasks/ecto.migrate.ex:118: anonymous fn/5 in Mix.Tasks.Ecto.Migrate.run/2
(elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
(ecto_sql) lib/mix/tasks/ecto.migrate.ex:106: Mix.Tasks.Ecto.Migrate.run/2
(mix) lib/mix/task.ex:331: Mix.Task.run_task/3
(mix) lib/mix/task.ex:365: Mix.Task.run_alias/3
(mix) lib/mix/task.ex:292: Mix.Task.run/2
(mix) lib/mix/cli.ex:79: Mix.CLI.run_task/2
(elixir) lib/code.ex:813: Code.require_file/2
As a matter of process, we don't backport features to previous releases - only bug fixes and (for 2 releases back) security fixes.
This lack of support is not a bug.
Is there a released (docker) version with this fix included ?
I was told it would be included in 20.1 alpha back in december https://cockroachdb.slack.com/archives/CP4D9LD5F/p1575642161341500?thread_ts=1575633661.339600&cid=CP4D9LD5F
Here is the 20.1 alpha with the docker information https://www.cockroachlabs.com/docs/releases/v20.1.0-alpha20191216.html
Much appreciated @awoods187
@awoods187 - works like a charm with cockroachdb/cockroach-unstable:v20.1.0-alpha20191216
Just for the record - this fix is in cockroachdb/cockroach:v19.2.1
Just for the record - this fix is in cockroachdb/cockroach:v19.2.1
sorry to break the spirit but that...should not be the case :o
what commands are you running? afaict, we should only support timestamp(6) in there.
Because support for timestamp(0) was pulled, Craft CMS, a popular PHP CMS built with the Yii framework doesn't work with CockroachDB. I believe this also re-blocks use with Elixir's Ecto.
Could/should support be re-added in a way which avoids the issues that PR addresses? If so, should this issue be re-opened? Thanks!
We actually fixed this for 20.1, our upcoming release. You can see it in this pr #42580 or in a beta here https://www.cockroachlabs.com/docs/releases/v20.1.0-beta.2.html