L.S.
I haven't been able to reproduce this at will, the problem probably depends on
exactly how the trigger being replaced is used at that exact moment, but it
may still be enough info.....
db=# select version();
version
---------------------------------------------------------------------
PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
I updated a 200 line pl/pgsql function which was used as a before trigger
through a copy/paste of the code into a psql terminal and got the proper
response 'CREATE FUNCTION'. The code was using the syntax 'create or replace
function....<etc>'. At the moment of the update the database was in use and
some users may very well have been actually 'running' the particular trigger.
I then noticed that all actions on this particular table seem to be ignored by
the database, even a simple 'delete from <table> where id = ...' returned a
'DELETE 0', updating resulted in 'UPDATE 0', etc.
Issuing '\df+ <name>()' did show the new function content to be present, so I
then suspected that the old function had been removed alright, but that it
somehow didn't get replaced by the new function, leaving a kind of
'unassigned before trigger' that wasn't returning rows, thus effectively
'nullifying' any action on the table.
Since this was a production database, I needed to correct the situation asap
and simply repeating the copy/paste action did exactly that.
It would be great if this gave someone enough clues to pinpoint the problem,
but if not, what kind of action could I take (queries on system tables, etc.)
in order to provide more info on this the next time it happens?
--
Best,
Frank.
de elektronische=20
verzending van dit bericht kunnen er geen rechten worden ontleend aan de=20
informatie.
This email and any files transmitted with it are confidential and intended=
=20
solely for the use of the individual or entity to whom they are addressed.
Copyright (c) 2005 Foxi C.i.T. BV All world wide rights reserved.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
