Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

Re: Need help with DBD::Pg

by eclark (Scribe)
on Feb 23, 2005 at 21:44 UTC ( #433866=note: print w/replies, xml ) Need Help??

in reply to Need help with DBD::Pg

Take a look at the pg_server_prepare attribute in the DBD::Pg docs.

That feature prevents you from running multiple statements in one prepare. Something that you shouldnt do anyway, since by nature prepared statements are to be reused

On another note, why bother locking?

update: What I mean is, you're just going to have to change your code. It was bad that DBD::Pg let you do this in the old version.

Replies are listed 'Best First'.
Re^2: Need help with DBD::Pg
by Miguel (Friar) on Feb 23, 2005 at 22:07 UTC
    You're right.
    I tried with $dbh->{pg_server_prepare} = 1; but with no success too.

    For me, that was a Big Change.

    Answering your question, on another statement, I have:

    $sql = "INSERT INTO tabreserves( date_reserve, hour_i, hour_e, date, fk_idclient, fk_idfield, fk_iduser,name_temp, value, vat_perc, vat_val,desc_perc, desc_val, total, nhours) VALUES(NOW(),?,?,?,?,?,?,?,?,?,?,?,?,?,?); SELECT currval('public.tabreserves_idreserve_seq'::text) $sth = $dbh->prepare($sql); $sth->execute( <values_goes_here> ); $sth->bind_columns(\(my $new_id_reserve)); $sth->fetch();
    This code was working before the upgrade. Now it's broken. It raises errors like the ones I posted before.

      That code is incorrectly designed. Another process could call nextval() between the INSERT and SELECT. Sequences are not atomic in transactions.

      Sequences are to be used by calling nextval() first and then inserting that value in a second statement. The database guarantees that no clients will retrieve the same nextval.

        ++ for you eclark for pointing me to the docs one more time :-) I always tought it was correct, since I read this:

        Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value.


        Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

        About my problem:
        I'm rewriting everything using:

        use DBI qw(:sql_types); ..... $sth->bind_param(1,undef,SQL_....); $sth->execute(<values>);

        and spliting the multiple statements I have.


        Umm... What? The call to 'currval' will most certainly do exactly what is intended by the code. Because any call to currval returns the database _session's_ notion of the most recent squence value, Postgres will return whatever the last call to nextval (including column's DEFAULT constraint) returned *only for that session*. And because the INSERT and SELECT statements are in a single string there is no chance of another call to nextval between the INSERT and SELECT.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://433866]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (4)
As of 2022-05-20 10:56 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (73 votes). Check out past polls.