Blobs in Postgresql with Perl

Are you using, or do you want to use Postgresql Blobs with Perl?

Providing you are in the Moose ecosystem, here is Pg::Blobs. Pg::Blobs is a role that adds blobs handling methods to any Moose based package.

Here is how to use it:

package My::App;
use Moose;
with qw/Pg::Blobs/;
# Just provide:
sub pgblobs_dbh{ .. return the DBH .. }
1;

Now your package My::App is capable of managing Postgresql blobs:

my $o = .. and instance of My::App ..
my $blob_id = $o->pgblobs_store_blob($binary_content);
print $o->pgblobs_fetch_blob($blob_id);

A couple of guidelines:

Blobs in Postgresql are just numeric Object IDs. You WILL have to store them in a OID column for later retrieval. If you don't, there is no guarantee they will persist, as any unreferenced blob can get vacuumed away.

You MUST wrap any blob operation in a transaction.

Happy blobing!

Run Postgresql pg_dump from your scripts

If you use Postgresql, you probably noticed that the command line tools who come with it don't accept passwords on the command line.

That's a good thing, as it prevents other users of the system from spying your command line via ps and see your password.

Ok, but what if you want to run let's say pg_dump from your script? Easy, just export an environment variable PGPASSFILE that points to a file that contains your credentials. Note that this file MUST be in mode 0600, to prevent other users to look into it.

If you use Perl, and File::Temp, that's exactly what it does, so here's a Perl snippet that uses pg_dump:

my ( $cf, $cf_name ) = File::Temp::tempfile();
print $cf '*:*:*:*:'.$password."\n";
close($cf);
system('export PGPASSFILE='.$cf_name.'; pg_dump  etc...');
## Don't forget to unlink cf_name to avoid disk pollution.
unlink $cf_name;

Also, don't forget to run in taint mode and sanitise everything you use to build your command. Managing correctly the returned value of the system call is left as an exercise to the reader :)

Happy coding!

J.

Perl Postgresql Test Harness

These days, I'm developing pg_cryobit, a piece of software that'll allow you to manage your PostgreSQL continuous backup strategy with ease.

I stumbled upon Test::postgresql, and at first I was a bit suspicious about it, given the fact the latest version is not recent and that there's no debian build of it.

I decided to give it a go anyway. I installed it manually, and only one dependency needed another manual install. The other ones come as a debian packages (This is one reason why I don't quite like to use the cpan shell).

The tests went smoothly, and in no time I had instances of PostgreSQL ready to test with my own software.

I definitely recommend using it if you're writing PostgreSQL dependent software.

Has anyone has experience with the MySQL flavour of it?