| -- |
| -- Validate GPDB can create unique index on a table created in utility mode |
| -- |
| -- NOTICE: we must connect to master in utility mode because the oid of table is |
| -- preassigned in QD, if we create a table in utility mode in QE, the oid might |
| -- conflict with preassigned oid. |
| -1U: create table utilitymode_primary_key_tab (c1 int); |
| CREATE |
| -1U: create unique index idx_utilitymode_c1 on utilitymode_primary_key_tab (c1); |
| CREATE |
| -1U: drop table utilitymode_primary_key_tab; |
| DROP |
| |
| -- Try a few queries in utility mode. (Once upon a time, there was a bug that |
| -- caused a crash on EXPLAIN ANALYZE on a Sort node in utility mode.) |
| 0U: begin; |
| BEGIN |
| 0U: set local enable_seqscan=off; |
| SET |
| -- start_ignore |
| 0U: explain analyze select * from gp_segment_configuration order by dbid; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------------------- |
| Sort (cost=0.02..0.03 rows=1 width=116) (actual time=0.078..0.087 rows=1 loops=1) |
| Sort Key: gp_get_segment_configuration.dbid |
| Sort Method: quicksort Memory: 25kB |
| -> Function Scan on gp_get_segment_configuration (cost=0.00..0.01 rows=1 width=116) (actual time=0.006..0.008 rows=1 loops=1) |
| Planning Time: 0.470 ms |
| (slice0) Executor memory: 134K bytes (seg0). Work_mem: 17K bytes max. |
| Optimizer: Postgres query optimizer |
| Execution Time: 0.128 ms |
| (8 rows) |
| -- end_ignore |
| 0U: reset enable_seqscan; |
| RESET |
| 0U: set local enable_indexscan=off; |
| SET |
| -- start_ignore |
| 0U: explain analyze select * from gp_segment_configuration order by dbid; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------------ |
| Sort (cost=168.75..174.75 rows=2400 width=108) (actual time=0.067..0.071 rows=4 loops=1) |
| Sort Key: dbid |
| Sort Method: quicksort Memory: 50kB |
| -> Function Scan on gp_get_segment_configuration (cost=0.00..0.01 rows=1 width=116) (actual time=0.002..0.002 rows=1 loops=1) |
| Planning Time: 0.331 ms |
| (slice0) Executor memory: 59K bytes (seg0). |
| Optimizer: Postgres query optimizer |
| Execution Time: 0.136 ms |
| (8 rows) |
| -- end_ignore |
| 0U: rollback; |
| ROLLBACK |
| |
| -- |
| -- Temp tables should have a different schema name pattern in utility mode. |
| -- |
| -- A temp table's schema name used to be pg_temp_<session_id> in normal mode |
| -- and pg_temp_<backend_id> in utility mode, once the normal-mode session id |
| -- equals to the utility-mode backend id they will conflict with each other and |
| -- cause catalog corruption on the segment. |
| -- |
| -- We have changed the name to pg_temp_0<backend_id> in utility mode. |
| 0U: CREATE TEMP TABLE utilitymode_tmp_tab (c1 int) DISTRIBUTED BY (c1); |
| CREATE |
| 0U: SELECT substring(n.nspname FROM 1 FOR 9) FROM pg_namespace n JOIN pg_class c ON n.oid = c.relnamespace WHERE c.relname = 'utilitymode_tmp_tab'; |
| substring |
| ----------- |
| pg_temp_0 |
| (1 row) |
| 0U: SELECT substring(n2.nspname FROM 1 FOR 15) FROM pg_namespace n1 JOIN pg_class c ON n1.oid = c.relnamespace JOIN pg_namespace n2 ON n2.nspname = 'pg_toast_temp_0' || substring(n1.nspname FROM 10) WHERE c.relname = 'utilitymode_tmp_tab'; |
| substring |
| ----------------- |
| pg_toast_temp_0 |
| (1 row) |
| |
| -- |
| -- gp_dist_random('<view>') should not crash in utility mode |
| -- |
| create or replace view misc_v as select 1; |
| CREATE |
| 0U: select 1 from gp_dist_random('misc_v') union select 1 from misc_v; |
| ?column? |
| ---------- |
| 1 |
| (1 row) |
| 0U: select count(*) from gp_dist_random('misc_v'); |
| count |
| ------- |
| 1 |
| (1 row) |
| -- But views created in utility mode should not throw away gp_dist_random |
| 0U: create or replace view misc_v2 as select 1 from gp_dist_random('pg_class'); |
| CREATE |
| 0U: select definition from pg_views where viewname = 'misc_v2'; |
| definition |
| ------------------------------------------------------------- |
| FROM gp_dist_random('pg_class'); |
| SELECT 1 AS "?column?" |
| (1 row) |
| 0U: select count(*) > 0 from gp_dist_random('misc_v2'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| 0U: drop view misc_v2; |
| DROP |
| drop view misc_v; |
| DROP |
| |
| -- |
| -- gp_toolkit.gp_check_orphaned_files should not be running with concurrent transaction (even idle) |
| -- |
| -- use a different database to do the test, otherwise we might be reporting tons |
| -- of orphaned files produced by the many intential PANICs/restarts in the isolation2 tests. |
| create database check_orphaned_db; |
| CREATE |
| 1:@db_name check_orphaned_db: begin; |
| BEGIN |
| 2:@db_name check_orphaned_db: select * from gp_toolkit.gp_check_orphaned_files; |
| ERROR: There is a client session running on one or more segment. Aborting... |
| CONTEXT: PL/pgSQL function gp_toolkit.__gp_check_orphaned_files_func() line 15 at RAISE |
| 1q: ... <quitting> |
| 2q: ... <quitting> |
| |
| drop database check_orphaned_db; |
| DROP |
