postgresql.UUID server default always compares to false, thereby always autogenerating

[sqlalchemy-bot]

Migrated issue, originally created by Peter Lada (@peterlada)

PROBLEM

My sqlalchemy model:

class Issue(Base):
    __tablename__ = 'issue'
    id = Column(Integer, primary_key=True) 
    ...
    uuid = Column(postgresql.UUID, nullable=False, index=True, default=uuid.uuid4(), server_default=text("uuid_generate_v4()"))

Configured env.py with compare_server_default=True

Yields a autogenerated revision each time, setting the server_default=sa.Text('uuid_generate_v4()')

ROOT CAUSE

Comparing the server defaults with:

SELECT uuid_generate_v4() = uuid_generate_v4() yields False (with an exceptionally high probability).

WORKAROUND

def my_compare_server_default(context, inspected_column, metadata_column,
                              inspected_default, metadata_default, rendered_metadata_default):
    if type(inspected_column.type).__name__ == 'UUID':
        if inspected_default == rendered_metadata_default:
            print("they are the same")
            return False  # unintuitive: no need for migration
        else:
            return True  # unintuitive: needs a migration
    return None

and in env.py: compare_server_default=my_compare_server_default

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• Added a fix to Postgresql server default comparison which first checks
  if the text of the default is identical to the original, before attempting
  to actually run the default. This accomodates for default-generation
  functions that generate a new value each time such as a uuid function.
  fixes postgresql.UUID server default always compares to false, thereby always autogenerating #365
• test against uuid_generate_v4() directly, but this requires extensions
  to be installed. should come up with a built in function for this test

→ 9538c3e

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed