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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
