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/

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/

-----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)---------------------------

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:

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!

"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

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

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

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

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>

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.

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