question

* 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

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

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

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