Re: PostgreSQL 8.1.0 catalog corruption

On Nov 21, 2005, at 4:33 PM, Alvaro Herrera wrote:

> Tom Lane wrote:
>> Bob Ippolito <bob(at)redivi(dot)com> writes:
>>> On Nov 21, 2005, at 3:56 PM, Tom Lane wrote:
>>>> Well, I count at least a couple hundred deleted versions of that
>>>> table
>>>> row :-(. What the heck were you doing with it?
>>
>>> The ETL process keeps trying until it succeeds or someone stops it,
>>> so I guess that's why there's so much churn in there for that table.
>>> Kept trying to create it, and ran into the issue. I'd estimate
>>> around 1700 to 1800 dead versions of that table, because it ran for
>>> some time before I noticed and stopped it... this is just a test box
>>> after all, I don't have 8.1 in production yet (thankfully!).
>>
>> Um, no, that theory doesn't seem to explain the evidence. A failed
>> insertion would result in a row with an uncommitted XMIN and no XMAX.
>> All of the entries I'm seeing have both XMIN and XMAX set. A good-
>> size
>> fraction have the same XMIN and XMAX (but different CMIN and
>> CMAX), but
>> I see some that have different XMIN and XMAX. It looks to me like
>> the
>> table was definitely created successfully, and it survived across
>> multiple transactions ... but something was doing a lot of DDL
>> changes
>> on it. If we could find out what, maybe we could reproduce the
>> problem.
>
> Maybe the UPDATE pg_class SET relhastriggers='f' that people is so
> fond
> of doing to deactivate triggers? Or something similar?

I don't touch pg_class at all... this is what I'm doing (over and
over again).

-- clone_table is almost always a no-op, but once a day it creates a
new table
SELECT clone_table('ping', 'ping_%s', '')
SELECT drop_ping_constraints('ping_%s')
-- stuff that doesn't effect DDL
SELECT add_ping_constraints('ping_%s')

and the referenced UDFs are as follows:

CREATE OR REPLACE FUNCTION
clone_table(parent text, child text, extra text) RETURNS boolean
AS $$
DECLARE
tmprec record;
user_index record;
parent_constraint record;
user_index_column record;
indexname text;
i integer;
columns text[];
BEGIN
-- are we done?
FOR tmprec IN
SELECT 1 FROM pg_sysviews.pg_user_tables WHERE
table_name=child
LOOP

RETURN FALSE;
END LOOP;

-- inherit the table
EXECUTE 'CREATE TABLE '
|| quote_ident(child)
|| '('
|| extra
|| ') INHERITS ('
|| quote_ident(parent)
|| ')';

FOR parent_constraint IN
SELECT *
FROM pg_sysviews.pg_user_table_constraints A
WHERE A.table_name = parent
LOOP
EXECUTE 'ALTER TABLE '
|| quote_ident(child)
|| ' ADD '
|| parent_constraint.definition;
END LOOP;

i := 0;
FOR user_index IN
SELECT *
FROM pg_sysviews.pg_user_indexes A
WHERE
A.table_name = parent
AND A.index_name != (parent || '_pkey')
LOOP

i := i + 1;
indexname := child;
columns := '{}'::text[];
FOR user_index_column IN
SELECT B.column_name, quote_ident(B.column_name) AS col
FROM pg_sysviews.pg_user_index_columns B
WHERE
B.table_name = user_index.table_name
AND B.index_name = user_index.index_name
ORDER BY B.column_position
LOOP

indexname := indexname || '_' ||
user_index_column.column_name;
columns := array_append(columns, user_index_column.col);
END LOOP;

IF user_index.predicate IS NOT NULL THEN
indexname := indexname || '_p' || i::text;
END IF;

-- this is not complete, but works
-- missing tablespace, index_method, is_clustered,
EXECUTE ('CREATE '
|| (CASE WHEN user_index.is_unique THEN 'UNIQUE ' ELSE
'' END)
|| 'INDEX '
|| quote_ident(indexname)
|| ' ON '
|| quote_ident(child)
|| ' USING '
|| quote_ident(user_index.index_method)
|| ' ('
|| array_to_string(columns, ',')
|| ')'
|| (CASE WHEN user_index.predicate IS NOT NULL
THEN ' WHERE ' || user_index.predicate
ELSE '' END)
);

END LOOP;

RETURN TRUE;

END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION
drop_ping_constraints(ping_table text) RETURNS void
AS $drop_ping_constraints$
DECLARE
next_sql text;
constraint_rec record;
BEGIN

next_sql := $sql$
SELECT
"constraint_name"
FROM pg_sysviews.pg_user_table_constraints
WHERE "constraint_name" IN ($sql$
|| quote_literal(ping_table || '_timestamp_check')
|| ', '
|| quote_literal(ping_table || '_id_check')
|| ')';

-- RAISE NOTICE 'SQL: %', next_sql;
FOR constraint_rec IN EXECUTE next_sql LOOP
next_sql := 'ALTER TABLE '
|| quote_ident(ping_table)
|| ' DROP CONSTRAINT '
|| quote_ident(constraint_rec.constraint_name);
-- RAISE NOTICE 'SQL: %', next_sql;
EXECUTE next_sql;
END LOOP;

RETURN;
END
$drop_ping_constraints$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION
add_ping_constraints(ping_table text) RETURNS void
AS $add_ping_constraints$
DECLARE
next_sql text;
extents_rec RECORD;
BEGIN

next_sql := $sql$
SELECT
MIN("id") AS "min_id",
MAX("id") AS "max_id",
MIN("timestamp") AS "min_timestamp",
MAX("timestamp") AS "max_timestamp"
FROM $sql$
|| quote_ident(ping_table);

-- RAISE NOTICE 'SQL: %', next_sql;
FOR extents_rec IN EXECUTE next_sql LOOP
END LOOP;

next_sql := 'ALTER TABLE '
|| quote_ident(ping_table)
|| ' ADD CHECK("id" BETWEEN '
|| quote_literal(extents_rec.min_id) || '::bigint'
|| ' AND '
|| quote_literal(extents_rec.max_id) || '::bigint'
|| ')';
-- RAISE NOTICE 'SQL: %', next_sql;
EXECUTE next_sql;

next_sql := 'ALTER TABLE '
|| quote_ident(ping_table)
|| ' ADD CHECK("timestamp" BETWEEN '
|| quote_literal(extents_rec.min_timestamp) || '::timestamptz'
|| ' AND '
|| quote_literal(extents_rec.max_timestamp) || '::timestamptz'
|| ')';

-- RAISE NOTICE 'SQL: %', next_sql;
EXECUTE next_sql;

RETURN;
END
$add_ping_constraints$ LANGUAGE plpgsql;