FetchedValue columns are INSERTed by Session.merge()

[Wilson, Chris]

Dear Michael and co,

I think that Columns which are marked as server_default=FetchedValue(), which are normally omitted from INSERT statements, are not omitted after an object has been merged from another session. Therefore SQLAlchemy tries to assign the old values to them, which fails if the database does not allow it (e.g. internal columns, such as xmin in Postgres).

If this is by design, is there any way to override it on the individual objects before merging?

Here is an example that reproduces the error:

from sqlalchemy import Column, FetchedValue, Integer, Text, create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import object_session, sessionmaker

Base = declarative_base()

class Dog(Base):

    __tablename__ = 'dog'

    __table_args__ = {'prefixes': ['TEMPORARY']}

    id = Column(Integer, primary_key=True)

    xmin = Column("xmin", Integer, server_default=FetchedValue(), server_onupdate=FetchedValue(), system=True)

    name = Column(Text)

def main():

    engine = create_engine('postgresql://username@server:port/db')

    engine.echo = True

    Base.metadata.create_all(engine)

    session_1 = sessionmaker(bind=engine)(autocommit=True)

    with session_1.begin():

        fido_1 = Dog(name="fido")

        session_1.add(fido_1)

    print(fido_1.xmin)

    session_2 = sessionmaker(bind=engine)(autocommit=True)

    with session_2.begin():

        session_2.query(Dog).delete()

    with sessionmaker(bind=engine)(autocommit=True).begin():

        fido_2 = session_2.merge(fido_1)

        session_2.add(fido_2)

    print(fido_2.xmin)

    print(session_2.query(Dog).count())

if __name__ == '__main__':

    main()

And the result is:

sqlalchemy.exc.ProgrammingError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)

(psycopg2.ProgrammingError) column "xmin" of relation "dog" does not exist

LINE 1: INSERT INTO dog (id, xmin, name) VALUES (1, '283302364', 'fi...

                             ^

[SQL: INSERT INTO dog (id, xmin, name) VALUES (%(id)s, %(xmin)s, %(name)s)]

[parameters: {'id': 1, 'xmin': '283302364', 'name': 'fido'}]

(Background on this error at: http://sqlalche.me/e/f405)

Note that this is raised when fido_2 is inserted (after fido_1 is merged into a new session).

Thanks, Chris.

** Cantab Capital Partners LLP is now named GAM Systematic LLP. Please note that our email addresses have changed from @cantabcapital.com to @gam.com.**

This email was sent by and on behalf of GAM Investments. GAM Investments is the corporate brand for GAM Holding AG and its direct and indirect subsidiaries. These companies may be referred to as ‘GAM’ or ‘GAM Investments’. In the United Kingdom, the business of GAM Investments is conducted by GAM (U.K.) Limited (No. 01664573) or one or more entities under the control of GAM (U.K.) Limited, including the following entities authorised and regulated by the Financial Conduct Authority: GAM International Management Limited (No. 01802911), GAM London Limited (No. 00874802), GAM Sterling Management Limited (No. 01750352), GAM Unit Trust Management Company Limited (No. 2873560) and GAM Systematic LLP (No. OC317557). GAM (U.K.) Limited and its regulated entities are registered in England and Wales. The registered office and principal place of business of GAM (U.K.) Limited and its regulated entities is at 8 Finsbury Circus, London, England, EC2M 7GB. The registered office of GAM Systematic LLP is at City House, Hills Road, Cambridge, CB2 1RE. This email, and any attachments, is confidential and may be privileged or otherwise protected from disclosure. It is intended solely for the stated addressee(s) and access to it by any other person is unauthorised. If you are not the intended recipient, you must not disclose, copy, circulate or in any other way use or rely on the information contained herein. If you have received this email in error, please inform us immediately and delete all copies of it. See - https://www.gam.com/en/legal/email-disclosures-eu/ for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you. GAM Investments will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice.

[Mike Bayer]

On Fri, Oct 2, 2020, at 12:15 PM, Wilson, Chris wrote:

Dear Michael and co,

I think that Columns which are marked as server_default=FetchedValue(), which are normally omitted from INSERT statements, are not omitted after an object has been merged from another session. Therefore SQLAlchemy tries to assign the old values to them, which fails if the database does not allow it (e.g. internal columns, such as xmin in Postgres).

If this is by design, is there any way to override it on the individual objects before merging?

it looks like you have an object where you've accessed "xmin", so it loads it, then you are deleting that row and trying to re-insert it, passing along the object that now has "xmin" loaded as part of its row.      The "system" flag right now only impacts CREATE TABLE it is not linked to INSERT statements, in that it isn't going to block that column from being included in the INSERT as "system" is not specific to PG's specific "xmin" idea here.

so most immediately if you expect to have this fairly particular sequence of events you would want to expire xmin first:

session.expire(fido_1, ["xmin"])

more automatically you might provide a before_insert handler that deletes the "xmin" attribute up front:

from sqlalchemy import event

@event.listens_for(Dog, "before_insert")

def _del_xmin(mapper, connection, target):

    if 'xmin' in target.__dict__:

        del target.xmin

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/DB8PR09MB3372A5AEA1D3D219C8EC882893310%40DB8PR09MB3372.eurprd09.prod.outlook.com.