DBLink: interesting issue

From: "Oleg Lebedev" <oleg(dot)lebedev(at)waterford(dot)org>
To: <pgsql-general(at)postgresql(dot)org>
Subject: DBLink: interesting issue
Date: 2002-09-20 22:19:55
Message-ID: 993DBE5B4D02194382EC8DF8554A52730334FA@postoffice.waterford.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

replica=> 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(dblink_settings,'SELECT objectid FROM activity') AS dblink_p
) t1
) a1
WHERE
remoteid=activity.objectid);

ERROR: 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.

replica=> SELECT objectid
FROM activity
WHERE
objectid NOT IN (SELECT remoteid
FROM (SELECT CAST
(dblink_tok(t1.dblink_p, 0) AS int8) AS remoteid
FROM (SELECT
dblink(dblink_settings,'SELECT objectid FROM activity') AS dblink_p
) t1
) a1
);
objectid
----------
(0 rows)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Conway 2002-09-20 23:36:30 Re: DBLink: interesting issue
Previous Message Alvaro Herrera 2002-09-20 21:32:02 Re: Weird Trigger behaviour