Thread: ERROR: fmgr_info: function 24809: cache lookup failed
Hi all. I feel awfully stupid.
Migrating my skillset from Oracle to Postgres, I am trying (in vain) to
create a trigger and a sequence to "auto increment" the primary key in a
table, so I won't have to worry about setting it; each time you INSERT
into the table, the primary key will be automagically assigned.
Problem is, it isn't working... I get "cache lookup failed" whenever I try
to INSERT on that table!
THE TABLE:
CREATE TABLE SENTENCES (
SENTENCEID NUMERIC,
CHAPTERID NUMERIC,
SOFFSET INTEGER,
PRIMARY KEY (SENTENCEID)
);
THE SEQUENCE:
CREATE SEQUENCE sentenceid_seq;
THE FUNCTION:
CREATE FUNCTION sentenceid_fcn()
RETURNS OPAQUE AS '
BEGIN
SELECT nextval(sentenceid_seq)
INTO :new.sentenceid
FROM dual;
END; ' language plpgsql;
THE TRIGGER:
CREATE TRIGGER sentenceid_trig
BEFORE INSERT
ON sentences
FOR EACH ROW
EXECUTE PROCEDURE sentenceid_fcn();
END;
And when I try to INSERT into SENTENCES, without my SENTENCEID, hoping
that the function/trigger/sequence will kick in and assign it for me...
architxts=# INSERT INTO SENTENCES (CHAPTERID, SOFFSET) VALUES (123, 147);
ERROR: fmgr_info: function 24809: cache lookup failed
I'm certain I'm doing SOMETHING terribly wrong. Could someone help?
PLEEEEASE? Why am I getting that error?
Many thanks in advance!
--
J e s s i c a L e a h B l a n k
On Tuesday 17 December 2002 06:33 pm, you wrote: > Hi all. I feel awfully stupid. > > Migrating my skillset from Oracle to Postgres, I am trying (in vain) to > create a trigger and a sequence to "auto increment" the primary key in a > table, so I won't have to worry about setting it; each time you INSERT > into the table, the primary key will be automagically assigned. Umm.. What is wrong with type serial. It does the same thing IIRC.. Shridhar
On Tue, 2002-12-17 at 13:03, Jessica Blank wrote: > Hi all. I feel awfully stupid. > > Migrating my skillset from Oracle to Postgres, I am trying (in vain) to > create a trigger and a sequence to "auto increment" the primary key in a > table, so I won't have to worry about setting it; each time you INSERT > into the table, the primary key will be automagically assigned. As Jessica said, use the SERIAL type, which will automatically increment the sequence and use its value unless an explicit value is given for the column. > And when I try to INSERT into SENTENCES, without my SENTENCEID, hoping > that the function/trigger/sequence will kick in and assign it for me... > > architxts=# INSERT INTO SENTENCES (CHAPTERID, SOFFSET) VALUES (123, 147); > ERROR: fmgr_info: function 24809: cache lookup failed This probably means that you have dropped and recreated the function after you created the trigger. The trigger is still looking for the old function, but it has been deleted. To avoid such problems, use CREATE OR REPLACE FUNCTION ... If you haven't got that syntax available, you need to update PostgreSQL. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "For I say, through the grace given unto me, to every man that is among you: Do not think of yourself more highly than you ought, but rather think of yourself with sober judgement, in accordance with the measure of faith God has given you." Romans 12:3
You can do without function/trigger, try this :
Create a sequence first :
CREATE SEQUENCE sentenceid_seq;
Create the table:
CREATE TABLE SENTENCES (
SENTENCEID integer DEFAULT NEXTVAL('sentenceid_seq'::text) NOT NULL,
CHAPTERID NUMERIC,
SOFFSET INTEGER,
PRIMARY KEY (SENTENCEID)
);
Besides, I am not sure if DUAL exists in PostgreSQL.
Tariq Muhammad
Liberty RMS
tariq@libertyrms.info
v:416-646-3304 x 111
c:416-993-1859
p:416-381-1457
On Tue, 17 Dec 2002, Jessica Blank wrote:
> Hi all. I feel awfully stupid.
>
> Migrating my skillset from Oracle to Postgres, I am trying (in vain) to
> create a trigger and a sequence to "auto increment" the primary key in a
> table, so I won't have to worry about setting it; each time you INSERT
> into the table, the primary key will be automagically assigned.
>
> Problem is, it isn't working... I get "cache lookup failed" whenever I try
> to INSERT on that table!
>
> THE TABLE:
> CREATE TABLE SENTENCES (
> SENTENCEID NUMERIC,
> CHAPTERID NUMERIC,
> SOFFSET INTEGER,
> PRIMARY KEY (SENTENCEID)
> );
>
> THE SEQUENCE:
> CREATE SEQUENCE sentenceid_seq;
>
> THE FUNCTION:
> CREATE FUNCTION sentenceid_fcn()
> RETURNS OPAQUE AS '
> BEGIN
> SELECT nextval(sentenceid_seq)
> INTO :new.sentenceid
> FROM dual;
> END; ' language plpgsql;
>
> THE TRIGGER:
> CREATE TRIGGER sentenceid_trig
> BEFORE INSERT
> ON sentences
> FOR EACH ROW
> EXECUTE PROCEDURE sentenceid_fcn();
> END;
>
> And when I try to INSERT into SENTENCES, without my SENTENCEID, hoping
> that the function/trigger/sequence will kick in and assign it for me...
>
> architxts=# INSERT INTO SENTENCES (CHAPTERID, SOFFSET) VALUES (123, 147);
> ERROR: fmgr_info: function 24809: cache lookup failed
>
> I'm certain I'm doing SOMETHING terribly wrong. Could someone help?
> PLEEEEASE? Why am I getting that error?
>
> Many thanks in advance!
>
>
>
> --
> J e s s i c a L e a h B l a n k
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
Many thanks to Oliver and the several others who provided similar information. :) I will implement that, yes. Still, it would be nice to know how you CAN make triggers/functions/whatnot do what I wanted.. if only for education's sake. :) On 17 Dec 2002, Oliver Elphick wrote: > On Tue, 2002-12-17 at 13:03, Jessica Blank wrote: > > Hi all. I feel awfully stupid. > > > > Migrating my skillset from Oracle to Postgres, I am trying (in vain) to > > create a trigger and a sequence to "auto increment" the primary key in a > > table, so I won't have to worry about setting it; each time you INSERT > > into the table, the primary key will be automagically assigned. > > As Jessica said, use the SERIAL type, which will automatically increment > the sequence and use its value unless an explicit value is given for the > column. > > > And when I try to INSERT into SENTENCES, without my SENTENCEID, hoping > > that the function/trigger/sequence will kick in and assign it for me... > > > > architxts=# INSERT INTO SENTENCES (CHAPTERID, SOFFSET) VALUES (123, 147); > > ERROR: fmgr_info: function 24809: cache lookup failed > > This probably means that you have dropped and recreated the function > after you created the trigger. The trigger is still looking for the old > function, but it has been deleted. To avoid such problems, use CREATE > OR REPLACE FUNCTION ... If you haven't got that syntax available, you > need to update PostgreSQL. > > -- J e s s i c a L e a h B l a n k