Re: COPY to/from non-local file

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 <JSilvela(at)Bear(dot)com> 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