Hello!
Some bugs and possible bugs from more tests:
1. The following crashes the server:
CREATE GLOBAL TEMP SEQUENCE slv;
-- new session
BEGIN;
SELECT nextval('slv');
ROLLBACK;
SELECT lastval();
2. And I also have some other questions above global temp sequences:
CREATE GLOBAL TEMP SEQUENCE s;
CREATE GLOBAL TEMP TABLE t (a int, b serial);
BEGIN;
SELECT nextval('s'); -- 1
SELECT nextval('s'); -- 2
SELECT nextval('s'); -- 3
ROLLBACK;
SELECT nextval('s'); -- 1 again, is this intended?
also within a single transaction with savepoints:
BEGIN;
SAVEPOINT sp;
INSERT INTO t(a) VALUES (1); -- b = 1
ROLLBACK TO sp;
INSERT INTO t(a) VALUES (2); -- b = 1 again
COMMIT;
SELECT a, b FROM t; -- 2,1 ?
3. What should happen with a subscription into a temporary table?
Currently it silently discard the data.
-- primary
CREATE TABLE pubt (a int PRIMARY KEY);
INSERT INTO pubt VALUES (1),(2);
CREATE PUBLICATION mypub FOR TABLE pubt;
SELECT pg_create_logical_replication_slot('mysub','pgoutput');
-- replica
CREATE GLOBAL TEMP TABLE pubt (a int PRIMARY KEY);
CREATE SUBSCRIPTION mysub
CONNECTION 'host=$DATADIR dbname=pubdb'
PUBLICATION mypub
WITH (create_slot = false, slot_name = 'mysub');
And then some inserts. No error shown anywhere, replication seems
healthy from diagnostics.
4. Reverse situation: a GLOBAL TEMPORARY TABLE partition of a
published permanent partition is currently allowed?
CREATE TABLE decp (a int) PARTITION BY LIST (a);
CREATE TABLE decp_perm PARTITION OF decp FOR VALUES IN (1);
CREATE GLOBAL TEMP TABLE decp_gtt PARTITION OF decp FOR VALUES IN (2);
CREATE PUBLICATION pubdecp FOR TABLE decp
WITH (publish_via_partition_root = true);
5. there are some issues with normal standbys too:
-- on primary
CREATE GLOBAL TEMP TABLE g1 (a int);
-- create replica, on replica:
BEGIN;
SELECT pg_relation_size('g1'); -- appears to work
COMMIT; -- fails?
SELECT count(*) FROM (SELECT pg_total_relation_size(oid) FROM pg_class
WHERE relkind='r') s; -- fails
6. I can create a permanent materialized view over a global temporary
table, is this intended?
CREATE GLOBAL TEMP TABLE gmv (a int);
INSERT INTO gmv VALUES (42);
CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM gmv;
-- another session, even after closing the previous one
SELECT * FROM mv1; -- 42
7. There is a statistic leak with multiple sessions after dropping a GTT:
-- session 1
CREATE GLOBAL TEMP TABLE gtt_x (a int, b text);
CREATE STATISTICS gtt_x_st (dependencies, ndistinct) ON a, b FROM gtt_x;
INSERT INTO gtt_x SELECT g, 'a'||g FROM generate_series(1,100) g;
ANALYZE gtt_x;
-- session 2 starts
INSERT INTO gtt_x SELECT g, 'b'||g FROM generate_series(1,200) g;
ANALYZE gtt_x;
SELECT 'gtt_x'::regclass::oid AS relid \gset
SELECT (SELECT oid FROM pg_statistic_ext WHERE stxname='gtt_x_st') AS
stxid \gset
SELECT count(*) FROM pg_temp_statistic WHERE starelid = :relid; -- 2
SELECT count(*) FROM pg_temp_statistic_ext_data WHERE stxoid = :stxid; -- 1
-- session 1
DROP TABLE gtt_x;
-- session 2 again
SELECT count(*) FROM pg_temp_class WHERE oid = :relid; -- 0
SELECT count(*) FROM pg_temp_statistic WHERE starelid = :relid; -- still 2
SELECT count(*) FROM pg_temp_statistic_ext_data WHERE stxoid = :stxid;
-- still 1
8. There's an issue with the first PREPARE TRANSACTION failing after
another session drops a GTT:
--session 1
CREATE GLOBAL TEMP TABLE g2 (a int);
INSERT INTO g2 VALUES (42);
-- session 2
DROP TABLE g2;
-- session 1 again
BEGIN;
PREPARE TRANSACTION 'foo'; -- fails
BEGIN;
PREPARE TRANSACTION 'bar'; -- succeeds
9. There's also an issue with ADD UNIQUE with multiple transactions
One session has duplicate rows in the current table, another session
executes ALTER TABLE g ADD CONSTRAINT u UNIQUE (id);
ADD PRIMARY KEY is blocked in the same situation, so UNIQUE should too?
10. Statements like VACUUM; or ANALYZE; REINDEX DATABASE; REPACK ALL;
open all global temporary tables
Also reading pg_sequences initializes all GTT sequences for the
session, but that one is probably expected.
This seems problematic because if one session runs VACUUM; and then
doesn't do anything else just stays open, it blocks other sessions
from performing rewrite ALTER TABLEs on the otherwise unused global
temporary tables.
11. How should GTTs interact with DISCARD?
DROP TABLE IF EXISTS gtt_disc;
DROP SEQUENCE IF EXISTS gtt_disc_s;
CREATE GLOBAL TEMP TABLE gtt_disc (a int);
CREATE GLOBAL TEMP SEQUENCE gtt_disc_s;
INSERT INTO gtt_disc VALUES (1), (2), (3);
SELECT nextval('gtt_disc_s'); -- 1
SELECT nextval('gtt_disc_s'); -- 2
DISCARD ALL;
SELECT count(*) FROM gtt_disc; -- 3
SELECT nextval('gtt_disc_s'); -- 3
