[BUGS] Periodic freezing of backend processes
PostgreSQL 7.0.2 Debian 2.3 (woody) Linux Kernel 2.2.15 Hi, I am finding that I periodically have a backend process just 'freeze' on me. It's not like it's doing something that all of a sudden get's wildly inefficient because this can be when I'm repetitively processing in a loop, and where responsiveness is normally sub-second I have waited hours to see if these terminate and they don't. What can I do to tickle the backend processes to get it to tell me where it is at? I have found that if I kill the backend process that is locked up, then do exactly the same query, it locks up again. BUT if I shut down and restart the postmaster and then do exactly the same query (reload my web page, in fact) the response is immediate again. A couple of other points to note: - the webserver, database server and all are on my laptop - I am the only user and there is only one request active at the time this happens (although I have seen it happen on our production machine, running 6.5.3 as well). - I have seen the freeze happening from PHP scripts as well as from Perl scripts. - A 'ps' does not show the process as 'waiting': $ps flaxww | grep postgres | grep -v grep 00031 15629 1 0 0 5824 1152 select Spts/2 0:00 /usr/lib/postgresql/bin/postmaster -b /usr/lib/postgresql/bin/postgres -B 256 -N 16 -D /var/lib/postgres/data -d 0 -o -F -S 4096 04031 15634 15629 0 0 6432 4380 select Spts/2 11:14 \_ /usr/lib/postgresql/bin/postgres localhost andrew newsfeed UPDATE That's right now, so it's frozen on an 'UPDATE' and looking at my (perl) program the only 'UPDATE' is this one: UPDATE story SET wcount=$count WHERE story_id=$story_id; With story_id being the table's primary key, of course. Up until it froze it was processing one of these UPDATE's every second or two (amongst many other SQL statements). Earlier in the evening a similar thing happened except the statement was an 'INSERT' and the program had been running fine for about three hours before it locked up. If I kill the backend process that has locked up I may get messages when I next do a VACUUM that say I need to recreate the indexes on some table too. Sorry if this is a bit vague, but if there's some signal I can send to the locked process to try and tell where it is or what it's trying to do, perhaps I can find out more next time it happens. I've tried kill -3 and kill -5 but don't seem to get any core files. I'd crank up the logging except that whenever I do that I tend to run out of disk space :-( Thanks, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
[BUGS] INITIALLY DEFERRED / UPDATE in transaction bug
POSTGRESQL BUG REPORT TEMPLATE Your name : Robert B. Easter Your email address : [EMAIL PROTECTED] System Configuration - Architecture (example: Intel Pentium) : Intel Celeron Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.13 Slackware PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.2 Compiler used (example: gcc 2.8.0) : egcs-2.91.66 Please enter a FULL description of your problem: Referential integrity problem. When using an INITIALLY DEFERRED foreign key within a transaction, I give it a value that is not in the referenced table. Then I UPDATE it so that it has a value in the referenced table. Then I COMMIT. I still get an RI error on COMMIT. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: -- CREATE TABLE t1 ( t2_id INTEGER NOT NULL ); CREATE TABLE t2 ( id SERIAL PRIMARY KEY ); ALTER TABLE t1 ADD CONSTRAINT t1_t2_id_fk FOREIGN KEY (t2_id) REFERENCES t2 INITIALLY DEFERRED; BEGIN; INSERT INTO t1 VALUES (0); INSERT INTO t2 VALUES (1); SELECT t2_id FROM t1; SELECT id FROM t2; UPDATE t1 SET t2_id = 1 WHERE t2_id = 0; SELECT t2_id FROM t1; SELECT id FROM t2; COMMIT; -- error -- Or CREATE TABLE t3 ( id SERIAL PRIMARY KEY ); CREATE TABLE t4 ( t3_id INTEGER REFERENCES t3 INITIALLY DEFERRED ); BEGIN; INSERT INTO t4 VALUES (0); INSERT INTO t3 VALUES (1); SELECT t3_id FROM t4; SELECT id FROM t3; UPDATE t4 SET t3_id = 1 WHERE t3_id = 0; SELECT t3_id FROM t4; SELECT id FROM t3; COMMIT; -- again, error If you know how this problem might be fixed, list the solution below: - -- Robert
Re: [BUGS] Periodic freezing of backend processes
Andrew McMillan <[EMAIL PROTECTED]> writes: > I am finding that I periodically have a backend process just 'freeze' on > me. Needs to be looked at, for sure. > What can I do to tickle the backend processes to get it to tell me where > it is at? What I'd do is attach to it with gdb and poke around. gdb path-of-postgres-executable attach PID-of-target-process bt ... It helps if you've compiled the backend with -g. It might also be useful to enable Asserts. > I have found that if I kill the backend process that is locked up, then > do exactly the same query, it locks up again. BUT if I shut down and > restart the postmaster and then do exactly the same query (reload my web > page, in fact) the response is immediate again. Sounds like the problem is associated with some particular configuration of shared memory. Perhaps some prior query has failed to release a lock, or some such? regards, tom lane
Re: [BUGS] Unnexpected results using to_number()
"Andrew Snow" <[EMAIL PROTECTED]> writes:
> # SELECT to_number('12,454.8-', '');
> pqReadData() -- backend closed the channel unexpectedly.
In current sources I get a NULL result, which seems to be what the
code author intended originally. However this seems a little bit
inconsistent --- shouldn't it raise a bad-format error instead?
For example,
regression=# SELECT to_number('12,454.8-', ' ');
ERROR: Bad numeric input format ' '
Seems odd that no spaces means "return NULL" but 1 or more spaces
doesn't.
regards, tom lane
[BUGS] Re: [SQL] MAX() of 0 records.
I wrote: > Comments anyone? What exactly *should* be the behavior of an UPDATE > that uses an aggregate function and a join to another table? Over what > set of tuples should the aggregate be evaluated? Further note on this: SQL99 specifies: ::= UPDATE SET [ WHERE ] ... 5) A in a shall not directly contain a . so the construct is definitely not SQL-compliant. Maybe we should just forbid it. However, if you are joining against another table (which itself is not an SQL feature) then it seems like there is some potential use in it. What do people think of my implicit-GROUP-BY-ctid idea? That would basically say that the aggregate is computed over all the tuples that join to a single target tuple. regards, tom lane
[BUGS] Re: [HACKERS] Re: [SQL] MAX() of 0 records.
Philip Warner <[EMAIL PROTECTED]> writes: >> What do people think of my implicit-GROUP-BY-ctid idea? >> That would basically say that the aggregate is computed over all the >> tuples that join to a single target tuple. > Sounds perfect to me... Note that it would not meet your expectation that update t1 set f2=count(*) from t2 where t1.f1=2 and t2.f1=t1.f1 ; means the same as update t1 set f2=(Select Count(*) from t2 where t2.f1=t1.f1) where t1.f1 = 2 ... at least not without some kind of outer-join support too. With an inner join, t1 tuples not matching any t2 tuple wouldn't be modified at all. regards, tom lane
Re: [HACKERS] Re: [BUGS] Unnexpected results using to_number()
Karel Zak <[EMAIL PROTECTED]> writes:
> On Sun, 9 Jul 2000, Tom Lane wrote:
>> "Andrew Snow" <[EMAIL PROTECTED]> writes:
# SELECT to_number('12,454.8-', '');
pqReadData() -- backend closed the channel unexpectedly.
>>
>> In current sources I get a NULL result, which seems to be what the
>> code author intended originally. However this seems a little bit
> my original code not return NULL, but return numeric_in(NULL, 0, 0) for
> this situation.
Yeah, I know. What did you expect that to produce, if not a NULL?
>> inconsistent --- shouldn't it raise a bad-format error instead?
What do you think about raising an error instead of returning NULL?
regards, tom lane
