BUG #2277: Can't Open Relation when returning data from a stored procedure - Mailing list pgsql-bugs

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');