1use strict; 2use warnings; 3 4use Config; 5use PostgresNode; 6use TestLib; 7use Test::More; 8 9my $tempdir = TestLib::tempdir; 10my $tempdir_short = TestLib::tempdir_short; 11 12############################################################### 13# Definition of the pg_dump runs to make. 14# 15# Each of these runs are named and those names are used below 16# to define how each test should (or shouldn't) treat a result 17# from a given run. 18# 19# test_key indicates that a given run should simply use the same 20# set of like/unlike tests as another run, and which run that is. 21# 22# dump_cmd is the pg_dump command to run, which is an array of 23# the full command and arguments to run. Note that this is run 24# using $node->command_ok(), so the port does not need to be 25# specified and is pulled from $PGPORT, which is set by the 26# PostgresNode system. 27# 28# restore_cmd is the pg_restore command to run, if any. Note 29# that this should generally be used when the pg_dump goes to 30# a non-text file and that the restore can then be used to 31# generate a text file to run through the tests from the 32# non-text file generated by pg_dump. 33# 34# TODO: Have pg_restore actually restore to an independent 35# database and then pg_dump *that* database (or something along 36# those lines) to validate that part of the process. 37 38my %pgdump_runs = ( 39 binary_upgrade => { 40 dump_cmd => [ 41 'pg_dump', 42 '--no-sync', 43 '--format=custom', 44 "--file=$tempdir/binary_upgrade.dump", 45 '-w', 46 '--schema-only', 47 '--binary-upgrade', 48 '-d', 'postgres', # alternative way to specify database 49 ], 50 restore_cmd => [ 51 'pg_restore', '-Fc', '--verbose', 52 "--file=$tempdir/binary_upgrade.sql", 53 "$tempdir/binary_upgrade.dump", 54 ], 55 }, 56 clean => { 57 dump_cmd => [ 58 'pg_dump', 59 '--no-sync', 60 "--file=$tempdir/clean.sql", 61 '-c', 62 '-d', 'postgres', # alternative way to specify database 63 ], 64 }, 65 clean_if_exists => { 66 dump_cmd => [ 67 'pg_dump', 68 '--no-sync', 69 "--file=$tempdir/clean_if_exists.sql", 70 '-c', 71 '--if-exists', 72 '--encoding=UTF8', # no-op, just tests that option is accepted 73 'postgres', 74 ], 75 }, 76 column_inserts => { 77 dump_cmd => [ 78 'pg_dump', '--no-sync', 79 "--file=$tempdir/column_inserts.sql", '-a', 80 '--column-inserts', 'postgres', 81 ], 82 }, 83 createdb => { 84 dump_cmd => [ 85 'pg_dump', 86 '--no-sync', 87 "--file=$tempdir/createdb.sql", 88 '-C', 89 '-R', # no-op, just for testing 90 '-v', 91 'postgres', 92 ], 93 }, 94 data_only => { 95 dump_cmd => [ 96 'pg_dump', 97 '--no-sync', 98 "--file=$tempdir/data_only.sql", 99 '-a', 100 '--superuser=test_superuser', 101 '--disable-triggers', 102 '-v', # no-op, just make sure it works 103 'postgres', 104 ], 105 }, 106 defaults => { 107 dump_cmd => [ 108 'pg_dump', '--no-sync', 109 '-f', "$tempdir/defaults.sql", 110 'postgres', 111 ], 112 }, 113 defaults_no_public => { 114 database => 'regress_pg_dump_test', 115 dump_cmd => [ 116 'pg_dump', '--no-sync', '-f', "$tempdir/defaults_no_public.sql", 117 'regress_pg_dump_test', 118 ], 119 }, 120 defaults_no_public_clean => { 121 database => 'regress_pg_dump_test', 122 dump_cmd => [ 123 'pg_dump', '--no-sync', '-c', '-f', 124 "$tempdir/defaults_no_public_clean.sql", 125 'regress_pg_dump_test', 126 ], 127 }, 128 129 # Do not use --no-sync to give test coverage for data sync. 130 defaults_custom_format => { 131 test_key => 'defaults', 132 dump_cmd => [ 133 'pg_dump', '-Fc', '-Z6', 134 "--file=$tempdir/defaults_custom_format.dump", 'postgres', 135 ], 136 restore_cmd => [ 137 'pg_restore', '-Fc', 138 "--file=$tempdir/defaults_custom_format.sql", 139 "$tempdir/defaults_custom_format.dump", 140 ], 141 }, 142 143 # Do not use --no-sync to give test coverage for data sync. 144 defaults_dir_format => { 145 test_key => 'defaults', 146 dump_cmd => [ 147 'pg_dump', '-Fd', 148 "--file=$tempdir/defaults_dir_format", 'postgres', 149 ], 150 restore_cmd => [ 151 'pg_restore', '-Fd', 152 "--file=$tempdir/defaults_dir_format.sql", 153 "$tempdir/defaults_dir_format", 154 ], 155 }, 156 157 # Do not use --no-sync to give test coverage for data sync. 158 defaults_parallel => { 159 test_key => 'defaults', 160 dump_cmd => [ 161 'pg_dump', '-Fd', '-j2', "--file=$tempdir/defaults_parallel", 162 'postgres', 163 ], 164 restore_cmd => [ 165 'pg_restore', 166 "--file=$tempdir/defaults_parallel.sql", 167 "$tempdir/defaults_parallel", 168 ], 169 }, 170 171 # Do not use --no-sync to give test coverage for data sync. 172 defaults_tar_format => { 173 test_key => 'defaults', 174 dump_cmd => [ 175 'pg_dump', '-Ft', 176 "--file=$tempdir/defaults_tar_format.tar", 'postgres', 177 ], 178 restore_cmd => [ 179 'pg_restore', 180 '--format=tar', 181 "--file=$tempdir/defaults_tar_format.sql", 182 "$tempdir/defaults_tar_format.tar", 183 ], 184 }, 185 exclude_dump_test_schema => { 186 dump_cmd => [ 187 'pg_dump', '--no-sync', 188 "--file=$tempdir/exclude_dump_test_schema.sql", 189 '--exclude-schema=dump_test', 'postgres', 190 ], 191 }, 192 exclude_test_table => { 193 dump_cmd => [ 194 'pg_dump', '--no-sync', 195 "--file=$tempdir/exclude_test_table.sql", 196 '--exclude-table=dump_test.test_table', 'postgres', 197 ], 198 }, 199 exclude_test_table_data => { 200 dump_cmd => [ 201 'pg_dump', 202 '--no-sync', 203 "--file=$tempdir/exclude_test_table_data.sql", 204 '--exclude-table-data=dump_test.test_table', 205 '--no-unlogged-table-data', 206 'postgres', 207 ], 208 }, 209 pg_dumpall_globals => { 210 dump_cmd => [ 211 'pg_dumpall', '-v', "--file=$tempdir/pg_dumpall_globals.sql", 212 '-g', '--no-sync', 213 ], 214 }, 215 pg_dumpall_globals_clean => { 216 dump_cmd => [ 217 'pg_dumpall', "--file=$tempdir/pg_dumpall_globals_clean.sql", 218 '-g', '-c', '--no-sync', 219 ], 220 }, 221 pg_dumpall_dbprivs => { 222 dump_cmd => [ 223 'pg_dumpall', '--no-sync', 224 "--file=$tempdir/pg_dumpall_dbprivs.sql", 225 ], 226 }, 227 pg_dumpall_exclude => { 228 dump_cmd => [ 229 'pg_dumpall', '-v', "--file=$tempdir/pg_dumpall_exclude.sql", 230 '--exclude-database', '*dump_test*', '--no-sync', 231 ], 232 }, 233 no_blobs => { 234 dump_cmd => [ 235 'pg_dump', '--no-sync', 236 "--file=$tempdir/no_blobs.sql", '-B', 237 'postgres', 238 ], 239 }, 240 no_privs => { 241 dump_cmd => [ 242 'pg_dump', '--no-sync', 243 "--file=$tempdir/no_privs.sql", '-x', 244 'postgres', 245 ], 246 }, 247 no_owner => { 248 dump_cmd => [ 249 'pg_dump', '--no-sync', 250 "--file=$tempdir/no_owner.sql", '-O', 251 'postgres', 252 ], 253 }, 254 only_dump_test_schema => { 255 dump_cmd => [ 256 'pg_dump', '--no-sync', 257 "--file=$tempdir/only_dump_test_schema.sql", 258 '--schema=dump_test', 'postgres', 259 ], 260 }, 261 only_dump_test_table => { 262 dump_cmd => [ 263 'pg_dump', 264 '--no-sync', 265 "--file=$tempdir/only_dump_test_table.sql", 266 '--table=dump_test.test_table', 267 '--lock-wait-timeout=1000000', 268 'postgres', 269 ], 270 }, 271 role => { 272 dump_cmd => [ 273 'pg_dump', 274 '--no-sync', 275 "--file=$tempdir/role.sql", 276 '--role=regress_dump_test_role', 277 '--schema=dump_test_second_schema', 278 'postgres', 279 ], 280 }, 281 role_parallel => { 282 test_key => 'role', 283 dump_cmd => [ 284 'pg_dump', 285 '--no-sync', 286 '--format=directory', 287 '--jobs=2', 288 "--file=$tempdir/role_parallel", 289 '--role=regress_dump_test_role', 290 '--schema=dump_test_second_schema', 291 'postgres', 292 ], 293 restore_cmd => [ 294 'pg_restore', "--file=$tempdir/role_parallel.sql", 295 "$tempdir/role_parallel", 296 ], 297 }, 298 rows_per_insert => { 299 dump_cmd => [ 300 'pg_dump', 301 '--no-sync', 302 "--file=$tempdir/rows_per_insert.sql", 303 '-a', 304 '--rows-per-insert=4', 305 '--table=dump_test.test_table', 306 '--table=dump_test.test_fourth_table', 307 'postgres', 308 ], 309 }, 310 schema_only => { 311 dump_cmd => [ 312 'pg_dump', '--format=plain', 313 "--file=$tempdir/schema_only.sql", '--no-sync', 314 '-s', 'postgres', 315 ], 316 }, 317 section_pre_data => { 318 dump_cmd => [ 319 'pg_dump', "--file=$tempdir/section_pre_data.sql", 320 '--section=pre-data', '--no-sync', 321 'postgres', 322 ], 323 }, 324 section_data => { 325 dump_cmd => [ 326 'pg_dump', "--file=$tempdir/section_data.sql", 327 '--section=data', '--no-sync', 328 'postgres', 329 ], 330 }, 331 section_post_data => { 332 dump_cmd => [ 333 'pg_dump', "--file=$tempdir/section_post_data.sql", 334 '--section=post-data', '--no-sync', 'postgres', 335 ], 336 }, 337 test_schema_plus_blobs => { 338 dump_cmd => [ 339 'pg_dump', "--file=$tempdir/test_schema_plus_blobs.sql", 340 341 '--schema=dump_test', '-b', '-B', '--no-sync', 'postgres', 342 ], 343 },); 344 345############################################################### 346# Definition of the tests to run. 347# 348# Each test is defined using the log message that will be used. 349# 350# A regexp should be defined for each test which provides the 351# basis for the test. That regexp will be run against the output 352# file of each of the runs which the test is to be run against 353# and the success of the result will depend on if the regexp 354# result matches the expected 'like' or 'unlike' case. 355# 356# The runs listed as 'like' will be checked if they match the 357# regexp and, if so, the test passes. All runs which are not 358# listed as 'like' will be checked to ensure they don't match 359# the regexp; if they do, the test will fail. 360# 361# The below hashes provide convenience sets of runs. Individual 362# runs can be excluded from a general hash by placing that run 363# into the 'unlike' section. 364# 365# For example, there is an 'exclude_test_table' run which runs a 366# full pg_dump but with an exclude flag to not include the test 367# table. The CREATE TABLE test which creates the test table is 368# defined with %full_runs but then has 'exclude_test_table' in 369# its 'unlike' list, excluding that test. 370# 371# There can then be a 'create_sql' and 'create_order' for a 372# given test. The 'create_sql' commands are collected up in 373# 'create_order' and then run against the database prior to any 374# of the pg_dump runs happening. This is what "seeds" the 375# system with objects to be dumped out. 376# 377# Building of this hash takes a bit of time as all of the regexps 378# included in it are compiled. This greatly improves performance 379# as the regexps are used for each run the test applies to. 380 381# Tests which target the 'dump_test' schema, specifically. 382my %dump_test_schema_runs = ( 383 only_dump_test_schema => 1, 384 test_schema_plus_blobs => 1,); 385 386# Tests which are considered 'full' dumps by pg_dump, but there 387# are flags used to exclude specific items (ACLs, blobs, etc). 388my %full_runs = ( 389 binary_upgrade => 1, 390 clean => 1, 391 clean_if_exists => 1, 392 createdb => 1, 393 defaults => 1, 394 exclude_dump_test_schema => 1, 395 exclude_test_table => 1, 396 exclude_test_table_data => 1, 397 no_blobs => 1, 398 no_owner => 1, 399 no_privs => 1, 400 pg_dumpall_dbprivs => 1, 401 pg_dumpall_exclude => 1, 402 schema_only => 1,); 403 404# This is where the actual tests are defined. 405my %tests = ( 406 'ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role GRANT' => { 407 create_order => 14, 408 create_sql => 'ALTER DEFAULT PRIVILEGES 409 FOR ROLE regress_dump_test_role IN SCHEMA dump_test 410 GRANT SELECT ON TABLES TO regress_dump_test_role;', 411 regexp => qr/^ 412 \QALTER DEFAULT PRIVILEGES \E 413 \QFOR ROLE regress_dump_test_role IN SCHEMA dump_test \E 414 \QGRANT SELECT ON TABLES TO regress_dump_test_role;\E 415 /xm, 416 like => 417 { %full_runs, %dump_test_schema_runs, section_post_data => 1, }, 418 unlike => { 419 exclude_dump_test_schema => 1, 420 no_privs => 1, 421 }, 422 }, 423 424 'ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role GRANT EXECUTE ON FUNCTIONS' 425 => { 426 create_order => 15, 427 create_sql => 'ALTER DEFAULT PRIVILEGES 428 FOR ROLE regress_dump_test_role IN SCHEMA dump_test 429 GRANT EXECUTE ON FUNCTIONS TO regress_dump_test_role;', 430 regexp => qr/^ 431 \QALTER DEFAULT PRIVILEGES \E 432 \QFOR ROLE regress_dump_test_role IN SCHEMA dump_test \E 433 \QGRANT ALL ON FUNCTIONS TO regress_dump_test_role;\E 434 /xm, 435 like => 436 { %full_runs, %dump_test_schema_runs, section_post_data => 1, }, 437 unlike => { 438 exclude_dump_test_schema => 1, 439 no_privs => 1, 440 }, 441 }, 442 443 'ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role REVOKE' => { 444 create_order => 55, 445 create_sql => 'ALTER DEFAULT PRIVILEGES 446 FOR ROLE regress_dump_test_role 447 REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;', 448 regexp => qr/^ 449 \QALTER DEFAULT PRIVILEGES \E 450 \QFOR ROLE regress_dump_test_role \E 451 \QREVOKE ALL ON FUNCTIONS FROM PUBLIC;\E 452 /xm, 453 like => { %full_runs, section_post_data => 1, }, 454 unlike => { no_privs => 1, }, 455 }, 456 457 'ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role REVOKE SELECT' 458 => { 459 create_order => 56, 460 create_sql => 'ALTER DEFAULT PRIVILEGES 461 FOR ROLE regress_dump_test_role 462 REVOKE SELECT ON TABLES FROM regress_dump_test_role;', 463 regexp => qr/^ 464 \QALTER DEFAULT PRIVILEGES \E 465 \QFOR ROLE regress_dump_test_role \E 466 \QREVOKE ALL ON TABLES FROM regress_dump_test_role;\E\n 467 \QALTER DEFAULT PRIVILEGES \E 468 \QFOR ROLE regress_dump_test_role \E 469 \QGRANT INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,UPDATE ON TABLES TO regress_dump_test_role;\E 470 /xm, 471 like => { %full_runs, section_post_data => 1, }, 472 unlike => { no_privs => 1, }, 473 }, 474 475 'ALTER ROLE regress_dump_test_role' => { 476 regexp => qr/^ 477 \QALTER ROLE regress_dump_test_role WITH \E 478 \QNOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN \E 479 \QNOREPLICATION NOBYPASSRLS;\E 480 /xm, 481 like => { 482 pg_dumpall_dbprivs => 1, 483 pg_dumpall_globals => 1, 484 pg_dumpall_globals_clean => 1, 485 pg_dumpall_exclude => 1, 486 }, 487 }, 488 489 'ALTER COLLATION test0 OWNER TO' => { 490 regexp => qr/^\QALTER COLLATION public.test0 OWNER TO \E.+;/m, 491 collation => 1, 492 like => { %full_runs, section_pre_data => 1, }, 493 unlike => { %dump_test_schema_runs, no_owner => 1, }, 494 }, 495 496 'ALTER FOREIGN DATA WRAPPER dummy OWNER TO' => { 497 regexp => qr/^ALTER FOREIGN DATA WRAPPER dummy OWNER TO .+;/m, 498 like => { %full_runs, section_pre_data => 1, }, 499 unlike => { no_owner => 1, }, 500 }, 501 502 'ALTER SERVER s1 OWNER TO' => { 503 regexp => qr/^ALTER SERVER s1 OWNER TO .+;/m, 504 like => { %full_runs, section_pre_data => 1, }, 505 unlike => { no_owner => 1, }, 506 }, 507 508 'ALTER FUNCTION dump_test.pltestlang_call_handler() OWNER TO' => { 509 regexp => qr/^ 510 \QALTER FUNCTION dump_test.pltestlang_call_handler() \E 511 \QOWNER TO \E 512 .+;/xm, 513 like => 514 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 515 unlike => { 516 exclude_dump_test_schema => 1, 517 no_owner => 1, 518 }, 519 }, 520 521 'ALTER OPERATOR FAMILY dump_test.op_family OWNER TO' => { 522 regexp => qr/^ 523 \QALTER OPERATOR FAMILY dump_test.op_family USING btree \E 524 \QOWNER TO \E 525 .+;/xm, 526 like => 527 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 528 unlike => { 529 exclude_dump_test_schema => 1, 530 no_owner => 1, 531 }, 532 }, 533 534 'ALTER OPERATOR FAMILY dump_test.op_family USING btree' => { 535 create_order => 75, 536 create_sql => 537 'ALTER OPERATOR FAMILY dump_test.op_family USING btree ADD 538 OPERATOR 1 <(bigint,int4), 539 OPERATOR 2 <=(bigint,int4), 540 OPERATOR 3 =(bigint,int4), 541 OPERATOR 4 >=(bigint,int4), 542 OPERATOR 5 >(bigint,int4), 543 FUNCTION 1 (int4, int4) btint4cmp(int4,int4), 544 FUNCTION 2 (int4, int4) btint4sortsupport(internal);', 545 regexp => qr/^ 546 \QALTER OPERATOR FAMILY dump_test.op_family USING btree ADD\E\n\s+ 547 \QOPERATOR 1 <(bigint,integer) ,\E\n\s+ 548 \QOPERATOR 2 <=(bigint,integer) ,\E\n\s+ 549 \QOPERATOR 3 =(bigint,integer) ,\E\n\s+ 550 \QOPERATOR 4 >=(bigint,integer) ,\E\n\s+ 551 \QOPERATOR 5 >(bigint,integer) ,\E\n\s+ 552 \QFUNCTION 1 (integer, integer) btint4cmp(integer,integer) ,\E\n\s+ 553 \QFUNCTION 2 (integer, integer) btint4sortsupport(internal);\E 554 /xm, 555 like => 556 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 557 unlike => { exclude_dump_test_schema => 1, }, 558 }, 559 560 'ALTER OPERATOR CLASS dump_test.op_class OWNER TO' => { 561 regexp => qr/^ 562 \QALTER OPERATOR CLASS dump_test.op_class USING btree \E 563 \QOWNER TO \E 564 .+;/xm, 565 like => 566 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 567 unlike => { 568 exclude_dump_test_schema => 1, 569 no_owner => 1, 570 }, 571 }, 572 573 'ALTER PUBLICATION pub1 OWNER TO' => { 574 regexp => qr/^ALTER PUBLICATION pub1 OWNER TO .+;/m, 575 like => { %full_runs, section_post_data => 1, }, 576 unlike => { no_owner => 1, }, 577 }, 578 579 'ALTER LARGE OBJECT ... OWNER TO' => { 580 regexp => qr/^ALTER LARGE OBJECT \d+ OWNER TO .+;/m, 581 like => { 582 %full_runs, 583 column_inserts => 1, 584 data_only => 1, 585 section_pre_data => 1, 586 test_schema_plus_blobs => 1, 587 }, 588 unlike => { 589 no_blobs => 1, 590 no_owner => 1, 591 schema_only => 1, 592 }, 593 }, 594 595 'ALTER PROCEDURAL LANGUAGE pltestlang OWNER TO' => { 596 regexp => qr/^ALTER PROCEDURAL LANGUAGE pltestlang OWNER TO .+;/m, 597 like => { %full_runs, section_pre_data => 1, }, 598 unlike => { no_owner => 1, }, 599 }, 600 601 'ALTER SCHEMA dump_test OWNER TO' => { 602 regexp => qr/^ALTER SCHEMA dump_test OWNER TO .+;/m, 603 like => 604 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 605 unlike => { 606 exclude_dump_test_schema => 1, 607 no_owner => 1, 608 }, 609 }, 610 611 'ALTER SCHEMA dump_test_second_schema OWNER TO' => { 612 regexp => qr/^ALTER SCHEMA dump_test_second_schema OWNER TO .+;/m, 613 like => { 614 %full_runs, 615 role => 1, 616 section_pre_data => 1, 617 }, 618 unlike => { no_owner => 1, }, 619 }, 620 621 'ALTER SEQUENCE test_table_col1_seq' => { 622 regexp => qr/^ 623 \QALTER SEQUENCE dump_test.test_table_col1_seq OWNED BY dump_test.test_table.col1;\E 624 /xm, 625 like => { 626 %full_runs, 627 %dump_test_schema_runs, 628 only_dump_test_table => 1, 629 section_pre_data => 1, 630 }, 631 unlike => { 632 exclude_dump_test_schema => 1, 633 exclude_test_table => 1, 634 }, 635 }, 636 637 'ALTER TABLE ONLY test_table ADD CONSTRAINT ... PRIMARY KEY' => { 638 regexp => qr/^ 639 \QALTER TABLE ONLY dump_test.test_table\E \n^\s+ 640 \QADD CONSTRAINT test_table_pkey PRIMARY KEY (col1);\E 641 /xm, 642 like => { 643 %full_runs, 644 %dump_test_schema_runs, 645 only_dump_test_table => 1, 646 section_post_data => 1, 647 }, 648 unlike => { 649 exclude_dump_test_schema => 1, 650 exclude_test_table => 1, 651 }, 652 }, 653 654 'ALTER TABLE (partitioned) ADD CONSTRAINT ... FOREIGN KEY' => { 655 create_order => 4, 656 create_sql => 'CREATE TABLE dump_test.test_table_fk ( 657 col1 int references dump_test.test_table) 658 PARTITION BY RANGE (col1); 659 CREATE TABLE dump_test.test_table_fk_1 660 PARTITION OF dump_test.test_table_fk 661 FOR VALUES FROM (0) TO (10);', 662 regexp => qr/ 663 \QADD CONSTRAINT test_table_fk_col1_fkey FOREIGN KEY (col1) REFERENCES dump_test.test_table\E 664 /xm, 665 like => { 666 %full_runs, %dump_test_schema_runs, section_post_data => 1, 667 }, 668 unlike => { 669 exclude_dump_test_schema => 1, 670 }, 671 }, 672 673 'ALTER TABLE ONLY test_table ALTER COLUMN col1 SET STATISTICS 90' => { 674 create_order => 93, 675 create_sql => 676 'ALTER TABLE dump_test.test_table ALTER COLUMN col1 SET STATISTICS 90;', 677 regexp => qr/^ 678 \QALTER TABLE ONLY dump_test.test_table ALTER COLUMN col1 SET STATISTICS 90;\E\n 679 /xm, 680 like => { 681 %full_runs, 682 %dump_test_schema_runs, 683 only_dump_test_table => 1, 684 section_pre_data => 1, 685 }, 686 unlike => { 687 exclude_dump_test_schema => 1, 688 exclude_test_table => 1, 689 }, 690 }, 691 692 'ALTER TABLE ONLY test_table ALTER COLUMN col2 SET STORAGE' => { 693 create_order => 94, 694 create_sql => 695 'ALTER TABLE dump_test.test_table ALTER COLUMN col2 SET STORAGE EXTERNAL;', 696 regexp => qr/^ 697 \QALTER TABLE ONLY dump_test.test_table ALTER COLUMN col2 SET STORAGE EXTERNAL;\E\n 698 /xm, 699 like => { 700 %full_runs, 701 %dump_test_schema_runs, 702 only_dump_test_table => 1, 703 section_pre_data => 1, 704 }, 705 unlike => { 706 exclude_dump_test_schema => 1, 707 exclude_test_table => 1, 708 }, 709 }, 710 711 'ALTER TABLE ONLY test_table ALTER COLUMN col3 SET STORAGE' => { 712 create_order => 95, 713 create_sql => 714 'ALTER TABLE dump_test.test_table ALTER COLUMN col3 SET STORAGE MAIN;', 715 regexp => qr/^ 716 \QALTER TABLE ONLY dump_test.test_table ALTER COLUMN col3 SET STORAGE MAIN;\E\n 717 /xm, 718 like => { 719 %full_runs, 720 %dump_test_schema_runs, 721 only_dump_test_table => 1, 722 section_pre_data => 1, 723 }, 724 unlike => { 725 exclude_dump_test_schema => 1, 726 exclude_test_table => 1, 727 }, 728 }, 729 730 'ALTER TABLE ONLY test_table ALTER COLUMN col4 SET n_distinct' => { 731 create_order => 95, 732 create_sql => 733 'ALTER TABLE dump_test.test_table ALTER COLUMN col4 SET (n_distinct = 10);', 734 regexp => qr/^ 735 \QALTER TABLE ONLY dump_test.test_table ALTER COLUMN col4 SET (n_distinct=10);\E\n 736 /xm, 737 like => { 738 %full_runs, 739 %dump_test_schema_runs, 740 only_dump_test_table => 1, 741 section_pre_data => 1, 742 }, 743 unlike => { 744 exclude_dump_test_schema => 1, 745 exclude_test_table => 1, 746 }, 747 }, 748 749 'ALTER TABLE ONLY dump_test.measurement ATTACH PARTITION measurement_y2006m2' 750 => { 751 regexp => qr/^ 752 \QALTER TABLE ONLY dump_test.measurement ATTACH PARTITION dump_test_second_schema.measurement_y2006m2 \E 753 \QFOR VALUES FROM ('2006-02-01') TO ('2006-03-01');\E\n 754 /xm, 755 like => { 756 %full_runs, 757 role => 1, 758 section_pre_data => 1, 759 binary_upgrade => 1, 760 }, 761 }, 762 763 'ALTER TABLE test_table CLUSTER ON test_table_pkey' => { 764 create_order => 96, 765 create_sql => 766 'ALTER TABLE dump_test.test_table CLUSTER ON test_table_pkey', 767 regexp => qr/^ 768 \QALTER TABLE dump_test.test_table CLUSTER ON test_table_pkey;\E\n 769 /xm, 770 like => { 771 %full_runs, 772 %dump_test_schema_runs, 773 only_dump_test_table => 1, 774 section_post_data => 1, 775 }, 776 unlike => { 777 exclude_dump_test_schema => 1, 778 exclude_test_table => 1, 779 }, 780 }, 781 782 'ALTER TABLE test_table DISABLE TRIGGER ALL' => { 783 regexp => qr/^ 784 \QSET SESSION AUTHORIZATION 'test_superuser';\E\n\n 785 \QALTER TABLE dump_test.test_table DISABLE TRIGGER ALL;\E\n\n 786 \QCOPY dump_test.test_table (col1, col2, col3, col4) FROM stdin;\E 787 \n(?:\d\t\\N\t\\N\t\\N\n){9}\\\.\n\n\n 788 \QALTER TABLE dump_test.test_table ENABLE TRIGGER ALL;\E/xm, 789 like => { data_only => 1, }, 790 }, 791 792 'ALTER FOREIGN TABLE foreign_table ALTER COLUMN c1 OPTIONS' => { 793 regexp => qr/^ 794 \QALTER FOREIGN TABLE dump_test.foreign_table ALTER COLUMN c1 OPTIONS (\E\n 795 \s+\Qcolumn_name 'col1'\E\n 796 \Q);\E\n 797 /xm, 798 like => 799 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 800 unlike => { exclude_dump_test_schema => 1, }, 801 }, 802 803 'ALTER TABLE test_table OWNER TO' => { 804 regexp => qr/^\QALTER TABLE dump_test.test_table OWNER TO \E.+;/m, 805 like => { 806 %full_runs, 807 %dump_test_schema_runs, 808 only_dump_test_table => 1, 809 section_pre_data => 1, 810 }, 811 unlike => { 812 exclude_dump_test_schema => 1, 813 exclude_test_table => 1, 814 no_owner => 1, 815 }, 816 }, 817 818 'ALTER TABLE test_table ENABLE ROW LEVEL SECURITY' => { 819 create_order => 23, 820 create_sql => 'ALTER TABLE dump_test.test_table 821 ENABLE ROW LEVEL SECURITY;', 822 regexp => 823 qr/^\QALTER TABLE dump_test.test_table ENABLE ROW LEVEL SECURITY;\E/m, 824 like => { 825 %full_runs, 826 %dump_test_schema_runs, 827 only_dump_test_table => 1, 828 section_post_data => 1, 829 }, 830 unlike => { 831 exclude_dump_test_schema => 1, 832 exclude_test_table => 1, 833 }, 834 }, 835 836 'ALTER TABLE test_second_table OWNER TO' => { 837 regexp => 838 qr/^\QALTER TABLE dump_test.test_second_table OWNER TO \E.+;/m, 839 like => 840 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 841 unlike => { 842 exclude_dump_test_schema => 1, 843 no_owner => 1, 844 }, 845 }, 846 847 'ALTER TABLE measurement OWNER TO' => { 848 regexp => qr/^\QALTER TABLE dump_test.measurement OWNER TO \E.+;/m, 849 like => 850 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 851 unlike => { 852 exclude_dump_test_schema => 1, 853 no_owner => 1, 854 }, 855 }, 856 857 'ALTER TABLE measurement_y2006m2 OWNER TO' => { 858 regexp => 859 qr/^\QALTER TABLE dump_test_second_schema.measurement_y2006m2 OWNER TO \E.+;/m, 860 like => { 861 %full_runs, 862 role => 1, 863 section_pre_data => 1, 864 }, 865 unlike => { no_owner => 1, }, 866 }, 867 868 'ALTER FOREIGN TABLE foreign_table OWNER TO' => { 869 regexp => 870 qr/^\QALTER FOREIGN TABLE dump_test.foreign_table OWNER TO \E.+;/m, 871 like => 872 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 873 unlike => { 874 exclude_dump_test_schema => 1, 875 no_owner => 1, 876 }, 877 }, 878 879 'ALTER TEXT SEARCH CONFIGURATION alt_ts_conf1 OWNER TO' => { 880 regexp => 881 qr/^\QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1 OWNER TO \E.+;/m, 882 like => 883 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 884 unlike => { 885 exclude_dump_test_schema => 1, 886 no_owner => 1, 887 }, 888 }, 889 890 'ALTER TEXT SEARCH DICTIONARY alt_ts_dict1 OWNER TO' => { 891 regexp => 892 qr/^\QALTER TEXT SEARCH DICTIONARY dump_test.alt_ts_dict1 OWNER TO \E.+;/m, 893 like => 894 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 895 unlike => { 896 exclude_dump_test_schema => 1, 897 only_dump_test_table => 1, 898 no_owner => 1, 899 role => 1, 900 }, 901 }, 902 903 'BLOB create (using lo_from_bytea)' => { 904 create_order => 50, 905 create_sql => 906 'SELECT pg_catalog.lo_from_bytea(0, \'\\x310a320a330a340a350a360a370a380a390a\');', 907 regexp => qr/^SELECT pg_catalog\.lo_create\('\d+'\);/m, 908 like => { 909 %full_runs, 910 column_inserts => 1, 911 data_only => 1, 912 section_pre_data => 1, 913 test_schema_plus_blobs => 1, 914 }, 915 unlike => { 916 schema_only => 1, 917 no_blobs => 1, 918 }, 919 }, 920 921 'BLOB load (using lo_from_bytea)' => { 922 regexp => qr/^ 923 \QSELECT pg_catalog.lo_open\E \('\d+',\ \d+\);\n 924 \QSELECT pg_catalog.lowrite(0, \E 925 \Q'\x310a320a330a340a350a360a370a380a390a');\E\n 926 \QSELECT pg_catalog.lo_close(0);\E 927 /xm, 928 like => { 929 %full_runs, 930 column_inserts => 1, 931 data_only => 1, 932 section_data => 1, 933 test_schema_plus_blobs => 1, 934 }, 935 unlike => { 936 binary_upgrade => 1, 937 no_blobs => 1, 938 schema_only => 1, 939 }, 940 }, 941 942 'COMMENT ON DATABASE postgres' => { 943 regexp => qr/^COMMENT ON DATABASE postgres IS .+;/m, 944 945 # Should appear in the same tests as "CREATE DATABASE postgres" 946 like => { createdb => 1, }, 947 }, 948 949 'COMMENT ON EXTENSION plpgsql' => { 950 regexp => qr/^COMMENT ON EXTENSION plpgsql IS .+;/m, 951 952 # this shouldn't ever get emitted anymore 953 like => {}, 954 }, 955 956 'COMMENT ON TABLE dump_test.test_table' => { 957 create_order => 36, 958 create_sql => 'COMMENT ON TABLE dump_test.test_table 959 IS \'comment on table\';', 960 regexp => 961 qr/^\QCOMMENT ON TABLE dump_test.test_table IS 'comment on table';\E/m, 962 like => { 963 %full_runs, 964 %dump_test_schema_runs, 965 only_dump_test_table => 1, 966 section_pre_data => 1, 967 }, 968 unlike => { 969 exclude_dump_test_schema => 1, 970 exclude_test_table => 1, 971 }, 972 }, 973 974 'COMMENT ON COLUMN dump_test.test_table.col1' => { 975 create_order => 36, 976 create_sql => 'COMMENT ON COLUMN dump_test.test_table.col1 977 IS \'comment on column\';', 978 regexp => qr/^ 979 \QCOMMENT ON COLUMN dump_test.test_table.col1 IS 'comment on column';\E 980 /xm, 981 like => { 982 %full_runs, 983 %dump_test_schema_runs, 984 only_dump_test_table => 1, 985 section_pre_data => 1, 986 }, 987 unlike => { 988 exclude_dump_test_schema => 1, 989 exclude_test_table => 1, 990 }, 991 }, 992 993 'COMMENT ON COLUMN dump_test.composite.f1' => { 994 create_order => 44, 995 create_sql => 'COMMENT ON COLUMN dump_test.composite.f1 996 IS \'comment on column of type\';', 997 regexp => qr/^ 998 \QCOMMENT ON COLUMN dump_test.composite.f1 IS 'comment on column of type';\E 999 /xm, 1000 like => 1001 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1002 unlike => { exclude_dump_test_schema => 1, }, 1003 }, 1004 1005 'COMMENT ON COLUMN dump_test.test_second_table.col1' => { 1006 create_order => 63, 1007 create_sql => 'COMMENT ON COLUMN dump_test.test_second_table.col1 1008 IS \'comment on column col1\';', 1009 regexp => qr/^ 1010 \QCOMMENT ON COLUMN dump_test.test_second_table.col1 IS 'comment on column col1';\E 1011 /xm, 1012 like => 1013 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1014 unlike => { exclude_dump_test_schema => 1, }, 1015 }, 1016 1017 'COMMENT ON COLUMN dump_test.test_second_table.col2' => { 1018 create_order => 64, 1019 create_sql => 'COMMENT ON COLUMN dump_test.test_second_table.col2 1020 IS \'comment on column col2\';', 1021 regexp => qr/^ 1022 \QCOMMENT ON COLUMN dump_test.test_second_table.col2 IS 'comment on column col2';\E 1023 /xm, 1024 like => 1025 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1026 unlike => { exclude_dump_test_schema => 1, }, 1027 }, 1028 1029 'COMMENT ON CONVERSION dump_test.test_conversion' => { 1030 create_order => 79, 1031 create_sql => 'COMMENT ON CONVERSION dump_test.test_conversion 1032 IS \'comment on test conversion\';', 1033 regexp => 1034 qr/^\QCOMMENT ON CONVERSION dump_test.test_conversion IS 'comment on test conversion';\E/m, 1035 like => 1036 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1037 unlike => { exclude_dump_test_schema => 1, }, 1038 }, 1039 1040 'COMMENT ON COLLATION test0' => { 1041 create_order => 77, 1042 create_sql => 'COMMENT ON COLLATION test0 1043 IS \'comment on test0 collation\';', 1044 regexp => 1045 qr/^\QCOMMENT ON COLLATION public.test0 IS 'comment on test0 collation';\E/m, 1046 collation => 1, 1047 like => { %full_runs, section_pre_data => 1, }, 1048 }, 1049 1050 'COMMENT ON LARGE OBJECT ...' => { 1051 create_order => 65, 1052 create_sql => 'DO $$ 1053 DECLARE myoid oid; 1054 BEGIN 1055 SELECT loid FROM pg_largeobject INTO myoid; 1056 EXECUTE \'COMMENT ON LARGE OBJECT \' || myoid || \' IS \'\'comment on large object\'\';\'; 1057 END; 1058 $$;', 1059 regexp => qr/^ 1060 \QCOMMENT ON LARGE OBJECT \E[0-9]+\Q IS 'comment on large object';\E 1061 /xm, 1062 like => { 1063 %full_runs, 1064 column_inserts => 1, 1065 data_only => 1, 1066 section_pre_data => 1, 1067 test_schema_plus_blobs => 1, 1068 }, 1069 unlike => { 1070 no_blobs => 1, 1071 schema_only => 1, 1072 }, 1073 }, 1074 1075 'COMMENT ON PUBLICATION pub1' => { 1076 create_order => 55, 1077 create_sql => 'COMMENT ON PUBLICATION pub1 1078 IS \'comment on publication\';', 1079 regexp => 1080 qr/^COMMENT ON PUBLICATION pub1 IS 'comment on publication';/m, 1081 like => { %full_runs, section_post_data => 1, }, 1082 }, 1083 1084 'COMMENT ON SUBSCRIPTION sub1' => { 1085 create_order => 55, 1086 create_sql => 'COMMENT ON SUBSCRIPTION sub1 1087 IS \'comment on subscription\';', 1088 regexp => 1089 qr/^COMMENT ON SUBSCRIPTION sub1 IS 'comment on subscription';/m, 1090 like => { %full_runs, section_post_data => 1, }, 1091 }, 1092 1093 'COMMENT ON TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1' => { 1094 create_order => 84, 1095 create_sql => 1096 'COMMENT ON TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1 1097 IS \'comment on text search configuration\';', 1098 regexp => 1099 qr/^\QCOMMENT ON TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1 IS 'comment on text search configuration';\E/m, 1100 like => 1101 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1102 unlike => { exclude_dump_test_schema => 1, }, 1103 }, 1104 1105 'COMMENT ON TEXT SEARCH DICTIONARY dump_test.alt_ts_dict1' => { 1106 create_order => 84, 1107 create_sql => 1108 'COMMENT ON TEXT SEARCH DICTIONARY dump_test.alt_ts_dict1 1109 IS \'comment on text search dictionary\';', 1110 regexp => 1111 qr/^\QCOMMENT ON TEXT SEARCH DICTIONARY dump_test.alt_ts_dict1 IS 'comment on text search dictionary';\E/m, 1112 like => 1113 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1114 unlike => { exclude_dump_test_schema => 1, }, 1115 }, 1116 1117 'COMMENT ON TEXT SEARCH PARSER dump_test.alt_ts_prs1' => { 1118 create_order => 84, 1119 create_sql => 'COMMENT ON TEXT SEARCH PARSER dump_test.alt_ts_prs1 1120 IS \'comment on text search parser\';', 1121 regexp => 1122 qr/^\QCOMMENT ON TEXT SEARCH PARSER dump_test.alt_ts_prs1 IS 'comment on text search parser';\E/m, 1123 like => 1124 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1125 unlike => { exclude_dump_test_schema => 1, }, 1126 }, 1127 1128 'COMMENT ON TEXT SEARCH TEMPLATE dump_test.alt_ts_temp1' => { 1129 create_order => 84, 1130 create_sql => 'COMMENT ON TEXT SEARCH TEMPLATE dump_test.alt_ts_temp1 1131 IS \'comment on text search template\';', 1132 regexp => 1133 qr/^\QCOMMENT ON TEXT SEARCH TEMPLATE dump_test.alt_ts_temp1 IS 'comment on text search template';\E/m, 1134 like => 1135 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1136 unlike => { exclude_dump_test_schema => 1, }, 1137 }, 1138 1139 'COMMENT ON TYPE dump_test.planets - ENUM' => { 1140 create_order => 68, 1141 create_sql => 'COMMENT ON TYPE dump_test.planets 1142 IS \'comment on enum type\';', 1143 regexp => 1144 qr/^\QCOMMENT ON TYPE dump_test.planets IS 'comment on enum type';\E/m, 1145 like => 1146 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1147 unlike => { exclude_dump_test_schema => 1, }, 1148 }, 1149 1150 'COMMENT ON TYPE dump_test.textrange - RANGE' => { 1151 create_order => 69, 1152 create_sql => 'COMMENT ON TYPE dump_test.textrange 1153 IS \'comment on range type\';', 1154 regexp => 1155 qr/^\QCOMMENT ON TYPE dump_test.textrange IS 'comment on range type';\E/m, 1156 like => 1157 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1158 unlike => { exclude_dump_test_schema => 1, }, 1159 }, 1160 1161 'COMMENT ON TYPE dump_test.int42 - Regular' => { 1162 create_order => 70, 1163 create_sql => 'COMMENT ON TYPE dump_test.int42 1164 IS \'comment on regular type\';', 1165 regexp => 1166 qr/^\QCOMMENT ON TYPE dump_test.int42 IS 'comment on regular type';\E/m, 1167 like => 1168 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1169 unlike => { exclude_dump_test_schema => 1, }, 1170 }, 1171 1172 'COMMENT ON TYPE dump_test.undefined - Undefined' => { 1173 create_order => 71, 1174 create_sql => 'COMMENT ON TYPE dump_test.undefined 1175 IS \'comment on undefined type\';', 1176 regexp => 1177 qr/^\QCOMMENT ON TYPE dump_test.undefined IS 'comment on undefined type';\E/m, 1178 like => 1179 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1180 unlike => { exclude_dump_test_schema => 1, }, 1181 }, 1182 1183 'COPY test_table' => { 1184 create_order => 4, 1185 create_sql => 'INSERT INTO dump_test.test_table (col1) ' 1186 . 'SELECT generate_series FROM generate_series(1,9);', 1187 regexp => qr/^ 1188 \QCOPY dump_test.test_table (col1, col2, col3, col4) FROM stdin;\E 1189 \n(?:\d\t\\N\t\\N\t\\N\n){9}\\\.\n 1190 /xm, 1191 like => { 1192 %full_runs, 1193 %dump_test_schema_runs, 1194 data_only => 1, 1195 only_dump_test_table => 1, 1196 section_data => 1, 1197 }, 1198 unlike => { 1199 binary_upgrade => 1, 1200 exclude_dump_test_schema => 1, 1201 exclude_test_table => 1, 1202 exclude_test_table_data => 1, 1203 schema_only => 1, 1204 }, 1205 }, 1206 1207 'COPY fk_reference_test_table' => { 1208 create_order => 22, 1209 create_sql => 'INSERT INTO dump_test.fk_reference_test_table (col1) ' 1210 . 'SELECT generate_series FROM generate_series(1,5);', 1211 regexp => qr/^ 1212 \QCOPY dump_test.fk_reference_test_table (col1) FROM stdin;\E 1213 \n(?:\d\n){5}\\\.\n 1214 /xm, 1215 like => { 1216 %full_runs, 1217 %dump_test_schema_runs, 1218 data_only => 1, 1219 exclude_test_table => 1, 1220 exclude_test_table_data => 1, 1221 section_data => 1, 1222 }, 1223 unlike => { 1224 binary_upgrade => 1, 1225 exclude_dump_test_schema => 1, 1226 schema_only => 1, 1227 }, 1228 }, 1229 1230 # In a data-only dump, we try to actually order according to FKs, 1231 # so this check is just making sure that the referring table comes after 1232 # the referred-to table. 1233 'COPY fk_reference_test_table second' => { 1234 regexp => qr/^ 1235 \QCOPY dump_test.test_table (col1, col2, col3, col4) FROM stdin;\E 1236 \n(?:\d\t\\N\t\\N\t\\N\n){9}\\\.\n.* 1237 \QCOPY dump_test.fk_reference_test_table (col1) FROM stdin;\E 1238 \n(?:\d\n){5}\\\.\n 1239 /xms, 1240 like => { data_only => 1, }, 1241 }, 1242 1243 'COPY test_second_table' => { 1244 create_order => 7, 1245 create_sql => 'INSERT INTO dump_test.test_second_table (col1, col2) ' 1246 . 'SELECT generate_series, generate_series::text ' 1247 . 'FROM generate_series(1,9);', 1248 regexp => qr/^ 1249 \QCOPY dump_test.test_second_table (col1, col2) FROM stdin;\E 1250 \n(?:\d\t\d\n){9}\\\.\n 1251 /xm, 1252 like => { 1253 %full_runs, 1254 %dump_test_schema_runs, 1255 data_only => 1, 1256 section_data => 1, 1257 }, 1258 unlike => { 1259 binary_upgrade => 1, 1260 exclude_dump_test_schema => 1, 1261 schema_only => 1, 1262 }, 1263 }, 1264 1265 'COPY test_fourth_table' => { 1266 create_order => 7, 1267 create_sql => 1268 'INSERT INTO dump_test.test_fourth_table DEFAULT VALUES;' 1269 . 'INSERT INTO dump_test.test_fourth_table DEFAULT VALUES;', 1270 regexp => qr/^ 1271 \QCOPY dump_test.test_fourth_table FROM stdin;\E 1272 \n\n\n\\\.\n 1273 /xm, 1274 like => { 1275 %full_runs, 1276 %dump_test_schema_runs, 1277 data_only => 1, 1278 section_data => 1, 1279 }, 1280 unlike => { 1281 binary_upgrade => 1, 1282 exclude_dump_test_schema => 1, 1283 schema_only => 1, 1284 }, 1285 }, 1286 1287 'COPY test_fifth_table' => { 1288 create_order => 54, 1289 create_sql => 1290 'INSERT INTO dump_test.test_fifth_table VALUES (NULL, true, false, \'11001\'::bit(5), \'NaN\');', 1291 regexp => qr/^ 1292 \QCOPY dump_test.test_fifth_table (col1, col2, col3, col4, col5) FROM stdin;\E 1293 \n\\N\tt\tf\t11001\tNaN\n\\\.\n 1294 /xm, 1295 like => { 1296 %full_runs, 1297 %dump_test_schema_runs, 1298 data_only => 1, 1299 section_data => 1, 1300 }, 1301 unlike => { 1302 binary_upgrade => 1, 1303 exclude_dump_test_schema => 1, 1304 schema_only => 1, 1305 }, 1306 }, 1307 1308 'COPY test_table_identity' => { 1309 create_order => 54, 1310 create_sql => 1311 'INSERT INTO dump_test.test_table_identity (col2) VALUES (\'test\');', 1312 regexp => qr/^ 1313 \QCOPY dump_test.test_table_identity (col1, col2) FROM stdin;\E 1314 \n1\ttest\n\\\.\n 1315 /xm, 1316 like => { 1317 %full_runs, 1318 %dump_test_schema_runs, 1319 data_only => 1, 1320 section_data => 1, 1321 }, 1322 unlike => { 1323 binary_upgrade => 1, 1324 exclude_dump_test_schema => 1, 1325 schema_only => 1, 1326 }, 1327 }, 1328 1329 'INSERT INTO test_table' => { 1330 regexp => qr/^ 1331 (?:INSERT\ INTO\ dump_test\.test_table\ \(col1,\ col2,\ col3,\ col4\)\ VALUES\ \(\d,\ NULL,\ NULL,\ NULL\);\n){9} 1332 /xm, 1333 like => { column_inserts => 1, }, 1334 }, 1335 1336 'test_table with 4-row INSERTs' => { 1337 regexp => qr/^ 1338 (?: 1339 INSERT\ INTO\ dump_test\.test_table\ VALUES\n 1340 (?:\t\(\d,\ NULL,\ NULL,\ NULL\),\n){3} 1341 \t\(\d,\ NULL,\ NULL,\ NULL\);\n 1342 ){2} 1343 INSERT\ INTO\ dump_test\.test_table\ VALUES\n 1344 \t\(\d,\ NULL,\ NULL,\ NULL\); 1345 /xm, 1346 like => { rows_per_insert => 1, }, 1347 }, 1348 1349 'INSERT INTO test_second_table' => { 1350 regexp => qr/^ 1351 (?:INSERT\ INTO\ dump_test\.test_second_table\ \(col1,\ col2\) 1352 \ VALUES\ \(\d,\ '\d'\);\n){9}/xm, 1353 like => { column_inserts => 1, }, 1354 }, 1355 1356 'INSERT INTO test_fourth_table' => { 1357 regexp => 1358 qr/^(?:INSERT INTO dump_test\.test_fourth_table DEFAULT VALUES;\n){2}/m, 1359 like => { column_inserts => 1, rows_per_insert => 1, }, 1360 }, 1361 1362 'INSERT INTO test_fifth_table' => { 1363 regexp => 1364 qr/^\QINSERT INTO dump_test.test_fifth_table (col1, col2, col3, col4, col5) VALUES (NULL, true, false, B'11001', 'NaN');\E/m, 1365 like => { column_inserts => 1, }, 1366 }, 1367 1368 'INSERT INTO test_table_identity' => { 1369 regexp => 1370 qr/^\QINSERT INTO dump_test.test_table_identity (col1, col2) OVERRIDING SYSTEM VALUE VALUES (1, 'test');\E/m, 1371 like => { column_inserts => 1, }, 1372 }, 1373 1374 'CREATE ROLE regress_dump_test_role' => { 1375 create_order => 1, 1376 create_sql => 'CREATE ROLE regress_dump_test_role;', 1377 regexp => qr/^CREATE ROLE regress_dump_test_role;/m, 1378 like => { 1379 pg_dumpall_dbprivs => 1, 1380 pg_dumpall_exclude => 1, 1381 pg_dumpall_globals => 1, 1382 pg_dumpall_globals_clean => 1, 1383 }, 1384 }, 1385 1386 'CREATE ACCESS METHOD gist2' => { 1387 create_order => 52, 1388 create_sql => 1389 'CREATE ACCESS METHOD gist2 TYPE INDEX HANDLER gisthandler;', 1390 regexp => 1391 qr/CREATE ACCESS METHOD gist2 TYPE INDEX HANDLER gisthandler;/m, 1392 like => { %full_runs, section_pre_data => 1, }, 1393 }, 1394 1395 'CREATE COLLATION test0 FROM "C"' => { 1396 create_order => 76, 1397 create_sql => 'CREATE COLLATION test0 FROM "C";', 1398 regexp => qr/^ 1399 \QCREATE COLLATION public.test0 (provider = libc, locale = 'C');\E/xm, 1400 collation => 1, 1401 like => { %full_runs, section_pre_data => 1, }, 1402 }, 1403 1404 'CREATE CAST FOR timestamptz' => { 1405 create_order => 51, 1406 create_sql => 1407 'CREATE CAST (timestamptz AS interval) WITH FUNCTION age(timestamptz) AS ASSIGNMENT;', 1408 regexp => 1409 qr/CREATE CAST \(timestamp with time zone AS interval\) WITH FUNCTION pg_catalog\.age\(timestamp with time zone\) AS ASSIGNMENT;/m, 1410 like => { %full_runs, section_pre_data => 1, }, 1411 }, 1412 1413 'CREATE DATABASE postgres' => { 1414 regexp => qr/^ 1415 \QCREATE DATABASE postgres WITH TEMPLATE = template0 \E 1416 .+;/xm, 1417 like => { createdb => 1, }, 1418 }, 1419 1420 'CREATE DATABASE dump_test' => { 1421 create_order => 47, 1422 create_sql => 'CREATE DATABASE dump_test;', 1423 regexp => qr/^ 1424 \QCREATE DATABASE dump_test WITH TEMPLATE = template0 \E 1425 .+;/xm, 1426 like => { pg_dumpall_dbprivs => 1, }, 1427 }, 1428 1429 'CREATE EXTENSION ... plpgsql' => { 1430 regexp => qr/^ 1431 \QCREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;\E 1432 /xm, 1433 1434 # this shouldn't ever get emitted anymore 1435 like => {}, 1436 }, 1437 1438 'CREATE AGGREGATE dump_test.newavg' => { 1439 create_order => 25, 1440 create_sql => 'CREATE AGGREGATE dump_test.newavg ( 1441 sfunc = int4_avg_accum, 1442 basetype = int4, 1443 stype = _int8, 1444 finalfunc = int8_avg, 1445 finalfunc_modify = shareable, 1446 initcond1 = \'{0,0}\' 1447 );', 1448 regexp => qr/^ 1449 \QCREATE AGGREGATE dump_test.newavg(integer) (\E 1450 \n\s+\QSFUNC = int4_avg_accum,\E 1451 \n\s+\QSTYPE = bigint[],\E 1452 \n\s+\QINITCOND = '{0,0}',\E 1453 \n\s+\QFINALFUNC = int8_avg,\E 1454 \n\s+\QFINALFUNC_MODIFY = SHAREABLE\E 1455 \n\);/xm, 1456 like => { 1457 %full_runs, 1458 %dump_test_schema_runs, 1459 exclude_test_table => 1, 1460 section_pre_data => 1, 1461 }, 1462 unlike => { exclude_dump_test_schema => 1, }, 1463 }, 1464 1465 'CREATE CONVERSION dump_test.test_conversion' => { 1466 create_order => 78, 1467 create_sql => 1468 'CREATE DEFAULT CONVERSION dump_test.test_conversion FOR \'LATIN1\' TO \'UTF8\' FROM iso8859_1_to_utf8;', 1469 regexp => 1470 qr/^\QCREATE DEFAULT CONVERSION dump_test.test_conversion FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;\E/xm, 1471 like => 1472 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1473 unlike => { exclude_dump_test_schema => 1, }, 1474 }, 1475 1476 'CREATE DOMAIN dump_test.us_postal_code' => { 1477 create_order => 29, 1478 create_sql => 'CREATE DOMAIN dump_test.us_postal_code AS TEXT 1479 COLLATE "C" 1480 DEFAULT \'10014\' 1481 CHECK(VALUE ~ \'^\d{5}$\' OR 1482 VALUE ~ \'^\d{5}-\d{4}$\');', 1483 regexp => qr/^ 1484 \QCREATE DOMAIN dump_test.us_postal_code AS text COLLATE pg_catalog."C" DEFAULT '10014'::text\E\n\s+ 1485 \QCONSTRAINT us_postal_code_check CHECK \E 1486 \Q(((VALUE ~ '^\d{5}\E 1487 \$\Q'::text) OR (VALUE ~ '^\d{5}-\d{4}\E\$ 1488 \Q'::text)));\E 1489 /xm, 1490 like => 1491 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1492 unlike => { exclude_dump_test_schema => 1, }, 1493 }, 1494 1495 'CREATE FUNCTION dump_test.pltestlang_call_handler' => { 1496 create_order => 17, 1497 create_sql => 'CREATE FUNCTION dump_test.pltestlang_call_handler() 1498 RETURNS LANGUAGE_HANDLER AS \'$libdir/plpgsql\', 1499 \'plpgsql_call_handler\' LANGUAGE C;', 1500 regexp => qr/^ 1501 \QCREATE FUNCTION dump_test.pltestlang_call_handler() \E 1502 \QRETURNS language_handler\E 1503 \n\s+\QLANGUAGE c\E 1504 \n\s+AS\ \'\$ 1505 \Qlibdir\/plpgsql', 'plpgsql_call_handler';\E 1506 /xm, 1507 like => 1508 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1509 unlike => { exclude_dump_test_schema => 1, }, 1510 }, 1511 1512 'CREATE FUNCTION dump_test.trigger_func' => { 1513 create_order => 30, 1514 create_sql => 'CREATE FUNCTION dump_test.trigger_func() 1515 RETURNS trigger LANGUAGE plpgsql 1516 AS $$ BEGIN RETURN NULL; END;$$;', 1517 regexp => qr/^ 1518 \QCREATE FUNCTION dump_test.trigger_func() RETURNS trigger\E 1519 \n\s+\QLANGUAGE plpgsql\E 1520 \n\s+AS\ \$\$ 1521 \Q BEGIN RETURN NULL; END;\E 1522 \$\$;/xm, 1523 like => 1524 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1525 unlike => { exclude_dump_test_schema => 1, }, 1526 }, 1527 1528 'CREATE FUNCTION dump_test.event_trigger_func' => { 1529 create_order => 32, 1530 create_sql => 'CREATE FUNCTION dump_test.event_trigger_func() 1531 RETURNS event_trigger LANGUAGE plpgsql 1532 AS $$ BEGIN RETURN; END;$$;', 1533 regexp => qr/^ 1534 \QCREATE FUNCTION dump_test.event_trigger_func() RETURNS event_trigger\E 1535 \n\s+\QLANGUAGE plpgsql\E 1536 \n\s+AS\ \$\$ 1537 \Q BEGIN RETURN; END;\E 1538 \$\$;/xm, 1539 like => 1540 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1541 unlike => { exclude_dump_test_schema => 1, }, 1542 }, 1543 1544 'CREATE OPERATOR FAMILY dump_test.op_family' => { 1545 create_order => 73, 1546 create_sql => 1547 'CREATE OPERATOR FAMILY dump_test.op_family USING btree;', 1548 regexp => qr/^ 1549 \QCREATE OPERATOR FAMILY dump_test.op_family USING btree;\E 1550 /xm, 1551 like => 1552 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1553 unlike => { exclude_dump_test_schema => 1, }, 1554 }, 1555 1556 'CREATE OPERATOR CLASS dump_test.op_class' => { 1557 create_order => 74, 1558 create_sql => 'CREATE OPERATOR CLASS dump_test.op_class 1559 FOR TYPE bigint USING btree FAMILY dump_test.op_family 1560 AS STORAGE bigint, 1561 OPERATOR 1 <(bigint,bigint), 1562 OPERATOR 2 <=(bigint,bigint), 1563 OPERATOR 3 =(bigint,bigint), 1564 OPERATOR 4 >=(bigint,bigint), 1565 OPERATOR 5 >(bigint,bigint), 1566 FUNCTION 1 btint8cmp(bigint,bigint), 1567 FUNCTION 2 btint8sortsupport(internal);', 1568 regexp => qr/^ 1569 \QCREATE OPERATOR CLASS dump_test.op_class\E\n\s+ 1570 \QFOR TYPE bigint USING btree FAMILY dump_test.op_family AS\E\n\s+ 1571 \QOPERATOR 1 <(bigint,bigint) ,\E\n\s+ 1572 \QOPERATOR 2 <=(bigint,bigint) ,\E\n\s+ 1573 \QOPERATOR 3 =(bigint,bigint) ,\E\n\s+ 1574 \QOPERATOR 4 >=(bigint,bigint) ,\E\n\s+ 1575 \QOPERATOR 5 >(bigint,bigint) ,\E\n\s+ 1576 \QFUNCTION 1 (bigint, bigint) btint8cmp(bigint,bigint) ,\E\n\s+ 1577 \QFUNCTION 2 (bigint, bigint) btint8sortsupport(internal);\E 1578 /xm, 1579 like => 1580 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1581 unlike => { exclude_dump_test_schema => 1, }, 1582 }, 1583 1584 # verify that a custom operator/opclass/range type is dumped in right order 1585 'CREATE OPERATOR CLASS dump_test.op_class_custom' => { 1586 create_order => 74, 1587 create_sql => 'CREATE OPERATOR dump_test.~~ ( 1588 PROCEDURE = int4eq, 1589 LEFTARG = int, 1590 RIGHTARG = int); 1591 CREATE OPERATOR CLASS dump_test.op_class_custom 1592 FOR TYPE int USING btree AS 1593 OPERATOR 3 dump_test.~~; 1594 CREATE TYPE dump_test.range_type_custom AS RANGE ( 1595 subtype = int, 1596 subtype_opclass = dump_test.op_class_custom);', 1597 regexp => qr/^ 1598 \QCREATE OPERATOR dump_test.~~ (\E\n.+ 1599 \QCREATE OPERATOR FAMILY dump_test.op_class_custom USING btree;\E\n.+ 1600 \QCREATE OPERATOR CLASS dump_test.op_class_custom\E\n\s+ 1601 \QFOR TYPE integer USING btree FAMILY dump_test.op_class_custom AS\E\n\s+ 1602 \QOPERATOR 3 dump_test.~~(integer,integer);\E\n.+ 1603 \QCREATE TYPE dump_test.range_type_custom AS RANGE (\E\n\s+ 1604 \Qsubtype = integer,\E\n\s+ 1605 \Qsubtype_opclass = dump_test.op_class_custom\E\n 1606 \Q);\E 1607 /xms, 1608 like => 1609 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1610 unlike => { exclude_dump_test_schema => 1, }, 1611 }, 1612 1613 'CREATE OPERATOR CLASS dump_test.op_class_empty' => { 1614 create_order => 89, 1615 create_sql => 'CREATE OPERATOR CLASS dump_test.op_class_empty 1616 FOR TYPE bigint USING btree FAMILY dump_test.op_family 1617 AS STORAGE bigint;', 1618 regexp => qr/^ 1619 \QCREATE OPERATOR CLASS dump_test.op_class_empty\E\n\s+ 1620 \QFOR TYPE bigint USING btree FAMILY dump_test.op_family AS\E\n\s+ 1621 \QSTORAGE bigint;\E 1622 /xm, 1623 like => 1624 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1625 unlike => { exclude_dump_test_schema => 1, }, 1626 }, 1627 1628 'CREATE EVENT TRIGGER test_event_trigger' => { 1629 create_order => 33, 1630 create_sql => 'CREATE EVENT TRIGGER test_event_trigger 1631 ON ddl_command_start 1632 EXECUTE FUNCTION dump_test.event_trigger_func();', 1633 regexp => qr/^ 1634 \QCREATE EVENT TRIGGER test_event_trigger \E 1635 \QON ddl_command_start\E 1636 \n\s+\QEXECUTE FUNCTION dump_test.event_trigger_func();\E 1637 /xm, 1638 like => { %full_runs, section_post_data => 1, }, 1639 }, 1640 1641 'CREATE TRIGGER test_trigger' => { 1642 create_order => 31, 1643 create_sql => 'CREATE TRIGGER test_trigger 1644 BEFORE INSERT ON dump_test.test_table 1645 FOR EACH ROW WHEN (NEW.col1 > 10) 1646 EXECUTE FUNCTION dump_test.trigger_func();', 1647 regexp => qr/^ 1648 \QCREATE TRIGGER test_trigger BEFORE INSERT ON dump_test.test_table \E 1649 \QFOR EACH ROW WHEN ((new.col1 > 10)) \E 1650 \QEXECUTE FUNCTION dump_test.trigger_func();\E 1651 /xm, 1652 like => { 1653 %full_runs, 1654 %dump_test_schema_runs, 1655 only_dump_test_table => 1, 1656 section_post_data => 1, 1657 }, 1658 unlike => { 1659 exclude_test_table => 1, 1660 exclude_dump_test_schema => 1, 1661 }, 1662 }, 1663 1664 'CREATE TYPE dump_test.planets AS ENUM' => { 1665 create_order => 37, 1666 create_sql => 'CREATE TYPE dump_test.planets 1667 AS ENUM ( \'venus\', \'earth\', \'mars\' );', 1668 regexp => qr/^ 1669 \QCREATE TYPE dump_test.planets AS ENUM (\E 1670 \n\s+'venus', 1671 \n\s+'earth', 1672 \n\s+'mars' 1673 \n\);/xm, 1674 like => 1675 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1676 unlike => { 1677 binary_upgrade => 1, 1678 exclude_dump_test_schema => 1, 1679 }, 1680 }, 1681 1682 'CREATE TYPE dump_test.planets AS ENUM pg_upgrade' => { 1683 regexp => qr/^ 1684 \QCREATE TYPE dump_test.planets AS ENUM (\E 1685 \n\);.*^ 1686 \QALTER TYPE dump_test.planets ADD VALUE 'venus';\E 1687 \n.*^ 1688 \QALTER TYPE dump_test.planets ADD VALUE 'earth';\E 1689 \n.*^ 1690 \QALTER TYPE dump_test.planets ADD VALUE 'mars';\E 1691 \n/xms, 1692 like => { binary_upgrade => 1, }, 1693 }, 1694 1695 'CREATE TYPE dump_test.textrange AS RANGE' => { 1696 create_order => 38, 1697 create_sql => 'CREATE TYPE dump_test.textrange 1698 AS RANGE (subtype=text, collation="C");', 1699 regexp => qr/^ 1700 \QCREATE TYPE dump_test.textrange AS RANGE (\E 1701 \n\s+\Qsubtype = text,\E 1702 \n\s+\Qcollation = pg_catalog."C"\E 1703 \n\);/xm, 1704 like => 1705 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1706 unlike => { exclude_dump_test_schema => 1, }, 1707 }, 1708 1709 'CREATE TYPE dump_test.int42' => { 1710 create_order => 39, 1711 create_sql => 'CREATE TYPE dump_test.int42;', 1712 regexp => qr/^\QCREATE TYPE dump_test.int42;\E/m, 1713 like => 1714 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1715 unlike => { exclude_dump_test_schema => 1, }, 1716 }, 1717 1718 'CREATE TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1' => { 1719 create_order => 80, 1720 create_sql => 1721 'CREATE TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1 (copy=english);', 1722 regexp => qr/^ 1723 \QCREATE TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1 (\E\n 1724 \s+\QPARSER = pg_catalog."default" );\E/xm, 1725 like => 1726 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1727 unlike => { exclude_dump_test_schema => 1, }, 1728 }, 1729 1730 'ALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1 ...' => { 1731 regexp => qr/^ 1732 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1733 \s+\QADD MAPPING FOR asciiword WITH english_stem;\E\n 1734 \n 1735 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1736 \s+\QADD MAPPING FOR word WITH english_stem;\E\n 1737 \n 1738 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1739 \s+\QADD MAPPING FOR numword WITH simple;\E\n 1740 \n 1741 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1742 \s+\QADD MAPPING FOR email WITH simple;\E\n 1743 \n 1744 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1745 \s+\QADD MAPPING FOR url WITH simple;\E\n 1746 \n 1747 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1748 \s+\QADD MAPPING FOR host WITH simple;\E\n 1749 \n 1750 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1751 \s+\QADD MAPPING FOR sfloat WITH simple;\E\n 1752 \n 1753 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1754 \s+\QADD MAPPING FOR version WITH simple;\E\n 1755 \n 1756 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1757 \s+\QADD MAPPING FOR hword_numpart WITH simple;\E\n 1758 \n 1759 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1760 \s+\QADD MAPPING FOR hword_part WITH english_stem;\E\n 1761 \n 1762 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1763 \s+\QADD MAPPING FOR hword_asciipart WITH english_stem;\E\n 1764 \n 1765 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1766 \s+\QADD MAPPING FOR numhword WITH simple;\E\n 1767 \n 1768 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1769 \s+\QADD MAPPING FOR asciihword WITH english_stem;\E\n 1770 \n 1771 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1772 \s+\QADD MAPPING FOR hword WITH english_stem;\E\n 1773 \n 1774 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1775 \s+\QADD MAPPING FOR url_path WITH simple;\E\n 1776 \n 1777 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1778 \s+\QADD MAPPING FOR file WITH simple;\E\n 1779 \n 1780 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1781 \s+\QADD MAPPING FOR "float" WITH simple;\E\n 1782 \n 1783 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1784 \s+\QADD MAPPING FOR "int" WITH simple;\E\n 1785 \n 1786 \QALTER TEXT SEARCH CONFIGURATION dump_test.alt_ts_conf1\E\n 1787 \s+\QADD MAPPING FOR uint WITH simple;\E\n 1788 \n 1789 /xm, 1790 like => 1791 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1792 unlike => { exclude_dump_test_schema => 1, }, 1793 }, 1794 1795 'CREATE TEXT SEARCH TEMPLATE dump_test.alt_ts_temp1' => { 1796 create_order => 81, 1797 create_sql => 1798 'CREATE TEXT SEARCH TEMPLATE dump_test.alt_ts_temp1 (lexize=dsimple_lexize);', 1799 regexp => qr/^ 1800 \QCREATE TEXT SEARCH TEMPLATE dump_test.alt_ts_temp1 (\E\n 1801 \s+\QLEXIZE = dsimple_lexize );\E/xm, 1802 like => 1803 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1804 unlike => { exclude_dump_test_schema => 1, }, 1805 }, 1806 1807 'CREATE TEXT SEARCH PARSER dump_test.alt_ts_prs1' => { 1808 create_order => 82, 1809 create_sql => 'CREATE TEXT SEARCH PARSER dump_test.alt_ts_prs1 1810 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);', 1811 regexp => qr/^ 1812 \QCREATE TEXT SEARCH PARSER dump_test.alt_ts_prs1 (\E\n 1813 \s+\QSTART = prsd_start,\E\n 1814 \s+\QGETTOKEN = prsd_nexttoken,\E\n 1815 \s+\QEND = prsd_end,\E\n 1816 \s+\QLEXTYPES = prsd_lextype );\E\n 1817 /xm, 1818 like => 1819 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1820 unlike => { exclude_dump_test_schema => 1, }, 1821 }, 1822 1823 'CREATE TEXT SEARCH DICTIONARY dump_test.alt_ts_dict1' => { 1824 create_order => 83, 1825 create_sql => 1826 'CREATE TEXT SEARCH DICTIONARY dump_test.alt_ts_dict1 (template=simple);', 1827 regexp => qr/^ 1828 \QCREATE TEXT SEARCH DICTIONARY dump_test.alt_ts_dict1 (\E\n 1829 \s+\QTEMPLATE = pg_catalog.simple );\E\n 1830 /xm, 1831 like => 1832 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1833 unlike => { exclude_dump_test_schema => 1, }, 1834 }, 1835 1836 'CREATE FUNCTION dump_test.int42_in' => { 1837 create_order => 40, 1838 create_sql => 'CREATE FUNCTION dump_test.int42_in(cstring) 1839 RETURNS dump_test.int42 AS \'int4in\' 1840 LANGUAGE internal STRICT IMMUTABLE;', 1841 regexp => qr/^ 1842 \QCREATE FUNCTION dump_test.int42_in(cstring) RETURNS dump_test.int42\E 1843 \n\s+\QLANGUAGE internal IMMUTABLE STRICT\E 1844 \n\s+AS\ \$\$int4in\$\$; 1845 /xm, 1846 like => 1847 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1848 unlike => { exclude_dump_test_schema => 1, }, 1849 }, 1850 1851 'CREATE FUNCTION dump_test.int42_out' => { 1852 create_order => 41, 1853 create_sql => 'CREATE FUNCTION dump_test.int42_out(dump_test.int42) 1854 RETURNS cstring AS \'int4out\' 1855 LANGUAGE internal STRICT IMMUTABLE;', 1856 regexp => qr/^ 1857 \QCREATE FUNCTION dump_test.int42_out(dump_test.int42) RETURNS cstring\E 1858 \n\s+\QLANGUAGE internal IMMUTABLE STRICT\E 1859 \n\s+AS\ \$\$int4out\$\$; 1860 /xm, 1861 like => 1862 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1863 unlike => { exclude_dump_test_schema => 1, }, 1864 }, 1865 1866 'CREATE FUNCTION ... SUPPORT' => { 1867 create_order => 41, 1868 create_sql => 1869 'CREATE FUNCTION dump_test.func_with_support() RETURNS int LANGUAGE sql AS $$ SELECT 1 $$ SUPPORT varchar_support;', 1870 regexp => qr/^ 1871 \QCREATE FUNCTION dump_test.func_with_support() RETURNS integer\E 1872 \n\s+\QLANGUAGE sql SUPPORT varchar_support\E 1873 \n\s+AS\ \$\$\Q SELECT 1 \E\$\$; 1874 /xm, 1875 like => 1876 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1877 unlike => { exclude_dump_test_schema => 1, }, 1878 }, 1879 1880 'CREATE PROCEDURE dump_test.ptest1' => { 1881 create_order => 41, 1882 create_sql => 'CREATE PROCEDURE dump_test.ptest1(a int) 1883 LANGUAGE SQL AS $$ INSERT INTO dump_test.test_table (col1) VALUES (a) $$;', 1884 regexp => qr/^ 1885 \QCREATE PROCEDURE dump_test.ptest1(a integer)\E 1886 \n\s+\QLANGUAGE sql\E 1887 \n\s+AS\ \$\$\Q INSERT INTO dump_test.test_table (col1) VALUES (a) \E\$\$; 1888 /xm, 1889 like => 1890 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1891 unlike => { exclude_dump_test_schema => 1, }, 1892 }, 1893 1894 'CREATE TYPE dump_test.int42 populated' => { 1895 create_order => 42, 1896 create_sql => 'CREATE TYPE dump_test.int42 ( 1897 internallength = 4, 1898 input = dump_test.int42_in, 1899 output = dump_test.int42_out, 1900 alignment = int4, 1901 default = 42, 1902 passedbyvalue);', 1903 regexp => qr/^ 1904 \QCREATE TYPE dump_test.int42 (\E 1905 \n\s+\QINTERNALLENGTH = 4,\E 1906 \n\s+\QINPUT = dump_test.int42_in,\E 1907 \n\s+\QOUTPUT = dump_test.int42_out,\E 1908 \n\s+\QDEFAULT = '42',\E 1909 \n\s+\QALIGNMENT = int4,\E 1910 \n\s+\QSTORAGE = plain,\E 1911 \n\s+PASSEDBYVALUE\n\); 1912 /xm, 1913 like => 1914 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1915 unlike => { exclude_dump_test_schema => 1, }, 1916 }, 1917 1918 'CREATE TYPE dump_test.composite' => { 1919 create_order => 43, 1920 create_sql => 'CREATE TYPE dump_test.composite AS ( 1921 f1 int, 1922 f2 dump_test.int42 1923 );', 1924 regexp => qr/^ 1925 \QCREATE TYPE dump_test.composite AS (\E 1926 \n\s+\Qf1 integer,\E 1927 \n\s+\Qf2 dump_test.int42\E 1928 \n\); 1929 /xm, 1930 like => 1931 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1932 unlike => { exclude_dump_test_schema => 1, }, 1933 }, 1934 1935 'CREATE TYPE dump_test.undefined' => { 1936 create_order => 39, 1937 create_sql => 'CREATE TYPE dump_test.undefined;', 1938 regexp => qr/^\QCREATE TYPE dump_test.undefined;\E/m, 1939 like => 1940 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1941 unlike => { exclude_dump_test_schema => 1, }, 1942 }, 1943 1944 'CREATE FOREIGN DATA WRAPPER dummy' => { 1945 create_order => 35, 1946 create_sql => 'CREATE FOREIGN DATA WRAPPER dummy;', 1947 regexp => qr/CREATE FOREIGN DATA WRAPPER dummy;/m, 1948 like => { %full_runs, section_pre_data => 1, }, 1949 }, 1950 1951 'CREATE SERVER s1 FOREIGN DATA WRAPPER dummy' => { 1952 create_order => 36, 1953 create_sql => 'CREATE SERVER s1 FOREIGN DATA WRAPPER dummy;', 1954 regexp => qr/CREATE SERVER s1 FOREIGN DATA WRAPPER dummy;/m, 1955 like => { %full_runs, section_pre_data => 1, }, 1956 }, 1957 1958 'CREATE FOREIGN TABLE dump_test.foreign_table SERVER s1' => { 1959 create_order => 88, 1960 create_sql => 1961 'CREATE FOREIGN TABLE dump_test.foreign_table (c1 int options (column_name \'col1\')) 1962 SERVER s1 OPTIONS (schema_name \'x1\');', 1963 regexp => qr/ 1964 \QCREATE FOREIGN TABLE dump_test.foreign_table (\E\n 1965 \s+\Qc1 integer\E\n 1966 \Q)\E\n 1967 \QSERVER s1\E\n 1968 \QOPTIONS (\E\n 1969 \s+\Qschema_name 'x1'\E\n 1970 \Q);\E\n 1971 /xm, 1972 like => 1973 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 1974 unlike => { exclude_dump_test_schema => 1, }, 1975 }, 1976 1977 'CREATE USER MAPPING FOR regress_dump_test_role SERVER s1' => { 1978 create_order => 86, 1979 create_sql => 1980 'CREATE USER MAPPING FOR regress_dump_test_role SERVER s1;', 1981 regexp => 1982 qr/CREATE USER MAPPING FOR regress_dump_test_role SERVER s1;/m, 1983 like => { %full_runs, section_pre_data => 1, }, 1984 }, 1985 1986 'CREATE TRANSFORM FOR int' => { 1987 create_order => 34, 1988 create_sql => 1989 'CREATE TRANSFORM FOR int LANGUAGE SQL (FROM SQL WITH FUNCTION prsd_lextype(internal), TO SQL WITH FUNCTION int4recv(internal));', 1990 regexp => 1991 qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL WITH FUNCTION pg_catalog\.prsd_lextype\(internal\), TO SQL WITH FUNCTION pg_catalog\.int4recv\(internal\)\);/m, 1992 like => { %full_runs, section_pre_data => 1, }, 1993 }, 1994 1995 'CREATE LANGUAGE pltestlang' => { 1996 create_order => 18, 1997 create_sql => 'CREATE LANGUAGE pltestlang 1998 HANDLER dump_test.pltestlang_call_handler;', 1999 regexp => qr/^ 2000 \QCREATE PROCEDURAL LANGUAGE pltestlang \E 2001 \QHANDLER dump_test.pltestlang_call_handler;\E 2002 /xm, 2003 like => { %full_runs, section_pre_data => 1, }, 2004 unlike => { exclude_dump_test_schema => 1, }, 2005 }, 2006 2007 'CREATE MATERIALIZED VIEW matview' => { 2008 create_order => 20, 2009 create_sql => 'CREATE MATERIALIZED VIEW dump_test.matview (col1) AS 2010 SELECT col1 FROM dump_test.test_table;', 2011 regexp => qr/^ 2012 \QCREATE MATERIALIZED VIEW dump_test.matview AS\E 2013 \n\s+\QSELECT test_table.col1\E 2014 \n\s+\QFROM dump_test.test_table\E 2015 \n\s+\QWITH NO DATA;\E 2016 /xm, 2017 like => 2018 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2019 unlike => { exclude_dump_test_schema => 1, }, 2020 }, 2021 2022 'CREATE MATERIALIZED VIEW matview_second' => { 2023 create_order => 21, 2024 create_sql => 'CREATE MATERIALIZED VIEW 2025 dump_test.matview_second (col1) AS 2026 SELECT * FROM dump_test.matview;', 2027 regexp => qr/^ 2028 \QCREATE MATERIALIZED VIEW dump_test.matview_second AS\E 2029 \n\s+\QSELECT matview.col1\E 2030 \n\s+\QFROM dump_test.matview\E 2031 \n\s+\QWITH NO DATA;\E 2032 /xm, 2033 like => 2034 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2035 unlike => { exclude_dump_test_schema => 1, }, 2036 }, 2037 2038 'CREATE MATERIALIZED VIEW matview_third' => { 2039 create_order => 58, 2040 create_sql => 'CREATE MATERIALIZED VIEW 2041 dump_test.matview_third (col1) AS 2042 SELECT * FROM dump_test.matview_second WITH NO DATA;', 2043 regexp => qr/^ 2044 \QCREATE MATERIALIZED VIEW dump_test.matview_third AS\E 2045 \n\s+\QSELECT matview_second.col1\E 2046 \n\s+\QFROM dump_test.matview_second\E 2047 \n\s+\QWITH NO DATA;\E 2048 /xm, 2049 like => 2050 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2051 unlike => { exclude_dump_test_schema => 1, }, 2052 }, 2053 2054 'CREATE MATERIALIZED VIEW matview_fourth' => { 2055 create_order => 59, 2056 create_sql => 'CREATE MATERIALIZED VIEW 2057 dump_test.matview_fourth (col1) AS 2058 SELECT * FROM dump_test.matview_third WITH NO DATA;', 2059 regexp => qr/^ 2060 \QCREATE MATERIALIZED VIEW dump_test.matview_fourth AS\E 2061 \n\s+\QSELECT matview_third.col1\E 2062 \n\s+\QFROM dump_test.matview_third\E 2063 \n\s+\QWITH NO DATA;\E 2064 /xm, 2065 like => 2066 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2067 unlike => { exclude_dump_test_schema => 1, }, 2068 }, 2069 2070 'CREATE POLICY p1 ON test_table' => { 2071 create_order => 22, 2072 create_sql => 'CREATE POLICY p1 ON dump_test.test_table 2073 USING (true) 2074 WITH CHECK (true);', 2075 regexp => qr/^ 2076 \QCREATE POLICY p1 ON dump_test.test_table \E 2077 \QUSING (true) WITH CHECK (true);\E 2078 /xm, 2079 like => { 2080 %full_runs, 2081 %dump_test_schema_runs, 2082 only_dump_test_table => 1, 2083 section_post_data => 1, 2084 }, 2085 unlike => { 2086 exclude_dump_test_schema => 1, 2087 exclude_test_table => 1, 2088 }, 2089 }, 2090 2091 'CREATE POLICY p2 ON test_table FOR SELECT' => { 2092 create_order => 24, 2093 create_sql => 'CREATE POLICY p2 ON dump_test.test_table 2094 FOR SELECT TO regress_dump_test_role USING (true);', 2095 regexp => qr/^ 2096 \QCREATE POLICY p2 ON dump_test.test_table FOR SELECT TO regress_dump_test_role \E 2097 \QUSING (true);\E 2098 /xm, 2099 like => { 2100 %full_runs, 2101 %dump_test_schema_runs, 2102 only_dump_test_table => 1, 2103 section_post_data => 1, 2104 }, 2105 unlike => { 2106 exclude_dump_test_schema => 1, 2107 exclude_test_table => 1, 2108 }, 2109 }, 2110 2111 'CREATE POLICY p3 ON test_table FOR INSERT' => { 2112 create_order => 25, 2113 create_sql => 'CREATE POLICY p3 ON dump_test.test_table 2114 FOR INSERT TO regress_dump_test_role WITH CHECK (true);', 2115 regexp => qr/^ 2116 \QCREATE POLICY p3 ON dump_test.test_table FOR INSERT \E 2117 \QTO regress_dump_test_role WITH CHECK (true);\E 2118 /xm, 2119 like => { 2120 %full_runs, 2121 %dump_test_schema_runs, 2122 only_dump_test_table => 1, 2123 section_post_data => 1, 2124 }, 2125 unlike => { 2126 exclude_dump_test_schema => 1, 2127 exclude_test_table => 1, 2128 }, 2129 }, 2130 2131 'CREATE POLICY p4 ON test_table FOR UPDATE' => { 2132 create_order => 26, 2133 create_sql => 'CREATE POLICY p4 ON dump_test.test_table FOR UPDATE 2134 TO regress_dump_test_role USING (true) WITH CHECK (true);', 2135 regexp => qr/^ 2136 \QCREATE POLICY p4 ON dump_test.test_table FOR UPDATE TO regress_dump_test_role \E 2137 \QUSING (true) WITH CHECK (true);\E 2138 /xm, 2139 like => { 2140 %full_runs, 2141 %dump_test_schema_runs, 2142 only_dump_test_table => 1, 2143 section_post_data => 1, 2144 }, 2145 unlike => { 2146 exclude_dump_test_schema => 1, 2147 exclude_test_table => 1, 2148 }, 2149 }, 2150 2151 'CREATE POLICY p5 ON test_table FOR DELETE' => { 2152 create_order => 27, 2153 create_sql => 'CREATE POLICY p5 ON dump_test.test_table 2154 FOR DELETE TO regress_dump_test_role USING (true);', 2155 regexp => qr/^ 2156 \QCREATE POLICY p5 ON dump_test.test_table FOR DELETE \E 2157 \QTO regress_dump_test_role USING (true);\E 2158 /xm, 2159 like => { 2160 %full_runs, 2161 %dump_test_schema_runs, 2162 only_dump_test_table => 1, 2163 section_post_data => 1, 2164 }, 2165 unlike => { 2166 exclude_dump_test_schema => 1, 2167 exclude_test_table => 1, 2168 }, 2169 }, 2170 2171 'CREATE POLICY p6 ON test_table AS RESTRICTIVE' => { 2172 create_order => 27, 2173 create_sql => 'CREATE POLICY p6 ON dump_test.test_table AS RESTRICTIVE 2174 USING (false);', 2175 regexp => qr/^ 2176 \QCREATE POLICY p6 ON dump_test.test_table AS RESTRICTIVE \E 2177 \QUSING (false);\E 2178 /xm, 2179 like => { 2180 %full_runs, 2181 %dump_test_schema_runs, 2182 only_dump_test_table => 1, 2183 section_post_data => 1, 2184 }, 2185 unlike => { 2186 exclude_dump_test_schema => 1, 2187 exclude_test_table => 1, 2188 }, 2189 }, 2190 2191 'CREATE PUBLICATION pub1' => { 2192 create_order => 50, 2193 create_sql => 'CREATE PUBLICATION pub1;', 2194 regexp => qr/^ 2195 \QCREATE PUBLICATION pub1 WITH (publish = 'insert, update, delete, truncate');\E 2196 /xm, 2197 like => { %full_runs, section_post_data => 1, }, 2198 }, 2199 2200 'CREATE PUBLICATION pub2' => { 2201 create_order => 50, 2202 create_sql => 'CREATE PUBLICATION pub2 2203 FOR ALL TABLES 2204 WITH (publish = \'\');', 2205 regexp => qr/^ 2206 \QCREATE PUBLICATION pub2 FOR ALL TABLES WITH (publish = '');\E 2207 /xm, 2208 like => { %full_runs, section_post_data => 1, }, 2209 }, 2210 2211 'CREATE SUBSCRIPTION sub1' => { 2212 create_order => 50, 2213 create_sql => 'CREATE SUBSCRIPTION sub1 2214 CONNECTION \'dbname=doesnotexist\' PUBLICATION pub1 2215 WITH (connect = false);', 2216 regexp => qr/^ 2217 \QCREATE SUBSCRIPTION sub1 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub1');\E 2218 /xm, 2219 like => { %full_runs, section_post_data => 1, }, 2220 }, 2221 2222 'ALTER PUBLICATION pub1 ADD TABLE test_table' => { 2223 create_order => 51, 2224 create_sql => 2225 'ALTER PUBLICATION pub1 ADD TABLE dump_test.test_table;', 2226 regexp => qr/^ 2227 \QALTER PUBLICATION pub1 ADD TABLE ONLY dump_test.test_table;\E 2228 /xm, 2229 like => { %full_runs, section_post_data => 1, }, 2230 unlike => { 2231 exclude_dump_test_schema => 1, 2232 exclude_test_table => 1, 2233 }, 2234 }, 2235 2236 'ALTER PUBLICATION pub1 ADD TABLE test_second_table' => { 2237 create_order => 52, 2238 create_sql => 2239 'ALTER PUBLICATION pub1 ADD TABLE dump_test.test_second_table;', 2240 regexp => qr/^ 2241 \QALTER PUBLICATION pub1 ADD TABLE ONLY dump_test.test_second_table;\E 2242 /xm, 2243 like => { %full_runs, section_post_data => 1, }, 2244 unlike => { exclude_dump_test_schema => 1, }, 2245 }, 2246 2247 'CREATE SCHEMA public' => { 2248 regexp => qr/^CREATE SCHEMA public;/m, 2249 2250 # this shouldn't ever get emitted anymore 2251 like => {}, 2252 }, 2253 2254 'CREATE SCHEMA dump_test' => { 2255 create_order => 2, 2256 create_sql => 'CREATE SCHEMA dump_test;', 2257 regexp => qr/^CREATE SCHEMA dump_test;/m, 2258 like => 2259 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2260 unlike => { exclude_dump_test_schema => 1, }, 2261 }, 2262 2263 'CREATE SCHEMA dump_test_second_schema' => { 2264 create_order => 9, 2265 create_sql => 'CREATE SCHEMA dump_test_second_schema;', 2266 regexp => qr/^CREATE SCHEMA dump_test_second_schema;/m, 2267 like => { 2268 %full_runs, 2269 role => 1, 2270 section_pre_data => 1, 2271 }, 2272 }, 2273 2274 'CREATE TABLE test_table' => { 2275 create_order => 3, 2276 create_sql => 'CREATE TABLE dump_test.test_table ( 2277 col1 serial primary key, 2278 col2 text, 2279 col3 text, 2280 col4 text, 2281 CHECK (col1 <= 1000) 2282 ) WITH (autovacuum_enabled = false, fillfactor=80);', 2283 regexp => qr/^ 2284 \QCREATE TABLE dump_test.test_table (\E\n 2285 \s+\Qcol1 integer NOT NULL,\E\n 2286 \s+\Qcol2 text,\E\n 2287 \s+\Qcol3 text,\E\n 2288 \s+\Qcol4 text,\E\n 2289 \s+\QCONSTRAINT test_table_col1_check CHECK ((col1 <= 1000))\E\n 2290 \Q)\E\n 2291 \QWITH (autovacuum_enabled='false', fillfactor='80');\E\n/xm, 2292 like => { 2293 %full_runs, 2294 %dump_test_schema_runs, 2295 only_dump_test_table => 1, 2296 section_pre_data => 1, 2297 }, 2298 unlike => { 2299 exclude_dump_test_schema => 1, 2300 exclude_test_table => 1, 2301 }, 2302 }, 2303 2304 'CREATE TABLE fk_reference_test_table' => { 2305 create_order => 21, 2306 create_sql => 'CREATE TABLE dump_test.fk_reference_test_table ( 2307 col1 int primary key references dump_test.test_table 2308 );', 2309 regexp => qr/^ 2310 \QCREATE TABLE dump_test.fk_reference_test_table (\E 2311 \n\s+\Qcol1 integer NOT NULL\E 2312 \n\); 2313 /xm, 2314 like => 2315 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2316 unlike => { exclude_dump_test_schema => 1, }, 2317 }, 2318 2319 'CREATE TABLE test_second_table' => { 2320 create_order => 6, 2321 create_sql => 'CREATE TABLE dump_test.test_second_table ( 2322 col1 int, 2323 col2 text 2324 );', 2325 regexp => qr/^ 2326 \QCREATE TABLE dump_test.test_second_table (\E 2327 \n\s+\Qcol1 integer,\E 2328 \n\s+\Qcol2 text\E 2329 \n\); 2330 /xm, 2331 like => 2332 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2333 unlike => { exclude_dump_test_schema => 1, }, 2334 }, 2335 2336 'CREATE TABLE measurement PARTITIONED BY' => { 2337 create_order => 90, 2338 create_sql => 'CREATE TABLE dump_test.measurement ( 2339 city_id serial not null, 2340 logdate date not null, 2341 peaktemp int CHECK (peaktemp >= -460), 2342 unitsales int 2343 ) PARTITION BY RANGE (logdate);', 2344 regexp => qr/^ 2345 \Q-- Name: measurement;\E.*\n 2346 \Q--\E\n\n 2347 \QCREATE TABLE dump_test.measurement (\E\n 2348 \s+\Qcity_id integer NOT NULL,\E\n 2349 \s+\Qlogdate date NOT NULL,\E\n 2350 \s+\Qpeaktemp integer,\E\n 2351 \s+\Qunitsales integer,\E\n 2352 \s+\QCONSTRAINT measurement_peaktemp_check CHECK ((peaktemp >= '-460'::integer))\E\n 2353 \)\n 2354 \QPARTITION BY RANGE (logdate);\E\n 2355 /xm, 2356 like => 2357 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2358 unlike => { 2359 binary_upgrade => 1, 2360 exclude_dump_test_schema => 1, 2361 }, 2362 }, 2363 2364 'Partition measurement_y2006m2 creation' => { 2365 create_order => 91, 2366 create_sql => 2367 'CREATE TABLE dump_test_second_schema.measurement_y2006m2 2368 PARTITION OF dump_test.measurement ( 2369 unitsales DEFAULT 0 CHECK (unitsales >= 0) 2370 ) 2371 FOR VALUES FROM (\'2006-02-01\') TO (\'2006-03-01\');', 2372 regexp => qr/^ 2373 \QCREATE TABLE dump_test_second_schema.measurement_y2006m2 (\E\n 2374 \s+\Qcity_id integer DEFAULT nextval('dump_test.measurement_city_id_seq'::regclass) NOT NULL,\E\n 2375 \s+\Qlogdate date NOT NULL,\E\n 2376 \s+\Qpeaktemp integer,\E\n 2377 \s+\Qunitsales integer DEFAULT 0,\E\n 2378 \s+\QCONSTRAINT measurement_peaktemp_check CHECK ((peaktemp >= '-460'::integer)),\E\n 2379 \s+\QCONSTRAINT measurement_y2006m2_unitsales_check CHECK ((unitsales >= 0))\E\n 2380 \);\n 2381 /xm, 2382 like => { 2383 %full_runs, 2384 section_pre_data => 1, 2385 role => 1, 2386 binary_upgrade => 1, 2387 }, 2388 }, 2389 2390 'Creation of row-level trigger in partitioned table' => { 2391 create_order => 92, 2392 create_sql => 'CREATE TRIGGER test_trigger 2393 AFTER INSERT ON dump_test.measurement 2394 FOR EACH ROW EXECUTE PROCEDURE dump_test.trigger_func()', 2395 regexp => qr/^ 2396 \QCREATE TRIGGER test_trigger AFTER INSERT ON dump_test.measurement \E 2397 \QFOR EACH ROW \E 2398 \QEXECUTE FUNCTION dump_test.trigger_func();\E 2399 /xm, 2400 like => { 2401 %full_runs, %dump_test_schema_runs, section_post_data => 1, 2402 }, 2403 unlike => { 2404 exclude_dump_test_schema => 1, 2405 }, 2406 }, 2407 2408 'Disabled trigger on partition is altered' => { 2409 create_order => 93, 2410 create_sql => 2411 'CREATE TABLE dump_test_second_schema.measurement_y2006m3 2412 PARTITION OF dump_test.measurement 2413 FOR VALUES FROM (\'2006-03-01\') TO (\'2006-04-01\'); 2414 ALTER TABLE dump_test_second_schema.measurement_y2006m3 DISABLE TRIGGER test_trigger; 2415 CREATE TABLE dump_test_second_schema.measurement_y2006m4 2416 PARTITION OF dump_test.measurement 2417 FOR VALUES FROM (\'2006-04-01\') TO (\'2006-05-01\'); 2418 ALTER TABLE dump_test_second_schema.measurement_y2006m4 ENABLE REPLICA TRIGGER test_trigger; 2419 CREATE TABLE dump_test_second_schema.measurement_y2006m5 2420 PARTITION OF dump_test.measurement 2421 FOR VALUES FROM (\'2006-05-01\') TO (\'2006-06-01\'); 2422 ALTER TABLE dump_test_second_schema.measurement_y2006m5 ENABLE ALWAYS TRIGGER test_trigger; 2423 ', 2424 regexp => qr/^ 2425 \QALTER TABLE dump_test_second_schema.measurement_y2006m3 DISABLE TRIGGER test_trigger;\E 2426 /xm, 2427 like => { 2428 %full_runs, 2429 section_post_data => 1, 2430 role => 1, 2431 binary_upgrade => 1, 2432 }, 2433 }, 2434 2435 'Replica trigger on partition is altered' => { 2436 regexp => qr/^ 2437 \QALTER TABLE dump_test_second_schema.measurement_y2006m4 ENABLE REPLICA TRIGGER test_trigger;\E 2438 /xm, 2439 like => { 2440 %full_runs, 2441 section_post_data => 1, 2442 role => 1, 2443 binary_upgrade => 1, 2444 }, 2445 }, 2446 2447 'Always trigger on partition is altered' => { 2448 regexp => qr/^ 2449 \QALTER TABLE dump_test_second_schema.measurement_y2006m5 ENABLE ALWAYS TRIGGER test_trigger;\E 2450 /xm, 2451 like => { 2452 %full_runs, 2453 section_post_data => 1, 2454 role => 1, 2455 binary_upgrade => 1, 2456 }, 2457 }, 2458 2459 # We should never see the creation of a trigger on a partition 2460 'Disabled trigger on partition is not created' => { 2461 regexp => qr/CREATE TRIGGER test_trigger.*ON dump_test_second_schema/, 2462 like => {}, 2463 unlike => { %full_runs, %dump_test_schema_runs }, 2464 }, 2465 2466 # Triggers on partitions should not be dropped individually 2467 'Triggers on partitions are not dropped' => { 2468 regexp => qr/DROP TRIGGER test_trigger.*ON dump_test_second_schema/, 2469 like => {} 2470 }, 2471 2472 'CREATE TABLE test_fourth_table_zero_col' => { 2473 create_order => 6, 2474 create_sql => 'CREATE TABLE dump_test.test_fourth_table ( 2475 );', 2476 regexp => qr/^ 2477 \QCREATE TABLE dump_test.test_fourth_table (\E 2478 \n\); 2479 /xm, 2480 like => 2481 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2482 unlike => { exclude_dump_test_schema => 1, }, 2483 }, 2484 2485 'CREATE TABLE test_fifth_table' => { 2486 create_order => 53, 2487 create_sql => 'CREATE TABLE dump_test.test_fifth_table ( 2488 col1 integer, 2489 col2 boolean, 2490 col3 boolean, 2491 col4 bit(5), 2492 col5 float8 2493 );', 2494 regexp => qr/^ 2495 \QCREATE TABLE dump_test.test_fifth_table (\E 2496 \n\s+\Qcol1 integer,\E 2497 \n\s+\Qcol2 boolean,\E 2498 \n\s+\Qcol3 boolean,\E 2499 \n\s+\Qcol4 bit(5),\E 2500 \n\s+\Qcol5 double precision\E 2501 \n\); 2502 /xm, 2503 like => 2504 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2505 unlike => { exclude_dump_test_schema => 1, }, 2506 }, 2507 2508 'CREATE TABLE test_table_identity' => { 2509 create_order => 3, 2510 create_sql => 'CREATE TABLE dump_test.test_table_identity ( 2511 col1 int generated always as identity primary key, 2512 col2 text 2513 );', 2514 regexp => qr/^ 2515 \QCREATE TABLE dump_test.test_table_identity (\E\n 2516 \s+\Qcol1 integer NOT NULL,\E\n 2517 \s+\Qcol2 text\E\n 2518 \); 2519 .* 2520 \QALTER TABLE dump_test.test_table_identity ALTER COLUMN col1 ADD GENERATED ALWAYS AS IDENTITY (\E\n 2521 \s+\QSEQUENCE NAME dump_test.test_table_identity_col1_seq\E\n 2522 \s+\QSTART WITH 1\E\n 2523 \s+\QINCREMENT BY 1\E\n 2524 \s+\QNO MINVALUE\E\n 2525 \s+\QNO MAXVALUE\E\n 2526 \s+\QCACHE 1\E\n 2527 \); 2528 /xms, 2529 like => 2530 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2531 unlike => { exclude_dump_test_schema => 1, }, 2532 }, 2533 2534 'CREATE TABLE test_table_generated' => { 2535 create_order => 3, 2536 create_sql => 'CREATE TABLE dump_test.test_table_generated ( 2537 col1 int primary key, 2538 col2 int generated always as (col1 * 2) stored 2539 );', 2540 regexp => qr/^ 2541 \QCREATE TABLE dump_test.test_table_generated (\E\n 2542 \s+\Qcol1 integer NOT NULL,\E\n 2543 \s+\Qcol2 integer GENERATED ALWAYS AS ((col1 * 2)) STORED\E\n 2544 \); 2545 /xms, 2546 like => 2547 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2548 unlike => { exclude_dump_test_schema => 1, }, 2549 }, 2550 2551 'CREATE TABLE test_table_generated_child1 (without local columns)' => { 2552 create_order => 4, 2553 create_sql => 'CREATE TABLE dump_test.test_table_generated_child1 () 2554 INHERITS (dump_test.test_table_generated);', 2555 regexp => qr/^ 2556 \QCREATE TABLE dump_test.test_table_generated_child1 (\E\n 2557 \)\n 2558 \QINHERITS (dump_test.test_table_generated);\E\n 2559 /xms, 2560 like => 2561 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2562 unlike => { 2563 binary_upgrade => 1, 2564 exclude_dump_test_schema => 1, 2565 }, 2566 }, 2567 2568 'ALTER TABLE test_table_generated_child1' => { 2569 regexp => 2570 qr/^\QALTER TABLE ONLY dump_test.test_table_generated_child1 ALTER COLUMN col2 \E/m, 2571 2572 # should not get emitted 2573 like => {}, 2574 }, 2575 2576 'CREATE TABLE test_table_generated_child2 (with local columns)' => { 2577 create_order => 4, 2578 create_sql => 'CREATE TABLE dump_test.test_table_generated_child2 ( 2579 col1 int, 2580 col2 int 2581 ) INHERITS (dump_test.test_table_generated);', 2582 regexp => qr/^ 2583 \QCREATE TABLE dump_test.test_table_generated_child2 (\E\n 2584 \s+\Qcol1 integer,\E\n 2585 \s+\Qcol2 integer\E\n 2586 \)\n 2587 \QINHERITS (dump_test.test_table_generated);\E\n 2588 /xms, 2589 like => 2590 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2591 unlike => { 2592 binary_upgrade => 1, 2593 exclude_dump_test_schema => 1, 2594 }, 2595 }, 2596 2597 'CREATE TABLE table_with_stats' => { 2598 create_order => 98, 2599 create_sql => 'CREATE TABLE dump_test.table_index_stats ( 2600 col1 int, 2601 col2 int, 2602 col3 int); 2603 CREATE INDEX index_with_stats 2604 ON dump_test.table_index_stats 2605 ((col1 + 1), col1, (col2 + 1), (col3 + 1)); 2606 ALTER INDEX dump_test.index_with_stats 2607 ALTER COLUMN 1 SET STATISTICS 400; 2608 ALTER INDEX dump_test.index_with_stats 2609 ALTER COLUMN 3 SET STATISTICS 500;', 2610 regexp => qr/^ 2611 \QALTER INDEX dump_test.index_with_stats ALTER COLUMN 1 SET STATISTICS 400;\E\n 2612 \QALTER INDEX dump_test.index_with_stats ALTER COLUMN 3 SET STATISTICS 500;\E\n 2613 /xms, 2614 like => 2615 { %full_runs, %dump_test_schema_runs, section_post_data => 1, }, 2616 unlike => { exclude_dump_test_schema => 1, }, 2617 }, 2618 2619 'CREATE TABLE test_inheritance_parent' => { 2620 create_order => 90, 2621 create_sql => 'CREATE TABLE dump_test.test_inheritance_parent ( 2622 col1 int NOT NULL, 2623 col2 int CHECK (col2 >= 42) 2624 );', 2625 regexp => qr/^ 2626 \QCREATE TABLE dump_test.test_inheritance_parent (\E\n 2627 \s+\Qcol1 integer NOT NULL,\E\n 2628 \s+\Qcol2 integer,\E\n 2629 \s+\QCONSTRAINT test_inheritance_parent_col2_check CHECK ((col2 >= 42))\E\n 2630 \Q);\E\n 2631 /xm, 2632 like => 2633 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2634 unlike => { exclude_dump_test_schema => 1, }, 2635 }, 2636 2637 'CREATE TABLE test_inheritance_child' => { 2638 create_order => 91, 2639 create_sql => 'CREATE TABLE dump_test.test_inheritance_child ( 2640 col1 int NOT NULL, 2641 CONSTRAINT test_inheritance_child CHECK (col2 >= 142857) 2642 ) INHERITS (dump_test.test_inheritance_parent);', 2643 regexp => qr/^ 2644 \QCREATE TABLE dump_test.test_inheritance_child (\E\n 2645 \s+\Qcol1 integer,\E\n 2646 \s+\QCONSTRAINT test_inheritance_child CHECK ((col2 >= 142857))\E\n 2647 \)\n 2648 \QINHERITS (dump_test.test_inheritance_parent);\E\n 2649 /xm, 2650 like => { 2651 %full_runs, %dump_test_schema_runs, section_pre_data => 1, 2652 }, 2653 unlike => { 2654 binary_upgrade => 1, 2655 exclude_dump_test_schema => 1, 2656 }, 2657 }, 2658 2659 'CREATE STATISTICS extended_stats_no_options' => { 2660 create_order => 97, 2661 create_sql => 'CREATE STATISTICS dump_test.test_ext_stats_no_options 2662 ON col1, col2 FROM dump_test.test_fifth_table', 2663 regexp => qr/^ 2664 \QCREATE STATISTICS dump_test.test_ext_stats_no_options ON col1, col2 FROM dump_test.test_fifth_table;\E 2665 /xms, 2666 like => 2667 { %full_runs, %dump_test_schema_runs, section_post_data => 1, }, 2668 unlike => { exclude_dump_test_schema => 1, }, 2669 }, 2670 2671 'CREATE STATISTICS extended_stats_options' => { 2672 create_order => 97, 2673 create_sql => 'CREATE STATISTICS dump_test.test_ext_stats_opts 2674 (ndistinct) ON col1, col2 FROM dump_test.test_fifth_table', 2675 regexp => qr/^ 2676 \QCREATE STATISTICS dump_test.test_ext_stats_opts (ndistinct) ON col1, col2 FROM dump_test.test_fifth_table;\E 2677 /xms, 2678 like => 2679 { %full_runs, %dump_test_schema_runs, section_post_data => 1, }, 2680 unlike => { exclude_dump_test_schema => 1, }, 2681 }, 2682 2683 'CREATE SEQUENCE test_table_col1_seq' => { 2684 regexp => qr/^ 2685 \QCREATE SEQUENCE dump_test.test_table_col1_seq\E 2686 \n\s+\QAS integer\E 2687 \n\s+\QSTART WITH 1\E 2688 \n\s+\QINCREMENT BY 1\E 2689 \n\s+\QNO MINVALUE\E 2690 \n\s+\QNO MAXVALUE\E 2691 \n\s+\QCACHE 1;\E 2692 /xm, 2693 like => { 2694 %full_runs, 2695 %dump_test_schema_runs, 2696 only_dump_test_table => 1, 2697 section_pre_data => 1, 2698 }, 2699 unlike => { exclude_dump_test_schema => 1, }, 2700 }, 2701 2702 'CREATE INDEX ON ONLY measurement' => { 2703 create_order => 92, 2704 create_sql => 2705 'CREATE INDEX ON dump_test.measurement (city_id, logdate);', 2706 regexp => qr/^ 2707 \QCREATE INDEX measurement_city_id_logdate_idx ON ONLY dump_test.measurement USING\E 2708 /xm, 2709 like => { 2710 binary_upgrade => 1, 2711 clean => 1, 2712 clean_if_exists => 1, 2713 createdb => 1, 2714 defaults => 1, 2715 exclude_test_table => 1, 2716 exclude_test_table_data => 1, 2717 no_blobs => 1, 2718 no_privs => 1, 2719 no_owner => 1, 2720 only_dump_test_schema => 1, 2721 pg_dumpall_dbprivs => 1, 2722 pg_dumpall_exclude => 1, 2723 schema_only => 1, 2724 section_post_data => 1, 2725 test_schema_plus_blobs => 1, 2726 }, 2727 unlike => { 2728 exclude_dump_test_schema => 1, 2729 only_dump_test_table => 1, 2730 pg_dumpall_globals => 1, 2731 pg_dumpall_globals_clean => 1, 2732 role => 1, 2733 section_pre_data => 1, 2734 }, 2735 }, 2736 2737 'ALTER TABLE measurement PRIMARY KEY' => { 2738 all_runs => 1, 2739 catch_all => 'CREATE ... commands', 2740 create_order => 93, 2741 create_sql => 2742 'ALTER TABLE dump_test.measurement ADD PRIMARY KEY (city_id, logdate);', 2743 regexp => qr/^ 2744 \QALTER TABLE ONLY dump_test.measurement\E \n^\s+ 2745 \QADD CONSTRAINT measurement_pkey PRIMARY KEY (city_id, logdate);\E 2746 /xm, 2747 like => 2748 { %full_runs, %dump_test_schema_runs, section_post_data => 1, }, 2749 unlike => { exclude_dump_test_schema => 1, }, 2750 }, 2751 2752 'CREATE INDEX ... ON measurement_y2006_m2' => { 2753 regexp => qr/^ 2754 \QCREATE INDEX measurement_y2006m2_city_id_logdate_idx ON dump_test_second_schema.measurement_y2006m2 \E 2755 /xm, 2756 like => { 2757 %full_runs, 2758 role => 1, 2759 section_post_data => 1, 2760 }, 2761 }, 2762 2763 'ALTER INDEX ... ATTACH PARTITION' => { 2764 regexp => qr/^ 2765 \QALTER INDEX dump_test.measurement_city_id_logdate_idx ATTACH PARTITION dump_test_second_schema.measurement_y2006m2_city_id_logdate_idx\E 2766 /xm, 2767 like => { 2768 %full_runs, 2769 role => 1, 2770 section_post_data => 1, 2771 }, 2772 }, 2773 2774 'ALTER INDEX ... ATTACH PARTITION (primary key)' => { 2775 all_runs => 1, 2776 catch_all => 'CREATE ... commands', 2777 regexp => qr/^ 2778 \QALTER INDEX dump_test.measurement_pkey ATTACH PARTITION dump_test_second_schema.measurement_y2006m2_pkey\E 2779 /xm, 2780 like => { 2781 binary_upgrade => 1, 2782 clean => 1, 2783 clean_if_exists => 1, 2784 createdb => 1, 2785 defaults => 1, 2786 exclude_dump_test_schema => 1, 2787 exclude_test_table => 1, 2788 exclude_test_table_data => 1, 2789 no_blobs => 1, 2790 no_privs => 1, 2791 no_owner => 1, 2792 pg_dumpall_dbprivs => 1, 2793 pg_dumpall_exclude => 1, 2794 role => 1, 2795 schema_only => 1, 2796 section_post_data => 1, 2797 }, 2798 unlike => { 2799 only_dump_test_schema => 1, 2800 only_dump_test_table => 1, 2801 pg_dumpall_globals => 1, 2802 pg_dumpall_globals_clean => 1, 2803 section_pre_data => 1, 2804 test_schema_plus_blobs => 1, 2805 }, 2806 }, 2807 2808 'CREATE VIEW test_view' => { 2809 create_order => 61, 2810 create_sql => 'CREATE VIEW dump_test.test_view 2811 WITH (check_option = \'local\', security_barrier = true) AS 2812 SELECT col1 FROM dump_test.test_table;', 2813 regexp => qr/^ 2814 \QCREATE VIEW dump_test.test_view WITH (security_barrier='true') AS\E 2815 \n\s+\QSELECT test_table.col1\E 2816 \n\s+\QFROM dump_test.test_table\E 2817 \n\s+\QWITH LOCAL CHECK OPTION;\E/xm, 2818 like => 2819 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2820 unlike => { exclude_dump_test_schema => 1, }, 2821 }, 2822 2823 'ALTER VIEW test_view SET DEFAULT' => { 2824 create_order => 62, 2825 create_sql => 2826 'ALTER VIEW dump_test.test_view ALTER COLUMN col1 SET DEFAULT 1;', 2827 regexp => qr/^ 2828 \QALTER TABLE ONLY dump_test.test_view ALTER COLUMN col1 SET DEFAULT 1;\E/xm, 2829 like => 2830 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 2831 unlike => { exclude_dump_test_schema => 1, }, 2832 }, 2833 2834 # FIXME 2835 'DROP SCHEMA public (for testing without public schema)' => { 2836 database => 'regress_pg_dump_test', 2837 create_order => 100, 2838 create_sql => 'DROP SCHEMA public;', 2839 regexp => qr/^DROP SCHEMA public;/m, 2840 like => {}, 2841 }, 2842 2843 'DROP SCHEMA public' => { 2844 regexp => qr/^DROP SCHEMA public;/m, 2845 2846 # this shouldn't ever get emitted anymore 2847 like => {}, 2848 }, 2849 2850 'DROP SCHEMA IF EXISTS public' => { 2851 regexp => qr/^DROP SCHEMA IF EXISTS public;/m, 2852 2853 # this shouldn't ever get emitted anymore 2854 like => {}, 2855 }, 2856 2857 'DROP EXTENSION plpgsql' => { 2858 regexp => qr/^DROP EXTENSION plpgsql;/m, 2859 2860 # this shouldn't ever get emitted anymore 2861 like => {}, 2862 }, 2863 2864 'DROP FUNCTION dump_test.pltestlang_call_handler()' => { 2865 regexp => qr/^DROP FUNCTION dump_test\.pltestlang_call_handler\(\);/m, 2866 like => { clean => 1, }, 2867 }, 2868 2869 'DROP LANGUAGE pltestlang' => { 2870 regexp => qr/^DROP PROCEDURAL LANGUAGE pltestlang;/m, 2871 like => { clean => 1, }, 2872 }, 2873 2874 'DROP SCHEMA dump_test' => { 2875 regexp => qr/^DROP SCHEMA dump_test;/m, 2876 like => { clean => 1, }, 2877 }, 2878 2879 'DROP SCHEMA dump_test_second_schema' => { 2880 regexp => qr/^DROP SCHEMA dump_test_second_schema;/m, 2881 like => { clean => 1, }, 2882 }, 2883 2884 'DROP TABLE test_table' => { 2885 regexp => qr/^DROP TABLE dump_test\.test_table;/m, 2886 like => { clean => 1, }, 2887 }, 2888 2889 'DROP TABLE fk_reference_test_table' => { 2890 regexp => qr/^DROP TABLE dump_test\.fk_reference_test_table;/m, 2891 like => { clean => 1, }, 2892 }, 2893 2894 'DROP TABLE test_second_table' => { 2895 regexp => qr/^DROP TABLE dump_test\.test_second_table;/m, 2896 like => { clean => 1, }, 2897 }, 2898 2899 'DROP EXTENSION IF EXISTS plpgsql' => { 2900 regexp => qr/^DROP EXTENSION IF EXISTS plpgsql;/m, 2901 2902 # this shouldn't ever get emitted anymore 2903 like => {}, 2904 }, 2905 2906 'DROP FUNCTION IF EXISTS dump_test.pltestlang_call_handler()' => { 2907 regexp => qr/^ 2908 \QDROP FUNCTION IF EXISTS dump_test.pltestlang_call_handler();\E 2909 /xm, 2910 like => { clean_if_exists => 1, }, 2911 }, 2912 2913 'DROP LANGUAGE IF EXISTS pltestlang' => { 2914 regexp => qr/^DROP PROCEDURAL LANGUAGE IF EXISTS pltestlang;/m, 2915 like => { clean_if_exists => 1, }, 2916 }, 2917 2918 'DROP SCHEMA IF EXISTS dump_test' => { 2919 regexp => qr/^DROP SCHEMA IF EXISTS dump_test;/m, 2920 like => { clean_if_exists => 1, }, 2921 }, 2922 2923 'DROP SCHEMA IF EXISTS dump_test_second_schema' => { 2924 regexp => qr/^DROP SCHEMA IF EXISTS dump_test_second_schema;/m, 2925 like => { clean_if_exists => 1, }, 2926 }, 2927 2928 'DROP TABLE IF EXISTS test_table' => { 2929 regexp => qr/^DROP TABLE IF EXISTS dump_test\.test_table;/m, 2930 like => { clean_if_exists => 1, }, 2931 }, 2932 2933 'DROP TABLE IF EXISTS test_second_table' => { 2934 regexp => qr/^DROP TABLE IF EXISTS dump_test\.test_second_table;/m, 2935 like => { clean_if_exists => 1, }, 2936 }, 2937 2938 'DROP ROLE regress_dump_test_role' => { 2939 regexp => qr/^ 2940 \QDROP ROLE regress_dump_test_role;\E 2941 /xm, 2942 like => { pg_dumpall_globals_clean => 1, }, 2943 }, 2944 2945 'DROP ROLE pg_' => { 2946 regexp => qr/^ 2947 \QDROP ROLE pg_\E.+; 2948 /xm, 2949 2950 # this shouldn't ever get emitted anywhere 2951 like => {}, 2952 }, 2953 2954 'GRANT USAGE ON SCHEMA dump_test_second_schema' => { 2955 create_order => 10, 2956 create_sql => 'GRANT USAGE ON SCHEMA dump_test_second_schema 2957 TO regress_dump_test_role;', 2958 regexp => qr/^ 2959 \QGRANT USAGE ON SCHEMA dump_test_second_schema TO regress_dump_test_role;\E 2960 /xm, 2961 like => { 2962 %full_runs, 2963 role => 1, 2964 section_pre_data => 1, 2965 }, 2966 unlike => { no_privs => 1, }, 2967 }, 2968 2969 'GRANT USAGE ON FOREIGN DATA WRAPPER dummy' => { 2970 create_order => 85, 2971 create_sql => 'GRANT USAGE ON FOREIGN DATA WRAPPER dummy 2972 TO regress_dump_test_role;', 2973 regexp => qr/^ 2974 \QGRANT ALL ON FOREIGN DATA WRAPPER dummy TO regress_dump_test_role;\E 2975 /xm, 2976 like => { %full_runs, section_pre_data => 1, }, 2977 unlike => { no_privs => 1, }, 2978 }, 2979 2980 'GRANT USAGE ON FOREIGN SERVER s1' => { 2981 create_order => 85, 2982 create_sql => 'GRANT USAGE ON FOREIGN SERVER s1 2983 TO regress_dump_test_role;', 2984 regexp => qr/^ 2985 \QGRANT ALL ON FOREIGN SERVER s1 TO regress_dump_test_role;\E 2986 /xm, 2987 like => { %full_runs, section_pre_data => 1, }, 2988 unlike => { no_privs => 1, }, 2989 }, 2990 2991 'GRANT USAGE ON DOMAIN dump_test.us_postal_code' => { 2992 create_order => 72, 2993 create_sql => 2994 'GRANT USAGE ON DOMAIN dump_test.us_postal_code TO regress_dump_test_role;', 2995 regexp => qr/^ 2996 \QGRANT ALL ON TYPE dump_test.us_postal_code TO regress_dump_test_role;\E 2997 /xm, 2998 like => 2999 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 3000 unlike => { 3001 exclude_dump_test_schema => 1, 3002 no_privs => 1, 3003 }, 3004 }, 3005 3006 'GRANT USAGE ON TYPE dump_test.int42' => { 3007 create_order => 87, 3008 create_sql => 3009 'GRANT USAGE ON TYPE dump_test.int42 TO regress_dump_test_role;', 3010 regexp => qr/^ 3011 \QGRANT ALL ON TYPE dump_test.int42 TO regress_dump_test_role;\E 3012 /xm, 3013 like => 3014 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 3015 unlike => { 3016 exclude_dump_test_schema => 1, 3017 no_privs => 1, 3018 }, 3019 }, 3020 3021 'GRANT USAGE ON TYPE dump_test.planets - ENUM' => { 3022 create_order => 66, 3023 create_sql => 3024 'GRANT USAGE ON TYPE dump_test.planets TO regress_dump_test_role;', 3025 regexp => qr/^ 3026 \QGRANT ALL ON TYPE dump_test.planets TO regress_dump_test_role;\E 3027 /xm, 3028 like => 3029 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 3030 unlike => { 3031 exclude_dump_test_schema => 1, 3032 no_privs => 1, 3033 }, 3034 }, 3035 3036 'GRANT USAGE ON TYPE dump_test.textrange - RANGE' => { 3037 create_order => 67, 3038 create_sql => 3039 'GRANT USAGE ON TYPE dump_test.textrange TO regress_dump_test_role;', 3040 regexp => qr/^ 3041 \QGRANT ALL ON TYPE dump_test.textrange TO regress_dump_test_role;\E 3042 /xm, 3043 like => 3044 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 3045 unlike => { 3046 exclude_dump_test_schema => 1, 3047 no_privs => 1, 3048 }, 3049 }, 3050 3051 'GRANT CREATE ON DATABASE dump_test' => { 3052 create_order => 48, 3053 create_sql => 3054 'GRANT CREATE ON DATABASE dump_test TO regress_dump_test_role;', 3055 regexp => qr/^ 3056 \QGRANT CREATE ON DATABASE dump_test TO regress_dump_test_role;\E 3057 /xm, 3058 like => { pg_dumpall_dbprivs => 1, }, 3059 }, 3060 3061 'GRANT SELECT ON TABLE test_table' => { 3062 create_order => 5, 3063 create_sql => 'GRANT SELECT ON TABLE dump_test.test_table 3064 TO regress_dump_test_role;', 3065 regexp => 3066 qr/^\QGRANT SELECT ON TABLE dump_test.test_table TO regress_dump_test_role;\E/m, 3067 like => { 3068 %full_runs, 3069 %dump_test_schema_runs, 3070 only_dump_test_table => 1, 3071 section_pre_data => 1, 3072 }, 3073 unlike => { 3074 exclude_dump_test_schema => 1, 3075 exclude_test_table => 1, 3076 no_privs => 1, 3077 }, 3078 }, 3079 3080 'GRANT SELECT ON TABLE measurement' => { 3081 create_order => 91, 3082 create_sql => 'GRANT SELECT ON 3083 TABLE dump_test.measurement 3084 TO regress_dump_test_role;', 3085 regexp => 3086 qr/^\QGRANT SELECT ON TABLE dump_test.measurement TO regress_dump_test_role;\E/m, 3087 like => 3088 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 3089 unlike => { 3090 exclude_dump_test_schema => 1, 3091 no_privs => 1, 3092 }, 3093 }, 3094 3095 'GRANT SELECT ON TABLE measurement_y2006m2' => { 3096 create_order => 94, 3097 create_sql => 'GRANT SELECT ON TABLE 3098 dump_test_second_schema.measurement_y2006m2, 3099 dump_test_second_schema.measurement_y2006m3, 3100 dump_test_second_schema.measurement_y2006m4, 3101 dump_test_second_schema.measurement_y2006m5 3102 TO regress_dump_test_role;', 3103 regexp => 3104 qr/^\QGRANT SELECT ON TABLE dump_test_second_schema.measurement_y2006m2 TO regress_dump_test_role;\E/m, 3105 like => { 3106 %full_runs, 3107 role => 1, 3108 section_pre_data => 1, 3109 }, 3110 unlike => { no_privs => 1, }, 3111 }, 3112 3113 'GRANT ALL ON LARGE OBJECT ...' => { 3114 create_order => 60, 3115 create_sql => 'DO $$ 3116 DECLARE myoid oid; 3117 BEGIN 3118 SELECT loid FROM pg_largeobject INTO myoid; 3119 EXECUTE \'GRANT ALL ON LARGE OBJECT \' || myoid || \' TO regress_dump_test_role;\'; 3120 END; 3121 $$;', 3122 regexp => qr/^ 3123 \QGRANT ALL ON LARGE OBJECT \E[0-9]+\Q TO regress_dump_test_role;\E 3124 /xm, 3125 like => { 3126 %full_runs, 3127 column_inserts => 1, 3128 data_only => 1, 3129 section_pre_data => 1, 3130 test_schema_plus_blobs => 1, 3131 binary_upgrade => 1, 3132 }, 3133 unlike => { 3134 no_blobs => 1, 3135 no_privs => 1, 3136 schema_only => 1, 3137 }, 3138 }, 3139 3140 'GRANT INSERT(col1) ON TABLE test_second_table' => { 3141 create_order => 8, 3142 create_sql => 3143 'GRANT INSERT (col1) ON TABLE dump_test.test_second_table 3144 TO regress_dump_test_role;', 3145 regexp => qr/^ 3146 \QGRANT INSERT(col1) ON TABLE dump_test.test_second_table TO regress_dump_test_role;\E 3147 /xm, 3148 like => 3149 { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, 3150 unlike => { 3151 exclude_dump_test_schema => 1, 3152 no_privs => 1, 3153 }, 3154 }, 3155 3156 'GRANT EXECUTE ON FUNCTION pg_sleep() TO regress_dump_test_role' => { 3157 create_order => 16, 3158 create_sql => 'GRANT EXECUTE ON FUNCTION pg_sleep(float8) 3159 TO regress_dump_test_role;', 3160 regexp => qr/^ 3161 \QGRANT ALL ON FUNCTION pg_catalog.pg_sleep(double precision) TO regress_dump_test_role;\E 3162 /xm, 3163 like => { %full_runs, section_pre_data => 1, }, 3164 unlike => { no_privs => 1, }, 3165 }, 3166 3167 'GRANT SELECT (proname ...) ON TABLE pg_proc TO public' => { 3168 create_order => 46, 3169 create_sql => 'GRANT SELECT ( 3170 tableoid, 3171 oid, 3172 proname, 3173 pronamespace, 3174 proowner, 3175 prolang, 3176 procost, 3177 prorows, 3178 provariadic, 3179 prosupport, 3180 prokind, 3181 prosecdef, 3182 proleakproof, 3183 proisstrict, 3184 proretset, 3185 provolatile, 3186 proparallel, 3187 pronargs, 3188 pronargdefaults, 3189 prorettype, 3190 proargtypes, 3191 proallargtypes, 3192 proargmodes, 3193 proargnames, 3194 proargdefaults, 3195 protrftypes, 3196 prosrc, 3197 probin, 3198 proconfig, 3199 proacl 3200 ) ON TABLE pg_proc TO public;', 3201 regexp => qr/ 3202 \QGRANT SELECT(tableoid) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3203 \QGRANT SELECT(oid) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3204 \QGRANT SELECT(proname) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3205 \QGRANT SELECT(pronamespace) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3206 \QGRANT SELECT(proowner) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3207 \QGRANT SELECT(prolang) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3208 \QGRANT SELECT(procost) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3209 \QGRANT SELECT(prorows) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3210 \QGRANT SELECT(provariadic) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3211 \QGRANT SELECT(prosupport) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3212 \QGRANT SELECT(prokind) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3213 \QGRANT SELECT(prosecdef) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3214 \QGRANT SELECT(proleakproof) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3215 \QGRANT SELECT(proisstrict) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3216 \QGRANT SELECT(proretset) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3217 \QGRANT SELECT(provolatile) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3218 \QGRANT SELECT(proparallel) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3219 \QGRANT SELECT(pronargs) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3220 \QGRANT SELECT(pronargdefaults) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3221 \QGRANT SELECT(prorettype) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3222 \QGRANT SELECT(proargtypes) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3223 \QGRANT SELECT(proallargtypes) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3224 \QGRANT SELECT(proargmodes) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3225 \QGRANT SELECT(proargnames) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3226 \QGRANT SELECT(proargdefaults) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3227 \QGRANT SELECT(protrftypes) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3228 \QGRANT SELECT(prosrc) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3229 \QGRANT SELECT(probin) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3230 \QGRANT SELECT(proconfig) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E\n.* 3231 \QGRANT SELECT(proacl) ON TABLE pg_catalog.pg_proc TO PUBLIC;\E/xms, 3232 like => { %full_runs, section_pre_data => 1, }, 3233 unlike => { no_privs => 1, }, 3234 }, 3235 3236 'GRANT USAGE ON SCHEMA public TO public' => { 3237 regexp => qr/^ 3238 \Q--\E\n\n 3239 \QGRANT USAGE ON SCHEMA public TO PUBLIC;\E 3240 /xm, 3241 3242 # this shouldn't ever get emitted anymore 3243 like => {}, 3244 }, 3245 3246 'REFRESH MATERIALIZED VIEW matview' => { 3247 regexp => qr/^\QREFRESH MATERIALIZED VIEW dump_test.matview;\E/m, 3248 like => 3249 { %full_runs, %dump_test_schema_runs, section_post_data => 1, }, 3250 unlike => { 3251 binary_upgrade => 1, 3252 exclude_dump_test_schema => 1, 3253 schema_only => 1, 3254 }, 3255 }, 3256 3257 'REFRESH MATERIALIZED VIEW matview_second' => { 3258 regexp => qr/^ 3259 \QREFRESH MATERIALIZED VIEW dump_test.matview;\E 3260 \n.* 3261 \QREFRESH MATERIALIZED VIEW dump_test.matview_second;\E 3262 /xms, 3263 like => 3264 { %full_runs, %dump_test_schema_runs, section_post_data => 1, }, 3265 unlike => { 3266 binary_upgrade => 1, 3267 exclude_dump_test_schema => 1, 3268 schema_only => 1, 3269 }, 3270 }, 3271 3272 # FIXME 3273 'REFRESH MATERIALIZED VIEW matview_third' => { 3274 regexp => qr/^ 3275 \QREFRESH MATERIALIZED VIEW dump_test.matview_third;\E 3276 /xms, 3277 like => {}, 3278 }, 3279 3280 # FIXME 3281 'REFRESH MATERIALIZED VIEW matview_fourth' => { 3282 regexp => qr/^ 3283 \QREFRESH MATERIALIZED VIEW dump_test.matview_fourth;\E 3284 /xms, 3285 like => {}, 3286 }, 3287 3288 'REVOKE CONNECT ON DATABASE dump_test FROM public' => { 3289 create_order => 49, 3290 create_sql => 'REVOKE CONNECT ON DATABASE dump_test FROM public;', 3291 regexp => qr/^ 3292 \QREVOKE CONNECT,TEMPORARY ON DATABASE dump_test FROM PUBLIC;\E\n 3293 \QGRANT TEMPORARY ON DATABASE dump_test TO PUBLIC;\E\n 3294 \QGRANT CREATE ON DATABASE dump_test TO regress_dump_test_role;\E 3295 /xm, 3296 like => { pg_dumpall_dbprivs => 1, }, 3297 }, 3298 3299 'REVOKE EXECUTE ON FUNCTION pg_sleep() FROM public' => { 3300 create_order => 15, 3301 create_sql => 'REVOKE EXECUTE ON FUNCTION pg_sleep(float8) 3302 FROM public;', 3303 regexp => qr/^ 3304 \QREVOKE ALL ON FUNCTION pg_catalog.pg_sleep(double precision) FROM PUBLIC;\E 3305 /xm, 3306 like => { %full_runs, section_pre_data => 1, }, 3307 unlike => { no_privs => 1, }, 3308 }, 3309 3310 'REVOKE SELECT ON TABLE pg_proc FROM public' => { 3311 create_order => 45, 3312 create_sql => 'REVOKE SELECT ON TABLE pg_proc FROM public;', 3313 regexp => 3314 qr/^\QREVOKE SELECT ON TABLE pg_catalog.pg_proc FROM PUBLIC;\E/m, 3315 like => { %full_runs, section_pre_data => 1, }, 3316 unlike => { no_privs => 1, }, 3317 }, 3318 3319 'REVOKE CREATE ON SCHEMA public FROM public' => { 3320 create_order => 16, 3321 create_sql => 'REVOKE CREATE ON SCHEMA public FROM public;', 3322 regexp => qr/^ 3323 \QREVOKE ALL ON SCHEMA public FROM PUBLIC;\E 3324 \n\QGRANT USAGE ON SCHEMA public TO PUBLIC;\E 3325 /xm, 3326 like => { %full_runs, section_pre_data => 1, }, 3327 unlike => { no_privs => 1, }, 3328 }, 3329 3330 'REVOKE USAGE ON LANGUAGE plpgsql FROM public' => { 3331 create_order => 16, 3332 create_sql => 'REVOKE USAGE ON LANGUAGE plpgsql FROM public;', 3333 regexp => qr/^REVOKE ALL ON LANGUAGE plpgsql FROM PUBLIC;/m, 3334 like => { 3335 %full_runs, 3336 %dump_test_schema_runs, 3337 only_dump_test_table => 1, 3338 role => 1, 3339 section_pre_data => 1, 3340 }, 3341 unlike => { no_privs => 1, }, 3342 }, 3343 3344 3345 'CREATE ACCESS METHOD regress_test_table_am' => { 3346 create_order => 11, 3347 create_sql => 3348 'CREATE ACCESS METHOD regress_table_am TYPE TABLE HANDLER heap_tableam_handler;', 3349 regexp => qr/^ 3350 \QCREATE ACCESS METHOD regress_table_am TYPE TABLE HANDLER heap_tableam_handler;\E 3351 \n/xm, 3352 like => { 3353 %full_runs, section_pre_data => 1, 3354 }, 3355 }, 3356 3357 # It's a bit tricky to ensure that the proper SET of default table 3358 # AM occurs. To achieve that we create a table with the standard 3359 # AM, test AM, standard AM. That guarantees that there needs to be 3360 # a SET interspersed. Then use a regex that prevents interspersed 3361 # SET ...; statements, followed by the expected CREATE TABLE. Not 3362 # pretty, but seems hard to do better in this framework. 3363 'CREATE TABLE regress_pg_dump_table_am' => { 3364 create_order => 12, 3365 create_sql => ' 3366 CREATE TABLE dump_test.regress_pg_dump_table_am_0() USING heap; 3367 CREATE TABLE dump_test.regress_pg_dump_table_am_1 (col1 int) USING regress_table_am; 3368 CREATE TABLE dump_test.regress_pg_dump_table_am_2() USING heap;', 3369 regexp => qr/^ 3370 \QSET default_table_access_method = regress_table_am;\E 3371 (\n(?!SET[^;]+;)[^\n]*)* 3372 \n\QCREATE TABLE dump_test.regress_pg_dump_table_am_1 (\E 3373 \n\s+\Qcol1 integer\E 3374 \n\);/xm, 3375 like => { 3376 %full_runs, %dump_test_schema_runs, section_pre_data => 1, 3377 }, 3378 unlike => { exclude_dump_test_schema => 1 }, 3379 }, 3380 3381 'CREATE MATERIALIZED VIEW regress_pg_dump_matview_am' => { 3382 create_order => 13, 3383 create_sql => ' 3384 CREATE MATERIALIZED VIEW dump_test.regress_pg_dump_matview_am_0 USING heap AS SELECT 1; 3385 CREATE MATERIALIZED VIEW dump_test.regress_pg_dump_matview_am_1 3386 USING regress_table_am AS SELECT count(*) FROM pg_class; 3387 CREATE MATERIALIZED VIEW dump_test.regress_pg_dump_matview_am_2 USING heap AS SELECT 1;', 3388 regexp => qr/^ 3389 \QSET default_table_access_method = regress_table_am;\E 3390 (\n(?!SET[^;]+;)[^\n]*)* 3391 \QCREATE MATERIALIZED VIEW dump_test.regress_pg_dump_matview_am_1 AS\E 3392 \n\s+\QSELECT count(*) AS count\E 3393 \n\s+\QFROM pg_class\E 3394 \n\s+\QWITH NO DATA;\E\n/xm, 3395 like => { 3396 %full_runs, %dump_test_schema_runs, section_pre_data => 1, 3397 }, 3398 unlike => { exclude_dump_test_schema => 1 }, 3399 }); 3400 3401######################################### 3402# Create a PG instance to test actually dumping from 3403 3404my $node = get_new_node('main'); 3405$node->init; 3406$node->start; 3407 3408my $port = $node->port; 3409 3410# We need to see if this system supports CREATE COLLATION or not 3411# If it doesn't then we will skip all the COLLATION-related tests. 3412my $collation_support = 0; 3413my $collation_check_stderr; 3414$node->psql( 3415 'postgres', 3416 "CREATE COLLATION testing FROM \"C\"; DROP COLLATION testing;", 3417 on_error_stop => 0, 3418 stderr => \$collation_check_stderr); 3419 3420if ($collation_check_stderr !~ /ERROR: /) 3421{ 3422 $collation_support = 1; 3423} 3424 3425# Create a second database for certain tests to work against 3426$node->psql('postgres', 'create database regress_pg_dump_test;'); 3427 3428# Start with number of command_fails_like()*2 tests below (each 3429# command_fails_like is actually 2 tests) 3430my $num_tests = 12; 3431 3432foreach my $run (sort keys %pgdump_runs) 3433{ 3434 my $test_key = $run; 3435 my $run_db = 'postgres'; 3436 3437 if (defined($pgdump_runs{$run}->{database})) 3438 { 3439 $run_db = $pgdump_runs{$run}->{database}; 3440 } 3441 3442 # Each run of pg_dump is a test itself 3443 $num_tests++; 3444 3445 # If there is a restore cmd, that's another test 3446 if ($pgdump_runs{$run}->{restore_cmd}) 3447 { 3448 $num_tests++; 3449 } 3450 3451 if ($pgdump_runs{$run}->{test_key}) 3452 { 3453 $test_key = $pgdump_runs{$run}->{test_key}; 3454 } 3455 3456 # Then count all the tests run against each run 3457 foreach my $test (sort keys %tests) 3458 { 3459 3460 # postgres is the default database, if it isn't overridden 3461 my $test_db = 'postgres'; 3462 3463 # Specific tests can override the database to use 3464 if (defined($tests{$test}->{database})) 3465 { 3466 $test_db = $tests{$test}->{database}; 3467 } 3468 3469 # The database to test against needs to match the database the run is 3470 # for, so skip combinations where they don't match up. 3471 if ($run_db ne $test_db) 3472 { 3473 next; 3474 } 3475 3476 # Skip any collation-related commands if there is no collation support 3477 if (!$collation_support && defined($tests{$test}->{collation})) 3478 { 3479 next; 3480 } 3481 3482 # If there is a like entry, but no unlike entry, then we will test the like case 3483 if ($tests{$test}->{like}->{$test_key} 3484 && !defined($tests{$test}->{unlike}->{$test_key})) 3485 { 3486 $num_tests++; 3487 } 3488 else 3489 { 3490 # We will test everything that isn't a 'like' 3491 $num_tests++; 3492 } 3493 } 3494} 3495plan tests => $num_tests; 3496 3497######################################### 3498# Set up schemas, tables, etc, to be dumped. 3499 3500# Build up the create statements 3501my %create_sql = (); 3502 3503foreach my $test ( 3504 sort { 3505 if ($tests{$a}->{create_order} and $tests{$b}->{create_order}) 3506 { 3507 $tests{$a}->{create_order} <=> $tests{$b}->{create_order}; 3508 } 3509 elsif ($tests{$a}->{create_order}) 3510 { 3511 -1; 3512 } 3513 elsif ($tests{$b}->{create_order}) 3514 { 3515 1; 3516 } 3517 else 3518 { 3519 0; 3520 } 3521 } keys %tests) 3522{ 3523 my $test_db = 'postgres'; 3524 3525 if (defined($tests{$test}->{database})) 3526 { 3527 $test_db = $tests{$test}->{database}; 3528 } 3529 3530 if ($tests{$test}->{create_sql}) 3531 { 3532 3533 # Skip any collation-related commands if there is no collation support 3534 if (!$collation_support && defined($tests{$test}->{collation})) 3535 { 3536 next; 3537 } 3538 3539 # Add terminating semicolon 3540 $create_sql{$test_db} .= $tests{$test}->{create_sql} . ";"; 3541 } 3542} 3543 3544# Send the combined set of commands to psql 3545foreach my $db (sort keys %create_sql) 3546{ 3547 $node->safe_psql($db, $create_sql{$db}); 3548} 3549 3550######################################### 3551# Test connecting to a non-existent database 3552 3553command_fails_like( 3554 [ 'pg_dump', '-p', "$port", 'qqq' ], 3555 qr/\Qpg_dump: error: connection to database "qqq" failed: FATAL: database "qqq" does not exist\E/, 3556 'connecting to a non-existent database'); 3557 3558######################################### 3559# Test connecting with an unprivileged user 3560 3561command_fails_like( 3562 [ 'pg_dump', '-p', "$port", '--role=regress_dump_test_role' ], 3563 qr/\Qpg_dump: error: query failed: ERROR: permission denied for\E/, 3564 'connecting with an unprivileged user'); 3565 3566######################################### 3567# Test dumping a non-existent schema, table, and patterns with --strict-names 3568 3569command_fails_like( 3570 [ 'pg_dump', '-p', "$port", '-n', 'nonexistent' ], 3571 qr/\Qpg_dump: error: no matching schemas were found\E/, 3572 'dumping a non-existent schema'); 3573 3574command_fails_like( 3575 [ 'pg_dump', '-p', "$port", '-t', 'nonexistent' ], 3576 qr/\Qpg_dump: error: no matching tables were found\E/, 3577 'dumping a non-existent table'); 3578 3579command_fails_like( 3580 [ 'pg_dump', '-p', "$port", '--strict-names', '-n', 'nonexistent*' ], 3581 qr/\Qpg_dump: error: no matching schemas were found for pattern\E/, 3582 'no matching schemas'); 3583 3584command_fails_like( 3585 [ 'pg_dump', '-p', "$port", '--strict-names', '-t', 'nonexistent*' ], 3586 qr/\Qpg_dump: error: no matching tables were found for pattern\E/, 3587 'no matching tables'); 3588 3589######################################### 3590# Run all runs 3591 3592foreach my $run (sort keys %pgdump_runs) 3593{ 3594 my $test_key = $run; 3595 my $run_db = 'postgres'; 3596 3597 $node->command_ok(\@{ $pgdump_runs{$run}->{dump_cmd} }, 3598 "$run: pg_dump runs"); 3599 3600 if ($pgdump_runs{$run}->{restore_cmd}) 3601 { 3602 $node->command_ok(\@{ $pgdump_runs{$run}->{restore_cmd} }, 3603 "$run: pg_restore runs"); 3604 } 3605 3606 if ($pgdump_runs{$run}->{test_key}) 3607 { 3608 $test_key = $pgdump_runs{$run}->{test_key}; 3609 } 3610 3611 my $output_file = slurp_file("$tempdir/${run}.sql"); 3612 3613 ######################################### 3614 # Run all tests where this run is included 3615 # as either a 'like' or 'unlike' test. 3616 3617 foreach my $test (sort keys %tests) 3618 { 3619 my $test_db = 'postgres'; 3620 3621 if (defined($pgdump_runs{$run}->{database})) 3622 { 3623 $run_db = $pgdump_runs{$run}->{database}; 3624 } 3625 3626 if (defined($tests{$test}->{database})) 3627 { 3628 $test_db = $tests{$test}->{database}; 3629 } 3630 3631 # Skip any collation-related commands if there is no collation support 3632 if (!$collation_support && defined($tests{$test}->{collation})) 3633 { 3634 next; 3635 } 3636 3637 if ($run_db ne $test_db) 3638 { 3639 next; 3640 } 3641 3642 # Run the test listed as a like, unless it is specifically noted 3643 # as an unlike (generally due to an explicit exclusion or similar). 3644 if ($tests{$test}->{like}->{$test_key} 3645 && !defined($tests{$test}->{unlike}->{$test_key})) 3646 { 3647 if (!ok($output_file =~ $tests{$test}->{regexp}, 3648 "$run: should dump $test")) 3649 { 3650 diag("Review $run results in $tempdir"); 3651 } 3652 } 3653 else 3654 { 3655 if (!ok($output_file !~ $tests{$test}->{regexp}, 3656 "$run: should not dump $test")) 3657 { 3658 diag("Review $run results in $tempdir"); 3659 } 3660 } 3661 } 3662} 3663 3664######################################### 3665# Stop the database instance, which will be removed at the end of the tests. 3666 3667$node->stop('fast'); 3668