From: Guillaume Lelarge <guillaume@lelarge.info>
To: yohanes@logicsoftware.info
Cc: pgsql-sql@postgresql.org
Subject: Re: Error OID
Date: Wed, 05 Dec 2007 08:36:15 +0100
Message-ID: <475654EF.8010804@lelarge.info> (raw)
In-Reply-To: <1254.222.124.95.13.1196821462.squirrel@email.powweb.com>
References: <1254.222.124.95.13.1196821462.squirrel@email.powweb.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: guillaume@lelarge.info, yohanes@logicsoftware.info
Subject: Re: Error OID
In-Reply-To: <475654EF.8010804@lelarge.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox