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:

>> 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	alias 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
>>
>> _______________________________________________
>> Slony1-general mailing list
>> Slony1-general at gborg.postgresql.org
>> http://gborg.postgresql.org/mailman/listinfo/slony1-general
>>