Re: Execute Shell script after insert

andersondonda(at)gmail(dot)com ("Anderson dos Santos Donda") writes:
> Is there a way to execute a simple shell script in server after execute INSERT INTO ?
> Example?
> INSERT INTO clients (name) VALUES ('Donda');
> after it, execute shell : mkdir $1

You could do this, directly, via stored procedure languages:

1. Write an SPI function (C stored procedure) that does this;
2. Write a function in pl/perlu (untrusted version)
3. Write a function in pl/sh
4. Write a function in pl/phpu
5. Write a function in pl/tclu

"Untrusted" indicates that they *all* have the same security issue
that, in this "mode," these SP languages can all do "essentially
anything," which in principle wreaks havoc with security.

You'd create a trigger function that does the "mkdir".

There's the already-mentioned security issue; there's also the problem
that this is inherently a non-transactional process, which raises some
ugly questions:

1. How about rollback? What happens if I do:

begin;
insert into clients (name) values ('Donda');
rollback;
???

With the trigger-based approach, the tuple will be lost from the
table, but you'll probably still have the directory.

If you retry the insert, then maybe the trigger function will fail
(e.g. - the retry of mkdir causes the stored function to error out),
and you can only really clean this up by deleting the directory.

2. What if two attempts go in concurrently?

Race conditions are troublesome...

I would instead suggest doing this outside the DBMS, by having the
directory creation handled *outside* the database.

I would create a "work queue," and have a simpler trigger function
that adds the directory name to the queue. It might then generate
NOTIFY request
<http://www.postgresql.org/docs/8.3/static/sql-notify.html>, which
will cause the outside process, that is using LISTEN, to "wake up" and
process all the work in the queue when the transaction that did the
insert COMMITs.

That means you're not forcing non-transactional functionality into the
DBMS.
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/
When marriage is outlawed, only outlaws will have inlaws.