Miguel wrote:
> Hi, im facing  an error (stupid me !!!), im replicating between two 
> servers, the master server is gentoo and slave is freebsd, in gentoo the 
> owner of the database is postgres and in freebsd is pgsql, when i 
> created the schema in freebsd a modified every line
>
> ALTER BLABLABLA OWNER TO postgres;
>
> to
>
> ALTER BLABLABLA OWNER TO pgsql;
>
> and edited the conn line in slon_tools.conf according to every node's 
> database owner, the replication started without any problem, all was 
> working fine until today, i wanted to modify a function, so i created an 
> exec script, the problem was that i copied the original function from a 
> pgadmin window, edited it and pasted it in a vi screen, **BUT** i forgot 
> to edit the owner line, so now slon is dying in the slave node, refusing 
> to apply the schema change beacuse in the slave node there isnt a 
> postgres role
>
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1: queue event 1,20534 
> SYNC
> 2006-04-07 10:49:47 CST ERROR  remoteWorkerThread_1: "begin transaction; 
> set transaction isolation level serializable; lock table "_shiva".sl_
> config_lock; select "_shiva".ddlScript_int(1, 'DROP FUNCTION 
> formatted_time(float8);
>
> CREATE OR REPLACE FUNCTION formatted_time(integer)
>   RETURNS "varchar" AS
>   $BODY$ select ($1 * ''1 second''::interval)::varchar(15)$BODY$
>     LANGUAGE ''sql'' VOLATILE;
>     ALTER FUNCTION formatted_time(integer) OWNER TO postgres;
>
> ', -1); notify "_shiva_Event"; notify "_shiva_Confirm"; insert into 
> "_shiva".sl_event     (ev_origin, ev_seqno, ev_timestamp,      ev_minxid,
> ev_maxxid, ev_xip, ev_type , ev_data1, ev_data2, ev_data3    ) values 
> ('1', '17993', '2006-04-06 17:32:34.498772', '67820709', '67820710', '',
>  'DDL_SCRIPT', '1', 'DROP FUNCTION formatted_time(float8);
>
> CREATE OR REPLACE FUNCTION formatted_time(integer)
>   RETURNS "varchar" AS
>   $BODY$ select ($1 * ''1 second''::interval)::varchar(15)$BODY$
>     LANGUAGE ''sql'' VOLATILE;
>     ALTER FUNCTION formatted_time(integer) OWNER TO postgres;
>
> ', '-1'); insert into "_shiva".sl_confirm       (con_origin, 
> con_received, con_seqno, con_timestamp)    values (1, 2, '17993', 
> now()); commit
> transaction;" PGRES_FATAL_ERROR ERROR:  role "postgres" does not exist
> CONTEXT:  SQL statement "DROP FUNCTION formatted_time(float8);
>
> CREATE OR REPLACE FUNCTION formatted_time(integer)
>   RETURNS "varchar" AS
>   $BODY$ select ($1 * '1 second'::interval)::varchar(15)$BODY$
>     LANGUAGE 'sql' VOLATILE;
>     ALTER FUNCTION formatted_time(integer) OWNER TO postgres;
>
> "
> PL/pgSQL function "ddlscript_int" line 53 at execute statement
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1: queue event 1,20535 
> SYNC
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1: queue event 1,20536 
> SYNC
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1: queue event 1,20537 
> SYNC
> 2006-04-07 10:49:47 CST DEBUG1 slon: shutdown requested
> 2006-04-07 10:49:47 CST DEBUG2 slon: notify worker process to shutdown
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1: queue event 1,20538 
> SYNC
> 2006-04-07 10:49:47 CST DEBUG2 slon: wait for worker process to shutdown
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1: queue event 1,20539 
> SYNC
> 2006-04-07 10:49:47 CST DEBUG2 remoteListenThread_1: queue event 1,20540 
> SYNC
> 2006-04-07 10:49:47 CST DEBUG1 main: scheduler mainloop returned
> 2006-04-07 10:49:47 CST DEBUG2 remoteWorker_event: ignore new events due 
> to shutdown
> 2006-04-07 10:49:47 CST DEBUG2 main: wait for remote threads
>  
> how can i delete this replication event?, should this work if i create  
> a postgres role in  slave node? 
>   
There are multiple possible answers to this, of varying levels of safety.

1.  You could always create a 'postgres' user on the system that lacks it.

After doing that, the event will succeed, and all will be well.

You might very well want to have the 'postgres' user around anyways;
pg_dump from the Linux box will have things belonging to that user;
restoring dumps on FreeBSD would break, too.

I rather like this answer.

2.  You could delete the event entry on the origin node.

That means the event won't have propagated; you might have to submit it
again, with a more suitable user role...

Definitely *not* as safe...

It's definitely not a good thing to define things that you want
replicated that belong to users that don't exist on all nodes...