1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
"""PersistentPg - persistent classic PyGreSQL connections.
Implements steady, thread-affine persistent connections to a PostgreSQL
database using the classic (not DB-API 2 compliant) PyGreSQL API.
This should result in a speedup for persistent applications such as the
application server of "Webware for Python," without loss of robustness.
Robustness is provided by using "hardened" SteadyPg connections.
Even if the underlying database is restarted and all connections
are lost, they will be automatically and transparently reopened.
However, since you don't want this to happen in the middle of a database
transaction, you must explicitly start transactions with the begin()
method so that SteadyPg knows that the underlying connection shall not
be replaced and errors passed on until the transaction is completed.
Measures are taken to make the database connections thread-affine.
This means the same thread always uses the same cached connection,
and no other thread will use it. So the fact that the classic PyGreSQL
pg module is not thread-safe at the connection level is no problem here.
For best performance, the application server should keep threads persistent.
For this, you have to set MinServerThreads = MaxServerThreads in Webware.
For more information on PostgreSQL, see:
https://www.postgresql.org/
For more information on PyGreSQL, see:
http://www.pygresql.org
For more information on Webware for Python, see:
https://webwareforpython.github.io/w4py/
Usage:
First you need to set up a generator for your kind of database connections
by creating an instance of PersistentPg, passing the following parameters:
maxusage: the maximum number of reuses of a single connection
(the default of 0 or None means unlimited reuse)
When this maximum usage number of the connection is reached,
the connection is automatically reset (closed and reopened).
setsession: An optional list of SQL commands that may serve to
prepare the session, e.g. ["set datestyle to german", ...]
closeable: if this is set to true, then closing connections will
be allowed, but by default this will be silently ignored
threadlocal: an optional class for representing thread-local data
that will be used instead of our Python implementation
(threading.local is faster, but cannot be used in all cases)
Additionally, you have to pass the parameters for the actual
PostgreSQL connection which are passed via PyGreSQL,
such as the names of the host, database, user, password etc.
For instance, if you want every connection to your local database 'mydb'
to be reused 1000 times:
from dbutils.persistent_pg import PersistentPg
persist = PersistentPg(5, dbname='mydb')
Once you have set up the generator with these parameters, you can
request database connections of that kind:
db = persist.connection()
You can use these connections just as if they were ordinary
classic PyGreSQL API connections. Actually what you get is the
hardened SteadyPg version of a classic PyGreSQL connection.
Closing a persistent connection with db.close() will be silently
ignored since it would be reopened at the next usage anyway and
contrary to the intent of having persistent connections. Instead,
the connection will be automatically closed when the thread dies.
You can change this behavior by setting the closeable parameter.
Note that you need to explicitly start transactions by calling the
begin() method. This ensures that the transparent reopening will be
suspended until the end of the transaction, and that the connection
will be rolled back before being reused in the same thread. To end
transactions, use one of the end(), commit() or rollback() methods.
By setting the threadlocal parameter to threading.local, getting
connections may become a bit faster, but this may not work in all
environments (for instance, mod_wsgi is known to cause problems
since it clears the threading.local data between requests).
Ideas for improvement:
* Add a thread for monitoring, restarting (or closing) bad or expired
connections (similar to DBConnectionPool/ResourcePool by Warren Smith).
* Optionally log usage, bad connections and exceeding of limits.
Copyright, credits and license:
* Contributed as supplement for Webware for Python and PyGreSQL
by Christoph Zwerschke in September 2005
* Based on an idea presented on the Webware developer mailing list
by Geoffrey Talvola in July 2005
Licensed under the MIT license.
"""
from . import __version__
from .steady_pg import SteadyPgConnection
try:
# Prefer the pure Python version of threading.local.
# The C implementation turned out to be problematic with mod_wsgi,
# since it does not keep the thread-local data between requests.
from _threading_local import local
except ImportError:
# Fall back to the default version of threading.local.
from threading import local
__all__ = ['PersistentPg']
class PersistentPg:
"""Generator for persistent classic PyGreSQL connections.
After you have created the connection pool, you can use
connection() to get thread-affine, steady PostgreSQL connections.
"""
version = __version__
def __init__(
self, maxusage=None, setsession=None,
closeable=False, threadlocal=None, *args, **kwargs):
"""Set up the persistent PostgreSQL connection generator.
maxusage: maximum number of reuses of a single connection
(0 or None means unlimited reuse)
When this maximum usage number of the connection is reached,
the connection is automatically reset (closed and reopened).
setsession: optional list of SQL commands that may serve to prepare
the session, e.g. ["set datestyle to ...", "set time zone ..."]
closeable: if this is set to true, then closing connections will
be allowed, but by default this will be silently ignored
threadlocal: an optional class for representing thread-local data
that will be used instead of our Python implementation
(threading.local is faster, but cannot be used in all cases)
args, kwargs: the parameters that shall be used to establish
the PostgreSQL connections using class PyGreSQL pg.DB()
"""
self._maxusage = maxusage
self._setsession = setsession
self._closeable = closeable
self._args, self._kwargs = args, kwargs
self.thread = (threadlocal or local)()
def steady_connection(self):
"""Get a steady, non-persistent PyGreSQL connection."""
return SteadyPgConnection(
self._maxusage, self._setsession, self._closeable,
*self._args, **self._kwargs)
def connection(self):
"""Get a steady, persistent PyGreSQL connection."""
try:
con = self.thread.connection
except AttributeError:
con = self.steady_connection()
self.thread.connection = con
return con
|
