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)
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 |