Allow transactions inside YESQL functions

[phoe]

I have a complex installation function that is composed of a CREATE TABLE and two CREATE INDEX statements:

-- name: create-table-chapter-permission @execute
-- Creates the chapter permission table.
BEGIN;
CREATE TABLE chapter_permission (
    player_id        integer                 REFERENCES player(player_id)
                                             ON UPDATE CASCADE,
    player_group_id  integer                 REFERENCES player_group(player_group_id)
                                             ON UPDATE CASCADE,
    permission       chapter_permission_type NOT NULL,
    chapter_id       integer                 NOT NULL REFERENCES chapter(chapter_id)
                                             ON UPDATE CASCADE,
    CONSTRAINT chapter_permission_player_or_group
    CHECK ((player_id IS NULL     AND player_group_id IS NOT NULL) OR
           (player_id IS NOT NULL AND player_group_id IS NULL)));
CREATE UNIQUE INDEX chapter_permission_player_index
    ON chapter_permission (player_id, permission, chapter_id)
    WHERE player_id IS NOT NULL;
CREATE UNIQUE INDEX chapter_permission_player_group_index
    ON chapter_permission (player_group_id, permission, chapter_id)
    WHERE player_group_id IS NOT NULL;
COMMIT;

It seems that I am unable to execute them within one CL-YESQL transaction:

Database error 42601: cannot insert multiple commands into a prepared statement
   [Condition of type CL-POSTGRES-ERROR:COLUMNS-ERROR]

How should I proceed in this case? Should I create the transaction on the Lisp side and call the three individual subfunctions there?

[phoe]

Hm. Even if I split the above function into three subfunctions, I still think that transactions should work in this case - I can't see splitting this function into five just to satisfy the no-multiple-commands constraint.

-- name: set-initial-data @execute
-- Fills the database with the initial data.
BEGIN;
    INSERT INTO player(player_id, login, email, display_name, pass_hash, pass_salt)
        VALUES (1, 'narrator', 'narrator@gateway.localhost', 'Narrator', ''::bytea, ''::bytea);
    INSERT INTO persona(persona_id, persona_name)
        VALUES (1, 'Narrator');
    INSERT INTO player_group(player_group_id, player_group_name)
        VALUES (1, 'Everyone');
    INSERT INTO players_groups(player_id, player_group_id)
        VALUES (1, 1);
    INSERT INTO owners_borrowers(player_id, persona_id, is_owner)
        VALUES (1, 1, TRUE);
COMMIT;

[phoe]

I've worked around the issue by not using CL-YESQL for executing multi-statement files, and instead depending on the freshly hacked https://github.com/phoe/postmodern-execute-file/ library for executing those.

[ruricolist]

I'm reopening this as I think this use case should be supported, perhaps with a @block annotation.

[phoe]

FYI: https://github.com/marijnh/Postmodern/issues/192 https://github.com/marijnh/Postmodern/pull/193

[ruricolist]

While I'm intrigued to see that a Postgres parser in Lisp exists, Yesql is intended to be agnostic as to the SQL dialect used, so my first thought is to keep it simple and distinguish commands based on indentation.

[phoe]

It might need to be done per-database anyway due to the differences they have in transaction support.