pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedError OID
3+ messages / 2 participants
[nested] [flat]
* Error OID
@ 2007-12-05 02:24 Yohanes Purnomo <yohanes@logicsoftware.info>
2007-12-05 07:36 ` Re: Error OID Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 3+ messages in thread
From: Yohanes Purnomo @ 2007-12-05 02:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Error OID
2007-12-05 02:24 Error OID Yohanes Purnomo <yohanes@logicsoftware.info>
@ 2007-12-05 07:36 ` Guillaume Lelarge <guillaume@lelarge.info>
2007-12-06 02:08 ` Re: Error OID Yohanes Purnomo <yohanes@logicsoftware.info>
0 siblings, 1 reply; 3+ messages in thread
From: Guillaume Lelarge @ 2007-12-05 07:36 UTC (permalink / raw)
To: yohanes@logicsoftware.info; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Error OID
2007-12-05 02:24 Error OID Yohanes Purnomo <yohanes@logicsoftware.info>
2007-12-05 07:36 ` Re: Error OID Guillaume Lelarge <guillaume@lelarge.info>
@ 2007-12-06 02:08 ` Yohanes Purnomo <yohanes@logicsoftware.info>
0 siblings, 0 replies; 3+ messages in thread
From: Yohanes Purnomo @ 2007-12-06 02:08 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-12-06 02:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-05 02:24 Error OID Yohanes Purnomo <yohanes@logicsoftware.info>
2007-12-05 07:36 ` Guillaume Lelarge <guillaume@lelarge.info>
2007-12-06 02:08 ` Yohanes Purnomo <yohanes@logicsoftware.info>
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