Sequelize doesn't recognize `COMMIT PREPARED :id` and 'ROLLBACK PREPARED :id` statements to close database connections.

Issue Creation Checklist

• I understand that my issue will be automatically closed if I don't fill in the requested information
• I have read the contribution guidelines

Bug Description

We had created an issue reporting that sequelize is giving 'SequelizeConnectionAcquireTimeoutError' error after executing 5 prepared transactions. After some further analysis, we have found that we are not committing or rolling back the transaction explicitly and that's why the connection is not closed. But we are running raw queries to commit the prepared transaction or roll back the prepared transaction. This should handle the connection-closing task.

if I commit or rollback the transaction(involving prepared transaction), then the connection get's closed. is the following snippet valid for a prepared transaction scenario?

// sequelize1 & sequelize2 are two different sequelize instances, connected to different databases.
const sequelize1 = db1.connection;
const sequelize2 = db2.connection;

const id = crypto.randomUUID();
let firstTransactionPrepared = false;

const transaction1 = await sequelize1.transaction(); // In Sequelize 7, this method is called unmanagedTransaction()
try {
    const transaction2 = await sequelize2.transaction();

    //Start of actual task to do
    try {
        await test_table_1.create(
            {
                first_name: user.first_name
            },
            {
                transaction: transaction1
            }
        );

        await test_table_2.create(
            {
                last_name: user.last_name
            },
            {
                transaction: transaction2
            }
        );
        //End of actual task to do

        // Prepare & commit
        await sequelize1.query('PREPARE TRANSACTION :id', {
            replacements: { id },
            transaction: transaction1,
        });
        firstTransactionPrepared = true;

        await transaction2.commit();

        await sequelize1.query('COMMIT PREPARED :id', {
            replacements: { id },
            transaction: transaction1,
        });
        await transaction1.commit(); //is the usage of this line correct?
    } catch (error) {
        await transaction2.rollback();
        throw error;
    }
} catch (error) {
    if (firstTransactionPrepared) {
        await sequelize1.query('ROLLBACK PREPARED :id', {
            replacements: { id },
            transaction: transaction1,
        });
        await transaction1.rollback(); //is the usage of this line correct?
    } else {
        await transaction1.rollback();
    }

    throw error;
}

Reproducible Example

Here is the link to the SSCCE for this issue. Please update the database URLs in the .env file at the root before testing.

Here is the link to the SSCCE for this issue:

What do you expect to happen?

Sequelize should detect Commit prepared and rollback prepared statements and close the database connection.

What is actually happening?

Sequelize is not closing a database connection after a 'commit prepared' or 'rollback prepared' statement.

Environment

• Sequelize version: v6.32.1
• Node.js version: v16.20.1
• If TypeScript related: TypeScript version: not used
• Database & Version: PostgreSQL 14
• Connector library & Version: pg@8.11.3

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I will need guidance.
• No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
• No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in resolving my issue.

---

Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.

Start with the linked SSCCE and the Sequelize query and transaction calls shown in the report, then trace PostgreSQL handling for COMMIT PREPARED and ROLLBACK PREPARED. Done means both statements close or release the connection and the reproduced connection-acquire timeout no longer occurs.