771237 - IntegrityError for major_version_check with Postgres 9.1

Status: RESOLVED FIXED

(Socorro :: Database, task)

Description

[Robert Helmer [:rhelmer]]

IntegrityError: value for domain major_version violates check constraint "major_version_check"

Comment 1

[Peter Bengtsson [:peterbe]]

Attachment: Here's the necessary change (for PG 9.1)

Thanks Rob. I applied this locally and it made it work.

Comment 2

[[:jberkus] Josh Berkus]

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.

Comment 3

[Robert Helmer [:rhelmer]]

(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)

Comment 4

[Selena Deckelmann :selenamarie :selena]

I can't think of a reason not to fix the regex escaping. +1 to fixing them.

Comment 5

[Selena Deckelmann :selenamarie :selena]

r? https://github.com/mozilla/socorro/pull/904

Comment 6

[Selena Deckelmann :selenamarie :selena]

This has been merged.

Comment 7

[[github robot]]

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