libpq/c function question - Mailing list pgsql-interfaces

From Joe Conway
Subject libpq/c function question
Date
Msg-id 01aa01c088d4$cae4f220$0705a8c0@jecw2k1
Whole thread Raw
In response to Re: [ANNOUNCE] Gedafe (the Generic Database Front-End) 1.0.0  (Manika Dey <bithi@ipr.res.in>)
Responses Re: libpq/c function question
List 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,
compiledby 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)


pgsql-interfaces by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: [HACKERS] PgAccess - small bug?
Next
From: Tom Lane
Date:
Subject: Re: libpq/c function question