pg_prepare: cannot insert multiple commands into a prepared statement

12,066

To understand what is going on and your options, let me explain what a prepared statement is and what it is not. You can use pg_prepare, but only for the statements individually, not for the transaction as a whole.

A prepared statement is a statement handed to PostgreSQL which is then parsed for and stored as a parse tree for future use. On first execution, the parse tree is planned with the inputs provided, and executed, and the plan cached for future use. Usually it makes little sense to use prepared statements unless you want to reuse the query plan (i.e. executing a bunch of otherwise identical update statements hitting roughly the same number of rows), all in the same transaction.

If you want something that gives you the benefits of separating parameters from parse trees but does not cache plans, see pg_query_param() in the PHP documentation. That is probably what you want.

Share:
12,066
Hikari
Author by

Hikari

Updated on June 14, 2022

Comments

  • Hikari
    Hikari over 1 year

    I have 2 tables, TableA and TableB. TableB has a fk field pointing to TableA.

    I need to use a DELETE statement for TableA, and when a record in it is deleted I need to delete all records in TableB related to that record in TableA. Pretty basic.

    begin;
    
    DELETE FROM TableB
    WHERE nu_fornecedor = $1;
    
    DELETE FROM TableA
    WHERE nu_fornecedor = $1;
    
    commit;
    

    This string is passed to pg_prepare(), but then I get error ERROR: cannot insert multiple commands into a prepared statement

    Ok, but I need to run both commands in the same transaction, I cant execute 2 separated statements. I tried to use with without begin-commit and got same error.

    Any idea how to do it?

  • Kek
    Kek over 9 years
    "Additionally prepared statements expire when the transaction commits or rolls back" Are you sure of this ? I thought it was when connection was closed: multiple transactions on same connection should be able to reuse prepared statements...
  • Luis Paulo
    Luis Paulo over 6 years
    @Kek yeah you are right. He didn't read the documentation and is giving opinions instead of facts as answers. It's a bad answer.