[PERFORM] ACCESS EXCLUSIVE lock

[Atesz]

Hi!

I have a problem with ACCESS EXCLUSIVE lock when I drop a reference in 
transaction. I have 2 tables: 
create table  a(id SERIAL primary key);

create table  b(id SERIAL primary key references a(id));

After that I have 2 processes: P1, P2
In P1:
begin;
ALTER TABLE b DROP CONSTRAINT  b_id_fkey;

In P2:
SELECT * FROM a;

And I'm waiting for the result, but I don't get until P1 finishes.
I know the DROP CONSTRAINT put an ACCESS EXCLUSIVE table LOCK into the 
TABLE a, and the SELECT is stopped by this LOCK in P2.
Note: I cannot commit the P1 earlier, because it's a very long 
transaction (more hours, data conversion transaction)

My question:  Why need this strict locking?

In my opinion there isn't exclusion between the DROP CONSTRAINT and the 
SELECT.


Thanks for your suggestions!
Regards,
Antal Attila



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] ACCESS EXCLUSIVE lock

[Atesz]

Scott Marlowe wrote:

What if, a minute or two after the drop contraint, you issue a rollback?
  
After the DROP CONSTRAINT I insert 4 million rekords into the TABLE b. 
After the inserts I remake the dropped constraints, and commit the 
transaction (P1). This solution is faster then the conventional method 
without the constraint's trick.
In my work the table A is a dictionary table (key-value pairs) with 
100-200 records, and the TABLE b has 20 columns with 10 references to 
TABLE a. So my experience is that I have to drop constraints before the 
4 million inserts and remake those after it.


If there is an error in my transaction (P1) and I have to rollback, 
there isn't problem, because my inserts lost from TABLE b and the 
dropped constraints may be rolled back. In my opinion there isn't 
exclusion between  a dropped constraint (reference from b to a) and a 
select on TABLE a. If I think well the dropped constraint have to seem 
in other transation (for example: P2). And it doesn't have to seem in my 
transaction, because it has already dropped.



Thanks your suggestions!
Regards,
Antal Attila

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] ACCESS EXCLUSIVE lock

[Atesz]

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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] planner/optimizer question

[Atesz]

Hi,

You should try the next queries:

select support_person_id from ticket_crm_map where crm_id = 7 GROUP BY
support_person_id;
select support_person_id from ticket_crm_map where crm_id = 1 GROUP BY
support_person_id;

It can use the 'ticket_crm_map_crm_id_suppid' index. 

Generally the Postgres use an k-column index if columns of your
conditions are prefix of the index column.
For example:
CREATE INDEX test_idx on test(col1,col2,col3,col4);
SELECT * FROM test WHERE col1=3 AND col2=13;  -- This can use the index.

But the next queries cannot use the index:
SELECT * FROM test WHERE col1=3 AND col3=13;.
SELECT * FROM test WHERE col2=3;

If you have problem with seq_scan or sort, you can disable globally and
locally: 
SET enable_seqscan=0;
SET enable_sort = 0;

Regards, Antal Attila



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Simply join in PostrgeSQL takes too long

[Atesz]

Hi,

You can try some variation:

SELECT 
  book_id
FROM  
  bookgenres, genre_children
WHERE
   bookgenres.genre_id = genre_children.genre_child_id AND  
   genre_children.genre_id = 1
GROUP BY book_id
LIMIT 10

The next works if the 'genre_child_id' is UNIQUE on the 'genre_children'
table.

SELECT 
  book_id
FROM  
  bookgenres
WHERE
   bookgenres.genre_id = (SELECT genre_child_id FROM genre_children
WHERE genre_id = 1)
GROUP BY book_id
LIMIT 10

You may need some index. Try these with EXPLAIN!
CREATE INDEX bookgenres_genre_id_book_id ON bookgenres(genre_id,
book_id);  or
CREATE INDEX bookgenres_book_id_genre_id ON bookgenres(book_id,
genre_id);
CREATE INDEX genre_children_genre_id ON genre_children(genre_id);

Regards, Antal Attila



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match