PHP - Postgresql How I can rollback a query if is returning error?

Question

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();
    }

@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
– JuJoGuAl, Commented Mar 11, 2017 at 16:17

Tometzky · Accepted Answer · 2017-03-11 17:18:09Z

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:

postgresql generate sequence with no gap

answered Mar 11, 2017 at 17:18

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JuJoGuAl Over a year ago

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

Collectives™ on Stack Overflow

PHP - Postgresql How I can rollback a query if is returning error?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related