Thread: question
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
* 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."
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."
#include <stdio.h>
#include "libpq-fe.h"
#include "executor/spi.h" /* this is what you need to work with spi */
#include "commands/trigger.h" /* -"- and triggers */
HeapTuple insert_into_db2(void);
HeapTuple
insert_into_db2(void)
{
Trigger *trigger; /* to get trigger name */
int nargs,i,j; /* # of arguments */
Datum newval; /* new value of column */
char **args; /* arguments */
char *relname; /* triggered relation name */
Relation rel; /* triggered relation */
HeapTuple rettuple = NULL;
TupleDesc tupdesc; /* tuple description */
int attnum,natts;
Datum *cvals; /* column values */
char *cnulls; /* column nulls */
bool isnull;
char *fieldval, *fieldtype;
char *pghost, *pgport, *pgoptions, *pgtty, *dbName;
PGconn *conn;
PGresult *res;
/* sanity checks from autoinc.c */
if (!CurrentTriggerData)
elog(ERROR, "insert_username: triggers are not initialized");
if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event))
elog(ERROR, "insert_username: can't process STATEMENT events");
if (TRIGGER_FIRED_AFTER(CurrentTriggerData->tg_event))
elog(ERROR, "insert_username: must be fired before event");
if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event))
rettuple = CurrentTriggerData->tg_trigtuple;
else if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event))
rettuple = CurrentTriggerData->tg_newtuple;
else
elog(ERROR, "insert_username: can't process DELETE events");
rel = CurrentTriggerData->tg_relation;
relname = SPI_getrelname(rel);
trigger = CurrentTriggerData->tg_trigger;
nargs = trigger->tgnargs;
if (nargs != 3)
elog(ERROR, "insert_username (%s): one argument was expected", relname);
args = trigger->tgargs;
tupdesc = rel->rd_att;
natts = tupdesc->natts;
CurrentTriggerData = NULL;
attnum = SPI_fnumber(tupdesc, args[0]);
/* Fetch tuple values and nulls
cvals = (Datum *) palloc(natts * sizeof(Datum));
cnulls = (char *) palloc(natts * sizeof(char));
for (i = 0; i < natts; i++)
{
cvals[i] = SPI_getbinval(rettuple,tupdesc, i + 1, &isnull);
cnulls[i] = (isnull) ? 'n' : ' ';
} */
elog(NOTICE,"values %s. %s\n", SPI_fname(tupdesc, 1), SPI_getvalue(rettuple, tupdesc, 1));
elog(NOTICE,"values %s. %s\n", SPI_fname(tupdesc, 2), SPI_getvalue(rettuple, tupdesc, 2));
elog(NOTICE,"values %s. %s\n", SPI_fname(tupdesc, 3), SPI_getvalue(rettuple, tupdesc, 3));
pghost = NULL; /* host name of the backend server */
pgport = NULL; /* port of the backend server */
pgoptions = NULL; /* special options to start up the backend
* server */
pgtty = NULL; /* debugging tty for the backend server */
dbName = "db2";
/* make a connection to the database */
conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
/* check to see that the backend connection was successfully made */
if (PQstatus(conn) == CONNECTION_BAD)
{
elog(ERROR,"conneciton bad.\n");
}
res = PQexec(conn, "BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
elog(ERROR,"conneciton bad.\n");
PQclear(res);
}
PQclear(res);
res = PQexec(conn, "insert into db2user values (3,'test123','n05aWAjCn2cqo'); ");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
elog(ERROR,"PQexec bad.\n");
}
PQclear(res);
res = PQexec(conn, "END");
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
pfree(relname);
return (rettuple);
}
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)
* 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.
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.
* 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."
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
* 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."
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> 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
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/ ------------
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/ ------------
>
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
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