[3.0]: PostgreSQL: the upgrader cannot finish, and unapproving a post errors

[albertlast]

Basic Information

A forum installed on PostgreSQL cannot be brought up to date: the upgrader stops on the first of several statements that are either MySQL-only or illegal against a table that already has rows. Each run gets a little further, because the migrations in front partially apply, and then dies on the next one.

1. Improving search results storage

ERROR:  syntax error at or near "PRIMARY" at character 57
STATEMENT:  ALTER TABLE smf_log_search_results
                        DROP CONSTRAINT PRIMARY

PRIMARY is not a constraint name in PostgreSQL; dropping a primary key there needs the real constraint name (smf_log_search_results_pkey), or DROP CONSTRAINT looked up from pg_constraint.

2. Adding support for recurring events

ERROR:  column "end_time" of relation "smf_calendar" contains null values
STATEMENT:  ALTER TABLE smf_calendar
                ALTER COLUMN end_time SET NOT NULL

3. The general case behind (2): adding a NOT NULL column with a default to a table that already has rows.

PostgreSQL::add_column() emits the column bare —

'ALTER TABLE ' . $short_table_name . '
ADD COLUMN ' . $column_info['name'] . ' ' . $type . $generated,

— and then hands not_null and default to change_column(), which sets the default and afterwards SET NOT NULL. In PostgreSQL ALTER COLUMN … SET DEFAULT does not backfill existing rows, so every existing row is still NULL when the constraint arrives and the statement fails. MySQL is unaffected because its add_column() emits the whole definition in one statement.

Anything that adds such a column is therefore broken on PostgreSQL, and it leaves the table half-changed: the new column exists, nullable and empty, and the migration is not re-runnable — a second attempt fails at exactly the same statement.

4. Unapproving a post fails at runtime (not the upgrader).

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
STATEMENT:  INSERT INTO smf_approval_queue("id_msg")
                    VALUES
                        (13) ON CONFLICT (id_msg) DO NOTHING

smf_approval_queue has no unique index on id_msg, so the ignore insert method has nothing to conflict on.

Steps to reproduce

1. Install SMF 3.0 Alpha 4 on PostgreSQL and use it a little, so the tables are not empty.
2. Run the upgrader.
3. Run it again after it stops.

For (4): turn post moderation on, then unapprove a reply from the topic view.

Expected result

The upgrader walks through every migration and finishes. Unapproving a post queues it.

Actual result

The upgrader stops at the first offending statement, and re-running does not get past it. Unapproving a post writes a database error to the log.

Version/Git revision

3.0 Alpha 4, release-3.0 at bfbca5b

Database Engine

PostgreSQL

Database Version

PostgreSQL 17

PHP Version

8.4.24

Logs

2026-08-14 11:54:34 UTC ERROR:  syntax error at or near "PRIMARY" at character 57
2026-08-14 11:54:34 UTC STATEMENT:  ALTER TABLE smf_log_search_results
                        DROP CONSTRAINT PRIMARY
2026-08-14 11:54:45 UTC ERROR:  column "end_time" of relation "smf_calendar" contains null values
2026-08-14 11:54:45 UTC STATEMENT:  ALTER TABLE smf_calendar
                ALTER COLUMN end_time SET NOT NULL
2026-08-14 12:00:31 UTC ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
2026-08-14 12:00:31 UTC STATEMENT:  INSERT INTO smf_approval_queue("id_msg")
                    VALUES
                        (13) ON CONFLICT (id_msg) DO NOTHING

Additional Information

Found while testing #9517. Point (3) is what makes that PR's BoardPostsCount migration fail on PostgreSQL as well; I have noted it on the PR, but the root cause is in the schema layer rather than in any one migration, so fixing it here would fix that one too.

[albertlast]

I have since run a real 2.1 → 3.0 upgrade on PostgreSQL, using the committed SMF 2.1.7 baseline from #9330 (403 members, 6 000 messages, 24 boards), rather than the near-empty forum I opened this with. That gets much further into the migration list and turns up three more blockers, plus a sharper version of point (3) above.

