1-- 2-- MULTI_EXTENSION 3-- 4-- Tests around extension creation / upgrades 5-- 6-- It'd be nice to script generation of this file, but alas, that's 7-- not done yet. 8 9-- differentiate the output file for pg11 and versions above, with regards to objects 10-- created per citus version depending on the postgres version. Upgrade tests verify the 11-- objects are added in citus_finish_pg_upgrade() 12SHOW server_version \gset 13SELECT substring(:'server_version', '\d+')::int > 11 AS version_above_eleven; 14 15SET citus.next_shard_id TO 580000; 16CREATE SCHEMA multi_extension; 17 18SELECT $definition$ 19CREATE OR REPLACE FUNCTION test.maintenance_worker() 20 RETURNS pg_stat_activity 21 LANGUAGE plpgsql 22AS $$ 23DECLARE 24 activity record; 25BEGIN 26 DO 'BEGIN END'; -- Force maintenance daemon to start 27 -- we don't want to wait forever; loop will exit after 20 seconds 28 FOR i IN 1 .. 200 LOOP 29 PERFORM pg_stat_clear_snapshot(); 30 SELECT * INTO activity FROM pg_stat_activity 31 WHERE application_name = 'Citus Maintenance Daemon' AND datname = current_database(); 32 IF activity.pid IS NOT NULL THEN 33 RETURN activity; 34 ELSE 35 PERFORM pg_sleep(0.1); 36 END IF ; 37 END LOOP; 38 -- fail if we reach the end of this loop 39 raise 'Waited too long for maintenance daemon to start'; 40END; 41$$; 42$definition$ create_function_test_maintenance_worker 43\gset 44 45CREATE TABLE multi_extension.prev_objects(description text); 46CREATE TABLE multi_extension.extension_diff(previous_object text COLLATE "C", 47 current_object text COLLATE "C"); 48 49CREATE FUNCTION multi_extension.print_extension_changes() 50RETURNS TABLE(previous_object text, current_object text) 51AS $func$ 52BEGIN 53 SET LOCAL search_path TO multi_extension; 54 TRUNCATE TABLE extension_diff; 55 56 CREATE TABLE current_objects AS 57 SELECT pg_catalog.pg_describe_object(classid, objid, 0) 58 || ' ' || 59 coalesce(pg_catalog.pg_get_function_result(objid), '') AS description 60 FROM pg_catalog.pg_depend, pg_catalog.pg_extension e 61 WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass 62 AND refobjid = e.oid 63 AND deptype = 'e' 64 AND e.extname='citus'; 65 66 INSERT INTO extension_diff 67 SELECT p.description previous_object, c.description current_object 68 FROM current_objects c FULL JOIN prev_objects p 69 ON p.description = c.description 70 WHERE p.description is null OR c.description is null; 71 72 DROP TABLE prev_objects; 73 ALTER TABLE current_objects RENAME TO prev_objects; 74 75 RETURN QUERY SELECT * FROM extension_diff ORDER BY 1, 2; 76END 77$func$ LANGUAGE plpgsql; 78 79CREATE SCHEMA test; 80:create_function_test_maintenance_worker 81 82-- check maintenance daemon is started 83SELECT datname, current_database(), 84 usename, (SELECT extowner::regrole::text FROM pg_extension WHERE extname = 'citus') 85FROM test.maintenance_worker(); 86 87-- ensure no unexpected objects were created outside pg_catalog 88SELECT pgio.type, pgio.identity 89FROM pg_depend AS pgd, 90 pg_extension AS pge, 91 LATERAL pg_identify_object(pgd.classid, pgd.objid, pgd.objsubid) AS pgio 92WHERE pgd.refclassid = 'pg_extension'::regclass AND 93 pgd.refobjid = pge.oid AND 94 pge.extname = 'citus' AND 95 pgio.schema NOT IN ('pg_catalog', 'citus', 'citus_internal', 'test', 'columnar') 96ORDER BY 1, 2; 97 98 99-- DROP EXTENSION pre-created by the regression suite 100DROP EXTENSION citus; 101\c 102 103-- these tests switch between citus versions and call ddl's that require pg_dist_object to be created 104SET citus.enable_object_propagation TO 'false'; 105 106SET citus.enable_version_checks TO 'false'; 107 108CREATE EXTENSION citus VERSION '8.0-1'; 109ALTER EXTENSION citus UPDATE TO '8.0-2'; 110ALTER EXTENSION citus UPDATE TO '8.0-3'; 111ALTER EXTENSION citus UPDATE TO '8.0-4'; 112ALTER EXTENSION citus UPDATE TO '8.0-5'; 113ALTER EXTENSION citus UPDATE TO '8.0-6'; 114ALTER EXTENSION citus UPDATE TO '8.0-7'; 115ALTER EXTENSION citus UPDATE TO '8.0-8'; 116ALTER EXTENSION citus UPDATE TO '8.0-9'; 117ALTER EXTENSION citus UPDATE TO '8.0-10'; 118ALTER EXTENSION citus UPDATE TO '8.0-11'; 119ALTER EXTENSION citus UPDATE TO '8.0-12'; 120ALTER EXTENSION citus UPDATE TO '8.0-13'; 121ALTER EXTENSION citus UPDATE TO '8.1-1'; 122ALTER EXTENSION citus UPDATE TO '8.2-1'; 123ALTER EXTENSION citus UPDATE TO '8.2-2'; 124ALTER EXTENSION citus UPDATE TO '8.2-3'; 125ALTER EXTENSION citus UPDATE TO '8.2-4'; 126ALTER EXTENSION citus UPDATE TO '8.3-1'; 127ALTER EXTENSION citus UPDATE TO '9.0-1'; 128ALTER EXTENSION citus UPDATE TO '9.0-2'; 129ALTER EXTENSION citus UPDATE TO '9.1-1'; 130ALTER EXTENSION citus UPDATE TO '9.2-1'; 131ALTER EXTENSION citus UPDATE TO '9.2-2'; 132-- Snapshot of state at 9.2-2 133SELECT * FROM multi_extension.print_extension_changes(); 134 135-- Test downgrade to 9.2-2 from 9.2-4 136ALTER EXTENSION citus UPDATE TO '9.2-4'; 137ALTER EXTENSION citus UPDATE TO '9.2-2'; 138-- Should be empty result since upgrade+downgrade should be a no-op 139SELECT * FROM multi_extension.print_extension_changes(); 140 141/* 142 * As we mistakenly bumped schema version to 9.3-1 in a bad release, we support 143 * updating citus schema from 9.3-1 to 9.2-4, but we do not support updates to 9.3-1. 144 * 145 * Hence the query below should fail. 146 */ 147ALTER EXTENSION citus UPDATE TO '9.3-1'; 148 149ALTER EXTENSION citus UPDATE TO '9.2-4'; 150-- Snapshot of state at 9.2-4 151SELECT * FROM multi_extension.print_extension_changes(); 152 153-- Test downgrade to 9.2-4 from 9.3-2 154ALTER EXTENSION citus UPDATE TO '9.3-2'; 155ALTER EXTENSION citus UPDATE TO '9.2-4'; 156-- Should be empty result since upgrade+downgrade should be a no-op 157SELECT * FROM multi_extension.print_extension_changes(); 158 159-- Snapshot of state at 9.3-2 160ALTER EXTENSION citus UPDATE TO '9.3-2'; 161SELECT * FROM multi_extension.print_extension_changes(); 162 163-- Test downgrade to 9.3-2 from 9.4-1 164ALTER EXTENSION citus UPDATE TO '9.4-1'; 165ALTER EXTENSION citus UPDATE TO '9.3-2'; 166-- Should be empty result since upgrade+downgrade should be a no-op 167SELECT * FROM multi_extension.print_extension_changes(); 168 169-- Snapshot of state at 9.4-1 170ALTER EXTENSION citus UPDATE TO '9.4-1'; 171SELECT * FROM multi_extension.print_extension_changes(); 172 173-- Test upgrade paths for backported citus_pg_upgrade functions 174ALTER EXTENSION citus UPDATE TO '9.4-2'; 175ALTER EXTENSION citus UPDATE TO '9.4-1'; 176-- Should be empty result, even though the downgrade doesn't undo the upgrade, the 177-- function signature doesn't change, which is reflected here. 178SELECT * FROM multi_extension.print_extension_changes(); 179 180ALTER EXTENSION citus UPDATE TO '9.4-2'; 181SELECT * FROM multi_extension.print_extension_changes(); 182 183-- Snapshot of state at 9.4-1 184ALTER EXTENSION citus UPDATE TO '9.4-1'; 185SELECT * FROM multi_extension.print_extension_changes(); 186 187-- Test upgrade paths for backported improvement of master_update_table_statistics function 188ALTER EXTENSION citus UPDATE TO '9.4-3'; 189-- should see the new source code with internal function citus_update_table_statistics 190SELECT prosrc FROM pg_proc WHERE proname = 'master_update_table_statistics' ORDER BY 1; 191ALTER EXTENSION citus UPDATE TO '9.4-2'; 192 193-- should see the old source code 194SELECT prosrc FROM pg_proc WHERE proname = 'master_update_table_statistics' ORDER BY 1; 195-- Should be empty result 196SELECT * FROM multi_extension.print_extension_changes(); 197 198ALTER EXTENSION citus UPDATE TO '9.4-3'; 199-- should see the new source code with internal function citus_update_table_statistics 200SELECT prosrc FROM pg_proc WHERE proname = 'master_update_table_statistics' ORDER BY 1; 201-- Should be empty result 202SELECT * FROM multi_extension.print_extension_changes(); 203 204-- Snapshot of state at 9.4-1 205ALTER EXTENSION citus UPDATE TO '9.4-1'; 206-- should see the old source code 207SELECT prosrc FROM pg_proc WHERE proname = 'master_update_table_statistics' ORDER BY 1; 208-- Should be empty result 209SELECT * FROM multi_extension.print_extension_changes(); 210 211-- Test downgrade to 9.4-1 from 9.5-1 212ALTER EXTENSION citus UPDATE TO '9.5-1'; 213 214BEGIN; 215 SELECT master_add_node('localhost', :master_port, groupId=>0); 216 CREATE TABLE citus_local_table (a int); 217 SELECT create_citus_local_table('citus_local_table'); 218 219 -- downgrade from 9.5-1 to 9.4-1 should fail as we have a citus local table 220 ALTER EXTENSION citus UPDATE TO '9.4-1'; 221ROLLBACK; 222 223-- now we can downgrade as there is no citus local table 224ALTER EXTENSION citus UPDATE TO '9.4-1'; 225 226-- Should be empty result since upgrade+downgrade should be a no-op 227SELECT * FROM multi_extension.print_extension_changes(); 228 229-- Snapshot of state at 9.5-1 230ALTER EXTENSION citus UPDATE TO '9.5-1'; 231SELECT * FROM multi_extension.print_extension_changes(); 232 233-- Test upgrade paths for backported citus_pg_upgrade functions 234ALTER EXTENSION citus UPDATE TO '9.5-2'; 235ALTER EXTENSION citus UPDATE TO '9.5-1'; 236-- Should be empty result, even though the downgrade doesn't undo the upgrade, the 237-- function signature doesn't change, which is reflected here. 238SELECT * FROM multi_extension.print_extension_changes(); 239 240ALTER EXTENSION citus UPDATE TO '9.5-2'; 241SELECT * FROM multi_extension.print_extension_changes(); 242 243-- Snapshot of state at 9.5-1 244ALTER EXTENSION citus UPDATE TO '9.5-1'; 245SELECT * FROM multi_extension.print_extension_changes(); 246 247-- Test upgrade paths for backported improvement of master_update_table_statistics function 248ALTER EXTENSION citus UPDATE TO '9.5-3'; 249-- should see the new source code with internal function citus_update_table_statistics 250SELECT prosrc FROM pg_proc WHERE proname = 'master_update_table_statistics' ORDER BY 1; 251ALTER EXTENSION citus UPDATE TO '9.5-2'; 252 253-- should see the old source code 254SELECT prosrc FROM pg_proc WHERE proname = 'master_update_table_statistics' ORDER BY 1; 255-- Should be empty result 256SELECT * FROM multi_extension.print_extension_changes(); 257 258ALTER EXTENSION citus UPDATE TO '9.5-3'; 259-- should see the new source code with internal function citus_update_table_statistics 260SELECT prosrc FROM pg_proc WHERE proname = 'master_update_table_statistics' ORDER BY 1; 261-- Should be empty result 262SELECT * FROM multi_extension.print_extension_changes(); 263 264-- Snapshot of state at 9.5-1 265ALTER EXTENSION citus UPDATE TO '9.5-1'; 266-- should see the old source code 267SELECT prosrc FROM pg_proc WHERE proname = 'master_update_table_statistics' ORDER BY 1; 268-- Should be empty result 269SELECT * FROM multi_extension.print_extension_changes(); 270 271-- We removed the upgrade paths to 10.0-1, 10.0-2 and 10.0-3 due to a bug that blocked 272-- upgrades to 10.0, Therefore we test upgrades to 10.0-4 instead 273 274-- Test downgrade to 9.5-1 from 10.0-4 275ALTER EXTENSION citus UPDATE TO '10.0-4'; 276ALTER EXTENSION citus UPDATE TO '9.5-1'; 277-- Should be empty result since upgrade+downgrade should be a no-op 278SELECT * FROM multi_extension.print_extension_changes(); 279 280-- Snapshot of state at 10.0-4 281ALTER EXTENSION citus UPDATE TO '10.0-4'; 282SELECT * FROM multi_extension.print_extension_changes(); 283 284-- check that we depend on the existence of public schema, and we can not drop it now 285DROP SCHEMA public; 286 287-- verify that citus_tables view is on pg_catalog if public schema is absent. 288ALTER EXTENSION citus UPDATE TO '9.5-1'; 289DROP SCHEMA public; 290ALTER EXTENSION citus UPDATE TO '10.0-4'; 291SELECT * FROM multi_extension.print_extension_changes(); 292 293-- recreate public schema, and recreate citus_tables in the public schema by default 294CREATE SCHEMA public; 295GRANT ALL ON SCHEMA public TO public; 296ALTER EXTENSION citus UPDATE TO '9.5-1'; 297ALTER EXTENSION citus UPDATE TO '10.0-4'; 298SELECT * FROM multi_extension.print_extension_changes(); 299 300-- not print "HINT: " to hide current lib version 301\set VERBOSITY terse 302CREATE TABLE columnar_table(a INT, b INT) USING columnar; 303SET citus.enable_version_checks TO ON; 304 305-- all should throw an error due to version mismatch 306VACUUM FULL columnar_table; 307INSERT INTO columnar_table SELECT i FROM generate_series(1, 10) i; 308VACUUM columnar_table; 309TRUNCATE columnar_table; 310DROP TABLE columnar_table; 311CREATE INDEX ON columnar_table (a); 312SELECT alter_columnar_table_set('columnar_table', compression => 'pglz'); 313SELECT alter_columnar_table_reset('columnar_table'); 314INSERT INTO columnar_table SELECT * FROM columnar_table; 315 316SELECT 1 FROM columnar_table; -- columnar custom scan 317 318SET columnar.enable_custom_scan TO OFF; 319SELECT 1 FROM columnar_table; -- seq scan 320 321CREATE TABLE new_columnar_table (a int) USING columnar; 322 323-- do cleanup for the rest of the tests 324SET citus.enable_version_checks TO OFF; 325DROP TABLE columnar_table; 326RESET columnar.enable_custom_scan; 327\set VERBOSITY default 328 329-- Test downgrade to 10.0-4 from 10.1-1 330ALTER EXTENSION citus UPDATE TO '10.1-1'; 331ALTER EXTENSION citus UPDATE TO '10.0-4'; 332-- Should be empty result since upgrade+downgrade should be a no-op 333SELECT * FROM multi_extension.print_extension_changes(); 334 335-- Snapshot of state at 10.1-1 336ALTER EXTENSION citus UPDATE TO '10.1-1'; 337SELECT * FROM multi_extension.print_extension_changes(); 338 339-- Test downgrade to 10.1-1 from 10.2-1 340ALTER EXTENSION citus UPDATE TO '10.2-1'; 341ALTER EXTENSION citus UPDATE TO '10.1-1'; 342-- Should be empty result since upgrade+downgrade should be a no-op 343SELECT * FROM multi_extension.print_extension_changes(); 344 345-- Snapshot of state at 10.2-1 346ALTER EXTENSION citus UPDATE TO '10.2-1'; 347SELECT * FROM multi_extension.print_extension_changes(); 348 349-- Test downgrade to 10.2-1 from 10.2-2 350ALTER EXTENSION citus UPDATE TO '10.2-2'; 351ALTER EXTENSION citus UPDATE TO '10.2-1'; 352-- Should be empty result since upgrade+downgrade should be a no-op 353SELECT * FROM multi_extension.print_extension_changes(); 354 355-- Snapshot of state at 10.2-2 356ALTER EXTENSION citus UPDATE TO '10.2-2'; 357SELECT * FROM multi_extension.print_extension_changes(); 358 359-- Test downgrade to 10.2-2 from 10.2-3 360ALTER EXTENSION citus UPDATE TO '10.2-3'; 361ALTER EXTENSION citus UPDATE TO '10.2-2'; 362-- Should be empty result since upgrade+downgrade should be a no-op 363SELECT * FROM multi_extension.print_extension_changes(); 364 365-- Snapshot of state at 10.2-3 366ALTER EXTENSION citus UPDATE TO '10.2-3'; 367SELECT * FROM multi_extension.print_extension_changes(); 368 369-- Test downgrade to 10.2-3 from 10.2-4 370ALTER EXTENSION citus UPDATE TO '10.2-4'; 371ALTER EXTENSION citus UPDATE TO '10.2-3'; 372 373-- Make sure that we don't delete pg_depend entries added in 374-- columnar--10.2-3--10.2-4.sql when downgrading to 10.2-3. 375SELECT COUNT(*)=10 376FROM pg_depend 377WHERE classid = 'pg_am'::regclass::oid AND 378 objid = (select oid from pg_am where amname = 'columnar') AND 379 objsubid = 0 AND 380 refclassid = 'pg_class'::regclass::oid AND 381 refobjsubid = 0 AND 382 deptype = 'n'; 383 384-- Should be empty result since upgrade+downgrade should be a no-op 385SELECT * FROM multi_extension.print_extension_changes(); 386 387-- Snapshot of state at 10.2-4 388ALTER EXTENSION citus UPDATE TO '10.2-4'; 389SELECT * FROM multi_extension.print_extension_changes(); 390 391-- Make sure that we defined dependencies from all rel objects (tables, 392-- indexes, sequences ..) to columnar table access method ... 393SELECT pg_class.oid INTO columnar_schema_members 394FROM pg_class, pg_namespace 395WHERE pg_namespace.oid=pg_class.relnamespace AND 396 pg_namespace.nspname='columnar'; 397SELECT refobjid INTO columnar_schema_members_pg_depend 398FROM pg_depend 399WHERE classid = 'pg_am'::regclass::oid AND 400 objid = (select oid from pg_am where amname = 'columnar') AND 401 objsubid = 0 AND 402 refclassid = 'pg_class'::regclass::oid AND 403 refobjsubid = 0 AND 404 deptype = 'n'; 405 406-- ... , so this should be empty, 407(TABLE columnar_schema_members EXCEPT TABLE columnar_schema_members_pg_depend) 408UNION 409(TABLE columnar_schema_members_pg_depend EXCEPT TABLE columnar_schema_members); 410 411-- ... , and both columnar_schema_members_pg_depend & columnar_schema_members 412-- should have 10 entries. 413SELECT COUNT(*)=10 FROM columnar_schema_members_pg_depend; 414 415DROP TABLE columnar_schema_members, columnar_schema_members_pg_depend; 416 417DROP TABLE multi_extension.prev_objects, multi_extension.extension_diff; 418 419-- show running version 420SHOW citus.version; 421 422-- ensure no unexpected objects were created outside pg_catalog 423SELECT pgio.type, pgio.identity 424FROM pg_depend AS pgd, 425 pg_extension AS pge, 426 LATERAL pg_identify_object(pgd.classid, pgd.objid, pgd.objsubid) AS pgio 427WHERE pgd.refclassid = 'pg_extension'::regclass AND 428 pgd.refobjid = pge.oid AND 429 pge.extname = 'citus' AND 430 pgio.schema NOT IN ('pg_catalog', 'citus', 'citus_internal', 'test', 'columnar') 431ORDER BY 1, 2; 432 433-- see incompatible version errors out 434RESET citus.enable_version_checks; 435DROP EXTENSION citus; 436CREATE EXTENSION citus VERSION '8.0-1'; 437 438-- Test non-distributed queries work even in version mismatch 439SET citus.enable_version_checks TO 'false'; 440CREATE EXTENSION citus VERSION '8.1-1'; 441SET citus.enable_version_checks TO 'true'; 442 443-- Test CREATE TABLE 444CREATE TABLE version_mismatch_table(column1 int); 445 446-- Test COPY 447\copy version_mismatch_table FROM STDIN; 4480 4491 4502 4513 4524 453\. 454 455-- Test INSERT 456INSERT INTO version_mismatch_table(column1) VALUES(5); 457 458-- Test SELECT 459SELECT * FROM version_mismatch_table ORDER BY column1; 460 461-- Test SELECT from pg_catalog 462SELECT d.datname as "Name", 463 pg_catalog.pg_get_userbyid(d.datdba) as "Owner", 464 pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" 465FROM pg_catalog.pg_database d 466ORDER BY 1; 467 468-- We should not distribute table in version mistmatch 469SELECT create_distributed_table('version_mismatch_table', 'column1'); 470 471-- This function will cause fail in next ALTER EXTENSION 472CREATE OR REPLACE FUNCTION pg_catalog.relation_is_a_known_shard(regclass) 473RETURNS void LANGUAGE plpgsql 474AS $function$ 475BEGIN 476END; 477$function$; 478 479SET citus.enable_version_checks TO 'false'; 480-- This will fail because of previous function declaration 481ALTER EXTENSION citus UPDATE TO '8.1-1'; 482 483-- We can DROP problematic function and continue ALTER EXTENSION even when version checks are on 484SET citus.enable_version_checks TO 'true'; 485DROP FUNCTION pg_catalog.relation_is_a_known_shard(regclass); 486 487SET citus.enable_version_checks TO 'false'; 488ALTER EXTENSION citus UPDATE TO '8.1-1'; 489 490-- Test updating to the latest version without specifying the version number 491ALTER EXTENSION citus UPDATE; 492 493-- re-create in newest version 494DROP EXTENSION citus; 495\c 496CREATE EXTENSION citus; 497 498-- test cache invalidation in workers 499\c - - - :worker_1_port 500 501DROP EXTENSION citus; 502SET citus.enable_version_checks TO 'false'; 503CREATE EXTENSION citus VERSION '8.0-1'; 504SET citus.enable_version_checks TO 'true'; 505-- during ALTER EXTENSION, we should invalidate the cache 506ALTER EXTENSION citus UPDATE; 507 508-- if cache is invalidated succesfull, this \d should work without any problem 509\d 510 511\c - - - :master_port 512 513-- test https://github.com/citusdata/citus/issues/3409 514CREATE USER testuser2 SUPERUSER; 515SET ROLE testuser2; 516DROP EXTENSION Citus; 517-- Loop until we see there's no maintenance daemon running 518DO $$begin 519 for i in 0 .. 100 loop 520 if i = 100 then raise 'Waited too long'; end if; 521 PERFORM pg_stat_clear_snapshot(); 522 perform * from pg_stat_activity where application_name = 'Citus Maintenance Daemon'; 523 if not found then exit; end if; 524 perform pg_sleep(0.1); 525 end loop; 526end$$; 527SELECT datid, datname, usename FROM pg_stat_activity WHERE application_name = 'Citus Maintenance Daemon'; 528CREATE EXTENSION Citus; 529-- Loop until we there's a maintenance daemon running 530DO $$begin 531 for i in 0 .. 100 loop 532 if i = 100 then raise 'Waited too long'; end if; 533 PERFORM pg_stat_clear_snapshot(); 534 perform * from pg_stat_activity where application_name = 'Citus Maintenance Daemon'; 535 if found then exit; end if; 536 perform pg_sleep(0.1); 537 end loop; 538end$$; 539SELECT datid, datname, usename FROM pg_stat_activity WHERE application_name = 'Citus Maintenance Daemon'; 540RESET ROLE; 541 542-- check that maintenance daemon gets (re-)started for the right user 543DROP EXTENSION citus; 544CREATE USER testuser SUPERUSER; 545SET ROLE testuser; 546CREATE EXTENSION citus; 547 548SELECT datname, current_database(), 549 usename, (SELECT extowner::regrole::text FROM pg_extension WHERE extname = 'citus') 550FROM test.maintenance_worker(); 551 552-- and recreate as the right owner 553RESET ROLE; 554DROP EXTENSION citus; 555CREATE EXTENSION citus; 556 557 558-- Check that maintenance daemon can also be started in another database 559CREATE DATABASE another; 560\c another 561CREATE EXTENSION citus; 562 563CREATE SCHEMA test; 564:create_function_test_maintenance_worker 565 566-- see that the daemon started 567SELECT datname, current_database(), 568 usename, (SELECT extowner::regrole::text FROM pg_extension WHERE extname = 'citus') 569FROM test.maintenance_worker(); 570 571-- Test that database with active worker can be dropped. 572\c regression 573 574CREATE SCHEMA test_daemon; 575 576-- we create a similar function on the regression database 577-- note that this function checks for the existence of the daemon 578-- when not found, returns true else tries for 5 times and 579-- returns false 580CREATE OR REPLACE FUNCTION test_daemon.maintenance_daemon_died(p_dbname text) 581 RETURNS boolean 582 LANGUAGE plpgsql 583AS $$ 584DECLARE 585 activity record; 586BEGIN 587 PERFORM pg_stat_clear_snapshot(); 588 SELECT * INTO activity FROM pg_stat_activity 589 WHERE application_name = 'Citus Maintenance Daemon' AND datname = p_dbname; 590 IF activity.pid IS NULL THEN 591 RETURN true; 592 ELSE 593 RETURN false; 594 END IF; 595END; 596$$; 597 598-- drop the database and see that the daemon is dead 599DROP DATABASE another; 600SELECT 601 * 602FROM 603 test_daemon.maintenance_daemon_died('another'); 604 605-- we don't need the schema and the function anymore 606DROP SCHEMA test_daemon CASCADE; 607 608 609-- verify citus does not crash while creating a table when run against an older worker 610-- create_distributed_table piggybacks multiple commands into single one, if one worker 611-- did not have the required UDF it should fail instead of crash. 612 613-- create a test database, configure citus with single node 614CREATE DATABASE another; 615\c - - - :worker_1_port 616CREATE DATABASE another; 617\c - - - :master_port 618 619\c another 620CREATE EXTENSION citus; 621SET citus.enable_object_propagation TO off; -- prevent distributed transactions during add node 622SELECT FROM master_add_node('localhost', :worker_1_port); 623 624\c - - - :worker_1_port 625CREATE EXTENSION citus; 626ALTER FUNCTION assign_distributed_transaction_id(initiator_node_identifier integer, transaction_number bigint, transaction_stamp timestamp with time zone) 627RENAME TO dummy_assign_function; 628 629\c - - - :master_port 630SET citus.shard_replication_factor to 1; 631-- create_distributed_table command should fail 632CREATE TABLE t1(a int, b int); 633SET client_min_messages TO ERROR; 634DO $$ 635BEGIN 636 BEGIN 637 SELECT create_distributed_table('t1', 'a'); 638 EXCEPTION WHEN OTHERS THEN 639 RAISE 'create distributed table failed'; 640 END; 641END; 642$$; 643 644\c regression 645\c - - - :master_port 646DROP DATABASE another; 647 648\c - - - :worker_1_port 649DROP DATABASE another; 650 651\c - - - :master_port 652-- only the regression database should have a maintenance daemon 653SELECT count(*) FROM pg_stat_activity WHERE application_name = 'Citus Maintenance Daemon'; 654 655-- recreate the extension immediately after the maintenancae daemon errors 656SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE application_name = 'Citus Maintenance Daemon'; 657DROP EXTENSION citus; 658CREATE EXTENSION citus; 659 660-- wait for maintenance daemon restart 661SELECT datname, current_database(), 662 usename, (SELECT extowner::regrole::text FROM pg_extension WHERE extname = 'citus') 663FROM test.maintenance_worker(); 664 665-- confirm that there is only one maintenance daemon 666SELECT count(*) FROM pg_stat_activity WHERE application_name = 'Citus Maintenance Daemon'; 667 668-- kill the maintenance daemon 669SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE application_name = 'Citus Maintenance Daemon'; 670 671-- reconnect 672\c - - - :master_port 673-- run something that goes through planner hook and therefore kicks of maintenance daemon 674SELECT 1; 675 676-- wait for maintenance daemon restart 677SELECT datname, current_database(), 678 usename, (SELECT extowner::regrole::text FROM pg_extension WHERE extname = 'citus') 679FROM test.maintenance_worker(); 680 681-- confirm that there is only one maintenance daemon 682SELECT count(*) FROM pg_stat_activity WHERE application_name = 'Citus Maintenance Daemon'; 683 684DROP TABLE version_mismatch_table; 685DROP SCHEMA multi_extension; 686