pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Altering column type causes unstable server and data loss.
3+ messages / 3 participants
[nested] [flat]

* Altering column type causes unstable server and data loss.
@ 2004-10-08 05:39 Michael Long <mlong@datalong.com>
  2004-10-08 19:00 ` Re: Altering column type causes unstable server and data loss. Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Michael Long @ 2004-10-08 05:39 UTC (permalink / raw)
  To: ; +Cc: 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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Altering column type causes unstable server and data loss.
  2004-10-08 05:39 Altering column type causes unstable server and data loss. Michael Long <mlong@datalong.com>
@ 2004-10-08 19:00 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-10-09 03:03   ` Re: Altering column type causes unstable server and data Micahael Long <mlong@datalong.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2004-10-08 19:00 UTC (permalink / raw)
  To: Michael Long <mlong@datalong.com>; +Cc: pgsql-admin

Michael Long <mlong@datalong.com> writes:
> If this is not the correct place report this type thing please let me 
> know and I will post it to the correct location.

pgsql-bugs is much more appropriate, especially for problem reports
against a beta version.

> 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.

I tried to reproduce this without any success.  Given the inconsistency
of the behavior, I'm wondering about hardware flakiness on your machine.
I could believe that ALTER TABLE has a bug causing it to produce a
corrupt output table, but if the table is corrupt then "select *" should
produce consistently wrong answers or a consistent crash.  In any case,
I made a table matching your table schema, put a few rows in it, did the
ALTER, and was still able to SELECT * from it.  So if there's a bug it
requires additional triggering conditions you haven't mentioned.

> 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.  

So what's in the file now?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Altering column type causes unstable server and data
  2004-10-08 05:39 Altering column type causes unstable server and data loss. Michael Long <mlong@datalong.com>
  2004-10-08 19:00 ` Re: Altering column type causes unstable server and data loss. Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-10-09 03:03   ` Micahael Long <mlong@datalong.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Micahael Long @ 2004-10-09 03:03 UTC (permalink / raw)
  To: ; +Cc: pgsql-admin


>pgsql-bugs is much more appropriate, especially for problem reports
>against a beta version.
>
>  
>
I will keep this in mind in the outside chance that I run into another 
issue :)

>I tried to reproduce this without any success.  Given the inconsistency
>of the behavior, I'm wondering about hardware flakiness on your machine.
>
>  
>
As I get some time I will try to create a scenario to reproduce the 
problem. While thinking about the sequence of events that led to this 
problem I realized that it will take some controlled testing to isolate 
what went wrong. I may be that the alter column type command had nothing 
to do with it.

>>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.  
>>    
>>
>
>So what's in the file now?
>  
>
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

local   all         all                               trust

# IPv4-style local connections:
# host    all         all         127.0.0.1/32          trust
host    all          all         192.168.1.0/24        trust

# IPv6-style local connections:
# host    all         all         ::1/128               trust





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-10-09 03:03 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-08 05:39 Altering column type causes unstable server and data loss. Michael Long <mlong@datalong.com>
2004-10-08 19:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-09 03:03   ` Re: Altering column type causes unstable server and data Micahael Long <mlong@datalong.com>

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