Thread: FATAL 1: btree

FATAL 1: btree

From
"Chau, Artemis"
Date:
> 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
> **************************************************************************
> ****************

Re: FATAL 1: btree

From
Tom Lane
Date:
"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