in reply to Ignore Relation already exists error from DBI

You want to check the value of $DBI::errstr. Here's an example: 
$sth->execute or do_error($DBI::errstr);

sub do_error {
   my $error=shift;
  return if $error =~ /Relation already exists/;  # or whatever the ac
+tual error is
  die "A serious problem occured. According to the rumours: $error.\n"
+;
}

[download]
Note that the key here is not to die on a return value of undef from the execute, but to execute a subroutine that does some extra checking (which you can make as elaborate as you like).

Update: the above answers your immediate question, but I agree with tomte that your database design probably needs some work.

CU
Robartes-

Replies are listed 'Best First'.
Re: Re: Ignore Relation already exists error from DBI
by global (Novice) on Feb 19, 2003 at 12:43 UTC
    Thanks guys for the ideas I will just have to get the regular expression correct.

    I am dealing with Netflow data which records flows through a router. I am just grouping the flow data from each day into a seperate table until it is a week old and then I will aggregate the data some more.

    Does this still sound like a bad design?

[reply]
      Does this still sound like a bad design?

      To my ears, it does :-)
      The date of the day makes for a perfect column, if you think about it.

      regards,
      tomte

[reply]
        There is a timestamp entry for each record, it is just that I have to call the table something :-))

        Can you help with my regular expression. I was having difficulty so have tried both suggestions and this is what I'm getting back:

        DBD::Pg::st execute failed: ERROR: Relation '20030219' already exists at ./dbasetest.pl line 21.
        Database handle destroyed without explicit disconnect.
        Database handle destroyed without explicit disconnect.

        I have tried various bits and pieces, including: return if $error =~ /Relation\s.0-9*.\salready\sexists/;

        Thanks again.

[reply]
      I am just grouping the flow data from each day into a seperate table until it is a week old and then I will aggregate the data some more. ... Does this still sound like a bad design?

      Yes. By keeping data in separate tables, you're making more work for yourself. Databases are good at aggregating information, and it's easiest if all of the data is in a single table.

      If I was doing this, I'd keep add a (non-key) field to hold the date, and would keep all data in a single file. Then, I could do something like 

        SELECT sum(somefield)
  FROM table
  WHERE datefield BETWEEN ? AND ?

      [download]
      to do aggregate calculations. Or 
        SELECT datefield, sum(somefield)
  FROM table
  WHERE datafield BETWEEN ? AND ?
  GROUP BY datefield

      [download]
      if I needed the aggregates to apply to distinct dates.

      Flushing old data is easy. 

        DELETE from TABLE WHERE datafield < ?

      [download]
      These are all static queries. By keeping all data in a single table, I don't have to reconstruct new queries to plug in a dynamic table name. And I'm letting the database do the aggregate calculations, rather than pulling all of the data over the wire to the client and doing it there.

      If your particular calculations are more complicated, and can't be done in SQL, you may still be able to do part of the aggregation on the server side, reducing the amount of data you have to pull across the wire into the client.

[reply]
[d/l]
[select]
        Hi all,

        this has been great, thanks all for the input.

        At the moment even pushing only about 150Gb a month we are getting about 14000 flows per 5 minute period during the day. I just assumed that if I kept 5 minute data for a week even managing to aggregate somewhat that I would still end up with tables with millions of records in it.

        Having looked around the Postgres site maybe I'm being foolish :-) I will go with the one table and see how I go.

        Thanks all for the advice it has made my life a lot easier.

[reply]

In Section Seekers of Perl Wisdom