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
