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