[BUGS] BUG #3427: Autovacuum crashed server

[Rainer Bauer]

The following bug has been logged online:

Bug reference:  3427
Logged by:  Rainer Bauer
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system:   WinXP SP2
Description:Autovacuum crashed server
Details: 

Hello,

My server was running now without any problems since Mid-May: "PostgreSQL
8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)"

Today was the first time it crashed (see log file below). Unfortunatelly I
changed log_min_messages to info yesterday, otherwise it would contain more
info.

At the time of the crash there were 4 connections open to the database and
at least one was "probably" doing an update.

I searched the archive and only found these threads, which are not directly
connected to Autovacuum:




One thing to note is that I have enabled "UseServerSidePrepare" for ODBC
connections last week, because Hiroshi Inoue recommended it:

Since the server never crashed before I thought this might have something to
do with the failure.

Rainer

==
These are my autovacuum settings:
autovacuum = on
autovacuum_vacuum_scale_factor = 0.15   
autovacuum_analyze_scale_factor = 0.07

==
2007-07-04 09:21:19 LOG:  database system was shut down at 2007-07-03
23:45:14 Central Europe Standard Time
2007-07-04 09:21:19 LOG:  checkpoint record is at A/31B884C8
2007-07-04 09:21:19 LOG:  redo record is at A/31B884C8; undo record is at
0/0; shutdown TRUE
2007-07-04 09:21:19 LOG:  next transaction ID: 0/27694933; next OID: 127547
2007-07-04 09:21:19 LOG:  next MultiXactId: 1; next MultiXactOffset: 0
2007-07-04 09:21:20 LOG:  database system is ready
2007-07-04 10:53:00 LOG:  autovacuum process (PID 1712) exited with exit
code -1073741819
2007-07-04 10:53:00 LOG:  terminating any other active server processes
2007-07-04 10:53:00 WARNING:  terminating connection because of crash of
another server process
2007-07-04 10:53:00 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-07-04 10:53:00 HINT:  In a moment you should be able to reconnect to
the database and repeat your command.
2007-07-04 10:53:00 WARNING:  terminating connection because of crash of
another server process
2007-07-04 10:53:00 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-07-04 10:53:00 HINT:  In a moment you should be able to reconnect to
the database and repeat your command.
2007-07-04 10:53:00 WARNING:  terminating connection because of crash of
another server process
2007-07-04 10:53:00 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-07-04 10:53:00 HINT:  In a moment you should be able to reconnect to
the database and repeat your command.
2007-07-04 10:53:00 WARNING:  terminating connection because of crash of
another server process
2007-07-04 10:53:00 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-07-04 10:53:00 HINT:  In a moment you should be able to reconnect to
the database and repeat your command.
2007-07-04 10:53:01 LOG:  all server processes terminated; reinitializing
2007-07-04 10:53:01 LOG:  database system was interrupted at 2007-07-04
10:53:00 Central Europe Standard Time
2007-07-04 10:53:01 LOG:  checkpoint record is at A/3C572C78
2007-07-04 10:53:01 LOG:  redo record is at A/3C3AD8F8; undo record is at
0/0; shutdown FALSE
2007-07-04 10:53:01 LOG:  next transaction ID: 0/27849484; next OID: 135739
2007-07-04 10:53:01 LOG:  next MultiXactId: 1; next MultiXactOffset: 0
2007-07-04 10:53:01 LOG:  database system was not properly shut down;
automatic recovery in progress
2007-07-04 10:53:01 LOG:  redo starts at A/3C3AD8F8
2007-07-04 10:53:01 LOG:  record with zero length at A/3D1F1E60
2007-07-04 10:53:01 LOG:  redo done at A/3D1F1E30
2007-07-04 10:53:06 LOG:  database system is ready

---(end of broadcast)---
TIP 6: explain analyze is your friend

[BUGS] BUG #3428: plperl spi_exec_prepared char length always unity

[Matt Taylor]

The following bug has been logged online:

Bug reference:  3428
Logged by:  Matt Taylor
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system:   Gentoo
Description:plperl spi_exec_prepared char length always unity
Details: 

The spi_prepare and spi_exec_prepared doesn't seem to properly handle fixed
character types.  Only the first character of a string is passed to the
database.  

Is this behavior related to the following statement from the manual?

PostgreSQL 8.2.4 Documentation
Chapter 8. Data Types
8.3. Character Types

The type "char" (note the quotes) is different from char(1) in that it only
uses one byte of storage. It is internally used in the system catalogs as a
poor-man's enumeration type.

Matt Taylor


The following code should recreate the problem:

create table bug_demo_table ( xx char(2) );
insert into bug_demo_table ( xx ) values ( 'AB' );

CREATE FUNCTION bug_demo() RETURNS INTEGER AS $$
  
  my $sql = 'insert into bug_demo_table ( xx ) values ( $1 );' ;
  
  my $sth = spi_prepare( $sql, 'char' );
  spi_exec_prepared( $sth, 'CD' ); # broken
  
  my $sth = spi_prepare( $sql, 'text' );
  spi_exec_prepared( $sth, 'EF' ); # works
  
  my $sth = spi_prepare( $sql, 'varchar' );
  spi_exec_prepared( $sth, 'GH' ); # works
  
  return 1;

$$ LANGUAGE 'plperlu';

select bug_demo();
select * from bug_demo_table;

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [BUGS] BUG #3428: plperl spi_exec_prepared char length always unity

[Tom Lane]

"Matt Taylor" <[EMAIL PROTECTED]> writes:
> The spi_prepare and spi_exec_prepared doesn't seem to properly handle fixed
> character types.  Only the first character of a string is passed to the
> database.  

IIRC, "char" means "char(1)" by default, so I'm unconvinced that this
behavior is a bug.

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate