Re: DBLink: interesting issue - Mailing list pgsql-general

Joe,
I am running PostgreSQL 7.2.1 on Linux 7.2
I installed Postgres7.3b and a version of DBLink that comes with it in a
tar.gz archive.
I tried to use the optimized code you sent me and got the same error.
Here is what I got:
DELETE FROM activity WHERE NOT EXISTS (SELECT remoteid  FROM (SELECT
remoteid FROM dblink('dbname=replica','SELECT objectid  FROM activity
WHERE
objectid = ' || activity.objectid) AS dblink_rec(remoteid int8)) AS t1 )

server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Interstingly, this "haults" Postgres7.3b postmaster and I have to
restart it again. When I run a modified version of this delete statement
on Postgres7.2.1 I still get the same message, but am able to reconnect
to the database without restarting the postmaster.


-----Original Message-----
From: Joe Conway [mailto:mail@joeconway.com]
Sent: Friday, September 20, 2002 5:37 PM
To: Oleg Lebedev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] DBLink: interesting issue


Oleg Lebedev wrote:
> I am using dbLink library to synchronize tables in two databases.
> Below
> are two identical in terms of their results queries, one is using
> NOT EXISTS and another NOT IN. The later one is 100 time slower then
the
> former one according to EXPLAIN. However, the former one throws an
> interesting exception every time I run it. Interestingly, it works
fine
> if the local table is empty. Any ideas why it happens?

I need more information. What version of PostgreSQL, and is dblink the
version
distributed with Postgres, or is it a later copy from CVS?

I have a machine running 7.2.2 and a CVS (Version 0.4, ~mid April, 2002)
copy
of dblink, and cannot reproduce the failure:

testmst=# select version();
                            version
-------------------------------------------------------------
  PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row)

testslv=# SELECT objectid FROM activity WHERE NOT EXISTS (SELECT
remoteid FROM
(SELECT CAST (dblink_tok(t1.dblink_p, 0) AS int8) AS remoteid FROM
(SELECT
dblink('dbname=testmst','SELECT objectid FROM activity') AS dblink_p)
t1) a1
WHERE remoteid=activity.objectid);
  objectid
----------
(0 rows)

Early in 7.3 development I updated dblink in a 7.2 backward compatible
way.
Unfortunately, it wasn't practical to keep it backward compatible to
7.2.x, so
the current CVS HEAD version will no longer work with 7.2.x. If you
would like
to try dblink Version 0.4, let me know and I'll send you a patch.

As a side note, 7.3 (currently in beta) has much improved semantics for
use of
dblink, made possible by the new table function capability. E.g.:

regression_slave=# select version();
                                                     version
------------------------------------------------------------------------
----------------------------------------
  PostgreSQL 7.3b1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.1
20020205 (Red Hat Linux Rawhide 3.1-0.21)
(1 row)

regression_slave=# SELECT objectid FROM activity WHERE NOT EXISTS
(SELECT
remoteid FROM dblink('dbname=regression','SELECT objectid FROM
activity') AS
t1(remoteid int8) WHERE remoteid=activity.objectid);
  objectid
----------
         5
(1 row)

Or even better performance-wise:

regression_slave=# SELECT objectid FROM activity WHERE NOT EXISTS
(SELECT
remoteid FROM dblink('dbname=regression','SELECT objectid FROM activity
WHERE
objectid = ' || activity.objectid) AS t1(remoteid int8));
  objectid
----------
         5
(1 row)

If you can, please give the beta (Postgres and dblink) a try (not for
production yet, of course, but for feedback).

Thanks,

Joe