Thread: 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? I have already consulted to other mailing lists for help, with no results. I am a Windows 2003 user, version 8.0.1
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)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
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 > > >
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