Hi All,

I got 2 very simple tables, one hold few values and got a unique field
to be used as identifier and the other table got a reference to that
table (foreign key).
At my last testing before the problem I inserted to each one of the
tables about 1 million rows, for the process of inserting new
information I begin a transaction, delete all the previous information,
insert the new information and commit the transaction.
So far it never gave me any problems (though its the first time that I
let a foreign key to exist) but now from some reason it "locks" a
specific row from being deleted.... for example:
If ill query: DELETE FROM table1; it will just get stuck...
If ill try: DELETE FROM table1 WHERE table1_id=1523; it will work in
most cases but for some rows it will just get stuck!
Anyone know anything about this weird problem?
By the way when I restarted the DB server I was able to delete the
current row which stucked the process but then I got stuck at some other
row at the table....

Thanks in advance,
Ben-Nes Yonatan

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

Hi there

I have much problems with wierd locking too. I thing
that it problem is connected with localisation - when
i update a "problem" row (which have text fields) with
any different text data in text fields - all locks
dissapper. I hope this info can help to solve this bug
(because in PG 7.2.3 i didnt saw this problems//)

--
Thanks

BNY> Hi All,

BNY> I got 2 very simple tables, one hold few values and got a unique field
BNY> to be used as identifier and the other table got a reference to that
BNY> table (foreign key).
BNY> At my last testing before the problem I inserted to each one of the
BNY> tables about 1 million rows, for the process of inserting new
BNY> information I begin a transaction, delete all the previous information,
BNY> insert the new information and commit the transaction.
BNY> So far it never gave me any problems (though its the first time that I
BNY> let a foreign key to exist) but now from some reason it "locks" a
BNY> specific row from being deleted.... for example:
BNY> If ill query: DELETE FROM table1; it will just get stuck...
BNY> If ill try: DELETE FROM table1 WHERE table1_id=1523; it will work in
BNY> most cases but for some rows it will just get stuck!
BNY> Anyone know anything about this weird problem?
BNY> By the way when I restarted the DB server I was able to delete the
BNY> current row which stucked the process but then I got stuck at some other
BNY> row at the table....

BNY> Thanks in advance,
BNY> Ben-Nes Yonatan

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



--
Ñ óâàæåíèåì,
Èãîðü mailto:go@ugnn.ru


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Ben-Nes Yonatan wrote:

> If ill query: DELETE FROM table1; it will just get stuck...
> If ill try: DELETE FROM table1 WHERE table1_id=1523; it will work in
> most cases but for some rows it will just get stuck!
> Anyone know anything about this weird problem?
> By the way when I restarted the DB server I was able to delete the
> current row which stucked the process but then I got stuck at some other
> row at the table....

What do you mean by "get stuck"?
Are you sure it's not scanning one of the tables to check references
before deleting? If you don't have an index on the table in question
then PG will have to scan the entire table.

To check for locks, try:
SELECT * FROM pg_stat_activity;
SELECT * FROM pg_locks;

Let's see what's actually happening.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

go wrote:
> Hi there
>
> I have much problems with wierd locking too. I thing
> that it problem is connected with localisation - when
> i update a "problem" row (which have text fields) with
> any different text data in text fields - all locks
> dissapper. I hope this info can help to solve this bug
> (because in PG 7.2.3 i didnt saw this problems//)

Doesn't sound likely. Are you seeing this problem with PostgreSQL, or
perhaps with Access talking to PostgreSQL over ODBC.

If you can provide a simple, repeatable example then I'm sure the
developers will be interested.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Well what I mean at getting stuck is that some rows can be deleted fast
when you delete them with their specific id while there are rows which
when I tried to delete them it just didnt respond (seems like working
endlessly so i stop it after an hour or so).
Interesting that this morning I was able to delete all of the data in a
reasonable time... dunno whats the diffrence... the only diffrence that
I can think of is that I deleted the content of table2 and that from
some reason cleared the "locks" on this talbe though I remember testing
it afterwards and it didnt help.
Maybe its connected to the fact that I connected both of the tables with
a foreign key? I also got second thoughts about using foreign keys
between my tables at the DB that I currently build... I always use
foreign keys when I can but I noticed at the DB which im working on now
(will contain millions of rows) that its making the process of deleting
the content way too slow and I need to do it each day.... am I correct
with what im doing?

Thanks again,
Yonatan


Richard Huxton wrote:

> Ben-Nes Yonatan wrote:
>
>> If ill query: DELETE FROM table1; it will just get stuck...
>> If ill try: DELETE FROM table1 WHERE table1_id=1523; it will work in
>> most cases but for some rows it will just get stuck!
>> Anyone know anything about this weird problem?
>> By the way when I restarted the DB server I was able to delete the
>> current row which stucked the process but then I got stuck at some
>> other row at the table....
>
>
> What do you mean by "get stuck"?
> Are you sure it's not scanning one of the tables to check references
> before deleting? If you don't have an index on the table in question
> then PG will have to scan the entire table.
>
> To check for locks, try:
> SELECT * FROM pg_stat_activity;
> SELECT * FROM pg_locks;
>
> Let's see what's actually happening.




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org