Thread: Error OID

Hi all,

I have a big table:

CREATE TABLE Jurnal (Rekening Char(4) NOT NULL,  Debet Numeric(9) NOT
NULL,  Kredit Numeric(9) NOT NULL)
INSERT INTO Jurnal (Rekening,Debet,Kredit) values ('0001',1,2);
INSERT INTO Jurnal (Rekening,Debet,Kredit) values ('0002',3,4);



I create a function:

CREATE OR REPLACE FUNCTION ReProses()
RETURNS BOOLEAN
AS $$

DECLAREnHasil    Numeric;

BEGIN      CREATE TEMP TABLE tmpTtlRekening WITHOUT OIDS ON COMMIT DROP          AS      SELECT Rekening, SUM(Debet) AS
Debet,SUM(Kredit) AS Kredit        FROM Jurnal GROUP BY Rekening;
 
      SELECT COALESCE(SUM(Debet - Kredit), 0)        INTO nHasil        FROM tmpTtlRekening;
        -- bla,bla        RETURN '1';
END;
$$ LANGUAGE 'plpgsql';


Execute Query:

SELECT ReProses()

Result is fine, but when i execute again

SELECT ReProses()

ERROR: relation with OID 41573 does not exist



Can anyone help me

thanks

Yohanes Purnomo a écrit :
> [...]
> I create a function:
> 
> CREATE OR REPLACE FUNCTION ReProses()
> RETURNS BOOLEAN
> AS $$
> 
> DECLARE
>     nHasil    Numeric;
> 
> BEGIN
>        CREATE TEMP TABLE tmpTtlRekening WITHOUT OIDS ON COMMIT DROP
>            AS
>        SELECT Rekening, SUM(Debet) AS Debet, SUM(Kredit) AS Kredit
>          FROM Jurnal
>   GROUP BY Rekening;
> 
>        SELECT COALESCE(SUM(Debet - Kredit), 0)
>          INTO nHasil
>          FROM tmpTtlRekening;
> 
>          -- bla,bla
>          RETURN '1';
> END;
> $$ LANGUAGE 'plpgsql';
> 
> 
> Execute Query:
> 
> SELECT ReProses()
> 
> Result is fine, but when i execute again
> 

When you run a function for the first time in the session, PostgreSQL
keeps in cache some information... query plans for example.

> SELECT ReProses()
> 
> ERROR: relation with OID 41573 does not exist
> 

When you run it a second time, it uses the query plan in cache. As
tmpTtlRekening has been drop at the end of the first run, its OID will
change but the old OID is still in cache. So PostgreSQL tries to get
information from the old temp table.

If you don't want that PostgreSQL put the query plan in cache, use the
EXECUTE statement.

Regards.


-- 
Guillaume.http://www.postgresqlfr.orghttp://dalibo.com

Guillaume Lelarge write:

> When you run a function for the first time in the session, PostgreSQL
> keeps in cache some information... query plans for example.
>
>
> When you run it a second time, it uses the query plan in cache. As
> tmpTtlRekening has been drop at the end of the first run, its OID will
> change but the old OID is still in cache. So PostgreSQL tries to get
> information from the old temp table.
>

Guillaume Lelarge many thanks for your information

Regards

Yohanes Purnomo