Re: PostgreSQL 8.1.0 catalog corruption - Mailing list pgsql-hackers

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

> Tom Lane wrote:
>> Bob Ippolito <bob@redivi.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'teffect DDLSELECT 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
WHEREA.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;