Re: SQL/MED with simple wrappers

Thanks for your comments.

On Mon, 25 Oct 2010 15:05:51 +0200
Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> > 4) List of foreign connections
> > Users (especially DBAs?) might want to see list of foreign connections.
> > Currently postgresql_fdw provides its own connection list via
> > postgresql_fdw_connections view.  Common view such as
> > pg_foreign_connections would be needed?  If so, function which returns
> > list of active connections would be necessary in FDW API.
> >
>
> + list of foreign tables?

I've implemented that functionality in some places.

1) \det psql command shows list of foreign table in the format like
\dew and \des.
2) pg_foreign_tables catalog shows pair of OIDs (relation oid and
server oid) and options in raw format.
3) views in information_schema, foreign_tables and foreign_table_options
show information about foreign tables in SQL standard format.

Here the detail of \det psql command is.

\det psql command (followed naming of \des/\dew) shows list of
foreign tables, and \det <pattern> shows the list of foreign tables
whose name match the pattern. For example of file_fdw:

postgres=# \det
List of foreign tables
Table | Server
--------------+-------------
csv_accounts | file_server
csv_branches | file_server
csv_history | file_server
csv_tellers | file_server
(4 rows)

Adding postfix "+" shows per-table generic options too.

postgres=# \det+
List of foreign tables
Table | Server | Options
--------------+-------------+----------------------------------------------------------------
csv_accounts | file_server | {format=csv,filename=/home/hanada/DB/CSV/pgbench_accounts.csv}
csv_branches | file_server | {format=csv,filename=/home/hanada/DB/CSV/pgbench_branches.csv}
csv_history | file_server | {format=csv,filename=/home/hanada/DB/CSV/pgbench_history.csv}
csv_tellers | file_server | {format=csv,filename=/home/hanada/DB/CSV/pgbench_tellers.csv}
(4 rows)

I have chosen \det+ command to show per-table options because \d+
command has already many columns and seems difficult to add long
values.

In addition to \det, \dec command would be necessary to show per-column
options with columns. It hasn't been implemented yet, though.

> > 5) Routine mapping
> > If a function in local query can be evaluated on the remote side in
> > same semantics, it seems efficient to push the function down to the
> > remote side.  But how can we know whether the function can be pushed
> > down or not?  For such purpose, SQL/MED standard defines "routine
> > mapping".  Should we implement routine mapping?
> >
>
> is it related to aggregate functions? If yes, this it can be really
> significant help

Yes. I was thinking about only normal functions at original post,
though. To push down aggregate function to remote side, FDW would
need additional planner hook to merge Aggregate node in to ForeignScan
node. Such planner hook might be able to handle Order or Limit node
too.

> > 7) Using cursor in postgresql_fdw
> > postgresql_fdw fetches all of the result tuples from the foreign
> > server in the first pgIterate() call.  It could cause out-of-memory if
> > the result set was huge.  If libpq supports protocol-level cursor,
> > postgresql_fdw will be able to divide result set into some sets and
> > lower the usage of memory.  Or should we use declare implicit cursor
> > with DECLARE statement?  One connection can be used by multiple
> > ForeignScan nodes in a local query alternately.  An issue is that
> > cursor requires implicit transaction block.  Is it OK to start
> > transaction automatically?
>
> I don't know why DECLARE statement is problem? Can you explain it, please.

The most serious issue would be that SQL-level cursors require
explicit transaction block. To use SQL-level cursors with shared
connections between multiple ForeignScan, postgresql_fdw need to
manage transaction state per connection. Especially, recovering
error would make codes complex. Or, we would be able to take the
easiest way, discarding connection at the error.

I'll try to implement cursor-version of postgresql_fdw experimentally
to make this issue clearer.

Regards,
--
Shigeru Hanada