Error: Resetting connection with an active transaction <asyncpg.connection.Connection object at 0x00000....>

[xFGhoul]

Describe the bug Every time I access the database, by create, get, save I get this error that spams my terminal and another side effect is that the data that's returned becomes None, or when I try to save data it says saved but actually isn't.

To Reproduce I'm not even sure, this just started happening randomly and I don't know how to diagnose

Expected behavior What should happen is that my data is returned as normal AND when saving my data is actually saved, and also my terminal isn't spammed 24/7 :c

Additional context None that comes to mind

[long2ice]

What's the version?

[xFGhoul]

What's the version?

0.19.0

[long2ice]

Show me the code

[xFGhoul]

Show me the code

Show you the code of...what?

I'll assume you mean how I get info from my db, it's just

from db.models import SomeModel

something  = await SomeModel.get(some_id=1)

this will print the error, all other methods like .save get_or_create, etc also have the same result

[isaquealves]

@xFGhoul what DBMS you are using? can you show us a piece of your tortoise configurations?

[xFGhoul]

@xFGhoul what DBMS you are using? can you show us a piece of your tortoise configurations?

My DMBS is PostgreSQL

Tortoise Configuration:

TORTOISE_CONFIG = {
    "connections": {"default": config["DATABASE_URI"]},
    "apps": {
        config["TORTOISE_APP_NAME"]: {
            "models": [config["DATABASE_MODEL_PATH"], "aerich.models"],
            "default_connection": "default",
        }
    },
    "use_tz": config["DATABASE_USE_TZ"],
    "timezone": config["DATABASE_TIMEZONE"],
}

[xFGhoul]

once again just ghosted completely...

[youmustfight]

Hitting a similar issue, but I'm doing a loop using multiple bulk_create calls. I'm inserting ~30 records, and I will receive the "Resetting connection..." response at the same point in my loop. It's good for the first 11 records, fails when inserting the next 7. If I do as a single bigger bulk_create call, the "Resetting connection..." response will also appear. The data being sent on each record is just 2 or 3 sentences of text at most.

Python: 3.10.7 postgres: 14.5 tortoise-orm: 0.19.2 asyncpg: 0.26.0 platform: Linux-5.10.104-linuxkit-x86_64-with-glibc2.31 (atm, just working locally w/ docker-compose. networked services between an api + postgres)

TORTOISE_ORM = {
    "connections": { "default": "postgres://..." },
    "apps": {
        "models": {
            "models": ["models", "aerich.models"],
            "default_connection": "default",
        },
    },
}

Perhaps another important details is that I'm using the sanic plugin, so I also have this

# server.py
from tortoise.contrib.sanic import register_tortoise

app = Sanic("api")
# ...

register_tortoise(
    app,
    db_url=TORTOISE_ORM['connections']['default'],
    generate_schemas=True,
    modules={"models": ["models"]}
)
if __name__ == "__main__":
    app.run(host='0.0.0.0', port=3000, access_log=False, auto_reload=True) 

Here's a cleaned up snippet of my loop async/await call where I do as 1 bulk_create call now.

# index_pdf.py
async def _index_pdf_process_file(document):    
    # ... PDF > pngs code...
    new_content = []
    for idx, pdf_image_file in enumerate(pdf_image_files):
        # ... OCR code turning a pdf page to text
        sentences = tokenize_string(pdf_page_text)
        page_content = list(map(lambda sentence: DocumentContent(
                    document_id=document.id,
                    page_number=page_number,
                    text=sentence
        ), sentences))
        new_content = new_content + page_content
    await DocumentContent.bulk_create(new_content)

and models at play

# models.py
class Document(Model):
    id = fields.IntField(pk=True)
    content = fields.ReverseRelation["DocumentContent"]

class DocumentContent(Model):
    id = fields.IntField(pk=True)
    document: fields.ForeignKeyRelation[Document] = fields.ForeignKeyField(
        "models.Document", related_name="content", null=True
    )
    text = fields.TextField(null=True)
    page_number = fields.CharField(max_length=255,null=True)

I know writes are happening from seeing the first two loops go through and insert 11 records, so I don't think it's any issue with the models/insert logic, but just sharing in case it's helpful.

---

FYI, got it failing in a different way

