Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f55J48E49907 for ; Tue, 5 Jun 2001 15:04:08 -0400 (EDT) (envelope-from pgsql-hackers-owner@postgresql.org) Received: from femail13.sdc1.sfba.home.com (femail13.sdc1.sfba.home.com [24.0.95.140]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4V4xsE23591 for ; Thu, 31 May 2001 00:59:54 -0400 (EDT) (envelope-from joe.conway@mail.com) Received: from jecw2k1 ([24.0.42.111]) by femail13.sdc1.sfba.home.com (InterMail vM.4.01.03.20 201-229-121-120-20010223) with SMTP id <20010531045952.ERRO1515.femail13.sdc1.sfba.home.com@jecw2k1> for ; Wed, 30 May 2001 21:59:52 -0700 Message-ID: <009101c0e98e$872bdb00$0205a8c0@jecw2k1> From: "Joe Conway" To: References: <002901c0e733$5e683590$0705a8c0@jecw2k1> <15650.991084313@sss.pgh.pa.us> Subject: remote database queries Date: Wed, 30 May 2001 21:59:52 -0700 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_008E_01C0E953.DA9F3C40" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200106/90 X-Sequence-Number: 9663 This is a multi-part message in MIME format. ------=_NextPart_000_008E_01C0E953.DA9F3C40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit > Until we fix that (maybe for 7.2, maybe not) your existing hack is > probably pretty reasonable. You could save some cycles by avoiding > conversion to text, though --- instead return an opaque datum that is > pointer-to-tuple-slot and let the dblink_tok function extract fields > from the tuple. Look at SQL function support and the FieldSelect > expression node type for inspiration. > I changed the dblink() function to return a pointer instead of concatenated text, and dblink_tok() to use the pointer. FWIW, a query on a small (85 tuples) remote (a second PC on a 100baseT subnet) table takes about 34 milliseconds (based on show_query_stats) versus about 4 milliseconds when run locally. It actually takes a bit longer (~65 milliseconds) when run against a second database on the same PC. The original text parsing version was about 25% slower. Although shifting from text parsing to pointer passing is more efficient, I have one more question regarding this -- for now ;) -- is there any way to check the pointer passed to dblink_tok() to be sure it came from dblink()? Thanks, -- Joe ------=_NextPart_000_008E_01C0E953.DA9F3C40 Content-Type: application/octet-stream; name="dblink.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="dblink.patch" diff -Naur pgsql.virg/contrib/dblink/Makefile pgsql/contrib/dblink/Makefile --- pgsql.virg/contrib/dblink/Makefile Thu Jan 1 00:00:00 1970 +++ pgsql/contrib/dblink/Makefile Thu May 31 04:15:26 2001 @@ -0,0 +1,48 @@ +subdir =3D contrib/dblink +top_builddir =3D ../.. +include $(top_builddir)/src/Makefile.global +include $(top_builddir)/src/Makefile.shlib +include_srcdir =3D $(top_builddir)/src/include + + +NAME :=3D dblink +SONAME :=3D $(NAME)$(DLSUFFIX) + +override CFLAGS +=3D -I$(srcdir) +override CFLAGS +=3D -I$(include_srcdir) +override CFLAGS +=3D -I$(libpq_srcdir) +override CFLAGS +=3D $(CFLAGS_SL) + +OBJS =3D $(NAME).o + +all: $(OBJS) $(SONAME) $(NAME).sql=20 + +$(OBJS): $(NAME).c + $(CC) -o $@ -c $(CFLAGS) $< + +$(SONAME): $(OBJS) + $(LD) -o $@ -Bshareable $(libpq) $< + +$(NAME).sql: $(NAME).sql.in + sed -e 's:MODULE_PATHNAME:$(libdir)/contrib/$(SONAME):g' < $< > $@ + +install: all installdirs + $(INSTALL_DATA) README.$(NAME) $(docdir)/contrib + $(INSTALL_DATA) $(NAME).sql $(datadir)/contrib + $(INSTALL_SHLIB) $(SONAME) $(libdir)/contrib + +installdirs: + $(mkinstalldirs) $(docdir)/contrib $(datadir)/contrib $(libdir)/contrib + +uninstall: + rm -rf $(docdir)/contrib/README.$(NAME) $(datadir)/contrib/$(NAME).sql $(= libdir)/contrib/$(SONAME) + +clean distclean maintainer-clean: + rm -f $(SONAME) *.o *.sql + +depend dep: + $(CC) -MM -MG $(CFLAGS) *.c > depend + +ifeq (depend,$(wildcard depend)) +include depend +endif diff -Naur pgsql.virg/contrib/dblink/README.dblink pgsql/contrib/dblink/REA= DME.dblink --- pgsql.virg/contrib/dblink/README.dblink Thu Jan 1 00:00:00 1970 +++ pgsql/contrib/dblink/README.dblink Wed May 30 06:03:21 2001 @@ -0,0 +1,157 @@ +/* + * dblink + * + * Functions returning results from a remote database + * + * Copyright (c) Joseph Conway , 2001; + *=20 + * Permission to use, copy, modify, and distribute this software and its + * documentation for any purpose, without fee, and without a written agree= ment + * is hereby granted, provided that the above copyright notice and this + * paragraph and the following two paragraphs appear in all copies. + *=20 + * IN NO EVENT SHALL THE AUTHOR OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUD= ING + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF = THE + * POSSIBILITY OF SUCH DAMAGE. + *=20 + * THE AUTHOR AND DISTRIBUTORS SPECIFICALLY DISCLAIMS ANY WARRANTIES, + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER = IS + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS= TO + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. + * + */ + +Version 0.2 (29 May, 2001): + Function to test returning data set from remote database + Tested under Linux (Red Hat 6.2 and 7.0) and PostgreSQL 7.1 and 7.2devel + +Release Notes: + + Version 0.2 + - initial release=20=20=20=20 + +Installation: + Place these files in a directory called 'dblink' under 'contrib' in the = PostgreSQL source tree. Then run: + + make + make install + + You can use dblink.sql to create the functions in your database of choic= e, e.g. + + psql -U postgres template1 < dblink.sql + + installs following functions into database template1: + + dblink() - returns a pointer to results from remote query + dblink_tok() - extracts and returns individual field results + +Documentation +=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D +Name + +dblink -- Returns a pointer to a data set from a remote database + +Synopsis + +dblink(text connstr, text sql) + +Inputs + + connstr + + standard libpq format connection srting,=20 + e.g. "hostaddr=3D127.0.0.1 port=3D5432 dbname=3Dmydb user=3Dpostgres p= assword=3Dmypasswd" + + sql + + sql statement that you wish to execute on the remote host + e.g. "select * from pg_class" + +Outputs + + Returns setof int (pointer) + +Example usage + + select dblink('hostaddr=3D127.0.0.1 port=3D5432 dbname=3Dmydb user=3Dpos= tgres password=3Dmypasswd','select f1, f2 from mytable'); + + +=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D + +Name + +dblink_tok -- Returns individual select field results from a dblink remote= query + +Synopsis + +dblink_tok(int pointer, int fnumber) + +Inputs + + pointer + + a pointer returned by a call to dblink() + + fnumber + + the ordinal position (zero based) of the field to be returned from the= dblink result set + +Outputs + + Returns text + +Example usage + + select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2 + from (select dblink('hostaddr=3D127.0.0.1 port=3D5432 dbname=3Dmydb user= =3Dpostgres password=3Dmypasswd' + ,'select f1, f2 from mytable') as dblink_p) as t1; + + +=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D + +NOTE: If you need to provide selection criteria in a WHERE clause, it is n= ecessary +to 'fake' a UNION, e.g. + + select + dblink_tok(t1.dblink_p,0) as f1 + ,dblink_tok(t1.dblink_p,1) as f2 + from + ( + select dblink( + 'hostaddr=3D127.0.0.1 port=3D5432 dbname=3Dmydb user=3D= postgres password=3Dmypasswd' + ,'select f1, f2 from mytable' + ) as dblink_p + union + select null,null where false + ) as t1 + where + f1 =3D 'mycriteria'; + +in order to work around an issue with the query optimizer. A more convenie= nt way to approach +this problem is to create a view: + + create view myremotetable as + select + dblink_tok(t1.dblink_p,0) as f1 + ,dblink_tok(t1.dblink_p,1) as f2 + from + ( + select dblink( + 'hostaddr=3D127.0.0.1 port=3D5432 dbname=3Dmydb user=3D= postgres password=3Dmypasswd' + ,'select f1, f2 from mytable' + ) as dblink_p + union + select null,null where false + ) as t1; + +Then you can simply write: + + select f1,f2 from myremotetable where f1 =3D 'mycriteria'; + +=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D + +-- Joe Conway + diff -Naur pgsql.virg/contrib/dblink/dblink.c pgsql/contrib/dblink/dblink.c --- pgsql.virg/contrib/dblink/dblink.c Thu Jan 1 00:00:00 1970 +++ pgsql/contrib/dblink/dblink.c Wed May 30 05:13:23 2001 @@ -0,0 +1,276 @@ +/* + * dblink.c + * + * Functions returning results from a remote database + * + * Copyright (c) Joseph Conway , 2001; + *=20 + * Permission to use, copy, modify, and distribute this software and its + * documentation for any purpose, without fee, and without a written agree= ment + * is hereby granted, provided that the above copyright notice and this + * paragraph and the following two paragraphs appear in all copies. + *=20 + * IN NO EVENT SHALL THE AUTHOR OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUD= ING + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF = THE + * POSSIBILITY OF SUCH DAMAGE. + *=20 + * THE AUTHOR AND DISTRIBUTORS SPECIFICALLY DISCLAIMS ANY WARRANTIES, + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER = IS + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS= TO + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. + * + */ + +#include "dblink.h" + +PG_FUNCTION_INFO_V1(dblink); +Datum +dblink(PG_FUNCTION_ARGS) +{ + PGconn *conn =3D NULL; + PGresult *res =3D NULL; + dblink_results *results; + char *optstr; + char *sqlstatement; + char *curstr =3D "DECLARE mycursor CURSOR FOR "; + char *execstatement; + char *msg; + int ntuples =3D 0; + ReturnSetInfo *rsi; + + if (PG_ARGISNULL(0) || PG_ARGISNULL(1)) { + elog(ERROR, "dblink: NULL arguments are not permitted"); + } + + if (fcinfo->resultinfo =3D=3D NULL || ! IsA(fcinfo->resultinfo, ReturnSet= Info)) { + elog(ERROR, "dblink: function called in context that does not accept a s= et result"); + } + + optstr =3D DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(P= G_GETARG_TEXT_P(0)))); + sqlstatement =3D DatumGetCString(DirectFunctionCall1(textout, PointerGetD= atum(PG_GETARG_TEXT_P(1)))); + + if (fcinfo->flinfo->fn_extra =3D=3D NULL) { + + conn =3D PQconnectdb(optstr); + if (PQstatus(conn) =3D=3D CONNECTION_BAD) + { + msg =3D PQerrorMessage(conn); + PQfinish(conn); + elog(ERROR, "dblink: connection error: %s", msg); + } + + res =3D PQexec(conn, "BEGIN"); + if (PQresultStatus(res) !=3D PGRES_COMMAND_OK) + { + msg =3D PQerrorMessage(conn); + PQclear(res); + PQfinish(conn); + elog(ERROR, "dblink: begin error: %s", msg); + } + PQclear(res); + + execstatement =3D (char *) palloc(strlen(curstr) + strlen(sqlstatement) = + 1); + if (execstatement !=3D NULL) { + strcpy(execstatement, curstr); + strcat(execstatement, sqlstatement); + strcat(execstatement, "\0"); + } else { + elog(ERROR, "dblink: insufficient memory" ); + } + + res =3D PQexec(conn, execstatement); + if (!res || (PQresultStatus(res) !=3D PGRES_COMMAND_OK && PQresultStatus= (res) !=3D PGRES_TUPLES_OK)) + { + msg =3D PQerrorMessage(conn); + PQclear(res); + PQfinish(conn); + elog(ERROR, "dblink: sql error: %s", msg); + } else { + /* + * got results, start fetching them + */ + PQclear(res); + + res =3D PQexec(conn, "FETCH ALL in mycursor"); + if (!res || PQresultStatus(res) !=3D PGRES_TUPLES_OK) { + msg =3D PQerrorMessage(conn); + PQclear(res); + PQfinish(conn); + elog(ERROR, "dblink: sql error: %s", msg); + } + + ntuples =3D PQntuples(res); + + if (ntuples > 0) { + + results =3D init_dblink_results(fcinfo->flinfo->fn_mcxt); + results->tup_num =3D 0; + results->res =3D res; + res =3D NULL; + + (dblink_results *) fcinfo->flinfo->fn_extra =3D results; + + results =3D NULL; + results =3D fcinfo->flinfo->fn_extra; + + /* close the cursor */ + res =3D PQexec(conn, "CLOSE mycursor"); + PQclear(res); + + /* commit the transaction */ + res =3D PQexec(conn, "COMMIT"); + PQclear(res); + + /* close the connection to the database and cleanup */ + PQfinish(conn); + + rsi =3D (ReturnSetInfo *)fcinfo->resultinfo; + rsi->isDone =3D ExprMultipleResult; + + PG_RETURN_POINTER(results); + + } else { + + PQclear(res); + + /* close the cursor */ + res =3D PQexec(conn, "CLOSE mycursor"); + PQclear(res); + + /* commit the transaction */ + res =3D PQexec(conn, "COMMIT"); + PQclear(res); + + /* close the connection to the database and cleanup */ + PQfinish(conn); + + rsi =3D (ReturnSetInfo *)fcinfo->resultinfo; + rsi->isDone =3D ExprEndResult ; + + PG_RETURN_NULL(); + } + } + } else { + /* + * check for more results + */ + + results =3D fcinfo->flinfo->fn_extra; + results->tup_num++; + ntuples =3D PQntuples(results->res); + + if (results->tup_num < ntuples) { + + /* + * fetch them if available + */ + + rsi =3D (ReturnSetInfo *)fcinfo->resultinfo; + rsi->isDone =3D ExprMultipleResult; + + PG_RETURN_POINTER(results); + + } else { + + /* + * or if no more, clean things up + */ + + results =3D fcinfo->flinfo->fn_extra; + + PQclear(results->res); + + rsi =3D (ReturnSetInfo *)fcinfo->resultinfo; + rsi->isDone =3D ExprEndResult ; + + PG_RETURN_NULL(); + } + } + PG_RETURN_NULL(); +} + + +/* + * dblink_tok + * parse dblink output string + * return fldnum item (0 based) + * based on provided field separator + */ + +PG_FUNCTION_INFO_V1(dblink_tok); +Datum +dblink_tok(PG_FUNCTION_ARGS) +{ + dblink_results *results; + int fldnum; + text *result_text; + char *result; + int nfields =3D 0; + int text_len =3D 0; + + if (PG_ARGISNULL(0) || PG_ARGISNULL(1)) { + elog(ERROR, "dblink: NULL arguments are not permitted"); + } + + results =3D (dblink_results *) PG_GETARG_POINTER(0); + if (results =3D=3D NULL) { + elog(ERROR, "dblink: function called with invalid result pointer"); + } + + fldnum =3D PG_GETARG_INT32(1); + if (fldnum < 0) { + elog(ERROR, "dblink: field number < 0 not permitted"); + } + + nfields =3D PQnfields(results->res); + if (fldnum > (nfields - 1)) { + elog(ERROR, "dblink: field number %d does not exist", fldnum); + } + + text_len =3D PQgetlength(results->res, results->tup_num, fldnum); + + result =3D (char *) palloc(text_len + 1); + + if (result !=3D NULL) { + strcpy(result, PQgetvalue(results->res, results->tup_num, fldnum)); + strcat(result, "\0"); + } else { + elog(ERROR, "dblink: insufficient memory" ); + } + + result_text =3D DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum= (result))); + + PG_RETURN_TEXT_P(result_text); +} + + +/* + * internal functions + */ + + +/* + * init_dblink_results + * - create an empty dblink_results data structure + */ +dblink_results * +init_dblink_results(MemoryContext fn_mcxt) +{ + MemoryContext oldcontext; + dblink_results *retval; + + oldcontext =3D MemoryContextSwitchTo(fn_mcxt); + + retval =3D (dblink_results *) palloc(sizeof(dblink_results)); + MemSet(retval, 0, sizeof(dblink_results)); + + retval->tup_num =3D -1; + retval->res =3D NULL; + + MemoryContextSwitchTo(oldcontext); + + return retval; +} diff -Naur pgsql.virg/contrib/dblink/dblink.h pgsql/contrib/dblink/dblink.h --- pgsql.virg/contrib/dblink/dblink.h Thu Jan 1 00:00:00 1970 +++ pgsql/contrib/dblink/dblink.h Thu May 31 04:15:28 2001 @@ -0,0 +1,70 @@ +/* + * dblink.h + * + * Functions returning results from a remote database + * + * Copyright (c) Joseph Conway , 2001; + *=20 + * Permission to use, copy, modify, and distribute this software and its + * documentation for any purpose, without fee, and without a written agree= ment + * is hereby granted, provided that the above copyright notice and this + * paragraph and the following two paragraphs appear in all copies. + *=20 + * IN NO EVENT SHALL THE AUTHOR OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR + * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUD= ING + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS + * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF = THE + * POSSIBILITY OF SUCH DAMAGE. + *=20 + * THE AUTHOR AND DISTRIBUTORS SPECIFICALLY DISCLAIMS ANY WARRANTIES, + * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY + * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER = IS + * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS= TO + * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. + * + */ + +#ifndef DBLINK_H +#define DBLINK_H + +#include +#include "postgres.h" +#include "libpq-fe.h" +#include "libpq-int.h" +#include "fmgr.h" +#include "access/tupdesc.h" +#include "executor/executor.h" +#include "nodes/nodes.h" +#include "nodes/execnodes.h" +#include "utils/builtins.h" + +/* + * This struct holds the results of the remote query. + * Use fn_extra to hold a pointer to it across calls + */ +typedef struct +{ + /* + * last tuple number accessed + */ + int tup_num; + + /* + * the actual query results + */ + PGresult *res; + +} dblink_results; + +/* + * External declarations + */ +extern Datum dblink(PG_FUNCTION_ARGS); +extern Datum dblink_tok(PG_FUNCTION_ARGS); + +/* + * Internal declarations + */ +dblink_results *init_dblink_results(MemoryContext fn_mcxt); + +#endif /* DBLINK_H */ diff -Naur pgsql.virg/contrib/dblink/dblink.sql.in pgsql/contrib/dblink/dbl= ink.sql.in --- pgsql.virg/contrib/dblink/dblink.sql.in Thu Jan 1 00:00:00 1970 +++ pgsql/contrib/dblink/dblink.sql.in Wed May 30 04:49:32 2001 @@ -0,0 +1,5 @@ +CREATE FUNCTION dblink (text,text) RETURNS setof int + AS 'MODULE_PATHNAME','dblink' LANGUAGE 'c'; + +CREATE FUNCTION dblink_tok (int,int) RETURNS text + AS 'MODULE_PATHNAME','dblink_tok' LANGUAGE 'c'; ------=_NextPart_000_008E_01C0E953.DA9F3C40--