relation with OID 637165 does not exist

orclwujian發表於2016-11-11
執行儲存報錯物件表不存在，而且這個OID還會變，儲存中有段如下程式碼，迴圈刪除、刪除並使用同一個臨時表表名，猜測和臨時表有關 。查詢資料得知PostgreSQL版本＜8.3（GP4.3.8是pg8.2引擎），如果一個PL / pgsql函式訪問一個臨時表，然後這個臨時表刪除並重新建立，並再次呼叫，該函式會失敗，因為快取的功能內容仍指向老 臨時表

dt:='2015-11-01 00:00:00'::date;

while dt<now() loop
drop table if EXISTS t3;
create TEMPORARY table t3 as (
select test.*
      ,SUM(amount) OVER (PARTITION BY user_id ORDER BY date) AS s1
from   test
where date<dt
);

drop table if EXISTS t4;
create TEMPORARY table t4 as (
select  user_id,sum(-amount) as amount
from test
where date<dt GROUP BY user_id
);

INSERT INTO equ
select t3.user_id,t3.date as BEGIN,null as end,case when s1-t4.amount<t3.amount then s1-t4.amount else t3.amount end,3 as flag
  from t3 left join t4
on t3.user_id=t4.user_id and t3.s1>t4.amount where to_number(date,'9999999')=to_number(dt,'9999999');

dt:=dt + INTERVAL '1 month';
end loop;


把臨時表換成子查詢解決
dt:='2015-11-01 00:00:00'::date;

while dt<now() loop

INSERT INTO equ
select t3.user_id,t3.date as BEGIN,null as end,case when s1-t4.amount<t3.amount then s1-t4.amount else t3.amount end,3 as flag
  from
(
select test.*
      ,SUM(amount) OVER (PARTITION BY user_id ORDER BY date) AS s1
from  test
where date<dt
) t3 left join
(
select  user_id,sum(-amount) as amount
from  test
where date<dt GROUP BY user_id
 )t4
on t3.user_id=t4.user_id and t3.s1>t4.amount where to_number(date,'9999999')=to_number(dt,'9999999');

dt:=dt + INTERVAL '1 month';
end loop;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29989552/viewspace-2128284/，如需轉載，請註明出處，否則將追究法律責任。

相關文章