Re: sql/med review - problems with patching

Hello

I am playing with foreign tables now.

I found a few small issues now:

* fg tables are not dumped via pg_dump
* autocomplete for CREATE FOREIGN DATA WRAPPER doesn't offer HANDLER
keyword (probably it isn't your problem)
* ERROR: unrecognized objkind: 18 issue

create table omega(a int, b int, c int);
insert into omega select i, i+1, i+2 from generate_series(1,10000000,3) g(i);

postgres=# SELECT * from pg_foreign_server ;
srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
---------+----------+--------+---------+------------+--------+------------
fake | 16384 | 16385 | | | |
(1 row)

postgres=# SELECT * from pg_foreign_data_wrapper ;
fdwname | fdwowner | fdwvalidator | fdwhandler | fdwacl | fdwoptions
---------+----------+--------------+------------+--------+------------
xx | 16384 | 3120 | 3121 | |
(1 row)

COPY omega to '/tmp/omega';

CREATE FOREIGN TABLE omega3(a int, b int, c int) SERVER fake OPTIONS
(filename '/tmp/omega');

create role tom;
grant select on omega2 to tom;

there was unstable behave - first call of select * from omega was
finished by * ERROR: unrecognized objkind: 18 (I can't to simulate
later :( )

second was finished with correct exception

ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command
also works for anyone.

Have to be this security limits still ? I understand to this limit for
COPY statement, but I don't see a sense for foreign table. I agree -
only superuser can CREATE FOREIGN TABLE based on file fdw handler. But
why access via MED have to be limited?

I am very happy from implementation of file_fdw_handler. It is proof
so LIMIT isn't a problem, and I don't understand why it have to be a
problem for dblink handler.

postgres=# select count(*) from omega2;
count
---------
3335004
(1 row)

Time: 1915,281 ms
postgres=# select count(*) from omega2;
count
---------
3335004
(1 row)

Time: 1921,744 ms

postgres=# select count(*) from (select * from omega2 limit 1000) x;
count
-------
1000
(1 row)

Time: 1,597 ms

From practical view I like to see a used option for any tables. I am
missing a more described info in \d command

Regards

Pavel

2010/7/20 Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>:
> 2010/7/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> please, can you refresh patch, please?
>
> Updated patch attached. The latest version is always in the git repo.
> http://repo.or.cz/w/pgsql-fdw.git   (branch: fdw)
> I'm developing the patch on postgres' git repo. So, regression test
> for dblink might fail because of out-of-sync issue between cvs and git.
>
>> When I looked to documentation I miss a some tutorial for foreign
>> tables. There are only reference. I miss some paragraph where is
>> cleanly and simple specified what is possible now and whot isn't
>> possible. Enhancing of dblink isn't documented
>
> Sure. I'll start to write documentation when we agree the design of FDW.
>
>> In function  pgIterate(ForeignScanState *scanstate) you are iterare
>> via pg result. I am thinking so using a cursor and fetching multiple
>> rows should be preferable.
>
> Sure, but I'm thinking that it will be improved after libpq supports
> protocol-level cursor. The libpq improvement will be applied
> much more applications including postgresql_fdw.
>
> --
> Itagaki Takahiro
>