[BUGS] BUG #2458: Postgresql crash
The following bug has been logged online: Bug reference: 2458 Logged by: Cstdenis Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.3 Operating system: FreeBSD 6.1-RELEASE Description:Postgresql crash Details: I am running a very busy postgresql based database server. This server had been running for a few days with a load average in the 20's during most of the day but postgres seemed to still be returning results quickly enough. Then somebody reported a "the database system is in recovery mode" error and on investivation I see a crash. May 26 17:25:56 ai postgres[41657]: [73388-1] ERROR: value too long for type character varying(50) May 26 19:26:38 ai kernel: pid 41463 (postgres), uid 70: exited on signal 11 (core dumped) May 26 17:26:40 ai postgres[41712]: [73388-1] WARNING: terminating connection because of crash of another server process May 26 17:26:40 ai postgres[41712]: [73388-2] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server May 26 17:26:40 ai postgres[41712]: [73388-3] process exited abnormally and possibly corrupted shared memory. Here is some more info ai# gdb postgres postgres.core GNU gdb 6.1.1 [FreeBSD] Copyright 2004 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "i386-marcel-freebsd"...(no debugging symbols found)... Core was generated by `postgres'. Program terminated with signal 11, Segmentation fault. Reading symbols from /usr/local/lib/libintl.so.6...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/libintl.so.6 Reading symbols from /usr/lib/libssl.so.4...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libssl.so.4 Reading symbols from /lib/libcrypto.so.4...(no debugging symbols found)...done. Loaded symbols for /lib/libcrypto.so.4 Reading symbols from /lib/libz.so.3...(no debugging symbols found)...done. Loaded symbols for /lib/libz.so.3 Reading symbols from /lib/libreadline.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/libreadline.so.6 Reading symbols from /lib/libcrypt.so.3...(no debugging symbols found)...done. Loaded symbols for /lib/libcrypt.so.3 Reading symbols from /lib/libm.so.4...(no debugging symbols found)...done. Loaded symbols for /lib/libm.so.4 Reading symbols from /lib/libutil.so.5...(no debugging symbols found)...done. Loaded symbols for /lib/libutil.so.5 Reading symbols from /lib/libc.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/libc.so.6 Reading symbols from /usr/local/lib/libiconv.so.3...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/libiconv.so.3 Reading symbols from /lib/libncurses.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/libncurses.so.6 Reading symbols from /usr/local/lib/postgresql/plpgsql.so...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/postgresql/plpgsql.so Reading symbols from /usr/local/lib/postgresql/slony1_funcs.so...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/postgresql/slony1_funcs.so Reading symbols from /usr/local/lib/postgresql/xxid.so...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/postgresql/xxid.so Reading symbols from /usr/local/lib/postgresql/tsearch2.so...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/postgresql/tsearch2.so Reading symbols from /libexec/ld-elf.so.1...(no debugging symbols found)...done. Loaded symbols for /libexec/ld-elf.so.1 #0 0x35dfeff0 in plpgsql_xact_cb () from /usr/local/lib/postgresql/plpgsql.so (gdb) bt #0 0x35dfeff0 in plpgsql_xact_cb () from /usr/local/lib/postgresql/plpgsql.so #1 0x080ae891 in ReleaseCurrentSubTransaction () #2 0x080af075 in CommitTransactionCommand () #3 0x08213902 in pg_parse_query () #4 0x08215bb8 in PostgresMain () #5 0x081d8740 in ClosePostmasterPorts () #6 0x081da14b in PostmasterMain () #7 0x0818c1ed in main () (gdb) q ai# postmaster --version postmaster (PostgreSQL) 8.1.3 ai# uname -a FreeBSD ai.ctgameinfo.com 6.1-RELEASE FreeBSD 6.1-RELEASE #0: Sun May 21 13:59:57 CDT 2006 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/AI i386 postgresql.conf changed lines from default listen_addresses = '*' max_connections = 450 shared_buffers = 25600 work_mem = 10240 maintenance_work_mem = 102400 max_fsm_pages = 80 max_fsm_relations = 1000 vacuum_cost_delay = 200 vacuum_cost_limit = 50 fsync = on commit_delay = 6 commit_siblings = 2 checkpoint_segments = 8 checkpoint_warning = 30 geqo_threshold = 14 join_collapse_limit = 11 log_destination = 'syslog' silent_mode = on sta
Re: [BUGS] BUG #2458: Postgresql crash
My application does not use any transactions (other than the implicit per statement ones that must exist), but I think I did edit some functions in pgadmin around that time so it may be that bug or something similar/related. I didn't bother to upgrade to 8.1.4 because I thought it was just a security fix that didn't apply to me. I'll upgrade tonight during off hours. - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Cstdenis" <[EMAIL PROTECTED]> Cc: Sent: Friday, May 26, 2006 8:05 PM Subject: Re: [BUGS] BUG #2458: Postgresql crash > "Cstdenis" <[EMAIL PROTECTED]> writes: > > [ SIGSEGV in plpgsql_xact_cb ] > > I think this is probably an instance of the bug patched here: > http://archives.postgresql.org/pgsql-committers/2006-03/msg00022.php > > If you're certain that your applications never redefine a plpgsql > function that might be in active use, then we might need to look > harder. Otherwise, please update to 8.1.4 and see if the problem > recurs ... > > regards, tom lane > ---(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
[BUGS] BUG #3302: Crash on gist ltree - PANIC: failed to add item to index page
The following bug has been logged online: Bug reference: 3302 Logged by: Cstdenis Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.3 Operating system: FreeBSD 6.1-RELEASE-p15 Description:Crash on gist ltree - PANIC: failed to add item to index page Details: I was creating the following gist index of an ltree column CREATE INDEX idx_picture_comments_id_tree_gist ON picture_comments USING gist (id_tree); and the server exited on signal 6 (core dumped) Came back up just fine after in recovery mode. Here are the details. /var/log/messages - May 23 07:57:00 ayu postgres[39969]: [6-2] STATEMENT: select count(*) from picture_comments; May 23 08:01:40 ayu postgres[39969]: [7-1] PANIC: failed to add item to index page in "idx_picture_comments_id_tree_gist" May 23 08:01:40 ayu postgres[39969]: [7-2] STATEMENT: CREATE INDEX idx_picture_comments_id_tree_gist^M May 23 08:01:40 ayu postgres[39969]: [7-3]ON picture_comments^M May 23 08:01:40 ayu postgres[39969]: [7-4]USING gist^M May 23 08:01:40 ayu postgres[39969]: [7-5](id_tree); May 23 08:02:01 ayu kernel: pid 39969 (postgres), uid 70: exited on signal 6 (core dumped) May 23 08:02:01 ayu postgres[15722]: [1-1] WARNING: terminating connection because of crash of another server process May 23 08:02:01 ayu postgres[15722]: [1-2] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server May 23 08:02:01 ayu postgres[15722]: [1-3] process exited abnormally and possibly corrupted shared memory. May 23 08:02:01 ayu postgres[15722]: [1-4] HINT: In a moment you should be able to reconnect to the database and repeat your command. Backtrace - ayu# gdb /usr/local/bin/postgres postgres.core (gdb) bt #0 0x2861a3df in kill () from /lib/libc.so.6 #1 0x2861a37e in raise () from /lib/libc.so.6 #2 0x2861909e in abort () from /lib/libc.so.6 #3 0x082efe2a in errfinish () #4 0x082f0367 in elog_finish () #5 0x080850f7 in gistfillbuffer () #6 0x0808459e in gistmakedeal () #7 0x080849e0 in gistmakedeal () #8 0x080e2750 in IndexBuildHeapScan () #9 0x08084f08 in gistbuild () #10 0x082f4205 in OidFunctionCall3 () #11 0x080e228e in index_create () #12 0x081450a7 in DefineIndex () #13 0x0823332e in ProcessUtility () #14 0x08231d7e in PortalSetResultFormat () #15 0x08231f73 in PortalSetResultFormat () #16 0x0823247a in PortalRun () #17 0x0822d9e6 in pg_parse_query () #18 0x0822fbf8 in PostgresMain () #19 0x081f1cda in ClosePostmasterPorts () #20 0x081f3d35 in PostmasterMain () #21 0x081a3866 in main () ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3302: Crash on gist ltree - PANIC: failed to add item to index page
Teodor Sigaev wrote: CREATE INDEX idx_picture_comments_id_tree_gist ON picture_comments USING gist (id_tree); and the server exited on signal 6 (core dumped) Is it reproducible? Pls, send to me dump of ltree column. I haven't been able to reproduce it. I think its a race condition between some of the other processes running at the time. I'm not sure what else was running on it, but the error suggests an insert to me and there may have been a vacuum running (plus there are always plenty of selects running). It is not reasonable for me to send you an ltree dump since the talbe is about 2 million rows, but I'll send a small sample and describe it. The ltree is a tree is id numbers of rows (the table is a linked list of comments and their replies (think slashdot's comment system) the id numbers come from a serial and are in most cases 1-2 levels deep. Here is a sample tho not entirely representative because all very old entries have no replies (its a newer feature) and are therefore one level deep. "1821086" "1819309" "1817475" "1810945" "1810929" "1810913" "1808878" "1795503" "1792553" "1792753" "1792229" "1788609" "1787736" "1775321" "1775175" "1775231" "1773624" "1768493" "1767839" "116026.116028.116032" "116021.116048" "29.116063" "116093.116094" "116095.116100" "116101.116104" "116101.116104.116106" "116101.116104.116106.116114.116117" "116093.116094.116096.116122" "116119.116123" "116120.116126" "116093.116094.116096.116103.116127" "116128.116130" "116101.116104.116106.116114.116117.116121.116131" "116101.116104.116106.116114.116117.116121.116131.116136" "116138.116143" "116135.116146" "116138.116143.116148" "115343.115661.116152" "116163.116166" "116168.116171" "116173.116174.116175.116176.116177" "116173.116174.116175.116176.116177.116178" "116179.116188.116191" "116190.116192" "116190.116192.116198.116199" "116190.116192.116198.116199.116200" "116132.116144" "116244.116246" "116249.116251" "116155.116280" "116279.116291" "116260.116297" "116279.116298" "116257.116259.116262.116300" "116303.116306" "116308.116310" "116308.116310.116312" "116320.116321" "116319.116322" "116320.116321.116324" "116333.116335" "116342.116347" "116343.116353" "116359.116360" ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #3762: Inherited serials change on dump/restore
The following bug has been logged online:
Bug reference: 3762
Logged by: Cstdenis
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.5/8.3beta2
Operating system: FreeBSD 6.1
Description:Inherited serials change on dump/restore
Details:
Setup
--
Source: Server 8.2.3. Client 8.3beta2.
Dest: Server 8.2.3. Client 8.2.3.
Description
When I do a dump and restore of my database my serials change.
In the original version, the table looks like this (dump from pgAdmin III)
and references the sequence picture_comments_comment_id_seq
CREATE TABLE picture_comments
(
-- Inherited: comment_id serial NOT NULL,
-- Inherited: user_id integer NOT NULL,
-- Inherited: id_tree ltree NOT NULL DEFAULT ''::ltree,
CONSTRAINT picture_comments_pkey PRIMARY KEY (comment_id)
) INHERITS (base_comments)
WITHOUT OIDS;
when I restore the dump I get this which now references the base table's
serial base_comments_comment_id_seq
CREATE TABLE picture_comments
(
-- Inherited: comment_id integer NOT NULL DEFAULT
nextval('base_comments_comment_id_seq'::regclass),
-- Inherited: user_id integer NOT NULL,
-- Inherited: id_tree ltree NOT NULL DEFAULT ''::ltree,
pid integer NOT NULL,
CONSTRAINT picture_comments_pkey PRIMARY KEY (comment_id)
) INHERITS (base_comments)
WITHOUT OIDS;
When I do the dump with the 8.2 client libraries it seems to work fine so
this seems to be a bug in 8.3.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
