CDBI::Pg primary key lookup

[prev] [thread] [next] [Date index for 2004/06/29]

I am having a bit of a problem with CDBI::Pg when it attempts to look
up the primary key for one of my tables.

The error I am getting is:

DBD::Pg::st execute failed: ERROR:  more than one row returned by a subquery used as an expression [for Statement "SELECT indkey FROM pg_catalog.pg_index
WHERE indisprimary=true AND indrelid=(
SELECT oid FROM pg_catalog.pg_class
WHERE relname = ?)
"] at /usr/share/perl5/DBIx/ContextualFetch.pm line 51.
Compilation failed in require.
BEGIN failed--compilation aborted.

When I checked the database I can see that this table has two OIDs
associated with it (I have no idea what they are really or why there
is two of them). If I change the set_up_table sql statement in the
CDBI::Pg code below from an "=(sub-select)" to "in (sub-select)" it
all works fine, is this a reasonable thing to do?

sub set_up_table {
    my($class, $table) = @_;
    my $dbh = $class->db_Main;
    my $catalog = "";
    if ($class->pg_version >= 7.3) {
        $catalog = 'pg_catalog.';
    }
    # find primary key
    my $sth = $dbh->prepare(<<"SQL");
SELECT indkey FROM ${catalog}pg_index
WHERE indisprimary=true AND indrelid=(
SELECT oid FROM ${catalog}pg_class
WHERE relname = ?)


## Other possibly useful information ###

The table was created as:

CREATE TABLE domains (
        id   SERIAL      PRIMARY KEY,
        name VARCHAR(50) NOT NULL UNIQUE);


the two CDBI related modules are simply:

package Foo::Base;
use strict;
use warnings;

use base 'Class::DBI::Pg';

use Class::DBI::AbstractSearch; # provides search_where() method
use Foo::Config;

__PACKAGE__->set_db
  (
   'Main',
   @Foo::Config::DATABASE{qw(DataSource UserName Password)},
   {AutoCommit => 0, RaiseError => 1}
  );

1;

package Foo::Domains;
use strict;
use warnings;

use base qw(Foo::Base);

__PACKAGE__->set_up_table('domains');

1;