Skip site navigation (1) Skip section navigation (2)

Re: dblink and plpq

From: Darko Prenosil <darko(dot)prenosil(at)finteh(dot)hr>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: Re: dblink and plpq
Date: 2002-08-30 10:50:51
Message-ID: 200208300950.51428.darko.prenosil@finteh.hr (view raw or flat)
Thread:
Lists: pgsql-general
On Friday 30 August 2002 01:25, you wrote:
> Darko Prenosil wrote:
> > Hi Joe !
> >
> > I wrote some code to implement PQlib so it can be used in SQL functions.
> > With those functions pl/psql script becomes client of another SQL server.
> > In file attachment is plpq.tar.gz that should be copied into
> > /contrib/plpq. Add this dir in /contrib/Makefile  (WANTED_DIRS).
> >
> > The thing is that I did used some stuff from dblink, and I need your
> > opinion on it.
> > Maybe we can make one library that has all the functions ?
> >
> > Best regards !
>
> Hi Darko,
>
> I'm working on dblink right now, with a deadline of September 1 to get
> it finished. I'll try to take a look at your stuff and include some of
> it, but I doubt I'll have the time to include it all. Do you have a
> preference for one or two of the functions you wrote? We can always add
> more for the next release (i.e. 7.4) which hopefully won't be too long
> in the future.

There is no meaning to include PQexec wrapper function for example, and not 
include PQGetValue. We can skip only few functions (such as PQsetdbLogin) and
preserve functionality. If there is not enough time to do it now, then we 
should wait (IMHO). 
I just think that such interface should be interesting.
For example there is dbmirror replication engine in contrib directory,
and it is using perl script to actually replicate the data. I did it in 
similar way as dbmirror, but replication is done with
pl/pgsql. I think that with those wrapper functions even dbmirror perl script
can be ported to pl/pgsql.
There is my rsync function (just to get a picture what I was trying to do. It 
is riped out of the context but it is illustrative):

CREATE FUNCTION "rsync" () RETURNS integer AS '
DECLARE
        connString text;
        rsync_row RECORD; 
        connHandle int;
        cTmp text;
        resultHande int;
        nResStatus int;
BEGIN
	connString:=srv_conn_string(''reg_master'');
	connHandle:=PQconnectdb(connString);
	cTmp:=PQstatusStr(connHandle);
	IF  cTmp = ''CONNECTION_BAD'' THEN 
		--NOT CONNECTED - RAISE ERROR
		RAISE EXCEPTION ''rlog sync: Connection error %'',cTmp;
	ELSE
		--CONNECTED
		RAISE NOTICE ''rlog sync: CONNECT (%) - status[%]'',connString,cTmp;
	END IF;
	RAISE NOTICE ''rlog sync: BEGIN .... '';
	--Start transaction on host server
	resultHande:=PQExec(connHandle,''BEGIN;'');
	nResStatus=PQresultStatus (resultHande);
	IF nResStatus != 1 THEN
		PERFORM PQfinish(connHandle);
		RAISE EXCEPTION ''rlog sync: Execution status % on BEGIN 
TRANSACTION'',nResStatus;
	END IF;
	--Loop through unsync records and send them to host server
	FOR rsync_row IN SELECT oid,* from rlog_data WHERE rsync_time IS NULL LOOP
		resultHande:=PQExec(connHandle,rsync_row.query_src);
		nResStatus=PQresultStatus (resultHande);
		IF nResStatus != 1 THEN
			cTmp:=PQResultErrorMessage(resultHande);
			PERFORM PQfinish(connHandle);
			RAISE EXCEPTION ''rlog sync: Execution of SQL statement % finished with 
error: %'',rsync_row.query_src,cTmp;
		END IF;
		UPDATE rlog_data SET rsync_time = current_timestamp WHERE oid=rsync_row.oid;
		RAISE NOTICE ''rlog sync:	%'',rsync_row.query_src;
    END LOOP; 
	resultHande:=PQExec(connHandle,''END;'');
	nResStatus=PQresultStatus (resultHande);
	IF nResStatus != 1 THEN
		PERFORM PQfinish(connHandle);
		RAISE EXCEPTION ''rlog sync: Execution status % on END 
TRANSACTION'',nResStatus;
	END IF;
	PERFORM PQfinish(connHandle);
	RAISE NOTICE ''rlog sync: END .... '';
	RETURN 1;
END;' LANGUAGE 'plpgsql';




pgsql-general by date

Next:From: Ericson SmithDate: 2002-08-30 12:33:08
Subject: Max Shared Memory
Previous:From: jeromeDate: 2002-08-30 09:21:55
Subject: postmaster respawn....?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group