Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: don't use the name with spaces timestamp with time zone in introspection tables #27601

Closed
knz opened this issue Jul 16, 2018 · 0 comments · Fixed by #28945
Closed

sql: don't use the name with spaces timestamp with time zone in introspection tables #27601

knz opened this issue Jul 16, 2018 · 0 comments · Fixed by #28945
Labels
A-sql-pgcompat Semantic compatibility with PostgreSQL C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.
Milestone
2.1

Comments

@knz
Copy link
Contributor

knz commented Jul 16, 2018

The pg_catalog tables and information_schema should print out timestamptz instead. Otherwise clients get confused.

cockroachdb/sqlalchemy-cockroachdb#60
@knz knz added C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. A-sql-pgcompat Semantic compatibility with PostgreSQL labels Jul 16, 2018
@knz knz added this to the 2.1 milestone Jul 23, 2018
This was referenced Aug 16, 2018
Merged
Merged
craig bot pushed a commit that referenced this issue Aug 23, 2018
@knz
Merge #28945
e41ba2e 
28945: sql: fix the reporting of types in information_schema.columns r=knz a=knz

First commits from #28944 and priors.
Forked off #28690.
Fixes #27601.
Largely addresses the concerns that led to issue #26925.

Prior to this patch, CockroachDB incorrectly placed the "input syntax"
of each SQL type in the column `data_type` of
`information_schema.columns`.

The input syntax is the one reported in SHOW COLUMNS, SHOW CREATE
TABLE and other places, and is suitable to reproduce the exact type of
at able.

In contrast, `information_schema.columns.data_type` is constrained by
compatibility with third party tools and PostgreSQL clients.  It must
report the name of the type like PostgreSQL does, which in turn is
constrained by the SQL standard. A text column must be reported as
"text" not "string"; a decimal column as "numeric" not "decimal", a
float8 column as "double precision" not "float8", and so on.

By reporting the wrong string in that column CockroachDB is confusing
ORMs, which subsequently decide that the current on-disk type is not
the one expected by the app and then initiate a schema change (ALTER
COLUMN SET TYPE).

This patch corrects this incompatibility by introducing logic that
produces the proper information schema names for column types. This is
expected to reduce ORM complaints about insufficient support for ALTER
COLUMN SET TYPE (but will be tested/evaluated separately).

Release note (bug fix): CockroachDB now populates the `data_type`
column of `information_schema.columns` like PostgreSQL for
compatibility with 3rd party tools and ORMs.

Co-authored-by: Raphael 'kena' Poss <[email protected]>
@craig craig bot closed this as completed in #28945 Aug 23, 2018
@annrpom annrpom mentioned this issue Sep 27, 2023
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
A-sql-pgcompat Semantic compatibility with PostgreSQL C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.
Projects
None yet
Milestone
2.1
Development

Successfully merging a pull request may close this issue.

sql: fix the reporting of types in information_schema.columns knz/cockroach
1 participant
@knz