IntegrityError for major_version_check with Postgres 9.1

RESOLVED FIXED in 29

Status

RESOLVED FIXED
6 years ago
6 years ago

People

(Reporter: rhelmer, Assigned: selenamarie)

Tracking

unspecified
Dependency tree / graph

Firefox Tracking Flags

(Not tracked)

Details

(Whiteboard: [qa-])

Attachments

(1 attachment)

(Reporter)

Description

6 years ago
IntegrityError: value for domain major_version violates check constraint "major_version_check"
(Reporter)

Updated

6 years ago
Blocks: 757274
Created attachment 639449 [details] [diff] [review]
Here's the necessary change (for PG 9.1)

Thanks Rob. I applied this locally and it made it work.
(Reporter)

Updated

6 years ago
Summary: major_version_check not working when loading sql/schema.sql into Postgres 9.1 → IntegrityError for major_version_check with Postgres 9.1
So, this problem is caused because the file schema.sql is designed to be loaded by psql, instead of by psycopg2.  As a result, there are a number of SET commands at the beginning of the schema.sql file which are being ignored by setupdb.py.  This one in particular is causing this issue:

SET standard_conforming_strings = off;

However, given how setupdb.py is run, it's not clear on why it's ignoring the SET statements.  It should be running them, and they should have the correct effect.  More debugging needed.
(Reporter)

Updated

6 years ago
Blocks: 795223
(Reporter)

Comment 3

6 years ago
(In reply to [:jberkus] Josh Berkus from comment #2)
> So, this problem is caused because the file schema.sql is designed to be
> loaded by psql, instead of by psycopg2.  As a result, there are a number of
> SET commands at the beginning of the schema.sql file which are being ignored
> by setupdb.py.  This one in particular is causing this issue:
> 
> SET standard_conforming_strings = off;
> 
> However, given how setupdb.py is run, it's not clear on why it's ignoring
> the SET statements.  It should be running them, and they should have the
> correct effect.  More debugging needed.

I think this might be a bug in psycopg2 - there is an API for this specific setting, and it just doesn't seem to work on a per-connection basis.

So, I think our options are:

1) fix the regex escaping so standard_conforming_strings=off is not needed
2) set this in the postgres config
3) have setupdb_app.py exec psql instead of using psycopg2

I have tested all three.

Is there any reason not go to with #1? I think we should just break compatibility with 9.0 for the latest schema, at least for dev environments (bug 795223). Once we are loading the schema from something other than a dump we'll have more flexibility, but this is breaking dev and anyone trying to install Socorro now, because 9.0 is getting harder to come by.

None of this really applies to production of course since we migrate it forward, so we can figure out ways to rollback a bad 9.2 upgrade (for example)
I can't think of a reason not to fix the regex escaping. +1 to fixing them.
This has been merged.
Assignee: nobody → sdeckelmann
Status: NEW → RESOLVED
Last Resolved: 6 years ago
Resolution: --- → FIXED
Whiteboard: [qa-]

Comment 7

6 years ago
Commit pushed to master at https://github.com/mozilla/socorro

https://github.com/mozilla/socorro/commit/b56d6e39739e0978dc1b6eada04a7aad49fbb6e6
bug 771237 fix regex escaping for domain and fix missing table
Target Milestone: --- → 28
Target Milestone: 28 → 29
You need to log in before you can comment on or make changes to this bug.