relation with OID 637165 does not exist
执行存储报错对象表不存在，而且这个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博客 ” ，链接：https://blog.itpub.net/29989552/viewspace-2128284/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
gp_dump备份恢复greenplum
下一篇：
greenplum 简单sql优化案例
请登录后发表评论
登录
全部评论
