Re: reducing IO and memory usage: sending the content of a table to multiple files

On Thu, Apr 02, 2009 at 09:48:33PM +0200, Ivan Sergio Borgonovo wrote:
> I didn't find any elegant example of cursor use in PHP... OK PHP is
> not the most elegant language around... but still any good exapmle
> someone could point me at?

I don't program PHP; but my guess would be something like:

pg_query("BEGIN;");
pg_query("DECLARE cur CURSOR FOR SELECT * FROM t1;");
while (pg_num_rows($result = pg_query("FETCH 1000 FROM cur;")) > 0) {
while($row = pg_fetch_array($result)) {
}
}
pg_query("COMMIT;");

You can obviously increase the "FETCH" upwards and if you're feeling
fancy you could even run the FETCH async from the code that processes
the results. Maybe something like:

pg_query($conn, "BEGIN;");
pg_query($conn, "DECLARE cur CURSOR FOR SELECT * FROM t1;");
pg_send_query($conn, "FETCH 1000 FROM cur;");
while(1) {
$result = pg_get_result($conn);
pg_send_query($conn, "FETCH 1000 FROM cur;");
if (pg_num_rows($result) == 0)
break;
while($row = pg_fetch_array($conn, $result)) {
}
if (pg_get_result($conn)) {
// badness, only expecting a single result
}
}

Note, I've never tried to do PG database stuff from PHP, let alone stuff
like this so it may be all wrong! AFAICT, there's no need to bother
with pg_connection_busy because the call to pg_get_result will block
until the results come back from the database.

> So I think the largest cost of the operation will be IO.
> \copy should be optimised for "raw" data output, but maybe all its
> advantages get lost once I've to use pipes and adding complexity to
> filtering.

Streaming IO is pretty fast, I think you'll be hard pushed to keep up
with it from PHP and you'll end up CPU bound in no time. Be interesting
to find out though.

> I was reading about all the php documents and trying to understand
> how buffers and memory usage works, so I gave a look to MySQL
> documents too...

Not sure about PG, but the C api pretty much always buffers everything
in memory first. There was mention of getting control of this, but I've
got no idea where it got.

--
Sam http://samason.me.uk/