Re: Where is PLbash ?? - Mailing list pgsql-interfaces

> As Jan pointed out, either plperlu or pltclu can accomplish just about
> anything you'd want in that line, even without the escape hatch of
> firing a shell script.

But if you can do it more easily with PL/sh why don't you?

> 
> I think Bruce has correctly identified a documentation deficiency:
> there's not any material explaining the transaction-unsafety risks of
> using these PLs to do stuff outside the database.  We should talk
> about that someplace.

Yes, please. See below.

> 
> My opinion is that the *preferred* way would involve having a background
> client-side task that watches for things to do (eg via NOTIFY/LISTEN),
> and then updates the database to indicate that it's done it.  This
> doesn't require any unsafe PL programming at all, but it does mean extra
> work to set up a suitable client task.  An example would make a great
> contrib item ...

Well, I am one of those poor guys who live in the real world and just
would like to (and have to) get their job done.

In my case, I don't want to fire an email. We have an application server
running on top of postgresql and we need a reliable method to trigger
some action in the application server as soon as some table is changed
by an external program. As far as I see the application server
specification (here: EJB) doesn't provide for such a method that is
guaranteed to not do a resource consuming polling. This is a typical
instance of the often occuring problem that a higher level interface
(EJB) isolates you from things you could have done easily on a lower
level (postgres). Now, the solution I had in mind when I asked for PL/sh
was to trigger a shell script that calls a tiny Java-program that calls
a SessionBean in the EJB-Tier which in turn finds out what happened in
the table. This is nasty, this is slow, but it does what I want to get
done - and it prevents polling. In fact, before you told me where to
find PL/sh, I wrote a small C procedure that forks the shell.

Yes, I realized there is the NOTIFY/LISTEN possibility. But using this
would cost me much more development time. And, later on, it will cost
more administration effort. The point here is: How can I be safe that my
LISTENing process is always up and running? That goes into the startup
scripts, into the keepalive logic, and it requires a much more subtle
testing.

So, what is the weakness of my strategy? Is there a
"transaction-unsafety risk" in this case? I would LIKE to learn more
about this.

The discussion of whether to include PL/sh into the
Postgres-Distribution or not resembles a discussion of whether to have a
knife in your kitchen. You can do a lot of useful things with a knife
but you and your children can hurt themselves with it as well. What do
you do then? You tell your children that a knife can be dangerous and
you try to be careful yourself. If you overprotect your children they
never get adult. - I mean treat your users as adults. If you don't
recommend the tool don't put it into the distribution, but it would be
nice if you put it somewhere where it can be found easily, needless to
say with the appropriate warnings.

Regards
Friedrich Dodt