On Nov 21, 2005, at 4:33 PM, Alvaro Herrera wrote:
Tom Lane wrote:
Bob Ippolito <[EMAIL PROTECTED]> 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;
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
