Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE test1 (a INTEGER);
CREATE PROCEDURE test_commit()
LANGUAGE plpgsql
AS $f$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        COMMIT;
    END LOOP;
END;
$f$;

CREATE TABLE

CREATE PROCEDURE

CALL test_commit();--this is now unwrapped, works fine

CALL

--dbfiddle wraps this in a transaction
select * from test1 limit 0;
CALL test_commit();

a

SELECT 0

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function test_commit() line 5 at COMMIT

start transaction;--this explicitly wraps this in a transaction
CALL test_commit();

START TRANSACTION

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function test_commit() line 5 at COMMIT

rollback;

ROLLBACK

rollback;
CALL test_commit();--this won't work

ROLLBACK

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function test_commit() line 5 at COMMIT

select * from test1 limit 0;
vacuum test1;

a

SELECT 0

ERROR:  VACUUM cannot run inside a transaction block