Thread
-
question
Sandeep Joshi <sjoshi@zambeel.com> — 2000-12-15T20:20:42Z
I need to update a second database through a trigger function written 'C'. Is that possible ? how? Do I need to go to commercial product for this? - Sandeep
-
Re: question
Alfred Perlstein <bright@wintelcom.net> — 2000-12-15T21:37:51Z
* Sandeep Joshi <sjoshi@Zambeel.com> [001215 12:43] wrote: > > I need to update a second database through a trigger function written > 'C'. > > Is that possible ? how? > > Do I need to go to commercial product for this? No, just reading the documentation should help. :) I imagine you should be able to link to libpq and do the updates using that. -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
-
Re: question
Sandeep Joshi <sjoshi@zambeel.com> — 2000-12-15T22:35:53Z
I have attached my program which I am trying to load as a trigger function. I was unsuccessful. I cannot link due to "elog" and "SPI_*" functions. which library are they defined in? I am linking "-lpq" on the command line. Sandeep > * Sandeep Joshi <sjoshi@Zambeel.com> [001215 12:43] wrote: > > > > I need to update a second database through a trigger function written > > 'C'. > > > > Is that possible ? how? > > > > Do I need to go to commercial product for this? > > No, just reading the documentation should help. :) > > I imagine you should be able to link to libpq and do the updates > using that. > > -- > -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] > "I have the heart of a child; I keep it in a jar on my desk."
-
Re: question
Sandeep Joshi <sjoshi@zambeel.com> — 2000-12-15T22:49:16Z
ld -shared -o db1trig.so db1trig.o -L/work/posgresql/lib -static -lpq Using above link line I was able to link and load the library but I get following error ERROR: conneciton bad. Sandeep > I have attached my program which I am trying to load as a trigger > function. > I was unsuccessful. > > I cannot link due to "elog" and "SPI_*" functions. which library are > they > defined in? > > I am linking "-lpq" on the command line. > > Sandeep > > > * Sandeep Joshi <sjoshi@Zambeel.com> [001215 12:43] wrote: > > > > > > I need to update a second database through a trigger function > written > > > 'C'. > > > > > > Is that possible ? how? > > > > > > Do I need to go to commercial product for this? > > > > No, just reading the documentation should help. :) > > > > I imagine you should be able to link to libpq and do the updates > > using that. > > > > -- > > -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] > > "I have the heart of a child; I keep it in a jar on my desk." > > ------------------------------------------------------------------------ > > db1trig.c.errName: db1trig.c.err > Type: Plain Text (text/plain)
-
Re: question
Alfred Perlstein <bright@wintelcom.net> — 2000-12-15T22:57:41Z
* Sandeep Joshi <sjoshi@Zambeel.com> [001215 14:54] wrote: > ld -shared -o db1trig.so db1trig.o -L/work/posgresql/lib -static -lpq > > Using above link line I was able to link and load the library but I get > following error > ERROR: conneciton bad. I think using both -shared and -static is an error, you might try linking to /work/posgresql/lib/libpq.a or try without as well.
-
Re: question
Sandeep Joshi <sjoshi@zambeel.com> — 2000-12-15T23:12:50Z
It results in following error: (just "shared") ERROR: Load of file /var/lib/pgsql/trigger/db1trig.so failed: libpq.so.2.1: cannot open shared object file: No such file or directory I tried following link line (which specifies "run-time" linking path). ld -shared -o db1trig.so db1trig.o -R/work/posgresql/lib -lpq This results in following error when "PQsetdb" is called: pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Sandeep > * Sandeep Joshi <sjoshi@Zambeel.com> [001215 14:54] wrote: > > ld -shared -o db1trig.so db1trig.o -L/work/posgresql/lib -static -lpq > > > > Using above link line I was able to link and load the library but I get > > following error > > ERROR: conneciton bad. > > I think using both -shared and -static is an error, you might try > linking to /work/posgresql/lib/libpq.a or try without as well. -
Re: question
Alfred Perlstein <bright@wintelcom.net> — 2000-12-15T23:42:04Z
* Sandeep Joshi <sjoshi@Zambeel.com> [001215 15:25] wrote: > It results in following error: (just "shared") > > ERROR: Load of file /var/lib/pgsql/trigger/db1trig.so failed: libpq.so.2.1: > cannot open shared object file: No such file or directory > > I tried following link line (which specifies "run-time" linking path). > ld -shared -o db1trig.so db1trig.o -R/work/posgresql/lib -lpq > > This results in following error when "PQsetdb" is called: > > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. You didn't follow my last suggestion which was to take out -L... and -lpq and instead use /work/posgresql/lib/libpq.a on the command line. > > > Sandeep > > > > > * Sandeep Joshi <sjoshi@Zambeel.com> [001215 14:54] wrote: > > > ld -shared -o db1trig.so db1trig.o -L/work/posgresql/lib -static -lpq > > > > > > Using above link line I was able to link and load the library but I get > > > following error > > > ERROR: conneciton bad. > > > > I think using both -shared and -static is an error, you might try > > linking to /work/posgresql/lib/libpq.a or try without as well. -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
-
Re: question
Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-16T01:36:09Z
Sandeep Joshi <sjoshi@Zambeel.com> writes: > It results in following error: (just "shared") > ERROR: Load of file /var/lib/pgsql/trigger/db1trig.so failed: libpq.so.2.1: > cannot open shared object file: No such file or directory > I tried following link line (which specifies "run-time" linking path). > ld -shared -o db1trig.so db1trig.o -R/work/posgresql/lib -lpq > This results in following error when "PQsetdb" is called: > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. It looks like you're down to debugging vagaries of the dynamic linker on your platform :-(. You might find that the linker sends useful error messages to stderr --- check the postmaster's logfile. (If you're not redirecting the postmaster's stdout/stderr into a logfile, do so. Don't forget to NOT use -S, or the logfile will never get any output.) regards, tom lane
-
Re: question
Alfred Perlstein <bright@wintelcom.net> — 2000-12-16T01:37:32Z
* Tom Lane <tgl@sss.pgh.pa.us> [001215 17:36] wrote: > Sandeep Joshi <sjoshi@Zambeel.com> writes: > > It results in following error: (just "shared") > > ERROR: Load of file /var/lib/pgsql/trigger/db1trig.so failed: libpq.so.2.1: > > cannot open shared object file: No such file or directory > > > I tried following link line (which specifies "run-time" linking path). > > ld -shared -o db1trig.so db1trig.o -R/work/posgresql/lib -lpq > > > This results in following error when "PQsetdb" is called: > > > pqReadData() -- backend closed the channel unexpectedly. > > This probably means the backend terminated abnormally > > before or while processing the request. > > The connection to the server was lost. Attempting reset: Failed. > > It looks like you're down to debugging vagaries of the dynamic linker > on your platform :-(. You might find that the linker sends useful > error messages to stderr --- check the postmaster's logfile. (If you're > not redirecting the postmaster's stdout/stderr into a logfile, do so. > Don't forget to NOT use -S, or the logfile will never get any output.) Actually we got it working, it just took several tries for Sandeep to understand what I was trying to tell him to do: ld -shared -o db1trig.so db1trig.o /work/posgresql/lib/libpq.a -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
-
Re: question
Robert B. Easter <reaster@comptechnews.com> — 2000-12-16T02:51:02Z
If you or anyone reading this can put together a working C trigger example with a test table and compile and trigger load procedure, I'd like to have it to play with! I've been trying to use the examples in the docs and the ones in contrib/spi, but with no luck. I'm using 7.0.3. I notice that the delev docs have a different C trigger example than the 7.0 docs. I don't know what the differences are, one returns a HeapTuple and the other a Datum (a unsigned long)? Anyhow, some more detailed, up-to-date, docs on some of this would be nice. If I can learn these structs etc for C triggers, I'll try to write some HTML page about it how it all works in detail. On Friday 15 December 2000 18:12, Sandeep Joshi wrote: > It results in following error: (just "shared") > > ERROR: Load of file /var/lib/pgsql/trigger/db1trig.so failed: > libpq.so.2.1: cannot open shared object file: No such file or directory > > I tried following link line (which specifies "run-time" linking path). > ld -shared -o db1trig.so db1trig.o -R/work/posgresql/lib -lpq > > This results in following error when "PQsetdb" is called: > > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > > > Sandeep > > > * Sandeep Joshi <sjoshi@Zambeel.com> [001215 14:54] wrote: > > > ld -shared -o db1trig.so db1trig.o -L/work/posgresql/lib -static -lpq > > > > > > Using above link line I was able to link and load the library but I get > > > following error > > > ERROR: conneciton bad. > > > > I think using both -shared and -static is an error, you might try > > linking to /work/posgresql/lib/libpq.a or try without as well. -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------
-
Re: question
Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-16T17:10:41Z
"Robert B. Easter" <reaster@comptechnews.com> writes: > If you or anyone reading this can put together a working C trigger example > with a test table and compile and trigger load procedure, I'd like to have it > to play with! I've been trying to use the examples in the docs and the ones > in contrib/spi, but with no luck. I'm using 7.0.3. I notice that the delev > docs have a different C trigger example than the 7.0 docs. The C-level trigger interface changed for 7.1 (as part of the big function manager rewrite). The info at http://www.postgresql.org/devel-corner/docs/postgres/triggers.htm is correct for 7.1 but not for earlier versions. The critical difference is that the TriggerData parameter structure is now passed in the 'fcinfo->context' optional argument, not via a global variable. This means you *must* use the new-style function interface, else you'll have no way to get at TriggerData. The user docs aren't yet updated for the new fmgr (mea culpa, hope to do it soon) but you can read about it in src/backend/utils/fmgr/README. I believe that the current docs examples and the current-sources contrib triggers are OK, but I can't claim to have tested them carefully. The contrib/spi/refint.c and contrib/spi/autoinc.c examples definitely work, because they are tested by the regression tests. Bit-rot could have set in elsewhere --- if you find problems please send details. > I don't know what > the differences are, one returns a HeapTuple and the other a Datum (a > unsigned long)? Same thing, different declaration. You're supposed to explicitly cast the HeapTuple (a pointer) to Datum with PointerGetDatum(). > Anyhow, some more detailed, up-to-date, docs on some of this would be nice. If you don't like the docs we have, improvements are gladly accepted... regards, tom lane
-
Re: question
Sandeep Joshi <sjoshi@zambeel.com> — 2000-12-16T21:43:45Z
Sure, I will mail the example to you and anyone else IFF I get it working ! I have already received couple of mails of this sort. Also, example in doc needs to make "PQconnectdb()" call rather than "PQsetdb()" if the earlier call is better call. thanks and regards, Sandeep > If you or anyone reading this can put together a working C trigger example > with a test table and compile and trigger load procedure, I'd like to have it > to play with! I've been trying to use the examples in the docs and the ones > in contrib/spi, but with no luck. I'm using 7.0.3. I notice that the delev > docs have a different C trigger example than the 7.0 docs. I don't know what > the differences are, one returns a HeapTuple and the other a Datum (a > unsigned long)? > > Anyhow, some more detailed, up-to-date, docs on some of this would be nice. > If I can learn these structs etc for C triggers, I'll try to write some HTML > page about it how it all works in detail. > > On Friday 15 December 2000 18:12, Sandeep Joshi wrote: > > It results in following error: (just "shared") > > > > ERROR: Load of file /var/lib/pgsql/trigger/db1trig.so failed: > > libpq.so.2.1: cannot open shared object file: No such file or directory > > > > I tried following link line (which specifies "run-time" linking path). > > ld -shared -o db1trig.so db1trig.o -R/work/posgresql/lib -lpq > > > > This results in following error when "PQsetdb" is called: > > > > pqReadData() -- backend closed the channel unexpectedly. > > This probably means the backend terminated abnormally > > before or while processing the request. > > The connection to the server was lost. Attempting reset: Failed. > > > > > > Sandeep > > > > > * Sandeep Joshi <sjoshi@Zambeel.com> [001215 14:54] wrote: > > > > ld -shared -o db1trig.so db1trig.o -L/work/posgresql/lib -static -lpq > > > > > > > > Using above link line I was able to link and load the library but I get > > > > following error > > > > ERROR: conneciton bad. > > > > > > I think using both -shared and -static is an error, you might try > > > linking to /work/posgresql/lib/libpq.a or try without as well. > > -- > -------- Robert B. Easter reaster@comptechnews.com --------- > - CompTechNews Message Board http://www.comptechnews.com/ - > - CompTechServ Tech Services http://www.comptechserv.com/ - > ---------- http://www.comptechnews.com/~reaster/ ------------
-
Re: question
Robert B. Easter <reaster@comptechnews.com> — 2000-12-17T01:30:15Z
I have no idea what my problems were with getting the autoinc example to work before. I took a break today and came back later and tried it with no problems. (this was with PostgreSQL 7.0.3) mkdir autoinc cd autoinc cp /usr/src/postgresql-7.0.3/contrib/spi/autoinc.* . Created a simple Makefile: autoinc.so: autoinc.c gcc -shared -I/usr/local/pgsql/include -I/usr/src/postgresql-7.0.3/src/include autoinc.c -o autoinc.so clean: rm -f *.so Edited autoinc.source and changed path to the so file as: /home/reaster/prog/triggers/autoinc/autoinc.so (where it compiled to). createdb tt psql tt \i autoinc.source \i autoinc.example It ran fine: tt=# \i autoinc.example DROP DROP CREATE CREATE CREATE INSERT 2834802 1 INSERT 2834803 1 INSERT 2834804 1 id | idesc ----+--------------- -2 | first (-2 ?) -1 | second (-1 ?) 1 | third (1 ?!) (3 rows) UPDATE 1 UPDATE 1 UPDATE 1 id | idesc ----+------------------ 2 | first: -2 --> 2 3 | second: -1 --> 3 4 | third: 1 --> 4 (3 rows) nextval | value ---------------+------- Wasn't it 4 ? | 4 (1 row) INSERT 0 3 id | idesc ----+------------------------- 2 | first: -2 --> 2 3 | second: -1 --> 3 4 | third: 1 --> 4 5 | first: -2 --> 2. Copy. 6 | second: -1 --> 3. Copy. 7 | third: 1 --> 4. Copy. (6 rows) Sorry for any confusion. On Saturday 16 December 2000 16:43, Sandeep Joshi wrote: > Sure, I will mail the example to you and anyone else IFF I get it > working ! I have already received couple of mails of this sort. > > Also, example in doc needs to make "PQconnectdb()" call rather than > "PQsetdb()" if the earlier call is better call. > > thanks and regards, > > Sandeep > > > If you or anyone reading this can put together a working C trigger > > example with a test table and compile and trigger load procedure, I'd > > like to have it to play with! I've been trying to use the examples in > > the docs and the ones in contrib/spi, but with no luck. I'm using 7.0.3. > > I notice that the delev docs have a different C trigger example than the > > 7.0 docs. I don't know what the differences are, one returns a HeapTuple > > and the other a Datum (a unsigned long)? > > > > Anyhow, some more detailed, up-to-date, docs on some of this would be > > nice. If I can learn these structs etc for C triggers, I'll try to write > > some HTML page about it how it all works in detail. > > > > On Friday 15 December 2000 18:12, Sandeep Joshi wrote: > > > It results in following error: (just "shared") > > > > > > ERROR: Load of file /var/lib/pgsql/trigger/db1trig.so failed: > > > libpq.so.2.1: cannot open shared object file: No such file or directory > > > > > > I tried following link line (which specifies "run-time" linking path). > > > ld -shared -o db1trig.so db1trig.o -R/work/posgresql/lib -lpq > > > > > > This results in following error when "PQsetdb" is called: > > > > > > pqReadData() -- backend closed the channel unexpectedly. > > > This probably means the backend terminated abnormally > > > before or while processing the request. > > > The connection to the server was lost. Attempting reset: Failed. > > > > > > > > > Sandeep > > > > > > > * Sandeep Joshi <sjoshi@Zambeel.com> [001215 14:54] wrote: > > > > > ld -shared -o db1trig.so db1trig.o -L/work/posgresql/lib -static > > > > > -lpq > > > > > > > > > > Using above link line I was able to link and load the library but I > > > > > get following error > > > > > ERROR: conneciton bad. > > > > > > > > I think using both -shared and -static is an error, you might try > > > > linking to /work/posgresql/lib/libpq.a or try without as well. > > > > -- > > -------- Robert B. Easter reaster@comptechnews.com --------- > > - CompTechNews Message Board http://www.comptechnews.com/ - > > - CompTechServ Tech Services http://www.comptechserv.com/ - > > ---------- http://www.comptechnews.com/~reaster/ ------------ -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------ -
Re: question
Sandeep Joshi <sjoshi@zambeel.com> — 2000-12-18T20:56:42Z
> Hi, I reduced my trigger to do following : (Trigger gets invoked for Table1 in Db1) 1. conn=PQconnectdb ( db2 ) 2. PQexec(conn,"begin") <--- FAILS 3. PQfinish(conn) I get failure in step (2) with following message in 'postmaster.log' ERROR: begin pq_recvbuf: unexpected EOF on client connection I don't know what does this means. If anybody is interested I can mail the ".c" file. Is support for trigger better in 7.1 ? where do I get the 7.1? regards, sandeep -
Trigger question
Sandeep Joshi <sjoshi@zambeel.com> — 2000-12-19T00:12:32Z
Hi, When I start my postmaster with "-i" option I get following error while trying to connect to other databae. FATAL: StreamServerPort: bind() failed: Address already in use Is another postmaster already running on that port? If not, remove socket node (/tmp/.s.PGSQL.5432) and retry. /work/posgresql/bin/postmaster: cannot create UNIX stream port any hints, remarks? I understand what I am trying to do is POSSIBLE but has anybody really tried it? regards, Sandeep > > > Hi, > I reduced my trigger to do following : > > (Trigger gets invoked for Table1 in Db1) > > 1. conn=PQconnectdb ( db2 ) > 2. PQexec(conn,"begin") <--- FAILS > 3. PQfinish(conn) > > I get failure in step (2) with following message in 'postmaster.log' > > ERROR: begin > pq_recvbuf: unexpected EOF on client connection > > I don't know what does this means. If anybody is interested I can mail the ".c" > file. > > Is support for trigger better in 7.1 ? where do I get the 7.1? > > regards, > sandeep