The following bug has been logged online:

Bug reference: 2855
Logged by: Mike
Email address: worky.workerson (AT) gmail (DOT) com
PostgreSQL version: 8.2
Operating system: RHEL AS 4.3 x86_64
Description: SEGV on PL/PGSQL function
Details:

(retyping this by hand ... forgive any mistakes)

I am getting a SEGV every time I attempt to run the following plpgsql
function:

CREATE FUNCTION drop_empty_ip_partitions(VARCHAR)
RETURNS SETOF VARCHAR AS $$
DECLARE
table_basename ALIAS FOR $1;
is_empty INTEGER;
BEGIN
FOR first_octet IN 0..255 LOOP
BEGIN
EXECUTE 'SELECT 1 FROM ' || quote_ident(table_basename || '_ip' ||
first_octet) || ' LIMIT 1' INTO is_empty;
IF is_empty IS NULL THEN
EXECUTE 'DROP TABLE ' || quote_ident(table_basename || '_ip' ||
first_octet);
RETURN NEXT quote_ident(table_basename || '_ip' ||
first_octet);
END IF;
EXCEPTION WHEN undefined_table THEN
RAISE NOTICE 'Table for octet % does not exist', first_octet;
END;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;

Basically, I have tables partitioned by the first octet of an ip, i.e.
table_ip1, table_ip2, table_ip3, etc. The function simply goes through the
all the possible ip partition tables for a given prefix and drops any that
are empty. I have another very similar plpgsql function that simply returns
the number of elements in each partition, and that causes a SEGV as well.

I am on RHEL AS 4.3 x86_64 on an 8-way HP Opteron box using the 8.2 PGDG
RPMS. I recently upgraded from 8.1 and reloaded (pg_dumpall; cat | psql) my
databases successfully. As I periodically do, I dropped all the tables in
one of the databases and was in the process of reloading them when I ran
into this issue. I can successfully run the plpgsql command on another
(similar) database.

I configured the system to drop a core, and get the following backtrace:

pfree ()
AtEOSuXact_SPI ()
DefineSavepoint ()
RollbackAndReleaseCurrentSubTransaction ()
plpgsql_compile () from /usr/lib64/pgsql/plpgsql.so
.....

Let me know what other information I can provide. I'm hoping that its not a
trivial thing that I missed in the release notes ...

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

Mike wrote:

this seems to be yet another report of the bug already fixed here:

http://archives.postgresql.org/pgsql...2/msg00063.php

the fix for this will appear in 8.2.1 or you could try to apply the
patch manually.


Stefan

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