Lists: | pgsql-bugs |
---|
From: | "Tiago Jacobs" <tiago(at)mdtestudio(dot)com(dot)br> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #3479: contraint exclusion and locks |
Date: | 2007-07-23 13:24:08 |
Message-ID: | 200707231324.l6NDO8Nm010057@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 3479
Logged by: Tiago Jacobs
Email address: tiago(at)mdtestudio(dot)com(dot)br
PostgreSQL version: 8.2.4
Operating system: Linux 2.6.21.5
Description: contraint exclusion and locks
Details:
Hi People!
Connection #1
create table tab (dt_tab date);
create table tab_2 () inherits (tab);
create table tab_1 () inherits (tab);
alter table tab_2 add constraint constraint_x check (dt_tab >= '20070701');
alter table tab_2 add constraint constraint_y check (dt_tab <= '20070731');
alter table tab_1 add constraint constraint_x check (dt_tab >= '20070601');
alter table tab_1 add constraint constraint_y check (dt_tab <= '20070630');
explain select * from tab where dt_tab = '20070705';
>"Result (cost=0.00..73.50 rows=22 width=4)"
>" -> Append (cost=0.00..73.50 rows=22 width=4)"
>" -> Seq Scan on tab (cost=0.00..36.75 rows=11 width=4)"
>" Filter: (dt_tab = '2007-07-05'::date)"
>" -> Seq Scan on tab_2 tab (cost=0.00..36.75 rows=11 width=4)"
>" Filter: (dt_tab = '2007-07-05'::date)"
Connection #2
begin;
lock table tab_1 in exclusive mode;
TrUNCATE TABLE tab_1;
Connection #1
explain select * from tab where dt_tab = '20070705'
oh-ow... It waits while the table (That is not used) is locked.
Even that the final plan dont use tab_2007_07, it wait for unlock of table
for make the plan.
So, if I`m running a VACUUM on specific table, all the querys on the
"master" table don't work.
Best Regards,
Tiago
From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | "Tiago Jacobs" <tiago(at)mdtestudio(dot)com(dot)br> |
Cc: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #3479: contraint exclusion and locks |
Date: | 2007-07-23 14:25:03 |
Message-ID: | 87y7h7kxj4.fsf@oxford.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Tiago Jacobs" <tiago(at)mdtestudio(dot)com(dot)br> writes:
> oh-ow... It waits while the table (That is not used) is locked.
>
> Even that the final plan dont use tab_2007_07, it wait for unlock of table
> for make the plan.
Well one of the reasons exclusive locks are taken are to make changes to
constraints.
> So, if I`m running a VACUUM on specific table, all the querys on the
> "master" table don't work.
VACUUM doesn't take an exclusive lock. VACUUM is designed to be run regularly
without interfering with full use of the table.
You're not running VACUUM FULL are you? That's much more intrusive and
shouldn't be needed in regular operation.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Tiago Jacobs" <tiago(at)mdtestudio(dot)com(dot)br> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #3479: contraint exclusion and locks |
Date: | 2007-07-23 14:37:13 |
Message-ID: | 24459.1185201433@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Tiago Jacobs" <tiago(at)mdtestudio(dot)com(dot)br> writes:
> oh-ow... It waits while the table (That is not used) is locked.
This is not a bug. It has to inspect the table to find out that
there is a constraint.
regards, tom lane
From: | Tiago Daniel Jacobs <tiago(at)mdtestudio(dot)com(dot)br> |
---|---|
To: | Gregory Stark <stark(at)enterprisedb(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org, tgl(at)sss(dot)pgh(dot)pa(dot)us |
Subject: | Re: BUG #3479: contraint exclusion and locks |
Date: | 2007-07-23 14:46:31 |
Message-ID: | 46A4BF47.1080004@mdtestudio.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<small><font face="Arial">Tom and Gregory. U're right! The problem is
that we're using constraints for partitioned tables and by definition,
a partition never, absolutely never, can affect the entire system.<br>
<br>
But I think that we have nothing to do about this. There are plans to
another kind of partitioning? If yes, I would like to contribute. If
not, i`m okay. <br>
<br>
Just tried to help.<br>
<br>
Okay, sorry about take your time for it.<br>
<br>
Tiago<br>
<br>
<br>
Tom Lane escreveu:
<blockquote type="cite">
<pre wrap=""><small><font>"Tiago Jacobs" <a class="moz-txt-link-rfc2396E" href="mailto:tiago(at)mdtestudio(dot)com(dot)br"><tiago(at)mdtestudio(dot)com(dot)br></a> writes:
</font></small></pre>
<blockquote type="cite">
<pre wrap=""><small><font>oh-ow... It waits while the table (That is not used) is locked.
</font></small></pre>
</blockquote>
<pre wrap=""><!---->
<small><font>This is not a bug. It has to inspect the table to find out that
there is a constraint.
regards, tom lane
</font></small></pre>
</blockquote>
<br>
<br>
<br>
</font></small>Gregory Stark escreveu:
<blockquote cite="mid87y7h7kxj4(dot)fsf(at)oxford(dot)xeocode(dot)com" type="cite">
<pre wrap="">"Tiago Jacobs" <a class="moz-txt-link-rfc2396E" href="mailto:tiago(at)mdtestudio(dot)com(dot)br"><tiago(at)mdtestudio(dot)com(dot)br></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">oh-ow... It waits while the table (That is not used) is locked.
Even that the final plan dont use tab_2007_07, it wait for unlock of table
for make the plan.
</pre>
</blockquote>
<pre wrap=""><!---->
Well one of the reasons exclusive locks are taken are to make changes to
constraints.
</pre>
<blockquote type="cite">
<pre wrap="">So, if I`m running a VACUUM on specific table, all the querys on the
"master" table don't work.
</pre>
</blockquote>
<pre wrap=""><!---->
VACUUM doesn't take an exclusive lock. VACUUM is designed to be run regularly
without interfering with full use of the table.
You're not running VACUUM FULL are you? That's much more intrusive and
shouldn't be needed in regular operation.
</pre>
</blockquote>
<br>
</body>
</html>
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/html | 2.3 KB |
From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | "Tiago Daniel Jacobs" <tiago(at)mdtestudio(dot)com(dot)br> |
Cc: | <pgsql-bugs(at)postgresql(dot)org>, <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: BUG #3479: contraint exclusion and locks |
Date: | 2007-07-23 15:34:54 |
Message-ID: | 87fy3fkuap.fsf@oxford.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Tiago Daniel Jacobs" <tiago(at)mdtestudio(dot)com(dot)br> writes:
> <small><font face="Arial">Tom and Gregory. U're right! The problem is
> that we're using constraints for partitioned tables and by definition,
> a partition never, absolutely never, can affect the entire system.<br>
> <br>
> But I think that we have nothing to do about this. There are plans to
> another kind of partitioning? If yes, I would like to contribute. If
> not, i`m okay. <br>
There are lots of ideas of where to go with partitioning including possibly
ditching the use of constraints. But I don't think there's any settled plans
yet.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
From: | "Simon Riggs" <simon(at)2ndquadrant(dot)com> |
---|---|
To: | "Tiago Daniel Jacobs" <tiago(at)mdtestudio(dot)com(dot)br> |
Cc: | "Gregory Stark" <stark(at)enterprisedb(dot)com>, <pgsql-bugs(at)postgresql(dot)org>, <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: BUG #3479: contraint exclusion and locks |
Date: | 2007-07-23 22:23:20 |
Message-ID: | 1185229400.4284.439.camel@ebony.site |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On Mon, 2007-07-23 at 11:46 -0300, Tiago Daniel Jacobs wrote:
> Tom and Gregory. U're right! The problem is that we're using
> constraints for partitioned tables and by definition, a partition
> never, absolutely never, can affect the entire system.
>
Yeh, the problem is that partitioning uses additional information to
avoid reading data. The additional information and the actual data need
to be kept in step, so at some point we may need to re-write that data
and we'll always need a lock to do that.
So whether we use constraints, segment headers or what have you,
there'll still be a need to lock and be locked out.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
From: | Tiago Daniel Jacobs <tiago(at)mdtestudio(dot)com(dot)br> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #3479: contraint exclusion and locks |
Date: | 2007-11-01 15:17:44 |
Message-ID: | 4729EE18.4080907@mdtestudio.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi, why if I REINDEX a inherited table, all tables are locked?<br>
<br>
Example:<br>
[conn #1] REINDEX TABLE month_a;<br>
[conn #2] PREPARE updt as update month_b set col_a=1 where col_b=$1;<br>
<br>
The query on conn#2 waits.<br>
<br>
regards,<br>
Tiago<br>
<br>
Tom Lane escreveu:
<blockquote cite="mid:24459(dot)1185201433(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">"Tiago Jacobs" <a class="moz-txt-link-rfc2396E" href="mailto:tiago(at)mdtestudio(dot)com(dot)br"><tiago(at)mdtestudio(dot)com(dot)br></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">oh-ow... It waits while the table (That is not used) is locked.
</pre>
</blockquote>
<pre wrap=""><!---->
This is not a bug. It has to inspect the table to find out that
there is a constraint.
regards, tom lane
</pre>
</blockquote>
<br>
</body>
</html>
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/html | 1.0 KB |