From: Joe Conway <joe.conway@mail.com>
To: pgsql-interfaces@postgresql.org
Subject: libpq/c function question
Date: Sat, 27 Jan 2001 18:48:28 -0800
Message-ID: <01aa01c088d4$cae4f220$0705a8c0@jecw2k1> (raw)
References: <Pine.GSO.4.10.10101251120200.21455-100000@sun4.plasma.ernet.in>
Hi,
Apologies if this hits the list twice, but my first email (from yesterday)
seems have to gone to /dev/null instead of the list :(
Also, please be gentle, this is my first foray into C programming :)
Here's what I did/am trying to do:
- First, I created a C function based on the version 1 calling convention
from the 7.1 docs
(http://www.postgresql.org/devel-corner/docs/programmer/xfunc-c.htm). It is
intended to accept two text parameters -- the first is a connection string
for PQconnectdb, and the second is a SQL statement. The code is attached in
jec-repl.c
- Second, I registered the function in PostgeSQL using jec-repl.sql
(attached).
- Third, I created a pgplsql function (sync_data_test) to invoke the C
function (sync_data_test.sql)
- The overall idea was to do a simple form of host-to-host replication using
a trigger based on a pgplsql function similar to sysnc_data_test. I wanted
to keep the C function very specific to the task of connecting to an
arbitrary host and executing a SQL statement (not returning tuples, of
course) and leave most of the replication logic to the pgplsql function.
The problem is that the C function fails to connect consistantly. I found
through trial and error that if I pad the connection string with several
spaces (the exact number seems to constant within one psql session, but
varies from session to session) it is able to connect and execute my insert
statement successfully. Below I'll include some sample output of a psql
session. I'm running RedHat Linux 6.2, and the 7.1 beta 3 tarball.
Any help or guidance is very much appreciated!
Joe Conway
p.s. Note - the function returns 2 on a failure to connect (PQstatus(conn)
== CONNECTION_BAD) and 0 on successful execution of the insert statement.
jec1=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.1devel on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
jec1=# select sync_data_test('hostaddr=172.16.1.84 port=5432 dbname=jec2
user=postgres password=mypasswd',' insert into t1(f1) values(987)');
sync_data_test
----------------
2
(1 row)
jec1=# select sync_data_test(' hostaddr=172.16.1.84 port=5432 dbname=jec2
user=postgres password=mypasswd',' insert into t1(f1) values(987)');
sync_data_test
----------------
2
(1 row)
jec1=# select sync_data_test(' hostaddr=172.16.1.84 port=5432 dbname=jec2
user=postgres password=mypasswd',' insert into t1(f1) values(987)');
sync_data_test
----------------
2
(1 row)
jec1=# select sync_data_test(' hostaddr=172.16.1.84 port=5432 dbname=jec2
user=postgres password=mypasswd',' insert into t1(f1) values(987)');
sync_data_test
----------------
0
(1 row)
jec1=# select sync_data_test(' hostaddr=172.16.1.84 port=5432 dbname=jec2
user=postgres password=mypasswd',' insert into t1(f1) values(987)');
sync_data_test
----------------
0
(1 row)
jec1=# select sync_data_test('hostaddr=172.16.1.84 port=5432 dbname=jec2
user=postgres password=mypasswd'::text,' insert into t1(f1)
values(987)');
sync_data_test
----------------
2
(1 row)
Attachments:
sync_data_test.sql (application/octet-stream, 282B) download
jec-repl.sql (application/octet-stream, 183B) download
jec-repl.c (application/octet-stream, 891B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: joe.conway@mail.com
Subject: Re: libpq/c function question
In-Reply-To: <01aa01c088d4$cae4f220$0705a8c0@jecw2k1>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox