Thread: Email notification pgAgent

Hi

On Mon, Dec 27, 2010 at 2:32 AM, Jasmin Dizdarevic
<jasmin.dizdarevic@gmail.com> wrote:
> Hi,
> we've just decided to use pgAgent in our company, so I set it up and
> discovered that there is no email notification support on failures.
> I've decided to add this functionality, but I have some questions:
> 1. Have there been any plans to add mail notification in the past?

No.

> 2. Is the feature desirable?

It seems like a reasonable addition.

> 3. I'm absolutely new to wxwidgets: Is wxSMTP a good idea?
> (http://wiki.wxwidgets.org/WxSMTP)

That seems like a good way forward, though to reduce the requirements
on users who don't need it, the email feature should really be made
optional, so if the user doesn't want it, they don't need the
additional module to build pgAgent.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi

On Mon, Dec 27, 2010 at 9:46 AM, Jasmin Dizdarevic
<jasmin.dizdarevic@gmail.com> wrote:
> Hi Dave,
> P3 is a good idea.
> I think the best way to configure notifications is on a per job level. So I
> need two fields in the pga_job table:
> alter table pgagent.pga_job add column jobmailnotkind varchar(7) check
> (jobmailnotkind in ('Success','Failure','Both'));

That should probably be:

alter table pgagent.pga_job add column jobmailnotkind char check
(jobmailnotkind in ('s','f','b'));

which more closely matches the existing style - eg. pga_jobstep.jstonerror.

> alter table pgagent.pga_job add column jobmailnotrecp varchar(320);

LOL - where did you get 320 from? :-). Just make that one a "text"
column. It's actually marginally more efficient in PostgreSQL anyway,
because it eliminates the length check that is required by a
varchar(n).

> The SMTP server address is going to be an optional cl-argument (-s).
> If this is going into a release.

Seems reasonable.

> Can I assume that the fields are present,
> even if pgagent is compiled without mail notification?

You can handle that by bumping the schema version number in
pgagent.pgagent_schema_version, and the pgAgent major version number
to match. pgAgent should then refuse to run on the older, non-upgraded
schema. Of course, you should also update the two SQL scripts
accordingly.

> Background: I think, it would be inefficient to perform an extra select
> between #ifdef MAIL_NOTIFICATION and #endif,

I don't think you'd do it that way - you'd put the 2 versions of the query in an

#ifdef MAIL_NOTIFICATION

#else

#endif

block. But... don't bother :-). Just update the schema as noted above.
The reason for making the mail feature optional, is that we don't want
to force a dependency on the wxSMTP package, but that doesn't mean we
need to remove all traces of the mail feature in builds where it's
disabled.

> Maybe, my questions seems to be silly, sorry for that. It's my first try to
> contribute to an open source project.

No problem at all - we were all first timers once.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi

On Tue, Dec 28, 2010 at 1:54 AM, Jasmin Dizdarevic
<jasmin.dizdarevic@gmail.com> wrote:
> Okay, I'm almost done. Actually there is a problem with building the wxSMTP
> package, so it doesn't send mails yet.
> If this is done, I'm going to test it on Windows and *nix.

Ok.

> Time to take care about some other things:
> 1. I'm coding on Windows with VS 2010. I've downloaded the source files and
> run cmake to get a VS-Project. Here i made the changes. How I can generate a
> patch file with git now, because I'm not working on the original source
> files - because of the cmake step? Have I to redo the changes on *nix?

You need to update the CMakeLists.txt file from which the VC++ project
file and *nix build files are generated.

> 2. What steps are necessary to send the changes to the community? Is there a

Just send a patch (git diff) to pgadmin-hackers@postgresql.org.

> desired process and what's about the necessary changes in the pgAdmin UI?
> Will they be done by the maintainer of pgAdmin? Of course I'm ready to
> participate in pgAdmin too, but my knowledge of C++ and wx are limited, so I
> can not promise best quality ;)

The maintainers of pgAdmin are the same people, and no, they won't
automatically update the UI for the new feature I'm afraid (they have
enough work to do just keeping up with changes in PostgreSQL). Please
give it a try yourself, and we can help you iron out any kinks or
issues you may run into.

