John Sidney-Woollett wrote:
> Thanks for the info, Richard.
> 
> I didn't think that it was a slony issue per se, but that a note should 
> be added to the slony docs warning to recycle connections after making 
> substantive changes to the schema.
> 
> You're right, we use both (java) prepared statements and pl/pgsql 
> functions.
> 
> The data loss aspect is not so clear cut (for us). We definitely got 
> records that failed to insert (missing sequence numbers in tables) while 
> others did insert correctly into the same tables (using the same 
> pl/pgsql functions). So we can't figure out what the pattern is.
> 
> Although we do have our connection pool recycle idle connections - it 
> could be that things worked when a new recycled connection was used by 
> the web app, and failed when one of the "old/original" connections was 
> used. This is probably what masked the errors for us...

That's almost certainly the case. I'm luck in that I've always been able 
to have some downtime for schema changes.

> It would be great if postgres could "recompile" pl/pgsql functions 
> whenever it found a missing object referenced within the function - 
> chances are that it would compile cleanly (in this case) and then could 
> be executed without error.
> 
> something along the lines of
> 
> execute function
> OK -> return result
> ERROR - OID -> recompile function, and re-execute
>   OK -> return result
>   ERROR - OID -> report error
> 
> This would help get rid of the temp table in functions work around 
> having to use an execute statement.

Hmm - not sure you could do this without a savepoint to catch the error. 
However, it might be possible to add track dependencies with the 
function (as with views). Then you'd have to issue a CASCADE to alter 
the table.

--
   Richard Huxton
   Archonet Ltd