Re: MCPK and sequences

[prev] [thread] [next] [Date index for 2004/09/21]

On Tue, Sep 21, 2004 at 06:31:14PM +0100, Tony Bowden wrote:
> On Tue, Sep 21, 2004 at 05:15:46PM +0100, Stephen Quinney wrote:
> > Someone recently asked me to apply a patch to the Debian packaged
> > version of Class::DBI::Pg to support the discovery of multi-column
> > primary keys (which I did). This got me wondering, what does CDBI do
> > when it has multiple primary keys and a sequence defined? Is it a case
> > of the two not mixing?
> 
> What does Pg do in this case?

I'm not sure, as I've never felt the desire to use MCPKs. I was hoping
that someone who had would be able to tell me. I have also been
wondering if there is a possibility of CDBI::Pg choosing the incorrect
sequence for the primary key if there was one PK but two SERIAL
fields. At the moment, I think it appears to juse use the first
sequence it finds. Here's a perfectly legal if slightly unusual
example:

CREATE TABLE foo (
    bar SERIAL,
    baz SERIAL PRIMARY KEY,
    quux TEXT);

The "select" it does to find the sequence becomes:

SELECT adsrc FROM pg_catalog.pg_attrdef
WHERE
adrelid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='foo');

which gives:

                adsrc
-------------------------------------
 nextval('public.foo_bar_seq'::text)
 nextval('public.foo_baz_seq'::text)
(2 rows)

and it takes the first one which is the sequence on the column bar not
the one we want which is column baz.

Looks like a bug to me, not sure of a solution right now though.

Stephen