Regards, Dave


--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi

On Wed, Dec 29, 2010 at 6:55 PM, Jasmin Dizdarevic
<jasmin.dizdarevic@gmail.com> wrote:
> Hi,
> I've made the necessary changes to pgAdmin, but how do we handle schema
> version conflicts?
> pgAdmin's job UI now will not work with pgAgent schema version 3, because of
> the changes in pgagent.pga_job table. I think we have two possibilities:
> 1. Disable editing Jobs in pgAdmin until a schema upgrade is done
> 2. Check schema version during GetUpdateSql and GetInsertSql and return two
> different versions of the statement.
> What do you think?

I think 2. If the schema isn't of a high enough version, then the
appropriate controls on the UI should also be disabled. This is akin
to how we handle missing features in older versions of PostgreSQL
itself.

> I've got another two topics to discuss about pgAgent:
> 1. Step ordering
>     I suggest adding a column named "jstorder" to pgagent.pga_jobsteps, so
> we don't have to rename the steps "A_", "B_" if an ordering is required. In
> the GUI we would add an integer field to the "Change Step" mask.

I'm not so keen on that - it could require some funky code to ensure
that the user uses sequential (or at least, non-duplicate) numbers
across all steps and would be a pain to upgrade to. Plus, there is
precedence for using alpha ordering - that's how triggers work

> 2. Definition from File
>     Add an extra job step type "SQL from file". The definition field would
> be treated as a path to a file, which contains SQL-Statements.

That seems like a potentially useful feature.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Dec 29, 2010 at 7:30 PM, Jasmin Dizdarevic
<jasmin.dizdarevic@gmail.com> wrote:
>
> Hi,
> I'm not so keen on that - it could require some funky code to ensure
> that the user uses sequential (or at least, non-duplicate) numbers
> across all steps and would be a pain to upgrade to. Plus, there is
> precedence for using alpha ordering - that's how triggers work
>> I don't think that we must ensure that no duplicate values are used. With
>> changing the "order by jstname,jstid" clause to "order by
>> jstorder,jstname,jstid" we would have a fall back on alpha ordering.
>> Steps with "jstorder" = null would be executed last - so there is no need to
>> upgrade. To give the user feedback about ordering in pgadmin, the steps
>> could be ordered the same way in tree view and steps tab in job properties
>> dialog. We could also add the jstorder-column to the list view.

What do others think? I'm still not convinced this is necessary - and
it certainly will become inconsistent with triggers.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Dec 31, 2010 at 3:09 PM, Jasmin Dizdarevic
<jasmin.dizdarevic@gmail.com> wrote:
> Hi,
> back to the main topic: Email notification.
> This week I tried to use the wxSMTP library for the email notification
> feature - without success. There are two versions of the lib. One will not
> be supported in the future, the other is an alpha or beta. I hope this
> extensions will be available as a contrib module for wx in the near future.

