PostgreSQL TestContainers failing: UUID column type mismatch in migrations

[dfcoffin]

Problem

PostgreSQL TestContainers integration tests are failing with schema validation errors:

Schema validation: wrong column type encountered in column [batch_list_id] 
in table [batch_list_resources]; 
found [bpchar (Types#CHAR)], but expecting [uuid (Types#UUID)]

Test Results:

• Tests run: 545
• Failures: 0
• Errors: 2 (both PostgreSQL TestContainers tests)
• Skipped: 2

Root Cause

Flyway migrations use CHAR(36) for UUID storage (MySQL/H2 compatible), but JPA entities use @GeneratedValue(strategy = GenerationType.UUID) which expects native UUID columns in PostgreSQL.

Affected Tables

All tables with UUID primary keys or foreign keys are affected:

• batch_lists.id
• batch_list_resources.batch_list_id
• time_configurations.id
• usage_points.id
• meter_readings.id
• interval_blocks.id
• retail_customers.id
• subscriptions.id
• And many more...

Current Migration Structure

V1__Create_Base_Tables.sql (MySQL/H2 compatible):

CREATE TABLE batch_lists (
    id CHAR(36) PRIMARY KEY  -- ❌ String UUID representation
    ...
);

CREATE TABLE batch_list_resources (
    batch_list_id CHAR(36) NOT NULL  -- ❌ String UUID
    ...
);

V2__PostgreSQL_Specific_Tables.sql:

CREATE TABLE time_configurations (
    id CHAR(36) PRIMARY KEY  -- ❌ Also uses string UUID
    ...
);

JPA Entity (BatchListEntity.java after Phase A refactoring):

@Id
@GeneratedValue(strategy = GenerationType.UUID)  // ✅ Expects native UUID type
private UUID id;

When Hibernate validates (spring.jpa.hibernate.ddl-auto=validate):

• Expected: UUID columns (PostgreSQL native type)
• Found: CHAR/bpchar columns (string representation)
• Result: ❌ Schema validation fails, ApplicationContext won't load

Solutions

Option 1: Create V4 Migration for UUID Conversion (Recommended)

Create new migration: V4__Convert_UUID_Columns_PostgreSQL.sql in db/vendor/postgres/:

/*
 * Convert all CHAR(36) UUID columns to native PostgreSQL UUID type
 * This migration is PostgreSQL-specific and should only run on PostgreSQL databases
 */

-- Base tables from V1
ALTER TABLE batch_lists 
    ALTER COLUMN id TYPE UUID USING id::uuid;

ALTER TABLE batch_list_resources 
    ALTER COLUMN batch_list_id TYPE UUID USING batch_list_id::uuid;

ALTER TABLE retail_customers 
    ALTER COLUMN id TYPE UUID USING id::uuid;

ALTER TABLE subscriptions 
    ALTER COLUMN id TYPE UUID USING id::uuid;

ALTER TABLE application_information 
    ALTER COLUMN id TYPE UUID USING id::uuid;

-- PostgreSQL-specific tables from V2
ALTER TABLE time_configurations 
    ALTER COLUMN id TYPE UUID USING id::uuid;

ALTER TABLE usage_points 
    ALTER COLUMN id TYPE UUID USING id::uuid;

ALTER TABLE meter_readings 
    ALTER COLUMN id TYPE UUID USING id::uuid,
    ALTER COLUMN usage_point_id TYPE UUID USING usage_point_id::uuid;

ALTER TABLE interval_blocks 
    ALTER COLUMN id TYPE UUID USING id::uuid,
    ALTER COLUMN meter_reading_id TYPE UUID USING meter_reading_id::uuid;

ALTER TABLE interval_readings 
    ALTER COLUMN id TYPE UUID USING id::uuid,
    ALTER COLUMN interval_block_id TYPE UUID USING interval_block_id::uuid;

ALTER TABLE reading_qualities 
    ALTER COLUMN id TYPE UUID USING id::uuid,
    ALTER COLUMN interval_reading_id TYPE UUID USING interval_reading_id::uuid;

ALTER TABLE usage_summaries 
    ALTER COLUMN id TYPE UUID USING id::uuid,
    ALTER COLUMN usage_point_id TYPE UUID USING usage_point_id::uuid;

-- Customer schema tables
ALTER TABLE customer_accounts 
    ALTER COLUMN id TYPE UUID USING id::uuid;

ALTER TABLE customer_agreements 
    ALTER COLUMN id TYPE UUID USING id::uuid;

ALTER TABLE service_locations 
    ALTER COLUMN id TYPE UUID USING id::uuid;

ALTER TABLE meters 
    ALTER COLUMN id TYPE UUID USING id::uuid;

ALTER TABLE statements 
    ALTER COLUMN id TYPE UUID USING id::uuid;

-- Add conversions for any other tables with UUID columns...

Pros:

• ✅ Proper fix, PostgreSQL uses native UUID type
• ✅ Better performance (UUID is 16 bytes vs CHAR 36 bytes)
• ✅ Type safety at database level
• ✅ Clear migration history

Cons:

• ⚠️ Need to identify and convert all affected tables
• ⚠️ Requires careful testing on existing data

Option 2: Disable Schema Validation (Temporary Workaround)

Change test configuration in DataCustodianApplicationPostgresTest.java:

registry.add("spring.jpa.hibernate.ddl-auto", () -> "none");  // Instead of "validate"

Pros:

• ✅ Quick fix, tests will pass immediately

Cons:

• ❌ Doesn't fix the underlying schema mismatch
• ❌ Hides the problem instead of solving it
• ❌ Schema drift could cause issues in production

Option 3: Update V2 to Include Conversions

Modify existing V2__PostgreSQL_Specific_Tables.sql to include ALTER statements for base tables.

Pros:

• ✅ All PostgreSQL UUID conversions in one place

Cons:

• ⚠️ Modifies existing migration (risky if already applied to databases)
• ⚠️ V2 becomes responsible for more than just vendor-specific tables

Recommendation

Implement Option 1 (new V4 migration) as the proper long-term fix. This:

1. Creates a clear migration specifically for UUID type conversion
2. Maintains clean separation of concerns
3. Provides proper PostgreSQL UUID support
4. Improves database performance and type safety

Use Option 2 temporarily to unblock development while working on Option 1.

Impact

• ❌ PostgreSQL TestContainers tests fail (prevents testing PostgreSQL compatibility)
• ✅ MySQL/H2 tests pass (no impact on current testing)
• ⚠️ Production PostgreSQL deployments may encounter similar issues during schema validation
• ⚠️ May affect future PostgreSQL-specific features or deployments

Files Affected

• openespi-common/src/main/resources/db/migration/V1__Create_Base_Tables.sql
• openespi-common/src/main/resources/db/vendor/postgres/V2__PostgreSQL_Specific_Tables.sql
• openespi-common/src/test/java/org/greenbuttonalliance/espi/common/migration/DataCustodianApplicationPostgresTest.java
• New file needed: openespi-common/src/main/resources/db/vendor/postgres/V4__Convert_UUID_Columns_PostgreSQL.sql

Steps to Reproduce

1. Run PostgreSQL TestContainers tests:

   mvn test -pl openespi-common -Dtest=DataCustodianApplicationPostgresTest

2. Observe error:

   Schema validation: wrong column type encountered in column [batch_list_id] 
   in table [batch_list_resources]; found [bpchar (Types#CHAR)], 
   but expecting [uuid (Types#UUID)]
   

Related Work

This issue surfaced during Phase A of ESPI 4.0 schema compliance refactoring where entities (PhoneNumberEntity, LineItemEntity, BatchListEntity) were updated to remove IdentifiedObject inheritance and use native UUID types with @GeneratedValue(strategy = GenerationType.UUID).

Environment

• Java: 25.0.1 (Zulu)
• Spring Boot: 4.0.1
• PostgreSQL: 18 (TestContainer)
• Hibernate: 7.2.0.Final
• Database: H2 (tests pass), MySQL (tests pass), PostgreSQL (tests fail)

[dfcoffin]

Decision: Postponing UUID Conversion

After analysis and discussion, we've decided to postpone the PostgreSQL UUID conversion until after the MULTI_PHASE schema compliance plan completes.

Rationale

1. Avoid Double Work: The MULTI_PHASE plan will restructure tables, remove incorrect related_links tables, and add missing ones. Converting CHAR(36) → UUID now means converting again after these schema changes.

2. Schema Changes Pending: The schema compliance remediation will significantly modify the database structure to properly align with ESPI 4.0 specification.

3. Comprehensive Solution: After MULTI_PHASE completion, we can implement UUID conversion for all three databases (H2, MySQL, PostgreSQL) simultaneously with vendor-specific V4 migrations.

Temporary Mitigation

PostgreSQL TestContainers integration tests have been temporarily disabled with @Disabled annotation in PR #54:

• Commit: 8053df6
• File: DataCustodianApplicationPostgresTest.java
• Reason documented in annotation with link to this issue

Tests will be re-enabled after UUID conversion is completed.

Future Work

After MULTI_PHASE schema compliance plan:

1. Create vendor-specific V4 migrations for H2, MySQL, PostgreSQL
2. Implement DROP FK → Convert CHAR(36) to UUID/native type → Recreate FK pattern
3. Re-enable PostgreSQL TestContainers tests
4. Verify all 545+ tests pass with 0 errors

Related

• PR refactor: ESPI 4.0 Schema Compliance - Pilot Refactoring (Phases A-D) #54: ESPI 4.0 Schema Compliance - Pilot Refactoring (Phases A-D)
• MULTI_PHASE schema compliance remediation plan

[springframeworkguru]

@dfcoffin - Problem with the proposed solution, MySQL does not support the UUID datatype that Postgres does. This solution will only work with Postgres.

I recommend further troubleshooting for this issue. I'm suspect we have something going on with the test profiles, or an issue with the JDBC driver version.

We could could also try converting from Char, to VARCHAR, or consider changing the Id property to a Long.