Mercurial > p > roundup > code
annotate roundup/backends/back_postgresql.py @ 7668:5b41018617f2
fix: out of memory error when importing under postgresql
If you try importing more than 20k items under postgresql you can run
out of memory:
psycopg2.errors.OutOfMemory: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
Tuning memory may help, it's unknown at this point.
This checkin forces a commit to the postgres database after 10,000
rows have been added. This clears out the savepoints for each row and
starts a new transaction.
back_postgresql.py:
Implement commit mechanism in checkpoint_data(). Add two class level
attributes for tracking the number of savepoints and the limit when
the commit should happen.
roundup_admin.py:
implement pragma and dynamically create the config item
RDBMS_SAVEPOINT_LIMIT used by checkpoint_data.
Also fixed formatting of descriptions when using pragma list in
verbose mode.
admin_guide.txt, upgrading.txt:
Document change and use of pragma savepoint_limit in roundup-admin
for changing the default of 10,000.
test/db_test_base.py:
add some more asserts. In existing testAdminImportExport, set the
savepoint limit to 5 to test setting method and so that the commit
code will be run by existing tests. This provides coverage, but
does not actually test that the commit is done every 5 savepoints
8-(. The verification of every 5 savepoints was done manually
using a pdb breakpoint just before the commit.
acknowledgements.txt:
Added 2.4.0 section mentioning Norbert as he has done a ton of
testing with much larger datasets than I can test with.
| author | John Rouillard <rouilj@ieee.org> |
|---|---|
| date | Thu, 19 Oct 2023 16:11:25 -0400 |
| parents | b5062cb5c2a2 |
| children | 4af0d235b570 |
| rev | line source |
|---|---|
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
1 # Copyright (c) 2003 Martynas Sklyzmantas, Andrey Lebedev <andrey@micro.lt> |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
2 # |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
3 # This module is free software, and you may redistribute it and/or modify |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
4 # under the same terms as Python, so long as this copyright message and |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
5 # disclaimer are retained in their original form. |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
6 # |
|
5751
5cb6e6b594b0
issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents:
5319
diff
changeset
|
7 '''Postgresql backend via psycopg2 for Roundup.''' |
|
2005
fc52d57c6c3e
documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents:
1920
diff
changeset
|
8 __docformat__ = 'restructuredtext' |
|
fc52d57c6c3e
documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents:
1920
diff
changeset
|
9 |
| 6935 | 10 import logging |
| 11 import os | |
| 12 import shutil | |
| 13 import time | |
| 14 | |
|
4887
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
15 ISOLATION_LEVEL_READ_UNCOMMITTED = None |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
16 ISOLATION_LEVEL_READ_COMMITTED = None |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
17 ISOLATION_LEVEL_REPEATABLE_READ = None |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
18 ISOLATION_LEVEL_SERIALIZABLE = None |
|
5118
57452bc6d989
issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
19 |
| 6935 | 20 import psycopg2 # noqa: E402 |
| 21 from psycopg2 import ProgrammingError # noqa: E402 | |
| 22 from psycopg2.extensions import QuotedString # noqa: E402 | |
| 23 from psycopg2.extensions import ISOLATION_LEVEL_READ_UNCOMMITTED # noqa: F401 E402 | |
| 24 from psycopg2.extensions import ISOLATION_LEVEL_READ_COMMITTED # noqa: E402 | |
| 25 from psycopg2.extensions import ISOLATION_LEVEL_REPEATABLE_READ # noqa: E402 | |
| 26 from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE # noqa: E402 | |
| 27 from psycopg2.extensions import TransactionRollbackError # noqa: F401 E402 | |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
28 |
| 6935 | 29 from roundup import hyperdb # noqa: E402 |
| 30 from roundup.backends import rdbms_common # noqa: E402 | |
| 31 from roundup.backends import sessions_rdbms # noqa: E402 | |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
32 |
| 6935 | 33 isolation_levels = { |
| 34 'read uncommitted': ISOLATION_LEVEL_READ_COMMITTED, | |
| 35 'read committed': ISOLATION_LEVEL_READ_COMMITTED, | |
| 36 'repeatable read': ISOLATION_LEVEL_REPEATABLE_READ, | |
| 37 'serializable': ISOLATION_LEVEL_SERIALIZABLE | |
| 38 } | |
| 39 | |
|
4887
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
40 |
|
3099
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
41 def connection_dict(config, dbnamestr=None): |
|
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
42 ''' read_default_group is MySQL-specific, ignore it ''' |
|
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
43 d = rdbms_common.connection_dict(config, dbnamestr) |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4239
diff
changeset
|
44 if 'read_default_group' in d: |
|
3099
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
45 del d['read_default_group'] |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4239
diff
changeset
|
46 if 'read_default_file' in d: |
|
3099
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
47 del d['read_default_file'] |
|
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
48 return d |
|
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
49 |
| 6935 | 50 |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
51 def db_create(config): |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
52 """Clear all database contents and drop database itself""" |
| 6935 | 53 command = "CREATE DATABASE \"%s\" WITH ENCODING='UNICODE'" % config.RDBMS_NAME |
|
6332
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
54 if config.RDBMS_TEMPLATE: |
|
4471
4f353d71d716
Configuration issue:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4421
diff
changeset
|
55 command = command + " TEMPLATE=%s" % config.RDBMS_TEMPLATE |
|
4420
9655a1b65974
- more logger fixes -- use correct hierarchical logger names...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4357
diff
changeset
|
56 logging.getLogger('roundup.hyperdb').info(command) |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
57 db_command(config, command) |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
58 |
| 6935 | 59 |
|
5319
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5248
diff
changeset
|
60 def db_nuke(config): |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
61 """Clear all database contents and drop database itself""" |
| 6935 | 62 command = 'DROP DATABASE "%s"' % config.RDBMS_NAME |
|
4421
67bef70ab9b9
- more logger fixes, sorry for the noise.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4420
diff
changeset
|
63 logging.getLogger('roundup.hyperdb').info(command) |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
64 db_command(config, command) |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
65 |
|
2256
0b198ed096af
fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents:
2243
diff
changeset
|
66 if os.path.exists(config.DATABASE): |
|
0b198ed096af
fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents:
2243
diff
changeset
|
67 shutil.rmtree(config.DATABASE) |
|
0b198ed096af
fixes for py2.1 (booleans, sigh)
Richard Jones <richard@users.sourceforge.net>
parents:
2243
diff
changeset
|
68 |
| 6935 | 69 |
|
4514
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
70 def db_command(config, command, database='postgres'): |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
71 '''Perform some sort of database-level command. Retry 10 times if we |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
72 fail by conflicting with another user. |
|
4514
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
73 |
|
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
74 Since PostgreSQL version 8.1 there is a database "postgres", |
| 6935 | 75 before "template1" seems to have been used, so we fall back to it. |
|
4514
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
76 Compare to issue2550543. |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
77 ''' |
|
3099
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
78 template1 = connection_dict(config) |
|
4514
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
79 template1['database'] = database |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
80 |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
81 try: |
|
5751
5cb6e6b594b0
issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents:
5319
diff
changeset
|
82 conn = psycopg2.connect(**template1) |
|
5cb6e6b594b0
issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents:
5319
diff
changeset
|
83 except psycopg2.OperationalError as message: |
|
4514
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
84 if str(message).find('database "postgres" does not exist') >= 0: |
|
f6c49df25048
PostgreSQL backend minor improvement:
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4471
diff
changeset
|
85 return db_command(config, command, database='template1') |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4239
diff
changeset
|
86 raise hyperdb.DatabaseError(message) |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
87 |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
88 conn.set_isolation_level(0) |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
89 cursor = conn.cursor() |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
90 try: |
| 6935 | 91 for _n in range(10): |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
92 if pg_command(cursor, command): |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
93 return |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
94 finally: |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
95 conn.close() |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4239
diff
changeset
|
96 raise RuntimeError('10 attempts to create database failed') |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
97 |
| 6935 | 98 |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
99 def pg_command(cursor, command): |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
100 '''Execute the postgresql command, which may be blocked by some other |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
101 user connecting to the database, and return a true value if it succeeds. |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
102 |
|
3717
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
103 If there is a concurrent update, retry the command. |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
104 ''' |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
105 try: |
|
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
106 cursor.execute(command) |
|
5751
5cb6e6b594b0
issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents:
5319
diff
changeset
|
107 except psycopg2.DatabaseError as err: |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
108 response = str(err).split('\n')[0] |
|
6332
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
109 if "FATAL" not in response: |
|
5118
57452bc6d989
issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
110 msgs = ( |
|
3717
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
111 'is being accessed by other users', |
|
5770f1802cd0
better conflict retry in postgresql backend [SF#1552809]
Richard Jones <richard@users.sourceforge.net>
parents:
3715
diff
changeset
|
112 'could not serialize access due to concurrent update', |
|
5118
57452bc6d989
issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
113 ) |
|
6332
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
114 for msg in msgs: |
|
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
115 if msg in response: |
|
5118
57452bc6d989
issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
116 time.sleep(0.1) |
|
57452bc6d989
issue2550853 - better error handling and cleanup on some postgres
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
117 return 0 |
| 6935 | 118 raise RuntimeError(response) |
|
2243
20507f6759fc
postgresql backend altered to not use popen (thanks Georges Martin)
Richard Jones <richard@users.sourceforge.net>
parents:
2107
diff
changeset
|
119 return 1 |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
120 |
| 6935 | 121 |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
122 def db_exists(config): |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
123 """Check if database already exists""" |
|
3099
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
124 db = connection_dict(config, 'database') |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
125 try: |
|
5751
5cb6e6b594b0
issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents:
5319
diff
changeset
|
126 conn = psycopg2.connect(**db) |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
127 conn.close() |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
128 return 1 |
| 6935 | 129 except Exception: |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
130 return 0 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
131 |
| 6935 | 132 |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
133 class Sessions(sessions_rdbms.Sessions): |
|
3924
21d3d7eeea8c
assorted pyflakes fixes
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3920
diff
changeset
|
134 def set(self, *args, **kwargs): |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
135 try: |
|
3927
97ae174f7a0e
add self to Sessions.set
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3924
diff
changeset
|
136 sessions_rdbms.Sessions.set(self, *args, **kwargs) |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5118
diff
changeset
|
137 except ProgrammingError as err: |
|
3918
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
138 response = str(err).split('\n')[0] |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
139 if -1 != response.find('ERROR') and \ |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
140 -1 != response.find('could not serialize access due to concurrent update'): |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
141 # another client just updated, and we're running on |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
142 # serializable isolation. |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
143 # see http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
144 self.db.rollback() |
|
c8899c4bf6ad
safer session cleanup on postgres backends
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3863
diff
changeset
|
145 |
| 6935 | 146 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
147 class Database(rdbms_common.Database): |
|
5096
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
148 """Postgres DB backend implementation |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
149 |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
150 attributes: |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
151 dbtype: |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
152 holds the value for the type of db. It is used by indexer to |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
153 identify the database type so it can import the correct indexer |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
154 module when using native text search mode. |
|
7668
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
155 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
156 import_savepoint_count: |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
157 count the number of savepoints that have been created during |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
158 import. Once the limit of savepoints is reached, a commit is |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
159 done and this is reset to 0. |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
160 |
|
5096
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
161 """ |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
162 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
163 arg = '%s' |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
164 |
|
5096
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
165 dbtype = "postgres" |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
166 |
|
7668
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
167 import_savepoint_count = 0 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
168 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
169 # Value is set from roundup-admin using db.config["RDBMS_SAVEPOINT_LIMIT"] |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
170 # or to the default of 10_000 at runtime. Use 0 here to trigger |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
171 # initialization. |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
172 savepoint_limit = 0 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
173 |
|
3048
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
2745
diff
changeset
|
174 # used by some code to switch styles of query |
|
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
2745
diff
changeset
|
175 implements_intersect = 1 |
|
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
2745
diff
changeset
|
176 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
177 def sql_open_connection(self): |
|
3099
519b92df37dc
handle ~/.my.cnf files for MySQL defaults [SF#1096031]
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
178 db = connection_dict(self.config, 'database') |
|
4421
67bef70ab9b9
- more logger fixes, sorry for the noise.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4420
diff
changeset
|
179 logging.getLogger('roundup.hyperdb').info( |
| 6935 | 180 'open database %r' % db['database']) |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
181 try: |
|
5751
5cb6e6b594b0
issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents:
5319
diff
changeset
|
182 conn = psycopg2.connect(**db) |
|
5cb6e6b594b0
issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents:
5319
diff
changeset
|
183 except psycopg2.OperationalError as message: |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4239
diff
changeset
|
184 raise hyperdb.DatabaseError(message) |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
185 |
|
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
186 cursor = conn.cursor() |
|
4887
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
187 if ISOLATION_LEVEL_REPEATABLE_READ is not None: |
| 6935 | 188 lvl = isolation_levels[self.config.RDBMS_ISOLATION_LEVEL] |
|
4887
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4787
diff
changeset
|
189 conn.set_isolation_level(lvl) |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
190 |
|
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
191 return (conn, cursor) |
|
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
192 |
|
6332
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
193 def sql_new_cursor(self, name='default', conn=None, *args, **kw): |
|
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
194 """ Create new cursor, this may need additional parameters for |
|
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
195 performance optimization for different backends. |
|
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
196 """ |
|
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
197 use_name = self.config.RDBMS_SERVERSIDE_CURSOR |
|
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
198 kw = {} |
|
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
199 if use_name: |
|
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
200 kw['name'] = name |
|
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
201 if conn is None: |
|
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
202 conn = self.conn |
|
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
203 return conn.cursor(*args, **kw) |
|
6a6b4651be1f
Use server-side cursor for postgres in some cases
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5751
diff
changeset
|
204 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
205 def open_connection(self): |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
206 if not db_exists(self.config): |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
207 db_create(self.config) |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
208 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
209 self.conn, self.cursor = self.sql_open_connection() |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
210 |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
211 try: |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
212 self.load_dbschema() |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5118
diff
changeset
|
213 except ProgrammingError as message: |
|
3739
f1363e19121a
fix error detection for non-EN locales of postgres [SF#1592249]
Richard Jones <richard@users.sourceforge.net>
parents:
3717
diff
changeset
|
214 if str(message).find('schema') == -1: |
|
2736
402d6d556558
postgres backend open doesn't hide corruption in schema [SF#956375]
Richard Jones <richard@users.sourceforge.net>
parents:
2721
diff
changeset
|
215 raise |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
216 self.rollback() |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
217 self.init_dbschema() |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
218 self.sql("CREATE TABLE schema (schema TEXT)") |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
219 self.sql("CREATE TABLE dual (dummy integer)") |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
220 self.sql("insert into dual values (1)") |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
221 self.create_version_2_tables() |
|
6587
4f8fc55730e1
Add words_both_idx to newly created databases.
John Rouillard <rouilj@ieee.org>
parents:
6433
diff
changeset
|
222 self.fix_version_3_tables() |
|
5319
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5248
diff
changeset
|
223 # Need to commit here, otherwise otk/session will not find |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5248
diff
changeset
|
224 # the necessary tables (in a parallel connection!) |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5248
diff
changeset
|
225 self.commit() |
|
6604
0d99ae7c8de6
Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents:
6599
diff
changeset
|
226 self._add_fts_table() |
|
0d99ae7c8de6
Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents:
6599
diff
changeset
|
227 self.commit() |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
228 |
|
6610
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
229 def checkpoint_data(self, savepoint="importing"): |
|
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
230 """Create a subtransaction savepoint. Allows recovery/retry |
|
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
231 of operation in exception handler because |
|
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
232 postgres requires a rollback in case of error |
|
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
233 generating exception. Used with |
|
7668
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
234 restore_connection_on_error to handle uniqueness |
|
6610
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
235 conflict in import_table(). |
|
7668
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
236 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
237 Savepoints take memory resources. Postgres keeps all |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
238 savepoints (rather than overwriting) until a |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
239 commit(). Commit every ~10,000 savepoints to prevent |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
240 running out of memory on import. |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
241 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
242 NOTE: a commit outside of this method will not reset the |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
243 import_savepoint_count. This can result in an unneeded |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
244 commit on a new cursor (that has no savepoints) as there is |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
245 no way to find out if there is a savepoint or how many |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
246 savepoints are opened on a db connection/cursor. |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
247 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
248 Because an import is a one shot deal and not part of a long |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
249 running daemon (e.g. the roundup-server), I am not too |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
250 worried about it. It will just slow the import down a tad. |
|
6433
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6332
diff
changeset
|
251 """ |
|
7668
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
252 |
|
6610
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
253 self.sql('SAVEPOINT %s' % savepoint) |
|
6433
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6332
diff
changeset
|
254 |
|
7668
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
255 self.import_savepoint_count += 1 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
256 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
257 if not self.savepoint_limit: |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
258 if "RDBMS_SAVEPOINT_LIMIT" in self.config.keys(): |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
259 # note this config option is created on the fly |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
260 # by admin.py::do_import. It is never listed in |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
261 # config.ini. |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
262 self.savepoint_limit = self.config["RDBMS_SAVEPOINT_LIMIT"] |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
263 else: |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
264 self.savepoint_limit = 10000 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
265 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
266 if self.import_savepoint_count > self.savepoint_limit: |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
267 # track savepoints and commit every 10000 (or user value) |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
268 # so we don't run postgres out of memory. An import of a |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
269 # customer's tracker ran out of memory after importing |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
270 # ~23000 items with: psycopg2.errors.OutOfMemory: out of |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
271 # shared memory HINT: You might need to increase |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
272 # max_locks_per_transaction. |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
273 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
274 self.commit() |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
275 self.import_savepoint_count = 0 |
|
5b41018617f2
fix: out of memory error when importing under postgresql
John Rouillard <rouilj@ieee.org>
parents:
6935
diff
changeset
|
276 |
|
6610
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
277 def restore_connection_on_error(self, savepoint="importing"): |
|
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
278 """Postgres leaves a connection/cursor in an unusable state |
|
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
279 after an error. Rollback the transaction to a |
|
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
280 previous savepoint and permit a retry of the |
|
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
281 failed statement. Used with checkpoint_data to |
|
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
282 handle uniqueness conflict in import_table(). |
|
6433
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6332
diff
changeset
|
283 """ |
|
6610
db3f0ba75b4a
Change checkpoint_data and restore_connection_on_error to subtransaction
John Rouillard <rouilj@ieee.org>
parents:
6604
diff
changeset
|
284 self.sql('ROLLBACK TO %s' % savepoint) |
|
6433
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6332
diff
changeset
|
285 |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
286 def create_version_2_tables(self): |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
287 # OTK store |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
288 self.sql('''CREATE TABLE otks (otk_key VARCHAR(255), |
|
6806
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6610
diff
changeset
|
289 otk_value TEXT, otk_time float)''') |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
290 self.sql('CREATE INDEX otks_key_idx ON otks(otk_key)') |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
291 |
|
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
292 # Sessions store |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
293 self.sql('''CREATE TABLE sessions ( |
|
6806
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6610
diff
changeset
|
294 session_key VARCHAR(255), session_time float, |
|
2721
1cd01cf106e1
extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents:
2719
diff
changeset
|
295 session_value TEXT)''') |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
296 self.sql('''CREATE INDEX sessions_key_idx ON |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
297 sessions(session_key)''') |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
298 |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
299 # full-text indexing store |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
300 self.sql('CREATE SEQUENCE ___textids_ids') |
|
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
301 self.sql('''CREATE TABLE __textids ( |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
302 _textid integer primary key, _class VARCHAR(255), |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
303 _itemid VARCHAR(255), _prop VARCHAR(255))''') |
|
6593
e70e2789bc2c
issue2551189 - increase text search maxlength
John Rouillard <rouilj@ieee.org>
parents:
6587
diff
changeset
|
304 self.sql('''CREATE TABLE __words (_word VARCHAR(%s), |
|
e70e2789bc2c
issue2551189 - increase text search maxlength
John Rouillard <rouilj@ieee.org>
parents:
6587
diff
changeset
|
305 _textid integer)''' % (self.indexer.maxlength + 5)) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
306 self.sql('CREATE INDEX words_word_idx ON __words(_word)') |
|
3858
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3739
diff
changeset
|
307 self.sql('CREATE INDEX words_by_id ON __words (_textid)') |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3739
diff
changeset
|
308 self.sql('CREATE UNIQUE INDEX __textids_by_props ON ' |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3739
diff
changeset
|
309 '__textids (_class, _itemid, _prop)') |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
310 |
|
2413
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
311 def fix_version_2_tables(self): |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
312 # Convert journal date column to TIMESTAMP, params column to TEXT |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
313 self._convert_journal_tables() |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
314 |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
315 # Convert all String properties to TEXT |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
316 self._convert_string_properties() |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
317 |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
318 # convert session / OTK *_time columns to REAL |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
319 for name in ('otk', 'session'): |
| 6935 | 320 self.sql('drop index %ss_key_idx' % name) |
| 321 self.sql('drop table %ss' % name) | |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
322 self.sql('''CREATE TABLE %ss (%s_key VARCHAR(255), |
| 6935 | 323 %s_value VARCHAR(255), %s_time REAL)''' % (name, name, |
| 324 name, name)) | |
| 325 self.sql('CREATE INDEX %ss_key_idx ON %ss(%s_key)' % (name, name, | |
| 326 name)) | |
|
2413
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
327 |
|
2745
b284ff7af3fb
add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents:
2736
diff
changeset
|
328 def fix_version_3_tables(self): |
|
b284ff7af3fb
add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents:
2736
diff
changeset
|
329 rdbms_common.Database.fix_version_3_tables(self) |
|
b284ff7af3fb
add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents:
2736
diff
changeset
|
330 self.sql('''CREATE INDEX words_both_idx ON public.__words |
|
b284ff7af3fb
add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents:
2736
diff
changeset
|
331 USING btree (_word, _textid)''') |
|
b284ff7af3fb
add optimised index on postgresql text index table
Richard Jones <richard@users.sourceforge.net>
parents:
2736
diff
changeset
|
332 |
|
6604
0d99ae7c8de6
Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents:
6599
diff
changeset
|
333 def _add_fts_table(self): |
| 6935 | 334 self.sql( |
| 335 'CREATE TABLE __fts (_class VARCHAR(255), ' | |
| 336 '_itemid VARCHAR(255), _prop VARCHAR(255), _tsv tsvector)' | |
|
6604
0d99ae7c8de6
Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents:
6599
diff
changeset
|
337 ) |
|
0d99ae7c8de6
Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents:
6599
diff
changeset
|
338 |
|
0d99ae7c8de6
Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents:
6599
diff
changeset
|
339 self.sql('CREATE INDEX __fts_idx ON __fts USING GIN (_tsv)') |
|
0d99ae7c8de6
Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents:
6599
diff
changeset
|
340 |
|
6599
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
341 def fix_version_6_tables(self): |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
342 # Modify length for __words._word column. |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
343 c = self.cursor |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
344 sql = 'alter table __words alter column _word type varchar(%s)' % ( |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
345 self.arg) |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
346 # Why magic number 5? It was the original offset between |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
347 # column length and maxlength. |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
348 c.execute(sql, (self.indexer.maxlength + 5,)) |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
349 |
|
6604
0d99ae7c8de6
Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents:
6599
diff
changeset
|
350 self._add_fts_table() |
|
0d99ae7c8de6
Allow Roundup to use PostgreSQL database native full text search
John Rouillard <rouilj@ieee.org>
parents:
6599
diff
changeset
|
351 |
|
6806
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6610
diff
changeset
|
352 def fix_version_7_tables(self): |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6610
diff
changeset
|
353 # Modify type for session.session_time/otk.otk_time column. |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6610
diff
changeset
|
354 # float is double precision 15 signifcant digits |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6610
diff
changeset
|
355 sql = 'alter table sessions alter column session_time type float' |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6610
diff
changeset
|
356 self.sql(sql) |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6610
diff
changeset
|
357 sql = 'alter table otks alter column otk_time type float' |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6610
diff
changeset
|
358 self.sql(sql) |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6610
diff
changeset
|
359 |
|
6913
b0dbc13a835a
fix missed renaming from revision 98d3bf8ffb19 done 2004-04-18
John Rouillard <rouilj@ieee.org>
parents:
6806
diff
changeset
|
360 def add_new_columns_v2(self): |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
361 # update existing tables to have the new actor column |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
362 tables = self.database_schema['tables'] |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4239
diff
changeset
|
363 for name in tables: |
| 6935 | 364 self.sql('ALTER TABLE _%s add __actor VARCHAR(255)' % name) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
365 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
366 def __repr__(self): |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
367 return '<roundpsycopgsql 0x%x>' % id(self) |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
368 |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
369 def sql_stringquote(self, value): |
|
5751
5cb6e6b594b0
issue2551040: New release of psycopg2 drops support for psycopg1
John Rouillard <rouilj@ieee.org>
parents:
5319
diff
changeset
|
370 ''' psycopg2.QuotedString returns a "buffer" object with the |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
371 single-quotes around it... ''' |
|
3715
50add98cbbac
change as per last message in patch [SF#1429391]
Richard Jones <richard@users.sourceforge.net>
parents:
3687
diff
changeset
|
372 return str(QuotedString(str(value)))[1:-1] |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
373 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
374 def sql_index_exists(self, table_name, index_name): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
375 sql = 'select count(*) from pg_indexes where ' \ |
| 6935 | 376 'tablename=%s and indexname=%s' % (self.arg, self.arg) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
377 self.sql(sql, (table_name, index_name)) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
378 return self.cursor.fetchone()[0] |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
379 |
|
2424
74474ec41050
argh! backwards compat
Richard Jones <richard@users.sourceforge.net>
parents:
2413
diff
changeset
|
380 def create_class_table(self, spec, create_sequence=1): |
|
2413
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2256
diff
changeset
|
381 if create_sequence: |
| 6935 | 382 sql = 'CREATE SEQUENCE _%s_ids' % spec.classname |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
383 self.sql(sql) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
384 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
385 return rdbms_common.Database.create_class_table(self, spec) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
386 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
387 def drop_class_table(self, cn): |
| 6935 | 388 sql = 'drop table _%s' % cn |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
389 self.sql(sql) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
390 |
| 6935 | 391 sql = 'drop sequence _%s_ids' % cn |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
392 self.sql(sql) |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
393 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
394 def newid(self, classname): |
| 6935 | 395 sql = "select nextval('_%s_ids') from dual" % classname |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
396 self.sql(sql) |
|
3977
732a37da3a10
Fix for postgres 8.3 compatibility (and bug) (patch [SF#2030479])
Richard Jones <richard@users.sourceforge.net>
parents:
3927
diff
changeset
|
397 return str(self.cursor.fetchone()[0]) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
398 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
399 def setid(self, classname, setid): |
| 6935 | 400 sql = "select setval('_%s_ids', %s) from dual" % (classname, |
| 401 int(setid)) | |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
402 self.sql(sql) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
403 |
|
3310
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
404 def clear(self): |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
405 rdbms_common.Database.clear(self) |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
406 |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
407 # reset the sequences |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4239
diff
changeset
|
408 for cn in self.classes: |
| 6935 | 409 self.cursor.execute('DROP SEQUENCE _%s_ids' % cn) |
| 410 self.cursor.execute('CREATE SEQUENCE _%s_ids' % cn) | |
| 411 | |
|
3310
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
412 |
|
3685
4d9adb8bc3b1
Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3659
diff
changeset
|
413 class PostgresqlClass: |
|
4d9adb8bc3b1
Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3659
diff
changeset
|
414 order_by_null_values = '(%s is not NULL)' |
|
4787
4a017661e414
Closed issue2550805 (Postgresql should search title case insensitive), by Tom Ekberg.
Bernhard Reiter <bernhard@intevation.de>
parents:
4570
diff
changeset
|
415 case_insensitive_like = 'ILIKE' |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
416 |
| 6935 | 417 |
|
3685
4d9adb8bc3b1
Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3659
diff
changeset
|
418 class Class(PostgresqlClass, rdbms_common.Class): |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
419 pass |
| 6935 | 420 |
| 421 | |
|
3685
4d9adb8bc3b1
Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3659
diff
changeset
|
422 class IssueClass(PostgresqlClass, rdbms_common.IssueClass): |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
423 pass |
| 6935 | 424 |
| 425 | |
|
3685
4d9adb8bc3b1
Null-value sorting fixes:
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3659
diff
changeset
|
426 class FileClass(PostgresqlClass, rdbms_common.FileClass): |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
427 pass |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
428 |
|
3920
416606b09b27
fix vim modelines
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3918
diff
changeset
|
429 # vim: set et sts=4 sw=4 : |
