Problem with backend crashing - Mailing list pgsql-general

Hi,

I have 2 unrelated problems:

I'm running PostgreSQL 6.4.2 on FreeBSD 2.2.8-STABLE and I'm having some
problem with my database.
I'm using PostgreSQL for keeping RADIUS logs. At this time I have about
50000 records in the table.
When I try to update some records in the table I get the following
message from psql:

pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally before or
while processing the request.
We have lost the connection to the backend, so further processing is
impossible.  Terminating.

Here's the schema of the table:

Table    = session
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| id                               | int4 not null default nextval (
|     4 |
| seq                              | int4 not null default 1
|     4 |
| last                             | bool not null default 'f'
|     1 |
| nas                              | text not null
|   var |
| sessionid                        | text not null
|   var |
| description                      | text
|   var |
| account                          | text not null
|   var |
| ticket                           | text not null
|   var |
| rate                             | float8
|     8 |
| value                            | money
|     4 |
| start                            | datetime not null default dateti
|     8 |
| stop                             | datetime
|     8 |
| reserved                         | datetime
|     8 |
| approved                         | datetime
|     8 |
| cancelled                        | datetime
|     8 |
| clientip                         | text
|   var |
+----------------------------------+----------------------------------+-------+
Indices:  i_snsa
          session_account
          session_pkey
          session_ticket

Table    = i_snsa
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| seq                              | int4
|     4 |
| nas                              | text
|   var |
| sessionid                        | text
|   var |
| account                          | text
|   var |
+----------------------------------+----------------------------------+-------+

Table    = session_account
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| account                          | text
|   var |
+----------------------------------+----------------------------------+-------+

Table    = session_pkey
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| id                               | int4
|     4 |
| seq                              | int4
|     4 |
+----------------------------------+----------------------------------+-------+

Table    = session_ticket
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| ticket                           | text
|   var |
+----------------------------------+----------------------------------+-------+

Session.ID defaults to nextval('ssessionid') and here it is:

Table    = ssessionid
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| sequence_name                    | name
|    32 |
| last_value                       | int4
|     4 |
| increment_by                     | int4
|     4 |
| max_value                        | int4
|     4 |
| min_value                        | int4
|     4 |
| cache_value                      | int4
|     4 |
| is_cycled                        | char
|     1 |
| is_called                        | char
|     1 |
+----------------------------------+----------------------------------+-------+

Session.Start defaults to datetime(now());

The problem only occurs on some records. In this particular instance,
Value, Reserved, Approved,  and Cancelled attributes are NULL;

I tried to SELECT the affected records to another table and then deleted
the records and it failed again when I tried to insert the corrected
records back.

/var/log/messages indicated that postgres process died with signal 11
(SIGSEGV).

Any pointers?...


The second problem was on Postgresql 6.3.x on FreeBSD 2.28-STABLE.

I have the following tables:

Table    = rate
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| id                               | int4 not null default nextval (
|     4 |
| description                      | text not null
|   var |
| priority                         | int4 not null default 0
|     4 |
| rate                             | float8 not null
|     8 |
+----------------------------------+----------------------------------+-------+
Index:    rate_pkey

Table    = rate_pkey
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| id                               | int4
|     4 |
+----------------------------------+----------------------------------+-------+

Table    = rateentry
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| id                               | int4 not null
|     4 |
| start                            | datetime not null
|     8 |
| stop                             | datetime not null
|     8 |
+----------------------------------+----------------------------------+-------+
Index:    rateentry_pkey

Table    = rateentry_pkey
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| id                               | int4
|     4 |
| start                            | datetime
|     8 |
| stop                             | datetime
|     8 |
+----------------------------------+----------------------------------+-------+

This query returns:

access=> select r.id,r.priority,r.rate,e.start,e.stop from rate r,
rateentry e where '1999/3/15 22:00' between e.start and e.stop order by
r.priority desc;
id|priority|rate|start                 |stop
--+--------+----+----------------------+----------------------
 2|       1|  30|1999-03-15 22:00:00+07|1999-03-16 07:00:00+07
 2|       1|  30|-infinity             |infinity
 1|       0|  60|1999-03-15 22:00:00+07|1999-03-16 07:00:00+07
 1|       0|  60|-infinity             |infinity
(4 rows)

Another one returns:

access=> select r.id,r.priority,r.rate,e.start,e.stop from rate r,
rateentry e where '1999/3/15 22:00' between e.start and e.stop and
r.id=e.id order by r.priority desc;
id|priority|rate|start    |stop
--+--------+----+---------+--------
 1|       0|  60|-infinity|infinity
(1 row)

The last query should return 2 rows, right?

What's weird is that the last query was working OK until beginning of
March when it began to fail.
Has anyone seen this before and is this problem fixed in 6.4.x?

Thanks,
Anto.