Re: pgsql_fdw, FDW for PostgreSQL server

On 28 March 2012 08:39, Thom Brown  wrote:
> On 28 March 2012 08:13, Thom Brown  wrote:
>> 2012/3/28 Shigeru HANADA :
>>> (2012/03/27 20:32), Thom Brown wrote:
>>>> 2012/3/26 Shigeru HANADA:
>>>>> * pgsql_fdw_v17.patch
>>>>>     - Adds pgsql_fdw as contrib module
>>>>> * pgsql_fdw_pushdown_v10.patch
>>>>>     - Adds WHERE push down capability to pgsql_fdw
>>>>> * pgsql_fdw_analyze_v1.patch
>>>>>     - Adds pgsql_fdw_analyze function for updating local stats
>>>>
>>>> Hmm... I've applied them using the latest Git master, and in the order
>>>> specified, but I can't build the contrib module.  What am I doing
>>>> wrong?
>>>
>>> I'm sorry, but I couldn't reproduce the errors with this procedure.
>>>
>>> $ git checkout master
>>> $ git pull upstream master      # make master branch up-to-date
>>> $ git clean -fd                 # remove files for other branches
>>> $ make clean                    # just in case
>>> $ patch -p1 < /path/to/pgsql_fdw_v17.patch
>>> $ patch -p1 < /path/to/pgsql_fdw_pushdown_v10.patch
>>> $ patch -p1 < /path/to/pgsql_fdw_analyze_v1.patch
>>> $ make                          # make core first for libpq et al.
>>> $ cd contrib/pgsql_fdw
>>> $ make                          # pgsql_fdw
>>>
>>> Please try "git clean" and "make clean", if you have not.
>>> FWIW, I'm using GNU Make 3.82 and gcc 4.6.0 on Fedora 15.
>>
>> I had done a make clean, git stash and git clean -f, but I didn't try
>> git clean -fd.  For some reason it's working now.
>
> Hmm.. I'm getting some rather odd errors though:
>
> thom@test=# select * from stuff limit 3 ;
> DEBUG:  StartTransactionCommand
> DEBUG:  StartTransaction
> DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR,
> xid/subid/cid: 0/1/0, nestlvl: 1, children:
> LOG:  statement: select * from stuff limit 3 ;
> DEBUG:  relid=16402 fetch_count=10000
> DEBUG:  Remote SQL: SELECT id, stuff, age FROM public.stuff
> DEBUG:  starting remote transaction with "START TRANSACTION ISOLATION
> LEVEL REPEATABLE READ"
> ERROR:  could not declare cursor
> DETAIL:  ERROR:  relation "public.stuff" does not exist
> LINE 1: ...or_6 SCROLL CURSOR FOR SELECT id, stuff, age FROM public.stu...
>                                                             ^
>
> HINT:  DECLARE pgsql_fdw_cursor_6 SCROLL CURSOR FOR SELECT id, stuff,
> age FROM public.stuff
>
> The table in question indeed doesn't exist, but I'm confused as to why
> the user is being exposed to such messages.
>
> And more troublesome:
>
> (local select on foreign server):
>
> test=# select * from stuff limit 3 ;
>  id |  thing   | age
> ----+----------+-----
>  1 | STANDARD |  30
>  2 | STANDARD |  29
>  3 | STANDARD |  12
> (3 rows)
>
> (foreign select on foreign server):
>
> thom@test=# select * from stuff limit 3 ;
>  id |      stuff      | age
> ----+-----------------+-----
>  1 | (1,STANDARD,30) |  30
>  2 | (2,STANDARD,29) |  29
>  3 | (3,STANDARD,12) |  12
> (3 rows)
>
>
> The row expansion seems to incorrectly rewrite the column without a
> table prefix if both column and table name are identical.

Actually, correction.  The foreign table definition names the column
the same as the table.  I accidentally omitted the 'thing' column and
instead substituted it with the table name in the definition.

Original table definition on foreign server:

create table stuff (id serial primary key, thing text, age int);

Foreign table definition:

create foreign table stuff (id int not null, stuff text, age int) server pgsql;

So it appears I'm allowed to use the table as a column in this
context.  So please disregard my complaint.

-- 
Thom