I have created a procedure in Postgres that will create a table with the contents of another three tables (SELECT with JOINS). Then the resulting table will be altered adding two columns that didnt exist before and finally within the same procedure I have created 3 triggers that will be applied on those three tables so anytime a new write happens on any of those, the same entity will be recorded in the new table
I know the procedure itself is atomic and that is transactional from its own scope, but the procedure has no means of knowing anything about the three tables. Im afraid that in the time between the new table creation and the creation of the triggers, some writes to the existing tables could happen thus desynchronizing the new table, which wont register those writes. That cannot happen.
My table creation/trigger creation procedure looks like this:
CREATE OR REPLACE PROCEDURE myschema.table_creation()
LANGUAGE plpgsql
AS $procedure$
begin
create table newtable as
SELECT * FROM myschema.session a NATURAL JOIN (SELECT * FROM myschema.message b NATURAL left JOIN myschema.data) as d;
ALTER TABLE myschema.newtable ADD created_at timestamp;
ALTER TABLE myschema.newtable ADD source text;
CREATE TRIGGER mytrigger
after INSERT
ON myschema.session
FOR EACH ROW
EXECUTE PROCEDURE myschema.trigger_proc();
CREATE TRIGGER mytrigger
after INSERT
ON myschema.messages
FOR EACH ROW
EXECUTE PROCEDURE myschema.trigger_proc();
CREATE TRIGGER mytrigger
after INSERT
ON myschema.data
FOR EACH ROW
EXECUTE PROCEDURE myschema.trigger_proc();
end;
$procedure$
;
How can I lock the writes in the existing tables to postpone them until the whole table_creation procedure has finished? Otherwise I would have a race condition and some entities would be lost in the new table. i don't think my procedure in its current state has any protection against writes
LOCKcommand...