So instead of bulk_create operation, I'm now creating a asyncio task for each model and calling save(). Now the writes do go through and it doesn't send the error message, but it indefinitely hangs.

# ... 
tasks = [asyncio.create_task(nc.save()) for nc in new_content]
done, pending = await asyncio.wait(tasks) # <- hangs here forever despite all the inserts being done
# ...

It seems like the insertion count mattered again. When I ran with a smaller doc doing 11 inserts at a time, the first two batches went through, but my 3rd time calling the function ended up hanging. So it seems like somewhere around ~20 models it gets hung up with both the bulk_create or calling save() on model instances

As for logs, the insert gets fired off an immediately connection resets

api_1           | INFO (index_pdf.py:_index_pdf_process_file): prepped 1 document content records [<DocumentContent>]
api_1           | INFO (index_pdf.py:_index_pdf_process_file): Inserting 25 document content records
api_1           | 2022-10-09 23:32:35 - tortoise.db_client:113 - DEBUG - INSERT INTO "documentcontent" ("tokenizing_strategy","text","page_number","document_id") VALUES ($1,$2,$3,$4) RETURNING "id": [['sentence', 'Case 9.22-mj-08332-BER Document 18 Entered on FLSD Docket 08/11/2022 Page 1 of 5 UNITED STATES DISTRICT COURT SOUTHERN DISTRICT OF FLORIDA CASE NO.', '1', 53], ['sentence', "22-MJ-8332-BER IN RE SEALED SEARCH WARRANT UNTED STATES' MOTION TO UNSEAL LIMTED WARRANT MATERIALS On August &, 2022, the Department of Justice executed a search warrant issued by this Court upon the Iequisite finding of probable cause, see Fed. R. Crim P. 41(c)(1)-(2), at the premises located at 1100 S. Ocean Blvd. Palm Beach; Florida 33480, a property of former President Donald J. Trump.", '1', 53], ['sentence', "At the time the warrantwas initially executed, the Departmentprovided notice directly to former President Trump's counsel.", '1', 53], ['sentence', 'The Department did not make any statements about the search; and the search apparently attracted little OI no attention as it was taking place: Later that same former President Trump issued a public   statement acknowledging the execution ofthe warant In the days since, the search warrantand related materials have been the subject of significant interest and attention from news media organizations and other entities In these circumstances involving a search of the Tesidence of a former President; the government hereby Iequests that the Court unseal the Notice of Filing and its attachment (Docket Enty 17), absent objection by former President Trump.', '1', 53], ['sentence', 'The attachment to that Notice consists of: The search warrant signed and approved by the Court 0 August 5, 2022, including Attachments A and B; public public day, and.', '1', 53], ['sentence', 'Case 9.22-mj-08332-BER Document 18 Entered on FLSD Docket 08/11/2022 Page 2 of 5 The Iedacted Property Receipt items seized pursuant to the search; filed with the Court on August 11, 2022_ The government will respond to the direction of the Court to provide further briefing as to additional entries 0n the docket pursuant to the schedule set by the Court Consistent with standaId practice in this Court; the search warant and attachments were each filed under seal in Case No 22-mj-8332-BER to the search; the Property Receipt was filed under seal Former President Trump, through counsel was provided copies of each of these documents on August 8, 2022, as part of the execution of the search: Argument In These Circumstances_the Court Should Unseal the Search Warrant_Including Attachments A and B the_Property _Receipt,_Absent_Objection_from_the_Former President.', '2', 53], ['sentence', 'The press and the enjoy a qualified right of access to criminal and judicial proceedings and the judicial records filed therein. e.g Romero v. Drummond Inc 480 F.3d 1234, 1245 (1lth Cir.', '2', 53], ['sentence', '2007); Chicago Tribune Va Bridgestone / Firestone, Inc 263 F.3d 1304,1311 (1lth Cir: 2001).', '2', 53], ['sentence', 'The unsealing ofjudicial materials pursuant to the common-law rightofaccess  requires a balancing ofcompeting interests Chicago Tribune Co. 263 F.3d at 1311.', '2', 53], ['sentence', 'In balancing the interest in accessing court documents against a paIty $ interest in keeping the information confidential, courts  consider; among other factors,  whether allowing access would impair court functions Or harm legitimate privacy interests, the degree ofand likelihood ofinjury ifmade public, the reliability of the informa- whether there will The redactions in Docket Enty 17 remove the names oflaw enforcement personnel who executed the search from the unsealed materials.', '2', 53], ['sentence', 'For ease ofreference; the documents the government seeks to unseal; in the form to be made available to the public, have been filed under seal as Docket Number 17. listing prior today. and public See, Co , Co: public tion; Enty.', '2', 53], ['sentence', 'Case 9.22-mj-08332-BER Document 18 Entered on FLSD Docket 08/11/2022 Page 3 of 5 be an opportunity to respond to the information, whether the information concens public officials O1 concerns, and the availability of a less OneTous alternative to sealing the documents." Romero, 480 F.3d at 1246.2 Given the intense interest presented by a seaIch of a Iesidence of a former President; the government believes these factors favor unsealing the search warant its accompanying Attachments A and B, and the Property Receipt; absent objection from the former President Although the government initially asked, and this Court agreed, to file the warrant and Attachments A and B under seal Ieleasing those documents at this time would not %impait couit functions;" including the government\'s ability to execute the warant given that the warant has already been executed. See Romero, 480 F.3d at 1246.', '3', 53], ['sentence', "Furthermore, on the that the search was executed, foImer President Trump issued a public statement that provided the first confirmation that the search had occuired_ Subsequently; the former President's representatives have given additional statements to the press concerning the search; including public characterizations of the materials sought. eg, FBI Search of Trump'$ Home Pushes Long Conflict Into Public N.Y.", '3', 53], ['sentence', 'Times (Aug: 9, 2022), available at https: /WWW nytimes_com/2022/08/09/us /politics/fbi-search-tiunphtinl (Christina Bobb, lawyer and aide to Mr.', '3', 53], ['sentence', 'Trump who said she received a copy of the search warant; told one inteiviewer that the agents were looking for \'presidential IecoIds O any possibly classified material \'").', '3', 53], ['sentence', 'As such, the occurrence of the search and indications of the subject matter involved are already In addition; the First Amendment provides a basis for the press and the public\'$ "right of access to criminal tial proceedings. Chicago Tribune 263 F.3d at 1310.', '3', 53], ['sentence', 'However;,  this Circuit has not addressed whether the First Amendment right of access to sealed search warrant materials . eg , Benett V United States; No. 12-61499-CIV, 2013 WL 3821625, at*3 (S.D. Fla.', '3', 53], ['sentence', 'July 23, 2023) ("this Court has found no Eleventh Circuit decisions addressing whether a First Amendment Iight of access extends to sealed search-warrant affidavits, particularly at the preindictment stage") . 3 public public day public See, View, public . Co., applies See,.', '3', 53], ['sentence', 'Case 9.22-mj-08332-BER Document 18 Entered on FLSD Docket 08/11/2022 Page 4 of 5 This matter plainly %concerns public officials O1 public concerns; Romero, 480 F.3d at 1246, as it involves a law enforcement action taken at the property of the 4Sth President of the United States.', '4', 53], ['sentence', 'The public\'$ clear and powerful interest in understanding what occurred under these circumstances weighs heavily in favor of unsealing; That said, the former President should have an opportunity to respond to this   Motion objections, including with Iegards to any legitimate privacy interese O1 the potential for other cinjury" if these materials are made Romero, 480 F.3d at 1246.', '4', 53], ['sentence', 'To that end, the government will furnish counsel for the former President with a copy of this Motion.', '4', 53], ['sentence', 'Conclusion This Couit should unseal Docket   Enty 17, subject to the   presentation of countervailing interests by former President Trump. A proposed Order is attached herein.', '4', 53], ['sentence', 'Respectfully submitted, Ls Juan Antonio Gonzalez JUAN ANTONIO GONZALEZ UNITED STATES ATTORNEY Florida Bar No.', '4', 53], ['sentence', '897388 99 NE 4th Street; &th Floor Miami; Fl 33132 Tel: 305-961-9001 Email: juan antonio_gonzalez@usdojgov Ls[JayL Bratt JAY I BRATT CHIEF Counterintelligence Export Control Section National Security Division 950 Pennsylvania Avenue; NW Washington, D.C. 20530 Illinois Bar No. 6187361 202.233.0986 jay bratt2@usdoj gov and lodge ts" public . and.', '4', 53], ['sentence', "Case 9.22-mj-08332-BER Document 18 Entered on FLSD Docket 08/11/2022 Page 5 of 5 Certificate of Service I HEREBY CERTIFY that I caused the attached document to be electronically tansmitted to the Clerk' $ Office the CM/ECF system for Ls Juan Antonio Gonzalez Juan Antonio Gonzalez United States Attorney 5 filing: using.", '5', 53]]
api_1           | Resetting connection with an active transaction <asyncpg.connection.Connection object at 0x7f2f7d0392a0>

