Brian Hirt <bhirt (AT) mobygames (DOT) com> writes:
This sure looks like the same bug already fixed in 8.1.1:

2005-11-28 08:35 alvherre

* src/backend/postmaster/autovacuum.c: Set a snapshot before
running analyze on a single table, to avoid a crash when analyzing
tables with expressional indexes.

Per report from Frank van Vugt.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

that's strange, because I'm running 8.1.1.

[root@pgdb01 root]# /usr/pg-8.1/bin/postmaster --version
postmaster (PostgreSQL) 8.1.1

Is there more information i can provide to help find the problem?

On Jan 4, 2006, at 10:04 AM, Tom Lane wrote:

--------------------------------------------
MobyGames
http://www.mobygames.com
The world's largest and most comprehensive
gaming database project


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On 1/4/06, Brian Hirt <bhirt (AT) mobygames (DOT) com> wrote:
what Tom is saying is that a patch was applied after 8.1.1 was
launched... it will be fixed in 8.1.2 and if you are installing from
sources you can apply yourself the patch to your tree source recompile
and you will have your problem solved now...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Brian Hirt <bhirt (AT) mobygames (DOT) com> writes:
How about the schema of the table in question? If the backtrace is
to be trusted, it's OID 32465292.

Does it crash if you ANALYZE that table manually?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Wed, Jan 04, 2006 at 12:20:28PM -0500, Jaime Casanova wrote:
Is that what Tom is saying? The commit message he posted had a
date of 2005-11-28; 8.1.1 wasn't tagged until 2005-12-08.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 1/4/06, Michael Fuhr <mike (AT) fuhr (DOT) org> wrote:
sorry... you are right... my memory is not as good as some years ago...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom,

I can analyze that table without problems. I don't know if it's the
same table every time. I'm trying to set up a development
environment where i can test this stuff better without messing up our
production systems. The table does have an expresional index.

basement=# select relname from pg_class where oid = 32465292;
relname
---------------
developer_aka
(1 row)

basement=# ANALYZE verbose developer_aka;
ANALYZE
basement=# \d developer_aka
Table "public.developer_aka"
Column | Type
+----------------------------------------------------------------
developer_id | integer |
developer_aka_id | integer | not null default nextval
(('developer_id_seq'::text)::regclass)
first_name | character varying(255) |
last_name | character varying(255) |
search_name | character varying(255) |
Indexes:
"developer_aka_pkey" PRIMARY KEY, btree (developer_aka_id)
"developer_aka_developer_id_inde" btree (developer_id)
"developer_aka_name_idx" btree (name_idx(first_name, last_name))
"developer_aka_search" btree (search_name)
Foreign-key constraints:
"$1" FOREIGN KEY (developer_id) REFERENCES developer(developer_id)
Triggers:
developer_modified AFTER INSERT OR UPDATE ON developer_aka FOR
EACH ROW EXECUTE PROCEDURE add_developer_mod()

function definition:

CREATE FUNCTION name_idx(character varying, character varying)
RETURNS character varying
AS $_$
DECLARE
n varchar;
BEGIN
select upper($1) || ' ' || upper($2) into n;
return n;
END;
$_$
LANGUAGE plpgsql IMMUTABLE;



--brian

On Jan 4, 2006, at 10:25 AM, Tom Lane wrote:

--------------------------------------------
MobyGames
http://www.mobygames.com
The world's largest and most comprehensive
gaming database project


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Brian Hirt <bhirt (AT) mobygames (DOT) com> writes:
I've managed to reproduce this: the triggering condition is that a
single autovac iteration has to VACUUM one table and then ANALYZE
(no vac) another table that has an expressional index with a plpgsql
function. It looks like we missed a path of control where
ActiveSnapshot has to be re-set-up, but I'm not clear where.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Cool,

I was just writing to let you know I created an easily reproducible
test case too, but I guess you don't need that now.

Let me know if there is anything else I can do to help.

Also, if a patch is produced, I'd love to get a copy of it. We just
upgraded our production servers to 8.1.1 this morning (this issue
never came up during testing) and I'l like to get this in there
because it's likely to happen again.

--brian

On Jan 4, 2006, at 11:31 AM, Tom Lane wrote:

--------------------------------------------
MobyGames
http://www.mobygames.com
The world's largest and most comprehensive
gaming database project


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Brian Hirt <bhirt (AT) mobygames (DOT) com> writes:
Does your test case agree with my description of the problem?
(If you're not sure, crank up log_min_messages and watch the log
to see what autovacuum does before it crashes.)

The problem I'm seeing seems to be that vacuum() exits with a
transaction started but no snapshot set (StartTransactionCommand will
leave ActiveSnapshot set to NULL), if it's in use_own_xacts mode.
autovac thinks it only has to set the snapshot once, but that's really
not the case given this behavior.

I'm unsure whether to fix this by adding a CopySnapshot operation right
in vacuum(), or in autovacuum.c. The former is probably cleaner but it
clutters vacuum.c with something only autovac really needs. Any
opinions?

Definitely a big risk of that :-(. It'll be a one-liner patch in any
case, we just have to decide where ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match