Is a copy_records_from_table function possible?

[Spacerat]

Hi! I've been wondering, are there any technical limitations preventing the existence of a copy_records_from_table function? It would use COPY with FORMAT BINARY and yield Record objects.

Presumably it would only be easy to build this if the COPY FROM binary format is the same as the binary format used by Postgres for select queries. Does anyone know if that's the case?

I think this is related to #17 ('pandas dataframe') since in that issue too, the suggestion is to parse the binary output of a COPY; there the proposal seems to be a plugin architecture with Arrow support, and actually that would suit my needs even better, but I feel like a simple function which passes records to a coroutine (or yields them) would be more than good enough.

[elprans]

See #328

[Spacerat]

I have already seen that the speeds are comparable. However, despite that, I'm seeing some evidence that COPY vs SELECT queries are not treated the same by postgres itself.

I'm making queries against large tables in a database replica. This is a chart of "replica lag" over time:

This is the most "apples-to-apples" comparison I have so far, but I have managed to cause a replica lag of over an hour with "SELECT * "; something that COPY never seems to do. In one case, a SELECT * against the largest table we have caused the transaction log disk usage to shoot up to 10GB.

If I understand #328 correctly, the literal answer to my question is "yes, it's possible (but not worth it)" - is that interpretation correct?

I still don't fully understand the differences between COPY vs SELECT, but if they actually do impact the database differently, perhaps this idea is worth considering further?

[elprans]

Interesting. AFAIK, both COPY table TO and SELECT * FROM table take an ACCESS SHARE lock on the table and thats it. They should be largely equivalent. Perhaps it would be worth posting a question to pgsql-general with your findings, it might be a bug. I’ll reopen this for now.

[elprans]

No activity for a while, closing.