records zapped to null

* Stephen Davies <scldad@sdc.com.au> [010326 21:36]:

Hello again.

I have just had to restore a database from backup because the majority
of records in one table had "magically" become null.

Last Friday morning there were some 700 valid rows in the table but yesterday
all but 42 had become totally null.

Restoring from Thursday night's backup repaired things so I am
wondering what could have happened in between to cause this strange
phenomenon.

What version of PostgreSQL?

What Platform?

What type of queries?

LER

Cheers and thanks,
Stephen Davies

========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 08-8177 1595
Computing & Network solutions. Fax: 08-8177 0133

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Oops! Sorry.

7.0.2 and Linux.

The database is accessed from a VB5 app via ODBC.

Stephen

On Mon, 26 Mar 2001 21:38:09 -0600, Larry Rosenman said:

* Stephen Davies <scldad@sdc.com.au> [010326 21:36]:

Hello again.

I have just had to restore a database from backup because the majority
of records in one table had "magically" become null.

Last Friday morning there were some 700 valid rows in the table but yesterd

ay

all but 42 had become totally null.

Restoring from Thursday night's backup repaired things so I am
wondering what could have happened in between to cause this strange
phenomenon.

What version of PostgreSQL?

What Platform?

What type of queries?

LER

Cheers and thanks,
Stephen Davies

========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 08-8177 1595
Computing & Network solutions. Fax: 08-8177 0133

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 08-8177 1595
Computing & Network solutions. Fax: 08-8177 0133

Stephen Davies <scldad@sdc.com.au> writes:

I have just had to restore a database from backup because the majority
of records in one table had "magically" become null.

There's no such thing as a null record in Postgres. What do you mean
exactly: were the records still present but their fields all read as
nulls?

What is the declaration of the table?

regards, tom lane

Yes, that is what I mean: every column in the affected rows had been
changed to null;

The description is:

Table "request"
Attribute | Type | Modifier
-------------+----------+----------------------------------------
id | integer | default nextval('reqid'::text)
agency_id | integer |
opportunity | text |
start_date | date |
end_date | date |
focus | text |
slots | smallint |
filled | smallint |
referred | smallint |
date_recd | date | default date("timestamp"('now'::text))
task | text |
uflg | boolean | default 'f'
Indices: redtx,
reqkey,
rfocx,
rmemx,
rsdtx,
tskx

On Tue, 27 Mar 2001 00:09:46 -0500, Tom Lane said:

Stephen Davies <scldad@sdc.com.au> writes:

I have just had to restore a database from backup because the majority
of records in one table had "magically" become null.

There's no such thing as a null record in Postgres. What do you mean
exactly: were the records still present but their fields all read as
nulls?

What is the declaration of the table?

regards, tom lane

========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 08-8177 1595
Computing & Network solutions. Fax: 08-8177 0133

On Tue, Mar 27, 2001 at 03:02:57PM +0930, Stephen Davies wrote:

Yes, that is what I mean: every column in the affected rows had been
changed to null;

The description is:

Table "request"
Attribute | Type | Modifier
-------------+----------+----------------------------------------
id | integer | default nextval('reqid'::text)
agency_id | integer |
opportunity | text |
start_date | date |
end_date | date |
focus | text |
slots | smallint |
filled | smallint |
referred | smallint |
date_recd | date | default date("timestamp"('now'::text))
task | text |
uflg | boolean | default 'f'
Indices: redtx,
reqkey,
rfocx,
rmemx,
rsdtx,
tskx

On Tue, 27 Mar 2001 00:09:46 -0500, Tom Lane said:

Stephen Davies <scldad@sdc.com.au> writes:

I have just had to restore a database from backup because the majority
of records in one table had "magically" become null.

There's no such thing as a null record in Postgres. What do you mean
exactly: were the records still present but their fields all read as
nulls?

What is the declaration of the table?

regards, tom lane

So you have nothing to prevent ...

UPDATE request
SET id = NULL, agency_id = NULL, opportunity = NULL,
start_date = NULL, end_date = NULL, focus = NULL,
slots = NULL, filled = NULL, referred = NULL, date_recd = NULL,
task = NULL, uflg = NULL;

... from succeeding. I'd suspect an errant query/update, perhaps tied
to one of the original values of one of your fields (say agency_id ??).

--
Eric G. Miller <egm2@jps.net>