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

[Jonathan Hoser]

On 01/17/2013 01:30 PM, Jan Pazdziora wrote:

On Thu, Jan 17, 2013 at 12:49:51PM +0100, Jonathan Hoser wrote:


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.



Does does

        select * from dual;

return?

Yepp it does:
-bash-4.2$ psql spaceschema
psql (9.1.7)
Type "help" for help.

spaceschema=# select * from dual;
 dummy
-------
 X
 X
(2 rows)

spaceschema=#
And here I read that dual was just created for compatibility with Oracle...

Oh well, I manually did the last steps of the upgrade,
and am -- so far -- happily running 1.8.

But this might be interessting to look into for the future.

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20130118/af045859/attachment.htm>