[ADMIN] Altering column type causes unstable server and data loss.
Michael Long
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend