Slony1 PostgreSQL Replication

Author

DDL Script eror

Sven Willenberger

2005-12-29, 9:23 am

I am trying to create a trigger on a subscriber node's replicated table
using "EXECUTE SCRIPT" but it is bailing and causing the slon process on
the subscriber to shut down with the error:

"begin transaction; set transaction ..... PGRES_FATAL_ERROR ERROR:
Slony-I: Table with id 20 not found".

Now, table id 20 is not the table I wanted to affect (when I look up the
table name in sl_table it has an id of 13) and even so, there actually
is a different table with id 20.

For now, how can I remove the DDL event from the sync queue so that at
least I can get the slon process running on the subscriber node again?
Then, how can I go about figuring out how slon has the completely wrong
table information ?

Sven

Brad Nicholson

2006-01-03, 8:25 pm

Sven Willenberger wrote:

>I am trying to create a trigger on a subscriber node's replicated table
>using "EXECUTE SCRIPT" but it is bailing and causing the slon process on
>the subscriber to shut down with the error:
>
>"begin transaction; set transaction ..... PGRES_FATAL_ERROR ERROR:
>Slony-I: Table with id 20 not found".
>
>Now, table id 20 is not the table I wanted to affect (when I look up the
>table name in sl_table it has an id of 13) and even so, there actually
>is a different table with id 20.
>
>
>

DDL is run on a set, not just a table. As such, it affects the entire
set, not just the table(s) that you are running changes against. What
DDL does is removes the slony triggers from all tables in the set,
applies the changes, and then adds the slony triggers back. Have you by
any chance dropped the table associated with table id 20?

>For now, how can I remove the DDL event from the sync queue so that at
>least I can get the slon process running on the subscriber node again?
>Then, how can I go about figuring out how slon has the completely wrong
>table information ?
>
>
>

I think that you are going to have to find the sl_log_1 entry for the
DDL statement and remove it, but don't take my word for that. If you
get it wrong, you'll be in trouble. I'll defer to someone else on this
point.

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.

Sven Willenberger

2006-01-03, 8:25 pm

Brad Nicholson presumably uttered the following on 01/03/06 15:11:
> Sven Willenberger wrote:
>
>
> DDL is run on a set, not just a table. As such, it affects the entire
> set, not just the table(s) that you are running changes against. What
> DDL does is removes the slony triggers from all tables in the set,
> applies the changes, and then adds the slony triggers back. Have you by
> any chance dropped the table associated with table id 20?
>
>
> I think that you are going to have to find the sl_log_1 entry for the
> DDL statement and remove it, but don't take my word for that. If you
> get it wrong, you'll be in trouble. I'll defer to someone else on this
> point.
>

I found the issue which was namely that the primary key had been removed
from the table with id 20. Since the mechanism that slony uses to
"disable" triggers on subscriber nodes is to tie the trigger to the
primary key instead of the table, the error now makes sense: it could
not find the primary key to attach the trigger to.

Removing the entry from sl_event (I believe) did allow replication to
move on and removed the ddl attempt from happening.

Thanks,

Sven

Jan Wieck

2006-01-09, 8:26 pm

On 1/3/2006 3:11 PM, Brad Nicholson wrote:
> Sven Willenberger wrote:
>
>
> DDL is run on a set, not just a table. As such, it affects the entire
> set, not just the table(s) that you are running changes against. What
> DDL does is removes the slony triggers from all tables in the set,
> applies the changes, and then adds the slony triggers back. Have you by
> any chance dropped the table associated with table id 20?

There is a change in 1.1.5 (which still needs to be applied to 1.2 as
well) that will do that for ALL tables, not only those in one set. The
set specification will merely tell on which nodes (subscribers) the DDL
script will be executed. But the script is no longer restricted to work
on tables in that set alone.

Jan

>
>
> I think that you are going to have to find the sl_log_1 entry for the
> DDL statement and remove it, but don't take my word for that. If you
> get it wrong, you'll be in trouble. I'll defer to someone else on this
> point.
>

--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #

Sponsored Links