2

At https://wiki.postgresql.org/wiki/Serializable#PostgreSQL_Implementation, it says

Any transaction which is run at a transaction isolation level other than SERIALIZABLE will not be affected by SSI. If you want to enforce business rules through SSI, all transactions should be run at the SERIALIZABLE transaction isolation level, and that should probably be set as the default.

Instead of merely setting SERIALIZABLE as the default, can we make it the only allowed isolation level?

1

2 Answers 2

3

No, all you can do is set the default_transaction_isolation parameter to serializable and hope that nobody explicitly selects a different isolation level.

0

Snippets to use triggers on any modification of any table to check the isolation level:

Set up:

-- function to verify current iso level
create or replace function check_iso_level_fn ()
  returns trigger
  as $$
begin
  assert current_setting('transaction_isolation') = 'serializable';
  return null;
end;
$$
language plpgsql;

-- function to install triggers on all tables
create or replace function check_iso_level_all ()
  returns void
  as $$
declare
  r record;
begin
  for r in (
    select
      tablename
    from
      pg_tables
    where
      schemaname = 'public')
    loop
      execute format('
create or replace trigger check_iso_level__%I
  before insert or update or delete or truncate on %I
  for each statement
  execute function check_iso_level_fn()', r.tablename, r.tablename);
    end loop;
end;
$$
language plpgsql;

-- wrapper for check_iso_level_all
create or replace function check_iso_level_event_fn ()
  returns event_trigger
  as $$
begin
  perform
    check_iso_level_all ();
end;
$$
language plpgsql;

-- trigger to run check_iso_level_all after any table is created
create event trigger on_table_create_check_iso_level on ddl_command_end
  when TAG in ('CREATE TABLE', 'CREATE TABLE AS')
    execute procedure check_iso_level_event_fn ();

-- set up triggers for all existing tables
select check_iso_level_all();

Remove:

drop event trigger on_table_create_check_iso_level;

do $$
declare
  r RECORD;
begin
  for r in
  select
    c.relname as table_name,
    t.tgname as tgname
  from
    pg_trigger t
    join pg_class c on t.tgrelid = c.oid
    join pg_namespace n on c.relnamespace = n.oid
  where
    t.tgname like 'check_iso_level__%'
    and n.nspname = 'public' loop
      execute format('DROP TRIGGER %I ON %I', r.tgname, r.table_name);
    end loop;
end
$$;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.