[Spacewalk-list] Database problems upgrading Spacewalk 1.7 -> 1.8

[Jonathan Hoser]

Hi all,

I'm finally upgrading to 1.8
and have managed 1.7 (from 1.6), but am now baffled by issues from the
schema-upgrade:
My Spacewalk is running on Fedora16 and PostgreSQL 9.1.7

 From the subset of
spacewalk-schema-1.7-to-spacewalk-schema-1.8/142-suse-package-keys.sql

There are the following inserts that fail:

insert into rhnPackageKey (id, key_id, key_type_id, provider_id) (select
sequence_nextval('rhn_pkey_id_seq'), 'e3a5c360307e3d54',
lookup_package_key_type('gpg'), lookup_package_provider('Suse') from
dual where not exists (select 1 from rhnPackageKey where key_id =
'e3a5c360307e3d54'));

(right below are another 2 inserts very similar)

Now the problem I am facing is the fact that running that query gets me an
"
ERROR: duplicate key value violates unique constraint "rhn_pkey_keyid_uq"
DETAIL: Key (key_id)=(e3a5c360307e3d54) already exists.
"

dissecting the query, I find that the select delivers two rows:

spaceschema=# select sequence_nextval('rhn_pkey_id_seq'),
'e3a5c360307e3d54', lookup_package_key_type('gpg'),
lookup_package_provider('Suse') from dual where not exists (select 1
from rhnPackageKey where key_id = 'e3a5c360307e3d54');

sequence_nextval | ?column? | lookup_package_key_type |
lookup_package_provider
------------------+------------------+-------------------------+-------------------------

135 | e3a5c360307e3d54 | 100 | 104
136 | e3a5c360307e3d54 | 100 | 104
(2 rows)

which of course - when trying to be inserted - causes the duplicate key
issues.
But why do I get two rows?

The last sub-select is

select 1 from rhnPackageKey where key_id = 'e3a5c360307e3d54'
which returns 0 rows.

Could anyone shed a bit of light on this for me?
Right now I'm quite baffled about the how, why and so on - but maybe I'm
missing something.

Best
-Jonathan

--



Helmholtz Zentrum München
Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH)
Ingolstädter Landstr. 1
85764 Neuherberg
www.helmholtz-muenchen.de
Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe
Geschäftsführer: Prof. Dr. Günther Wess und Dr. Nikolaus Blum
Registergericht: Amtsgericht München HRB 6466
USt-IdNr: DE 129521671