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

[Jan Pazdziora]

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?

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