select fails inside function, but works otherwise

From: Daniel Wickstrom <danw(at)rtp(dot)ericsson(dot)se>
To: pgsql-general(at)postgresql(dot)org
Subject: select fails inside function, but works otherwise
Date: 2001-03-19 18:04:11
Message-ID: 15030.18971.505148.101469@gargle.gargle.HOWL
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've encountered a strange problem with some code that I'm porting
from oracle. When I do a select inside of a function it returns
nulls, but if I do the same select from psql it works fine. Here is
the test case that reproduces the problem:

drop table acs_objects;
create table acs_objects (
object_id integer not null
constraint acs_objects_pk primary key,
context_id integer constraint acs_objects_context_id_fk
references acs_objects(object_id),
security_inherit_p boolean default 't' not null
);

insert into acs_objects values (0,null,'t');
insert into acs_objects values (5,null,'t');
insert into acs_objects values (44,5,'t');

drop function acs_object__check_path (integer,integer);

create function acs_object__check_path (integer,integer)
returns boolean as '
declare
check_path__object_id alias for $1;
check_path__ancestor_id alias for $2;
context_id acs_objects.context_id%TYPE;
security_inherit_p acs_objects.security_inherit_p%TYPE;
begin
raise notice ''check path: % %'', check_path__object_id,
check_path__ancestor_id;
if check_path__object_id = check_path__ancestor_id then
return ''t'';
end if;

-- This select is failing. It does not return the correct value
-- when object_id = 44

select context_id, security_inherit_p
into context_id, security_inherit_p
from acs_objects
where object_id = check_path__object_id;

raise notice ''check path context : % %'', context_id,security_inherit_p;

if context_id is null or security_inherit_p = ''f'' then
context_id := 0;
end if;

raise notice ''check path recurse : % %'', context_id,check_path__ancestor_id;

return acs_object__check_path(context_id, check_path__ancestor_id);

end;' language 'plpgsql';

select acs_object__check_path(44,5);

Because of the select failure and a bug in this code, the function will
recurse until the back-end crashes. Of course if the select were
working properly that wouldn't happen. Here is the output from the
test run:

%psql -f tst.sql acspg
DROP
psql:tst.sql:10: NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'acs_objects_pk' for table 'acs_objects'
psql:tst.sql:10: NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
INSERT 125290 1
INSERT 125291 1
INSERT 125292 1
DROP
CREATE
psql:tst.sql:49: NOTICE: check path: 44 5
psql:tst.sql:49: NOTICE: check path context : <NULL> <NULL>
psql:tst.sql:49: NOTICE: check path recurse : 0 5
psql:tst.sql:49: NOTICE: check path: 0 5
psql:tst.sql:49: NOTICE: check path context : <NULL> <NULL>

... [snipped] ...

psql:tst.sql:49: NOTICE: check path: 0 5
psql:tst.sql:49: NOTICE: check path context : <NULL> <NULL>
psql:tst.sql:49: NOTICE: check path recurse : 0 5
psql:tst.sql:49: NOTICE: chec^CCancel request sent
psql:tst.sql:49: ERROR: Query was cancelled.

And here is the select from psql:

acspg=# select context_id, security_inherit_p from acs_objects where object_id = 44;
context_id | security_inherit_p
------------+--------------------
5 | t
(1 row)

acspg=#

Browse pgsql-general by date

  From Date Subject
Next Message Jan Ploski 2001-03-19 18:04:42 Re: concurrent updates problem
Previous Message Brent R. Matzelle 2001-03-19 17:37:47 Re: Cant connect if -B 1024 was set to postmaster