Re: [BUGS] BUG #3760: Comment on restore database
On Mon, 2007-11-19 at 18:38 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Reason to do this now: Perf Tips have been changed for 8.3 to recommend using --single-transaction when restoring pg_dumps, because of fast COPY enhancement. Allowing this to cause an error will prevent the whole dump from restoring. If it were indeed an error condition, this argument might have some merit. Since it is not, I don't have a lot of sympathy for a hasty last-minute change. Well, if its not an ERROR condition, no problem. I interpreted failure to mean ERROR, which luckily it isn't. The failure of the COMMENT isn't any reason for a last-minute change, so request withdrawn. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[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, snip some unrelated columns for brevity -- 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, snip -- 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
[BUGS] BUG #3763: crash after create table with primary key defined
The following bug has been logged online: Bug reference: 3763 Logged by: migo Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: Gentoo Description:crash after create table with primary key defined Details: On psql console: = CREATE TABLE phpbb_acl_roles_data ( role_id INT4 DEFAULT '0' NOT NULL CHECK (role_id = 0), auth_option_id INT4 DEFAULT '0' NOT NULL CHECK (auth_option_id = 0), auth_setting INT2 DEFAULT '0' NOT NULL, PRIMARY KEY (role_id, auth_option_id) ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. wolni= In logs after this: Nov 20 11:02:04 www postgres[32757]: [4-1] wolniNOTICE: CREATE TABLE / PRIMARY KEY will create implicit index phpbb_acl_roles_data_pkey for table Nov 20 11:02:04 www postgres[32757]: [4-2] phpbb_acl_roles_data Nov 20 11:02:04 www postgres[32758]: [2-1] [unknown]LOG: connection received: host=[local] Nov 20 11:02:04 www postgres[32758]: [3-1] wolniLOG: connection authorized: user=wolni database=wolni Nov 20 11:02:04 www postgres[32731]: [2-1] LOG: server process (PID 32757) was terminated by signal 11 Nov 20 11:02:04 www postgres[32731]: [3-1] LOG: terminating any other active server processes Nov 20 11:02:04 www postgres[32758]: [4-1] wolniWARNING: terminating connection because of crash of another server process Nov 20 11:02:04 www postgres[32758]: [4-2] wolniDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another Nov 20 11:02:04 www postgres[32758]: [4-3] server process exited abnormally and possibly corrupted shared memory. Nov 20 11:02:04 www postgres[32758]: [4-4] wolniHINT: In a moment you should be able to reconnect to the database and repeat your command. Nov 20 11:02:04 www postgres[32731]: [4-1] LOG: all server processes terminated; reinitializing Nov 20 11:02:04 www postgres[32759]: [5-1] LOG: database system was interrupted at 2007-11-20 11:01:46 CET Nov 20 11:02:04 www postgres[32759]: [6-1] LOG: checkpoint record is at 0/F5FBD7FC Nov 20 11:02:04 www postgres[32759]: [7-1] LOG: redo record is at 0/F5FBD7FC; undo record is at 0/0; shutdown TRUE Nov 20 11:02:04 www postgres[32759]: [8-1] LOG: next transaction ID: 0/2321145; next OID: 206248 Nov 20 11:02:04 www postgres[32759]: [9-1] LOG: next MultiXactId: 1; next MultiXactOffset: 0 Nov 20 11:02:04 www postgres[32759]: [10-1] LOG: database system was not properly shut down; automatic recovery in progress Nov 20 11:02:04 www postgres[32759]: [11-1] LOG: redo starts at 0/F5FBD844 Nov 20 11:02:04 www postgres[32759]: [12-1] LOG: unexpected pageaddr 0/F0FD4000 in log file 0, segment 245, offset 16596992 Nov 20 11:02:04 www postgres[32759]: [13-1] LOG: redo done at 0/F5FD3194 Nov 20 11:02:05 www postgres[32759]: [14-1] LOG: database system is ready What's wrong? ---(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 #3763: crash after create table with primary key defined
On Tue, 2007-11-20 at 10:12 +, migo wrote: The following bug has been logged online: Bug reference: 3763 Logged by: migo Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: Gentoo Description:crash after create table with primary key defined Details: On psql console: = CREATE TABLE phpbb_acl_roles_data ( role_id INT4 DEFAULT '0' NOT NULL CHECK (role_id = 0), auth_option_id INT4 DEFAULT '0' NOT NULL CHECK (auth_option_id = 0), auth_setting INT2 DEFAULT '0' NOT NULL, PRIMARY KEY (role_id, auth_option_id) ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. wolni= In logs after this: Nov 20 11:02:04 www postgres[32757]: [4-1] wolniNOTICE: CREATE TABLE / PRIMARY KEY will create implicit index phpbb_acl_roles_data_pkey for table Nov 20 11:02:04 www postgres[32757]: [4-2] phpbb_acl_roles_data Nov 20 11:02:04 www postgres[32758]: [2-1] [unknown]LOG: connection received: host=[local] Nov 20 11:02:04 www postgres[32758]: [3-1] wolniLOG: connection authorized: user=wolni database=wolni Nov 20 11:02:04 www postgres[32731]: [2-1] LOG: server process (PID 32757) was terminated by signal 11 Nov 20 11:02:04 www postgres[32731]: [3-1] LOG: terminating any other active server processes Nov 20 11:02:04 www postgres[32758]: [4-1] wolniWARNING: terminating connection because of crash of another server process Looks to me that the process that crashed was not the process that issued the CREATE TABLE command. So it could be something else entirely. Is this behaviour repeatable? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3763: crash after create table with primary keydefined
Simon Riggs wrote: On Tue, 2007-11-20 at 10:12 +, migo wrote: = CREATE TABLE phpbb_acl_roles_data ( role_id INT4 DEFAULT '0' NOT NULL CHECK (role_id = 0), auth_option_id INT4 DEFAULT '0' NOT NULL CHECK (auth_option_id = 0), auth_setting INT2 DEFAULT '0' NOT NULL, PRIMARY KEY (role_id, auth_option_id) ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. wolni= In logs after this: Nov 20 11:02:04 www postgres[32757]: [4-1] wolniNOTICE: CREATE TABLE / PRIMARY KEY will create implicit index phpbb_acl_roles_data_pkey for table Nov 20 11:02:04 www postgres[32757]: [4-2] phpbb_acl_roles_data Nov 20 11:02:04 www postgres[32758]: [2-1] [unknown]LOG: connection received: host=[local] Nov 20 11:02:04 www postgres[32758]: [3-1] wolniLOG: connection authorized: user=wolni database=wolni Nov 20 11:02:04 www postgres[32731]: [2-1] LOG: server process (PID 32757) was terminated by signal 11 Nov 20 11:02:04 www postgres[32731]: [3-1] LOG: terminating any other active server processes Nov 20 11:02:04 www postgres[32758]: [4-1] wolniWARNING: terminating connection because of crash of another server process Looks to me that the process that crashed was not the process that issued the CREATE TABLE command. So it could be something else entirely. To me it does look like it was the CREATE TABLE that crashed. The NOTICE shows that it's process ID 32757 that's running the CREATE TABLE, and the LOG line later on says that that's the process that crashed. Is this behaviour repeatable? I couldn't reproduce this on my laptop with 8.2.4. Migo, is it possible for you to get a core dump and a back trace out of it? Is there anything unusual about the system / compiler / compiler options used? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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 #3763: crash after create table with primary keydefined
Dnia Wt Listopada 20 2007, 12:46, Heikki Linnakangas napisał(a): Simon Riggs wrote: On Tue, 2007-11-20 at 10:12 +, migo wrote: = CREATE TABLE phpbb_acl_roles_data ( role_id INT4 DEFAULT '0' NOT NULL CHECK (role_id = 0), auth_option_id INT4 DEFAULT '0' NOT NULL CHECK (auth_option_id = 0), auth_setting INT2 DEFAULT '0' NOT NULL, PRIMARY KEY (role_id, auth_option_id) ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. wolni= In logs after this: Nov 20 11:02:04 www postgres[32757]: [4-1] wolniNOTICE: CREATE TABLE / PRIMARY KEY will create implicit index phpbb_acl_roles_data_pkey for table Nov 20 11:02:04 www postgres[32757]: [4-2] phpbb_acl_roles_data Nov 20 11:02:04 www postgres[32758]: [2-1] [unknown]LOG: connection received: host=[local] Nov 20 11:02:04 www postgres[32758]: [3-1] wolniLOG: connection authorized: user=wolni database=wolni Nov 20 11:02:04 www postgres[32731]: [2-1] LOG: server process (PID 32757) was terminated by signal 11 Nov 20 11:02:04 www postgres[32731]: [3-1] LOG: terminating any other active server processes Nov 20 11:02:04 www postgres[32758]: [4-1] wolniWARNING: terminating connection because of crash of another server process Looks to me that the process that crashed was not the process that issued the CREATE TABLE command. So it could be something else entirely. Strange thinks it that this happen only when i log into psql via wolni user. When i do this as postgres admin all works fint too. User 'wolni' has granted all privilages to database 'wolni'. To me it does look like it was the CREATE TABLE that crashed. The NOTICE shows that it's process ID 32757 that's running the CREATE TABLE, and the LOG line later on says that that's the process that crashed. Is this behaviour repeatable? I couldn't reproduce this on my laptop with 8.2.4. Migo, is it possible for you to get a core dump and a back trace out of it? Is there anything unusual about the system / compiler / compiler options used? Its gentoo distribution. # gcc -v gcc version 3.4.6 (Gentoo Hardened 3.4.6-r2 p1.5, ssp-3.4.6-1.0, pie-8.7.10) with opts: CFLAGS=-march=prescott -O2 -pipe -fforce-addr -fomit-frame-pointer CXXFLAGS=${CFLAGS} MAKEOPTS=-j2 On another my system (with gentoo too, and postgresql 8.2.4 all queries works). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3763: crash after create table with primary key defined
Dnia Wt Listopada 20 2007, 12:12, Simon Riggs napisał(a): On Tue, 2007-11-20 at 10:12 +, migo wrote: The following bug has been logged online: Bug reference: 3763 Logged by: migo Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: Gentoo Description:crash after create table with primary key defined Details: On psql console: = CREATE TABLE phpbb_acl_roles_data ( role_id INT4 DEFAULT '0' NOT NULL CHECK (role_id = 0), auth_option_id INT4 DEFAULT '0' NOT NULL CHECK (auth_option_id = 0), auth_setting INT2 DEFAULT '0' NOT NULL, PRIMARY KEY (role_id, auth_option_id) ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. wolni= In logs after this: Nov 20 11:02:04 www postgres[32757]: [4-1] wolniNOTICE: CREATE TABLE / PRIMARY KEY will create implicit index phpbb_acl_roles_data_pkey for table Nov 20 11:02:04 www postgres[32757]: [4-2] phpbb_acl_roles_data Nov 20 11:02:04 www postgres[32758]: [2-1] [unknown]LOG: connection received: host=[local] Nov 20 11:02:04 www postgres[32758]: [3-1] wolniLOG: connection authorized: user=wolni database=wolni Nov 20 11:02:04 www postgres[32731]: [2-1] LOG: server process (PID 32757) was terminated by signal 11 Nov 20 11:02:04 www postgres[32731]: [3-1] LOG: terminating any other active server processes Nov 20 11:02:04 www postgres[32758]: [4-1] wolniWARNING: terminating connection because of crash of another server process Looks to me that the process that crashed was not the process that issued the CREATE TABLE command. So it could be something else entirely. Is this behaviour repeatable? Yes, when i type this create table command allways postgresql crashes in that same way. Its strange that when i modify this command to: CREATE TABLE phpbb_acl_roles_data ( role_id INT4 DEFAULT '0' NOT NULL CHECK (role_id = 0), auth_option_id INT4 DEFAULT '0' NOT NULL CHECK (auth_option_id = 0), auth_setting INT2 DEFAULT '0' NOT NULL ); (PRIMARY KEY option cuted off) query works fine. Regards, -- Tomasz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3763: crash after create table with primary keydefined
On Tue, 2007-11-20 at 11:46 +, Heikki Linnakangas wrote: In logs after this: Nov 20 11:02:04 www postgres[32757]: [4-1] wolniNOTICE: CREATE TABLE / PRIMARY KEY will create implicit index phpbb_acl_roles_data_pkey for table Nov 20 11:02:04 www postgres[32731]: [2-1] LOG: server process (PID 32757) was terminated by signal 11 To me it does look like it was the CREATE TABLE that crashed. The NOTICE shows that it's process ID 32757 that's running the CREATE TABLE, and the LOG line later on says that that's the process that crashed. Yep, agreed. :-( -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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] BUG #3763: crash after create table with primary keydefined
[EMAIL PROTECTED] wrote: Dnia Wt Listopada 20 2007, 12:46, Heikki Linnakangas napisał(a): Simon Riggs wrote: On Tue, 2007-11-20 at 10:12 +, migo wrote: = CREATE TABLE phpbb_acl_roles_data ( role_id INT4 DEFAULT '0' NOT NULL CHECK (role_id = 0), auth_option_id INT4 DEFAULT '0' NOT NULL CHECK (auth_option_id = 0), auth_setting INT2 DEFAULT '0' NOT NULL, PRIMARY KEY (role_id, auth_option_id) ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. wolni= In logs after this: Nov 20 11:02:04 www postgres[32757]: [4-1] wolniNOTICE: CREATE TABLE / PRIMARY KEY will create implicit index phpbb_acl_roles_data_pkey for table Nov 20 11:02:04 www postgres[32757]: [4-2] phpbb_acl_roles_data Nov 20 11:02:04 www postgres[32758]: [2-1] [unknown]LOG: connection received: host=[local] Nov 20 11:02:04 www postgres[32758]: [3-1] wolniLOG: connection authorized: user=wolni database=wolni Nov 20 11:02:04 www postgres[32731]: [2-1] LOG: server process (PID 32757) was terminated by signal 11 Nov 20 11:02:04 www postgres[32731]: [3-1] LOG: terminating any other active server processes Nov 20 11:02:04 www postgres[32758]: [4-1] wolniWARNING: terminating connection because of crash of another server process Looks to me that the process that crashed was not the process that issued the CREATE TABLE command. So it could be something else entirely. Strange thinks it that this happen only when i log into psql via wolni user. When i do this as postgres admin all works fint too. User 'wolni' has granted all privilages to database 'wolni'. To me it does look like it was the CREATE TABLE that crashed. The NOTICE shows that it's process ID 32757 that's running the CREATE TABLE, and the LOG line later on says that that's the process that crashed. Is this behaviour repeatable? I couldn't reproduce this on my laptop with 8.2.4. Migo, is it possible for you to get a core dump and a back trace out of it? Is there anything unusual about the system / compiler / compiler options used? Its gentoo distribution. # gcc -v gcc version 3.4.6 (Gentoo Hardened 3.4.6-r2 p1.5, ssp-3.4.6-1.0, pie-8.7.10) with opts: CFLAGS=-march=prescott -O2 -pipe -fforce-addr -fomit-frame-pointer CXXFLAGS=${CFLAGS} MAKEOPTS=-j2 Ok, you've used -fomit-frame-pointer, so we can't get meaningful debug information out of it. :-( Can you please recompile without -fomit-frame-pointer, and reconfigure postgres with --enable-debug and --enable-cassert? You don't want the --enable-cassert in production, since it slows things down, but --enable-debug shouldn't hurt. On another my system (with gentoo too, and postgresql 8.2.4 all queries works). Is it on similar hardware? Same configure and compiler options? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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 #3763: crash after create table with primary keydefined
[EMAIL PROTECTED] wrote: Dnia Wt Listopada 20 2007, 14:20, Simon Riggs napisał(a): On Tue, 2007-11-20 at 11:46 +, Heikki Linnakangas wrote: In logs after this: Nov 20 11:02:04 www postgres[32757]: [4-1] wolniNOTICE: CREATE TABLE / PRIMARY KEY will create implicit index phpbb_acl_roles_data_pkey for table Nov 20 11:02:04 www postgres[32731]: [2-1] LOG: server process (PID 32757) was terminated by signal 11 To me it does look like it was the CREATE TABLE that crashed. The NOTICE shows that it's process ID 32757 that's running the CREATE TABLE, and the LOG line later on says that that's the process that crashed. Yep, agreed. :-( OK so what we can do now? Do you have core file? It is usually stored in data postgreSQL directory. Can you provide stack trace to determine which function fails? It is very important. what's happen when you create primary key by alter table command? what's happen when you create unique index on (role_id, auth_option_id)? Is it fresh database? Can you reproduce it on the same machine with the same postgreSQL installation on new created database cluster? Zdenek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3763: crash after create table with primary keydefined
Dnia Wt Listopada 20 2007, 14:20, Simon Riggs napisał(a): On Tue, 2007-11-20 at 11:46 +, Heikki Linnakangas wrote: In logs after this: Nov 20 11:02:04 www postgres[32757]: [4-1] wolniNOTICE: CREATE TABLE / PRIMARY KEY will create implicit index phpbb_acl_roles_data_pkey for table Nov 20 11:02:04 www postgres[32731]: [2-1] LOG: server process (PID 32757) was terminated by signal 11 To me it does look like it was the CREATE TABLE that crashed. The NOTICE shows that it's process ID 32757 that's running the CREATE TABLE, and the LOG line later on says that that's the process that crashed. Yep, agreed. :-( OK so what we can do now? Regards, -- Tomasz ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3763: crash after create table with primary keydefined
Dnia Wt Listopada 20 2007, 15:02, Zdenek Kotala napisał(a): [EMAIL PROTECTED] wrote: Dnia Wt Listopada 20 2007, 14:20, Simon Riggs napisał(a): On Tue, 2007-11-20 at 11:46 +, Heikki Linnakangas wrote: In logs after this: Nov 20 11:02:04 www postgres[32757]: [4-1] wolniNOTICE: CREATE TABLE / PRIMARY KEY will create implicit index phpbb_acl_roles_data_pkey for table Nov 20 11:02:04 www postgres[32731]: [2-1] LOG: server process (PID 32757) was terminated by signal 11 To me it does look like it was the CREATE TABLE that crashed. The NOTICE shows that it's process ID 32757 that's running the CREATE TABLE, and the LOG line later on says that that's the process that crashed. Yep, agreed. :-( OK so what we can do now? Do you have core file? It is usually stored in data postgreSQL directory. Can you provide stack trace to determine which function fails? It is very important. I cant get core file now. wolni= CREATE TABLE phpbb_acl_roles_data ( role_id INT4 DEFAULT '0' NOT NULL CHECK (role_id = 0), auth_option_id INT4 DEFAULT '0' NOT NULL CHECK (auth_option_id = 0), auth_setting INT2 DEFAULT '0' NOT NULL); CREATE TABLE ... what's happen when you create primary key by alter table command? wolni= ALTER TABLE phpbb_acl_roles_data add PRIMARY KEY (role_id, auth_option_id); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index phpbb_acl_roles_data_pkey for table phpbb_acl_roles_data WARNING: you don't own a lock of type RowExclusiveLock server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. but when what's happen when you create unique index on (role_id, auth_option_id)? wolni= CREATE UNIQUE INDEX indeks ON phpbb_acl_roles_data (role_id, auth_option_id); CREATE INDEX Is it fresh database? Can you reproduce it on the same machine with the same postgreSQL installation on new created database cluster? Yes, its fresh database (without tables). Regards -- Tomasz ---(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] Start up error
Hussain Jawad-FXRM43 [EMAIL PROTECTED] writes: 2)There are several postgresql process running after a failed restart.Below are the proccesess ps -ef|grep post root 730 30758 0 12:22 pts/200:00:00 grep post postgres 2700 15927 73 12:00 ?00:16:15 postgres: cscti csctools [local] DELETE postgres 6706 15927 72 12:00 ?00:15:42 postgres: cscti csctools [local] DELETE postgres 14351 15927 69 12:02 ?00:13:38 postgres: cscti csctools [local] DELETE postgres 15927 1 0 11:58 ?00:00:05 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 15936 15927 0 11:58 ?00:00:00 postgres: logger process postgres 15967 15927 0 11:58 ?00:00:00 postgres: writer process postgres 15968 15927 0 11:58 ?00:00:00 postgres: stats buffer process postgres 15969 15968 0 11:58 ?00:00:00 postgres: stats collector process postgres 16118 15927 73 11:58 ?00:17:23 postgres: cscti csctools [local] DELETE postgres 23600 15927 65 12:08 ?00:09:25 postgres: cscti csctools [local] DELETE That hardly looks like a failed restart. I can't help wondering if you were looking at the wrong log file. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3762: Inherited serials change on dump/restore
Cstdenis [EMAIL PROTECTED] writes: When I do a dump and restore of my database my serials change. This appears to be the same bug reported and fixed about two weeks ago. http://archives.postgresql.org/pgsql-committers/2007-10/msg00447.php http://archives.postgresql.org/pgsql-committers/2007-10/msg00472.php 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. No, 8.2.5 is just as broken. 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] BUG #3752: query yields could not find block containing chunk, then server crashes
Just forwarding this info along as Zdenek requested... Turns out this problem is not a bug in pg8.3, it was a problem with our custom data type. I have since dropped the custom data type and am now using standard pg float4 arrays. Did the dump and restore, and our app works just fine, no crash when the query is run. BTW- PG8.3 seriously rocks! We've got some large tables that had very poor performance in PG8.1... things are really snappy now, HOT usage really helps our app (as shown by the handy pg_stat_all_tables). Mike Zdenek Kotala wrote: Mike Charnoky wrote: It seems this problem has to do with a custom data type we are using. I am working on eliminating this custom data type, as it is becoming too much of a pain to support (it is basically float4[]). If the problem persists after the data type conversion, I will follow up. Otherwise, I think this was an error in our custom type code (maybe corruption during dump/reload) Thanks for update. Would the stack trace still be useful? Where would I find the dump file? I didn't see anything... If you are sure, that it is in your data type implementation then probably not. You can find core file usually in postgres data directory if you have core file generation enabled by ulimit command. You can get stack trace by gdb. Zdenek Mike Zdenek Kotala wrote: Michael Charnoky wrote: snip 2007-11-15 15:38:03.880 PST: ERROR: could not find block containing chunk 0x902fb98 This message appears in AllocSetFree or AllocSetRealloc function in aset.c source. In both function it means that defined context does not contain memory block. By my opinion there should be two more probable scenarios: 1) memory block does not exist - for AllocSetFree it means e.g. double free or for AllocSetRealloc it means that somebody want to realloc memory which was already freed. 2) memory is still allocated but in different context. However, palloc and pfree should control it. By my opinion it is double free problem, but without stack trace or reproduction scenario it is difficult to find it. Zdenek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #3764: Update count returns zero for a view with 'on update' rules when criteria contains updatable field
The following bug has been logged online: Bug reference: 3764 Logged by: Eugene M. Hutorny Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: Windows 2000 Description:Update count returns zero for a view with 'on update' rules when criteria contains updatable field Details: I noticed strange behaviour of update statements issued to a view with an 'on update' rule when the where criterion contains the field being updated. Please read this example: --- create table a ( aid integer not null, val varchar(32) null, constraint apk primary key (aid) ); create table b ( bid integer not null, mem varchar(32) null, constraint bpk primary key (bid) ); create view ab(id,val,mem) as select a.aid, a.val, b.mem from a inner join b on a.aid = b.bid; create rule ab_ii as on insert to ab do instead ( insert into a(aid,val) select new.id, new.val; insert into b(bid,mem) select new.id, new.mem; ); create rule ab_iu as on update to ab do instead ( update a set val = new.val where a.aid = new.id; update b set mem = new.mem where b.bid = new.id; ); insert into ab(id,val,mem) values(1,'1','1'); insert into ab(id,val,mem) values(2,'2','2'); -- !! This statement reports one row updated an it is expected result update ab set val = '11' where id = 1; -- !! This statement reports zero rows updated and it is unexpected result, it indeed updates the row update ab set val = '22' where id = 2 and val='2'; select * from ab; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3764: Update count returns zero for a view with 'on update' rules when criteria contains updatable field
Eugene M. Hutorny [EMAIL PROTECTED] writes: I noticed strange behaviour of update statements issued to a view with an 'on update' rule when the where criterion contains the field being updated. This isn't a bug --- once the first update is performed, the visible view contents change and so the second update finds no matching row. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #3765: strange results for bit string hex notation cast to bit
The following bug has been logged online: Bug reference: 3765 Logged by: Cade Cairns Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.10 Operating system: Mac OS X 10.5.1 Description:strange results for bit string hex notation cast to bit Details: When casting a bit string constant using hexadecimal notation to a longer bit string, the result is padded with 0's on the right. This will result in inconsistent/useless results: test=# select x'ff'::integer; int4 -- 255 (1 row) test=# select x'ff'::bit(16)::integer; int4 --- 65280 (1 row) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3765: strange results for bit string hex notation cast to bit
Cade Cairns wrote: When casting a bit string constant using hexadecimal notation to a longer bit string, the result is padded with 0's on the right. This will result in inconsistent/useless results: test=# select x'ff'::integer; int4 -- 255 (1 row) test=# select x'ff'::bit(16)::integer; int4 --- 65280 (1 row) It works like it should according to the manual: http://www.postgresql.org/docs/8.1/interactive/datatype-bit.html Note: If one explicitly casts a bit-string value to bit(n), it will be truncated or zero-padded on the right to be exactly n bits, without raising an error. Similarly, if one explicitly casts a bit-string value to bit varying(n), it will be truncated on the right if it is more than n bits. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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] BUG #3765: strange results for bit string hex notation cast to bit
Can anyone explain why this is the case? On 20-Nov-07, at 2:42 PM, Heikki Linnakangas wrote: Cade Cairns wrote: When casting a bit string constant using hexadecimal notation to a longer bit string, the result is padded with 0's on the right. This will result in inconsistent/useless results: test=# select x'ff'::integer; int4 -- 255 (1 row) test=# select x'ff'::bit(16)::integer; int4 --- 65280 (1 row) It works like it should according to the manual: http://www.postgresql.org/docs/8.1/interactive/datatype-bit.html Note: If one explicitly casts a bit-string value to bit(n), it will be truncated or zero-padded on the right to be exactly n bits, without raising an error. Similarly, if one explicitly casts a bit- string value to bit varying(n), it will be truncated on the right if it is more than n bits. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3765: strange results for bit string hex notation cast to bit
On Tue, Nov 20, 2007 at 3:47 PM, in message [EMAIL PROTECTED], Cade Cairns [EMAIL PROTECTED] wrote: On 20-Nov-07, at 2:42 PM, Heikki Linnakangas wrote: http://www.postgresql.org/docs/8.1/interactive/datatype-bit.html Note: If one explicitly casts a bit-string value to bit(n), it will be truncated or zero-padded on the right to be exactly n bits, without raising an error. Similarly, if one explicitly casts a bit- string value to bit varying(n), it will be truncated on the right if it is more than n bits. Can anyone explain why this is the case? That's the correct result, as I read the CAST specification in the ANSI standard; although I think this should generate a warning. d) If SD is fixed-length bit string or variable-length bit string, then let LSV be the value of BIT_LENGTH(SV) and let B be the BIT_LENGTH of the character with the smallest BIT_ LENGTH in the form-of-use of TD. Let PAD be the value of the remainder of the division LSV/B. Let NC be a character whose bits all have the value 0. If PAD is not 0, then append (B - PAD) 0-valued bits to the least significant end of SV; a completion condition is raised: warning-implicit zero-bit padding. Let SVC be the possibly padded value of SV expressed as a character string without regard to valid character encodings and let LTDS be a character string of LTD characters of value NC characters in the form-of-use of TD. TV is the result of SUBSTRING (SVC | LTDS FROM 1 FOR LTD) Case: i) If the length of TV is less than the length of SVC, then a completion condition is raised: warning-string data, right truncation. ii) If the length of TV is greater than the length of SVC, then a completion condition is raised: warning-implicit zero-bit padding. -Kevin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #3766: tsearch2 index creation error
The following bug has been logged online: Bug reference: 3766 Logged by: Thomas Haegi Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3b3 Operating system: Windows 2003 Description:tsearch2 index creation error Details: when following the documentation (http://www.postgresql.org/docs/8.3/static/textsearch-tables.html), the creation of an gin tsearch index fails: CREATE INDEX posts_fts_idx ON forum.posts USING gin(to_tsvector('english', p_msg_clean)); ERROR: translation from wchar_t to server encoding failed: No error ** Error ** ERROR: translation from wchar_t to server encoding failed: No error SQL state: 22021 field p_msg_clean is TEXT (unlimited), db encoding is UTF8. - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #3767: tsearch2 index creation fatal crash
The following bug has been logged online: Bug reference: 3767 Logged by: Thomas Haegi Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3b3 Operating system: Windows 2003 Description:tsearch2 index creation fatal crash Details: the previously reported problem gets worse if you execute the query 2-3 times... postgres.exe terminates: Faulting application postgres.exe, version 8.3.0.7319, faulting module postgres.exe, version 8.3.0.7319, fault address 0x001ced2f. from the pgsql logs: 2007-11-21 03:24:40 CET LOG: server process (PID 2376) exited with exit code 128 2007-11-21 03:24:40 CET LOG: terminating any other active server processes 2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of another server process 2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of another server process 2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of another server process 2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of another server process 2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of another server process 2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of another server process 2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-11-21 03:24:40 CET LOG: all server processes terminated; reinitializing 2007-11-21 03:24:41 CET FATAL: pre-existing shared memory block is still in use 2007-11-21 03:24:41 CET HINT: Check if there are any old server processes still running, and terminate them. ---(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 #3767: tsearch2 index creation fatal crash
the reported problem below can be reproduced by using this simple query straight from the documentation: SELECT to_tsvector('a fat cat sat on a mat and ate a fat rat'); -- postgres.exe dies instantly, with the logs being the same as in the bugreport. interestingly using ::tsvector (which according to the documentation is equivalent) works just fine: SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector; gives the correct result. default configuration for text search is set to: default_text_search_config = 'pg_catalog.german' - thomas Original Message Subject: [BUGS] BUG #3767: tsearch2 index creation fatal crash From: Thomas Haegi [EMAIL PROTECTED] To: pgsql-bugs@postgresql.org Date: 21.11.2007 03:25 The following bug has been logged online: Bug reference: 3767 Logged by: Thomas Haegi Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3b3 Operating system: Windows 2003 Description:tsearch2 index creation fatal crash Details: the previously reported problem gets worse if you execute the query 2-3 times... postgres.exe terminates: Faulting application postgres.exe, version 8.3.0.7319, faulting module postgres.exe, version 8.3.0.7319, fault address 0x001ced2f. from the pgsql logs: 2007-11-21 03:24:40 CET LOG: server process (PID 2376) exited with exit code 128 2007-11-21 03:24:40 CET LOG: terminating any other active server processes 2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of another server process 2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of another server process 2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of another server process 2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of another server process 2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of another server process 2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of another server process 2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-11-21 03:24:40 CET LOG: all server processes terminated; reinitializing 2007-11-21 03:24:41 CET FATAL: pre-existing shared memory block is still in use 2007-11-21 03:24:41 CET HINT: Check if there are any old server processes still running, and terminate them. ---(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 ---(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 #3767: tsearch2 index creation fatal crash
Thomas H. [EMAIL PROTECTED] writes: the reported problem below can be reproduced by using this simple query straight from the documentation: SELECT to_tsvector('a fat cat sat on a mat and ate a fat rat'); Works for me: u=# set default_text_search_config = 'pg_catalog.german'; SET u=# SELECT to_tsvector('a fat cat sat on a mat and ate a fat rat'); to_tsvector --- 'a':1,6,10 'on':5 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4 (1 row) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3767: tsearch2 index creation fatal crash
the reported problem below can be reproduced by using this simple query straight from the documentation: SELECT to_tsvector('a fat cat sat on a mat and ate a fat rat'); Works for me: u=# set default_text_search_config = 'pg_catalog.german'; SET u=# SELECT to_tsvector('a fat cat sat on a mat and ate a fat rat'); to_tsvector --- 'a':1,6,10 'on':5 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4 (1 row) even when implicitly setting default_text_search_config before the query as you did, it fails (but gives a slightly different error message): ERROR: translation from wchar_t to server encoding failed: No such file or directory maybe win32 / file paths related? there are more problems with tsvectors. this also fails: SELECT ' just a test: 123 '::tsvector; ERROR: syntax error in tsvector: just a test: 123 ** Error ** ERROR: syntax error in tsvector: just a test: 123 SQL state: 42601 without : it works: SELECT ' just a test 123 '::tsvector; regards, thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3766: tsearch2 index creation error
Thomas Haegi [EMAIL PROTECTED] writes: Operating system: Windows 2003 CREATE INDEX posts_fts_idx ON forum.posts USING gin(to_tsvector('english', p_msg_clean)); ERROR: translation from wchar_t to server encoding failed: No error Hmm. That error message is close to some code that is specific to the Windows-and-UTF8 case, which might explain why I don't see it. Can any Windows hackers check into whether the WIN32 coding in wchar2char() and char2wchar() in ts_locale.c is sane? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3767: tsearch2 index creation fatal crash
there are more problems with tsvectors. this also fails: SELECT ' just a test: 123 '::tsvector; ERROR: syntax error in tsvector: just a test: 123 That's not a bug; your input isn't valid tsvector syntax. ok. after re-reading page http://www.postgresql.org/docs/8.3/static/textsearch-intro.html#TEXTSEARCH-SEARCHES i saw my mistake. i misinterpreted the examples to show the possibility to convert *any* text by using casting to tsvector as an alternative to using to_tsvector :) to new tsearch-users, it might not be obvious clear that you can't just cast any text but should use to_tsvector. the example string 'a fat cat sat on a mat and ate a fat rat' looks like an normal random text string, especially when a tsvector in psql looks like 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4 and doesn't contain stopwords like a and and, which are included in the casted string... maybe an additional example that shows the usage of to_tsvector for any input string would help... thanks, thomas ---(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] BUG #3767: tsearch2 index creation fatal crash
Thomas H. [EMAIL PROTECTED] writes: i saw my mistake. i misinterpreted the examples to show the possibility to convert *any* text by using casting to tsvector as an alternative to using to_tsvector :) Yeah, the examples in section 12.1.2 are not actually good practice: both sides of the match should usually get normalized before comparison, and these examples don't do that. I'm not sure that putting in to_tsvector calls there would be an improvement, though, since at that point we haven't introduced to_tsvector. Thoughts anyone? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3767: tsearch2 index creation fatal crash
Tom Lane wrote: Thomas H. [EMAIL PROTECTED] writes: i saw my mistake. i misinterpreted the examples to show the possibility to convert *any* text by using casting to tsvector as an alternative to using to_tsvector :) Yeah, the examples in section 12.1.2 are not actually good practice: both sides of the match should usually get normalized before comparison, and these examples don't do that. I'm not sure that putting in to_tsvector calls there would be an improvement, though, since at that point we haven't introduced to_tsvector. Thoughts anyone? Yep, I saw that chicken and egg problem in the docs when I was reviewing it long ago. I never came up with a solution either. We would have to introduce configurations a lot earlier, but how would you explain them when you don't know what a tsvector is. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3767: tsearch2 index creation fatal crash
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I'm not sure that putting in to_tsvector calls there would be an improvement, though, since at that point we haven't introduced to_tsvector. Thoughts anyone? Yep, I saw that chicken and egg problem in the docs when I was reviewing it long ago. I never came up with a solution either. OTOH, I see that the very next sentence mentions to_tsquery without defining it. So maybe it wouldn't be materially less readable if we just changed the examples to use to_tsquery and to_tsvector instead of literal-casting. Either way, there is something going on that doesn't meet the eye, and we might as well write an example that is actually OK practice rather than bad practice. 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] BUG #3767: tsearch2 index creation fatal crash
I'm not sure that putting in to_tsvector calls there would be an improvement, though, since at that point we haven't introduced to_tsvector. Uh ... wait a moment ... stop the presses. Thomas is looking at beta1 documentation. CVS HEAD handles this a bit better I think: http://developer.postgresql.org/pgdocs/postgres/textsearch-intro.html#TEXTSEARCH-MATCHING So the first thing is to go bug the www team about updating the online 8.3beta docs, which I shall do forthwith. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend