From: | "Luke Lonergan" <llonergan(at)greenplum(dot)com> |
---|---|
To: | pgadmin-support(at)postgresql(dot)org |
Subject: | Small change needed to support Greenplum DB |
Date: | 2007-10-12 03:09:43 |
Message-ID: | C3343388.464BA%llonergan@greenplum.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
Hi all,
A customer of ours is using pgAgent with Greenplum Database and needs a
change to one of the queries used by pgAgent to allow it to work with
Greenplum.
Here is the conversation:
http://groups.google.com/group/pgsql.bugs/browse_thread/thread/27866354fa4d7
a5e/60e6e5de3c5156d4#60e6e5de3c5156d4
The problem is that Greenplum does not support some correlated subqueries,
so Jon created a version of the query that is both faster and does not use a
correlated subquery.
The requested change is from this in pgaJob.cpp:
SELECT *,
(SELECT jlgstatus FROM pgagent.pga_joblog jl WHERE jl.jlgjobid =
j.jobid ORDER BY jlgid DESC LIMIT 1) AS joblastresult
FROM pgagent.pga_job j
JOIN pgagent.pga_jobclass cl ON cl.jclid=jobjclid
LEFT OUTER JOIN pgagent.pga_jobagent ag ON ag.jagpid=jobagentid
-- + restriction +
ORDER BY jobname;
To this:
select j.*, cl.*, ag.*, sub3.jlgstatus
from pgagent.pga_job j join
pgagent.pga_jobclass cl on cl.jclid=jobjclid left outer join
pgagent.pga_jobagent ag on ag.jagpid=jobagentid
join (select j2.jlgstatus,
sub.jlgjobid
from pgagent.pga_joblog j2 join
(select jl.jlgjobid,
max(jl.jlgid) as max_jlgid
from pgagent.pga_joblog jl
group by jl.jlgjobid) sub
on sub.jlgjobid = j2.jlgjobid and
sub.max_jlgid = j2.jlgid) sub3
on sub3.jlgjobid = j.jobid
-- + restriction +
order by jobname;
- Luke
From | Date | Subject | |
---|---|---|---|
Next Message | Artur Linhart - PostgresQL Comunication | 2007-10-12 06:09:12 | PGAdmin crashes on Windows if the file pgpass.conf locally does not exist |
Previous Message | Alexander Kirpa | 2007-10-11 23:18:22 | Re: Grid editor crash during row insert |