0

I have a psql db with several tables - I'm trying to hoist a child table's column into its parent table. In the process, I ran into some trouble trying to update an index on the child table. Not my exact code, but a close illustration (thanks in advance for forgiving any sqlalchemy misunderstandings):


class Parent(db.Model):
    __tablename__ = "parent_table"

    id = Column(
        UUID, primary_key=True, default=uuid.uuid4, **kwargs
    )
    # other table that is not inherited here
    foo_id = Column(UUID, nullable=False)
    foo = relationship(
        Foo,
        foreign_keys=foo_id,
        primaryjoin=(Foo.id == foo_id),
        backref=backref(
            "items", cascade="all, delete-orphan", passive_deletes=True
        ),
    )
    # the hoisted values:
    moved_at = Column(DateTime, default=sql.null())
    is_moved = column_property(moved_at != sql.null())


class Child(Parent):
    __tablename__ = "child_table"

    id = Column(
        ForeignKey(Parent.id, ondelete="CASCADE"), primary_key=True
    )
    name = Column(Text, nullable=False)
    location = Column(
        Text, nullable=False, server_default=""
    )
    # removed `moved_at` and `is_moved`

    __mapper_args__ = {
        "polymorphic_identity": "..."
    }
    __table_args__ = (
        Index(
            "ux_child_id_name",
            id,
            name,
            unique=True,
            postgresql_where=(~Parent.moved_at.isnot(None)),
        ),
    )

I removed moved_at and is_moved from Child and put it in Parent. The index's condition prior to hoisting was postgresql_where=(~moved_at.isnot(None)),.

I then run alembic migrations updating the db structure, and execute my unit tests (using pytest), but I get the following:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "moved_at" does not exist

I can see that the attempt produces the following, so I know I must be doing the inheritance or something else wrong, since it doesn't seem to access the parent table at all:

[SQL: CREATE UNIQUE INDEX ux_child_id_name ON
child_table (id, name) WHERE moved_at IS NOT NULL]

There may be some other thing that I'm neglecting to include that may be a clue, but I don't know. Anything immediately wrong here?

I considered moving the index up to the Parent table, but I need the name column from the Child table present in the index. I tried bringing the Index out of __table_args__ so it appears like this outside of the Parent model altogether:

Index(
    "ux_child_id_name",
    Child.id,
    Child.name,
    unique=True,
    postgresql_where=(~Parent.moved_at.isnot(None)),
),

... but I still get the same error. I couldn't find anything so far in the docs.

earl-95
  • 133
  • 3
  • 11
  • 1
    I'm not sure that [cross-table indexes in Postgres are a thing](https://stackoverflow.com/a/63388068/5320906). (Link is about PG12, but I don't see anything in the PG15 docs to suggest anything has changed in that respect. – snakecharmerb Aug 11 '23 at 15:17

0 Answers0