The following bug has been logged online:
Bug reference: 2855
Logged by: Mike
Email address: [EMAIL PROTECTED]
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