:-(

Whats the problem? Does it seem fixable?

> I also couldn't find any other multi platform library, which is open source.
> Do you know one?

No, unfortunately not.

> Another idea was to use a command line tool, which is available for *nix and
> windows (http://caspian.dotconf.net/menu/Software/SendEmail/) and invoke it
> via Exec in pgAgent.
> Is this a practicable way for you?

Not really. It's GPL licenced, which isn't compatible with ours. But
exec'ing a third party app really isn't an architecture I want to
support. Even exec'ing sendmail or "mail" doesn't seem like a nice
option to me.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Dec 31, 2010 at 3:51 PM, Jasmin Dizdarevic
<jasmin.dizdarevic@gmail.com> wrote:
> I've excepted the "no" ;-)
> It would be great if somebody could take a look on it. Maybe it's just a
> beginners mistake of mine.
> The wxSMTP lib and a sample of mine is attached. The author of the lib said
> it could be a problem with missing wxWidgets Event Loop.
> But if running the sample (in wxSMTP-Solution) there is also an access
> violation.
> I used "Static Unicode Debug Monolithic DLL|Win32" configuration to build
> wxSMTP.

Static *and* DLL? For use with pgAgent, you should try what would
normally be "Unicode Debug" or "Unicode Release". That should be a
static, non-monolithic build.

It's not uncommon for for things to work in static wx builds, but not
DLL, or vice-versa, especially on Windows.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Jul 15, 2011 at 12:02 AM, Jasmin Dizdarevic
<jasmin.dizdarevic@gmail.com> wrote:
> Well...it took a long time, but the feature is done :)
> A friend of mine wrote a C implementation of an smtp client and gave it to
> me to include it into pgAgent.
> It's a very basic smtp client, without authentication support. I think we're
> going to include it, if needed.

You're going to hate me for this, but I thought you'd given up on this
project...

I found out the other day that libcurl has support for SMTP, including
TLS/STARTTLS, and SMTP Auth. Is there any reason that cannot be used?
It has an MIT license which is compatible.

Not having SMTP Auth support will be a big issue for many users...

> There is another change in pgAgent. Now it's possible to execute an SQL
> statement from a file located on a place, where pgAgent can access it. I've
> included this feature, because we need it at work. We can exclude it, if
> you're against it.
> The second patch is the GUI for pgAdmin - email notification fields.
> The mailer.c in pgAgent will need some tweaking because of possible double
> includes (wxWidgets).
> pgAgent version has to be 4 now.
>
> Any comments are welcome.
> Jasmin
> 2011/1/5 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
>>
>> First of all the agents check's if email is configured and writes this to
>> the database (pga_jobagent).
>> Older agent's won't run, if the schema version has changed. This is
>> nothing new: Matching between PGAGENT_VERSION_MAJOR and
>> pgagent.schema_version().
>> For the User there are two scenarios:
>> 1. Creating/Modifying job
>> see screen1.png
>> It will check, if the desired agents are configured for email. If "Host
>> agent" is empty, it will check every host registered otherwise the selected
>> host only.
>> This check only applies, when you enable email notification - so there is
>> no problem, when not using it.
>> 2. The configuration changes after the job has been created with email
>> notification
>> see screen2.png
>> In this case the configuratin status for this job will be shown in
>> properties box. It's not possible to update the job, until email
>> notification is disabled, configured or bound to an host agent, that is
>> already configured properly.
>> I'm relatively sure, that we will get the authorization, so I'm supposing
>> that pgAgent 4 will be with SMTP support.
>> Regards,
>> Jasmin
>> 2011/1/5 Dave Page <dpage@pgadmin.org>
>>>
>>> On Tue, Jan 4, 2011 at 10:33 PM, Jasmin Dizdarevic
>>> <jasmin.dizdarevic@gmail.com> wrote:
>>> > Hi Dave, i think we're on the right way to bear this baby ;)
>>>
>>> Well, that's one way of putting it :-)
>>>
>>> > I'm waiting for the authorization for jwsmtp now. When I get it, i will
>>> > include the files into the source tree of pgAgent.
>>>
>>> Please ensure he CC's me with his authorisation, as I'll need to keep
>>> a record of it.
>>>
>>> > The UI checks the schema version and disables/enables all related
>>> > features.
>>> > I've also added an column to pgagent.pga_jobagent, that shows if
>>> > smtp-server
>>> > on the host instance is configured.
>>> > If the user creates or modifies a job where email notification is
>>> > enabled,
>>> > he will get a message, that it won't work.
>>>
>>> How does that work with multiple agents? Don't forget, one of our
>>> supported configurations is having agents running on multiple machines
>>> against the same database, so we'd need to allow for the case where a
>>> user has some agents with SMTP support and some without.
>>>
>>> Though of course, if we get permission to incorporate the code into
>>> pgagent under the appropriate licence, then that is irrelevant as
>>> we'll be able to safely enable email in all cases, and rely on the
>>> schema version to ensure agents are compatible.
>>>
>>> --
>>> Dave Page
>>> Blog: http://pgsnake.blogspot.com
>>> Twitter: @pgsnake
>>>
>>> EnterpriseDB UK: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>
>
>



--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company