[BUGS] Postmaster can't stop with pg_ctl
POSTGRESQL BUG REPORT Your name : Takuya Koide Your email address : koide-txa (at) necst (dot) nec (dot) co (dot) jp Category: runtime: back-end: Severity: serious Summary: Postmaster can't stop with pg_ctl System Configuration Operating System : Red Hat Enterprise Linux ES release 4 (Nahant Update 3) PostgreSQL version : PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.5 20051201 (Red Hat 3.4.5-2) notice: I use following RPM packages. $ rpm -qa|grep -i postgresql postgresql-server-8.2.4-1PGDG postgresql-plperl-8.2.4-1PGDG postgresql-8.2.4-1PGDG postgresql-contrib-8.2.4-1PGDG postgresql-docs-8.2.4-1PGDG postgresql-plpython-8.2.4-1PGDG postgresql-test-8.2.4-1PGDG postgresql-libs-8.2.4-1PGDG postgresql-devel-8.2.4-1PGDG postgresql-pltcl-8.2.4-1PGDG Compiler used : gcc Hardware: - x86 Versions of other tools: -- Problem Description: I found that pg_ctl can't stop postmaster processes under some conditions. If PostgreSQL's process is abnormal condition (stall), I would like to stop PostgreSQL's process (and restart) with /etc/rc.d/init.d/postgresql But I couldn't stop its process. -- Test Case (reproduce procedures): - I can reproduce with following steps. 1) confirm current status. # ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -' postgres 3507 0.1 1.0 21352 2800 ? S 18:48 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 3509 0.0 0.2 11132 568 ? S 18:48 0:00 postgres: logger process postgres 3514 0.0 0.3 21352 844 ? S 18:48 0:00 postgres: writer process postgres 3515 0.0 0.2 12132 564 ? S 18:48 0:00 postgres: stats buffer process postgres 3516 0.0 0.2 11364 748 ? S 18:48 0:00 postgres: stats collector process 2) connect with psql command by postgres user $ id uid=26(postgres) gid=26(postgres) group=26(postgres) context=user_u:system_r:unconfined_t -bash-3.1$ psql template1 template1=# 3) re-confirm status # ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -' postgres 3507 0.0 1.1 21352 2804 ?S18:48 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 3509 0.0 0.2 11132 568 ? S 18:48 0:00 postgres: logger process postgres 3514 0.0 0.3 21352 852 ? S 18:48 0:00 postgres: writer process postgres 3515 0.0 0.2 12132 564 ? S 18:48 0:00 postgres: stats buffer process postgres 3516 0.0 0.3 11364 772 ? S 18:48 0:00 postgres: stats collector process postgres 3618 0.0 0.6 8476 1752 pts/3 S+ 18:54 0:00 psql template1 postgres 3619 0.0 0.8 22012 2124 ? S 18:54 0:00 postgres: postgres template1 [local] idle 4) send 'SIGSTOP' signal to postgres # kill -SIGSTOP 3619 # ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -' postgres 3507 0.0 1.1 21352 2804 ?S18:48 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 3509 0.0 0.2 11132 568 ? S 18:48 0:00 postgres: logger process postgres 3514 0.0 0.3 21352 852 ? S 18:48 0:00 postgres: writer process postgres 3515 0.0 0.2 12132 564 ? S 18:48 0:00 postgres: stats buffer process postgres 3516 0.0 0.3 11364 772 ? S 18:48 0:00 postgres: stats collector process postgres 3618 0.0 0.6 8476 1752 pts/3 S+ 18:54 0:00 psql template1 postgres 3619 0.0 0.8 22012 2124 ? T 18:54 0:00 postgres: postgres template1 [local] idle 5) try to stop PostgreSQL with normal method # /etc/rc.d/init.d/postgresql stop postgresql stopping service: [fail] 6) confirm status and confirm that PostgreSQL is not stop. (this is problem) # ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -' postgres 3507 0.0 1.1 21352 2816 ? S 18:48 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 3509 0.0 0.2 11132 568 ? S 18:48 0:00 postgres: logger process postgres 3514 0.0 0.3 21352 852 ? S 18:48 0:00 postgres: writer process postgres 3515 0.0 0.2 12132 564 ? S 18:48 0:00 postgres: stats buffer process postgres 3516 0.0 0.3 11364 772 ? S 18:48 0:00 postgres: stats collector process postgres 3618 0.0 0.6 8476 1752 pts/3 S+ 18:54 0:00 psql template1 postgres 3619 0.0 0.8 22012 2124 ? T 18:54 0:00 postgres: postgres template1 [local] idle 7) try to stop PostgreSQL with SIGINT signal. # kill -SIGINT 3507 8) confirm status
RV: [BUGS] BUG #3236: Partitioning has problem with timestamp and timestamptz data type
I'm sorry Tom, you are right. Atentamente, Soluciones Integrales GIS C.A (SIGIS) Christian Gonzalez [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Tom Lane Enviado el: Miércoles, 18 de Abril de 2007 01:24 p.m. Para: Christian Gonzalez CC: pgsql-bugs@postgresql.org Asunto: Re: [BUGS] BUG #3236: Partitioning has problem with timestamp and timestamptz data type Christian Gonzalez [EMAIL PROTECTED] writes: When you use timestamp and timestamptz data type for partitioning implementation, your postgresql partitioning implementation doesen't work fine when you make a SELECT using this columns type. Your example is a bit silly, since you created a table partitioned on logdate, then added two unrelated columns and expected the system to assume the table was partitioned on those. I suppose this is a mistake in the example, but it's not clear what the real complaint is. regards, tom lane ---(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 -- Este mensaje ha sido analizado por MailScanner en busca de virus y otros contenidos peligrosos, y se considera que está limpio. -- Este mensaje ha sido analizado por MailScanner en busca de virus y otros contenidos peligrosos, y se considera que está limpio. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] Postmaster can't stop with pg_ctl
takuya koide wrote: 4) send 'SIGSTOP' signal to postgres # kill -SIGSTOP 3619 # ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -' postgres 3507 0.0 1.1 21352 2804 ?S18:48 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 3509 0.0 0.2 11132 568 ? S 18:48 0:00 postgres: logger process postgres 3514 0.0 0.3 21352 852 ? S 18:48 0:00 postgres: writer process postgres 3515 0.0 0.2 12132 564 ? S 18:48 0:00 postgres: stats buffer process postgres 3516 0.0 0.3 11364 772 ? S 18:48 0:00 postgres: stats collector process postgres 3618 0.0 0.6 8476 1752 pts/3 S+ 18:54 0:00 psql template1 postgres 3619 0.0 0.8 22012 2124 ? T 18:54 0:00 postgres: postgres template1 [local] idle If you stop a process by SIGSTOP you must make it run again with SIGCONT. Otherwise it's just not processing signals, so it'll obviously not shut down. I don't think this is a bug. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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
Re: [BUGS] Postmaster can't stop with pg_ctl
Alvaro Herrera [EMAIL PROTECTED] writes: takuya koide wrote: 4) send 'SIGSTOP' signal to postgres If you stop a process by SIGSTOP you must make it run again with SIGCONT. Otherwise it's just not processing signals, so it'll obviously not shut down. I don't think this is a bug. SIGSTOP is a debugging tool, which would be rendered nigh useless if the postmaster tried to override it automatically. So definitely NOTABUG in my opinion too. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] pg_dump doesn't properly honor -O for sequences
pg_dump -O apparently removes all instances of ALTER TABLE ... OWNER TO from the output, but does not remove ALTER SEQUENCE ... OWNED BY from the output. Specifically this is with SERIAL sequences, which are dumped using the ALTER SEQUENCE syntax (as of 8.2). Normal sequences are dumped using the ALTER TABLE ... OWNER TO syntax, which properly honors -O. Regards, Jeff Davis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] not bug after all, sorry for the noise
On Wed, 2007-04-25 at 11:36 -0700, Jeff Davis wrote: pg_dump -O apparently removes all instances of ALTER TABLE ... OWNER TO from the output, but does not remove ALTER SEQUENCE ... OWNED BY from the output. Specifically this is with SERIAL sequences, which are dumped using the ALTER SEQUENCE syntax (as of 8.2). Normal sequences are dumped using the ALTER TABLE ... OWNER TO syntax, which properly honors -O. Sorry for the noise. I misinterpreted the meaning of OWNED BY to mean the user who owns, not the table who owns. Regards, Jeff Davis ---(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: [BUGS] BUG #3245: PANIC: failed to re-find shared loc k o b j ect
I wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: We could have two kinds of seq scans, with and without support for concurrent inserts. Yeah, I considered that too, but it just seems too error-prone. We could maybe make it trustworthy by having hash_seq_search complain if it noticed there had been any concurrent insertions --- but then you're putting new overhead into hash_seq_search, which kind of defeats the argument for it (and hash_seq_search is a bit of a bottleneck, so extra cycles there matter). I just finished looking through the uses of hash_seq_search, and realized that there is one place where it would be a bit painful to convert to the insertion-safe approach I'm proposing; namely nodeAgg.c. The places where the hashtable iteration is started and used are scattered, and we don't really track whether the iteration is done or not, so it's hard to be sure where to cancel the iteration. It could probably be made to work but it seems like it'd be fragile. I still don't want to introduce more checking overhead into hash_seq_search, though, so what I'm now thinking about is a new dynahash primitive named something like hash_freeze, which'd mark a hashtable as disallowing insertions. If the hashtable is frozen before hash_seq_init then we don't add it to the central list of scans, and therefore there is no cleanup to do at the end. nodeAgg can use this mode since it doesn't modify its hashtable anymore after beginning its readout scan. BTW, we didn't really get into details, but for the insertion-safe case I'm envisioning adding a routine hash_seq_term, which you would need to call if and only if you abandon a hash_seq_search scan without running it to completion (if you do the complete scan, hash_seq_search will automatically call hash_seq_term before returning NULL). All but a very small number of places run their searches to completion and therefore won't require any source code changes with this API. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3252: Select Order by time
On Wed, Apr 25, 2007 at 12:42 AM, in message [EMAIL PROTECTED],
Tom Lane [EMAIL PROTECTED] wrote:
Lee Chua [EMAIL PROTECTED] writes:
When we select and order by time we get 00:00:00 as the latest time of the
day.
Really? It works as expected for me:
regression=# create table foo(f1 time);
CREATE TABLE
regression=# insert into foo values ('1:00:00'),('2:00:00'),('0:00:00'),
regression- # ('23:00:00'), ('23:59:59');
INSERT 0 5
regression=# select * from foo order by f1;
f1
--
00:00:00
01:00:00
02:00:00
23:00:00
23:59:59
(5 rows)
I just wanted to point out that midnight is supported at both ends -- the start
of the day as 00:00:00, and the end of the day as 24:00:00. Perhaps the
application software is not distinguishing these?
Modifying Tom's example to insert one more row, you will see:
f1
--
00:00:00
01:00:00
02:00:00
23:00:00
23:25:59
24:00:00
(6 rows)
I know there are some who require this behavior. (I had to add it to a
database product years ago when it was used to develop an application for fire
departments.)
-Kevin
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [BUGS] pg_dump doesn't properly honor -O for sequences
Jeff Davis [EMAIL PROTECTED] writes: pg_dump -O apparently removes all instances of ALTER TABLE ... OWNER TO from the output, but does not remove ALTER SEQUENCE ... OWNED BY from the output. Why should it? That's not ownership in the same sense. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] pg_dump doesn't properly honor -O for sequences
On Wed, 2007-04-25 at 16:44 -0400, Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: pg_dump -O apparently removes all instances of ALTER TABLE ... OWNER TO from the output, but does not remove ALTER SEQUENCE ... OWNED BY from the output. Why should it? That's not ownership in the same sense. I sent a retraction of the bug report quickly afterward. My apologies. Regards, Jeff Davis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3245: PANIC: failed to re-find shared loc k o b j ect
Tom Lane wrote: I wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: We could have two kinds of seq scans, with and without support for concurrent inserts. Yeah, I considered that too, but it just seems too error-prone. We could maybe make it trustworthy by having hash_seq_search complain if it noticed there had been any concurrent insertions --- but then you're putting new overhead into hash_seq_search, which kind of defeats the argument for it (and hash_seq_search is a bit of a bottleneck, so extra cycles there matter). I just finished looking through the uses of hash_seq_search, and realized that there is one place where it would be a bit painful to convert to the insertion-safe approach I'm proposing; namely nodeAgg.c. The places where the hashtable iteration is started and used are scattered, and we don't really track whether the iteration is done or not, so it's hard to be sure where to cancel the iteration. It could probably be made to work but it seems like it'd be fragile. I still don't want to introduce more checking overhead into hash_seq_search, though, so what I'm now thinking about is a new dynahash primitive named something like hash_freeze, which'd mark a hashtable as disallowing insertions. If the hashtable is frozen before hash_seq_init then we don't add it to the central list of scans, and therefore there is no cleanup to do at the end. nodeAgg can use this mode since it doesn't modify its hashtable anymore after beginning its readout scan. This plan includes having the list of hash tables that mustn't be expanded? And the list would be cleaned up at the end of transaction, to avoid leaks. BTW, we didn't really get into details, but for the insertion-safe case I'm envisioning adding a routine hash_seq_term, which you would need to call if and only if you abandon a hash_seq_search scan without running it to completion (if you do the complete scan, hash_seq_search will automatically call hash_seq_term before returning NULL). All but a very small number of places run their searches to completion and therefore won't require any source code changes with this API. Sounds good to me. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3245: PANIC: failed to re-find shared loc k o b j ect
Heikki Linnakangas [EMAIL PROTECTED] writes: Tom Lane wrote: I still don't want to introduce more checking overhead into hash_seq_search, though, so what I'm now thinking about is a new dynahash primitive named something like hash_freeze, which'd mark a hashtable as disallowing insertions. If the hashtable is frozen before hash_seq_init then we don't add it to the central list of scans, and therefore there is no cleanup to do at the end. nodeAgg can use this mode since it doesn't modify its hashtable anymore after beginning its readout scan. This plan includes having the list of hash tables that mustn't be expanded? And the list would be cleaned up at the end of transaction, to avoid leaks. Right, all that's still the same. This is just a way to exempt certain scans from that machinery, by allowing the caller to declare he doesn't need to modify the hashtable anymore. AFAICS that covers our needs, at least for the present. regards, tom lane ---(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
Re: [BUGS] Postmaster can't stop with pg_ctl
Thank you for your reply. On Wed, 25 Apr 2007 09:44:47 -0400 Alvaro Herrera [EMAIL PROTECTED] wrote: takuya koide wrote: 4) send 'SIGSTOP' signal to postgres # kill -SIGSTOP 3619 # ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -' postgres 3507 0.0 1.1 21352 2804 ?S18:48 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 3509 0.0 0.2 11132 568 ? S 18:48 0:00 postgres: logger process postgres 3514 0.0 0.3 21352 852 ? S 18:48 0:00 postgres: writer process postgres 3515 0.0 0.2 12132 564 ? S 18:48 0:00 postgres: stats buffer process postgres 3516 0.0 0.3 11364 772 ? S 18:48 0:00 postgres: stats collector process postgres 3618 0.0 0.6 8476 1752 pts/3 S+ 18:54 0:00 psql template1 postgres 3619 0.0 0.8 22012 2124 ? T 18:54 0:00 postgres: postgres template1 [local] idle If you stop a process by SIGSTOP you must make it run again with SIGCONT. Otherwise it's just not processing signals, so it'll obviously not shut down. I don't think this is a bug. I am sorry lack of my talk about SIGSTOP. [assumed premise] I have performed PostgreSQL with third-party cluster system and have evaluated that if PostgreSQL's status become unusual, cluster system can stop PostgreSQL. So I have used SIGSTOP to create environment like this case. [result] This cluster system picked up stalled postgres process and tried to stop PostgreSQL with /etc/rc.d/init.d/postgresql. but pg_ctl colud not stop it. (I confirmed that cluster system perform /etc/rc.d/init.d/postgresql) [expectation] I expect that pg_ctl can stop PostgreSQL even if postgres processes or postgres process stalled. Thank you. Best Regards --- Takuya Koide NEC System Technologies, Ltd. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
