Sorry again for previous rough messages, some good people advice me to post these problems again With sincere and friendly attitude 。
I think i should do this .
In recently projects , I determine use pg in some medium or big projects , as the projects has been finished, it prove that I made a right decision. Maturity and stability of the postgresql has left us a deep iompression, of coz, there is some problems in postgresql , and finally we take some interim measures to avoid this problems
ENV: postgresql 8.4.2 , CentOS5.4, JDK6.0
problems 1: My previous view is that the insert operation would use a exclusive lock on referenced row on FK , but now I realyzed that I am wrong , after test , pg does not take a exclusive lock on fk row, My prvous test procedure make a stupid mistake: when i check the performence problem , i remove the fks and unique constraints in one time , and the good result make me think the fk is the problem, but actually the unique constraints is the problem .
i shame myself .
after shaming , I think i should pick out some my points:
the unique constraints actualy kill concurrency write transaction when concurrency insert violate the unique constraints , they block each other , i test this in oracle10g, has the same behavour. I think this may be reasonable because the uqniue check must be the seriazable check .
for resolve this problem , i do the unique check in application as possible , but in big concurrency env , this is not good way .
problems 2: my mistake too , i think i misunderstanding read committed isolation , shame myself again too .
problems 3:
After i do some config by this link: http://wiki.postgresql.org/wiki/SlowQueryQuestions .
the cost now just is 2-4 seconds , it is acceptable .
thanks u very much and forgive me .
