Обсуждение: records zapped to null
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. 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
* 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>