Re: Backend crash (long)

I've definitely seen errors from including vacuum and/or analyze
statements in functions, I think I've seen crashes too. If you check the
docs I'm pretty sure they mention the specifics of not being able to use
such statements.

Robert Treat

On Wed, 2002-09-18 at 04:09, Michael Paesold wrote:
> Hi all,
>
> I have written a test function, that will create a sequence and a table,
> than insert one million rows into the table, analyze the table and create an
> index on one of the columns.
> (so this will all happen inside on transaction)
>
> After doing that, the backend will crash.
> (but the data will be inserted)
>
> If I comment out the table analyzing and the create index (I have not tested
> which on leads to the crash), everything works fine. I have sent a copy of
> the error log, the psql session, the function and some parts of my
> postgresql.conf file.
>
> My system is RedHat 7.2, Kernel 2.4.9-34, glibc-2.2.4, gcc 2.96, PostgreSQL
> 7.2.2 built from source.
>
> If you want, I could try other combinations of create/insert/analyze etc. to
> test the exact steps needed to crash the backend.
>
> I know what I am doing is not really standard. This was rather a stability
> test of postgres :). What do you think about this all?
>
> Best Regards,
> Michael Paesold
>
>
> --> logfile:
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> 'bench_big_pkey' for table 'bench_big'
> DEBUG: recycled transaction log file 000000000000009F
> [...skipping: recycled transaction log file 00000000000000A0 to
> 00000000000000AE]
> DEBUG: recycled transaction log file 00000000000000B0
> DEBUG: Analyzing bench_big
> DEBUG: server process (pid 13840) was terminated by signal 11
> DEBUG: terminating any other active server processes
> DEBUG: all server processes terminated; reinitializing shared memory and
> semaphores
> DEBUG: database system was interrupted at 2002-09-17 11:45:56 CEST
> DEBUG: checkpoint record is at 0/B41170A4
> DEBUG: redo record is at 0/B400DF34; undo record is at 0/0; shutdown FALSE
> DEBUG: next transaction id: 96959; next oid: 6282462
> DEBUG: database system was not properly shut down; automatic recovery in
> progress
> DEBUG: redo starts at 0/B400DF34
> DEBUG: ReadRecord: record with zero length at 0/B495F754
> DEBUG: redo done at 0/B495F730
> DEBUG: recycled transaction log file 00000000000000B2
> DEBUG: recycled transaction log file 00000000000000B1
> DEBUG: recycled transaction log file 00000000000000B3
> DEBUG: database system is ready
>
> The first time I tried the insert, there was an additional notice from
> another backend, just after the line "DEBUG: terminating any other active
> server processes":
> NOTICE: Message from PostgreSQL backend:
> The Postmaster has informed me that some other backend
> died abnormally and possibly corrupted shared memory.
> I have rolled back the current transaction and am
> going to terminate your database system connection and exit.
> Please reconnect to the database system and repeat your query.
>
> --> in psql:
> billing=# select create_benchmark ();
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> 'bench_big_pkey' for table 'bench_big'
> 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: Failed.
> !# \c
> Password:
> You are now connected to database billing as user billing.
> billing=# select real_time from bench_big where int_id in (1, 1000000);
> real_time
> -------------------------------
> 2002-09-17 11:32:22.63334+02
> 2002-09-17 11:46:16.601282+02
> (2 rows)
>
> --> all rows have definatly been inserted!
>
>
> --> the trigger function:
>
> CREATE OR REPLACE FUNCTION create_benchmark () RETURNS BOOLEAN AS '
> DECLARE
> char100 VARCHAR :=
> \'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZäöüÄÖÜß1234567890!"§$%
> &/()=?+*#<>|-_,;.:^°{}´`[]\';
> r1 INTEGER;
> r2 INTEGER;
> r3 INTEGER;
> BEGIN
> CREATE SEQUENCE bench_seq;
>
> CREATE TABLE bench_big (
> int_id INTEGER NOT NULL default nextval(\'bench_seq\'),
> bigint_id BIGINT NOT NULL,
> sometext1 VARCHAR (50),
> sometext2 VARCHAR (50),
> sometext3 VARCHAR (50),
> trx_time TIME WITHOUT TIME ZONE NOT NULL default CURRENT_TIME,
> trx_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL default
> CURRENT_TIMESTAMP,
> trx_date DATE NOT NULL default CURRENT_DATE,
> real_time TIMESTAMP NOT NULL default timeofday(),
> someboolean1 BOOLEAN NOT NULL,
> someboolean2 BOOLEAN NOT NULL,
> PRIMARY KEY (int_id)
> );
>
> FOR i IN 1..1000000 LOOP
> r1 = CAST( RANDOM() * 49 AS INTEGER );
> r2 = CAST( RANDOM() * 49 AS INTEGER );
> r3 = CAST( RANDOM() * 49 AS INTEGER );
>
> INSERT INTO bench_big
> (bigint_id, sometext1, sometext2, sometext3, someboolean1,
> someboolean2)
> VALUES (
> CAST(RANDOM() * 10000000000 AS BIGINT),
> SUBSTR(char100, 50, 49), -- this should be r1, r1 (but doesn't work!)
> SUBSTR(char100, 50, 49), -- this should be r2, r2 (but doesn't work!)
> SUBSTR(char100, 50, 49), -- this should be r3, r3 (but doesn't work!)
> CASE WHEN r1 > 25 THEN TRUE ELSE FALSE END,
> CASE WHEN r3 > 10 THEN TRUE ELSE FALSE END
> );
> END LOOP;
>
> -- WARNING: un-commenting these lines could crash your postgres
> -- CREATE INDEX bench_bigint_id_idx ON bench_big(bigint_id);
> -- ANALYZE bench_big;
>
> RETURN TRUE;
> END;
> ' LANGUAGE 'plpgsql';
>
>
> --> Perhaps relevant parts of my postgresql.conf file:
>
> # Shared Memory Size
> #
> shared_buffers = 12288 # 2*max_connections, min 16 (one usually 8Kb)
> max_fsm_relations = 100 # min 10, fsm is free space map (number of
> tables)
> max_fsm_pages = 20000 # min 1000, fsm is free space map (one about 8Kb)
> max_locks_per_transaction = 64 # min 10
> wal_buffers = 8 # min 4
>
> # Non-shared Memory Sizes
> #
> sort_mem = 4096 # min 32 (in Kb)
> vacuum_mem = 16384 # min 1024
>
> # Write-ahead log (WAL)
> #
> wal_files = 8 # range 0-64, default 0
> wal_sync_method = fdatasync # the default varies across platforms:
> # # fsync, fdatasync, open_sync, or open_datasync
> #wal_debug = 0 # range 0-16
> #commit_delay = 0 # range 0-100000
> #commit_siblings = 5 # range 1-1000
> #checkpoint_segments = 3 # in logfile segments (16MB each), min 1, default
> 3
> #checkpoint_timeout = 300 # in seconds, range 30-3600
> #fsync = true
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly