Mercurial > p > roundup > code
comparison roundup/backends/back_postgresql.py @ 6806:bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
The data types used to represent timestamps in pg and mysql for
ephemeral tables: sessions and otks don't have enough signifcant
digits to work. As a result the timestamps are rounduped (up/down)
rsuling in the stored timestamp being 2 minutes (pg) or 2-3
hours(mysql) off from what it should be.
Modify db schema to use a numeric type that preserves more significant
figures. Implement schema upgrade. Document need for upgrade in
upgrading.txt.
Write tests for schema upgrade.
Implement test for updateTimestamp method on BasicDatabase that showed
this issue in the first place. Write overrides for test for
anydbm/memorydb which store timestamp properly or not at all.
| author | John Rouillard <rouilj@ieee.org> |
|---|---|
| date | Mon, 25 Jul 2022 17:20:20 -0400 |
| parents | db3f0ba75b4a |
| children | b0dbc13a835a |
comparison
equal
deleted
inserted
replaced
| 6805:09d9c646ca89 | 6806:bdd28b244839 |
|---|---|
| 232 self.sql('ROLLBACK TO %s' % savepoint) | 232 self.sql('ROLLBACK TO %s' % savepoint) |
| 233 | 233 |
| 234 def create_version_2_tables(self): | 234 def create_version_2_tables(self): |
| 235 # OTK store | 235 # OTK store |
| 236 self.sql('''CREATE TABLE otks (otk_key VARCHAR(255), | 236 self.sql('''CREATE TABLE otks (otk_key VARCHAR(255), |
| 237 otk_value TEXT, otk_time REAL)''') | 237 otk_value TEXT, otk_time float)''') |
| 238 self.sql('CREATE INDEX otks_key_idx ON otks(otk_key)') | 238 self.sql('CREATE INDEX otks_key_idx ON otks(otk_key)') |
| 239 | 239 |
| 240 # Sessions store | 240 # Sessions store |
| 241 self.sql('''CREATE TABLE sessions ( | 241 self.sql('''CREATE TABLE sessions ( |
| 242 session_key VARCHAR(255), session_time REAL, | 242 session_key VARCHAR(255), session_time float, |
| 243 session_value TEXT)''') | 243 session_value TEXT)''') |
| 244 self.sql('''CREATE INDEX sessions_key_idx ON | 244 self.sql('''CREATE INDEX sessions_key_idx ON |
| 245 sessions(session_key)''') | 245 sessions(session_key)''') |
| 246 | 246 |
| 247 # full-text indexing store | 247 # full-text indexing store |
| 294 # column length and maxlength. | 294 # column length and maxlength. |
| 295 c.execute(sql, (self.indexer.maxlength + 5,)) | 295 c.execute(sql, (self.indexer.maxlength + 5,)) |
| 296 | 296 |
| 297 self._add_fts_table() | 297 self._add_fts_table() |
| 298 | 298 |
| 299 def fix_version_7_tables(self): | |
| 300 # Modify type for session.session_time/otk.otk_time column. | |
| 301 # float is double precision 15 signifcant digits | |
| 302 sql = 'alter table sessions alter column session_time type float' | |
| 303 self.sql(sql) | |
| 304 sql = 'alter table otks alter column otk_time type float' | |
| 305 self.sql(sql) | |
| 306 | |
| 299 def add_actor_column(self): | 307 def add_actor_column(self): |
| 300 # update existing tables to have the new actor column | 308 # update existing tables to have the new actor column |
| 301 tables = self.database_schema['tables'] | 309 tables = self.database_schema['tables'] |
| 302 for name in tables: | 310 for name in tables: |
| 303 self.sql('ALTER TABLE _%s add __actor VARCHAR(255)'%name) | 311 self.sql('ALTER TABLE _%s add __actor VARCHAR(255)'%name) |