Thread: question

question

From
Sandeep Joshi
Date:
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

From
Alfred Perlstein
Date:
* 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

From
Sandeep Joshi
Date:
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);

}

Re: question

From
Sandeep Joshi
Date:
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

From
Alfred Perlstein
Date:
* 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

From
Sandeep Joshi
Date:
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

From
Alfred Perlstein
Date:
* 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

From
Tom Lane
Date:
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

From
Alfred Perlstein
Date:
* 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

From
"Robert B. Easter"
Date:
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

From
Tom Lane
Date:
"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

From
Sandeep Joshi
Date:
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

From
"Robert B. Easter"
Date:
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

From
Sandeep Joshi
Date:
>

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

From
Sandeep Joshi
Date:
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