PostgreSQL – Unable to Create COMMIT Inside Procedure

plpgsqlpostgresql

I'm trying to learn PostgreSQL stored procedures. Specifically creating the procedure below in PSQL.

CREATE OR REPLACE PROCEDURE BUILD_AND_POPULATE(INOUT cresults refcursor) 
    LANGUAGE PLPGSQL 
    AS $$
    BEGIN
        BEGIN; -- I've tried removing this but the behaviour is the same
        cresults:= 'cur';
        DROP TABLE IF EXISTS procsampledata;
        CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x;  
        COMMIT;
        OPEN cresults FOR SELECT * FROM procsampledata;  
    END;
$$;

Then I execute it like so, but receive an error:

postgres=# call build_and_populate(null);
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function build_and_populate(refcursor) line 6 at COMMIT

I've tried with AUTOCOMMIT set to both on and off.

This is my Postgres version

 PostgreSQL 11.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit

Can anyone see what I'm doing wrong? Thank you!

Best Answer

The

BEGIN;

is wrong and will cause an error. You cannot start a transaction inside a procedure, because there is already an active transaction.

You can end a transaction, which implies that a new transaction is started immediately.

Without the BEGIN;, your procedure works just fine.

The problem must be with the way you are calling it. As the documentation says:

Transaction control is only possible in CALL or DO invocations from the top level or nested CALL or DO invocations without any other intervening command. For example, if the call stack is CALL proc1() → CALL proc2() → CALL proc3(), then the second and third procedures can perform transaction control actions. But if the call stack is CALL proc1() → SELECT func2() → CALL proc3(), then the last procedure cannot do transaction control, because of the SELECT in between.

There is probably a SELECT in your call stack.

Another possibility is that you explicitly started a transaction with BEGIN before calling the stored procedure.

This does not work either, which is an undocumented implementation restriction that may be fixed in the future. See this thread for reference.

Related Solutions

PostgreSQL – Dell PE T420 (Perc H710) Performance Issues Compared to MacMini

After asking on pgsql-performance list, Jeff Janes figured out that the cause was associated to the default collation used by Postgres (see this link for more informations). MacMini was using the much performing collation while Dell T420 was using the en/US collation.

T420 (Postgres 9.4.1)

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 pen       | pen      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

MacMini (Postgres 9.0.13)

                                    List of databases
       Name        |   Owner    | Encoding | Collation | Ctype |    Access privileges    
-------------------+------------+----------+-----------+-------+-------------------------
 caldav            | caldav     | UTF8     | C         | C     | 
 collab            | collab     | UTF8     | C         | C     | 
 device_management | _devicemgr | UTF8     | C         | C     | 
 pen               | pen        | UTF8     | C         | C     | 
 postgres          | _postgres  | UTF8     | C         | C     | 
 roundcubemail     | roundcube  | UTF8     | C         | C     | 
 template0         | _postgres  | UTF8     | C         | C     | =c/_postgres           +
                   |            |          |           |       | _postgres=CTc/_postgres
 template1         | _postgres  | UTF8     | C         | C     | =c/_postgres           +
                   |            |          |           |       | _postgres=CTc/_postgres
(8 rows)

After setting collation on T420 "C" the A transaction went from 195 seconds to 33 seconds against 40 seconds on Mac Mini; B type transaction went from 141 seconds to 78 seconds against 101 seconds on Mac Mini. This is the best performance improvement after modifing BIOS settings. Many kernel adjustments didn't provide significant improvements.

So, running the following command will initialize a new database with collation C and encoding UTF8:

/usr/local/pgsql/bin/initdb -D /path/to/your/data --no-locale --encoding=UTF8

I hope this post will help other people in the future. After reading about a lot of digressions about kernel, virtual memory, RAID controllers, disk cache, WAL and other tech stuff I never found someone talking about collations.

PostgreSQL – Cannot Create RULE from Stored Procedure

Thank you everyone for suggestions in comments. I've found out two solutions.

Use SQL stored procedure instead of PL/pgSQL:

    CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
      RETURNS VOID AS $test_create_rule_from_stored_procedure$
    CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
    DO INSTEAD
      INSERT INTO person_y (
        firstname,
        lastname)
      VALUES (
        new.firstname,
        new.lastname
      );
    $test_create_rule_from_stored_procedure$ LANGUAGE SQL;

Stay with PL/pgSQL but specify query for creating rule as string literal:

    CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
      RETURNS INTEGER AS $test_create_rule_from_stored_procedure$
    BEGIN
      EXECUTE 'CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
      DO INSTEAD
        INSERT INTO person_y (
          firstname,
          lastname)
        VALUES (
          new.firstname,
          new.lastname
        );';

      RETURN 1;
    END;
    $test_create_rule_from_stored_procedure$ LANGUAGE plpgsql;

Best Answer

Related Solutions

PostgreSQL – Dell PE T420 (Perc H710) Performance Issues Compared to MacMini

PostgreSQL – Cannot Create RULE from Stored Procedure

Related Question