Обсуждение: Execute Shell script after insert
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
Thanks!!!
Example?
INSERT INTO clients (name) VALUES ('Donda');
after it, execute shell : mkdir $1
Thanks!!!
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: > 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 > > > > Thanks!!!
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>, 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