Re: SQL/MED with simple wrappers
От | Shigeru HANADA |
---|---|
Тема | Re: SQL/MED with simple wrappers |
Дата | |
Msg-id | 20101026222256.F643.6989961C@metrosystems.co.jp обсуждение исходный текст |
Ответ на | Re: SQL/MED with simple wrappers (Pavel Stehule <pavel.stehule@gmail.com>) |
Список | pgsql-hackers |
Thanks for your comments. On Mon, 25 Oct 2010 15:05:51 +0200 Pavel Stehule <pavel.stehule@gmail.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 standardformat. 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_servercsv_branches | file_servercsv_history | file_servercsv_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
В списке pgsql-hackers по дате отправления: