1. Mike Bayer Avatar Mike Bayer
  2. Untitled project
  3. sqlalchemy_old
  4. Issues

session.merge/nullable primary keys - FlushError: Can't update table using NULL for primary key value

Issue #2820 wontfix
Former user created an issue

SQLAlchemy==0.8.2

I believe this is a bug:

Traceback (most recent call last):
  File "test.py", line 42, in <module>
    session.commit()
  File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 721, in commit
    self.transaction.commit()
  File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 354, in commit
    self._prepare_impl()
  File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 334, in _prepare_impl
    self.session.flush()
  File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1818, in flush
    self._flush(objects)
  File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1936, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 58, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1900, in _flush
    flush_context.execute()
  File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 372, in execute
    rec.execute(self)
  File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 525, in execute
    uow
  File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 54, in save_obj
    table, states_to_update)
  File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 383, in _collect_update_commands
    "Can't update table "
sqlalchemy.orm.exc.FlushError: Can't update table using NULL for primary key value

This is the code that triggers this:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

class Related(Base):
    __tablename__ = 'related'
    parent = Column(Integer, ForeignKey("parent.id"))
    key1 = Column(Integer, primary_key=True, nullable=True)
    key2 = Column(Integer, primary_key=True, nullable=True)

    def __init__(self, key1, key2):
        self.key1, self.key2 = key1, key2

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    related = relationship(Related, collection_class=set)

Base.metadata.create_all(engine)

# First object
parent = Parent()
parent.id = 1
parent.related.add(Related(3, None))
session.add(parent)
session.commit()

# Update first object
parent = Parent()
parent.id = 1
parent.related.add(Related(2, None))
session.merge(parent)
session.commit()

Comments (7)

  1. Mike Bayer repo owner

    the intent of your code isn't clear. the issue is not the merge, it's the implicit removal of Related(3, None) from the collection which is not supported. Here's a simpler reproduction:

    from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

class Related(Base):
    __tablename__ = 'related'
    parent = Column(Integer, ForeignKey("parent.id"))
    key1 = Column(Integer, primary_key=True, nullable=True)
    key2 = Column(Integer, primary_key=True, nullable=True)

    def __init__(self, key1, key2):
        self.key1, self.key2 = key1, key2

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    related = relationship(Related, collection_class=set)

Base.metadata.create_all(engine)

# First object
parent = Parent()
r1 = Related(3, None)
parent.related.add(r1)
session.add(parent)
session.commit()

