3 ms·
huh? Postgres already has "COPY FROM STDIN" command, which makes database server use postgres connection for the raw data. However, since it uses the existing
by theamk 2y ago
huh?
Postgres already has "COPY FROM STDIN" command, which makes database server use postgres connection for the raw data. However, since it uses the existing connection, it needs to be compatible with Postgres protocol, which means that there is an extra overhead in wrapping streaming data.
On the other hand, "COPY FROM COMMAND" has no wrapping overhead, as it opens direct pipe to/from command, so no Postgres protocol parsing is involved - as only short command string is sent via postgres connection, while bulk data goes over dedicated channel. This makes it faster, although I am not sure how much does this actually save.
amluto's point was that one can achieve no-wrapping performance of "COPY FROM COMMAND" if one could pass dedicated data descriptor to "COPY FROM STDIN". This could be done using SCM_RIGHTS (a AF_UNIX protocol feature that passes descriptors between processes) or pipes+slice (not 100% sure how those would help). But with SCM_RIGHTS, you'd have your psql client create pipe, exec process, and then pass the descriptor to the sql server. This would have exactly the same speed as "COPY FROM COMMAND" (no overhead, dedicated pipe) but would not mix security contexts and would execute any code under server's username - overall better solution.
Your point was "only on localhost", which I interpreted as "this approach would only work if psql runs on localhost (because SCM_RIGHTS only works on localhost); while "COPY FROM COMMAND" could even be executed remotely".
This is 100% correct, but as I said, I think an ability to execute commands remotely as a server user is a bad idea and should have never existed.
- kragen 2y agoi appreciate you having reconstructed the incorrect thing i said as a perfectly sensible and correct one. i agree that everything you said here about postgres is correct except possibly for 'an ability to execute commands remotely as a server user is a bad idea'. i think you are right about what amluto's point was as well; i had understood them to mean something different
- amluto 2y agoIt’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 agothanks 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 agoFor 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..