Might be a connection closing/unintended asyncpg error handle? @xFGhoul @isaquealves @long2ice I'm new to this ORM and coding in python, so continuing to comb through the source code to understand things 😅

---

An issue w/ asyncpg ?

Checking out asyncpg and these might be related/helpful: https://github.com/MagicStack/asyncpg/issues/271 https://github.com/MagicStack/asyncpg/issues/652 From asyncpg issue # 652, a member wrote: "This is actually more like a warning, not really an error in a strict sense. In your case I suspect that ROLLBACK in Transaction.aexit gets cancelled, which is why it complains. I have a fix in mind, but meanwhile it's safe to ignore the message."

Progress! I think?

Adding logging around backends/asyncpg/client.py and it looks like the reset happens before the query, when execute_many is setting up the transaction and calls await transaction.start(). So in my case, it's not even getting to the try/except.

Also I replicated the issue on both tortoise-orm versions 0.19.2 and 0.18.1 to see if the connections PR had anything to do with it and seems like that had no effect. The issue exists for both releases.

https://github.com/tortoise/tortoise-orm/blob/9709355cbe6029631af1d8126bc075b7ae943bed/tortoise/backends/asyncpg/client.py#L110-L113

[youmustfight]

Unfortunately still investigating the issue but FYI in terms of severity, this issue is making the ORM unusable for me :( doesn't seem like many folks are reporting it though

[youmustfight]

Still trying to address this, switched from asyncpg to psycopg and getting a variation of the same error 😩

psycopg==3.0.12
psycopg_pool==3.1.3

error text is

closing returned connection: <psycopg.AsyncConnection [ACTIVE] (host=database_app database=gideon) at 0x7f9955d5c4f0>
discarding closed connection: <psycopg.AsyncConnection [BAD] at 0x7f9955d5c4f0>

[arsen671games3]

I got this error when Postgres rebooted due to network error in Patroni. So maybe the problem is not with the libraries but with your Postgres.

[xFGhoul]

but what about my postures configuration could be the error?? I've tried so much

[xFGhoul]

and honestly as a library maintainer @long2ice I'm disappointed in your lack of attention to this issue that clearly isn't something that doesn't occur often, I've had to remake this issue so you could actually see and respond, and after responding to you you still didn't even bother to check.

[xFGhoul]

clearly detailed issues, even more than me by @youmustfight were ignored...for such a great library this is sad

[arsen671games3]

What system is your postgres running on? Do you manage it yourself or is it managed by your hosting?

[xFGhoul]

What system is your postgres running on? Do you manage it yourself or is it managed by your hosting?

windows, manage it myself

[xFGhoul]

but when I do host it on gcp, same issue

[volbil]

Hey, this might be shot in the dark but try removing Tortoise.close_connections() from your code (it was deprecated in 0.19.0). This seams to solve similar issue I had.

[long2ice]

Actually I have no idea for that, sorry

[YuriFontella]

I have exactly the same problem, the data in bulk_create is not saved when this resetting connection message appears.

@atomic()
async def task():
    objects = [Customers(**customer) for customer in data]

    await Customers.bulk_create(
        objects,
        on_conflict=['customer_id'],
        ignore_conflicts=True,
        batch_size=1000
    )

try:
    await task()
except Exception as e:
    print(e)

I removed the atomic decorator and all transaction contexts from my application. I think it solved it. I could also be doing something wrong.

Python 3.10.12 pip 22.0.2

tortoise-orm 0.20.0 asyncpg 0.28.0 fastapi 0.103.2