Requiring a minimum of 9.2 was approved by techcom in T164898: PostgreSQL schema change for consistency with MySQL.

I'd lean towards just bumping our version

Me too. According to https://www.postgresql.org/support/versioning/, 8.3 has been unsupported since 2011.

We should probably go for at least 9.3 (released September 2013), which is the lowest version currently supported (until September 2018).

If we want our 1.31 LTS (which issupposed to remain supported until June 2021) to match PG's version support schedule, we should bump the supported version to 9.6 (released September 2016).

For more comparison, Debian jessie has 9.4 with 9.6 available in jessie-backports. Debian stretch (current stable) also has 9.6.

Change 421972 had a related patch set uploaded (by Legoktm; owner: MaxSem):
[mediawiki/core@master] Bump required Postgres version to 9.2

https://gerrit.wikimedia.org/r/421972

We can safely bump it for 1.31, but I'm not sure what we should be doing for 1.30, which is already released...is it already broken to the point where using < 9.1 doesn't work at all?

Change 421972 merged by jenkins-bot:
[mediawiki/core@master] Bump required Postgres version to 9.2

https://gerrit.wikimedia.org/r/421972

I believe it works except for the one schema change.

• rMW6260545feee3: Follow-up to '3D' file type addition added the schema change in question, that was broken both in this respect and in breaking update.php for fresh installations.
• rMWb559f73e54a3: Fix PostgreSQL patch-add-3d.sql by replacing it fixed the breakage for fresh installs, but didn't fix this.

So the current situation is that new installs are fine, but old installs on old PG can't be upgraded.

It looks like making that schema change work for 8.3 is complicated. I see two ways to do it:

• ALTER TABLE $table ALTER COLUMN $column TYPE text for every column that currently uses the enum type.
• Drop the type, then recreate it with the new value.
• ALTER TABLE $table ALTER COLUMN $column TYPE $type USING column::$type for every column again.

Or if we don't care about the actual name of the type in the database, we can do it with only one ALTER TABLE:

• Create the new version of the type under a different name.
• ALTER TABLE $table ALTER COLUMN $column TYPE $newtype USING column::text::$newtype for every column using the old type.
• Drop the old type.

But changing the type name makes future schema changes problematic. If we increase the 1.30 requirement from 8.3 to 8.4, we could use the new-in-8.4 ALTER TYPE $newtype RENAME TO $oldtype to keep the old name with the second method.

So probably the thing to do for 1.30 is to write some sort of hack for only this change, replacing the addPgEnumValue() added in rMWb559f73e54a3: Fix PostgreSQL patch-add-3d.sql by replacing it with something like (warning, untested)

public function doMediaTypeAdd3D() {
    $row = $this->db->selectRow(
        [
            't' => 'pg_catalog.pg_type',
            'n' => 'pg_catalog.pg_namespace',
            'e' => 'pg_catalog.pg_enum',
        ],
        [ 't.typname', 't.typtype', 'e.enumlabel' ],
        [
            't.typname' => 'media_type',
            'n.nspname' => $this->db->getCoreSchema(),
        ],
        __METHOD__,
        [],
        [
            'n' => [ 'JOIN', 't.typnamespace = n.oid' ],
            'e' => [ 'LEFT JOIN', [ 'e.enumtypid = t.oid', 'e.enumlabel' => '3D' ] ],
        ]
    );

    if ( !$row ) {
        $this->output( "...Type 'media_type' does not exist, skipping modify enum.\n" );
        return;
    } elseif ( $row->typtype !== 'e' ) {
        $this->output( "...Type 'media_type' does not seem to be an enum, skipping modify enum.\n" );
        return;
    } elseif ( $row->enumlabel === '3D' ) {
        $this->output( "...Enum type 'media_type' already contains value '3D'.\n" );
        return;
    }

    $this->output( "...Adding value '3D' to enum type 'media_type'.\n" );
    $etable = $this->db->realTableName( 'uploadstash' );
    try {
        $this->db->begin();
        $this->db->query( "ALTER TABLE $etable ALTER COLUMN us_media_type TYPE text" );
        $this->db->query( "DROP TYPE media_type" );
        $this->db->query( "CREATE TYPE media_type AS ENUM ('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE','3D')" );
        $this->db->query( "ALTER TABLE $etable ALTER COLUMN us_media_type TYPE media_type USING us_media_type::media_type" );
        $this->db->commit();
    } catch ( Exception $ex ) {
        $this->db->rollback();
        throw $ex;
    }
}

I'm removing this as a 1.31 blocker since that part has been fixed.

Can't we just increase the version requirement in 1.30 to 9.1? I don't think it's worth going to a lot of trouble to support 8.3 in an old branch.

Change 456063 had a related patch set uploaded (by Legoktm; owner: Legoktm):
[mediawiki/core@REL1_30] Explicitly require Postgres 9.1

https://gerrit.wikimedia.org/r/456063

Change 456063 merged by jenkins-bot:
[mediawiki/core@REL1_30] Explicitly require Postgres 9.1

https://gerrit.wikimedia.org/r/456063