Slony1 PostgreSQL Replication - Adding a new table from a stored procedure

Author

Adding a new table from a stored procedure

Nuno Santos

2005-07-18, 7:23 am

Hello.

I'm still struggling to add a new table to a set dynamically. I think I
figured out how to do it, but I found a strange behaviour that I would
like to understand.

I'm creating a table using a stored procedure. This is a simplified
version of how it looks like:

CREATE OR REPLACE function _cluster.createTable (text, text) RETURNS
void as $$
declare
p_table alias for $1;
p_creation_script al
ias for $2;
v_row record;
begin
perform _cluster.ddlscript(1, p_creation_script, 0);
perform _cluster.storeset(2, 'Temporary set');
perform _cluster.setaddtable(2, 42, p_table, p_table || '_pkey',
'new table');

for v_row in
select * from _cluster.sl_subscribe
where sub_set=1
loop
perform _cluster.subscribeset(
v_tmp_set_id,
v_row.sub_provider,
v_row.sub_receiver,
v_row.sub_forward);
end loop;

perform _cluster.mergeset(1, 2);
return;
end;

This fails with the same problem I mentioned before. As I was told that
I should wait for the events to propagate to other replicas before
calling mergeset, I tried to make a small pause inside the stored
procedure before calling mergeset. So something like:

loop
perform _cluster.subscribeset(
v_tmp_set_id,
v_row.sub_provider,
v_row.sub_receiver,
v_row.sub_forward);
end loop;

perform sleep(10);
perform _cluster.mergeset(1, 2);
return;
end;

sleep is another stored procedure that I created. This doesn't work
either, failing for the same reason as before.

I then tried to execute mergeset in a separate call and this time it worked.

This is good enough for my purposes, but I would like to know what's
going on. I would guess that the stored procedure is executed inside a
single transaction and that somehow the events are not propagated until
that transaction is closed. Am I right?

And another related question, how can I ensure atomicity between the two
calls (the first to create the new table and temporary set, the second
to merge the sets)? I want to make sure that the list of subscribers is
not changed between the two calls.

Thank you,
Nuno Santos

Nuno Santos

2005-07-21, 3:24 am

Hello.

After reading the documentation more carefully I finally figured out why
a stored procedure can't be used to add a table dynamically to a set
that's being currently replicated.

I'm writing so that people reading my previous email in the future will
also know what was wrong with my idea.

It seems that in Postgres a stored procedure is executed as a single
transaction. And somehow, it also seems that no triggers are fired
before the transaction is committed. Hence the triggers that would force
slony to act and to start spreading the events to other nodes are not
fired during the execution of the stored procedure, making it impossible
to wait for the new temporary set to be updated in all nodes before
merging it with the old set. So my idea of having a single stored
procedure would never work.

My current solution is a bit of an hack. I created a stored procedure
that will generate a Slonik script that will accomplish the task of
adding a new table. The reason why I'm creating the script inside a
stored procedure is simplicity. I need to obtain several values from the
slony tables, like valid set and table ids that can be used to create
the new temporary set and the new table, and the list of the nodes that
are currently listening. I could do this using ODBC from my C program,
but the coding required to do so from C is very complex. pgSQL is much
more compact and simple for this type of things. Instead, my C program
calls this stored procedure to retrieve the slonik script and then
spawns a slonik isntance to run it. So far it seems to be working well.

Anyway, I'm a newbie to Slony and databases in general, and probably
there are better ways of doing this. So I apologize if I'm proposing an
excessively complex solution, and would appreciate any suggestions to
improve if.

Cheers,
Nuno

Nuno Santos wrote:
[color=darkred]
worked.[color=darkred]

Sponsored Links