Thread: BUG #2277: Can't Open Relation when returning data from a stored procedure
BUG #2277: Can't Open Relation when returning data from a stored procedure
From
"Andrew Nykolyn"
Date:
The following bug has been logged online:
Bug reference: 2277
Logged by: Andrew Nykolyn
Email address: nykolyn@ngc.com
PostgreSQL version: 8.1.3
Operating system: OS Red Hat Enterprise Linux AS release 3 (Taroon Update
4) Kernel 2.4.21-27.0.4.EL.snaresmp on an i686
Description: Can't Open Relation when returning data from a stored
procedure
Details:
When running a stored procedure that loads data into a temporary table, the
following error occurs when returning the result set through a composite
data type.
Could not open relation with OID 443748
The OID number changes on each error invocation of the stored procedure.
This occurs only when a certain row of data is returned. When that row is
not part of the result set the stored procedure executes correctly. The
table contains many text fields. The row in question contains two text
fields with large amounts of data. When the data from those two text fields
are deleted, the procedure executes correctly. I have pared down the DDL,
stored procedure and data and still can reproduce the problem. Included in
this email is a self contained script containing: the pared down versions
of: the stored procedure; the DDL to create the table; insert statements to
load the data that contains the data that corrupts the procedure; and the
executions of the stored procedure to produce the error as well as
demonstration that deletion of the bad data executes the stored procedure
correctly. The script can be executed directly through psql to show the
problem.
The script will perform the following actions create the table create the
type create the stored procedure load the data, execute the stored procedure
without error and with the data in question execute the stored procedure to
get the error which includes the data in question remove the data in
question execute the stored procedure with the updated data and perform
correctly
I hope that there is enough information here for this issue to be resolved.
It is critical for the application that I am developing. This is my second
submittal of this problem because I don't know what the turnaround time is
for bug submittals and there are no attachments this time and the script is
now completely self contained. My appolgies if this is already being looked
at. Thank you very much for your help.
Andy Nykolyn
Northrop Grumman Corporation
-- create table
drop table CHANGEREQUESTS;
create table CHANGEREQUESTS (
CHANGEREQUESTID SERIAL not null,
CHANGEREQUESTTYPE TEXT null,
REASONFORCHANGE TEXT null,
constraint PK_CHANGEREQUESTS primary key (CHANGEREQUESTID) );
-- Composite Data Type
drop type getchangerequests_type cascade; create type getchangerequests_type
as (
changerequestid int,
changerequesttype text,
reasonforchange text
);
-- Stored procedure
create or replace function get_changerequests(text) returns setof
getchangerequests_type as '
declare
rs_type getchangerequests_type%rowtype;
v_changerequesttype alias for $1;
from1 text='''';
where1 text='''';
insert1 text='''';
begin
create temporary table t_resultset (
changerequestid int,
changerequesttype text,
reasonforchange text
);
insert1 = '' insert into t_resultset
select
changerequestid, changerequesttype, reasonforchange '';
from1 = '' from changerequests c '';
where1 = '' where changerequesttype = '' ||
quote_literal(v_changerequesttype);
execute insert1 || from1 || where1;
for rs_type in execute '' select * from t_resultset ''
loop
return next rs_type;
end loop;
drop table t_resultset;
return;
end' language 'plpgsql';
-- Load data
INSERT INTO changerequests VALUES (2, 'MCR', '# 1'); INSERT INTO
changerequests VALUES (4, 'SWCR', 'TEST'); INSERT INTO changerequests VALUES
(5, 'SWCR', 'zvzxcv'); INSERT INTO changerequests VALUES (1, 'MCR', 'First
MCR'); INSERT INTO changerequests VALUES (6, 'MCR', ' df'); INSERT INTO
changerequests VALUES (3, 'SWCR', '-- IDR#276, IDR#354, IDR#357 pragma
INITIALIZE_SCALARS; with RDR_ROTODOME_API; with ACT_TRACK_C_TYPES; with
MCC_EXCEPTIONS; with RDR_API_TYPES; use RDR_API_TYPES;
-- with RDR_IDD_TYPES;
with RDR_TYPES;
with RADAR_STATUS_CONTROL_DAC;
with RDR_OP_BUILD_PAC;
with ROTODOME_STATUS_CONTROL_DAC;
with RDR_FCI_DATA_DAC;
with RDR_MESSAGE_API;
with RDR_TRACKS_DAC;
with DIA_API_TYPES;
-- with DIA_RDR_MSG_API;
with HMI_API_TYPES;
with HMI_RDR_MSG_API;
with HMI_RDR_API_TYPES;
with IFF_API_TYPES;
with IFF_RDR_MSG_API;
-- with SEN_RDR_DATA_API;
with TDM_RDR_API_TYPES;
with TDM_RDR_DATA_API;
with TDM_RDR_MSG_API;
with GLOBAL_TYPES;
use GLOBAL_TYPES; -- AHE
New
with IPC_IDENTIFIER;
with STANDARD_TYPES; use STANDARD_TYPES;
--
###########################################################################
-- #####################
#####################
-- ##################### RDR_ASYN_INP_PAC
#####################
-- #####################
#####################
--
###########################################################################
separate (RDR_ASYN_PROC_PKG)
package body RDR_ASYN_INP_PAC
-- RDR_ASYN_INP_PAC package body
--
--
--
-- Source Filename: rdr_asyn_proc_pkg-rdr_asyn_inp_pac.adb
--
--
--
-- Abstract:
--
--
--
--
------------------------------------------------------------------------
is
function "=" (A,B : RDR_API_TYPES.SCAN_MODE_TYPE)
return BOOLEAN renames RDR_API_TYPES."=";
function "=" (A,B : RDR_API_TYPES.RDR_PSV_DROP_STATUS_TYPE)
return BOOLEAN renames RDR_API_TYPES."=";
----------------------------------------------------------------------------
-----
----------------------------------------------------------------------------
-----
--
--
-- HMI/ RDR (Operator) Function Request Handling
--
--
--
----------------------------------------------------------------------------
-----
----------------------------------------------------------------------------
-----
--
-- This procedure handles operator requests for:
-- auto decorrelation, and
-- saving operator settings for ACM_MEDIUM_CHANNEL_QUALITY_RANGE.
procedure PROCESS_HMI_RDR_TWS_FUNCTION
(MESSAGE : in RDR_TYPES.ASYN_PROC_MSGS_TYPE)
is
begin -- PROCESS_HMI_RDR_TWS_FUNCTION
--
case MESSAGE.HMI_RDR_TWS_FUNCTION_REQUEST_MESSAGE.
HMI_RDR_REQUESTED_FUNCTION.REQUEST_ID is
-- Start AHE New
when RDR_API_TYPES.ACM_AUTOMATIC_DECORRELATION_FUNCTION =>
begin
RADAR_STATUS_CONTROL_DAC.WRITE_ACM_AUTOMATIC_DECORRELATION_ON_OFF
(MESSAGE.HMI_RDR_TWS_FUNCTION_REQUEST_MESSAGE.
HMI_RDR_REQUESTED_FUNCTION.
ACM_AUTO_DECORRELATE_SELECT);
--
-- Echo operator control to HMI
HMI_RDR_MSG_API.SEND_RDR_HMI_TWS_UPDATE_DATA
(MESSAGE =>
(SELECTOR =>
HMI_RDR_API_TYPES.RDR_ACM_AUTOMATIC_DECORRELATE_SELECT,
ACM_AUTOMATIC_DECORRELATION_ON_OFF =>
MESSAGE.HMI_RDR_TWS_FUNCTION_REQUEST_MESSAGE.
HMI_RDR_REQUESTED_FUNCTION.ACM_AUTO_DECORRELATE_SELECT));
--
-- Send function acknowledgement
HMI_RDR_MSG_API.SEND_HMI_FUNCTION_ACKNOWLEDGEMENT
(MESSAGE =>
(FUNCTION_REQUEST_ID =>
MESSAGE.HMI_RDR_FUNCTION_REQUEST_MESSAGE.
HMI_FUNCTION_REQUEST_ID,
TWS_ID =>
MESSAGE.HMI_RDR_FUNCTION_REQUEST_MESSAGE.TWS_ID,
ACKNOWLEDGEMENT =>
(SUCCESS => TRUE,
OPERATOR_ALERT => GLOBAL_TYPES.NONE,
ADVISORY_ALERT => GLOBAL_TYPES.NONE )));
--
end;
--
when RDR_API_TYPES.ACM_MEDIUM_CHANNEL_QUALITY_RANGE_FUNCTION =>
begin
RADAR_STATUS_CONTROL_DAC.WRITE_CHANNEL_QUALITY_DATA
(MESSAGE.HMI_RDR_TWS_FUNCTION_REQUEST_MESSAGE.
HMI_RDR_REQUESTED_FUNCTION.
LOW_THRESHOLD,
MESSAGE.HMI_RDR_TWS_FUNCTION_REQUEST_MESSAGE.
HMI_RDR_REQUESTED_FUNCTION.
HIGH_THRESHOLD);
--
-- Echo operator control to HMI
HMI_RDR_MSG_API.SEND_RDR_HMI_TWS_UPDATE_DATA
(MESSAGE =>
(SELECTOR =>
HMI_RDR_API_TYPES.RDR_ACM_MEDIUM_CHANNEL_QUALITY_RANGE,
RDR_ACM_MEDIUM_CHANNEL_QUALITY_DATA =>
(LOW_LIMIT =>
MESSAGE.HMI_RDR_TWS_FUNCTION_REQUEST_MESSAGE.
HMI_RDR_REQUESTED_FUNCTION.LOW_THRESHOLD,
HIGH_LIMIT =>
MESSAGE.HMI_RDR_TWS_FUNCTION_REQUEST_MESSAGE.
HMI_RDR_REQUESTED_FUNCTION.HIGH_THRESHOLD)));
--
-- Send function acknowledgement
HMI_RDR_MSG_API.SEND_HMI_FUNCTION_ACKNOWLEDGEMENT
(MESSAGE =>
(FUNCTION_REQUEST_ID =>
MESSAGE.HMI_RDR_FUNCTION_REQUEST_MESSAGE.
HMI_FUNCTION_REQUEST_ID,
TWS_ID =>
MESSAGE.HMI_RDR_FUNCTI ADVISORY_ALERT =>
GLOBAL_TYPES.NONE)));
end if;
exception
when MCC_EXCEPTIONS.TRACK_DOES_NOT_EXIST_ERROR =>
HMI_RDR_MSG_API.SEND_HMI_FUNCTION_ACKNOWLEDGEMENT
(MESSAGE =>
(FUNCTION_REQUEST_ID =>
MESSAGE.HMI_RDR_FUNCTION_REQUEST_MESSAGE.
HMI_FUNCTION_REQUEST_ID,
TWS_ID =>
MESSAGE.HMI_RDR_FUNCTION_REQUEST_MESSAGE.TWS_ID,
ACKNOWLEDGEMENT =>
(SUCCESS => FALSE,
OPERATOR_ALERT => GLOBAL_TYPES.HOOKED_TRK_WRONG,
ADVISORY_ALERT => GLOBAL_TYPES.NONE)));
end;
-- Unlock transaction.
RDR_TRACKS_DAC.COMMIT;
when RDR_API_TYPES.RDR_TRACK_MASTER_CANCEL_REQUEST =>
declare
RDR_TRACK_ID : ACT_TRACK_C_TYPES.RDR_TRACK_ID_TYPE :=
MESSAGE.HMI_RDR_FUNCTION_REQUEST_MESSAGE
.HMI_RDR_FUNCTION_REQUEST.RDR_TRACK_MASTER_CANCEL_ID;
begin
RDR_TRACKS_DAC.SELECT_RDR_PSV_TRACK_FOR_UPDATE
(RDR_TRACK_ID => RDR_TRACK_ID,
RDR_TRACK_DATA => RDR_TRACK_DATA);
TRACK_MASTER_CANCEL_ELIGIBILITY
:=TDM_RDR_DATA_API.IS_MASTER_CANCEL_ELIGIBLE
(RDR_TDM_RDR_MASTER_CANCEL_ELIGIBLE =>
(RDR_TRACK_ID => RDR_TRACK_ID));
if (RDR_TRACK_DATA.RDR_PSV_DROP_STATUS = RDR_API_TYPES.ACTIVE
and then
TRACK_MASTER_CANCEL_ELIGIBILITY.DELETE_ELIGIBILITY = true)
then
-- Identify this track as "dropping".
RDR_TRACK_DATA.RDR_PSV_DROP_STATUS :=
RDR_API_TYPES.DROPPING;
-- Update RDR Psv Track File.
RDR_TRACKS_DAC.UPDATE_PSV_TRACK
(RDR_TRACK_ID => RDR_TRACK_ID,
RDR_PSV_TRACK_RECORD => RDR_TRACK_DATA);
-- Request TDM to drop this track.
TDM_RDR_MSG_API.SEND_RDR_TDM_RDR_MASTER_CANCEL_COMMAND
(MESSAGE => (RDR_TRACK_ID => RDR_TRACK_ID));
--
--
---------------------------------------------------------------------------
');
select '**** stored procedure excutes correctly when not returning data in
question - select * from get_changerequests(MCR)'; select * from
get_changerequests('MCR');
select '**** stored procedure causes error to occur when accessing data in
question - select * from get_changerequests(SWCR)'; select * from
get_changerequests('SWCR');
select '**** remove data in question - update changerequests set
reasonforchange = '' where changerequestid = 3;'; update changerequests set
reasonforchange = '' where changerequestid = 3;
select '**** stored procedure excutes correctly when data in question is
removed select * from get_changerequests(SWCR)'; select * from
get_changerequests('SWCR');
"Andrew Nykolyn" <nykolyn@ngc.com> writes: > When running a stored procedure that loads data into a temporary table, the > following error occurs when returning the result set through a composite > data type. > Could not open relation with OID 443748 It looks to me like you're running into the same issue described here: http://archives.postgresql.org/pgsql-general/2006-02/msg00172.php namely that a toasted datum is still in memory after the owning table has been dropped, and so subsequent attempts to expand the datum fail. I'm not sure there is any really nice fix for that --- the easy fix of forcibly detoasting everything before plpgsql stores it anyplace would have very unpleasant performance implications. Needs more thought. Curious that this behavior has been there unremarked since 7.1 or so and now two different people stumble across it within a couple of weeks of each other. regards, tom lane