pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Long <mlong@datalong.com>
Cc: pgsql-admin@postgresql.org
Subject: Altering column type causes unstable server and data loss.
Date: Fri, 08 Oct 2004 01:39:51 -0400
Message-ID: <41662827.8030207@datalong.com> (raw)
In-Reply-To: <9756.1096842275@sss.pgh.pa.us>
References: <415F87D7.5000800@datalong.com>
	<28880.1096780739@sss.pgh.pa.us>
	<415F8EC5.9040504@datalong.com>
	<415F9A9F.6090500@datalong.com>
	<29421.1096785225@sss.pgh.pa.us>
	<415FA1FB.9000306@datalong.com>
	<3071.1096822055@sss.pgh.pa.us>
	<41606106.2080006@datalong.com>
	<9756.1096842275@sss.pgh.pa.us>

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





view thread (3+ messages)  latest in thread

Message-ID: <41662827.8030207@datalong.com>
Permalink:  ../41662827.8030207@datalong.com/
Also on:    postgresql.org/message-id/41662827.8030207@datalong.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: mlong@datalong.com
  Subject: Re: Altering column type causes unstable server and data loss.
  In-Reply-To: <41662827.8030207@datalong.com>
  References: <415F87D7.5000800@datalong.com> <28880.1096780739@sss.pgh.pa.us> <415F8EC5.9040504@datalong.com> <415F9A9F.6090500@datalong.com> <29421.1096785225@sss.pgh.pa.us> <415FA1FB.9000306@datalong.com> <3071.1096822055@sss.pgh.pa.us> <41606106.2080006@datalong.com> <9756.1096842275@sss.pgh.pa.us> <41662827.8030207@datalong.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox