FATAL 1: btree

Started by Chau, Artemisover 25 years ago2 messagesbugs
Jump to latest
#1Chau, Artemis
artemis.chau@intel.com
Show quoted text

I am using postgressql 6.5.2. (Please don't tell me to upgrade to 7.0
because we can't yet).
I wrote a function, the error I got is
FATAL 1: btree: cannot split if start (2) >= maxoff (1).

It took me a while to isolate a section of the code. I suspected the line
was too lone, so I changed it from
new.problem := new.problem || ''\n'' || ''Asset Owner
updated: '' || orig_rec.asset_owner || '' -> '' || new.asset_owner;

TO

new.problem := new.problem || ''\n'' || ''Owner updated: ''
|| orig_rec.asset_owner || '' -> '' || new.asset_owner;

After that I was able to submit it through psql.

I continued to work on the second version of the function, got the same
error again. I decided to reverse to the first version, but when I ran
the sql file through psql, the same error happens again.

Here is the code:
**************************************************************************
****************
drop function col_upd_asset() \g
create function col_upd_asset() returns opaque as '
DECLARE
orig_rec record;

BEGIN
SELECT INTO orig_rec *
FROM asset
WHERE case_num = new.case_num;
IF FOUND THEN
IF new.asset_owner <> orig_rec.asset_owner THEN
IF orig_rec.asset_owner ISNULL THEN
new.problem := new.problem || ''\n'' || ''Owner updated:
-> '' || new.asset_owner;
END IF;
IF new.asset_owner ISNULL THEN
new.problem := new.problem || ''\n'' || ''Owner updated: ''
|| orig_rec.asset_owner || '' -> '';
END IF;
IF new.asset_owner IS NOT NULL AND orig_rec.asset_owner IS NOT
NULL THEN
new.problem := new.problem || ''\n'' || ''Owner updated: ''
|| orig_rec.asset_owner || '' -> '' || new.asset_owner;
END IF;
END IF;

IF new.short_desc <> orig_rec.short_desc THEN
IF orig_rec.short_desc ISNULL THEN
new.problem := new.problem || ''\n'' || ''Hostname updated:
-> '' || new.short_desc;
END IF;
IF new.short_desc ISNULL THEN
new.problem := new.problem || ''\n'' || ''Hostname updated:
'' || orig_rec.short_desc || '' -> '';
END IF;
IF new.short_desc IS NOT NULL AND orig_rec.short_desc IS NOT
NULL THEN
new.problem := new.problem || ''\n'' || ''Hostname updated:
'' || orig_rec.short_desc || '' -> '' || new.short_desc;
END IF;
END IF;

IF new.asset_type <> orig_rec.asset_type THEN
IF orig_rec.asset_type ISNULL THEN
new.problem := new.problem || ''\n'' || ''Asset Type
updated: -> '' || new.asset_type;
END IF;
IF new.asset_type ISNULL THEN
new.problem := new.problem || ''\n'' || ''Asset Type
updated: '' || orig_rec.asset_type || '' -> '';
END IF;
IF new.asset_type IS NOT NULL AND orig_rec.asset_type IS NOT
NULL THEN
new.problem := new.problem || ''\n'' || ''Asset Type
updated: '' || orig_rec.asset_type || '' -> '' || new.asset_type;
END IF;
END IF;

IF new.status <> orig_rec.status THEN
IF orig_rec.status ISNULL THEN
new.problem := new.problem || ''\n'' || ''Status updated:
-> '' || new.status;
END IF;
IF new.status ISNULL THEN
new.problem := new.problem || ''\n'' || ''Status updated:
'' || orig_rec.status || '' -> '';
END IF;
IF new.status IS NOT NULL AND orig_rec.status IS NOT NULL THEN
new.problem := new.problem || ''\n'' || ''Status updated:
'' || orig_rec.status || '' -> '' || new.status;
END IF;
END IF;

IF new.media_type <> orig_rec.media_type THEN
IF orig_rec.media_type ISNULL THEN
new.problem := new.problem || ''\n'' || ''Location updated:
-> '' || new.media_type;
END IF;
IF new.media_type ISNULL THEN
new.problem := new.problem || ''\n'' || ''Location updated:
'' || orig_rec.media_type || '' -> '';
END IF;
IF new.media_type IS NOT NULL AND orig_rec.media_type IS NOT
NULL THEN
new.problem := new.problem || ''\n'' || ''Location updated:
'' || orig_rec.media_type || '' -> '' || new.media_type;
END IF;
END IF;

IF new.os_ver <> orig_rec.os_ver THEN
IF orig_rec.os_ver ISNULL THEN
new.problem := new.problem || ''\n'' || ''OS Version
updated: -> '' || new.os_ver;
END IF;
IF new.os_ver ISNULL THEN
new.problem := new.problem || ''\n'' || ''OS Version
updated: '' || orig_rec.os_ver || '' -> '';
END IF;
IF new.os_ver IS NOT NULL AND orig_rec.os_ver IS NOT NULL THEN
new.problem := new.problem || ''\n'' || ''OS Version
updated: '' || orig_rec.os_ver || '' -> '' || new.os_ver;
END IF;
END IF;
IF new.problem <> orig_rec.problem THEN
new.problem := new.problem || ''\n'' || ''
======================================================================'';
END IF;
END IF;
RETURN new;
END;
' language 'plpgsql';
drop trigger upd_asset_chg on asset \g
create trigger upd_asset_chg before update on asset for each row
execute procedure col_upd_asset();
**************************************************************************
****************

Here is the error log:
**************************************************************************
****************
FindExec: found "/usr/bin/postgres" using argv[0]
/usr/bin/postmaster: BackendStartup: pid 2212 user root db asset_track
socket 5
FindExec: found "/usr/bin/postgres" using argv[0]
started: host=localhost user=root database=asset_track
InitPostgres
StartTransactionCommand
ProcessUtility
CommitTransactionCommand
StartTransactionCommand
ProcessUtility
FATAL 1: btree: cannot split if start (2) >= maxoff (1)
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: CleanupProc: pid 2212 exited with status 0
/usr/bin/postmaster: BackendStartup: pid 2214 user root db problem_track
socket 5
FindExec: found "/usr/bin/postgres" using argv[0]
started: host=localhost user=root database=problem_track
InitPostgres
StartTransactionCommand
ProcessQuery
CommitTransactionCommand
/usr/bin/postmaster: BackendStartup: pid 2215 user root db problem_track
socket 5
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: CleanupProc: pid 2214 exited with status 0
FindExec: found "/usr/bin/postgres" using argv[0]
started: host=localhost user=root database=problem_track
InitPostgres
StartTransactionCommand
ProcessQuery
CommitTransactionCommand
/usr/bin/postmaster: BackendStartup: pid 2216 user root db problem_track
socket 5
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: CleanupProc: pid 2215 exited with status 0
FindExec: found "/usr/bin/postgres" using argv[0]
started: host=localhost user=root database=problem_track
InitPostgres
StartTransactionCommand
ProcessQuery
CommitTransactionCommand
/usr/bin/postmaster: BackendStartup: pid 2217 user root db problem_track
socket 5
FindExec: found "/usr/bin/postgres" using argv[0]
started: host=localhost user=root database=problem_track
InitPostgres
StartTransactionCommand
ProcessQuery
CommitTransactionCommand
/usr/bin/postmaster: BackendStartup: pid 2218 user root db problem_track
socket 5
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: CleanupProc: pid 2217 exited with status 0
/usr/bin/postmaster: CleanupProc: pid 2216 exited with status 0
FindExec: found "/usr/bin/postgres" using argv[0]
started: host=localhost user=root database=problem_track
InitPostgres
StartTransactionCommand
ProcessQuery
CommitTransactionCommand
/usr/bin/postmaster: BackendStartup: pid 2219 user root db problem_track
socket 5
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: CleanupProc: pid 2218 exited with status 0
FindExec: found "/usr/bin/postgres" using argv[0]
started: host=localhost user=root database=problem_track
InitPostgres
StartTransactionCommand
ProcessQuery
CommitTransactionCommand
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: CleanupProc: pid 2219 exited with status 0
**************************************************************************
****************

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Chau, Artemis (#1)
Re: FATAL 1: btree

"Chau, Artemis" <artemis.chau@intel.com> writes:

I wrote a function, the error I got is
FATAL 1: btree: cannot split if start (2) >= maxoff (1).

You are running into limits on the size of a function body. You can't
safely make the body longer than about 2700 bytes in 6.5.*. The limit
is about 8K in 7.0, and 7.1 will have effectively no limit.

I am using postgressql 6.5.2. (Please don't tell me to upgrade to 7.0
because we can't yet).

You really should upgrade ;-). In the meantime, your only alternative
is to write shorter functions. Try splitting the work into multiple
functions...

regards, tom lane