streaming inserts / Copy API

[oyvindberg]

doobie supports streaming rows through the Copy API and the Text type class. typo should too

[sbrunk]

I've tried the Copy API with Doobie with mixed results (ran into CSV escaping issues). I guess a more robust way would be the Postgres binary format but that's not supported in Doobie yet.

Perhaps not as fast but still faster than single inserts could be batch updates via updateMany. See https://tpolecat.github.io/doobie/docs/07-Updating.html#batch-updates Do you think it's feasible to generate an insertMany/updateMany for the repo impls anologously to single insert/update?

[oyvindberg]

It's absolutely possible, yes. I think it's possibly the largest missing feature from typo right now to effortlessly insert many rows fast.

I'm not sure how to proceed really. What you suggest is likely easier in the sense that it can be implemented straight away.

Maybe we should just do that? Do you have any time to contribute towards that? What I would need is a proposal for the code which should be generated. It's pretty fast for me to put it into typo once that research phase is over. For instance implement it in code for Person, say here https://github.com/oyvindberg/typo/tree/main/typo-tester-doobie/generated-and-checked-in/adventureworks/person/person . You can compile that normally with bleep, write tests against it and so on.

Later

But the Copy API is much faster, so I would really like that to be supported and working. In fact that's how I do pretty much all inserts at $WORK now. I think if there are CSV escaping issues we should be able to solve that in doobie, no?

Sometime later I would prefer to 1) fix doobie, 2) port Text and whatever else is needed to anorm and zio-jdbc, 3) support COPY in typo.

[sbrunk]

Maybe we should just do that? Do you have any time to contribute towards that? What I would need is a proposal for the code which should be generated. It's pretty fast for me to put it into typo once that research phase is over. For instance implement it in code for Person, say here https://github.com/oyvindberg/typo/tree/main/typo-tester-doobie/generated-and-checked-in/adventureworks/person/person . You can compile that normally with bleep, write tests against it and so on.

I've tried a first draft in https://github.com/oyvindberg/typo/pull/64 for UsersRepo. Perhaps you could have a look if that goes into a direction you can work with.

But the Copy API is much faster, so I would really like that to be supported and working. In fact that's how I do pretty much all inserts at $WORK now. I think if there are CSV escaping issues we should be able to solve that in doobie, no?

Sometime later I would prefer to 1) fix doobie, 2) port Text and whatever else is needed to anorm and zio-jdbc, 3) support COPY in typo.

Completely agree that we should support the Copy API for batch inserts eventually. I actually tried that first manually on top of the typo generated row classes. I only had to provide Text instances for value classes (for id types) to get it compiling if I recall correctly. But I gave up (perhaps too early) when I got these escaping issues, saw that copy-manager is labeled experimental in doobie, and had a look at the escaping code. But maybe it's worth another shot :)

Long term I'd like to have something like https://github.com/adriangb/pgpq to actually support encoding inputs in the Postgres binary protocol for Copy. Well except if native ADBC for Postgres is there fast enough to make it obsolete :D

[sbrunk]

I take back my statement about the Copy issues. It was entirely my fault trying to get default values working. I got it working (without default values for now) on my schema and I'll try to get a working testcase for UsersRepo.

[sbrunk]

With #65 merged, is this done?

[oyvindberg]

Absolutely