Message-ID: <01aa01c088d4$cae4f220$0705a8c0@jecw2k1>
Reply-To: "Joe Conway" <joe.conway@mail.com>
From: "Joe Conway" <joe.conway@mail.com>
To: <pgsql-interfaces@postgresql.org>
References: <Pine.GSO.4.10.10101251120200.21455-100000@sun4.plasma.ernet.in>
Subject: libpq/c function question
Date: Sat, 27 Jan 2001 18:48:28 -0800
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_NextPart_000_01A7_01C08891.BC87B660"

This is a multi-part message in MIME format.

------=_NextPart_000_01A7_01C08891.BC87B660
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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)


------=_NextPart_000_01A7_01C08891.BC87B660
Content-Type: application/octet-stream; name="sync_data_test.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="sync_data_test.sql"

drop function sync_data_test(text,text);
create function sync_data_test(text,text) returns int4 as '
	DECLARE
		pgoptstr		ALIAS FOR $1;
		pgsqlstr		ALIAS FOR $2; 
		retval			int4;
	BEGIN
		select sync_data(pgoptstr,pgsqlstr) into retval;
		return retval; 
	END;'
language 'plpgsql';

------=_NextPart_000_01A7_01C08891.BC87B660
Content-Type: application/octet-stream; name="jec-repl.sql"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment; filename="jec-repl.sql"

DROP FUNCTION sync_data (text,text);
CREATE FUNCTION sync_data (text,text) RETURNS int4
  AS '/opt/src/postgresql-snapshot/contrib/earthdistance/jec-repl.so','sync=
_data' LANGUAGE 'C';


------=_NextPart_000_01A7_01C08891.BC87B660
Content-Type: application/octet-stream; name="jec-repl.c"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="jec-repl.c"

#include "postgres.h"
#include "libpq-fe.h"
#include "fmgr.h"

Datum sync_data(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(sync_data);
Datum
sync_data(PG_FUNCTION_ARGS)
{
	text  		*optstr;
	text  		*jpgsql;
	PGconn		*conn;
	PGresult	*res;

	if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
	{
		PG_RETURN_INT32(1);
	}

	optstr = PG_GETARG_TEXT_P(0);
	jpgsql = PG_GETARG_TEXT_P(1);

	conn = PQconnectdb(VARDATA(optstr));
	if (PQstatus(conn) == CONNECTION_BAD)
	{
		PQfinish(conn);
		PG_RETURN_INT32(2);
	}

	res = PQexec(conn, "BEGIN");
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		PQclear(res);
		PQfinish(conn);
		PG_RETURN_INT32(3);
	}
	PQclear(res);

	res = PQexec(conn, VARDATA(jpgsql));
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		PQclear(res);
		PQfinish(conn);
		PG_RETURN_INT32(4);
	}
	PQclear(res);

	res = PQexec(conn, "END");
	PQclear(res);
	PQfinish(conn);
	PG_RETURN_INT32(0);
}

------=_NextPart_000_01A7_01C08891.BC87B660--