52 ms·

It’s not just COPY … TO/FROM PROGRAM. IMO all variants of COPY are a mistake — the server should not be opening files on behalf of the client. What is actually

by amluto 2y ago

It’s not just COPY … TO/FROM PROGRAM. IMO all variants of COPY are a mistake — the server should not be opening files on behalf of the client. What is actually wanted is the rough equivalent of: psql … < file Where < could be > or | or piped the other way, plus all the FORMAT goodies, plus perhaps some optimizations to make it fast, but keeping the fact that the client opens the file or executes the program. If you are trying to write a table to a CSV file remotely and you want it to land in a file on the server, then (a) that’s weird and I struggle to come up with a case in which you actually want that and (b) use SSH or some other bespoke protocol to do what you actually want, rather than having the database server kludge it up for you. On the occasions where I’ve wanted anything resembling this functionality, I have always actually wanted psql’s \copy. COPY … STDIN/STDOUT is fine, but it takes some potentially awkward code that is, as far as I can tell, not particularly portable between languages or between database protocols.

kragen 2y ago
thanks for clarifying what you meant! > that’s weird and I struggle to come up with a case in which you actually want that oh, that's easy: 1. your san backplane is faster than ssh 2. you want to make a backup in a non-postgres-dependent format on the server's tape drive 3. you want to import the data into a new test database you're creating on the server i mean, sure, nowadays you would usually do all those things on multiple servers. but postgres still scales down to the scale where your servers are pets, not cattle, and that's a feature. remember ingres got renamed postgres in the 90s, and ingres itself is from the 70s > On the occasions where I’ve wanted anything resembling this functionality, I have always actually wanted psql’s \copy. that's great, but would you mind parking your car somewhere that isn't my lawn? you're right about fd passing tho
- yencabulator 2y ago
  For those uses, the DBA can use their existing ability to connect to the database server host. From grandparent: > That's kinda the point - there is an argument that one should not be baking arbitrary shell command execution into database server at all. Such execution will lack critical security features - setting right user, process tracking, cleanup, etc..

by amluto 2y ago

kragen 2y ago

thanks for clarifying what you meant! > that’s weird and I struggle to come up with a case in which you actually want that oh, that's easy: 1. your san backplane is faster than ssh 2. you want to make a backup in a non-postgres-dependent format on the server's tape drive 3. you want to import the data into a new test database you're creating on the server i mean, sure, nowadays you would usually do all those things on multiple servers. but postgres still scales down to the scale where your servers are pets, not cattle, and that's a feature. remember ingres got renamed postgres in the 90s, and ingres itself is from the 70s > On the occasions where I’ve wanted anything resembling this functionality, I have always actually wanted psql’s \copy. that's great, but would you mind parking your car somewhere that isn't my lawn? you're right about fd passing tho

yencabulator 2y ago
For those uses, the DBA can use their existing ability to connect to the database server host. From grandparent: > That's kinda the point - there is an argument that one should not be baking arbitrary shell command execution into database server at all. Such execution will lack critical security features - setting right user, process tracking, cleanup, etc..