Re: [HACKERS] PostgreSQL FDW update
On Thu, Feb 24, 2011 at 9:06 AM, Robert Haas wrote: > On Thu, Feb 24, 2011 at 8:13 AM, Shigeru HANADA > wrote: >> >> On Wed, 23 Feb 2011 20:30:05 +0900 >> Shigeru HANADA wrote: >> >>> >>> On Tue, 22 Feb 2011 11:33:25 -0500 >>> Robert Haas wrote: >>> > Is anyone actually working on a new version of this patch sufficiently >>> > rapidly that we can expect a new version in the next day or two? >>> > >>> > If not, I think we mark this one Returned with Feedback and revisit it >>> > for 9.2. >>> >>> I'm working on it. >>> >>> Fixes for new FDW API have been done, but there are some problems in >>> SQL generation codes, such as SELECT clause optimization (omitting >>> unused column from SELECT clause). It would take a while, but I'll >>> post revised version of the patch tomorrow. >> >> Attached is a revised version of postgresql_fdw patch. I started from >> Heikki's latest patch, and modified some points: >> >> 1) use new FDW API >> 2) use EXTENSION framework >> 3) SELECT clause optimization (use NULL for unused columns) >> 4) show remote query in EXPLAIN output >> >> WHERE clause pushdown was implemented in Heikki's version, so I didn't >> touch around it. Now I'm working on cost estimation and connection >> management, but they would need some more work. > > So this is still work-in-progress? When do you expect a final version? Since it sounds like this still needs more work, I'm going to mark it Returned with Feedback. I hope we integrate this during the 9.2 cycle. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL FDW update
On Thu, Feb 24, 2011 at 8:13 AM, Shigeru HANADA wrote: > > On Wed, 23 Feb 2011 20:30:05 +0900 > Shigeru HANADA wrote: > >> >> On Tue, 22 Feb 2011 11:33:25 -0500 >> Robert Haas wrote: >> > Is anyone actually working on a new version of this patch sufficiently >> > rapidly that we can expect a new version in the next day or two? >> > >> > If not, I think we mark this one Returned with Feedback and revisit it for >> > 9.2. >> >> I'm working on it. >> >> Fixes for new FDW API have been done, but there are some problems in >> SQL generation codes, such as SELECT clause optimization (omitting >> unused column from SELECT clause). It would take a while, but I'll >> post revised version of the patch tomorrow. > > Attached is a revised version of postgresql_fdw patch. I started from > Heikki's latest patch, and modified some points: > > 1) use new FDW API > 2) use EXTENSION framework > 3) SELECT clause optimization (use NULL for unused columns) > 4) show remote query in EXPLAIN output > > WHERE clause pushdown was implemented in Heikki's version, so I didn't > touch around it. Now I'm working on cost estimation and connection > management, but they would need some more work. So this is still work-in-progress? When do you expect a final version? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL FDW update
On Tue, 22 Feb 2011 11:33:25 -0500 Robert Haas wrote: > Is anyone actually working on a new version of this patch sufficiently > rapidly that we can expect a new version in the next day or two? > > If not, I think we mark this one Returned with Feedback and revisit it for > 9.2. I'm working on it. Fixes for new FDW API have been done, but there are some problems in SQL generation codes, such as SELECT clause optimization (omitting unused column from SELECT clause). It would take a while, but I'll post revised version of the patch tomorrow. Regards, -- Shigeru Hanada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL FDW update
On Mon, Feb 21, 2011 at 10:31 AM, Tom Lane wrote: > Heikki Linnakangas writes: >> I needed something to test the FDW API patch with, and didn't want to >> get involved in the COPY API changes, and also wanted to have something >> that needs real connection management and can push down quals. So I >> updated the postgresql_fdw patch to work with the latest FDW patch. > >> Here. It's a bit of a mess, but it works for simple queries.. > > I'm going to set this CF item back to Waiting on Author, because (a) > there doesn't seem to be an actual concrete patch presented at the > moment, only a stack of hard-to-follow deltas, and (b) the patch > certainly needs updates for the extension mechanism and committed > FDW API anyway. Is anyone actually working on a new version of this patch sufficiently rapidly that we can expect a new version in the next day or two? If not, I think we mark this one Returned with Feedback and revisit it for 9.2. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL FDW update
Heikki Linnakangas writes: > I needed something to test the FDW API patch with, and didn't want to > get involved in the COPY API changes, and also wanted to have something > that needs real connection management and can push down quals. So I > updated the postgresql_fdw patch to work with the latest FDW patch. > Here. It's a bit of a mess, but it works for simple queries.. I'm going to set this CF item back to Waiting on Author, because (a) there doesn't seem to be an actual concrete patch presented at the moment, only a stack of hard-to-follow deltas, and (b) the patch certainly needs updates for the extension mechanism and committed FDW API anyway. > It requires a small change to the FDW api > (fdw-api-add-serverid-userid.patch). I added server oid and user oid > fields to the FdwPlan - that seems like basic information that most > FDW's will need, so it seems awkward to require the FDW to wrap them in > Const nodes and a List. FYI, I removed those fields from the committed API patch. I don't think it's a good idea to cache potentially-changeable things in a plan node when you can perfectly well fetch them at runtime. Caching user OID is flat wrong anyway, as there's no guarantee that a cached plan will be executed under the same userid it was made with. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL FDW update
On Tue, Feb 8, 2011 at 3:52 AM, Heikki Linnakangas wrote: > I needed something to test the FDW API patch with, and didn't want to get > involved in the COPY API changes, and also wanted to have something that > needs real connection management and can push down quals. So I updated the > postgresql_fdw patch to work with the latest FDW patch. > > Here. It's a bit of a mess, but it works for simple queries.. > > It requires a small change to the FDW api > (fdw-api-add-serverid-userid.patch). I added server oid and user oid fields > to the FdwPlan - that seems like basic information that most FDW's will > need, so it seems awkward to require the FDW to wrap them in Const nodes and > a List. > > These are also available in my git repository at > git://git.postgresql.org/git/users/heikki/postgres.git, branches fdw2 and > postgresql_fdw. So how close are we to getting the core support committed? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PostgreSQL FDW update
I needed something to test the FDW API patch with, and didn't want to get involved in the COPY API changes, and also wanted to have something that needs real connection management and can push down quals. So I updated the postgresql_fdw patch to work with the latest FDW patch. Here. It's a bit of a mess, but it works for simple queries.. It requires a small change to the FDW api (fdw-api-add-serverid-userid.patch). I added server oid and user oid fields to the FdwPlan - that seems like basic information that most FDW's will need, so it seems awkward to require the FDW to wrap them in Const nodes and a List. These are also available in my git repository at git://git.postgresql.org/git/users/heikki/postgres.git, branches fdw2 and postgresql_fdw. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/contrib/postgresql_fdw/postgresql_fdw.c b/contrib/postgresql_fdw/postgresql_fdw.c index cd7902d..17e3856 100644 --- a/contrib/postgresql_fdw/postgresql_fdw.c +++ b/contrib/postgresql_fdw/postgresql_fdw.c @@ -3,17 +3,19 @@ * postgresql_fdw.c * foreign-data wrapper for PostgreSQL * - * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group + * Portions Copyright (c) 1996-2011, PostgreSQL Global Development Group * * IDENTIFICATION - * $PostgreSQL$ + * contrib/postgresql_fdw/postgresql_fdw.c * *- */ #include "postgres.h" +#include "catalog/pg_namespace.h" #include "catalog/pg_operator.h" #include "catalog/pg_proc.h" +#include "foreign/foreign.h" #include "funcapi.h" #include "libpq-fe.h" #include "mb/pg_wchar.h" @@ -40,25 +42,21 @@ extern Datum postgresql_fdw_handler(PG_FUNCTION_ARGS); /* * FDW routines */ -static FSConnection* pgConnectServer(ForeignServer *server, UserMapping *user); -static void pgFreeFSConnection(FSConnection *conn); -static void pgEstimateCosts(ForeignPath *path, PlannerInfo *root, RelOptInfo *baserel); -static void pgOpen(ForeignScanState *scanstate); -static void pgBeginScan(ForeignScanState *scanstate); -static void pgIterate(ForeignScanState *scanstate); -static void pgClose(ForeignScanState *scanstate); -static void pgReOpen(ForeignScanState *scanstate); +static FdwPlan *pgPlanRelScan(Oid foreigntableid, PlannerInfo *root, + RelOptInfo *baserel); +static FdwExecutionState *pgBeginScan(FdwPlan *plan, ParamListInfo params); +static void pgIterate(FdwExecutionState *state, TupleTableSlot *slot); +static void pgReScan(FdwExecutionState *state); +static void pgEndScan(FdwExecutionState *state); /* helper for deparsing a request into SQL statement */ -static bool is_foreign_qual(Expr *expr); + +static bool is_foreign_qual(PlannerInfo *root, RelOptInfo *baserel, Expr *expr); static bool foreign_qual_walker(Node *node, void *context); static void deparseSelectClause(StringInfo sql, ForeignTable *table, TupleDesc tupdesc, const char *aliasname, bool prefix); -static void deparseFromClause(StringInfo sql, ForeignTable *table, const char *aliasname, bool prefix); -static char *deparseSql(ForeignScanState *scanstate); +static char *deparseSql(Oid foreigntableid, PlannerInfo *root, RelOptInfo *baserel); -/* helper for handling result tuples */ -static void storeResult(Tuplestorestate *tupstore, bool is_sql_cmd, - TupleDesc tupdesc, PGresult *res); +static void storeResult(TupleTableSlot *slot, PGresult *res, int rowno); /* * Connection management @@ -75,25 +73,23 @@ static void cleanup_connection(ResourceReleasePhase phase, /* * PostgreSQL specific portion of a foreign query request */ -typedef struct pgFdwReply +typedef struct pgFdwExecutionState { - char *sql; /* SQL text to be sent to foreign server */ - Tuplestorestate *tupstore; /* store all of result tuples */ -} pgFdwReply; + PGconn *conn; + PGresult *res; + int nextrow; +} pgFdwExecutionState; /* * FdwRoutine of PostgreSQL wrapper */ static FdwRoutine postgresql_fdw_routine = { - pgConnectServer, - pgFreeFSConnection, - pgEstimateCosts, - pgOpen, + pgPlanRelScan, pgBeginScan, pgIterate, - pgClose, - pgReOpen, + pgReScan, + pgEndScan }; /* @@ -107,58 +103,18 @@ postgresql_fdw_handler(PG_FUNCTION_ARGS) PG_RETURN_POINTER(&postgresql_fdw_routine); } -/* - * Connect to a foreign PostgreSQL server with libpq if necessary. - */ -static FSConnection * -pgConnectServer(ForeignServer *server, UserMapping *user) +static FdwPlan * +pgPlanRelScan(Oid foreigntableid, PlannerInfo *root, RelOptInfo *baserel) { - elog(DEBUG3, "%s() called for \"%s\"", __FUNCTION__, server->servername); - - return (FSConnection *) GetConnection(server, user); -} + FdwPlan *plan = makeNode(FdwPlan); + char *sql; + sql = deparseSql(foreigntableid, root, baserel); -/* - * Disconnect from the foreign server if the connection is not referenced by - * any other scan. - */ -static void -pgFreeFSConnection(FSConnection *conn) -{ - elog(DEBUG3, "%s() called",