Thank you for the feedback. I am considering just centralizing pk
generation to the HQ. Basically, every so often, the branch will connect
back to HQ to get back a block of pk for each table. This way, performance
impact is limited and maintainence is simple.
thanks.
>From: Joe Conway <mail@joeconway.com>
>To: anon permutation <anonpermutation@hotmail.com>
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Merging Data from Multiple DB
>Date: Mon, 03 Jan 2005 07:20:54 -0800
>
>anon permutation wrote:
>>For performance reasons, each branch must has its own database and a
>>centralized transactional system is not an option.
>>
>>I was considering just centralizing primary keys generation, but that
>>seems very slow too.
>>
>>Segmenting primary keys among the branches is doable, but it is too much
>>of a maintainence nightmare.
>>
>>What do you suggest?
>
>We have a similar application. What we did is this:
>
>1. Each database instance is assigned a unique identifier, stored in a 1
>row, 1 column table (with a trigger to ensure it stays that way).
>
>2. Write a function that can take two integers, convert them to text, and
>concatenate them. In our case we convert to hex and concatenate with a
>delimiter character.
>
>3. Write another function, called something like 'nextrowid', that takes a
>sequence name as its argument. Use the sequence name to get the next value
>from the sequence, lookup the local unique identifier from the table
>defined in #1, and pass them both to the function defined in #2.
>
>4. Use nextrowid('seq_name') to generate your primary keys.
>
>HTH,
>
>Joe