Обсуждение: 9.3-beta postgres-fdw COPY error
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" Is this expected behavior or a bug? thanks!
On 06/21/2013 10:39 AM, Lonni J Friedman 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" > > > Is this expected behavior or a bug? Expected I believe: http://www.postgresql.org/docs/9.3/static/postgres-fdw.html "Now you need only SELECT from a foreign table to access the data stored in its underlying remote table. You can also modify the remote table using INSERT, UPDATE, or DELETE. " > > thanks! > > -- Adrian Klaver adrian.klaver@gmail.com
On Jun 21, 2013, at 1:39 PM, Lonni J Friedman <netllama@gmail.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 ','); I am curious to know, why do you want to execute COPY command through fdw, why not run directly on server, which has table? Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Postgres Database Company Blog:http://vibhork.blogspot.com
I was afraid someone would say that. Is this a limitation that might be removed in the future (like 9.4), or is there a technical reason why its not possible to do a COPY against a foreign table? On Fri, Jun 21, 2013 at 10:52 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 06/21/2013 10:39 AM, Lonni J Friedman 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" >> >> >> Is this expected behavior or a bug? > > > Expected I believe: > > http://www.postgresql.org/docs/9.3/static/postgres-fdw.html > > "Now you need only SELECT from a foreign table to access the data stored in > its underlying remote table. You can also modify the remote table using > INSERT, UPDATE, or DELETE. " > >> >> thanks! >> >> > > > -- > Adrian Klaver > adrian.klaver@gmail.com -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand https://netllama.linux-sxs.org
On 06/21/2013 10:56 AM, Lonni J Friedman wrote: > I was afraid someone would say that. Is this a limitation that might > be removed in the future (like 9.4), or is there a technical reason > why its not possible to do a COPY against a foreign table? > Others would have to comment on that. Important to remember that FDWs are a work in progress. They just got write capabilities in 9.3. I would expect their feature set to increase with each major release. -- Adrian Klaver adrian.klaver@gmail.com
On Jun 21, 2013, at 1:56 PM, Lonni J Friedman <netllama@gmail.com> wrote: > I was afraid someone would say that. Is this a limitation that might > be removed in the future (like 9.4), or is there a technical reason > why its not possible to do a COPY against a foreign table? I think reason is, FDW is a way of representation of foreign table in your current database. And when you take logical backup(pg_dump), you actually dump the definition of representation, not table. And if its allowed then, in my view you arelosing the idea of representation with idea of actually creating that table in database. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Postgres Database Company Blog:http://vibhork.blogspot.com
On Jun 21, 2013, at 3:38 PM, Lonni J Friedman <netllama@gmail.com> wrote: > On Fri, Jun 21, 2013 at 11:17 AM, Vibhor Kumar > <vibhor.kumar@enterprisedb.com> wrote: >> >> On Jun 21, 2013, at 2:05 PM, Lonni J Friedman <netllama@gmail.com> wrote: >> >>> On Fri, Jun 21, 2013 at 10:56 AM, Vibhor Kumar >>> <vibhor.kumar@enterprisedb.com> wrote: >>>> >>>> On Jun 21, 2013, at 1:39 PM, Lonni J Friedman <netllama@gmail.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 bandwidthperformance. 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 hastable? >>> >>> 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 FDWCOPY 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