Database Reference
In-Depth Information
CREATE OR REPLACE FUNCTION login(
IN i_username text, IN i_pwdhash text,
OUT status int, OUT message text )
AS $$
import psycopg2
partitions = [
'dbname=chap9p0 port=5433',
'dbname=chap9p1 port=5433',
'dbname=chap9p2 port=5433',
'dbname=chap9p3 port=5433',
]
partition_nr = hash(i_username) & 3
con =
psycopg2.connect(partitions[partition_nr])
cur = con.cursor()
cur.execute('select * from login(%s,%s)', (
i_username, i_pwdhash))
status, message = cur.fetchone()
return (status, message)
$$ LANGUAGE plpythonu SECURITY DEFINER;
Here, we defined a set of four partition databases, given by their connect strings
stored as a list in variable
partitions
.
When executing the function, we first evaluate the hash function on the username
argument (
hash(i_username)
) and extract two bits from it (
& 3
) to get index into
the partitions list (the partition number) for executing each call.
Then, we open a connection to a partition database using the connect string selected
by
the
partition
number
(
con=psycop-
g2.connect(partitions[partition_nr])
).
Finally, we execute a remote query in the partition database and return the results of
this to the caller of this proxy function.
This works reasonably well if implemented like this, but also has at least two places
where it is suboptimal:
