Re: Tables dissapearing - Mailing list pgsql-general

Erik Jones wrote:
> On Aug 27, 2007, at 4:44 PM, Kamil Srot wrote:
>
>>> Also, in your original post you mentioned a "proprietal CMS
>>> system".  Is this proprietary to your company or one that you've
>>> purchased?  The fact that the same table going on multiple dbs all
>>> being run by that CMS system certainly makes it worthy of suspicion.
>>>
>> This is software developed in our company... so I'm sure it's not
>> duing aby schema manipulation. I'm actually senior developer of this
>> project by accident :-)
>
> That doesn't mean that there's not some whacked out race condition
> causing corruption.  I'm just saying, keep exploring every option.
> Saying, "I wrote and am in charge of that so I know it didn't do it"
> is bad.  You should at the very least have someone else, as well as
> you, review any code that touches that table.
>
>>
>> The strange thing is, all the projects are completelly independend...
>> has its own DB, folder with scripts, different data... just the DB
>> user is the same... so it's higly unprobable, that it'll do 2 similar
>> errors in thow distinct databases at nearly the same time...
>
> Just the DB and your CMS.  Given that setup, if your CMS is causing
> this to happen on one DB, it actually is not unlikely that it would do
> it to the others.
>
OK, sure I cannot put my hand in the fire for it, but after searching
the sources for DDL commands and also becouse of the manner of the
application, I simply do not belive in this opinion. OK, I have the logs
on and I do log everything what goes to the server in the timestamped
logfile...

>> When this problem appeared for the first time, I had clearly the
>> wraparound problem... I did vacuum it and partially restored the
>> data... but in some meantime, I had commands like \dt showing all
>> relations twice... (some system catalog problem)... then I did full
>> dump and restore along with upgrade to newest pgsql server
>> software... this duplicity was gone and never appeared again.
>>
>> From above mentioned duplications of relatio names and what Tom wrote
>> recently (doesn't see like WA problem), it looks like the relation
>> name is/gets corrupted in some way and this corruption is internally
>> taken over to another instance of relation named the same but in
>> another database... but I know - it's too speculative.
>
> Is it the same instance of your CMS managing each of the databases or
> a separate instance per DB?
Every DB has it's own web domain and folder structure with copy of
script files... nothing is shared.
>   Also, how often has this happened?
It happened I thing four times... first time about 4 months ago and then
3 times after about 20 to 30 days... last time yesterday. Then I noticed
this problem one more time today just in one database (one of the two
affected yesterday).
> Since wraparound has been ruled out, it's hard to say what else could
> be the culprit or what to look at and do next without any more
> specific details about the system at the time(s) this has happened.
> What kind of monitoring do you have set up on your DBs?
Starting 2 ours ago, I have complete logs of all commands along with
user and database they are applied to. In the past I have just the
general logs w/o timestamps and DB names... so hard to read...
>   Have you verified that the table's files are still on disk after
> it's "disappeared"?
Do not have any idea how to do it... I wasn't able to access it using
any DML/DDL commands... can try it on a binary backup of the damaged DB
if you'll guide me...

Thank you,
--
Kamil