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.

#!/usr/bin/perl
use strict;
use warnings;

my %sequences;
my %ids;
my $current_table;
my $in_copy = 0;
while(<>)
{
    if(/pg_catalog\.setval\('(\w+[^']*)_id_seq',\s*(\d+),/i)
    {
        $sequences{$1} = $2;
    }
    if(/COPY (\w+)\s*\(id/i) 
    {
        $in_copy = 1;
        $current_table = $1;
    }
    elsif($in_copy)
    {
        if(/\\\./)
        {
            $in_copy = 0;
        }
        else
        {
            /^(\d+)\t/;
            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";
    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s