Notes for anyone wanting to work on this:

1. This will involve adding a small migration here:

   argo-workflows/persist/sqldb/migrate.go

   Lines 267 to 271 in f796449

   	// PostgreSQL only: convert argo_archived_workflows.workflow column to JSONB for performance and consistency with MySQL. #13779
   	ternary(dbType == MySQL,
   	noop{},
   	ansiSQLChange(`alter table argo_archived_workflows alter column workflow set data type jsonb using workflow::jsonb`),
   	),

2. Make sure to test with both PostgreSQL and MySQL, which you do with the go run ./hack/db migrate tool: https://argo-workflows.readthedocs.io/en/latest/running-locally/#database-tooling

I ran some tests with Percona XtraDB and noticed that adding the migration as suggested by @MasonM doesn’t fully address the issue. After the schema_history table is created, subsequent DML commands in the migrations will fail without a primary key.

Adding the primary key directly in the create table statement helps with new installations, but it creates a problem for migrations attempting to add the key for existing installations. Similarly, trying to remove and re-add the primary key in the migrations isn’t reliable because it may not exist in the first place.

Given these constraints, one possible solution is to introduce conditional logic immediately after the schema_history table creation. This logic would check whether the primary key exists and add it if it doesn’t – or safely handle any duplicate key errors if it already exists. While this approach isn’t perfectly elegant (as it requires running an extra query each time), it seems like a practical compromise for both new and existing installations.

If this approach sounds acceptable, I’d be happy to submit a PR to implement it.

@radusora Thanks for the details. That sounds reasonable. The query select 1 from information_schema.table_constraints where constraint_type = 'PRIMARY KEY' and table_name = 'schema_history' should allow you to quickly check if the primary key is already there with both MySQL and PostgreSQL (at least with the versions I tried).

@MasonM Thank you for the suggestion! I realized I needed to add an additional filter for the database, as the DB user in use could theoretically have access to other databases. Without the filter, the query against information_schema might return irrelevant results. Submitted the PR now.