Re: pgsql_fdw, FDW for PostgreSQL server

On 28 March 2012 08:13, Thom Brown <thom(at)linux(dot)com> wrote:
> 2012/3/28 Shigeru HANADA <shigeru(dot)hanada(at)gmail(dot)com>:
>> (2012/03/27 20:32), Thom Brown wrote:
>>> 2012/3/26 Shigeru HANADA<shigeru(dot)hanada(at)gmail(dot)com>:
>>>> * 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(at)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(at)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.

--
Thom