<ThomasLocke> Hey guys. Trying to update to 1.18.1 from 1.15.5 but am getting stuck at the database error: A database query syntax error has occurred. The last attempted database query was: "ALTER TABLE externallinks DROP CONSTRAINT ".

<Reedy> ThomasLocke, do you get a message the line before saying "Altering column "...
<Reedy> If so, can you paste the whole line?
<Reedy> I can see where iit's seemingly happening
<ThomasLocke> Altering column 'externallinks.el_from' to be DEFERRABLE INITIALLY DEFERRED
<ThomasLocke> ALTER /* */ TABLE externallinks DROP CONSTRAINT
<Reedy> the offending line is (in trunk at least) line 513 of includes/installer/PostgresUpdater.php
<Reedy> it unconditionally used $fi->conname() no matter it's value

Getting the user to do:

SELECT attnotnull, attlen, COALESCE(conname, '') AS conname, COALESCE(condeferred, 'f') AS deferred, COALESCE(condeferrable, 'f') AS deferrable, CASE WHEN typname = 'int2' THEN 'smallint' WHEN typname = 'int4' THEN 'integer' WHEN typname = 'int8' THEN 'bigint' WHEN typname = 'bpchar' THEN 'char' ELSE typname END AS typname FROM pg_class c JOIN pg_namespace n ON (n.oid = c.relnamespace) JOIN pg_attribute a ON (a.attrelid = c.oid) JOIN pg_type t ON (t.oid = a.atttypid) LEFT JOIN pg_constraint o ON (o.conrelid = c.oid AND a.attnum = ANY(o.conkey) AND o.contype = 'f') WHERE relkind = 'r' AND nspname='mediawiki' AND relname='externallinks' AND attname='elfrom';

via sql.php gave "Query OK, 0 row(s)"...

... AND atname='el_from'; ?

Can't reproduce;

On a fresh MediaWiki 1.15.5 install on PostgreSQL 9.1.3 the query returns:

SELECT attnotnull, attlen, COALESCE(conname, '') AS conname,
COALESCE(condeferred, 'f') AS deferred, COALESCE(condeferrable, 'f') AS
deferrable, CASE WHEN typname = 'int2' THEN 'smallint' WHEN typname = 'int4'
THEN 'integer' WHEN typname = 'int8' THEN 'bigint' WHEN typname = 'bpchar' THEN
'char' ELSE typname END AS typname FROM pg_class c JOIN pg_namespace n ON
(n.oid = c.relnamespace) JOIN pg_attribute a ON (a.attrelid = c.oid) JOIN
pg_type t ON (t.oid = a.atttypid) LEFT JOIN pg_constraint o ON (o.conrelid =
c.oid AND a.attnum = ANY(o.conkey) AND o.contype = 'f') WHERE relkind = 'r' AND
nspname='mediawiki' AND relname='externallinks' AND attname='el_from';

------+--------+----------------------------+----------+------------+---------
t | 4 | externallinks_el_from_fkey | f | f | integer

Would be good to know which PostgreSQL version is installed there (and preferably on which the original was installed).

This query works for me even as above
on MediaWiki 1.7.3 installed on PostgreSQL 8.2.23

Please reopen if there is more information.

I think I nailed it down when testing for gerrit change 3841. It happens when updater invokes

array( 'changeFkeyDeferrable', 'externallinks', 'el_from', 'page(page_id) ON DELETE CASCADE' ),

but there is no foreign key constraint on el_from.

It's strange since I could trace this definition:

el_from   INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,

back to this commit:

commit 6832afd9ea7d280e0e63648424de26e6ead6bf56
Author: Brion Vibber <brion@users.mediawiki.org>
Date: Sun Apr 30 17:36:16 2006 +0000

Somehow managed to forget to check this in...

So it seems like the constraint should always be there.

There is a small issue though with PostgresUpdater::changeFkeyDeferrable and DatabasePostgres::FieldInfo::conname()

conname() returns empty string instead of NULL because of
COALESCE(conname, '') AS conname

easy fix...

Submitted gerrit change 3889 to propose a fix.

commit fa6ab166863f911e927720878763cda7e945f96d

Change-Id: I7ca351e07d228afdf4a5c3bef365f42a27c9ac45

Migrating from the old tracking task to a tag for PostgreSQL-related tasks.