Q: disabling codecs while transferring data?

[ale-dd]

I have a couple of potentially silly questions.

Suppose I am reading rows in batches from a table in a source database and writing the same rows in batches into another table in another database.

1. Can I disable all decoders on the connection to the source database as well as all encoders on the connection to the destination database and just deal with records that have a binary representation of each column?
2. Can I do so on a statement by statement basis such that for all the statements that are not directly involved into this data triangulation the codecs still apply? Or should I have a separate connection for all the meta operations like introspecting the current status and checkpointing progress?

Thank you!

[elprans]

Codecs are configured per-connection, so if you want them off in one case and on in another, you must use separate connections.

[ale-dd]

Would turning them off also avoid the introspection query that takes place when preparing a statement? That would be a big plus to me, since I often have to coerce types via explicit casting to avoid that query breaking when I am dealing with CRDB...

[elprans]

No. The introspection query is necessary for builtin decoders also.

[ale-dd]

Is there no way to avoid even builtin decoders and encoders so as to be able to just "shuttle" the raw data between two databases without any attempt at interpreting it?

[elprans]

Theres is: use copy_from_table/copy_to_table.

[ale-dd]

Unfortunately, the destination table is expected to already contain older versions of many of the same rows I'd be copying into it. What I need to perform are UPSERTs. For this reason, I cannot use copy_from_table/copy_to_table.

[elprans]

Use a temporary table and then INSERT ... ON CONFLICT from it: https://schinckel.net/2019/12/13/asyncpg-and-upserting-bulk-data/

[ale-dd]

I've seen that recommendation before. I'll keep it in mind in the future. Thank you!

I'm curious if what I was hoping for is something that is just not feasible because of intrinsic reasons pertaining the protocol, or if it is just not a feature that asyncpg presently offers.

[elprans]

It is inherent. asyncpg needs introspection to make sense of the incoming bytes, it's not even text.

[ale-dd]

Yes, I understand that bit, and I was wondering if the same blob of bytes attained from a SELECT could be provided to an INSERT, without interpreting it, provided that the same columns are specified by the SELECT and by the subsequent INSERT.

[elprans]

No, that's not how the implementation works. Turning off data decoding completely is not supported and is not planned to be supported (well, except raw copy which is already implemented).