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 $$

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

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.org
http://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