[Spacewalk-list] duplicate key value during Spacewalk 1.6 to 1.7, using postgresql

[Jan Pazdziora]

On Wed, Mar 07, 2012 at 08:54:24PM +0000, Alan Pittman wrote:
> Hi,
>   I'm upgrading Spacewalk w/postgresql on a RHEL6, x86_64 server. All went well until I ran the schema update. Got the following  error message:
> 
>                                      ?column?                                      
> ------------------------------------------------------------------------------------
>  spacewalk-schema-1.6-to-spacewalk-schema-1.7/110-create_pxt_session.sql.postgresql
> (1 row)
> 
> CREATE FUNCTION
> COMMIT
>                                 ?column?                                 
> -------------------------------------------------------------------------
>  spacewalk-schema-1.6-to-spacewalk-schema-1.7/111-rhnPackageKey-data.sql
> (1 row)
> 
> psql:/var/log/spacewalk/schema-upgrade/20120307-154732-script.sql:5090: ERROR:  duplicate key value violates unique constraint "rhn_pkey_keyid_uq"
> 
> This is just the bottom of the schema-upgrade log file. If you want to see the whole file, let me know and I'll send it. Just trying to conserve space here.
> 

Can you replace the 111-rhnPackageKey-data.sql file with

-- Fedora 16, 17, 18
update rhnPackageKey set provider_id = lookup_package_provider('Fedora')
where key_id in ('067f00b6a82ba4b7', '50e94c991aca3465', '0983129322b3b81a');

-- Fedora 16
insert into rhnPackageKey (id, key_id, key_type_id, provider_id)
select sequence_nextval('rhn_pkey_id_seq'), '067f00b6a82ba4b7', lookup_package_key_type('gpg'), lookup_package_provider('Fedora')
from dual
where not exists ( select 1 from rhnPackageKey where key_id = '067f00b6a82ba4b7' );
-- Fedora 17
insert into rhnPackageKey (id, key_id, key_type_id, provider_id)
select sequence_nextval('rhn_pkey_id_seq'), '50e94c991aca3465', lookup_package_key_type('gpg'), lookup_package_provider('Fedora')
from dual
where not exists ( select 1 from rhnPackageKey where key_id = '50e94c991aca3465' );
-- Fedora 18
insert into rhnPackageKey (id, key_id, key_type_id, provider_id)
select sequence_nextval('rhn_pkey_id_seq'), '0983129322b3b81a', lookup_package_key_type('gpg'), lookup_package_provider('Fedora')
from dual
where not exists ( select 1 from rhnPackageKey where key_id = '0983129322b3b81a' );

and start the schema upgrade anew?

-- 
Jan Pazdziora
Principal Software Engineer, Satellite Engineering, Red Hat