Обсуждение: Re: Small change needed to support Greenplum DB
<p><font size="2">Yes it does!<br /><br /> - Luke<br /><br /> Msg is shrt cuz m on ma treo<br /><br /> -----Original Message-----<br/> From: Dave Page [<a href="mailto:dpage@postgresql.org">mailto:dpage@postgresql.org</a>]<br /> Sent: Friday, October 12, 2007 10:21 AM Eastern Standard Time<br /> To: Luke Lonergan<br /> Cc: pgadmin-support@postgresql.org<br/> Subject: Re: [pgadmin-support] Small change needed to support Greenplum DB<br/><br /> Luke Lonergan wrote:<br /> > Hi all,<br /> ><br /> > A customer of ours is using pgAgent with GreenplumDatabase and needs a<br /> > change to one of the queries used by pgAgent to allow it to work with<br /> >Greenplum.<br /> ><br /> > Here is the conversation:<br /> > _<a href="http://groups.google.com/group/pgsql.bugs/browse_thread/thread/27866354fa4d7a5e/60e6e5de3c5156d4#60e6e5de3c5156d4">http://groups.google.com/group/pgsql.bugs/browse_thread/thread/27866354fa4d7a5e/60e6e5de3c5156d4#60e6e5de3c5156d4</a><br />><br /> > _The problem is that Greenplum does not support some correlated<br /> > subqueries, so Jon created aversion of the query that is both faster<br /> > and does not use a correlated subquery.<br /> ><br /> > The requestedchange is from this in pgaJob.cpp:<br /><br /><br /> Hi Luke,<br /><br /> Does Greenplum support DISTINCT ON? Specifically,<br/><br /> SELECT j.*, cl.*, ag.*, sub.jlgstatus<br /> FROM pgagent.pga_job j JOIN<br /> pgagent.pga_jobclasscl ON cl.jclid=jobjclid LEFT OUTER JOIN<br /> pgagent.pga_jobagent ag ON ag.jagpid=jobagentid JOIN<br/> (SELECT DISTINCT ON (jlgjobid) jlgstatus, jlgjobid<br /> FROM pgagent.pga_joblog<br /> ORDER BYjlgjobid, jlgid desc) sub ON sub.jlgjobid = j.jobid<br /> -- + restriction +<br /> ORDER BY jobname;<br /><br />Regards, Dave.<br /></font>
Thanks Luke - fix committed for 1.8 using the DISTINCT ON version of the query. Regards, Dave Luke Lonergan wrote: > Yes it does! > > - Luke > > Msg is shrt cuz m on ma treo > > -----Original Message----- > From: Dave Page [mailto:dpage@postgresql.org] > Sent: Friday, October 12, 2007 10:21 AM Eastern Standard Time > To: Luke Lonergan > Cc: pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] Small change needed to support > Greenplum DB > > Luke Lonergan wrote: >> 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/27866354fa4d7a5e/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: > > > Hi Luke, > > Does Greenplum support DISTINCT ON? Specifically, > > SELECT j.*, cl.*, ag.*, sub.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 DISTINCT ON (jlgjobid) jlgstatus, jlgjobid > FROM pgagent.pga_joblog > ORDER BY jlgjobid, jlgid desc) sub ON sub.jlgjobid = j.jobid > -- + restriction + > ORDER BY jobname; > > Regards, Dave. >