Hello folks,
Stumbled across an odd problem while cleaning data out of a database. I am
getting these "invalid tid" errors. I tried the upgrade from 7.3.2 to 7.3.4.
I tried a dumpall/initdb/restore... nadda. Nothing really usefull is coming
from the logs either, even though logging is cranked up. If anyone can
suggest a method to track down the cause of the following dialog with the db,
I would greatly appreciate it. If you need any more info, please just ask.
Thank you in advance.
-Wade
version
---------------------------------------------------------------------
PostgreSQL 7.3.4 on i386-unknown-freebsd4.6, compiled by GCC 2.95.4
(-STABLE cvs from today)
dropsites=# begin;
BEGIN
dropsites=# delete from te_users where id = 954;
WARNING: Error occurred while executing PL/pgSQL function c_delete_categories
WARNING: line 14 at SQL statement
ERROR: heap_mark4update: (am)invalid tid
dropsites=# rollback;
ROLLBACK
Table "public.te_users"
Column | Type |
Modifiers
-------------------+-----------------------------+-----------------------------------------------------
id | integer | not null default
nextval('"te_users_id_seq"'::text)
username | text | not null
password | text |
reseller | integer | not null default 0
directory | text |
contact | integer |
creation_date | timestamp with time zone | default now()
active | boolean | not null default 'f'
domain | integer | not null default 0
has_domain | boolean | not null default 'f'
tutorial_type | integer | default -1
tutorial_step | integer | default -1
license_agreement | boolean | default 'f'
use_header | integer | default 0
promo | boolean | not null default 'f'
last_billed | timestamp without time zone | default now()
Indexes: primary_fk primary key btree (username, "domain"),
te_users_id_key unique btree (id),
te_users_username_lower_idx btree (lower(username))
dropsites=# \d c_categories
Table "public.c_categories"
Column | Type | Modifiers
-------------+---------+--------------------------------------------------------------
id | integer | not null default
nextval('public.c_categories_id_seq'::text)
category | integer | not null default 0
userid | integer | not null
form | integer | not null
name | text |
description | text |
lft | integer |
rgt | integer |
level | integer |
parentid | integer |
Indexes: c_categories_id_key unique btree (id)
Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES te_users(id) ON
UPDATE NO ACTION ON DELETE CASCADE,
$2 FOREIGN KEY (form) REFERENCES c_forms(id) ON
UPDATE NO ACTION ON DELETE CASCADE,
c_categories_fk FOREIGN KEY (parentid) REFERENCES
c_categories(id) ON UPDATE NO ACTION ON DELETE SET DEFAULT,
c_categories_cat_fk FOREIGN KEY (category) REFERENCES
c_categories(id) ON UPDATE NO ACTION ON DELETE NO ACTION
--- Source of c_delete_categories ---
CREATE OR REPLACE FUNCTION c_delete_categories() returns TRIGGER AS '
begin
IF c_category_mutex() THEN
-- delete entry
DELETE FROM c_categories WHERE ID = old.id;
IF (old.rgt - old.lft) > 1 THEN
-- update children
UPDATE c_categories SET ParentID = old.parentid WHERE ParentID =
old.id;
UPDATE c_categories SET lft = lft - 1, rgt = rgt - 1, level = level -
1 WHERE lf
t > old.lft AND lft < old.rgt;
END IF;
-- remove extra space
UPDATE c_categories SET lft = lft - 2 WHERE lft > old.rgt;
UPDATE c_categories SET rgt = rgt - 2 WHERE rgt > old.rgt;
PERFORM c_category_clear_mutex();
return NULL;
else
return old;
END IF;
end;
' language 'plpgsql';
--- source of c_category_mutex ---
CREATE OR REPLACE FUNCTION c_category_mutex() returns BOOL AS '
DECLARE
mutex_count integer;
BEGIN
SELECT INTO mutex_count COUNT(*) FROM pg_class c, pg_attribute a
WHERE a.attrelid = c.oid
AND c.relname = ''___c_category_mutex___''
AND a.attname = ''___c_category_mutex___''
AND pg_catalog.pg_table_is_visible ( c.oid );
IF mutex_count > 0 THEN
RETURN ''f'';
ELSE
CREATE TEMP TABLE ___c_category_mutex___ (___c_category_mutex___ INT2);
RETURN ''t'';
END IF;
END;' LANGUAGE 'plpgsql';
--- source of c_category_clear_mutex ---
CREATE OR REPLACE FUNCTION c_category_clear_mutex() returns BOOL AS '
DECLARE
mutex_count INT4;
BEGIN
SELECT INTO mutex_count COUNT(*) FROM pg_class c, pg_attribute a
WHERE a.attrelid = c.oid
AND c.relname = ''___c_category_mutex___''
AND a.attname = ''___c_category_mutex___''
AND pg_catalog.pg_table_is_visible ( c.oid );
IF mutex_count > 0 THEN
DROP TABLE ___c_category_mutex___;
RETURN ''t'';
ELSE
RETURN ''f'';
END IF;
END;' LANGUAGE 'plpgsql';
--
Wade Klaver
Wavefire Technologies Corporation
GPG Public Key at http://archeron.wavefire.com
/"\ ASCII Ribbon Campaign .
\ / - NO HTML/RTF in e-mail .
X - NO Word docs in e-mail .
/ \ -----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
