Re: [Gnumed-devel] re: tricky

[Karsten Hilbert]

On Sat, May 28, 2005 at 09:33:37PM +0800, Syan Tan wrote:

> When replaying this dump file into a database we cannot
> guarantee that the original primary keys are still unused.
> However, primary keys are not supposed to carry business
> meaning. Hence the dump file may be constructed in such a way
> that first a new row is created in the "root" table (eg
> identity) and the new "root" primary key is gotten from that
> and used in the process to write appropriate dependant insert
> queries. Likewise with further keyed dependancies ...
> 
> Oh, and:
> 
> - Preferably client_encoding unicode should be used to cleanly
>   get across all text data.
> - The dump file should be runnable inside a transaction.
> 
> can one use begin; set constraints off; ....  set constraints on; commit; ?
One can, however, I am not sure of what use they would be ?
AFAIK there isn't even a performance gain because if you
reenable constraints at the end of the transaction they'll
just get checked at that time rather than before. Also note
that AFAICT "set constraints ..." actually is some complicated
fiddling with the system catalogs prone to breaking. A
performance gain *could* be achieved by dropping indexes and
reindexing at the very end if a lot of data is going into one
and the same table.

My idea was roughly like this:

identity.id = 12

... lot's of appropriate selects are run ...

the resulting dump file:

set client_emcoding ...
set timezone ...

insert into identity () ...

:new_id_identity = currval(identity_id_seq)

insert into health_issue (fk_identity, ...)
values (:new_id_identity, ...)

...

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346