Oliver
I've read your email, with interest. I haven't had to deal with this
sort of problem in PostgreSQL, but I have frequently dealt with it in a
Sybase environment, first encountered about 25 years ago.
I am most curious to know why you didn't use the same sequence for both
tables, I must be missing something. If there is a gotcha, I'd like to
know about it as I can see this being an issue in a load sharing
environment.
Many years ago, before auto sequencing was common, we set up explicit
functions to generate sequwnce numbers. Whilst this had some perormance
costs in multi-user systems, it did have benefits in terms of making it
easier to restrict the need for row locking to the underlying data table.
Robin St.Clair
On 13/05/2014 10:44, Oliver Kohll - Mailing Lists wrote:
I'm sure no one else on this list has done anything like this, but here's a
cautionary tale.
I wanted to synchronise data in two tables (issue lists) - i.e. whenever a
record is added into one, add a similar record into the other. The two tables
are similar in format but not exactly the same so only a subset of fields are
copied. Both tables have synthetic primary keys, these can't be used to match
data as they are auto-incrementing sequences that might interfere. What I could
have done perhaps is get both tables to use the same sequence, but what I
actually did is:
* join both tables based on a natural key
* use that to copy any missing items from table1 to table2
* truncate table1 and copy all of table2's rows to table1
* run this routine once an hour
The natural key was based on the creation timestamp (stored on insert) and the
one of the text fields, called 'subject'.
The problem came when someone entered a record with no subject, but left it
null. When this was copied over and present in both tables, the *next* time the
join was done, a duplicate was created because the join didn't see them as
matching (null != null).
So after 1 hour there were two records. After two there were four, after 3, 8
etc.
When I logged in after 25 hrs and noticed table access was a little slow, there
were 2^25 = 33 million records.
That's a learning experience for me at least. It's lucky I did check it at the
end of that day rather than leaving it overnight, otherwise I think our server
would have ground to a halt.
One other wrinkle to note. After clearing out these rows, running 'VACUUM
table2', 'ANALYZE table2' and 'REINDEX table table2', some queries with simple
sequence scans were taking a few seconds to run even though there are only a
thousand rows in the table. I finally found that running CLUSTER on the table
sorted that out, even though we're on an SSD so I would have thought seeking
all over the place for a seq. scan wouldn't have made that much difference. It
obviously does still make some.
Oliver Kohll
www.agilebase.co.uk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
