I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html 16  2848 
Hi Ben,
Check this mailing list for "foreign keys" and "deadlock".
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if they
update rows which reference the same parent keys in reverse order.
Check your foreign keys...
HTH,
Csaba.
On Wed, 2004-06-16 at 17:33, Ben wrote: I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Hi Ben,
Check this mailing list for "foreign keys" and "deadlock".
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if they
update rows which reference the same parent keys in reverse order.
Check your foreign keys...
HTH,
Csaba.
On Wed, 2004-06-16 at 17:33, Ben wrote: I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Ben <be***@silentmedia.com> writes: I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them.
The processes involved in the deadlock should be identified in the
DETAIL message for the deadlock error. Perhaps you are using client
code that doesn't show you the DETAIL? If so, look in the postmaster
log. You can correlate the process PIDs mentioned in the message to
pg_stat_activity.procpid.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Ben <be***@silentmedia.com> writes: I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them.
The processes involved in the deadlock should be identified in the
DETAIL message for the deadlock error. Perhaps you are using client
code that doesn't show you the DETAIL? If so, look in the postmaster
log. You can correlate the process PIDs mentioned in the message to
pg_stat_activity.procpid.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
I hate to reply so quickly to my own post, but I found out how to turn
on the current_query (I somehow missed that config file line - doh!).
It turns out that my insert statements are blocking each other. (An
insert in one thread blocks the same insert with different values in
another thread.) That's curious to me, as I thought that inserts were
atomic? It doesn't happen for all inserts, just some of them, and the
problem gets worse as the threads increase in number.
I compiled my client libs with thread safety, and am running on linux.
On Jun 16, 2004, at 8:33 AM, Ben wrote: I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
I hate to reply so quickly to my own post, but I found out how to turn
on the current_query (I somehow missed that config file line - doh!).
It turns out that my insert statements are blocking each other. (An
insert in one thread blocks the same insert with different values in
another thread.) That's curious to me, as I thought that inserts were
atomic? It doesn't happen for all inserts, just some of them, and the
problem gets worse as the threads increase in number.
I compiled my client libs with thread safety, and am running on linux.
On Jun 16, 2004, at 8:33 AM, Ben wrote: I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
See my previous post, there I mentioned updates, but it applies to
inserts too.
On Wed, 2004-06-16 at 17:53, Ben wrote: I hate to reply so quickly to my own post, but I found out how to turn
on the current_query (I somehow missed that config file line - doh!).
It turns out that my insert statements are blocking each other. (An
insert in one thread blocks the same insert with different values in
another thread.) That's curious to me, as I thought that inserts were
atomic? It doesn't happen for all inserts, just some of them, and the
problem gets worse as the threads increase in number.
I compiled my client libs with thread safety, and am running on linux.
On Jun 16, 2004, at 8:33 AM, Ben wrote:
I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(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
See my previous post, there I mentioned updates, but it applies to
inserts too.
On Wed, 2004-06-16 at 17:53, Ben wrote: I hate to reply so quickly to my own post, but I found out how to turn
on the current_query (I somehow missed that config file line - doh!).
It turns out that my insert statements are blocking each other. (An
insert in one thread blocks the same insert with different values in
another thread.) That's curious to me, as I thought that inserts were
atomic? It doesn't happen for all inserts, just some of them, and the
problem gets worse as the threads increase in number.
I compiled my client libs with thread safety, and am running on linux.
On Jun 16, 2004, at 8:33 AM, Ben wrote:
I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(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
Thanks for the quick reply (and summary!).
According to the messages I've found on the list, basically the answer
seems to be, "don't do this." On the other hand, pretty much every
message on the subject is pre-7.4. There is some mention of using
deferred foreign keys to reduce the chance for a deadlock, but nothing
says doing that actually eliminates the chance.
Is this just a known limitation? In this particular instance, I
probably could get rid of my foreign keys and if things go bad it
wouldn't hurt anything.... but I make heavy use of foreign keys
throughout the rest of my schema, which are useful for the programs
that aren't doing data mining. I wouldn't want to get rid of those
foreign keys.
On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote: Hi Ben,
Check this mailing list for "foreign keys" and "deadlock".
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if
they
update rows which reference the same parent keys in reverse order.
Check your foreign keys...
HTH,
Csaba.
On Wed, 2004-06-16 at 17:33, Ben wrote: I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(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
Thanks for the quick reply (and summary!).
According to the messages I've found on the list, basically the answer
seems to be, "don't do this." On the other hand, pretty much every
message on the subject is pre-7.4. There is some mention of using
deferred foreign keys to reduce the chance for a deadlock, but nothing
says doing that actually eliminates the chance.
Is this just a known limitation? In this particular instance, I
probably could get rid of my foreign keys and if things go bad it
wouldn't hurt anything.... but I make heavy use of foreign keys
throughout the rest of my schema, which are useful for the programs
that aren't doing data mining. I wouldn't want to get rid of those
foreign keys.
On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote: Hi Ben,
Check this mailing list for "foreign keys" and "deadlock".
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if
they
update rows which reference the same parent keys in reverse order.
Check your foreign keys...
HTH,
Csaba.
On Wed, 2004-06-16 at 17:33, Ben wrote: I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(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
Hello,
Deferred checks can greatly reduce the deadlock chance because of the
timing of the foreign key check. I won't say it can eliminate them,
and I don't think anyone here would suggest that you don't use Foreign keys.
Sincerely,
Joshua D. Drake
Ben wrote: Thanks for the quick reply (and summary!).
According to the messages I've found on the list, basically the answer
seems to be, "don't do this." On the other hand, pretty much every
message on the subject is pre-7.4. There is some mention of using
deferred foreign keys to reduce the chance for a deadlock, but nothing
says doing that actually eliminates the chance.
Is this just a known limitation? In this particular instance, I probably
could get rid of my foreign keys and if things go bad it wouldn't hurt
anything.... but I make heavy use of foreign keys throughout the rest of
my schema, which are useful for the programs that aren't doing data
mining. I wouldn't want to get rid of those foreign keys.
On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote:
Hi Ben,
Check this mailing list for "foreign keys" and "deadlock".
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if they
update rows which reference the same parent keys in reverse order.
Check your foreign keys...
HTH,
Csaba.
On Wed, 2004-06-16 at 17:33, Ben wrote:
I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(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
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Hello,
Deferred checks can greatly reduce the deadlock chance because of the
timing of the foreign key check. I won't say it can eliminate them,
and I don't think anyone here would suggest that you don't use Foreign keys.
Sincerely,
Joshua D. Drake
Ben wrote: Thanks for the quick reply (and summary!).
According to the messages I've found on the list, basically the answer
seems to be, "don't do this." On the other hand, pretty much every
message on the subject is pre-7.4. There is some mention of using
deferred foreign keys to reduce the chance for a deadlock, but nothing
says doing that actually eliminates the chance.
Is this just a known limitation? In this particular instance, I probably
could get rid of my foreign keys and if things go bad it wouldn't hurt
anything.... but I make heavy use of foreign keys throughout the rest of
my schema, which are useful for the programs that aren't doing data
mining. I wouldn't want to get rid of those foreign keys.
On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote:
Hi Ben,
Check this mailing list for "foreign keys" and "deadlock".
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if they
update rows which reference the same parent keys in reverse order.
Check your foreign keys...
HTH,
Csaba.
On Wed, 2004-06-16 at 17:33, Ben wrote:
I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them. I have a ton of select queries
(but none for update), and then a single query to insert into a table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(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
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
So is everybody simply accepting the chance of deadlocks, thanks to
their foreign keys? Given what I know about why this problem exists, it
doesn't seem to have an easy solution.... but from my naive perspective
it seems like something that we shouldn't have to just live with,
either.
On Jun 16, 2004, at 10:50 AM, Joshua D. Drake wrote: Hello,
Deferred checks can greatly reduce the deadlock chance because of the
timing of the foreign key check. I won't say it can eliminate them,
and I don't think anyone here would suggest that you don't use Foreign
keys.
Sincerely,
Joshua D. Drake
Ben wrote: Thanks for the quick reply (and summary!).
According to the messages I've found on the list, basically the
answer seems to be, "don't do this." On the other hand, pretty much
every message on the subject is pre-7.4. There is some mention of
using deferred foreign keys to reduce the chance for a deadlock, but
nothing says doing that actually eliminates the chance.
Is this just a known limitation? In this particular instance, I
probably could get rid of my foreign keys and if things go bad it
wouldn't hurt anything.... but I make heavy use of foreign keys
throughout the rest of my schema, which are useful for the programs
that aren't doing data mining. I wouldn't want to get rid of those
foreign keys.
On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote: Hi Ben,
Check this mailing list for "foreign keys" and "deadlock".
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if
they
update rows which reference the same parent keys in reverse order.
Check your foreign keys...
HTH,
Csaba.
On Wed, 2004-06-16 at 17:33, Ben wrote:
I'm doing a bunch of data mining against a postgres database and
have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process,
and
I can't figure out what's causing them. I have a ton of select
queries
(but none for update), and then a single query to insert into a
table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(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
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
<jd.vcf>
---------------------------(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
So is everybody simply accepting the chance of deadlocks, thanks to
their foreign keys? Given what I know about why this problem exists, it
doesn't seem to have an easy solution.... but from my naive perspective
it seems like something that we shouldn't have to just live with,
either.
On Jun 16, 2004, at 10:50 AM, Joshua D. Drake wrote: Hello,
Deferred checks can greatly reduce the deadlock chance because of the
timing of the foreign key check. I won't say it can eliminate them,
and I don't think anyone here would suggest that you don't use Foreign
keys.
Sincerely,
Joshua D. Drake
Ben wrote: Thanks for the quick reply (and summary!).
According to the messages I've found on the list, basically the
answer seems to be, "don't do this." On the other hand, pretty much
every message on the subject is pre-7.4. There is some mention of
using deferred foreign keys to reduce the chance for a deadlock, but
nothing says doing that actually eliminates the chance.
Is this just a known limitation? In this particular instance, I
probably could get rid of my foreign keys and if things go bad it
wouldn't hurt anything.... but I make heavy use of foreign keys
throughout the rest of my schema, which are useful for the programs
that aren't doing data mining. I wouldn't want to get rid of those
foreign keys.
On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote: Hi Ben,
Check this mailing list for "foreign keys" and "deadlock".
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if
they
update rows which reference the same parent keys in reverse order.
Check your foreign keys...
HTH,
Csaba.
On Wed, 2004-06-16 at 17:33, Ben wrote:
I'm doing a bunch of data mining against a postgres database and
have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process,
and
I can't figure out what's causing them. I have a ton of select
queries
(but none for update), and then a single query to insert into a
table.
Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem
useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(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
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
<jd.vcf>
---------------------------(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
Ben,
My personal solution is to patch the postgres sources so the foreign key
check does NOT lock the parent record. This has the disadvantage that in
some situations the foreign key semantics are not guarrantied, but for
my application works just fine. I think there was a patch posted to do
exactly this, but I didn't use that one. Note that the postgres
regression test was still running through fine after disabling the
parent record locking, so the basic FK functionality is not affected by
that.
Now this patch worked for me, but I won't post it, cause it's just too
dangerous, and I only have it for one postgres version and won't
maintain it. If you want to take your data at danger, patch for yourself
;-)
Hint: take a look at
src/backend/utils/adt/ri_triggers.c
in the postgres sources, that file contains the foreign key logic.
Cheers,
Csaba.
On Wed, 2004-06-16 at 20:11, Ben wrote: So is everybody simply accepting the chance of deadlocks, thanks to
their foreign keys? Given what I know about why this problem exists, it
doesn't seem to have an easy solution.... but from my naive perspective
it seems like something that we shouldn't have to just live with,
either.
On Jun 16, 2004, at 10:50 AM, Joshua D. Drake wrote:
Hello,
Deferred checks can greatly reduce the deadlock chance because of the
timing of the foreign key check. I won't say it can eliminate them,
and I don't think anyone here would suggest that you don't use Foreign
keys.
Sincerely,
Joshua D. Drake
Ben wrote: Thanks for the quick reply (and summary!).
According to the messages I've found on the list, basically the
answer seems to be, "don't do this." On the other hand, pretty much
every message on the subject is pre-7.4. There is some mention of
using deferred foreign keys to reduce the chance for a deadlock, but
nothing says doing that actually eliminates the chance.
Is this just a known limitation? In this particular instance, I
probably could get rid of my foreign keys and if things go bad it
wouldn't hurt anything.... but I make heavy use of foreign keys
throughout the rest of my schema, which are useful for the programs
that aren't doing data mining. I wouldn't want to get rid of those
foreign keys.
On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote:
Hi Ben,
Check this mailing list for "foreign keys" and "deadlock".
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if
they
update rows which reference the same parent keys in reverse order.
Check your foreign keys...
HTH,
Csaba.
On Wed, 2004-06-16 at 17:33, Ben wrote:
> I'm doing a bunch of data mining against a postgres database and
> have
> run into an interesting problem with deadlocks. The problem is,
> postgres is detecting them and then wacking the offending process,
> and
> I can't figure out what's causing them. I have a ton of select
> queries
> (but none for update), and then a single query to insert into a
> table.
> Nothing selects from that table. So where could the deadlock be?
>
> pg_stat_activity has a column named current_query, which would seem
> useful in tracking this down, but it's not being populated.
>
> Oh, I'm running 7.4.2.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(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
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
<jd.vcf>
---------------------------(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
---------------------------(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
Ben,
My personal solution is to patch the postgres sources so the foreign key
check does NOT lock the parent record. This has the disadvantage that in
some situations the foreign key semantics are not guarrantied, but for
my application works just fine. I think there was a patch posted to do
exactly this, but I didn't use that one. Note that the postgres
regression test was still running through fine after disabling the
parent record locking, so the basic FK functionality is not affected by
that.
Now this patch worked for me, but I won't post it, cause it's just too
dangerous, and I only have it for one postgres version and won't
maintain it. If you want to take your data at danger, patch for yourself
;-)
Hint: take a look at
src/backend/utils/adt/ri_triggers.c
in the postgres sources, that file contains the foreign key logic.
Cheers,
Csaba.
On Wed, 2004-06-16 at 20:11, Ben wrote: So is everybody simply accepting the chance of deadlocks, thanks to
their foreign keys? Given what I know about why this problem exists, it
doesn't seem to have an easy solution.... but from my naive perspective
it seems like something that we shouldn't have to just live with,
either.
On Jun 16, 2004, at 10:50 AM, Joshua D. Drake wrote:
Hello,
Deferred checks can greatly reduce the deadlock chance because of the
timing of the foreign key check. I won't say it can eliminate them,
and I don't think anyone here would suggest that you don't use Foreign
keys.
Sincerely,
Joshua D. Drake
Ben wrote: Thanks for the quick reply (and summary!).
According to the messages I've found on the list, basically the
answer seems to be, "don't do this." On the other hand, pretty much
every message on the subject is pre-7.4. There is some mention of
using deferred foreign keys to reduce the chance for a deadlock, but
nothing says doing that actually eliminates the chance.
Is this just a known limitation? In this particular instance, I
probably could get rid of my foreign keys and if things go bad it
wouldn't hurt anything.... but I make heavy use of foreign keys
throughout the rest of my schema, which are useful for the programs
that aren't doing data mining. I wouldn't want to get rid of those
foreign keys.
On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote:
Hi Ben,
Check this mailing list for "foreign keys" and "deadlock".
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if
they
update rows which reference the same parent keys in reverse order.
Check your foreign keys...
HTH,
Csaba.
On Wed, 2004-06-16 at 17:33, Ben wrote:
> I'm doing a bunch of data mining against a postgres database and
> have
> run into an interesting problem with deadlocks. The problem is,
> postgres is detecting them and then wacking the offending process,
> and
> I can't figure out what's causing them. I have a ton of select
> queries
> (but none for update), and then a single query to insert into a
> table.
> Nothing selects from that table. So where could the deadlock be?
>
> pg_stat_activity has a column named current_query, which would seem
> useful in tracking this down, but it's not being populated.
>
> Oh, I'm running 7.4.2.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(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
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
<jd.vcf>
---------------------------(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
---------------------------(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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Paul C |
last post by:
I have a font size being applied to an element, however I have been unable
to track where it is coming from.
Is there an app that will tell you the complete style sheet property of an
element...
|
by: Ben Amada |
last post by:
Hello! There are a couple of visitors (out of dozens) to this web page who
are causing the following error to occur:
"Object reference not set to an instance of an object."
I'm actually...
|
by: Ben |
last post by:
I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending...
|
by: Alan Pretre |
last post by:
I have an application installed at a customer site that has been getting a
general network error for a couple of years. I was hoping that .NET 2.0
would clear it up, but unfortunately it didn't. ...
|
by: depkefamily |
last post by:
I have a large C++ program using multiple vendors DLLs which is giving
me a major
headache. Under release mode I get exceptions thrown from what looks
like a dereferenced
corrupt pointer. The...
|
by: Carl J. Van Arsdall |
last post by:
Hey everyone, cPickle is raising an ImportError that I just don't quite
understand. Before I paste the code, let me explain the application.
Basically the part of the application that failed is a...
|
by: Jonathan Wilson |
last post by:
I have a closed source app. I have a .dll plugin for this app (which I am
writing). This plugin contains a bug somewhere which seems to clobber
memory in a "random" fashion (as in, its not...
|
by: tagg3rx |
last post by:
Hi All,
I recently implemented a global error catcher in my application that sends me an e-mail every time an error happens. One error I'm seeing regularly that I can't track down appears to have...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
