Thread: Error in trigger after upgrading to 8.0.1?
I have just upgraded to 8.0.1 from 7.4.6 and now my triggers doesn't work. When I insert a row in a table that has an insert trigger, I get the following error msg: ERROR: INSERT is not allowed in a non-volatile function What does this mean? Regards, BTJ -- ----------------------------------------------------------------------------------------------- Bjørn T Johansen btj@havleik.no ----------------------------------------------------------------------------------------------- Someone wrote: "I understand that if you play a Windows CD backwards you hear strange Satanic messages" To which someone replied: "It's even worse than that; play it forwards and it installs Windows" -----------------------------------------------------------------------------------------------
Bjørn T Johansen wrote:
> I have just upgraded to 8.0.1 from 7.4.6 and now my triggers doesn't
> work. When I insert a row in a table that has an insert trigger, I get
> the following error msg:
>
> ERROR: INSERT is not allowed in a non-volatile function
>
>
> What does this mean?
That is highly documented in the PGSQL 8.0 documentation. That exactly
means that a function that is not VOLATILE (such as STABLE, IMMUTABLE)
can't make any changes in tables (INSERT, UPDATE, DELETE). For such
operations, it MUST be VOLATILE. So, re-create (or REPLACE) your
functions, that do make any changes in tables with VOLATILE option set up.
Best regards,
Andrey V. Semyonov
Bjørn T Johansen wrote: > I have just upgraded to 8.0.1 from 7.4.6 and now my triggers doesn't > work. When I insert a row in a table that has an insert trigger, I get > the following error msg: > > ERROR: INSERT is not allowed in a non-volatile function > > > What does this mean? > > > Regards, > > BTJ > It would appear as though the function in question was not created as a VOLATILE function (i.e. it was created with the STABLE or IMMUTABLE attribute). Can you try and: CREATE OR REPLACE FUNCTION function_name ...... AS $$ <your function def> $$ LANGUAGE plpgsql VOLATILE; Sven Willenberger
=?ISO-8859-1?Q?Bj=F8rn_T_Johansen?= <btj@havleik.no> writes:
> I have just upgraded to 8.0.1 from 7.4.6 and now my triggers doesn't work. When I insert a
> row in a table that has an insert trigger, I get the following error msg:
> ERROR: INSERT is not allowed in a non-volatile function
> What does this mean?
Just what it says. What are you doing declaring that trigger function
as immutable (or stable), when it has side effects?
regards, tom lane
> Bjørn T Johansen wrote: > >> I have just upgraded to 8.0.1 from 7.4.6 and now my triggers doesn't >> work. When I insert a row in a table that has an insert trigger, I get >> the following error msg: >> >> ERROR: INSERT is not allowed in a non-volatile function >> >> >> What does this mean? >> >> >> Regards, >> >> BTJ >> > > It would appear as though the function in question was not created as a > VOLATILE function (i.e. it was created with the STABLE or IMMUTABLE > attribute). > > Can you try and: > CREATE OR REPLACE FUNCTION function_name ...... AS $$ > <your function def> > $$ LANGUAGE plpgsql VOLATILE; > > Sven Willenberger > That did the trick, thx... BTJ