> > I tried something slightly different:
> > * LOCK the table in SHARE ROW EXCLUSIVE mode,
> Well, sure, if you don't mind giving up concurrency of writers completely.
> How likely is a conflict, anyway? If it's very probable then you're not
> losing much, but if it's not then this isn't a good approach.
There are only two tables that would have such conflicts: users and hosts (as i said before, it's a system like ordb.org, but for vulnerable proxies). This has been working for a while and i'm new migrating the whole thing to this new schema (yes, the current one has this concurrency problem as well).
So far i have received over 540,000 emails (there are a lot of automated spam detection systems that mail this address) and this number grows in the order of hundreds per day. All i have to do for testing concurrency problems is carbon-copy those emails to the "testing" address and watch the flames in syslog :) (it takes only ten minutes to see one).
> Within a function the snapshot doesn't get updated, so you cannot see
> results of other transactions that commit after the function starts.
> This means you pretty much have to issue the LOCK as a separate
> interactive command.
I did this, i issued a BEGIN TRANSACTION & CO before calling this function from the C++ program that uses the database (the email parser) and so far it seems to work (haven't seen any dup key error yet).
Thanks for the tip.
