Postgres sequences

For some reason I’m getting plagued by broken sequence numbers in Postgres at the moment. These exhibit themselves like this, despite the fact that your id field is generated by a postgres sequence.

duplicate key value violates unique constraint "ir_property_pkey"
DETAIL:  Key (id)=(181) already exists.

I know how it’s possible to create them, setup a database, then import data from a dump file that has setval statements like this, but I thought I’d learnt from that mistake and I’ve generally avoided that particular method of screwing up my database integrity.

SELECT pg_catalog.setval('account_account_id_seq', 203, true);

Anyhow, it’s become boring manually fixing them so I’ve created a quick script to spot the problems. It probably makes some naive assumptions but it seems to be working for me and making life easier for the right now.

use strict;
use warnings;

my %sequences;
my %ids;
my $current_table;
my $in_copy = 0;
        $sequences{$1} = $2;
    if(/COPY (\w+)\s*\(id/i) 
        $in_copy = 1;
        $current_table = $1;
            $in_copy = 0;
            my $id = $1;
            $ids{$current_table} = $ids{$current_table} && $ids{$current_table} > $id ? $ids{$current_table} : $id;

for my $table (keys %ids)
    my $id = $ids{$table};
    my $sequence = $sequences{$table} || 0;
    if($sequence < $id)
        print "The sequence for $table is too low ($sequence), it should be set to $id\n";


I have finally had a chance to use App::Cmd, a handy Perl module for constructing command line user interfaces for programs. It’s a fine module from the prolific Ricardo Signes.

It is designed for the non trivial command line app which has multiple ‘tasks’. Take a look at programs like git and svn for an example. Dist::Zilla is the most well known of application I know of that has actually been implemented using the library. It makes it trivial for you to push those tasks into separate modules and builds the help system automatically. It deals with all the command line parsing but you can override that if you want to. I like it’s default behaviour though because it integrates nicely into the help system and seems generally powerful enough.

The only negatives I’ve found are trivial, and they’re really just things to avoid rather than something to worry about.

  • You do want to make sure that you are not loading heavy weight modules in your commands immediately by requiring them rather than using them, otherwise the help system gets bogged down. (This is noted in the documentation)
  • The task names are based on the module names so your tasks have to obey the same punctuation rules as Perl module names.

There were a couple of things that made my progress with the module a little slower than they should have been. Until I can figure out how to turn my feedback into a a contribution back to the project I am noting it here so that I don’t forget them.

  • Filling in the abstract is necessary for the help when you run your app with no commands. There are two methods, provide an abstract function in your command which returns the abstract, or setup your pod correctly. The bit of the pod it’s looking for is the bit after the – in the =head1 NAME section. i.e. “App::Command::task – abstract goes here”. See the ‘ABSTRACT_FROM’ setting in ExtUtils::MakeMaker for a better description of the Abstract. I guess this exposes the fact that I haven’t been in the habit of filling in the abstract in my pod!
  • You need to look at the documentation for App::Cmd::Command in order to figure out what your commands can override and use. This is not really made clear in the documentation and the code because you use App::Cmd::Setup and then use your own app with -command to actually inherit from it. That means the module name appears nowhere in your own code, and should really be referenced in the documentation somewhere.
  • Using global options isn’t really touched on much in the documentation. That would be really useful since an app commonly deals with a common theme. I need to figure out what is possible with the module because I missed that capability when I constructed my first app.

As you can see, this is basically just niggles with the documentation. The main reason I haven’t simply contributed a doc patch already is that I haven’t figured out the best way to express this. Hopefully elucidating these points helps me figure out how to document them better.

It’s a fine module and I can definitely see myself using it a lot in the future.