> De : Tom Lane [mailto:[EMAIL PROTECTED]
> Envoyé : jeudi, juillet 27, 2006 16:06
> À : Daniel Caune
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] PostgreSQL server terminated by signal 11
>
> "Daniel Caune" <[EMAIL PROTECTED]> writes:
> > My PostgreSQL server running on a Linux machine is terminated by signal
> > 11 whenever I try to create some indexes on a table, which contains
> > quite a lot of data.
>
> Judging from your examples it's got something to do with the partial
> index WHERE clause. What PG version is this exactly? If you leave out
> different parts of the WHERE, does it still crash? Does the crash
> happen immediately after you give the command, or does it run for
> awhile? It might be worth getting a stack trace from the failure
> (best way is to attach to the running backend with gdb, provoke the
> crash, and do "bt" --- search for "gdb" in the archives if you need
> details).
>
> regards, tom lane
The postgres server version is 8.1.4.
Yes, if leave the WHERE clause a simple index, I don't encounter any problem:
CREATE INDEX IDX_GSLOG_EVENTTIME
ON GSLOG_EVENT (EVENT_DATE_CREATED);
Anyway, I'm not sure, Tom, that is only related to the WHERE clause as crash
occur with composite index too, such as:
CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT
ON GSLOG_EVENT (PLAYER_USERNAME, EVENT_NAME);
The crash may happen a while after sending the command. For example, supposing
I reboot the Linux machine and I immediately run the command (i.e. most of
memory is unused), it takes more than five minutes before crash occurs. At
such time the memory usage is the following (top every second):
Mem: 2075860k total, 1787600k used, 288260k free, 6300k buffers
Swap: 369452k total, 0k used, 369452k free, 1748032k cached
When reconnecting to the new postgres respawn, it takes approximately the same
time for having it crashing, whatever the number of times I proceed like this.
I did some other tests trying to detect any common denominator that may make
the postgres server crashing. Here some results are:
select max(length(game_client_version))
from gslog_event;
=> [CRASH]
select max(length(game_client_version))
from gslog_event
where game_client_version is not null;
=> [OK, max = 28]
select count(*)
from gslog_event
where length(game_client_version) >= 0;
=> [OK, count = 4463726]
select count(*)
from gslog_event
where upper(game_client_version) = 'FARCRYPC1.33';
=> [OK, count = 576318]
select count(*)
from gslog_event
where lower(player_username) = 'lythanhphu';
=> [CRASH]
I was thinking about nullable value, but finally, you know what? I have
strictly no idea! :-)
I'll look at the archive for running postgres with gdb and provide more
accurate information.
Thanks,
--
Daniel
---------------------------(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
