[GENERAL] DBD::Pg & cursors & total results

[Patrick Verdon]

Hi,

I'm using PostgreSQL with Apache and mod_perl to keep
persistent connections. Thus far I have been doing 
standard selects using DBD::Pg on tables sometimes
returning 1000s of records. This becomes a problem with
the larger queries, as the memory needed by Perl to 
receive the result set bloats the Web server processes
horrendously (exceeding 20+ MB per process).

To address this memory issue I've started using cursors to 
select the 10 or 20 records that the user will see
at one time. This alleviates the memory issue to some
extent, however this approach creates a new problem: I 
can't seem to (easily) determine the total results that 
a select statement will yield when using a cursor. The 
only way I've managed to get hold of the total results 
is by doing the following, which seems absolutely 
ludicrous (!):

 $dbh = DBI->connect( ...)
 $dbh->{AutoCommit} = 0;
 $dbh->do("declare c cursor for select * from reports order by id");
 $dbh->do("move 10 in c");

 --> $dbh->do("select * into table temp from reports where id > 0");
 --> my $num_rows = $dbh->do("delete from temp where id > 0");
 --> $dbh->do("drop table temp");

 my $sth = $dbh->prepare("fetch 20 in c");
 $sth->execute();
 while ( my $r = $sth->fetchrow_hashref ) { .. }
 $dbh->commit();
 $sth->finish();
 $dbh->disconnect();

Is there any other less expensive way to find out what
$num_rows is going to be for a select statement? Am I
missing something obvious?

I'd be grateful for any help / thoughts.

Cheers.



Patrick

-- 

#===#
\  KAN Design & Publishing Ltd  /
/  T: +44 (0)1223 511134\
\  F: +44 (0)1223 571968/
/  E: mailto:[EMAIL PROTECTED]  \ 
\  W: http://www.kan.co.uk  /
#===#

[GENERAL] NOTICE: SIAssignBackendId: discarding tag 2147483647

[Patrick Verdon]

Hi,

I'm running PostgreSQL 6.3.2. on SPARC Solaris 2.6. 
I have several Web applications that I run under
mod_perl, using Apache::DBI to achieve persistent
database connections. I now have three databases
that connect on initialisation when Apache starts 
up, which essentially means each Apache process has 
an associated postgres process (or 2 by the looks 
of it). When I have the three databases connecting
at start-up, there are about 60+ postgres processes.

When there are three databases that connect at 
start-up, the most complicated application fails
with the following error:

   NOTICE:  SIAssignBackendId: discarding tag 2147483647

The other applications do not fail. If I only start
the database for this application on start-up, I do
not get this message. 

The machine has 256 MB RAM + 256 MB swap. I don't think
there should be a resource problem.

Does anyone know what is going on here and how I can 
fix it? Will 6.4 fix it? I know it's worth trying 6.4
but do you know why it would fix it? 

I have included some debugging output below.

Also - is this the right list to mail such problems
to?

Thanks.



Patrick

--

query is:
SELECT * FROM images ORDER BY name

/kan/databases/pgsql/bin/postmaster: ServerLoop:handling reading 5
/kan/databases/pgsql/bin/postmaster: ServerLoop:handling reading 5
/kan/databases/pgsql/bin/postmaster: ServerLoop:handling writing 5
/kan/databases/pgsql/bin/postmaster child[0]: execv(/kan/databases/pgsql/bin/postgres, 
-p, -d2, -P5, -e, -v 65536, marchcom_site_engine, )
FindBackend: found "/kan/databases/pgsql/bin/postgres" using argv[0]
NOTICE:  SIAssignBackendId: discarding tag 2147483647
FATAL 1:  Backend cache invalidation initialization failed
---debug info---
Quiet =f
Noversion =f
timings   =f
dates =European
bufsize   =64
sortmem   =512
query echo =   f
DatabaseName = [marchcom_site_engine]


InitPostgres()..
/kan/databases/pgsql/bin/postmaster: BackendStartup: pid 24702 user web db 
marchcom_site_engine socket 5
/kan/databases/pgsql/bin/postmaster: reaping dead processes...
/kan/databases/pgsql/bin/postmaster: CleanupProc: pid 24702 exited with status 0
/kan/databases/pgsql/bin/postmaster: ServerLoop:handling reading 5
/kan/databases/pgsql/bin/postmaster: ServerLoop:handling reading 5
/kan/databases/pgsql/bin/postmaster: ServerLoop:handling writing 5
/kan/databases/pgsql/bin/postmaster child[0]: execv(/kan/databases/pgsql/bin/postgres, 
-p, -d2, -P5, -e, -v 65536, marchcom_site_engine, )
FindBackend: found "/kan/databases/pgsql/bin/postgres" using argv[0]
NOTICE:  SIAssignBackendId: discarding tag 2147483647
FATAL 1:  Backend cache invalidation initialization failed

-- 

#===#
\  KAN Design & Publishing Ltd  /
/  T: +44 (0)1223 511134\
\  F: +44 (0)1223 571968/
/  E: mailto:[EMAIL PROTECTED]  \ 
\  W: http://www.kan.co.uk  /
#===#

[GENERAL] Digital UNIX

[Patrick Verdon]

Hi, 

I'm hoping to run a production application using 
PostgreSQL on Digital Unix, however I found this
note in the INSTALL file under 6.4.2:

   Digital Unix   Alpha v6.4.2 1998-10-29   Minor patchable problems 
   4.0

Is anyone making use of PostgreSQL in a production
environment on this platform? What are the minor
problems mentioned here? Should I avoid Digital 
Unix?

Cheers.



Patrick

-- 

#===#
\  KAN Design & Publishing Ltd  /
/  T: +44 (0)1223 511134\
\  F: +44 (0)1223 571968/
/  E: mailto:[EMAIL PROTECTED]  \ 
\  W: http://www.kan.co.uk  /
#===#