Search code examples
pythonpostgresqlsqlalchemymaterialized-views

How to prevent related object persistence in sqlalchemy?

Environment:

  • python 3.7
  • SqlAlchemy 1.3.10 (I also tested on 1.2.16, same result)
  • PostgreSQL 11

TL,DR: I have a table and a 1-1 "related" materialized view (no fk, the relation is implicit on the sql side). In SQLAlchemy this relationship is declared as viewonly=True. But if I assign to it, the session tries to insert the assigned mat view object anyway (which fails because it's a materialized view, obviously).

Am I misunderstanding the purpose of viewonly or is the relationship not set up properly?


Complete reproducible test case:

  • setup a python 3.7 venv with SQLalchemy 1.3.10
  • create a new (empty) postgresql database
  • save this code sample in a file and change the DB_URI value to the one you just created
  • execute the file within the env (python <path-to-script>)
from __future__ import annotations

import unittest
from unittest import TestCase

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
from sqlalchemy.orm import relationship, backref, sessionmaker, object_session

create_sql = """
CREATE TABLE universe (
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL,
   is_perfect BOOLEAN NULL DEFAULT 'f'
);

CREATE MATERIALIZED VIEW answer AS (
    SELECT
        id,
        trunc(random() * 100)::INT AS computed
    FROM universe
)
"""


Base: DeclarativeMeta = declarative_base()
metadata = Base.metadata


class Universe(Base):
    __tablename__ = 'universe'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String, nullable=False)
    is_perfect = sa.Column(sa.Boolean, nullable=False, server_default='f')

    answer: Answer = relationship('Answer',
                                  backref=backref('universe', uselist=False),
                                  innerjoin=False,
                                  lazy='select',
                                  uselist=False,
                                  viewonly=True)

    def set_perfect(self):
        self.is_perfect = (self.answer.computed == 42)

        session = object_session(self)
        if session:
            session.commit()


class Answer(Base):
    __tablename__ = 'answer'

    id = sa.Column(sa.Integer, sa.ForeignKey('universe.id'), primary_key=True)
    computed = sa.Column(sa.Integer, nullable=False)


DB_URI = 'postgresql://user:pass@localhost:5432/db_name'  # Fill in your own
engine = sa.create_engine(DB_URI)
Session = sessionmaker(bind=engine, expire_on_commit=True)
session = Session()


class UniverseTests(TestCase):
    def setUp(self) -> None:
        session.execute("DROP MATERIALIZED VIEW IF EXISTS answer")
        session.execute("DROP TABLE IF EXISTS universe")
        session.execute(create_sql)
        session.execute("INSERT INTO universe (id, name) VALUES (1, 'HG2G');")
        session.commit()

    def tearDown(self) -> None:
        session.execute("DROP MATERIALIZED VIEW answer")
        session.execute("DROP TABLE universe")
        session.commit()
        session.close()

    def test__is_perfect(self):
        universe: Universe = session.query(Universe).get(1)

        universe.answer = Answer(id=1, computed=42)
        universe.set_perfect()
        assert universe.is_perfect is True


if __name__ == '__main__':
    unittest.main()

As you can see, during the test, I assign a fake Answer(id=1, computed=42) to universe.answer to test the method .set_perfect().

The test errors out during the set_perfect() function because the call to session.commit() tries to commit the Answer(id=1, computed=42) object along with the updated field on the Universe object.


A sample of the error message

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot change materialized view "answer"

[SQL: INSERT INTO answer (id, computed) VALUES (%(id)s, %(computed)s)]
[parameters: {'id': 1, 'computed': 42}]
(Background on this error at: http://sqlalche.me/e/f405)
Solution

  • Updated answer:

    For SQLAlchemy 1.3.10 (the version I was using for this example) and below, the fix is to set cascade on the relationship to either None, or one of the non-persistence related cascade options ("expunge", "refresh-expire" or both). False (the default) won't work because it means "save-update, merge".

    To sum up:

    answer: Answer = relationship(
    'Answer',
    ...,
    viewonly=True,
    cascade=None  # or "expunge" or "refresh-expire" or "expunge,refresh-expire"
)

    This was indeed a bug of versions 1.3 and below which was corrected somewhere between 1.3.11 and 1.4.22. All I know is that the original example, which doesn't specify cascade, works like a charm in 1.4.22.

    Related bug-reports:

    And the corresponding changelogs: