FDW state from plan time

Dave Page <dpage@pgadmin.org> writes:

I can't help thinking I must be missing something obvious here, but is
there any way to persist some data from PlanForeignScan to at least
BeginForeignScan in an FDW? I'm aware of fdwplan->fdw_private, but at
that needs to be copyObject compatible, it's not much use to me.

The issue I have is that in order to properly plan my remote scan, I
need to connect to the remote database to try to calculate some stats
for the query. I'm currently then disconnecting and freeing all the
data associated with that connection, and then reconnecting again in
BeginForeignScan in order to do the actual work. I'd like to avoid the
connect/reconnect, as we all know they can be expensive!

I'd suggest that you reference your open connections with an integer
index, which you could then store in the plan node. Plans don't have to
survive longer than the current backend, so this would be perfectly safe
so long as you don't re-use the indexes within a session.

Or you could use a hash of some other identifying information (perhaps
the OID of the foreign server would be sufficient ID for a connection?).

regards, tom lane

On 03.04.2011 19:38, Tom Lane wrote:

Dave Page<dpage@pgadmin.org> writes:

I can't help thinking I must be missing something obvious here, but is
there any way to persist some data from PlanForeignScan to at least
BeginForeignScan in an FDW? I'm aware of fdwplan->fdw_private, but at
that needs to be copyObject compatible, it's not much use to me.

If you have to, you can encapsulate arbitrary data in a bytea const.

Or you could use a hash of some other identifying information (perhaps
the OID of the foreign server would be sufficient ID for a connection?).

Foreign server + user id, probably. That's what I would recommend in
this case.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Sun, Apr 3, 2011 at 5:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dave Page <dpage@pgadmin.org> writes:

I can't help thinking I must be missing something obvious here, but is
there any way to persist some data from PlanForeignScan to at least
BeginForeignScan in an FDW? I'm aware of fdwplan->fdw_private, but at
that needs to be copyObject compatible, it's not much use to me.

The issue I have is that in order to properly plan my remote scan, I
need to connect to the remote database to try to calculate some stats
for the query. I'm currently then disconnecting and freeing all the
data associated with that connection, and then reconnecting again in
BeginForeignScan in order to do the actual work. I'd like to avoid the
connect/reconnect, as we all know they can be expensive!

I'd suggest that you reference your open connections with an integer
index, which you could then store in the plan node.  Plans don't have to
survive longer than the current backend, so this would be perfectly safe
so long as you don't re-use the indexes within a session.

Or you could use a hash of some other identifying information (perhaps
the OID of the foreign server would be sufficient ID for a connection?).

Ahh, ok - that should work. Thanks!

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Sun, Apr 3, 2011 at 7:05 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:

On 03.04.2011 19:38, Tom Lane wrote:

Dave Page<dpage@pgadmin.org>  writes:

I can't help thinking I must be missing something obvious here, but is
there any way to persist some data from PlanForeignScan to at least
BeginForeignScan in an FDW? I'm aware of fdwplan->fdw_private, but at
that needs to be copyObject compatible, it's not much use to me.

If you have to, you can encapsulate arbitrary data in a bytea const.

Or you could use a hash of some other identifying information (perhaps
the OID of the foreign server would be sufficient ID for a connection?).

Foreign server + user id, probably. That's what I would recommend in this
case.

And database in some cases, but yes :-)

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company