Обсуждение: Writing data to a text file based on a trigger event...
Hi , I am basically a MySQL DBA and have little idea on PostgreSQL. In our environment we have an application which is using PostgreSQL as its back end. The application logs the status of the jobs running in it to a table in this database i.e when a job starts it inserts a new row to this table and it keeps on updating the column `status` based on the status of the job running. So the requirement that we have is I need to capture certain status values and based on it need to through alert to our centralized monitoring system. What I need to know is it possible to write a trigger which will write the data of the row whose status column gets updated to a text file? Thanks & Regards Raghupradeep -- View this message in context: http://postgresql.1045698.n5.nabble.com/Writing-data-to-a-text-file-based-on-a-trigger-event-tp5635290p5635290.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Le jeudi 12 avril 2012 à 06:58, raghupradeep a écrit : > What I need to know is it possible to write a trigger which will write the > data of the row whose status column gets updated to a text file? I would advise simply INSERTing the old values into a new table. This would be more flexible in the end, as you could exportto a file, or run queries, or whatever. The trigger would be an ON UPDATE, and very similar to the auditing trigger at http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE Welcome to PostgreSQL! Hope that helps! François
On 2012-04-12, raghupradeep <raghupradeep@gmail.com> wrote: > Hi , > > I am basically a MySQL DBA and have little idea on PostgreSQL. In our > environment we have an application which is using PostgreSQL as its back > end. The application logs the status of the jobs running in it to a table in > this database i.e when a job starts it inserts a new row to this table and > it keeps on updating the column `status` based on the status of the job > running. So the requirement that we have is I need to capture certain status > values and based on it need to through alert to our centralized monitoring > system. > > What I need to know is it possible to write a trigger which will write the > data of the row whose status column gets updated to a text file? you mean like "RAISE LOG" does? or do you have specific requirements? -- ⚂⚃ 100% natural
On 12.4.2012 12:58, raghupradeep wrote: > Hi , > > I am basically a MySQL DBA and have little idea on PostgreSQL. In our > environment we have an application which is using PostgreSQL as its back > end. The application logs the status of the jobs running in it to a table in > this database i.e when a job starts it inserts a new row to this table and > it keeps on updating the column `status` based on the status of the job > running. So the requirement that we have is I need to capture certain status > values and based on it need to through alert to our centralized monitoring > system. > > What I need to know is it possible to write a trigger which will write the > data of the row whose status column gets updated to a text file? Hi, as the others already suggested, it's much easier to do this inside a database, i.e. storing the data inside the database itself and writing a simple script to read them. Most monitoring systems I'm aware of (e.g. nagios) support this out of the box. But if you really need to write the data to a file, you may look at this contrib module (called "extension" since 9.1) http://www.postgresql.org/docs/9.1/interactive/adminpack.html You may either use that directly or use that as an inspiration to write your own C extension (it's quite simple). Just be careful about granting the execution rights to regular users, it's probably better to wrap the function in your own functions with fixed (or properly checked) filenames. Tomas
Le dimanche 15 avril 2012 à 15:43 +0200, Tomas Vondra a écrit : > But if you really need to write the data to a file, you may look at this > contrib module (called "extension" since 9.1) > > http://www.postgresql.org/docs/9.1/interactive/adminpack.html > > You may either use that directly or use that as an inspiration to write > your own C extension (it's quite simple). I use plperlu in the function below (update_coll_list) to rewrite a series of files; it is used by a trigger on the table (tblcollectivite). The function only rewrites the files if one particular field (libelle) was modified. Documentation is here : http://www.postgresql.org/docs/9.1/interactive/plperl.html CREATE TRIGGER "tblcollectivite_after_update" AFTER UPDATE OR DELETE OR INSERT ON tblcollectivite FOR EACH ROW EXECUTE PROCEDURE update_coll_list(); CREATE OR REPLACE FUNCTION update_coll_list() RETURNS TRIGGER AS $$ #fonction de re-création des listes alphabétiques des collectivités #inutile de tout réécrire si le libelle n'a pas changé return if ( ( $_TD->{event} eq 'UPDATE' ) and ( $_TD->{new}{libelle} eq $_TD->{old}{libelle}) ); my $id_client = ( $_TD->{event} eq 'DELETE' ) ? $_TD->{old}{id_client} : $_TD->{new}{id_client}; #répertoire de stockage des fichiers écrits par la procédure my $storage_dir = "/home/www_aspro/base/liste_collectivites/$id_client"; #la requête qui ramène les données my $rv = spi_exec_query("SELECT id_collectivite, libelle FROM tblcollectivite WHERE id_client=$id_client ORDER BY 2"); #le fichier 'all' qui liste toutes les collectivités open my $fh, ">$storage_dir/all" or elog(ERROR, qq{could not open file $storage_dir/all : $?}); my %list; #exécuter la requête, compter les lignes my $nrows = $rv->{processed}; #pour chaque ligne, imprimer le nom foreach my $rn (0 .. $nrows - 1) { my $row = $rv->{rows}[$rn]; my $libelle = $row->{id_collectivite} . ';' . $row->{libelle} . "\n"; print $fh $libelle; my $initial = lc(substr($row->{libelle},0,1)); die "non alphabétique : $libelle" if $initial !~/[a-z]/i; $list{$initial} .= $libelle; #elog(INFO, qq {$list{$initial} }); } close $fh; for ('a'..'z') { my $initial_file = $storage_dir . '/' . $_; open my $new_fh, ">$initial_file" or elog(ERROR, qq{could not open file $initial_file : $!}); print $new_fh $list{$_}; #elog(INFO, qq {file: $initial_file / $list{$_} }); close $new_fh; } return; $$ LANGUAGE plperlu; -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique