Skip site navigation (1) Skip section navigation (2)

Re: ACCESS EXCLUSIVE lock

From: Atesz <atesz(at)ritek(dot)hu>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: ACCESS EXCLUSIVE lock
Date: 2006-10-25 17:41:06
Message-ID: 453FA1B2.4050303@ritek.hu (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> This isn't going to be changed, because the likely direction of future
> development is that the planner will start making use of constraints
> even for SELECT queries.  This means that a DROP CONSTRAINT operation
> could invalidate the plan of a SELECT query, so the locking will be
> essential.
>   
Hi!

I also think the constraints can increase performance of queries, if the 
planner can use them. It will be a great feature in the future! But I 
have more questions about the coherency between a constraint and a 
transaction. Can a constraint live in differenet isolation levels? If I 
drop a constraint in a transaction (T1), it doesn't seem after the drop 
operation in T1. But it should seem in another transaction (T2) in line 
with T1 (if T2 is started between T1's begin and commit!). If T1 start 
after T1's commit, our constraint doesn't have to seem in T2, so the 
planner cannot use it. If I think well, these predicates means the 
constraint follows its isolation level of the transaction.

How does it works in the current release?

If the constraints adapt its transaction why could it invalidate the 
plan of a SELECT query?  A SELECT could use a given constraint, if it's 
dropped without comitting or exists when the SELECT or the tansaction of 
the SELECT starts. I know we have to examine which rows can affect the 
result of the SELECT. The main question in this case is that: A wrong 
row (which break the dropped constraint) can affect the result of the 
SELECT? In my opininon there isn't wrong rows. Do you know such special 
case when it can happen? So some wrong rows can seem in the SELECT?

I know my original problem is not too common, but the parallel 
performance of the PostgreSQL is very important in multiprocessor 
environment. I see, you follow this direction! So you make better 
locking conditions in 8.2 in more cases. Generally the drop constraints 
are running in itself or in short transactions.

We have an optimalization trick when we have to insert more million rows 
into a table in same transaction. Before inserting them we drop the 
foreign key constraints after the begin of the transaction, and remake 
tem after insertations. This method is faster then the conventional 
solution. These trasactions are longer (5-40 minutes on a SunFireV40z).

I read the TODO list and I found more features about deferrability. 
Would you like to implement the deferrable foreign key constraints? If 
you want, in my opinion my posings will thouch it.

Thank you in anticipation!

Regards,
Antal Attila

In response to

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2006-10-25 18:19:10
Subject: Re: commit so slow program looks frozen
Previous:From: Craig A. JamesDate: 2006-10-25 17:28:05
Subject: Re: Best COPY Performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group