granicus.if.org Git - postgresql/blob - src/test/regress/sql/fast_default.sql

   1 --
   2 -- ALTER TABLE ADD COLUMN DEFAULT test
   3 --
   4
   5 SET search_path = fast_default;
   6 CREATE SCHEMA fast_default;
   7 CREATE TABLE m(id OID);
   8 INSERT INTO m VALUES (NULL::OID);
   9
  10 CREATE FUNCTION set(tabname name) RETURNS VOID
  11 AS $$
  12 BEGIN
  13   UPDATE m
  14   SET id = (SELECT c.relfilenode
  15             FROM pg_class AS c, pg_namespace AS s
  16             WHERE c.relname = tabname
  17                 AND c.relnamespace = s.oid
  18                 AND s.nspname = 'fast_default');
  19 END;
  20 $$ LANGUAGE 'plpgsql';
  21
  22 CREATE FUNCTION comp() RETURNS TEXT
  23 AS $$
  24 BEGIN
  25   RETURN (SELECT CASE
  26                WHEN m.id = c.relfilenode THEN 'Unchanged'
  27                ELSE 'Rewritten'
  28                END
  29            FROM m, pg_class AS c, pg_namespace AS s
  30            WHERE c.relname = 't'
  31                AND c.relnamespace = s.oid
  32                AND s.nspname = 'fast_default');
  33 END;
  34 $$ LANGUAGE 'plpgsql';
  35
  36 CREATE FUNCTION log_rewrite() RETURNS event_trigger
  37 LANGUAGE plpgsql as
  38 $func$
  39
  40 declare
  41    this_schema text;
  42 begin
  43     select into this_schema relnamespace::regnamespace::text
  44     from pg_class
  45     where oid = pg_event_trigger_table_rewrite_oid();
  46     if this_schema = 'fast_default'
  47     then
  48         RAISE NOTICE 'rewriting table % for reason %',
  49           pg_event_trigger_table_rewrite_oid()::regclass,
  50           pg_event_trigger_table_rewrite_reason();
  51     end if;
  52 end;
  53 $func$;
  54
  55 CREATE TABLE has_volatile AS
  56 SELECT * FROM generate_series(1,10) id;
  57
  58
  59 CREATE EVENT TRIGGER has_volatile_rewrite
  60                   ON table_rewrite
  61    EXECUTE PROCEDURE log_rewrite();
  62
  63 -- only the last of these should trigger a rewrite
  64 ALTER TABLE has_volatile ADD col1 int;
  65 ALTER TABLE has_volatile ADD col2 int DEFAULT 1;
  66 ALTER TABLE has_volatile ADD col3 timestamptz DEFAULT current_timestamp;
  67 ALTER TABLE has_volatile ADD col4 int DEFAULT (random() * 10000)::int;
  68
  69
  70
  71 -- Test a large sample of different datatypes
  72 CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT 1);
  73
  74 SELECT set('t');
  75
  76 INSERT INTO T VALUES (1), (2);
  77
  78 ALTER TABLE T ADD COLUMN c_bpchar BPCHAR(5) DEFAULT 'hello',
  79               ALTER COLUMN c_int SET DEFAULT 2;
  80
  81 INSERT INTO T VALUES (3), (4);
  82
  83
  84 ALTER TABLE T ADD COLUMN c_text TEXT  DEFAULT 'world',
  85               ALTER COLUMN c_bpchar SET DEFAULT 'dog';
  86
  87 INSERT INTO T VALUES (5), (6);
  88
  89 ALTER TABLE T ADD COLUMN c_date DATE DEFAULT '2016-06-02',
  90               ALTER COLUMN c_text SET DEFAULT 'cat';
  91
  92 INSERT INTO T VALUES (7), (8);
  93
  94 ALTER TABLE T ADD COLUMN c_timestamp TIMESTAMP DEFAULT '2016-09-01 12:00:00',
  95               ADD COLUMN c_timestamp_null TIMESTAMP,
  96               ALTER COLUMN c_date SET DEFAULT '2010-01-01';
  97
  98 INSERT INTO T VALUES (9), (10);
  99
 100 ALTER TABLE T ADD COLUMN c_array TEXT[]
 101                   DEFAULT '{"This", "is", "the", "real", "world"}',
 102               ALTER COLUMN c_timestamp SET DEFAULT '1970-12-31 11:12:13',
 103               ALTER COLUMN c_timestamp_null SET DEFAULT '2016-09-29 12:00:00';
 104
 105 INSERT INTO T VALUES (11), (12);
 106
 107 ALTER TABLE T ADD COLUMN c_small SMALLINT DEFAULT -5,
 108               ADD COLUMN c_small_null SMALLINT,
 109               ALTER COLUMN c_array
 110                   SET DEFAULT '{"This", "is", "no", "fantasy"}';
 111
 112 INSERT INTO T VALUES (13), (14);
 113
 114 ALTER TABLE T ADD COLUMN c_big BIGINT DEFAULT 180000000000018,
 115               ALTER COLUMN c_small SET DEFAULT 9,
 116               ALTER COLUMN c_small_null SET DEFAULT 13;
 117
 118 INSERT INTO T VALUES (15), (16);
 119
 120 ALTER TABLE T ADD COLUMN c_num NUMERIC DEFAULT 1.00000000001,
 121               ALTER COLUMN c_big SET DEFAULT -9999999999999999;
 122
 123 INSERT INTO T VALUES (17), (18);
 124
 125 ALTER TABLE T ADD COLUMN c_time TIME DEFAULT '12:00:00',
 126               ALTER COLUMN c_num SET DEFAULT 2.000000000000002;
 127
 128 INSERT INTO T VALUES (19), (20);
 129
 130 ALTER TABLE T ADD COLUMN c_interval INTERVAL DEFAULT '1 day',
 131               ALTER COLUMN c_time SET DEFAULT '23:59:59';
 132
 133 INSERT INTO T VALUES (21), (22);
 134
 135 ALTER TABLE T ADD COLUMN c_hugetext TEXT DEFAULT repeat('abcdefg',1000),
 136               ALTER COLUMN c_interval SET DEFAULT '3 hours';
 137
 138 INSERT INTO T VALUES (23), (24);
 139
 140 ALTER TABLE T ALTER COLUMN c_interval DROP DEFAULT,
 141               ALTER COLUMN c_hugetext SET DEFAULT repeat('poiuyt', 1000);
 142
 143 INSERT INTO T VALUES (25), (26);
 144
 145 ALTER TABLE T ALTER COLUMN c_bpchar    DROP DEFAULT,
 146               ALTER COLUMN c_date      DROP DEFAULT,
 147               ALTER COLUMN c_text      DROP DEFAULT,
 148               ALTER COLUMN c_timestamp DROP DEFAULT,
 149               ALTER COLUMN c_array     DROP DEFAULT,
 150               ALTER COLUMN c_small     DROP DEFAULT,
 151               ALTER COLUMN c_big       DROP DEFAULT,
 152               ALTER COLUMN c_num       DROP DEFAULT,
 153               ALTER COLUMN c_time      DROP DEFAULT,
 154               ALTER COLUMN c_hugetext  DROP DEFAULT;
 155
 156 INSERT INTO T VALUES (27), (28);
 157
 158 SELECT pk, c_int, c_bpchar, c_text, c_date, c_timestamp,
 159        c_timestamp_null, c_array, c_small, c_small_null,
 160        c_big, c_num, c_time, c_interval,
 161        c_hugetext = repeat('abcdefg',1000) as c_hugetext_origdef,
 162        c_hugetext = repeat('poiuyt', 1000) as c_hugetext_newdef
 163 FROM T ORDER BY pk;
 164
 165 SELECT comp();
 166
 167 DROP TABLE T;
 168
 169 -- Test expressions in the defaults
 170 CREATE OR REPLACE FUNCTION foo(a INT) RETURNS TEXT AS $$
 171 DECLARE res TEXT := '';
 172         i INT;
 173 BEGIN
 174   i := 0;
 175   WHILE (i < a) LOOP
 176     res := res || chr(ascii('a') + i);
 177     i := i + 1;
 178   END LOOP;
 179   RETURN res;
 180 END; $$ LANGUAGE PLPGSQL STABLE;
 181
 182 CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT LENGTH(foo(6)));
 183
 184 SELECT set('t');
 185
 186 INSERT INTO T VALUES (1), (2);
 187
 188 ALTER TABLE T ADD COLUMN c_bpchar BPCHAR(5) DEFAULT foo(4),
 189               ALTER COLUMN c_int SET DEFAULT LENGTH(foo(8));
 190
 191 INSERT INTO T VALUES (3), (4);
 192
 193 ALTER TABLE T ADD COLUMN c_text TEXT  DEFAULT foo(6),
 194               ALTER COLUMN c_bpchar SET DEFAULT foo(3);
 195
 196 INSERT INTO T VALUES (5), (6);
 197
 198 ALTER TABLE T ADD COLUMN c_date DATE
 199                   DEFAULT '2016-06-02'::DATE  + LENGTH(foo(10)),
 200               ALTER COLUMN c_text SET DEFAULT foo(12);
 201
 202 INSERT INTO T VALUES (7), (8);
 203
 204 ALTER TABLE T ADD COLUMN c_timestamp TIMESTAMP
 205                   DEFAULT '2016-09-01'::DATE + LENGTH(foo(10)),
 206               ALTER COLUMN c_date
 207                   SET DEFAULT '2010-01-01'::DATE - LENGTH(foo(4));
 208
 209 INSERT INTO T VALUES (9), (10);
 210
 211 ALTER TABLE T ADD COLUMN c_array TEXT[]
 212                   DEFAULT ('{"This", "is", "' || foo(4) ||
 213                            '","the", "real", "world"}')::TEXT[],
 214               ALTER COLUMN c_timestamp
 215                   SET DEFAULT '1970-12-31'::DATE + LENGTH(foo(30));
 216
 217 INSERT INTO T VALUES (11), (12);
 218
 219 ALTER TABLE T ALTER COLUMN c_int DROP DEFAULT,
 220               ALTER COLUMN c_array
 221                   SET DEFAULT ('{"This", "is", "' || foo(1) ||
 222                                '", "fantasy"}')::text[];
 223
 224 INSERT INTO T VALUES (13), (14);
 225
 226 ALTER TABLE T ALTER COLUMN c_bpchar    DROP DEFAULT,
 227               ALTER COLUMN c_date      DROP DEFAULT,
 228               ALTER COLUMN c_text      DROP DEFAULT,
 229               ALTER COLUMN c_timestamp DROP DEFAULT,
 230               ALTER COLUMN c_array     DROP DEFAULT;
 231
 232 INSERT INTO T VALUES (15), (16);
 233
 234 SELECT * FROM T;
 235
 236 SELECT comp();
 237
 238 DROP TABLE T;
 239
 240 DROP FUNCTION foo(INT);
 241
 242 -- Fall back to full rewrite for volatile expressions
 243 CREATE TABLE T(pk INT NOT NULL PRIMARY KEY);
 244
 245 INSERT INTO T VALUES (1);
 246
 247 SELECT set('t');
 248
 249 -- now() is stable, because it returns the transaction timestamp
 250 ALTER TABLE T ADD COLUMN c1 TIMESTAMP DEFAULT now();
 251
 252 SELECT comp();
 253
 254 -- clock_timestamp() is volatile
 255 ALTER TABLE T ADD COLUMN c2 TIMESTAMP DEFAULT clock_timestamp();
 256
 257 SELECT comp();
 258
 259 DROP TABLE T;
 260
 261 -- Simple querie
 262 CREATE TABLE T (pk INT NOT NULL PRIMARY KEY);
 263
 264 SELECT set('t');
 265
 266 INSERT INTO T SELECT * FROM generate_series(1, 10) a;
 267
 268 ALTER TABLE T ADD COLUMN c_bigint BIGINT NOT NULL DEFAULT -1;
 269
 270 INSERT INTO T SELECT b, b - 10 FROM generate_series(11, 20) a(b);
 271
 272 ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'hello';
 273
 274 INSERT INTO T SELECT b, b - 10, (b + 10)::text FROM generate_series(21, 30) a(b);
 275
 276 -- WHERE clause
 277 SELECT c_bigint, c_text FROM T WHERE c_bigint = -1 LIMIT 1;
 278
 279 EXPLAIN (VERBOSE TRUE, COSTS FALSE)
 280 SELECT c_bigint, c_text FROM T WHERE c_bigint = -1 LIMIT 1;
 281
 282 SELECT c_bigint, c_text FROM T WHERE c_text = 'hello' LIMIT 1;
 283
 284 EXPLAIN (VERBOSE TRUE, COSTS FALSE) SELECT c_bigint, c_text FROM T WHERE c_text = 'hello' LIMIT 1;
 285
 286
 287 -- COALESCE
 288 SELECT COALESCE(c_bigint, pk), COALESCE(c_text, pk::text)
 289 FROM T
 290 ORDER BY pk LIMIT 10;
 291
 292 -- Aggregate function
 293 SELECT SUM(c_bigint), MAX(c_text COLLATE "C" ), MIN(c_text COLLATE "C") FROM T;
 294
 295 -- ORDER BY
 296 SELECT * FROM T ORDER BY c_bigint, c_text, pk LIMIT 10;
 297
 298 EXPLAIN (VERBOSE TRUE, COSTS FALSE)
 299 SELECT * FROM T ORDER BY c_bigint, c_text, pk LIMIT 10;
 300
 301 -- LIMIT
 302 SELECT * FROM T WHERE c_bigint > -1 ORDER BY c_bigint, c_text, pk LIMIT 10;
 303
 304 EXPLAIN (VERBOSE TRUE, COSTS FALSE)
 305 SELECT * FROM T WHERE c_bigint > -1 ORDER BY c_bigint, c_text, pk LIMIT 10;
 306
 307 --  DELETE with RETURNING
 308 DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *;
 309 EXPLAIN (VERBOSE TRUE, COSTS FALSE)
 310 DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *;
 311
 312 -- UPDATE
 313 UPDATE T SET c_text = '"' || c_text || '"'  WHERE pk < 10;
 314 SELECT * FROM T WHERE c_text LIKE '"%"' ORDER BY PK;
 315
 316 SELECT comp();
 317
 318 DROP TABLE T;
 319
 320
 321 -- Combine with other DDL
 322 CREATE TABLE T(pk INT NOT NULL PRIMARY KEY);
 323
 324 SELECT set('t');
 325
 326 INSERT INTO T VALUES (1), (2);
 327
 328 ALTER TABLE T ADD COLUMN c_int INT NOT NULL DEFAULT -1;
 329
 330 INSERT INTO T VALUES (3), (4);
 331
 332 ALTER TABLE T ADD COLUMN c_text TEXT DEFAULT 'Hello';
 333
 334 INSERT INTO T VALUES (5), (6);
 335
 336 ALTER TABLE T ALTER COLUMN c_text SET DEFAULT 'world',
 337               ALTER COLUMN c_int  SET DEFAULT 1;
 338
 339 INSERT INTO T VALUES (7), (8);
 340
 341 SELECT * FROM T ORDER BY pk;
 342
 343 -- Add an index
 344 CREATE INDEX i ON T(c_int, c_text);
 345
 346 SELECT c_text FROM T WHERE c_int = -1;
 347
 348 SELECT comp();
 349
 350 -- query to exercise expand_tuple function
 351 CREATE TABLE t1 AS
 352 SELECT 1::int AS a , 2::int AS b
 353 FROM generate_series(1,20) q;
 354
 355 ALTER TABLE t1 ADD COLUMN c text;
 356
 357 SELECT a,
 358        stddev(cast((SELECT sum(1) FROM generate_series(1,20) x) AS float4))
 359           OVER (PARTITION BY a,b,c ORDER BY b)
 360        AS z
 361 FROM t1;
 362
 363 DROP TABLE T;
 364
 365 -- test that we account for missing columns without defaults correctly
 366 -- in expand_tuple, and that rows are correctly expanded for triggers
 367
 368 CREATE FUNCTION test_trigger()
 369 RETURNS trigger
 370 LANGUAGE plpgsql
 371 AS $$
 372
 373 begin
 374     raise notice 'old tuple: %', to_json(OLD)::text;
 375     if TG_OP = 'DELETE'
 376     then
 377        return OLD;
 378     else
 379        return NEW;
 380     end if;
 381 end;
 382
 383 $$;
 384
 385 -- 2 new columns, both have defaults
 386 CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
 387 INSERT INTO t (a,b,c) VALUES (1,2,3);
 388 ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
 389 ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
 390 CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
 391 SELECT * FROM t;
 392 UPDATE t SET y = 2;
 393 SELECT * FROM t;
 394 DROP TABLE t;
 395
 396 -- 2 new columns, first has default
 397 CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
 398 INSERT INTO t (a,b,c) VALUES (1,2,3);
 399 ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
 400 ALTER TABLE t ADD COLUMN y int;
 401 CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
 402 SELECT * FROM t;
 403 UPDATE t SET y = 2;
 404 SELECT * FROM t;
 405 DROP TABLE t;
 406
 407 -- 2 new columns, second has default
 408 CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
 409 INSERT INTO t (a,b,c) VALUES (1,2,3);
 410 ALTER TABLE t ADD COLUMN x int;
 411 ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
 412 CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
 413 SELECT * FROM t;
 414 UPDATE t SET y = 2;
 415 SELECT * FROM t;
 416 DROP TABLE t;
 417
 418 -- 2 new columns, neither has default
 419 CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
 420 INSERT INTO t (a,b,c) VALUES (1,2,3);
 421 ALTER TABLE t ADD COLUMN x int;
 422 ALTER TABLE t ADD COLUMN y int;
 423 CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
 424 SELECT * FROM t;
 425 UPDATE t SET y = 2;
 426 SELECT * FROM t;
 427 DROP TABLE t;
 428
 429 -- same as last 4 tests but here the last original column has a NULL value
 430 -- 2 new columns, both have defaults
 431 CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
 432 INSERT INTO t (a,b,c) VALUES (1,2,NULL);
 433 ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
 434 ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
 435 CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
 436 SELECT * FROM t;
 437 UPDATE t SET y = 2;
 438 SELECT * FROM t;
 439 DROP TABLE t;
 440
 441 -- 2 new columns, first has default
 442 CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
 443 INSERT INTO t (a,b,c) VALUES (1,2,NULL);
 444 ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
 445 ALTER TABLE t ADD COLUMN y int;
 446 CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
 447 SELECT * FROM t;
 448 UPDATE t SET y = 2;
 449 SELECT * FROM t;
 450 DROP TABLE t;
 451
 452 -- 2 new columns, second has default
 453 CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
 454 INSERT INTO t (a,b,c) VALUES (1,2,NULL);
 455 ALTER TABLE t ADD COLUMN x int;
 456 ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
 457 CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
 458 SELECT * FROM t;
 459 UPDATE t SET y = 2;
 460 SELECT * FROM t;
 461 DROP TABLE t;
 462
 463 -- 2 new columns, neither has default
 464 CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
 465 INSERT INTO t (a,b,c) VALUES (1,2,NULL);
 466 ALTER TABLE t ADD COLUMN x int;
 467 ALTER TABLE t ADD COLUMN y int;
 468 CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
 469 SELECT * FROM t;
 470 UPDATE t SET y = 2;
 471 SELECT * FROM t;
 472 DROP TABLE t;
 473
 474 -- make sure expanded tuple has correct self pointer
 475 -- it will be required by the RI trigger doing the cascading delete
 476
 477 CREATE TABLE leader (a int PRIMARY KEY, b int);
 478 CREATE TABLE follower (a int REFERENCES leader ON DELETE CASCADE, b int);
 479 INSERT INTO leader VALUES (1, 1), (2, 2);
 480 ALTER TABLE leader ADD c int;
 481 ALTER TABLE leader DROP c;
 482 DELETE FROM leader;
 483
 484 -- check that ALTER TABLE ... ALTER TYPE does the right thing
 485
 486 CREATE TABLE vtype( a integer);
 487 INSERT INTO vtype VALUES (1);
 488 ALTER TABLE vtype ADD COLUMN b DOUBLE PRECISION DEFAULT 0.2;
 489 ALTER TABLE vtype ADD COLUMN c BOOLEAN DEFAULT true;
 490 SELECT * FROM vtype;
 491 ALTER TABLE vtype
 492       ALTER b TYPE text USING b::text,
 493       ALTER c TYPE text USING c::text;
 494 SELECT * FROM vtype;
 495
 496 -- also check the case that doesn't rewrite the table
 497
 498 CREATE TABLE vtype2 (a int);
 499 INSERT INTO vtype2 VALUES (1);
 500 ALTER TABLE vtype2 ADD COLUMN b varchar(10) DEFAULT 'xxx';
 501 ALTER TABLE vtype2 ALTER COLUMN b SET DEFAULT 'yyy';
 502 INSERT INTO vtype2 VALUES (2);
 503
 504 ALTER TABLE vtype2 ALTER COLUMN b TYPE varchar(20) USING b::varchar(20);
 505 SELECT * FROM vtype2;
 506
 507
 508 -- cleanup
 509 DROP TABLE vtype;
 510 DROP TABLE vtype2;
 511 DROP TABLE follower;
 512 DROP TABLE leader;
 513 DROP FUNCTION test_trigger();
 514 DROP TABLE t1;
 515 DROP FUNCTION set(name);
 516 DROP FUNCTION comp();
 517 DROP TABLE m;
 518 DROP TABLE has_volatile;
 519 DROP EVENT TRIGGER has_volatile_rewrite;
 520 DROP FUNCTION log_rewrite;
 521 DROP SCHEMA fast_default;
 522
 523 -- Leave a table with an active fast default in place, for pg_upgrade testing
 524 set search_path = public;
 525 create table has_fast_default(f1 int);
 526 insert into has_fast_default values(1);
 527 alter table has_fast_default add column f2 int default 42;
 528 table has_fast_default;