rows affected inside a transaction

[x5150]

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.

[gam3]

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:

1
1
1

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.

[kyle]

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.

[x5150]

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