Postgres Pro
Downloads
Home   >   mailing lists

Thread: Trigger and Function Question

Trigger and Function Question

From
"Jeff Umstead"
Date:
I'm new to PostgreSQL and while it's been fairly straight forward
setting up, creating databases, tables, etc, I'm having trouble
understanding the correct creation of triggers and functions.

I have several tables with a timestamp field that I would like to use
a trigger to update whenever a row is updated.

I've created a function as follows

OS: Caldera OpenLinux eServer 2.3
PostgreSQL Version: 7.0.3

Create Function f_set_updated()
returns opaque
as 'Begin
    new.updated = current_timestamp;
    return new;
End;'
language 'plpgsql';

which return the Create message meaning it was accepted and created.

Next I created a trigger as follows

create trigger trig_t_user_updated
after update
on t_user
for each row
execute procedure f_set_updated();

This returned Create.

When I try to update table t_user:
    update t_user set midint='X' where user_id = 'mmortar';
I get the following error:

010418.22:47:10.789 [29762] ERROR:  fmgr_info: function 25479: cache
lookup failed
ERROR:  fmgr_info: function 25479: cache lookup failed

What am I doing wrong??
What are some good reference books, sites to use as I'm learning?

Thank you

Jeff Umstead
jumstead@timlsoftware.com

Re: Trigger and Function Question

From
Tom Lane
Date:
"Jeff Umstead" <jumstead@timlsoftware.com> writes:
> I get the following error:

> 010418.22:47:10.789 [29762] ERROR:  fmgr_info: function 25479: cache
> lookup failed

Looks like you deleted and recreated the function, without also deleting
and recreating the trigger that refers to it.  Now the trigger is
referring to a no-longer-existent function OID ... remake the trigger
and try again.

            regards, tom lane

Re: Trigger and Function Question

From
Shay Moreno
Date:
> Looks like you deleted and recreated the function, without also deleting
> and recreating the trigger that refers to it.  Now the trigger is
> referring to a no-longer-existent function OID ... remake the trigger
> and try again.

    Excuse other preposterous question of mine, but the reliance of OID
don't go against the Information Principle for relational databases?  Even
if PosgreSQL isn't strictly relational, and if triggers and functions aren't
really in the scope of the relational model, what is the rationale for such
exposition of a fairly internal construct as an OID?  Perhaps the use of the
name of the function in the internal reference from the trigger would be too
expensive performace-wise, but then perhaps the function could be at least
marked invalid, like the ORA-04063 error in
http://technet.oracle.com./doc/server.805/a58312/newcha25.htm#23933, in
order to make troubleshooting simpler.

    Just my 2c.



--
  _
 / \       Leandro Guimarães Faria Corcete Dutra        +55 (11) 3040 8913
 \ /       Amdocs Brasil Ltda at Tele Danmark                +45 3387 5214
  X   http://terravista.pt./Enseada/1989/     mailto:leandrod@amdocs.com
 / \     Campanha fita ASCII                        mailto:moreno@tdk.dk