Postgresql Triggers

nOw

Hi all !

Can anyone help me ???

I will use postgresql triggers to restart a service with new information

i thought when i use create trigger 'test' after, when the triggerfunction is called the new data INSERT/UPDATE is allready in the table, but my service restarted without the new information, the new data where insertered to the table after the triggerfunction was called

where is the real different between after/befor, out of that i can change the data for instert/update when i use befor

is there a way to call the function after the data is posted to the table

thanks for all,
nOw

Sxooter

Are you using a different session to see if the data is inserted? The data won't be visible until after commit to other sessions, generally speaking, so it's possible that the data are in the database, but you just can't see it yet from another session.

Just guessing.

nOw

Yes I'm using an other session, a C program,

is there a way to make the new data for other sessions visible ???

thx, nOw

[deleted]

"where is the real different between after/befor,"

The difference is that 'BEFORE' will run the trigger 'before' the query is executed, and 'AFTER' will run the trigger 'after' the query is executed.

But what do you mean by 'restarting a service'?

nOw

I mean that so,

i have a ftp daemon that can take his configuration information on start/reload from a database

so i start a trigger after I insert a anonymous ftp server for a ip in the database and a trigger will restart the server

but the new anonymous account isn't still activated, becourse the data isn't visible for him, until the triggerfunction is runnig

so what can I do that the data is visible for other sessions when the triggerfunction call a external program that open a new session

thx nOw

[deleted]

Triggers execute SQL queries, they obviously do not allow you to execute arbitrary system commands like restarting an FTP server.

csn

You could probably write a trigger function in python or C that could restart FTP. But it's probably better handled by a program/script outside of the database.

nOw

to restart the programm isn't the problem, my stored procedure is written in tcl and with the pltclu procedure language there isn't a problem to start the external program

my problem is that the external program cann't view the new inserted data in the table during the triggerprocedure process

and my question was if there is a way to make the new data visible to the other session when i call the triggerfunction with after

thx nOw

[deleted]

And the logical reply was that a trigger is not meant for these kinds of operations.

It is ofcourse quite insane to restart an FTP server from inside a database.

nOw

why is it insane,

what other ways there to do this, when data changed in the database ?

Sxooter

Yes, you can make your data visible. You have to commit your changes. You might have to wait for the your trigger to finish first. Is that workable?

[deleted]

"why is it insane, "

Because the database just contains information about the FTP server. The fact that the information changed should never affect the status of the server.

What if you somehow decide to make ten changes to the configuration (it can and will happen!) then the database will restart the ftp server ten times, an nine of those restarts are completely unnecessary.

What if you have 20 FTP servers and you decide to make 10 changes to all of them? The database will be stopping and starting servers 200(!) times...

But most importantly, a database should not be in control of services, it should not have th rights to stop and start services. If a hacker somehow managed to get into your database then not only can he change the configuration of the servers, all his changes are automatically implemented for him by the database. The hacker doesn't have to get root access to be abvle to make the changes active, you already did that through the database.

All he has to do is change the FTP-server's root to the server's root and he's in your system...