Re: 9.3-beta postgres-fdw COPY error

On Jun 21, 2013, at 3:38 PM, Lonni J Friedman <netllama(at)gmail(dot)com> wrote:

> On Fri, Jun 21, 2013 at 11:17 AM, Vibhor Kumar
> <vibhor(dot)kumar(at)enterprisedb(dot)com> wrote:
>>
>> On Jun 21, 2013, at 2:05 PM, Lonni J Friedman <netllama(at)gmail(dot)com> wrote:
>>
>>> On Fri, Jun 21, 2013 at 10:56 AM, Vibhor Kumar
>>> <vibhor(dot)kumar(at)enterprisedb(dot)com> wrote:
>>>>
>>>> On Jun 21, 2013, at 1:39 PM, Lonni J Friedman <netllama(at)gmail(dot)com> wrote:
>>>>
>>>>> Greetings,
>>>>> I'm trying to test out the new postgres-fdw support in postgresql-9.3
>>>>> (beta) in preparation for an upgrade from 9.2 later this year. So
>>>>> far, everything is working ok, however one problem I'm encountering is
>>>>> with the COPY command. When I run it against a foreign table (which is
>>>>> also in a 9.3 instance), it fails:
>>>>>
>>>>> COPY my_foreigntbl (id,testname) TO '/tmp/testlist_aid' (DELIMITER ',');
>>>>> ERROR: cannot copy from foreign table "my_foreigntbl"
>>>>>
>>>> You would like to try something like:
>>>> COPY (SELECT id, testname FROM my_foreigntbl) TO '/tmp/testlist_aid' (DELIMITER ',');
>>>
>>> thanks, that seems to work. is there any performance difference
>>> between what I was attempting, and what you suggested?
>>
>> I think so. When you use COPY (SELECT ) SELECT get executed first and with FDW, you will also include your network bandwidth performance. However COPY TABLE is direct read from data files of relation which is faster than COPY (SELECT).
>>
>>
>>
>>>>
>>>> I am curious to know, why do you want to execute COPY command through fdw, why not run directly on server, which has table?
>>>
>>> I'm hoping to setup postgres_FDW as a means of sharding for some
>>> tables that have characteristics which are significantly different
>>> than the rest of the cluster. For example, orders of magnitude
>>> greater changes to the data, size of data, etc, which makes having
>>> them hosted in a separate cluster desirable from a performance
>>> perspective.
>>
>> For Sharding purpose, it sounds me good. However, if you are thinking of dumping remote data and then pushing using FDW COPY command, then I don't think that is supported here. I preferably dump that specific data from remote cluster.
>
> I have a pre-existing process that relies on using COPY
> bi-directionally to maintain a list of hundreds of thousands of unique
> values, and a separate table which then references those unique values
> with new datasets, which range from 10k to 200k rows of new data at a
> time. In the past, I was doing all of this with simple INSERTS, but
> some basic benchmarking showed that the performance was poor, and
> switching to using COPY reduced it by more than 50%.

COPY is faster than multiple insert.

> Unfortunately, I also need to use "COPY ... FROM" to the foreign
> table, and that also fails
> COPY my_foreigntbl
> (last_update,current_status,testid,suiteid,regressioncl,testcl,os,arch,build_type,branch,gpu,subtest,osversion)
> FROM '/tmp/scrub_csv' with CSV HEADER ESCAPE '~';
> ERROR: cannot copy to foreign table "my_foreigntbl"

Thats true. If you look at my previous response I had mentioned that data pushing through FDW using COPY command is not available. However, to workaround you can do one thing. You can create temp table with data Or using file_fdw create a table on the csv/data dump

and then using INSERT INTO my_foreigntbl SELECT * FROM file_fdw/temp table, you can do load.

However, doing direct COPY on foreign server will still be faster than above workaround.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Postgres Database Company
Blog:http://vibhork.blogspot.com