Re: [Gnumed-bugs] a query problem when bootstrapping bundle [v18-v19-dynamic]

[Busser, Jim]

On 2013-11-19, at 8:44 AM, Karsten Hilbert <address@hidden> wrote:

> On Sun, Nov 17, 2013 at 08:53:38PM +0000, Jim Busser wrote:
> 
>> On closer examination I had misjudged the purpose of the problem query from 
>> the log which was
>> 
>> insert into dem.org<http://dem.org>_unit (fk_org, description)
>> select
>> (select pk from dem.org<http://dem.org> where dem.org.description = 
>> c_hs.narrative),
>> 'unit of ' || c_hs.narrative
>> from
>> clin.hospital_stay c_hs
>> where
>> not exists (
>> select 1 from dem.org<http://dem.org>_unit
>> where
>> dem.org<http://dem.org>_unit.description = 'unit of ' || c_hs.narrative
>> and
>> fk_org = (select pk from dem.org<http://dem.org> where dem.org.description = 
>> c_hs.narrative)
>> )
> 
> I have pushed to the git tree various patches regarding this
> and a few other problems with bootstrapping.
> 
> I encourage people to test and report their mileage.
> 
> Based on that I'll release 1.4.1 / 19.1 this week.
> 
> Karsten

The revision of the above was insufficient to remedy my data situation, but I 
seem to have figured out that below

        v19-clin-hospital_stay-dynamic.sql

        lines 32 … 46

==========================================
insert into dem.org_unit (fk_org, description)
select
        (select pk from dem.org where dem.org.description = c_hs.narrative 
order by pk limit 1),
        'unit of ' || c_hs.narrative
from
        clin.hospital_stay c_hs
where
        not exists (
                select 1 from dem.org_unit
                where
                        dem.org_unit.description = 'unit of ' || c_hs.narrative
                                and
                        fk_org = (select pk from dem.org where 
dem.org.description = c_hs.narrative order by pk limit 1)
        )
==========================================

in spite of LIMIT 1, still returns duplicate records:

         pk   |                 ?column?                 
        -------+------------------------------------------
         24712 | unit of Enterprise Healthcare Unit
         24709 | unit of VGH
         24710 | unit of Vancouver General - Med Day Care
         24710 | unit of Vancouver General - Med Day Care
         24713 | unit of Vancouver General Hospital
         24714 | unit of Vanc
        (6 rows)

whereas if I instead add a 'distinct' to line 33

32      insert into dem.org_unit (fk_org, description)
33      select distinct ...

this succeeded to insert 5 rows without hitting the duplicate constraint shown 
in the attached log.

Sorry I did not yet figure out to patch, but at least it is identified.

PS we are not done yet with fixing server 19.1 package ... I next hit a problem 
when the bootstrapper could not import

        cannot open file 
[../sql/v18-v19/fixups/v19-dem-lnk_org_unit2ext_id-fixup.sql]
        
        --> see next email

-- Jim

update_db-v18_v19.log
Description: update_db-v18_v19.log