Thread: psql problem describing tables
I am running postgres 6.5.3 on an SGI. I haven't done much except create a few tables and indexes. When I enter psql and type '\d', all my tables and indexes are listed. When I type '\d tablename' however, I get the following message: \d Images ERROR: typeidTypeRelid: Invalid type - oid = 0 I can select from the table, but there's no data in it. I have the same problem with all my tables, and I can't describe indexes either. Any suggestions? Thanks, Sarah Officer officers@aries.tucson.saic.com
Let me clarify. The reason there is not data in my table is because I haven't inserted any yet. I inserted a row of data. It gets selected correctly, but I still can't describe the table. So the problem doesn't seem to be related to having an empty table. Sarah Sarah Officer wrote: > > I am running postgres 6.5.3 on an SGI. I haven't done much except > create a few tables and indexes. When I enter psql and type '\d', > all my tables and indexes are listed. When I type '\d tablename' > however, I get the following message: > > \d Images > ERROR: typeidTypeRelid: Invalid type - oid = 0 > > I can select from the table, but there's no data in it. I have the > same problem with all my tables, and I can't describe indexes > either. Any suggestions? > > Thanks, > > Sarah Officer > officers@aries.tucson.saic.com > > ************
I am trying to create a simple trigger function. With some help
from the mailing list, I managed to create a trigger and functions.
Unfortunately I get an error message when I delete from the table
which has the trigger. Can anyone help me spot the error? Here's
what I have done:
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/achilles_usr12/mars/swl/IRIX6.5/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
create table Images (
id varchar(100) PRIMARY KEY,
title varchar(25) NOT NULL,
filepath varchar(256) NOT NULL UNIQUE,
status_code varchar(5) NOT NULL
) ;
create table Istatus (
status_code varchar(5) PRIMARY KEY,
status_desc varchar(100) NOT NULL
);
CREATE FUNCTION remove_status_func()
RETURNS opaque AS '
delete from Images
where Images.status_code = old.status_code ;
select 1 as val;
' LANGUAGE 'plpgsql' ;
CREATE TRIGGER Istatus_delete_trigger
AFTER DELETE ON Istatus
FOR EACH ROW
EXECUTE PROCEDURE remove_status_func() ;
Insert into Istatus(status_code, status_desc)
values('A1', 'A1 Desc');
Insert into Istatus(status_code, status_desc)
values('A2', 'A2 Desc');
Insert into Istatus(status_code, status_desc)
values('A3', 'A3 Desc');
Insert into Images(id, title, filepath, status_code)
values ('ID1', 'First Image', '/usr/local/foo.gif', 'A1');
Insert into Images(id, title, filepath, status_code)
values ('ID2', 'Another Image', '/usr/local/bar.gif', 'A2');
> select * from istatus;
status_code|status_desc
-----------+-----------
A1 |A1 Desc
A2 |A2 Desc
A3 |A3 Desc
(3 rows)
> select * from images;
id |title |filepath |status_code
---+-------------+------------------+-----------
ID1|First Image |/usr/local/foo.gif|A1
ID2|Another Image|/usr/local/bar.gif|A2
(2 rows)
> delete from istatus where status_code = 'A1';
ERROR: fmgr_info: function 18848: cache lookup failed
What is the problem with the cache lookup? Any suggestions would be
appreciated.
Sarah Officer
officers@aries.tucson.saic.com
Sarah Officer wrote: > > > delete from istatus where status_code = 'A1'; > ERROR: fmgr_info: function 18848: cache lookup failed > > What is the problem with the cache lookup? Any suggestions would be > appreciated. I seem to recall that kind of message often shows up when you have dropped and recreated the function, thinking that the trigger would be able to find your new function, when in fact it cannot. If you drop/recreate the function, you must then also drop/recreate the trigger. Bummer, but I believe that is going to be fixed sometime soon IIRC. Cheers, Ed Loehr
Thanks to Ed Loehr and others on the group, I finally was able to create triggers in my database. This is a summary of what I learned in the process. For the most part, I didn't find this in the documentation. If anything here is incorrect, please let me know. If not, can it be put in documentation somewhere? or in the FAQ? - The actual working code for a trigger must be put into a function which is called by the trigger. [This *is* in the docs] - If the trigger function needs access to rows which are affected by the insert/update/delete, the trigger function must use plpgsql as a language. A sql function cannot access the special 'old' and 'new' rows. - Before creating a function in plpgsql, a handler and trusted language must be created. Example syntax: CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/install/lib/path/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; - The return type for a plpgsql function must be opaque. - A value must be returned if a return type is specified. The old & new records are available as return values from the plpgsql function. - The body of a plpgsql function looks like sql except for reference to old and new. The SQL part of the function must be enclosed with 'begin' and 'end;' or there will be a compiler error at run time. - Example triggers and plpgsql functions can be found in the postgres subdirectory: src/test/regress/sql. - If a trigger function is dropped and recreated, the corresponding trigger must also be dropped and recreated. Otherwise postgres 6.5.3 will give a runtime error that the cache lookup failed.
Sarah Officer wrote: > > - A value must be returned if a return type is specified. The old & > new records are available as return values from the plpgsql > function. Unfortunately, OLD and NEW are only available in the function that's directly called by the trigger, not subsequent functions in the call chain, IIRC. > - The body of a plpgsql function looks like sql except for reference > to old and new. The SQL part of the function must be enclosed with > 'begin' and 'end;' or there will be a compiler error at run time. What goes between 'begin' and 'end' are PL/pgSQL statements, of which SQL is almost a subset. PL/pgSQL also has a number of plain vanilla programming language constructs (if-then, loops, etc.). > - If a trigger function is dropped and recreated, the corresponding > trigger must also be dropped and recreated. Otherwise postgres > 6.5.3 will give a runtime error that the cache lookup failed. More generally, any function that gets dropped/recreated requires all the functions/triggers above it in the call chain to be recreated, IIRC. Cheers, Ed Loehr