我从sqlalchemy得到了一个“ghost”回滚，但是在使用psql和postgres时没有

Question

我对postgres+sqlalchemy有一个奇怪的行为。在

我调用了一个插入到表中的函数，但是当从sqlalchemy调用它时，它会在末尾返回roolback，当从psql调用时，它会成功：

sqlalchemy调用时的日志（由日志报告）：

Jan 21 13:17:28 intersec.local postgres[3466]: [18-9] STATEMENT:  SELECT name, suffix 
Jan 21 13:17:28 intersec.local postgres[3466]: [18-10]  FROM doc_codes('195536d95bd155b9ea412154b3e920761495681a')
Jan 21 13:17:28 intersec.local postgres[3466]: [19-9] STATEMENT:  ROLLBACK
Jan 21 13:17:28 intersec.local postgres[3465]: [13-9] STATEMENT:  COMMIT

如果使用psql：

^{pr2}$

请注意，根本不是交易。在

这是我的python代码：

def getCon(self):
    conStr = "postgresql+psycopg2://%(USER)s:%(PASSWORD)s@%(HOST)s/%(NAME)s"
    config = settings.DATABASES['default']
    #print conStr % config
    con = sq.create_engine(
        conStr % config,
        echo=ECHO
    )

    event.listen(con, 'checkout', self.set_path)

    self.con = con
    self.meta.bind = con

    return con

def getDocPrefixes(self, deviceId):
    f = sq.sql.func.doc_codes(deviceId, type_=types.String)

    columns = [
        sq.Column('name', types.String),
        sq.Column('suffix', types.String)
    ]

    return [dict(x.items()) for x in self.con.execute
            (
                select(columns).
                select_from(f)
            ).fetchall()]

sync = dbSync('malab')
for k in sync.getDocPrefixes('195536d95bd155b9ea412154b3e920761495681a'):
    print k['name'], '=', k['suffix']

什么会触发回滚？在

p.D:我的数据库功能：

CREATE OR REPLACE FUNCTION next_letter (
  table_name TEXT,
  OUT RETURNS TEXT
)
AS
$$
DECLARE
    result TEXT = 'A';
    nextLetter TEXT;
    num INTEGER;
BEGIN
    SELECT INTO num nextval('letters');
    nextLetter := chr(num);
    result := nextLetter;

    WHILE true LOOP
        --RAISE NOTICE '%', result;
        IF EXISTS(SELECT 1 FROM DocPrefix WHERE Name=result AND TableName=table_name) THEN
            SELECT max(SUBSTRING(name FROM '\d+'))
                FROM DocPrefix WHERE Name=result AND TableName=table_name
                INTO num;

            result := nextLetter || (coalesce(num,0) + 1);
        ELSE
            EXIT;
        END IF;
    END LOOP;

    RETURNS = result;
END;
$$
LANGUAGE 'plpgsql';

-- Retorna el prefijo unico para la tabla/dispositivo.
CREATE OR REPLACE FUNCTION prefix_fordevice (
  table_name TEXT,
  device_id TEXT,
  OUT RETURNS TEXT
)
AS
$$
DECLARE
    result TEXT = NULL;
    row RECORD;
BEGIN
    IF NOT(EXISTS(SELECT 1 FROM DocPrefix WHERE MachineId=device_id AND TableName=table_name)) THEN
        INSERT INTO DocPrefix
            (Name, MachineId, TableName)
        VALUES
            (next_letter(table_name),  device_id, table_name);
    END IF;

    SELECT name FROM DocPrefix WHERE
        MachineId=device_id AND TableName=table_name
    INTO result;

    RETURNS =  result;
END;
$$
LANGUAGE 'plpgsql';

--Retornar los prefijos exclusivos para el ID de dispositvo
CREATE OR REPLACE FUNCTION doc_codes(device_id TEXT) RETURNS TABLE("name" TEXT, "suffix" TEXT) AS $$
    SELECT name, prefix_fordevice(name,  device_id) AS suffix FROM doccode;
$$ LANGUAGE SQL;

Answer 1

我终于在这里找到了答案：

Make SQLAlchemy COMMIT instead of ROLLBACK after a SELECT query

def getDocPrefixes(self, deviceId):
    f = sq.sql.func.doc_codes(deviceId, type_=types.String)

    columns = [
        sq.Column('name', types.String),
        sq.Column('sufix', types.String)
    ]

    with self.con.begin():
        return [dict(x.items()) for x in self.con.execute
            (
                select(columns).
                select_from(f)
            ).fetchall()]

问题是，函数可以插入data+还返回SELECT，因此，sqlalchemy认为这是一个正常的SELECT，而实际上函数也更改了数据并需要提交。

Answer 2

这里的反模式是，当您执行以下操作时，会混淆连接的SQLAlchemyEngine：

con = sq.create_engine(<url>)
result = con.execute(statement)

引擎与作为连接源的连接池相关联。在引擎上调用execute（）方法时，它会从池中检出一个连接，运行语句并返回结果；当结果集耗尽时，它返回到池的连接。在这个阶段，池将要么完全关闭连接，要么将其重新池化。将连接存储在池中意味着必须清除任何剩余的事务状态（请注意，DBAPI连接在使用时始终位于事务中），因此它发出回滚。

您的程序应该在模块级别为每个URL创建一个Engine，当它需要连接时，应该调用engine.connect()。

文档Working with Engines and Connections解释了所有这些。