Slony1 PostgreSQL Replication - Add single sequence to replicated set

Author

Add single sequence to replicated set

John Sidney-Woollett

2006-01-07, 9:23 am

I need to add a single sequence to an existing replicated set (slony
1.1.0 and Postgres 7.4.6)

I remember that it is not advised to have a set with a single sequence
(although I can't remember why).

So how can I add a sequence so that it is replicated?

Which of these scenarios is OK?

1) Create temp set. Add sequence to it. Subscribe all slaves to it.
Merge with existing set.

2) Drop existing set. Recreate the set (same ID), add the orginal
tables, sequences (with their previous IDs) and the extra new sequence
(with a new ID). Subscribe all slaves to this set.

The set currently contains 1 table with 2 rows, and 1 sequence. And I
only need to add one extra sequence to the set.

Which option is better/easier/less likely to cause problems? Are both valid?

Thanks

John

John Sidney-Woollett

2006-01-10, 7:24 am

Thanks for the information you've both given.

I'm now happy to proceed with adding the sequence using the
"Create temp set. Add sequence to it. Subscribe all slaves to it.
Merge with existing set."
scenario.

If any issues crop up, I'll report back.

John

Christopher Browne wrote:
> Andrew Sullivan wrote:
>
>
>
> Jan has fixed it in 1.1.5, but that's pending. Fixes to log shipping
> are mandating RC3, RSN...
>
> The trouble isn't a *massive* breakage, and, for this use case, probably
> won't be any breakage at all.
>
>
>
> This is a reasonable workaround, however, it wasn't too likely that John
> was going to run into the problem in the first place.
>
> The problem with "singleton sequence" comes up only if it turns out that
> the node that is subscribing to this set isn't drawing ANY tables from
> its data source.
>
> Thus, if you have the two sets:
>
> Set 1: tables = { tbl1, tbl2, tbl3 }, sequences = { s1, s2, s3 }
> Set 2: tables = NULL, sequences = { s4 }
>
> it actually gets treated, by Slony-I, as the *combination* of these,
> namely...
>
> Tables replicated = { tbl1, tbl2, tbl3 }, sequnces = { s1, s2, s3, s4 }
>
> And that won't cause a problem.
>
> The problem would come in if you did a MOVE SET on set 1, or somehow,
> otherwise, dropped set #1 out of the picture.
>
> The condition where replication starts breaking is where you have...
>
> Tables replicated = NULL
>
> It's a pretty abstruse case, which is why it:
> 1. wasn't noticed as a problem early on
> 2. wasn't noticed as a possible error condition until about a year
> after Slony-I had gotten first deployed
> 3. wasn't explained in total detail. Having a set of "just sequences"
> often *won't* break; explaining when it will break requires getting a
> bit wordy...
>
> I was discussing it with Brad, today; if memory serves, he's the one
> that first encountered the problem. The error condition is obscure
> enough that he got a bit confused as to when it does (and doesn't!) break.
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

Andrew Sullivan

2006-01-13, 8:24 pm

On Sat, Jan 07, 2006 at 02:20:53PM +0000, John Sidney-Woollett wrote:
> I need to add a single sequence to an existing replicated set (slony
> 1.1.0 and Postgres 7.4.6)
>
> I remember that it is not advised to have a set with a single sequence
> (although I can't remember why).

Because it won't work. It's some sort of breakage. I don't recall
whether it's been fixed -- IIRC it should be fixable, but I suspect
nobody has cared enough to do it.

> 1) Create temp set. Add sequence to it. Subscribe all slaves to it.
> Merge with existing set.

This will work, except that you should create a dummy table as well.
Then merge the sets, and then use SET DROP TABLE to drop the dummy
table. It's a kludgey workaround, but it does work around without
having to rebuild the whole replica from nothing.

A

--
Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie

Christopher Browne

2006-01-13, 8:24 pm

Andrew Sullivan wrote:

>On Sat, Jan 07, 2006 at 02:20:53PM +0000, John Sidney-Woollett wrote:
>
>
>
>Because it won't work. It's some sort of breakage. I don't recall
>whether it's been fixed -- IIRC it should be fixable, but I suspect
>nobody has cared enough to do it.
>
>
Jan has fixed it in 1.1.5, but that's pending. Fixes to log shipping
are mandating RC3, RSN...

The trouble isn't a *massive* breakage, and, for this use case, probably
won't be any breakage at all.

>
>This will work, except that you should create a dummy table as well.
>Then merge the sets, and then use SET DROP TABLE to drop the dummy
>table. It's a kludgey workaround, but it does work around without
>having to rebuild the whole replica from nothing.
>
>
This is a reasonable workaround, however, it wasn't too likely that John
was going to run into the problem in the first place.

The problem with "singleton sequence" comes up only if it turns out that
the node that is subscribing to this set isn't drawing ANY tables from
its data source.

Thus, if you have the two sets:

Set 1: tables = { tbl1, tbl2, tbl3 }, sequences = { s1, s2, s3 }
Set 2: tables = NULL, sequences = { s4 }

it actually gets treated, by Slony-I, as the *combination* of these,
namely...

Tables replicated = { tbl1, tbl2, tbl3 }, sequnces = { s1, s2, s3, s4 }

And that won't cause a problem.

The problem would come in if you did a MOVE SET on set 1, or somehow,
otherwise, dropped set #1 out of the picture.

The condition where replication starts breaking is where you have...

Tables replicated = NULL

It's a pretty abstruse case, which is why it:
1. wasn't noticed as a problem early on
2. wasn't noticed as a possible error condition until about a year
after Slony-I had gotten first deployed
3. wasn't explained in total detail. Having a set of "just sequences"
often *won't* break; explaining when it will break requires getting a
bit wordy...

I was discussing it with Brad, today; if memory serves, he's the one
that first encountered the problem. The error condition is obscure
enough that he got a bit confused as to when it does (and doesn't!) break.

Sponsored Links