Special behavior invocation via comment instead of a custom SQL

[ankurcha]

Is your feature request related to a problem? Please describe.
The custom SQL introduced for selecting shard and replicas is functional but it makes transition to a sharded environment and testing less transparent. It would be better if we could follow https://github.com/ossc-db/pg_hint_plan kind of approach where the SET SHARDING KEY TO ... and SET SERVER ROLE TO '(PRIMARY|REPLICA|ANY|AUTO|DEFAULT)' is into comments like:

/*+ SET SERVER ROLE TO '(PRIMARY|REPLICA|ANY|AUTO|DEFAULT)' +*/

to set the right behaior.

Describe the solution you'd like
Use comment based way of enabling shard / replica selection.

Describe alternatives you've considered
separate pools and application logic.

Additional context

• https://pghintplan.osdn.jp/pg_hint_plan.html

[ankurcha]

Refers to: #165

[levkk]

Agreed! One small issue I ran into so far is ORMs, e.g. ActiveRecord, will start a transaction automatically if you create/save a model, but it won't put the comment into the BEGIN statement; PgCat won't know which shard to talk to until it's too late. rails/rails#46032

For others, e.g. Django, this is not an issue because transactions are not started automatically.

Any ideas are welcome! We will probably just modify ActiveRecord on our side and run with the comment approach, but we'd like to make this easy to use for everyone.

@ankurcha , what ORM, if any, are you using for your apps?

[ankurcha]

I am using golang without any ORM. For ORMs like activerecord we would likely need to do some monkey patching to prepend the sql. In my experience, adding comments to annotate sql is probably going to be difficult to consistently added without some custom implementation for each one.

You could also think about wrapping the sql driver for each supported language.

[jmeagher]

There's a lot of overlap between this and what I'm working on in #293 to get around the Rails comment limitation. We solved that on the Rails side with a custom DB adapter. I'll see what I can do to merge the ideas from this and #165 into one solution that works in general.

[DeoLeung]

wondering how to use shard comment in sqlalchemy transaction
https://stackoverflow.com/questions/76563760/how-to-add-comment-before-begin-statement-in-sqlalchemy-for-postgres

[n-oden]

Just adding my voice to the chorus here: implementing this would make transitioning to pgcat from pgpool-II substantially simpler. PGPool uses comments to allow clients to pick whether they will be normally load-balanced or sent directly to the primary instance: https://www.pgpool.net/docs/42/en/html/runtime-config-load-balancing.html -- adding another comment on top of that one is trivial, but using a sql-ish statement that can only be parsed by pgcat makes the cutover process a lot trickier.