[mapserver-dev] Tested DDL on master, pushed with slonik_execute_script, failed on slaves

[Karl Lehenbauer]

So we follow the best practice of testing DDL by wrapping it in a  
BEGIN...ROLLBACK and making sure it "works" on the master before  
pushing it out using slonik_execute_script.

Yesterday we got burned.  We had some DDL that included a DROP  
FUNCTION.  Problem was the function existed on the master but not on  
the slaves.  (We should have used DROP FUNCTION IF EXISTS, but we  
didn't.)

So we got into the thing where the slon daemon on the slaves would die  
and then get respawned and then try to sync a ton of stuff and die  
whenever it hit the PGRES_FATAL_ERROR with the messed up DDL.  Finally  
we created the missing function on the slaves and then the DDL could  
complete and after a few minutes, everything was back to normal.

I propose that a better best practice is to test the DDL on the master  
and every slave in a BEGIN...ROLLBACK to make sure the DDL will  
succeed everywhere.  We've built a program to do this, try_slony_ddl,  
but it's written in Tcl.  Perhaps someone with better Perl skills than  
I could make a slonik_test_script that does the same thing but uses  
the slon_tools.conf.  (My stuff currently gets the db connect strings  
out of sl_path).

I share this in the hope that it might be useful to someone.  We feel  
like after something's been approved by try_slony_ddl, it's a pretty  
dead certainty that it's going to work when pushed out with  
slonik_execute_script.

Karl