Hi.

When I'm doing a database load of a 5gb database, autovacuum always
segfaults shortly after the load finishes. The load is being done
via slony
during the initial copy set command while building a slave, not through
pg_restore.

LOG: autovacuum process (PID ...) was terminated by signal 11
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server
process
DETAIL: The postmaster has commanded this server process to roll
back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.

This problem sounds similiar to these reports:

http://archives.postgresql.org/pgsql...9/msg00200.php

and

http://archives.postgresql.org/pgsql...1/msg00276.php


here is the stack for the autovacuum process.

Program received signal SIGSEGV, Segmentation fault.
CopySnapshot (snapshot=0x0) at tqual.c:1301
1301 newsnap = (Snapshot) palloc(sizeof(SnapshotData) +
(gdb) bt
#0 CopySnapshot (snapshot=0x0) at tqual.c:1301
#1 0x0812388c in _SPI_execute_plan (plan=0x83cd3e0, Values=0x841adf0,
Nulls=0x841ae00 " ", snapshot=0x0, crosscheck_snapshot=0x0,
read_only=1 '\001', tcount=1) at spi.c:1433
#2 0x0812215a in SPI_execute_plan (plan=0x83cd3e0, Values=0x841adf0,
Nulls=0x841ae00 " ", read_only=1 '\001', tcount=1) at spi.c:336
#3 0x403cc0ef in ?? ()
#4 0x403c90e9 in ?? ()
#5 0x403c8668 in ?? ()
#6 0x403c84e7 in ?? ()
#7 0x403c83f1 in ?? ()
#8 0x403c7651 in ?? ()
#9 0x403c442a in ?? ()
#10 0x081137c8 in ExecMakeFunctionResult (fcache=0x83fcb88,
econtext=0x83fcb18, isNull=0xbfffe94b "?\020???", isDone=0x0)
at execQual.c:1095
#11 0x0811554e in ExecEvalExprSwitchContext (expression=0x83fcb88,
econtext=0x83fcb18, isNull=0xbfffe94b "?\020???", isDone=0x0)
at execQual.c:2864
#12 0x080b20e6 in FormIndexDatum (indexInfo=0x83ad7d0, slot=0x83fea98,
estate=0x83fca90, values=0xbfffea10, isnull=0xbfffe9f0 "??:\b\n")
at index.c:971
#13 0x080dd832 in compute_index_stats (onerel=0x401f6f58,
totalrows=11323,
---Type <return> to continue, or q <return> to quit---
indexdata=0x83ad3d0, nindexes=4, rows=0x83e9940, numrows=3000,
col_context=0x83fab88) at analyze.c:540
#14 0x080dd4b7 in analyze_rel (relid=32465292, vacstmt=0x83a48d8)
at analyze.c:387
#15 0x08109365 in vacuum (vacstmt=0x83a48d8, relids=0x83a5540) at
vacuum.c:476
#16 0x0815b4bd in autovacuum_do_vac_analyze (relids=0x83a5540,
dovacuum=0,
doanalyze=1, freeze=0) at autovacuum.c:907
#17 0x0815b16d in do_autovacuum (dbentry=0x82fb410) at autovacuum.c:681
#18 0x0815acb7 in AutoVacMain (argc=0, argv=0x0) at autovacuum.c:423
#19 0x0815aa01 in autovac_start () at autovacuum.c:170
#20 0x0815fe84 in ServerLoop () at postmaster.c:1269
#21 0x0815f80f in PostmasterMain (argc=2, argv=0x82d6158) at
postmaster.c:943
#22 0x0812a9fb in main (argc=2, argv=0x82d6158) at main.c:256
#23 0x42017589 in __libc_start_main () from /lib/i686/libc.so.6
(gdb)


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

Brian Hirt <bhirt@mobygames.com> writes:
> When I'm doing a database load of a 5gb database, autovacuum always
> segfaults shortly after the load finishes.

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:

> Brian Hirt <bhirt@mobygames.com> writes:
>> When I'm doing a database load of a 5gb database, autovacuum always
>> segfaults shortly after the load finishes.
>
> 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

--------------------------------------------
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@mobygames.com> wrote:
> that's strange, because I'm running 8.1.1.
>

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@mobygames.com> writes:
> Is there more information i can provide to help find the problem?

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:
> On 1/4/06, Brian Hirt <bhirt@mobygames.com> wrote:
> > that's strange, because I'm running 8.1.1.
>
> what Tom is saying is that a patch was applied after 8.1.1 was
> launched...

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@fuhr.org> wrote:
> On Wed, Jan 04, 2006 at 12:20:28PM -0500, Jaime Casanova wrote:
> > On 1/4/06, Brian Hirt <bhirt@mobygames.com> wrote:
> > > that's strange, because I'm running 8.1.1.
> >
> > what Tom is saying is that a patch was applied after 8.1.1 was
> > launched...
>
> 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
>

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
| Modifiers
------------------+------------------------
+----------------------------------------------------------------
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:

> Brian Hirt <bhirt@mobygames.com> writes:
>> Is there more information i can provide to help find the problem?
>
> 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

--------------------------------------------
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@mobygames.com> writes:
> 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.

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:

> Brian Hirt <bhirt@mobygames.com> writes:
>> 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.
>
> 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

--------------------------------------------
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