[YSQL] Feature Support - ALTER TYPE #1893
Labels
area/ysql
Yugabyte SQL (YSQL)
kind/enhancement
This is an enhancement of an existing feature
pgcm
priority/medium
Medium priority issue
Comments
jaki
pushed a commit
to jaki/yugabyte-db
that referenced
this issue
Aug 22, 2019
Change the `ALTER TYPE` "not supported" error message to point to the new issue yugabyte#1893 instead of the old issue yugabyte#1152.
jaki
pushed a commit
that referenced
this issue
Aug 26, 2019
Summary: Enable `CREATE TYPE` and `DROP TYPE` for user-defined types, closing issue #1152. This includes user-defined composite types, enum types, range types, base types, and shell types. Also extend support to record types, particularly those returning from functions, and close issues #742 and #1676. * Defer `ALTER TYPE` to issue #1893 * Map Postgres user-defined types either to existing Postgres base types or directly to Yugabyte types * Apply fixes related to issue #1156 in regress test `yb_pg_plpgsql` (formerly named `yb_plpgsql`) * Invalidate some `t_ybctid` attributes of `HeapTuple`s to get records working * Discover and create a potentially related issue #1975 * Update several Postgres regress tests * Create new Postgres regress tests * Rename related tests from `yb_foo` to `yb_pg_foo` if they are derivatives of the vanilla Postgres `foo` test * Port related tests from `foo` to `yb_pg_foo` by copying then modifying * Fix an issue with the expected output of `TestPgDump` that is **not** related to UDTs and records Assuming it still exists, see fine-grained commit history at jj-kim/yugabyte-db/tree/support-user-defined-type. Otherwise, here are the commit message subjects (most recent to least recent). * Comment out more of an unrelated issue * Add more DISCARD TEMP just in case * Add DISCARD TEMP to the end of some tests * Set UDT type entity OIDs to invalid * Combine some DROP TABLE statements * Fix yb_pg_foreign_key expected output from 0294a9c * Rename schedule collections to arrays * Add yb_pg_rowtypes regress test * Order tests in schedules; split yb_create_index * Dynamically create type entities for certain UDTs * Improve base type test with custom C func type * Add base type test and handle more base type cases * Increase fixed length type entity size to 8 bytes * Reduce large diffs for yb_pg_jsonb test * Add testjsonb table for yb_pg_jsonb test * Reduce diff for yb_pg_jsonb Postgres regress tests * Rename more Postgres regress tests * Port case regress test to yb_pg_case * Add missing feature tests to yb_feature_types * Fix TestPgDump expected output discrepancy * Uncomment/fix more statements in yb_arrays test * Fix TestPgRegressBetaFeatures java test from tenk1 * Fix TestPgRegressPgMisc java test due to tenk1 * Remove WITH OIDS when creating tenk1 table * Finish cleaning off yb_privileges test * Resolve part of yb_privileges regress test * Remap UDTs without mucking around sys attributes * Increase timeout of TestPgRegressLargeTable * Uncomment TODOs in yb_arrays test * Remove TestPgTypes java test * Uncomment more statements in yb_pg_json test * Uncomment some statements in yb_pg_json test * Remove intermediate UDT mapping for direct mapping * Uncomment TODOs in yb_pg_enum regress test * Update utils to have new UDTOIDs * Add small test for record types * Change function name in TestPgRegressTypesUDT.java * Add test for creating and dropping types * Rename several regress tests * Redirect ALTER TYPE to issue #1893 * Create custom primitives for UDTs * Fix and address #1676 comments in yb_plpgsql * Address last half of #1156 comments in yb_plpgsql * Address first half of #1156 comments in yb_plpgsql * Update yb_plpgsql test expected output * Support functions returning record type * Create new debug utility YBCPgDataTypeToStr * Don't error on domain type * Update some existing yb_foo regress tests * Add TODO comment for BYTEAARRAY to BYTEA * Add tests for creating enums and ranges * Rename yb_enum to yb_pg_enum * Copy over CREATE TYPE tests * Support user-defined enum and base types * Remove IsYugaByteEnabled check for tup init * Set base type of user-defined types to BYTEAARRAY * Enable CREATE TYPE and DROP TYPE Test Plan: * `TestPgRegressArrays` java test, particularly `yb_pg_arrays` regress test * `TestPgRegressAuthorization` java test, particularly `yb_pg_privileges` regress test * `TestPgRegressBetaFeatures` java test, particularly `yb_pg_plpgsql`, `yb_plpgsql` regress tests * `TestPgRegressFeature` java test, particularly `yb_feature_types` regress test * `TestPgRegressPgMisc` java test, particularly `yb_pg_case`, `yb_pg_identity` regress tests * `TestPgRegressTypesString` java test, particularly `yb_pg_json`, `yb_pg_jsonb` regress tests * `TestPgRegressTypesUDT` java test, particularly `yb_base_type`, `yb_create_type`, `yb_pg_create_type`, `yb_pg_enum`, `yb_pg_rangetypes`, `yb_pg_rowtypes` Unrelated tests that got added or changed: * `yb_create_function` regress test, involving `TestPgRegressTypesUDT` java test * `yb_create_index`, `yb_pg_create_index` regress tests, involving `TestPgRegressMisc`, `TestPgRegressTypesUDT` java tests * `yb_create_table`, `yb_drop_table` regress tests, involving `TestPgRegressTable` java test * `yb_feature_temp` regress test, involving `TestPgRegressFeature` java test * `yb_pg_copy`, `yb_pg_create_table` regress tests, involving `TestPgRegressArrays`, `TestPgRegressBetaFeatures`, `TestPgRegressMisc`, `TestPgRegressPgMisc`, `TestPgRegressTypesString`, `TestPgRegressTypesUDT` java tests * `yb_pg_foreign_key` regress test, involving `TestPgForeignKey` java test * `yb_pg_int8` regress test, involving `TestPgRegressAuthorization`, `TestPgRegressTypesNumeric`, `TestPgRegressTypesUDT` java tests * `yb_pg_triggers` regress test, involving `TestPgRegressBetaFeatures` java test * `yb_select`, `yb_sequence` regress tests, involving `TestPgRegressPgMisc` java test * `yb_triggers` regress test, involving `TestPgRegressBetaFeatures` java test Reviewers: mihnea, neha Reviewed By: neha Subscribers: neil, kannan, yql Differential Revision: https://phabricator.dev.yugabyte.com/D6900
myang2021
added a commit
that referenced
this issue
May 27, 2021
Summary:
Currently in YSQL enum type only has limited support. The following examples
illustrate the restrictions:
```
yugabyte=# create type e_t as enum('a', 'b');
create type e_t as enum('a', 'b');
CREATE TYPE
yugabyte=# create table foo(id e_t primary key);
create table foo(id e_t primary key);
ERROR: PRIMARY KEY containing column of type 'user_defined_type' not yet supported
yugabyte=# create table bar(id e_t);
create table bar(id e_t);
CREATE TABLE
yugabyte=# create index bar_id_idx on bar(id);
create index bar_id_idx on bar(id);
ERROR: INDEX on column of type 'user_defined_type' not yet supported
yugabyte=# alter type e_t add value 'c';
alter type e_t add value 'c';
ERROR: ALTER TYPE not supported yet
LINE 1: alter type e_t add value 'c';
^
HINT: See #1893. Click '+' on the description to raise its priority
```
Multiple customers have requested better enum support to allow enum primary
key and index on enum columns.
This diff aims to get the same level of enum support as in postgres with one
exception of the so called "label renumbering". Postgres internally assign
each enum element a unique oid, plus a 4-byte binary float number that is used
to represent the sort order of the label. Postgres also allows adding new labels
before or after an existing label. If one keeps adding new labels before existing
labels, then at some point postgres will not be able to find a float number that
can maintain the sort order of all labels. At that time postgres goes through
a "label renumbering" process to throw away the old set of sorted floats, assign
a new set of sorted floats (initialized to be a new set of consecutive integers
starting from 1). YSQL does not support "label renumbering" because of docdb
implemention restriction.
The changes are:
(1) Remove enum related restrictions from gram.y
(2) Each enum element is stored as an 8-byte int64, which is composed of a 4-byte sort float plus a 4-byte oid of the enum element. Having the 4-byte oid of the enum element makes it possible in the future to use a custom comparator function which can ignore the 4-byte sort order and only do proper sort on the enum element.
(3) In pggate, when we compose PgConstant (which is where we are about to send the enum value to docdb), we build the above int64 combo.
(4) The int64 combo is built in a way that is byte-sortable, so docdb can support index and primary key of enum type.
(5) The existing YB enum test is expanded to include more from the orignal enum regression test. Compared with regular postgres test output, the only differences are
(5.1) "label renumbering"
(5.2) cannot add new values to existing enums in a transaction
(6) A new enum index backfill test is added to ensure pre-existing enum column data that did not have sort order can work properly after database upgrade. Pre-existing enum data are written under a test only gflag --TEST_do_not_add_enum_sort_order.
(7) Known issue: creating an enum index during upgrade (when some nodes are new and others are old) will not work because old postgres will not add sort order when creating the index. This needs to be added to the release notes.
Test Plan:
ybd debug --verbose --java-test org.yb.pgsql.TestPgRegressTypesUDT
ybd debug --cxx-test pgwrapper_pg_libpq-test --gtest_filter PgLibPqTest.EnumType
Reviewers: mbautin, mihnea, neil, jason
Reviewed By: neil, jason
Subscribers: yql
Differential Revision: https://phabricator.dev.yugabyte.com/D11379
myang2021
added a commit
that referenced
this issue
Jul 15, 2021
Summary:
Currently in YSQL enum type only has limited support. The following examples
illustrate the restrictions:
```
yugabyte=# create type e_t as enum('a', 'b');
create type e_t as enum('a', 'b');
CREATE TYPE
yugabyte=# create table foo(id e_t primary key);
create table foo(id e_t primary key);
ERROR: PRIMARY KEY containing column of type 'user_defined_type' not yet supported
yugabyte=# create table bar(id e_t);
create table bar(id e_t);
CREATE TABLE
yugabyte=# create index bar_id_idx on bar(id);
create index bar_id_idx on bar(id);
ERROR: INDEX on column of type 'user_defined_type' not yet supported
yugabyte=# alter type e_t add value 'c';
alter type e_t add value 'c';
ERROR: ALTER TYPE not supported yet
LINE 1: alter type e_t add value 'c';
^
HINT: See #1893. Click '+' on the description to raise its priority
```
Multiple customers have requested better enum support to allow enum primary
key and index on enum columns.
This diff aims to get the same level of enum support as in postgres with one
exception of the so called "label renumbering". Postgres internally assign
each enum element a unique oid, plus a 4-byte binary float number that is used
to represent the sort order of the label. Postgres also allows adding new labels
before or after an existing label. If one keeps adding new labels before existing
labels, then at some point postgres will not be able to find a float number that
can maintain the sort order of all labels. At that time postgres goes through
a "label renumbering" process to throw away the old set of sorted floats, assign
a new set of sorted floats (initialized to be a new set of consecutive integers
starting from 1). YSQL does not support "label renumbering" because of docdb
implemention restriction.
The changes are:
(1) Remove enum related restrictions from gram.y
(2) Each enum element is stored as an 8-byte int64, which is composed of a 4-byte sort float plus a 4-byte oid of the enum element. Having the 4-byte oid of the enum element makes it possible in the future to use a custom comparator function which can ignore the 4-byte sort order and only do proper sort on the enum element.
(3) In pggate, when we compose PgConstant (which is where we are about to send the enum value to docdb), we build the above int64 combo.
(4) The int64 combo is built in a way that is byte-sortable, so docdb can support index and primary key of enum type.
(5) The existing YB enum test is expanded to include more from the orignal enum regression test. Compared with regular postgres test output, the only differences are
(5.1) "label renumbering"
(5.2) cannot add new values to existing enums in a transaction
(6) A new enum index backfill test is added to ensure pre-existing enum column data that did not have sort order can work properly after database upgrade. Pre-existing enum data are written under a test only gflag --TEST_do_not_add_enum_sort_order.
(7) Known issue: creating an enum index during upgrade (when some nodes are new and others are old) will not work because old postgres will not add sort order when creating the index. This needs to be added to the release notes.
Original commit: D11379 / 131ea34
Test Plan: Jenkins: rebase: 2.6
Reviewers: mbautin, jason, mihnea, neil
Reviewed By: neil
Subscribers: yql
Differential Revision: https://phabricator.dev.yugabyte.com/D12186
yugabyte-ci
added
kind/bug
yugabyte-ci
added
kind/enhancement
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Jira Link: DB-1585
This issue is tracking
ALTER TYPEsupport. (Related issue: #1152).The text was updated successfully, but these errors were encountered: