Skip to content

Commit 1a22a8a

Browse files
anarazelanarazel
committed
tests: Expand temp table tests to some pin related matters
Added tests: - recovery from running out of unpinned local buffers - that we don't run out of unpinned buffers due to read stream (only recently fixed, in 92fc685) - temp tables can't be dropped while in use by cursors Discussion: weskknhckugbdm2yt7sa2uq53xlsax67gcdkac34sanb7qpd3p@hcc2wadao5wy Discussion: https://postgr.es/m/ge6nsuddurhpmll3xj22vucvqwp4agqz6ndtcf2mhyeydzarst@l75dman5x53p
1 parent 99aeb84 commit 1a22a8a

File tree

3 files changed

+264
-1
lines changed

3 files changed

+264
-1
lines changed

‎src/test/regress/expected/temp.out

Lines changed: 156 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -410,3 +410,159 @@ SELECT current_schema() ~ 'pg_temp' AS is_temp_schema;
410410

411411
PREPARE TRANSACTION 'twophase_search';
412412
ERROR: cannot PREPARE a transaction that has operated on temporary objects
413+
-- Tests to verify we recover correctly from exhausting buffer pins and
414+
-- related matters.
415+
-- use lower possible buffer limit to make the test cheaper
416+
\c
417+
SET temp_buffers = 100;
418+
CREATE TEMPORARY TABLE test_temp(a int not null unique, b TEXT not null, cnt int not null);
419+
INSERT INTO test_temp SELECT generate_series(1, 10000) as id, repeat('a', 200), 0;
420+
-- should be at least 2x as large than temp_buffers
421+
SELECT pg_relation_size('test_temp') / current_setting('block_size')::int8 > 200;
422+
?column?
423+
----------
424+
t
425+
(1 row)
426+
427+
-- Don't want cursor names and plpgsql function lines in the error messages
428+
\set VERBOSITY terse
429+
/* helper function to create cursors for each page in [p_start, p_end] */
430+
CREATE FUNCTION test_temp_pin(p_start int, p_end int)
431+
RETURNS void
432+
LANGUAGE plpgsql
433+
AS $f$
434+
DECLARE
435+
cursorname text;
436+
query text;
437+
BEGIN
438+
FOR i IN p_start..p_end LOOP
439+
cursorname = 'c_'||i;
440+
query = format($q$DECLARE %I CURSOR FOR SELECT ctid FROM test_temp WHERE ctid >= '( %s, 1)'::tid $q$, cursorname, i);
441+
EXECUTE query;
442+
EXECUTE 'FETCH NEXT FROM '||cursorname;
443+
-- for test development
444+
-- RAISE NOTICE '%: %', cursorname, query;
445+
END LOOP;
446+
END;
447+
$f$;
448+
-- Test overflow of temp table buffers is handled correctly
449+
BEGIN;
450+
-- should work, below max
451+
SELECT test_temp_pin(0, 9);
452+
test_temp_pin
453+
---------------
454+
455+
(1 row)
456+
457+
-- should fail, too many buffers pinned
458+
SELECT test_temp_pin(10, 105);
459+
ERROR: no empty local buffer available
460+
ROLLBACK;
461+
BEGIN;
462+
-- have some working cursors to test after errors
463+
SELECT test_temp_pin(0, 9);
464+
test_temp_pin
465+
---------------
466+
467+
(1 row)
468+
469+
FETCH NEXT FROM c_3;
470+
ctid
471+
-------
472+
(3,2)
473+
(1 row)
474+
475+
-- exhaust buffer pins in subtrans, check things work after
476+
SAVEPOINT rescue_me;
477+
SELECT test_temp_pin(10, 105);
478+
ERROR: no empty local buffer available
479+
ROLLBACK TO SAVEPOINT rescue_me;
480+
-- pre-subtrans cursors continue to work
481+
FETCH NEXT FROM c_3;
482+
ctid
483+
-------
484+
(3,3)
485+
(1 row)
486+
487+
-- new cursors with pins can be created after subtrans rollback
488+
SELECT test_temp_pin(10, 94);
489+
test_temp_pin
490+
---------------
491+
492+
(1 row)
493+
494+
-- Check that read streams deal with lower number of pins available
495+
SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp;
496+
count | max_a | min_a | max_cnt
497+
-------+-------+-------+---------
498+
10000 | 10000 | 1 | 0
499+
(1 row)
500+
501+
ROLLBACK;
502+
-- Check that temp tables with existing cursors can't be dropped.
503+
BEGIN;
504+
SELECT test_temp_pin(0, 1);
505+
test_temp_pin
506+
---------------
507+
508+
(1 row)
509+
510+
DROP TABLE test_temp;
511+
ERROR: cannot DROP TABLE "test_temp" because it is being used by active queries in this session
512+
COMMIT;
513+
-- Check that temp tables with existing cursors can't be dropped.
514+
BEGIN;
515+
SELECT test_temp_pin(0, 1);
516+
test_temp_pin
517+
---------------
518+
519+
(1 row)
520+
521+
TRUNCATE test_temp;
522+
ERROR: cannot TRUNCATE "test_temp" because it is being used by active queries in this session
523+
COMMIT;
524+
-- Check that temp tables that are dropped in transaction that's rolled back
525+
-- preserve buffer contents
526+
SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp;
527+
count | max_a | min_a | max_cnt
528+
-------+-------+-------+---------
529+
10000 | 10000 | 1 | 0
530+
(1 row)
531+
532+
INSERT INTO test_temp(a, b, cnt) VALUES (-1, '', 0);
533+
BEGIN;
534+
INSERT INTO test_temp(a, b, cnt) VALUES (-2, '', 0);
535+
DROP TABLE test_temp;
536+
ROLLBACK;
537+
SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp;
538+
count | max_a | min_a | max_cnt
539+
-------+-------+-------+---------
540+
10001 | 10000 | -1 | 0
541+
(1 row)
542+
543+
-- Check that temp table drop is transactional and preserves dirty
544+
-- buffer contents
545+
UPDATE test_temp SET cnt = cnt + 1 WHERE a = -1;
546+
BEGIN;
547+
DROP TABLE test_temp;
548+
ROLLBACK;
549+
SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp;
550+
count | max_a | min_a | max_cnt
551+
-------+-------+-------+---------
552+
10001 | 10000 | -1 | 1
553+
(1 row)
554+
555+
-- Check that temp table truncation is transactional and preserves dirty
556+
-- buffer contents
557+
UPDATE test_temp SET cnt = cnt + 1 WHERE a = -1;
558+
BEGIN;
559+
TRUNCATE test_temp;
560+
ROLLBACK;
561+
SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp;
562+
count | max_a | min_a | max_cnt
563+
-------+-------+-------+---------
564+
10001 | 10000 | -1 | 2
565+
(1 row)
566+
567+
-- cleanup
568+
DROP FUNCTION test_temp_pin(int, int);

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -108,7 +108,7 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson
108108
# ----------
109109
# Another group of parallel tests
110110
# with depends on create_misc
111-
# NB: temp.sql does a reconnect which transiently uses 2 connections,
111+
# NB: temp.sql does reconnects which transiently use 2 connections,
112112
# so keep this parallel group to at most 19 tests
113113
# ----------
114114
test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml

