I would like to know if it is possible to run a query where only records already recorded outside the current transaction are returned. The objective is to allow, when updating a table, to remove old records from another table without interfering with the insertion of current records that are possibly inserted in the same transaction.
create table entity_authorization(id varchar);
create table entity (entity_field varchar);
CREATE or REPLACE FUNCTION revoke_obsolete_autorization_entity()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; --ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
DELETE from entity_authorization WHERE TRUE; -- i need remove just rows exists on database before this transaction
RETURN new;
END
$$;
CREATE or REPLACE FUNCTION request_autorization_entity()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
PERFORM 1 from entity_authorization;
if not found then
raise EXCEPTION 'not allowed';
end if;
RETURN new;
END
$$;
CREATE TRIGGER revoke_obsolete_autorization
BEFORE UPDATE
ON entity
FOR EACH ROW
EXECUTE PROCEDURE revoke_obsolete_autorization_entity();
CREATE CONSTRAINT TRIGGER request_autorization
AFTER UPDATE
ON entity
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE request_autorization_entity();
--INSERTS
DELETE from entity_authorization WHERE TRUE;
DELETE FROM entity WHERE TRUE;
INSERT INTO entity_authorization VALUES('AAA');
INSERT INTO entity VALUES ('value');
--Transaction Test
BEGIN;
INSERT INTO entity_authorization VALUES ('AAA'), ('BBB'); --don't change this to after 'update' line
UPDATE entity SET entity_field = 'new_value' WHERE entity_field = 'value'; -- error because have zero rows in entity_authorization
COMMIT;