Re: DBI/DBD::Pg mem. use goes exponential - Mailing list pgsql-general

From SCassidy@overlandstorage.com
Subject Re: DBI/DBD::Pg mem. use goes exponential
Date
Msg-id OFB9916957.EED74E44-ON8825708B.00634AB3-8825708B.00773A03@overlandstorage.com
Whole thread Raw
In response to DBI/DBD::Pg mem. use goes exponential  ("Bob Parkinson" <rwp@biome.ac.uk>)
List pgsql-general
Hi,

You cannot actually say that "do" is more correct than prepare/execute (for
a non-select statement), because "do" is just a shortcut.  According to the
DBI documentation, it does a prepare and execute for you.  In fact, if you
look at the DBI.pm code, that is what it is doing.  I do agree that the
original $dbh->prepare($stmt)->execute () || die;
is kind of odd.


However, certainly inside the loop where the original  code example was
doing the same basic statement in a loop, it would be more efficient to
"prepare" the statement outside the loop, using a placeholder for the $key
value, then "execute" multiple times.

As far as "finish" is concerned, unless you did not go through all the
data, it is not necessary to call "finish". From the docs:

      The "finish" method is rarely needed, and frequently overused ...

He may also need to check again for errors after the end of the loop.  It
may be there in the actual code.  The original example appears to have been
"paraphrased".

Susan



                  
                           Keary Suska
                  
                      <hierophant@pcisys.net>        To:       Postgres General <pgsql-general@postgresql.org>
                  
                           Sent by:                  cc:
                  
                                                     Subject:  Re: [GENERAL] DBI/DBD::Pg mem. use goes exponential
                  

                  
                      pgsql-general-owner@pos         |-------------------|
                  
                      tgresql.org                     | [ ] Expand Groups |
                  
                                                      |-------------------|
                  

                  
                           09/29/2005 10:23
                  
                      AM
                  

                  

                  




on 9/29/05 6:38 AM, rwp@biome.ac.uk purportedly said:

> Any ideas please? This seems well weird to me, but...

Assuming the code snippet is representative, a couple things:

1. $dbh->prepare($stmt)->execute () || die;

This is mostly a point of protocol, but I can't resist pointing it out.
Although the above syntax works, it is not precisely correct. You should
instead:

    $dbh->do($stmt) || die;

2. my $rv=$sth->execute() || die;

You should always finish() every "select" statement handle, or both DBI and
libpq will leak:

    $sth->finish;

after the closing bracket of the while() loop.

Best,

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster





----------------------------------------------------------------------------------------------
See our award-winning line of tape and disk-based
backup & recovery solutions at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------


pgsql-general by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: insertion becoming slow
Next
From: "Oisin Glynn"
Date:
Subject: DBLINK on WINDOWS?