Обсуждение: 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.
>