Norman Yamada wrote:
> Aha -- that's the answer.
>
Knowing why it happens is at least half the battle!  That means you know
what to fight with, at least.
>> 2.  The question becomes:  How did an extra trigger get into place?
>
>
> We're using other schemas on our staging and testing servers. Our
> database has a price table and an updates table in its public schema,
> which are both slonied to the slave nodes. The public.price table has
> triggers to track changes on this table and write the changes to the
> public.updates table.
>
> When we want to test things in our database, on the staging and
> testing servers, we create an independent price table + updates table
> in another schema and test our stuff there using the same trigger
> mechanism, but on test data -- so the triggers have the same names,
> but are attached to a table in a different schema.
>
> I guess the work around here would be to drop the triggers with the
> same names in the other schemas before applying DDL changes. STORE
> TRIGGER doesn't work here, because the trigger isn't on the table
> being slonied --
Yeah, STORE TRIGGER isn't quite the right answer...  Although maybe it
could be made into the right answer, ultimately...
> Is there a way that slony can recognize that that there can be two
> triggers with the same name, one on a slonied table, one on a
> non-slonied table, in different schemas? I realize this is an edge
> case, but it gives us mightly useful functionality that I'm loath to
> lose.
Kick me if I'm wrong, but I don't think that's quite at the root of the
problem...

The name of the trigger wasn't the whole problem; the unique index was
on (reloid, trigger name).

There shouldn't be any conflict if you have a trigger on a table that
isn't participating in replication at all.

I'll go out on a limb and suggest that something more complex happened...

At Time T1, I expect you added triggers to those tables on the subscriber.

At Time T2 > T1, you ran EXECUTE SCRIPT, which, after running the
script, hid them.

Someone noticed the triggers were gone.

Time T3 > T2, triggers were added again.

Subsequently, EXECUTE SCRIPT would lead to the breakage observed.

It seems to me there's value to having some more functionality in STORE
TRIGGER, and that this is a feature worth discussing at the PG
Anniversary conference next week.

Could you perhaps put a feature request in on the web site?  If we start
by trying to describe how to configure what's needed, that's something
for people to fire arrows at or perhaps to make better still :-).

I can't say this is forcibly the next feature we'd want to add, but it's
worth describing what is needed, so it can get more eyes on it for good
or ill...