libpq/c function question

From: "Joe Conway" <joe(dot)conway(at)mail(dot)com>
To: <pgsql-interfaces(at)postgresql(dot)org>
Subject: libpq/c function question
Date: 2001-01-28 02:48:28
Message-ID: 01aa01c088d4$cae4f220$0705a8c0@jecw2k1
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces

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)

Attachment Content-Type Size
sync_data_test.sql application/octet-stream 283 bytes
jec-repl.sql application/octet-stream 185 bytes
jec-repl.c application/octet-stream 892 bytes

In response to

Responses

Browse pgsql-interfaces by date

  From Date Subject
Next Message Tom Lane 2001-01-28 03:48:34 Re: libpq/c function question
Previous Message Bruce Momjian 2001-01-27 18:46:43 Re: PgAccess - small bug?