Re: COPY (query) TO file

> Mark Woodward wrote:
> ...
>
>>>> pg_dump -t mytable | psql -h target -c "COPY mytable FROM STDIN"
>>>>
>>>> With a more selective copy, you can use pretty much this mechanism to
>>>> limit a copy to a sumset of the records in a table.
>>> Ok, but why not just implement this into pg_dump or psql?
>>> Why bother the backend with that functionality?
>>
>> Because "COPY" runs on the back-end, not the front end, and the front
>> end
>> may not even be in the same city as the backend. When you issue a "COPY"
>> the file it reads or writes local to the backend. True, the examples I
>> gave may not show how that is important, but consider this:
>
>
> We were talking about COPY to stdout :-) Copy to file is another
> issue :-) Copy to (server fs) file has so many limitations I dont see
> wide use for it. (Of course there are usecases)

"wide use for" is not always the same as "useful." Sometimes "useful" is
something not easily doable in other ways or completes a feature set.

>
>> psql -h remote masterdb -c "COPY (select * from mytable where ID <
>> xxlastxx) as mytable TO '/replicate_backup/mytable-060602.pgc'"
>>
>> This runs completely in the background and can serve as a running
>> backup.
>
> And you are sure it would be much faster then a server local running
> psql just dumping the result of a query?

No I can't be sure of that at all, but .... The COPY command has a
specific use that is understood and an operation that is separate from the
normal query mechanism.

> (And you could more easy avoid raceconditions in contrast to several
> remote clients trying to trigger your above backup )

Again, the examples may not have been precise in presenting "why," the
focus was mostly "what" so it could be discussed. As a generic feature it
has many potential uses. Trying to debate and defend a specific use limits
the potential scope of the feature.

Why have COPY anyway? Why not just use "SELECT * FROM TABLE?"