Re: DBLink: interesting issue

Oleg Lebedev wrote:
> Ok, here are all the files.
>

I'm now seeing the problem you reported. It is a bug in the new table function
code. Basically, you are trying to do this:

DELETE FROM tablea
WHERE NOT EXISTS
(
   SELECT remoteid
   FROM
   (
     SELECT remoteid
     FROM dblink('hostaddr=1.23.45.6 port=5432 dbname=webspec user=user
                  password=pass',
                 'SELECT objectid  FROM tablea WHERE objectid = ' ||
                  tablea.objectid)
     AS dblink_rec(remoteid int8)
   ) AS t1
);

But if you try:

SELECT remoteid
FROM
(
   SELECT remoteid
   FROM dblink('hostaddr=1.23.45.6 port=5432 dbname=webspec user=user
                password=pass',
               'SELECT objectid  FROM tablea WHERE objectid = ' ||
                tablea.objectid)
   AS dblink_rec(remoteid int8)
) AS t1;

you'll get:

ERROR:  FROM function expression may not refer to other relations of same
query level

which is what you're supposed to get. Apparently the error is not getting
generated as it should when this query is run as a subquery.

What you should actually be doing is:

DELETE FROM tablea
WHERE NOT EXISTS
(
   SELECT remoteid
   FROM dblink('hostaddr=1.23.45.6 port=5432 dbname=webspec user=user
                password=pass',
               'SELECT objectid  FROM tablea WHERE objectid = ' ||
                tablea.objectid)
   AS dblink_rec(remoteid int8)
);
DELETE 0

This should make your function work on 7.3beta, but I still need to track down
a fix for the bug. Thanks for the report!

Joe