Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

rows affected inside a transaction

by x5150 (Acolyte)
on May 13, 2008 at 20:56 UTC ( #686366=perlquestion: print w/replies, xml ) Need Help??
x5150 has asked for the wisdom of the Perl Monks concerning the following question:

My problem is sth->rows always returns zero for a DELETE command inside a transaction. I'm using DBD::Pg 2.7.1.
$dbh->begin_work; foreach (@$bp_aref) { $bind_parms = $_; my $ra = $sth->execute(@$bind_parms); # $ra is zero # records are still in the db # I need to keep a count of how many rows are going to be deleted, for + each delete statement $count[$i] += $sth->rows; # returns zero } $dbh->commit unless $skip_trans; # now records are not in db
How can I get a number of rows affected inside the transaction? Thanks.

Replies are listed 'Best First'.
Re: rows affected inside a transaction
by gam3 (Curate) on May 14, 2008 at 03:40 UTC
    I ran this code and ->rows was never zero. I ran it on the data that is at the bottom.
    use DBI; my $db = 'Pg'; my $database = 'test'; my $user = 'test'; my $password = 'test'; my $dbh = DBI->connect( "DBI:${db}:database=" . $database . ";", $user, $password, { RaiseError => 1, PrintError => 1, PrintWarn => 1, # AutoCommit => 0, } ); $dbh->begin_work; my $st = $dbh->prepare('delete from test where inc = ?'); for my $x (2, 4, 6) { my $ra = $st->execute($x); print $st->rows, "\n"; } $dbh->rollback;
    The output of this is:

    Here is the a dump of the database that I used.

    -- -- PostgreSQL database dump -- SET client_encoding = 'UTF8'; SET check_function_bodies = false; SET client_min_messages = warning; -- -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres -- COMMENT ON SCHEMA public IS 'Standard public schema'; CREATE USER test WITH PASSWORD 'test'; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: test; Type: TABLE; Schema: public; Owner: test; Tablespace: -- CREATE TABLE test ( inc integer, test character varying(40) ); ALTER TABLE public.test OWNER TO test; -- -- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: test -- COPY test (inc, test) FROM stdin; 1 a 2 b 3 b 4 b 5 b 6 c \. GRANT ALL ON TABLE test TO test;

    You might want to run a simple test like this to check that the problem is in DBI and not in your logic.

    -- gam3
    A picture is worth a thousand words, but takes 200K.
Re: rows affected inside a transaction
by kyle (Abbot) on May 14, 2008 at 03:42 UTC

    This is not an ideal solution, but...

    Since you're in a transaction, you don't have to worry about a race condition. As such, you can query for the matching records before you delete them.

    my $suffix = 'FROM table WHERE blah blah'; my ($to_be_deleted) = $dbh->selectrow_array( "SELECT count(*) $suffix" ); $dbh->do( "DELETE $suffix" );

    The first problem with this is performance. You'll be going to these same records twice. If it's a huge table, and the condition causes a full table scan, this is going to be really painful.

    The second problem is that it's possible for $suffix to work fine for the "SELECT", but not for the "DELETE". That's not a huge problem, I think, because it will abort the transaction. That is, it'll fail instead of lie. I could be wrong, however. Not knowing what kind of SQL you have in $sth, I don't know if this is a potential issue.

      Found out there were some DELETE and SELECT triggers on those tables that didn't actually delete the data but looked like it did.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://686366]
Approved by planetscape
Front-paged by planetscape
and the radiator hisses contentedly...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (2)
As of 2018-05-23 04:03 GMT
Find Nodes?
    Voting Booth?