[YSQL] Allow ALTER TABLE which requires table rewrite

[d-uspenskiy]

Jira Link: DB-2919

Description

Some ALTER TABLE commands require table rewrite. This could be done by using same approach like for ALTER TABLE ... ADD PRIMARY KEY implemented in #1104 But this could be done only after fixing the #13274

[ddorian]

Maybe we can allow it for empty tables? This may fix some cases where users are running schema migrations with an empty db.

[bllewell]

alter table... add column... works fine when the data type is a base type like int, numeric, or text. But it fails when the data type is a domain.

What is this mysterious "Rewriting of YB table"? And what's so special about adding a column whose data type is a domain? Try this test:

\set VERBOSITY verbose

drop schema if exists s cascade;
create schema s;
create domain s.d1 as numeric(4, 2) constraint val_ok check(value > 0.0 and value <= 10.0);
create table s.t(k int primary key, c1 s.d1);
alter table s.t add column c2 numeric;
create domain s.d2 as int constraint val_ok check(value > 0 and value <= 20);

So far so good. Now do this:

alter table s.t add column c3 s.d2;

It causes this error:

ERROR:  0A000: Rewriting of YB table is not yet implemented
HINT:  See https://github.com/yugabyte/yugabyte-db/issues/13278. React with thumbs up to raise its priority
LOCATION:  ATRewriteTables, tablecmds.c:4760

The domain notion is a key part of relational modeling theory and correspondingly database design. Its raison d'être is to let you maintain a single point of definition (given that you might want to change the definition) for the data types (and constraints) for table columns, for subprogram formal arguments, for local variables in PL/pgSQL source text, and so on. (Notice that the %type notation for inheriting the data type (but not the constraints) from a table column for subprogram formal arguments doesn't deliver its promise because it's translated to its actual meaning at subprogram create or replace time and is then "frozen" in _pgproc so that it does not track table changes. Anyway, a domain has wider applicability than %type. For example:

• The data type(s) of the column(s) on which a foreign key constraint is defined must match the data type(s) of the column(s) on which the primary key constraint is defined in the referenced table.
• A numeric quantity like the route distance between two points on the Earth's surface will ideally be defined in the same way (for example, must be expressed in kilometers with three decimal digits of precision, must not be negative, must not be greater than, say, the Earth's circumference) and might appear in several different tables for different kinds of routes.

It's hugely frustrating to find that the value of the domain notion is defeated in YSQL..

[bllewell]

Notice that the problem that I reported HERE doesn't occur when the domain that defines the data type of the to-be-added column does not define a constraint on the domain value. Try this:

drop schema if exists s cascade;
create schema s;
create domain s.d1 as int constraint val_ok check(value > 0 and value <= 10);
create table s.t(k int primary key, c1 s.d1);
with g(v) as (select generate_series(1, 5))
insert into s.t(k, c1)
select g.v, (g.v)*2 from g;
alter table s.t add column c2 numeric(4, 2);
create domain s.d2 as numeric(4, 2);

alter table s.t add column c3 s.d2;
update s.t set c3 = c1;
alter table s.t add constraint t_c3_in_1_10 check(c3 > 0 and c3 <= 10);

It completes without error.

[inpos]

Same here with UUID:

=# alter table offers add column url_id uuid not null default gen_random_uuid();
ERROR:  Rewriting of YB table is not yet implemented
ПОДСКАЗКА:  See https://github.com/yugabyte/yugabyte-db/issues/13278. React with thumbs up to raise its priority

[ddorian]

@inpos what version are you using?

[inpos]

@inpos what version are you using?

=# show server_version;
   server_version    
---------------------
 11.2-YB-2.20.1.1-b0

[ddorian]

@inpos looks like it's failing because of the dynamic default value.

@kai-franz do we have an issue for alter table failing when the default value is a function, like:

test=# create table offers (id bigserial primary key);
test=# insert into offers(id) values (0);
test=# CREATE EXTENSION IF NOT EXISTS pgcrypto;
test=# alter table offers add column url_id uuid not null default gen_random_uuid();
ERROR:  Rewriting of YB table is not yet implemented
HINT:  See https://github.com/yugabyte/yugabyte-db/issues/13278. React with thumbs up to raise its priority
test=# alter table offers add column url_id22 DOUBLE PRECISION not null default random();
ERROR:  Rewriting of YB table is not yet implemented
HINT:  See https://github.com/yugabyte/yugabyte-db/issues/13278. React with thumbs up to raise its priority
test=# alter table offers add column url_id22 DOUBLE PRECISION not null default 0.77;
ALTER TABLE

[inpos]

@inpos looks like it's failing because of the dynamic default value.

I think you are right. But PostgreSQL allows default from function. And I thought it would work here too

[fizaaluthra]

hey @inpos, this issue (add column with volatile default values) was recently addressed and should be available in the next stable release. It is also available in 2.21 2.21.0.0-b430 onwards.

[ddorian]

@fizaaluthra can you link to the commit or issue?

[fizaaluthra]

@ddorian #19563, commit 2fb7ea649683574f7d6a62edaf0318166d5eb5a1

[ddorian]

@fizaaluthra I didn't see a backport so the first stable version to include it would be 2.22, correct?

[fizaaluthra]

@ddorian Yes, 2.22.