Slony1 PostgreSQL Replication - Possible bug when adding a table dynamically

Author

Possible bug when adding a table dynamically

Nuno Santos

2005-07-13, 11:24 am

Hi.

I'm trying to add a table dynamically to a set while it is being
replicated. After reading the Slony documentation I figured out that the
correct procedure is something like:

- Create a table using ddlscript (or execute script from slonik)
- Create a new set
- Add the table to the newly created set
- Update the new set with all the subscribers of the initial set
- Merge the sets.

After some trial and error I arrived to the following sequence of calls
to stored procedures:

select _ns.ddlscript(1, 'create table public.t (f VARCHAR(20) PRIMARY
KEY);', 0);
select _ns.storeset(2, 'Temp set');
select _ns.setAddTable (2, 999, 'public.t', 't_pkey', 'New table');
select _ns.subscribeset(2, 1, 2, false);
select _ns.mergeset(1, 2);

I'm using stored procedures because my goal is to add tables
programatically, using JDBC or ODBC.

The problem is that when I execute these commands without any pause
between them (copy'n'paste of all commands into the psql prompt) the new
table is added to set 1 on the master but not on the slave. In the
slave, the table is created but not added to the set. If I do a single
pause of a few seconds before calling mergeset, everything works fine.
This is, if I execute the first 4 commands with no stop between them I
end up with a new set with table t being replicated correctly in the two
nodes. If after that I merge the two sets, it works fine and the new
table is added to the initial set. It's just if I execute all the 5
commands without stopping that things fail. Seems like a concurrency bug.

My configuration consists of two databases running on the same
postmaster instance. There is a single set, with one database being the
master and the other the subscriber.

Is this really a bug or am I doing something wrong? I would also
appreciate any comments on the way I'm adding tables dynamically to a
set. I wasn't able to find any example of how to do it, so I'm not sure
if there isn't a better way.

Cheers,
Nuno Santos

Thomas Pundt

2005-07-14, 3:24 am

Hi,

On Wednesday 13 July 2005 18:11, Nuno Santos wrote:
| After some trial and error I arrived to the following sequence of calls
| to stored procedures:
|
| select _ns.ddlscript(1, 'create table public.t (f VARCHAR(20) PRIMARY
| KEY);', 0);
| select _ns.storeset(2, 'Temp set');
| select _ns.setAddTable (2, 999, 'public.t', 't_pkey', 'New table');
| select _ns.subscribeset(2, 1, 2, false);
| select _ns.mergeset(1, 2);
|
| I'm using stored procedures because my goal is to add tables
| programatically, using JDBC or ODBC.
[...]
| Seems like a concurrency bug.
[...]
| Is this really a bug or am I doing something wrong? I would also
| appreciate any comments on the way I'm adding tables dynamically to a
| set. I wasn't able to find any example of how to do it, so I'm not sure
| if there isn't a better way.

I'd rather call it a concurrency bug with your procedure; you have to
wait for the slave to acknowledge the operations. Slonik therefore
provides the "wait for event" command.

Ciao,
Thomas Pundt

--
Dr. Thomas Pundt <thomas.pundt- TxOGGw7xYDWoYr4blSSd
5g@public.gmane.org> ---- http://rp-online.de/ ----
fon: +49 211 5051920 - fax: +49 211 5051001991

Nuno Santos

2005-07-18, 7:23 am

Thomas Pundt wrote:
> Hi,
>
> On Wednesday 13 July 2005 18:11, Nuno Santos wrote:
> | After some trial and error I arrived to the following sequence of calls
> | to stored procedures:
> |
> | select _ns.ddlscript(1, 'create table public.t (f VARCHAR(20) PRIMARY
> | KEY);', 0);
> | select _ns.storeset(2, 'Temp set');
> | select _ns.setAddTable (2, 999, 'public.t', 't_pkey', 'New table');
> | select _ns.subscribeset(2, 1, 2, false);
> | select _ns.mergeset(1, 2);
> |
> | I'm using stored procedures because my goal is to add tables
> | programatically, using JDBC or ODBC.
> [...]
> | Seems like a concurrency bug.
> [...]
> | Is this really a bug or am I doing something wrong? I would also
> | appreciate any comments on the way I'm adding tables dynamically to a
> | set. I wasn't able to find any example of how to do it, so I'm not sure
> | if there isn't a better way.
>
> I'd rather call it a concurrency bug with your procedure; you have to
> wait for the slave to acknowledge the operations. Slonik therefore
> provides the "wait for event" command.

Ok, makes sense now. Is there a Slony stored procedure equivalent to the
'wait for event' command? I want to create a stored procedure for adding
a new table so the slonik command is of no use for me there. Of course I
can reimplement the code from slonik, but perhaps there is some other
way that I haven't discovered yet.

Another question, it seems to make sense to have a createTable() stored
procedure in Slon. Why doesn't such a function exist?

Cheers,
Nuno Santos

Sponsored Links