Migration from v2.3.0 to v25.4.0 does not work with PostgreSQL 11.22

[ibe-314]

I use the self-hosted ory hydra variant. When upgrading from v2.3.0 to v25.4.0 I got an error message during the sql migration. My PostgreSQL Version is 11.22, and the problem occurs at the following sql statement:

ALTER TABLE hydra_oauth2_flow ADD COLUMN IF NOT EXISTS expires_at TIMESTAMP GENERATED ALWAYS AS (CASE WHEN consent_remember_for > 0 THEN requested_at + (consent_remember_for * INTERVAL '1 second') END) STORED; -- postgres supports virtual columns only with version 18+, so we have to use a stored column instead

see in persistence/sql/migrations/20251030112809000000_flow_expires_at.postgres.up.sql

I looked at the ory hydra documentation, and I only found the information that "The SQL adapter supports PostgreSQL 9.6+...", see https://www.ory.com/docs/hydra/self-hosted/dependencies-environment.

What is the minimum Postgres Version that Ory Hydra 25.4.0 supports?

[vinckr]

This looks more like a migration bug or a missing version-compatibility guard than an intended change of minimum PostgreSQL version - just to be sure, could you try the migration on a newer version?

[ibe-314]

I cannot test it in the exactly same setting with a different postgres version. I could test it locally on my laptop later.

However, I think the reason for the error is that GENERATED ALWAYS AS ... STORED is not supported by postgres 11. I looked at the postgres docu and it seems to be supported by Version 12+, see https://www.postgresql.org/docs/12/sql-altertable.html. For version 12 it says:

and column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

For version 11 it says

and column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Thus, GENERATED ALWAYS AS ( generation_expr ) STORED is missing in version 11. So, I think the migration only works on postgres 12+.

[vinckr]

Thanks for investigating!
In general I would recommend to run a supported version of Postgres, which I believe is 14 at the moment.

[ibe-314]

Yes, I think this is the best solution ;-)

It would be nice if there would be some information on the minimum requirements, or maybe an update of the ory website that one should use at least postgres version 12.

[vinckr]

For sure!
created an issue to track it here: ory/docs#2388