Execute Shell script after insert

Anderson dos Santos Donda wrote:

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 might find the following project useful:

http://plsh.projects.postgresql.org/

It adds sh as a procedural language to your PostgreSQL installation.
Please be aware that there are the same security concerns as with all
other untrusted procedural languages.

--
Best regards,
Hannes Dorbath

On Mon, Oct 27, 2008 at 12:21 PM, Hannes Dorbath
<light@theendofthetunnel.de> wrote:

Anderson dos Santos Donda wrote:

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 might find the following project useful:

http://plsh.projects.postgresql.org/

It adds sh as a procedural language to your PostgreSQL installation.
Please be aware that there are the same security concerns as with all
other untrusted procedural languages.

Note that if you can't get that to work (I think there were some
issues with it and the latest versions of pgsql) you use almost any
untrusted pl language to accomplish the same thing, plperlu, plphpu,
pltclu etc...

You can write the insert into command in a shellscript by using

#!/bin/bash

psql -c "INSERT INTO ...." <db_name> -U <username>

mkdir $1

Anderson dos Santos Donda wrote:

On Mon, Oct 27, 2008 at 03:09:31PM -0300, Anderson dos Santos Donda wrote:

Is there a way to execute a simple shell script in server after execute
INSERT INTO ?

Yes; as other people have said most of the procedural languages allow
you to run code outside PG. You'd just need to hook this procedure up
to a trigger inside postgres that ran when data was inserted.

As a rule, though, I'd tend not to do this. The rationale being,
when something goes wrong (as code inevitably does) the database
will continue doing things automatically for you (like touching the
filesystem) when you're fighting against it trying to fix things. I'd
be more tempted to write a stored procedure that inserted something
into a table and did the fiddling with the outside world. That way you
can still write normal INSERT statements to fix up the database and not
worry about your triggers trying to be helpful. I'd expect to stop
normal users from being able to INSERT data into the table, thus forcing
them (or, more accurately, the code running on their behalf) to use the
stored procedure.

Sam

Sam Mason <sam@samason.me.uk> writes:

On Mon, Oct 27, 2008 at 03:09:31PM -0300, Anderson dos Santos Donda wrote:

Is there a way to execute a simple shell script in server after execute
INSERT INTO ?

Yes; as other people have said most of the procedural languages allow
you to run code outside PG. You'd just need to hook this procedure up
to a trigger inside postgres that ran when data was inserted.

As a rule, though, I'd tend not to do this. The rationale being,
when something goes wrong (as code inevitably does) the database
will continue doing things automatically for you (like touching the
filesystem) when you're fighting against it trying to fix things.

The usual explanation of why this is a bad idea is that if the
transaction aborts at some point after running the trigger, then the
INSERT effectively didn't happen --- but the trigger's
outside-the-database effects still happened, and there's no way to cause
them to roll back. So you will inevitably end up with the database
being out of sync with whatever you're trying to use the trigger to update.

regards, tom lane

On Mon, Oct 27, 2008 at 03:09:31PM -0300, Anderson dos Santos Donda wrote:

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

This will scale better if you batch it, as in:

INSERT INTO clients (name, status)
VALUES
('Donda', 'inserted_no_directory'),
('Eonda', 'inserted_no_directory'),
('Fonda', 'inserted_no_directory'),
...

Once you're done loading, sweep through the ones so marked.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

andersondonda@gmail.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&gt;, 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.

On Mon, Oct 27, 2008 at 04:16:16PM -0400, Tom Lane wrote:

Sam Mason <sam@samason.me.uk> writes:

when something goes wrong (as code inevitably does) the database
will continue doing things automatically for you (like touching the
filesystem) when you're fighting against it trying to fix things.

The usual explanation of why this is a bad idea is that if the
transaction aborts at some point after running the trigger, then the
INSERT effectively didn't happen --- but the trigger's
outside-the-database effects still happened, and there's no way to cause
them to roll back. So you will inevitably end up with the database
being out of sync with whatever you're trying to use the trigger to update.

Yes, that's probably better.

As a meta-comment, doing things in a stored procedure is still subject
to rollback (albeit with *many* less provisos) and hence there's still a
possibility of things getting out of step with each other. In general,
multiple failure domains (i.e. more than one) are difficult however
they are arranged. This is mainly why databases are nice, you can
normally just hide away in your own little world and pretend that there
is only one failure domain and, as an added bonus, it's even got nice
transactional semantics.

Sam