LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 03-28-2003, 02:39 AM   #1
playroll
LQ Newbie
 
Registered: Mar 2003
Location: Netherlands
Distribution: Slackware
Posts: 7

Rep: Reputation: 0
PostgreSQL trigger?


Hello,

I'm trying to make a system where one computer (let's call it the server) updates it's status to a database. All other computers in the network read the most current information about the server from the database.

But, when an alarm or error occurs on the server, it has to update the server, AND the computers in the network have to be informed immediately about the error.

I was thinking about solving it this way:
- Let the server update all info in the database (PostgreSQL)
- When an alarm is inserted or removed, the database will call a trigger
- The trigger runs a program which opens a socket connection to all computers in the network, and send a message that a new alarm has occured
- The computers immediately get the alarm-info from the database

Is this the best sollution, or is there any better/faster/more direct way of doing this? And is it possible for PostgreSQL to call an external program when a trigger is called?

Last edited by playroll; 03-28-2003 at 02:40 AM.
 
Old 03-28-2003, 03:00 AM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,067
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
I'm not quite sure what exactly you are
trying to achieve, but PostgreSQL's
triggers are flexible enough to more
or less do anything that's possible in
any language known to Postgres (except
for plain SQL :}) ... you could make
a trigger stop and start any process
(that's what the interface description
says, I haven't tried it yet, though ;})

Cheers,
Tink
 
Old 03-28-2003, 03:27 AM   #3
playroll
LQ Newbie
 
Registered: Mar 2003
Location: Netherlands
Distribution: Slackware
Posts: 7

Original Poster
Rep: Reputation: 0
Thank you for your information. You said triggers can do almost everything possible in any known language, but the only information I can find on triggers are the triggers that when some data is inserted into the database, the trigger can make changes only within the database.

What I'm trying is this:
I want to have one table, called alarms. Whenever some data is inserted into, or removed from this table, a trigger has to be called. All the trigger has to do, is run an external program/application.

But the only information I can find on triggers, are SQL-related. I just need to make a system call or something like that. Hope it's possible.
 
Old 03-28-2003, 10:20 PM   #4
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,067
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
Look again ...

this is the definition of a trigger, activated
by a change in a database; as you can easily
see you can do *anything* from this code
snippet.

Code:
From the PostgreSQL Programmers Guide
Chapter 16.4
 Here is a very simple example of trigger usage. Function trigf reports the number of tuples in the triggered relation ttest and skips the operation if the query attempts to insert a null value into x (i.e - it acts as a not-null constraint but doesn't abort the transaction).

#include "executor/spi.h"       /* this is what you need to work with SPI */
#include "commands/trigger.h"   /* -"- and triggers */

extern Datum trigf(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(trigf);

Datum
trigf(PG_FUNCTION_ARGS)
{
    TriggerData *trigdata = (TriggerData *) fcinfo->context;
    TupleDesc   tupdesc;
    HeapTuple   rettuple;
    char       *when;
    bool        checknull = false;
    bool        isnull;
    int         ret, i;

    /* Make sure trigdata is pointing at what I expect */
    if (!CALLED_AS_TRIGGER(fcinfo))
        elog(ERROR, "trigf: not fired by trigger manager");

    /* tuple to return to Executor */
    if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
        rettuple = trigdata->tg_newtuple;
    else
        rettuple = trigdata->tg_trigtuple;

    /* check for null values */
    if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
        && TRIGGER_FIRED_BEFORE(trigdata->tg_event))
        checknull = true;

    if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
        when = "before";
    else
        when = "after ";

    tupdesc = trigdata->tg_relation->rd_att;

    /* Connect to SPI manager */
    if ((ret = SPI_connect()) < 0)
        elog(INFO, "trigf (fired %s): SPI_connect returned %d", when, ret);

    /* Get number of tuples in relation */
    ret = SPI_exec("SELECT count(*) FROM ttest", 0);

    if (ret < 0)
        elog(NOTICE, "trigf (fired %s): SPI_exec returned %d", when, ret);

    /* count(*) returns int8 as of PG 7.2, so be careful to convert */
    i = (int) DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
                                          SPI_tuptable->tupdesc,
                                          1,
                                          &isnull));

    elog (NOTICE, "trigf (fired %s): there are %d tuples in ttest", when, i);

    SPI_finish();

    if (checknull)
    {
        (void) SPI_getbinval(rettuple, tupdesc, 1, &isnull);
        if (isnull)
            rettuple = NULL;
    }

    return PointerGetDatum(rettuple);
}
Cheers
Tink
 
Old 03-29-2003, 02:50 AM   #5
playroll
LQ Newbie
 
Registered: Mar 2003
Location: Netherlands
Distribution: Slackware
Posts: 7

Original Poster
Rep: Reputation: 0
Wow, thank you very much for your reply!! I'll give it a try
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
trigger 0.5.1.c mirto Slackware 2 09-08-2005 06:34 AM
PostgreSQL trigger dropping error nazdrowie Programming 3 08-03-2005 12:03 AM
How do I trigger ctrl+z via a script? closet geek Linux - General 1 05-31-2005 06:53 PM
trigger easy urpmi landroni Linux - Games 7 04-21-2005 04:38 PM
postgresql -odbc & postgresql-jdbc installation kjsubbu Linux - Software 0 06-19-2003 02:50 AM


All times are GMT -5. The time now is 10:21 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration