Thread: Need A Suggestion

Need A Suggestion

From
"Lane Van Ingen"
Date:
I am working on the development of a military application which uses
PostgreSQL trigger functions. I cannot (unfortunately) tell you specify use,
being as it is classified.

What I need the ability to occasionally call (execute) a .exe program object
outside of the database from within the function (either during or at the
end of the function would be acceptable), giving an explicit path and
command line parameters. It is understood that in doing such the call will
need to be brief in order to avoid slowing up PostgreSQL in any way. Can
anybody make a suggestion as to how to do that, or inform me if there is a
way to do thisa lready?

I have already consulted to other mailing lists for help, with no results. I
am a Windows 2003 user, version 8.0.1




Re: Need A Suggestion

From
"Jonah H. Harris"
Date:
In the past, I've just written a C-based function that calls out to system.


2005/10/10, Lane Van Ingen <lvaningen@esncc.com>:
> I am working on the development of a military application which uses
> PostgreSQL trigger functions. I cannot (unfortunately) tell you specify use,
> being as it is classified.
>
> What I need the ability to occasionally call (execute) a .exe program object
> outside of the database from within the function (either during or at the
> end of the function would be acceptable), giving an explicit path and
> command line parameters. It is understood that in doing such the call will
> need to be brief in order to avoid slowing up PostgreSQL in any way. Can
> anybody make a suggestion as to how to do that, or inform me if there is a
> way to do thisa lready?
>
> I have already consulted to other mailing lists for help, with no results. I
> am a Windows 2003 user, version 8.0.1
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


--
Respectfully,

Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
http://www.enterprisedb.com/


Re: Need A Suggestion

From
"Lane Van Ingen"
Date:
That sounds good, and about what I expected. I am not a C programmer, but
have
access to others who are. Where would I need to put the C function in order
to
have PostgreSQL find it? Any special considerations other than putting it in
'the right library' ??

-----Original Message-----
From: Jonah H. Harris [mailto:jonah.harris@gmail.com]
Sent: Monday, October 10, 2005 3:49 PM
To: Lane Van Ingen
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Need A Suggestion


In the past, I've just written a C-based function that calls out to system.


2005/10/10, Lane Van Ingen <lvaningen@esncc.com>:
> I am working on the development of a military application which uses
> PostgreSQL trigger functions. I cannot (unfortunately) tell you specify
use,
> being as it is classified.
>
> What I need the ability to occasionally call (execute) a .exe program
object
> outside of the database from within the function (either during or at the
> end of the function would be acceptable), giving an explicit path and
> command line parameters. It is understood that in doing such the call will
> need to be brief in order to avoid slowing up PostgreSQL in any way. Can
> anybody make a suggestion as to how to do that, or inform me if there is a
> way to do thisa lready?
>
> I have already consulted to other mailing lists for help, with no results.
I
> am a Windows 2003 user, version 8.0.1
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


--
Respectfully,

Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
http://www.enterprisedb.com/




Re: Need A Suggestion

From
"Dann Corbit"
Date:
> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
> owner@postgresql.org] On Behalf Of Lane Van Ingen
> Sent: Monday, October 10, 2005 12:41 PM
> To: pgsql-hackers@postgresql.org
> Subject: [HACKERS] Need A Suggestion
>
> I am working on the development of a military application which uses
> PostgreSQL trigger functions. I cannot (unfortunately) tell you
specify
> use,
> being as it is classified.
>
> What I need the ability to occasionally call (execute) a .exe program
> object
> outside of the database from within the function (either during or at
the
> end of the function would be acceptable), giving an explicit path and
> command line parameters. It is understood that in doing such the call
will
> need to be brief in order to avoid slowing up PostgreSQL in any way.
Can
> anybody make a suggestion as to how to do that, or inform me if there
is a
> way to do thisa lready?

This is vague.  Perhaps what you want to accomplish is best accomplished
by creation of a function from the source code.

You could also create a generic function that takes a string parameter
and then does a system call based upon that string.

If you need asynchronous operation look at exec.c
> I have already consulted to other mailing lists for help, with no
results.
> I
> am a Windows 2003 user, version 8.0.1
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster


Re: Need A Suggestion

From
Andrew Dunstan
Date:
Another possibility is to use an untrusted interpreted PL such as 
plperlu. That avoids the need for C. Or maybe you could prototype using 
plperlu and then translate to C when it's all working. It all depends on 
your needs.

cheers

andrew

Lane Van Ingen wrote:

>That sounds good, and about what I expected. I am not a C programmer, but
>have
>access to others who are. Where would I need to put the C function in order
>to
>have PostgreSQL find it? Any special considerations other than putting it in
>'the right library' ??
>
>-----Original Message-----
>From: Jonah H. Harris [mailto:jonah.harris@gmail.com]
>Sent: Monday, October 10, 2005 3:49 PM
>To: Lane Van Ingen
>Cc: pgsql-hackers@postgresql.org
>Subject: Re: [HACKERS] Need A Suggestion
>
>
>In the past, I've just written a C-based function that calls out to system.
>
>
>2005/10/10, Lane Van Ingen <lvaningen@esncc.com>:
>  
>
>>I am working on the development of a military application which uses
>>PostgreSQL trigger functions. I cannot (unfortunately) tell you specify
>>    
>>
>use,
>  
>
>>being as it is classified.
>>
>>What I need the ability to occasionally call (execute) a .exe program
>>    
>>
>object
>  
>
>>outside of the database from within the function (either during or at the
>>end of the function would be acceptable), giving an explicit path and
>>command line parameters. It is understood that in doing such the call will
>>need to be brief in order to avoid slowing up PostgreSQL in any way. Can
>>anybody make a suggestion as to how to do that, or inform me if there is a
>>way to do thisa lready?
>>
>>I have already consulted to other mailing lists for help, with no results.
>>    
>>
>I
>  
>
>>am a Windows 2003 user, version 8.0.1
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: Don't 'kill -9' the postmaster
>>
>>    
>>
>
>
>--
>Respectfully,
>
>Jonah H. Harris, Database Internals Architect
>EnterpriseDB Corporation
>http://www.enterprisedb.com/
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>  
>


Re: Need A Suggestion

From
David Fetter
Date:
On Mon, Oct 10, 2005 at 03:40:43PM -0400, Lane Van Ingen wrote:
> I am working on the development of a military application which uses
> PostgreSQL trigger functions. I cannot (unfortunately) tell you
> specify use, being as it is classified.
> 
> What I need the ability to occasionally call (execute) a .exe
> program object outside of the database from within the function
> (either during or at the end of the function would be acceptable),
> giving an explicit path and command line parameters.

You may be able to use the LISTEN/NOTIFY structure described below
instead of a trigger.

http://www.postgresql.org/docs/current/static/sql-notify.html
http://www.postgresql.org/docs/current/static/libpq-notify.html
http://blackhawk.supernews.net/listen.pl.txt

> It is understood that in doing such the call will need to be brief
> in order to avoid slowing up PostgreSQL in any way. Can anybody make
> a suggestion as to how to do that, or inform me if there is a way to
> do thisa lready?
> 
> I have already consulted to other mailing lists for help, with no
> results. I am a Windows 2003 user, version 8.0.1

You might want to upgrade to 8.0.4 :)

Cheers,
David.
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!


Re: Need A Suggestion

From
Tom Lane
Date:
"Jonah H. Harris" <jonah.harris@gmail.com> writes:
> In the past, I've just written a C-based function that calls out to system.

Use pltclu, plpythonu, or plperlu, according to taste.  They all have
pre-existing solutions for this.

Whether this is a good idea is another question entirely.  Lots of
people will tell you it's a horrid idea for PG functions to cause
outside-the-database side effects.  The reason is that if the
transaction that called the function aborts later, there is no way
to roll back what was done outside the database, and so the state
outside the database will no longer be in sync with the state inside.
        regards, tom lane


Re: Need A Suggestion

From
Neil Conway
Date:
On Mon, 2005-10-10 at 15:57 -0400, Lane Van Ingen wrote:
> That sounds good, and about what I expected. I am not a C programmer, but
> have access to others who are. Where would I need to put the C function
> in order to have PostgreSQL find it? Any special considerations
> other than putting it in 'the right library' ??

http://www.postgresql.org/docs/8.0/static/xfunc-c.html

-Neil




Re: Need A Suggestion

From
Christopher Kings-Lynne
Date:
> Whether this is a good idea is another question entirely.  Lots of
> people will tell you it's a horrid idea for PG functions to cause
> outside-the-database side effects.  The reason is that if the
> transaction that called the function aborts later, there is no way
> to roll back what was done outside the database, and so the state
> outside the database will no longer be in sync with the state inside.

The new solution for this is to use an XA transaction manager and 2PC I 
guess...

Chris



MySQL XA (Was Re: Need A Suggestion)

From
Christopher Kings-Lynne
Date:
> Whether this is a good idea is another question entirely.  Lots of
> people will tell you it's a horrid idea for PG functions to cause
> outside-the-database side effects.  The reason is that if the
> transaction that called the function aborts later, there is no way
> to roll back what was done outside the database, and so the state
> outside the database will no longer be in sync with the state inside.

By the way - an interesting note.  Since MySQL 5.0 has full support for 
2PC and XA from now is the first time that MySQL and PostgreSQL can 
participate in the same transaction :D

Perhaps people might start using the two databases together to harness 
the power of each? :)

Chris



Re: Need A Suggestion

From
Hannu Krosing
Date:
On E, 2005-10-10 at 16:32 -0400, Tom Lane wrote:
> "Jonah H. Harris" <jonah.harris@gmail.com> writes:
> > In the past, I've just written a C-based function that calls out to system.
> 
> Use pltclu, plpythonu, or plperlu, according to taste.  They all have
> pre-existing solutions for this.
> 
> Whether this is a good idea is another question entirely.  Lots of
> people will tell you it's a horrid idea for PG functions to cause
> outside-the-database side effects.  The reason is that if the
> transaction that called the function aborts later, there is no way
> to roll back what was done outside the database, and so the state
> outside the database will no longer be in sync with the state inside.

Is there a simple, user-accessible mechanism to schedule a function to
be run at query commit ?

-- 
Hannu Krosing <hannu@skype.net>



Re: Need A Suggestion

From
"Ilia Kantor"
Date:
> Is there a simple, user-accessible mechanism to schedule a function to
> be run at query commit ?

CONSTRAINT TRIGGER (DEFERRABLE)

It is kinda hack, because CONSTRAINT TRIGGERs are not indended for such use,
But there are no other "ON COMMIT" triggers in postgresql.




Re: Need A Suggestion

From
Alfranio Correia Junior
Date:
You can find something to run triggers on commit at 
http://gorda.di.uminho.pt/community/pgsqlhooks/
It also allows to define triggers on begin, startup and shutdown.

It works fine with triggers written in C.
A patch will be available soon in order to allow to writte triggers in 
other languages such as plgsql, perl, java, etc..
and also to allow triggers on [before | after] commit and [before | 
after] abort.

Best regards,

Alfranio Junior