Postgres Professional
   

some problems when i use postgresql 8.4.2 in my projects .

From: wyx6fox@sina.com
Subject: some problems when i use postgresql 8.4.2 in my projects .
Date: ,
Msg-id: 20100203022830.2A375358C60@mail175-236.sinamail.sina.com.cn
(view: Whole thread, Raw)
Responses: Re: some problems when i use postgresql 8.4.2 in my projects .  (Robert Haas)
Re: some problems when i use postgresql 8.4.2 in my projects .  (Leo Mannhart)
Re: some problems when i use postgresql 8.4.2 in my projects .  (Pierre Frédéric Caillaud<>)
List: pgsql-performance

Tree view

some problems when i use postgresql 8.4.2 in my projects .  (, )
 Re: some problems when i use postgresql 8.4.2 in my projects .  (Robert Haas, )
 Re: some problems when i use postgresql 8.4.2 in my projects .  (Leo Mannhart, )
 Re: some problems when i use postgresql 8.4.2 in my projects .  (Pierre Frédéric Caillaud<>, )
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 .

 

pgsql-performance by date:

Previous:
From: Greg Smith
Date:
Subject: Re: System overload / context switching / oom, 8.3
Next:
From: J Sisson
Date:
Subject: Re: the jokes for pg concurrency write performance