Altering column type causes unstable server and data loss. - Mailing list pgsql-admin

Hi,

If this is not the correct place report this type thing please let me
know and I will post it to the correct location. I was able to
successfully alter a column of type date to varchar(20). However now
when I perform selects on the table I will either get the results as
expected, have the psql connection to the server broken, or have the
server itself hang. The column in question is called "startdate". I have
copied the text from the psql sessions in question below along with
comments preceded with <<. I have also included the relevant entries in
the log file.

The other interesting behavior is that prior to altering the table I
could connect to the server from my Win2k box. After this point I get
the message "FATAL: missing or erroneous pg_hba.conf file." This file
not only exists but has not be modified by me during this time frame.
This is a test server so losing some data is acceptable, but it would be
nice to know what happened and how to resolve it.

Sorry for the long posting but I wanted to be as thorough as possible
when documenting this.

Environment:
    Server OS:  SuSE 9.1
    Database:    Postgres: 8.0b3
    Client OS:  Win2k


<< After altering the table and realizing there was a problem. I
described the table and it looks as expected
 proporg=>\d mortgage;
                                          Table "proporg.mortgage"
   Column    |            Type             |
Modifiers
-------------+-----------------------------+----------------------------------------------------------------
 id          | integer                     | not null default
nextval('proporg.mortgage_id_seq'::text)
 player_id   | integer                     |
 parcel_id   | integer                     | not null
 loannbr     | character varying(20)       |
 lender_id   | integer                     |
 startdate   | character varying(20)       |
 payment     | numeric(16,4)               |
 duedate     | smallint                    |
 years       | smallint                    |
 nbrpayments | smallint                    |
 amount      | numeric(16,4)               |
 rate        | numeric(3,2)                |
 terms       | character varying(255)      |
 pmi         | boolean                     | not null
 escrowins   | boolean                     | not null
 escrowtax   | boolean                     | not null
 createdate  | timestamp without time zone | not null
 modifydate  | timestamp without time zone | not null default
('now'::text)::timestamp(6) without time zone
Indexes:
    "mortgage_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$3" FOREIGN KEY (lender_id) REFERENCES player(id) ON UPDATE CASCADE
ON DELETE CASCADE
    "$2" FOREIGN KEY (player_id) REFERENCES player(id) ON UPDATE CASCADE
ON DELETE CASCADE
    "$1" FOREIGN KEY (parcel_id) REFERENCES parcel(id) ON UPDATE CASCADE
ON DELETE CASCADE

<< I attempted to alter the column type back to type "date" with no luck

proporg=> alter table mortgage alter column startdate type date;
ERROR:  column "startdate" cannot be cast to type "date"

<< It is possible to retrieve some information from the table

proporg=> select id from mortgage;
 id
----
  2
  8
(2 rows)

<< When attempting to retrieve all records from the table the connection
is broken
<< It is possible to exit psql with "\q" at this
point

proporg=> select * from mortgage;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> \q

<< After restarting the psql session and attempting to specifically
retrieve data from
<< the id and startdate columns both psql and postmaster hang. The
server becomes
<< very sluggish at this point as if the CPU is racing.  It is necessary
to issue
<< kill -QUIT pid_no to kill the server and then kill pid_no to kill
psql.  The message
<< Terminated does not appear untill after the postmaster is killed.


Welcome to psql 8.0.0beta3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

proporg=> select id, startdate from mortgage;
Terminated

<< Log file Entries - The table was altered and then the store proc was
executed. You will notice I left the to_char
<< function in the stor proc by accident after changing the column type
to varchar from date. May this has something
<< to do with it?

ERROR:  function to_char(character varying, "unknown") does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.
CONTEXT:  SQL query " select m.id ,m.parcel_id ,m.player_id ,m.loannbr
,to_char(m.startdate, 'MM/DD/YYYY') as "startdate" ,m.amount from
mortgage m ORDER BY startdate, loannbr"
        PL/pgSQL function "mortgage_list" line 22 at open
LOG:  server process (PID 4440) 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.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
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.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
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.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
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.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
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.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2004-10-07 23:22:30 EDT
LOG:  checkpoint record is at 0/FA094C
LOG:  redo record is at 0/FA094C; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 1866; next OID: 41804
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  redo starts at 0/FA0988
LOG:  record with zero length at 0/FB2D88
LOG:  redo done at 0/FB2D60
LOG:  database system is ready
LOG:  server process (PID 4557) was terminated by signal 11
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2004-10-07 23:23:57 EDT
LOG:  checkpoint record is at 0/FB2D88
LOG:  redo record is at 0/FB2D88; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 1872; next OID: 41804
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  record with zero length at 0/FB2DC4
LOG:  redo is not required
LOG:  database system is ready
ERROR:  function building_list("unknown") does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.
LOG:  server process (PID 4573) 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.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2004-10-07 23:25:51 EDT
LOG:  checkpoint record is at 0/FB2DC4
LOG:  redo record is at 0/FB2DC4; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 1872; next OID: 41804
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  record with zero length at 0/FB2E00
LOG:  redo is not required
LOG:  database system is ready
LOG:  server process (PID 4578) was terminated by signal 11
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2004-10-07 23:28:06 EDT
LOG:  checkpoint record is at 0/FB2E00
LOG:  redo record is at 0/FB2E00; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 1872; next OID: 41804
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  record with zero length at 0/FB2E3C
LOG:  redo is not required
LOG:  database system is ready
LOG:  server process (PID 4583) 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.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2004-10-07 23:29:08 EDT
LOG:  checkpoint record is at 0/FB2E3C
LOG:  redo record is at 0/FB2E3C; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 1872; next OID: 41804
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  record with zero length at 0/FB2E78
LOG:  redo is not required
LOG:  database system is ready
LOG:  received smart shutdown request
LOG:  shutting down
LOG:  database system is shut down
LOG:  could not bind IPv4 socket: Address already in use
HINT:  Is another postmaster already running on port 5450? If not, wait
a few seconds and retry.
LOG:  database system was shut down at 2004-10-07 23:31:36 EDT
LOG:  checkpoint record is at 0/FB2EB4
LOG:  redo record is at 0/FB2EB4; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 1872; next OID: 41804
LOG:  database system is ready
LOG:  invalid IP mask "trust" in pg_hba.conf file line 71: Name or
service not known
FATAL:  missing or erroneous pg_hba.conf file