PLPGSQL Procedures and Transactions

[wolframm]

I'm trying recreate an existing Firebase project on Supabase. I want client web apps to call PLPGSQL stored procedures exposed by pg_graphql or Postgrest to update the database.

I'm used to Firebase Cloud Functions, which support transactions consisting of multiple read statements followed by multiple write statements.

Having studied the Postgres docs and googled extensively, I cannot get clarity on how to do similar transactions using PLPGSQL stored procedures. Being a complete Postgres noob, I probably don't see the forest for the trees (at least that's my hope!).

Could anyone tell me if the following code behaves as intended, or if not, how to get the intended behavior?

--     Setting up a table with some data...
create table language
(
    code        text primary key,
    name        text    not null unique,
    native_name text    not null unique,
    is_primary  boolean not null default false,
    is_reviewed boolean not null default false
);
grant select on language to public;

insert into language
values ('ar', 'Arabic', 'العربية', false, false),
       ('bg', 'Bulgarian', 'български', false, false),
       ('cs', 'Czech', 'Český', false, false),
       ('da', 'Danish', 'Dansk', false, false),
       ('de', 'German', 'Deutsch', false, false),
       ('el', 'Greek', 'ελληνική', false, false),
       ('en', 'English', 'English', true, true),
       ('es', 'Spanish', 'Español', false, false),
       ('et', 'Estonian', 'Eesti', false, false),
       ('fi', 'Finnish', 'native name fi', false, false),
       ('fr', 'French', 'native name fr', false, false),
       ('zh', 'Chinese (Simplified)', 'native name zh', false, false);


-- "CLOUD" FUNCTION exposed to client apps
create function language_set_primary(language_code text) returns void
    language plpgsql
    security definer
as
$$
begin
    -- this procedure is where I would like to do the equivalent of firestore().runTransaction(...)
    call language_set_primary_tx(language_code);
end;
$$;
grant execute on function language_toggle_reviewed(text) to authenticated;


-- TRANSACTION (???)
-- Intended behaviour:
-- If  another user changes the data a microsecond after the SELECT statements were executed, 
-- then the SELECTs are executed again and any uncommitted UPDATES are rolled back and 
-- attempted again, unless now the checks performed based on the SELECTS raise exceptions.
create procedure language_set_primary_tx(language_code text)
    language plpgsql
    security definer
as
$$
declare
    _language         language;
    _primary_language language;
begin
    --     READ section of the transaction (Firestore: tx.get(...))
    select * into strict _language from language where language.code = language_code;
    if _language.is_primary then
        raise exception 'language is already primary';
    end if;
    if not _language.is_reviewed then raise exception 'language is not reviewed'; end if;
    select * into _primary_language from language where language.is_primary is true;
    --     UPDATE section of the transaction (Firestore: tx.update(...))
    update language set is_primary = true where code = language_code;
    update language set is_primary = false where code = _primary_language.code;
    -- here would potentially follow many more UPDATE statements, also into tables other than "language"
end;
$$;

[GaryAustin1]

PostgREST surrounds rpc functions with transaction logic but the function itself (if run in sql window is not). All operations must succeed or all are rolled back.

From Postgres docs
It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction.

From PostgREST docs (POST is used for rpc call): PostgREST executes POST requests in a read/write transaction except for functions marked as IMMUTABLE or STABLE. Those must not modify the database and are executed in a read-only transaction compatible for read-replicas.

"Trapping Errors" here might also be relevent: https://www.postgresql.org/docs/14/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

I've not read thru your function code, so hopefully this points you in the direction you need for more info.

[wolframm]

Thank you! This was one of those miss-forest-for-trees things. What I was looking for was simply:

START TRANSACTION
... read and write stuff...
COMMIT;