[Dbix-class] PK::Auto::Pg bugfix: GUINEA PIGS WANTED

[Brandon Black]

On 11/24/05, Krzysztof Krzyzaniak <eloy at kofeina.net> wrote:
> Matt S Trout wrote:
> > On Wed, Nov 23, 2005 at 01:31:32PM -0600, Brandon Black wrote:
> >
> >>Been away from the keyboard for a number of days, I'll get caught up
> >>on the backlogged email after this weekend probably.  But in the
> >>meantime...
> >>
> >>As of PostgreSQL 8.1, autoincrementing sequences can (and should) be
> >>specified differently than before.  To cover the new syntax, a small
> >>change needs to be made to Class/DBIx/PK/Auto/Pg.pm.  Works For Me,
> >>YMMV:
> >>
> >>30c30
> >><         m!^nextval\('"?([^"']+)"?'::(?:text|regclass)\)!;
> >>---
> >>
> >>>        m!^nextval\('"?([^"']+)"?'::text\)!;
> >
> >
> > I (as usual) don't have a postgres install handy, can those who do apply and
> > report results please?
>
> Dump in pg8.1 shows serial primary keys as:
>
> id bigint DEFAULT nextval(('account_id_seq'::text)::regclass) NOT NULL,
>
> I can test patch tonight.
>

That one-line patch definitely won't work for the case you're showing.
 Was that column definition imported via pgdump from pre-8.1?  My
serials created under 8.1 show up with "DEFAULT
nextval('account_id_seq'::regclass)".  Here's the 8.1 ChangeLog entry
that goes into detail about the change:

----------------------------------

#

Add proper dependencies for arguments of sequence functions (Tom)

In previous releases, sequence names passed to nextval(), currval(),
and setval() were stored as simple text strings, meaning that renaming
or dropping a sequence used in a DEFAULT clause made the clause
invalid. This release stores all newly-created sequence function
arguments as internal OIDs, allowing them to track sequence renaming,
and adding dependency information that prevents improper sequence
removal. It also makes such DEFAULT clauses immune to schema renaming
and search path changes.

Some applications might rely on the old behavior of run-time lookup
for sequence names. This can still be done by explicitly casting the
argument to text, for example nextval('myseq'::text).

Pre-8.1 database dumps loaded into 8.1 will use the old text-based
representation and therefore will not have the features of OID-stored
arguments. However, it is possible to update a database containing
text-based DEFAULT clauses. First, save this query into a file, such
as fixseq.sql:

SELECT  'ALTER TABLE ' ||
    pg_catalog.quote_ident(n.nspname) || '.' ||
    pg_catalog.quote_ident(c.relname) ||
    ' ALTER COLUMN ' || pg_catalog.quote_ident(a.attname) ||
    ' SET DEFAULT ' ||
    regexp_replace(d.adsrc,
                   $$val\(\(('[^']*')::text\)::regclass$$,
                   $$val\(\1$$,
                   'g') ||
    ';'
FROM    pg_namespace n, pg_class c, pg_attribute a, pg_attrdef d
WHERE   n.oid = c.relnamespace AND
    c.oid = a.attrelid AND
    a.attrelid = d.adrelid AND
    a.attnum = d.adnum AND
    d.adsrc ~ $$val\(\('[^']*'::text\)::regclass$$;

Next, run the query against a database to find what adjustments are
required, like this for database db1:

psql -t -f fixseq.sql db1

This will show the ALTER TABLE commands needed to convert the database
to the newer OID-based representation. If the commands look
reasonable, run this to update the database:

psql -t -f fixseq.sql db1 | psql -e db1

This process must be repeated in each database to be updated.