Re: [Gnumed-bugs] <bug>: install indian drug data pack

[Karsten Hilbert]

On Thu, May 16, 2013 at 12:12:48AM -0400, Dr.Leonard Horatio McCoy wrote:

> user comment  : install indian drug data pack
> 
> client version: 1.3.3

This is the SQL which PostgreSQL fails:

> INSERT INTO ref.branded_drug (description, preparation) SELECT
>       s_id.brand_name, s_id.form
> FROM
>       staging.india_drugs s_id
> WHERE NOT EXISTS (
>       SELECT 1 FROM ref.branded_drug r_bd WHERE
>               r_bd.description = s_id.brand_name
>                       AND
>               r_bd.preparation = s_id.form
> )

As we can see, it does not directly set ref.branded_drug.pk
(nor should it because that's the primary key which is
auto-set by the sequence ref.substance_brand_pk_seq.
However, PostgreSQL complains that it is told to insert a
duplicate value into the .pk column, which of course fails:

> 2013-05-16 09:40:27  ERROR     gm.bootstrapper 
> (d:\workplace\gnumed-client.1.3.3\build\pyi.win32\gnumed\outpyz1.pyz\gnumed.pycommon.gmpsql::run()
>  #238): 
> c:\users\dell\appdata\local\temp\gnumed\gm-kyzq5b\gm-dl-ya1_ap\install-data-pack.sql:337556:
>  duplicate key value violates unique constraint "substance_brand_pkey"

This can only happen if the sequence is out-of-sync with
what's actually in the table. Which, again, can only happen
if it was modified directly, manually, -- or if the link
between the column and the sequence was/is broken.

A few months back you reported a problem for which the only
explanation coming to mind was a mismatch between a foreign
key pointing to a primary key and its sequence. Back then I
asked for very specific details (which you never provided)
and warned of accumulation of ever severe problems down the
road (like now).

Back then I suspected that it might have come about by
restoring a backup which only partially succeeded or by
disabling foreign keys during restore or some such measure.

I would assume you will want to dig for those mails in the
list archive and answer the details I asked for.

The data pack installs (repeatedly) just fine in my
database. I'd be interested in reports if it doesn't for
others.

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346