| -- |
| -- 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) |