‎src/test/regress/sql/temp.sql

Lines changed: 107 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -311,3 +311,110 @@ SET search_path TO 'pg_temp';
311311
BEGIN;
312312
SELECT current_schema() ~ 'pg_temp' AS is_temp_schema;
313313
PREPARE TRANSACTION 'twophase_search';
314+
315+
316+
-- Tests to verify we recover correctly from exhausting buffer pins and
317+
-- related matters.
318+
319+
-- use lower possible buffer limit to make the test cheaper
320+
\c
321+
SET temp_buffers = 100;
322+
323+
CREATE TEMPORARY TABLE test_temp(a int not null unique, b TEXT not null, cnt int not null);
324+
INSERT INTO test_temp SELECT generate_series(1, 10000) as id, repeat('a', 200), 0;
325+
-- should be at least 2x as large than temp_buffers
326+
SELECT pg_relation_size('test_temp') / current_setting('block_size')::int8 > 200;
327+
328+
-- Don't want cursor names and plpgsql function lines in the error messages
329+
\set VERBOSITY terse
330+
331+
/* helper function to create cursors for each page in [p_start, p_end] */
332+
CREATE FUNCTION test_temp_pin(p_start int, p_end int)
333+
RETURNS void
334+
LANGUAGE plpgsql
335+
AS $f$
336+
DECLARE
337+
cursorname text;
338+
query text;
339+
BEGIN
340+
FOR i IN p_start..p_end LOOP
341+
cursorname = 'c_'||i;
342+
query = format($q$DECLARE %I CURSOR FOR SELECT ctid FROM test_temp WHERE ctid >= '( %s, 1)'::tid $q$, cursorname, i);
343+
EXECUTE query;
344+
EXECUTE 'FETCH NEXT FROM '||cursorname;
345+
-- for test development
346+
-- RAISE NOTICE '%: %', cursorname, query;
347+
END LOOP;
348+
END;
349+
$f$;
350+
351+
352+
-- Test overflow of temp table buffers is handled correctly
353+
BEGIN;
354+
-- should work, below max
355+
SELECT test_temp_pin(0, 9);
356+
-- should fail, too many buffers pinned
357+
SELECT test_temp_pin(10, 105);
358+
ROLLBACK;
359+
360+
BEGIN;
361+
-- have some working cursors to test after errors
362+
SELECT test_temp_pin(0, 9);
363+
FETCH NEXT FROM c_3;
364+
-- exhaust buffer pins in subtrans, check things work after
365+
SAVEPOINT rescue_me;
366+
SELECT test_temp_pin(10, 105);
367+
ROLLBACK TO SAVEPOINT rescue_me;
368+
-- pre-subtrans cursors continue to work
369+
FETCH NEXT FROM c_3;
370+
371+
-- new cursors with pins can be created after subtrans rollback
372+
SELECT test_temp_pin(10, 94);
373+
374+
-- Check that read streams deal with lower number of pins available
375+
SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp;
376+
377+
ROLLBACK;
378+
379+
380+
-- Check that temp tables with existing cursors can't be dropped.
381+
BEGIN;
382+
SELECT test_temp_pin(0, 1);
383+
DROP TABLE test_temp;
384+
COMMIT;
385+
386+
-- Check that temp tables with existing cursors can't be dropped.
387+
BEGIN;
388+
SELECT test_temp_pin(0, 1);
389+
TRUNCATE test_temp;
390+
COMMIT;
391+
392+
-- Check that temp tables that are dropped in transaction that's rolled back
393+
-- preserve buffer contents
394+
SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp;
395+
INSERT INTO test_temp(a, b, cnt) VALUES (-1, '', 0);
396+
BEGIN;
397+
INSERT INTO test_temp(a, b, cnt) VALUES (-2, '', 0);
398+
DROP TABLE test_temp;
399+
ROLLBACK;
400+
SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp;
401+
402+
-- Check that temp table drop is transactional and preserves dirty
403+
-- buffer contents
404+
UPDATE test_temp SET cnt = cnt + 1 WHERE a = -1;
405+
BEGIN;
406+
DROP TABLE test_temp;
407+
ROLLBACK;
408+
SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp;
409+
410+
-- Check that temp table truncation is transactional and preserves dirty
411+
-- buffer contents
412+
UPDATE test_temp SET cnt = cnt + 1 WHERE a = -1;
413+
BEGIN;
414+
TRUNCATE test_temp;
415+
ROLLBACK;
416+
SELECT count(*), max(a) max_a, min(a) min_a, max(cnt) max_cnt FROM test_temp;
417+
418+
419+
-- cleanup
420+
DROP FUNCTION test_temp_pin(int, int);

0 commit comments

Comments
 (0)