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 .