Taking them in the order the upgrader hits them:

AlertsObsolete uses MySQL-only update syntax

Sources/Maintenance/Migration/v2_1/AlertsObsolete.php:108:

UPDATE {db_prefix}user_alerts AS a
JOIN {db_prefix}attachments AS f
    ON (f.id_attach = a.content_id)
SET
    a.content_type = {literal:msg},
    ...

ERROR:  syntax error at or near "JOIN" at character 32

PostgreSQL wants UPDATE … SET … FROM … WHERE.

PostgreSqlSchemaDiff reads a property that does not exist

Sources/Maintenance/Migration/v2_1/PostgreSqlSchemaDiff.php declares private array $schema_fixes at line 43, loops on it at line 131, and then reads a different name at line 132:

while (Maintenance::getCurrentStart() < \count($this->schema_fixes)) {
    $fix = $this->schemaFixes[Maintenance::getCurrentStart()];

So $fix is null, every one of the ~30 fixes in the list is skipped, and the query built from it is an ALTER TABLE with nothing after it:

PHP Warning:  Undefined property: SMF\Maintenance\Migration\v2_1\PostgreSqlSchemaDiff::$schemaFixes on line 132
ERROR:  syntax error at end of input
LINE 2:
        ^

This migration is PostgreSQL-only and cannot ever have run, so whatever those 30 statements were meant to correct has never been corrected on any PostgreSQL install.

change_column() forces NOT NULL on columns that asked to be nullable

Sources/Db/APIs/PostgreSQL.php:

if (isset($column_info['not_null'])) {
    $action = 'SET NOT NULL';
} else {
    $action = 'DROP NOT NULL';
}

$column_info comes from get_object_vars() on a Column, so not_null is always set — it is just false when the column is nullable. isset() cannot tell those apart, so every column this method touches is made NOT NULL.

It shows up on messages.edit_history, which is declared with no not_null at all:

'edit_history' => new Column(
    name: 'edit_history',
    type: 'json',
    default: null,
),

ERROR:  column "edit_history" of relation "smf_messages" contains null values
STATEMENT:  ALTER TABLE smf_messages
                ALTER COLUMN edit_history SET NOT NULL

!empty() is what this wants.

More on (3): the backfill

Point (3) in the description is the next one to fire, on smf_calendar.rdates. Worth noting for whoever fixes it that a default is not always available to backfill from:

'rdates' => new Column(
    name: 'rdates',
    type: 'text',
    not_null: true,
    default: null,
),

NOT NULL with no default is legal in MySQL, which invents one; here there is nothing to put in the rows that already exist.

Nine primary keys are lost during the upgrade

Counting them before and after:

SELECT COUNT(*) FROM pg_constraint WHERE contype = 'p' AND connamespace = 'public'::regnamespace;
-- 70   in the restored 2.1 baseline
-- 61   after the upgrade

smf_settings is one of them. Its column order changes too — value before variable — which suggests the table is being rebuilt somewhere and the key is not put back:

                           Table "public.smf_settings"
  Column  |          Type          | Nullable |        Default
----------+------------------------+----------+-----------------------
 value    | text                   | not null |
 variable | character varying(255) | not null | ''::character varying

with no constraints listed at all. Every ON CONFLICT insert against it then fails, which is what stops HolidaysToEvents:

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
STATEMENT:  INSERT INTO smf_settings("variable", "value")
                VALUES
                    (SUBSTRING('calendar_updated', 1, 255), SUBSTRING('1786734937', 1, 65534))
                ON CONFLICT (variable) DO UPDATE SET value = EXCLUDED.value

That is the same shape as point (4) in the description, and I suspect the same cause: the constraint the insert names is not there.

---

With all of these patched locally the upgrade gets through, and #9517's BoardPostsCount migration then does the right thing on PostgreSQL with real data — boards that counted posts under 2.1 come out as posts_count = 1 and the two I had set to count_posts = 1 come out as 0. So the schema layer is what stands between that migration and PostgreSQL, not the migration.

The engine-independent blockers on the same upgrade are now #9521.