Thread

1. FATAL 1: btree

   Chau, Artemis <artemis.chau@intel.com> — 2000-11-03T15:18:07Z

   > 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
   > **************************************************************************
   > ****************
   
   
   

2. Re: FATAL 1: btree

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-03T16:19:55Z

   "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