I just checked the manual and it says nothing about being able to use the SQL copy to access network files. But you have an option that almost works for you. I am sure others here will have some suggestions, but if I were in your place, I'd gather more information about where bottlenecks exist in a solution that works for me, albeit slowly. To check your performance problem, you may want to add benchmarking code to your Ruby program to see where it is spending its time: on getting the data from PostgreSQL or writing it to a file. That will tell you where your efforts are best spent. I don't work with Ruby, but if I was using C++ or Java or Perl, I'd use the same strategy: get the data en mass, and then write it out as efficiently as possible. In C++ for example, there is an iostream class based idiom that using one line of code in the application program and that line is as fast as you can make your io code without delving deeply into IO processing and developing your own IOstream classes. In both C++ and Java, you have stream classes, and significant control over how the data is written: one character at a time (if you want the process to take forever ;-) or in blocks of whatever size you want. But this involves being willing to develop your own stream classes to implement your preferred buffering strategy. In C++, you can save a lot of development time by having template policy classes that control how best to optimize disk IO. In Perl, you can read a file en mass and then iterate through it a line at a time, but for this my preference at present is to use C++. Since I don't know Ruby, I don't know how well it supports these, and related, IO programming idioms. If it's support is poor, it may pay to use a more powerful and flexible language. If it has outstanding power and flexibility for supporting IO optimization programming idioms, let me know and I'll invest the time to add Ruby to my list of languages in my development toolbox. :-) I would assume that what I have learned using the languages I know can be applied in some fashion to programming in Ruby. HTH Ted
Jaime Silvela <[EMAIL PROTECTED]> wrote: I've been looking around for this functionality: Is it possible to use COPY with a file that doesn't reside in the DB's filesystem? I know there is sort of a solution in using COPY from stdin/ COPY to stdout, however that depends on calling the psql command, which breaks the flow of control of the programs I'm trying to write. Here's the story: I have a Ruby on Rails application which sits on server A, and connects to a Postgres Database running on B. Using the regular flow, the rails code writes into various tables, and then generates a table that needs to be exported into a file which will be used as input into a separate program, a simulator, and kept in the filesystem of the Rails server. Using the COPY command would entail either of a) Using COPY to generate the file on server B, then transfer to A - but how? ftp? I want to reduce this type of coupling b) Using COPY TO STDOUT from psql, called in the Rails code with a backtick, then gathering the output and filing it. - but this solution depends on having psql in the path of the Rails server, reintroducing the server credentials, and from a programming point of view is kind of ugly. c) The Postgres driver for Rails tries to give an interface to the COPY command using "raw_connection", "getline" and "endcopy", but it's quite brittle, so I'm avoiding it altogether. At the moment I'm avoiding those solutions, and instead get the table into Rails space with a CURSOR on a SELECT, then simply write the file in Rails, one line at a time. However, the tables I'm writing are pretty big, and the performance is so much worse than with COPY... Any suggestions? Thanks Jaime *********************************************************************** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. *********************************************************************** ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
