Обсуждение: pgAgent queries

Hi Andreas,

I've got a couple of questions on pgAgent if I may...

- In pgAgent.cpp, you run various queries to find and cleanup 'orphaned'
jobs. It's not entirely obviously what you're trying to do here,
especially as the first query seems somewhat bogus:

    rc = serviceConn->ExecuteVoid(
        "INSERT INTO pga_tmp_zombies (jagpid)\n"
        "SELECT jagpid\n"
        "  FROM pgadmin.pga_jobagent AG\n"
        "  LEFT JOIN pg_stat_activity PA ON jagpid=procpid\n"
        " WHERE procpid IS NULL"
        );

Specifically, you're joining on procpid /and/ requiring it to be null,
in a case where one would assume that an orphaned job would be one where
jagpid is definitely not null. I assume the aim here is to clear any
jobs from /this/ instance of the agent? Or are we assuming that there is
only ever one agent running against a given database?

Clearly I'm confused on this one!!

- Second, schedule representation. The UI doesn't seem to even remotely
match the table def, so I assume that was basically a WIP when you
stopped. I'm thinking that the following schedule representation should
be enough:

Start        datetime
RunInterval    interval
End        datetime
NoOfRuns    int
RunsSoFar    int
Next        DateTime

In this scheme, the Start represents the first date and time of the run.
When the row is inserted, the Next is set to this value. When a run
occurs, Next is set to (Next + RunInterval) to get the next run time,
and RunsSoFar is incremented. When End is reached, or NoOfRuns =
NoOfRuns, Next is set to null and no more runs happen. A null NoOfRuns
or End mean run indefinitely at the given interval.

This scheme should allow hourly, daily, weekly schedules etc, but
obviously won't do things like day X of the month. I don't think this is
a major problem for the type of tasks this is designed for - only report
engines (like Mark is designing :-p ) should need things like 'last
Friday of the month' etc. imho - and those features increase complexity
massively.

Any thoughts?

Regards, Dave.

> -----Original Message-----
> From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
> Sent: 02 March 2005 09:28
> To: Dave Page
> Subject: Re: pgAgent queries
>
> Dave Page wrote:
> > Hi Andreas,
> >
> > I've got a couple of questions on pgAgent if I may...
>
> Sure!
>
> > - In pgAgent.cpp, you run various queries to find and
> cleanup 'orphaned'
> > jobs. It's not entirely obviously what you're trying to do here,
> > especially as the first query seems somewhat bogus:
> >
> >     rc = serviceConn->ExecuteVoid(
> >         "INSERT INTO pga_tmp_zombies (jagpid)\n"
> >         "SELECT jagpid\n"
> >         "  FROM pgadmin.pga_jobagent AG\n"
> >         "  LEFT JOIN pg_stat_activity PA ON jagpid=procpid\n"
> >         " WHERE procpid IS NULL"
> >         );
> >
> > Specifically, you're joining on procpid /and/ requiring it
> to be null,
> > in a case where one would assume that an orphaned job would
> be one where
> > jagpid is definitely not null.
>
> Yes, jagpid not null, but procpid, i.e. job was started on a backend
> that's not there any more!
>
> This is yielding the same sa
> SELECT * from jobagent
>   WHERE jagpid NOT IN (select procpid from pg_stat_activity)

Ahh, yes - I totally failed to grok the fact it was a left join!


> > - Second, schedule representation. The UI doesn't seem to
> even remotely
> > match the table def, so I assume that was basically a WIP when you
> > stopped.
>
> Yes. Major rewrite was intended, I wasn't happy with it.
> What I wanted was a sophisticated scheduling scheme as found
> in backup
> programs:
> - repetitive in known intervals

OK, that's easy.

> - on certain days (weekday, monthday)

Weekday is easy as weeks are all 7 days long. Monthdays start to get
tricky.

> - Exceptions, e.g. "run every mo/tue/wed/thu/fr, but not on
> mar22 this
> year".

Why would you want to do that?


> Do you know TapeWare? See the attached screenshot.

No - but on a completely separate note, the tape drive problem on
dev.pga.org has been tracked back to a, err, 'fix' in the latest linux
kernel.

> Well, it shouldn't be YetAnotherCron... I agree, last day of month is
> really hard. Still, reporting (data warehousing) isn't too exotic.

Hmm, I can think of more uses for last day of month et al. than
exceptions :-(. I might need to consult with Mark some more then - his
is a full featured scheduler....

Cheers, Dave