David M Kerr

Here is a little perl script that I use to give me a list of tables in a schema, a rowcount and the pg_relation_size of a table.

The usage is just:

pg_rowcount <database> <schema>

The output looks like this:

./pg_rowcount.pl david.kerr pg_catalog
Table                                Rows                 Size
--------------------------------------------------------------
pg_statistic                         1853              1672 kB
pg_type                               910               168 kB
pg_attribute                         9569              1488 kB
pg_class                             1371               416 kB
pg_authid                              52                16 kB
pg_index                              766               112 kB
pg_operator                           705               104 kB
pg_database                            44                16 kB
[...]

You can download the script here

PostgreSQL provides the ability to return a value on insert. so for example:

create table foo (id serial, name text);
insert into foo('dave') returning id;

Will return the auto-generated value for "id" assigned to 'dave'

You can leverage this from within Perl by doing something like this:

my $foo_insert = << "EOH";
insert into foo(?) returning id
EOH

my $foo_cur = $dbh->prepare($foo_insert);
$foo_cur->execute('Dave');
my $foo_rec = $foo_cur->fetchrow_hashref();
print $$foo_rec{"id"};

Of course, you can use whichever fetch method you're comfortable with.

I once ran into an instance where the Unix admins didn't believe me that i was running out of shared memory despite the errors, I was showing them. I wrote this perl script to allocate chunks of shared memory until it failed to prove to them that yes, regardless of what you have the global ulimits set to my user's limits were lower.

#!/usr/bin/perl -w

use IPC::SysV qw(IPC_PRIVATE IPC_RMID S_IRWXU);

$size = 50000000;
$id = shmget(IPC_PRIVATE, $size, S_IRWXU) || die "$!";
sleep 10;
shmctl($id, IPC_RMID, 0)