0

I'm making a CRUD with php and postgres, and I want to know if there a way to rollback the transaction if there is a error. actually if I get an error in the transaction the id (primary key) get increased, then I lose one id for the future use.

How can I prevent it? I mean, if the Insert's Query fails, don't make the auto increment in the table.

Im using a class for execute the querys:

public function insertRecord ($data){
        $campos =$this->getTableFields();
        $data =implode ("', '", $data);
        $sql ="INSERT INTO {$this->table} ($campos) VALUES (";
        $sysData =$this->getDefaultValues();
        if($sysData){
            $sysData .= ",";
            $sql .="$sysData ";
        }
        $sql .="'$data') RETURNING {$this->campoId};";
        echo $sql;
        pg_query($this->linkid,$sql);
        return $this->validateOperation();
    }
hmatar
  • 2,437
  • 2
  • 17
  • 27
JuJoGuAl
  • 117
  • 1
  • 15
  • 2
    Take a look at [Transactions](https://www.postgresql.org/docs/current/static/tutorial-transactions.html). – tyteen4a03 Mar 11 '17 at 15:50
  • @tyteen4a03, i read about it, but i ask, i have a class called PG, where i put all the statements (Inserts,updates,selects,deletes) and then i have separate class for the table's use (articles, art_mov, art_warehouse), and in every class i call the statements passing the table and the conditions, Given it, i must to make in every class the sql statements? because in every table i don't make the same query - Right now i don't know how explain my problem – JuJoGuAl Mar 11 '17 at 16:17

1 Answers1

2

The transaction is rolled back. But it will still use up a number from a sequence, on which automatically incrementing values are based. This is by design and prevents long lasting locks, which would kill parallel execution performance.

There are some workarounds:

Tometzky
  • 22,573
  • 5
  • 59
  • 73
  • Well i made another questions, and this comment answers the both questions, well i guess i need improve another column if i wanna create a manual sequence (to show a pretty number) Thanks a lot @Tometzky – JuJoGuAl Mar 11 '17 at 17:31