Error in trigger after upgrading to 8.0.1?

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