parent.related.remove(r1)
session.commit()

    you may ask, "why is it trying to remove in my example?" because you are merging Parent->related((Related(3, None)) which no longer includes Related(2, None). Even though SQLite is allowing this primary key to proceed, it is in fact illegal in SQL to have a NULL value in a table-bound primary key column (most if not all other backends will reject it in some way - PG places an implicit NOT NULL on any PK col, MySQL seems to be inserting a zero despite it being missing). SQLAlchemy allows limited support of a NULL for the case where a class is mapped to an OUTER JOIN or other composed construct where the columns noted as primary key may contain nulls, but they would not ever be the target of an UPDATE.

    So the real issue here is why exactly you need to have a table that stores a NULL within a first-class primary key column. I'm not really sure SQLAlchemy should seek to support this pattern.

  3. Former user Account Deleted

    A quick post-mortem on this, since I finally had to address it:

    • Our pre-SQLAlchemy database had a permission table (user, group, permission), where a permission would apply to either a user or a group, and the other column was NULL.
    • It used a unique index rather than a primary key.
    • SQLAlchemy does not support a table without a primary key.
    • I wanted to trick SQLAlchemy by using mapper_args to define a primary key, but in truth using a unique key in the database, but during session.merge() you run into the FlushError above.

    I've now simply removed the foreign keys from the user and group columns, and am storing an integer 0 for the column that does not apply.

  5. Jean-Sébastien Suzanne

    Hi Mike, I reopen this bug, I have the same issue with postgres.

    from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


engine = create_engine('postgres:///memory', echo=True)
Base = declarative_base()


class Entity(Base):
    __tablename__ = 'person'

    id = Column(Integer, primary_key=True)
    id2 = Column(Integer, primary_key=True)
    label = Column(String(64))


Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

session.add(Entity(label="One label"))
session.flush()

    And this is the result with echo mode

    2015-06-02 00:16:44,235 INFO sqlalchemy.engine.base.Engine select version()
2015-06-02 00:16:44,235 INFO sqlalchemy.engine.base.Engine {}
2015-06-02 00:16:44,237 INFO sqlalchemy.engine.base.Engine select current_schema()
2015-06-02 00:16:44,237 INFO sqlalchemy.engine.base.Engine {}
2015-06-02 00:16:44,238 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-06-02 00:16:44,238 INFO sqlalchemy.engine.base.Engine {}
2015-06-02 00:16:44,238 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-06-02 00:16:44,238 INFO sqlalchemy.engine.base.Engine {}
2015-06-02 00:16:44,239 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2015-06-02 00:16:44,239 INFO sqlalchemy.engine.base.Engine {}
2015-06-02 00:16:44,240 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2015-06-02 00:16:44,240 INFO sqlalchemy.engine.base.Engine {'name': 'person'}
2015-06-02 00:16:44,242 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE person (
    id SERIAL NOT NULL, 
    id2 INTEGER NOT NULL, 
    label VARCHAR(64), 
    PRIMARY KEY (id, id2)
)


2015-06-02 00:16:44,242 INFO sqlalchemy.engine.base.Engine {}
2015-06-02 00:16:44,246 INFO sqlalchemy.engine.base.Engine COMMIT
2015-06-02 00:16:44,248 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-06-02 00:16:44,249 INFO sqlalchemy.engine.base.Engine INSERT INTO person (label) VALUES (%(label)s) RETURNING person.id, person.id2
2015-06-02 00:16:44,249 INFO sqlalchemy.engine.base.Engine {'label': 'One label'}
2015-06-02 00:16:44,250 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
psycopg2.IntegrityError: null value in column "id2" violates not-null constraint
DETAIL:  Failing row contains (1, null, One label).


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./bin/python", line 46, in <module>
    exec(compile(__file__f.read(), __file__, "exec"))
  File "test3.py", line 23, in <module>
    session.flush()
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/orm/session.py", line 2004, in flush
    self._flush(objects)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/orm/session.py", line 2122, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/util/compat.py", line 182, in reraise
    raise value
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/orm/session.py", line 2086, in _flush
    flush_context.execute()
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/orm/unitofwork.py", line 373, in execute
    rec.execute(self)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/orm/unitofwork.py", line 532, in execute
    uow
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/orm/persistence.py", line 174, in save_obj
    mapper, table, insert)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/orm/persistence.py", line 761, in _emit_insert_statements
    execute(statement, params)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/engine/base.py", line 1339, in _handle_dbapi_exception
    exc_info
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/util/compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/util/compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/jssuzanne/.buildout/eggs/SQLAlchemy-1.0.4-py3.5-macosx-10.10-x86_64.egg/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) null value in column "id2" violates not-null constraint
DETAIL:  Failing row contains (1, null, One label).
 [SQL: 'INSERT INTO person (label) VALUES (%(label)s) RETURNING person.id, person.id2'] [parameters: {'label': 'One label'}]
jssuzanne:anyblok jssuzanne$ cat test3.py

    You can see, in the echo that only one primary key is a SERIAL column, not the second.

  6. Mike Bayer repo owner

    Hi Jean-Sébastien -

    the script you post there is something different entirely. you are looking for support of the SERIAL data element to be explicitly rendered on columns. This feature isn't supported right now in exactly this way; you instead need to apply a Sequence() on both columns. there is some information on this here: http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#sequences-serial as well as here: http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html#sqlalchemy.schema.Column.params.autoincrement however this probably does not make clear the total picture of this behavior. https://bitbucket.org/zzzeek/sqlalchemy/issue/3438/support-explicit-serial-on-postgresql is added however to make your test work:

    class Entity(Base):
    __tablename__ = 'person'

    id = Column(Integer, Sequence('id1_seq'), primary_key=True)
    id2 = Column(Integer, Sequence('id2_seq'), primary_key=True)
    label = Column(String(64))

    see also https://bitbucket.org/zzzeek/sqlalchemy/issue/3438/support-explicit-serial-on-postgresql where i will post an immediate recipe for SERIAL.

  8. Log in to comment
Assignee
Type
bug
Priority
major
Status
wontfix
Component
orm
Milestone
–
Votes
1
Watchers
3
Jira: the preferred issue tracker for Bitbucket. Join the team!