Documenting read / write routing

[gilesw]

This might be a common use case that might want adding to the main documentation page or as an example.

[databases]
dev = host=<default> port=5439 dbname=dev
write = host=<write> port=5439 dbname=dev
read = host=<read> port=5439 dbname=dev

[pgbouncer]
pkt_buf = 32768
routing_rules_py_module_file = /usr/share/pgbouncer/routing_rules.py
rewrite_query_disconnect_on_failure = true

/usr/share/pgbouncer/routing_rules.py

def routing_rules(username, query):
    if "select" in query:
        return "read"
    elif "SELECT" in query:
        return "read"
    else:
        return "write"

I've timed a regex version and there is no meaningful difference in performance

import re
query_regex = re.compile(re.escape('select'), re.IGNORECASE)

if query_regex.search(query):
    print "read"
else:
    print "write"

Further to this is whether this is even a sensible use case or whether pgpool II would be a more performant solution for this specific requirement.

[rstrahan]

Thanks Giles. This looks like a good use case.
If it works for you, would it be possible for you to package it as an addition to the 'examples' directory & README, and send us a pull request. Thanks!

[rstrahan]

thread inactive - closing