Re: [GENERAL] Tables dissapearing
Adrian Klaver wrote: I have a question. First a little history. Right now, the people who know better than I are fairly certain Postgres is not changing things on its own and the developer is certain the CMS software is not doing schema changes. As I understand it logging has been cranked up to test both those assumptions. My question is, how are legitimate schema changes done? Just wondering if there is a third party involved. In the version used on this particular server, there is no automatic/programing way of changing the schema. Upgrades are done manually and application itself doesn't need schema changes for routine operations... It's nothing more, nothing less than set of PHP scripts/procedural language functions and few utility scripts doing quite simple DML statements... Regards, -- Kamil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Tables dissapearing
Tom Lane wrote: Erik Jones [EMAIL PROTECTED] writes: Also, I'd write a simple ping script to check for the table that runs every 5 seconds or so. I had gathered that the table was being touched constantly by his app, so that it'd be immediately obvious when it had gone away. If that's not so, then definitely do the above. You're right, Tom... the table is intensivelly used in the application and the application itself is under not high, but big enough load to buy a hit at least each few seconds... If anything happen with the table, entries with not existing relation will appear in the log right away. Regards, -- Kamil
Re: [GENERAL] Tables dissapearing
Alvaro Herrera wrote: Adrian Klaver wrote: I have a question. First a little history. Right now, the people who know better than I are fairly certain Postgres is not changing things on its own and the developer is certain the CMS software is not doing schema changes. As I understand it logging has been cranked up to test both those assumptions. My question is, how are legitimate schema changes done? Just wondering if there is a third party involved. Maybe someone discovered an SQL injection in your apps and is making fun of you by dropping tables. Andrew, Alvaro... well, sure SQL injection is possibility I cannot ignore... (and sure as dad of this application, I think it's not the case :-) ... just kidding... As even the injected SQL will be shown in the logs, so we'll know more after some time. It's too much issues of the same type to thing, it'll not appear anymore. I'm really interested in what is the problem not only from proffesional point of view... Regards, -- Kamil
Re: [GENERAL] Tables dissapearing
On Mon, Aug 27, 2007 at 05:58:26PM -0400, Andrew Sullivan wrote: On Mon, Aug 27, 2007 at 11:44:38PM +0200, Kamil Srot wrote: When this problem appeared for the first time, I had clearly the wraparound problem... I did vacuum it and partially restored the data... I don't think vacuum would fix a wrap around problem in the way you describe. I don't think you have a wrap problem. Actually, it can: if you have just experienced wraparound (within 1 billion transactions or so) then vacuum will magically cause the wrapped rows to reappear. Ofcourse, your indexes may be invalid, your constraints may be violated, but the data will still be there... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Tables dissapearing
Kamil Srot wrote: In the version used on this particular server, there is no automatic/programing way of changing the schema. Upgrades are done manually and application itself doesn't need schema changes for routine operations... In that case, you can settle the matter by making sure your application connects as a user that doesn't own any of the tables. That way they can't be dropped. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Tables dissapearing
Richard Huxton wrote: Kamil Srot wrote: In the version used on this particular server, there is no automatic/programing way of changing the schema. Upgrades are done manually and application itself doesn't need schema changes for routine operations... In that case, you can settle the matter by making sure your application connects as a user that doesn't own any of the tables. That way they can't be dropped. Yes, I'll do it in the future, but now, I want to know what is the problem and solve it rather than doing some workarounds (even if they are good also from the principle indeed). Regards, -- Kamil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tables dissapearing
Kamil Srot wrote: Heh, is the pool still open? Maybe I can make at least something from it :-D Current odds Application bug: even money Application configuration issue: 2-1 Rogue cron job or other maintenance process: 4-1 Somebody messing with you (or SQL injection): 8-1 XID wraparound issue :10-1 Alien or supernatural intervention: 18-1 Obscure postgresql bug nobody else has ever seen: 25-1 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Tables dissapearing
On Aug 28, 2007, at 9:41 AM, Kamil Srot wrote: Jeff Amiel wrote: My entire shop has set up a betting pool on the outcome of this...so I hope you post results regardless of the outcome, Kamil. Heh, is the pool still open? Maybe I can make at least something from it :-D (in all seriousness, we hope you find/fix the problem before things get really ugly) Will post about every relevant doings to this issue... Thanks! -- /me remembers Pete Rose. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tables dissapearing
My entire shop has set up a betting pool on the outcome of this...so I hope you post results regardless of the outcome, Kamil. (in all seriousness, we hope you find/fix the problem before things get really ugly) Kamil Srot wrote: Richard Huxton wrote: Kamil Srot wrote: In the version used on this particular server, there is no automatic/programing way of changing the schema. Upgrades are done manually and application itself doesn't need schema changes for routine operations... In that case, you can settle the matter by making sure your application connects as a user that doesn't own any of the tables. That way they can't be dropped. Yes, I'll do it in the future, but now, I want to know what is the problem and solve it rather than doing some workarounds (even if they are good also from the principle indeed). Regards, ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tables dissapearing
Jeff Amiel wrote: My entire shop has set up a betting pool on the outcome of this...so I hope you post results regardless of the outcome, Kamil. Heh, is the pool still open? Maybe I can make at least something from it :-D (in all seriousness, we hope you find/fix the problem before things get really ugly) Will post about every relevant doings to this issue... Thanks! -- Kamil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tables dissapearing
At 03:15 PM 8/28/2007, Kamil Srot wrote: Andrew, Alvaro... well, sure SQL injection is possibility I cannot ignore... (and sure as dad of this application, I think it's not the case :-) ... just kidding... As even the injected SQL will be shown in the logs, so we'll know more after some time. It's too much issues of the same type to thing, it'll not appear anymore. I'm really interested in what is the problem not only from proffesional point of view... If it's SQL injection via a webserver app, and you have _already_ logged http requests in sufficient detail you could try to look for a drop in them or variations of escaped versions of it e.g. ..%44%52%4f%50... %64%52o%70 at about the time you think the incident happened. Of course it could just be someone had db access and sent the drop command. Regards, Link. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Tables dissapearing
On 28/08/2007 15:48, Jeff Amiel wrote: Alien or supernatural intervention: 18-1 Obscure postgresql bug nobody else has ever seen: 25-1 That's the sort of confidence in the DBMS we all like to see! :-) Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Tables dissapearing
Kamil Srot [EMAIL PROTECTED] writes: One more thing: The project runs proprietal CMS system and there are more instances of it with the same database layout in different databases. Every time the lost table is the same one - the bussiest one (mostly read)... and everytime the lost table is gone from more projects at once... today it was 2 projects affected out of 7 in the same pgsql server... it looks also like the problem happens everytime short after midnight - scarry - ha ha :-) Tables don't just disappear like that. Check your application code to see if it's not dropping the table someplace. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Tables dissapearing
Tom Lane wrote: Kamil Srot [EMAIL PROTECTED] writes: One more thing: The project runs proprietal CMS system and there are more instances of it with the same database layout in different databases. Every time the lost table is the same one - the bussiest one (mostly read)... and everytime the lost table is gone from more projects at once... today it was 2 projects affected out of 7 in the same pgsql server... it looks also like the problem happens everytime short after midnight - scarry - ha ha :-) Tables don't just disappear like that. Check your application code to see if it's not dropping the table someplace. regards, tom lane grep -i drop your_application_dir ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tables dissapearing
brian wrote: Tom Lane wrote: Kamil Srot [EMAIL PROTECTED] writes: One more thing: The project runs proprietal CMS system and there are more instances of it with the same database layout in different databases. Every time the lost table is the same one - the bussiest one (mostly read)... and everytime the lost table is gone from more projects at once... today it was 2 projects affected out of 7 in the same pgsql server... it looks also like the problem happens everytime short after midnight - scarry - ha ha :-) Tables don't just disappear like that. Check your application code to see if it's not dropping the table someplace. regards, tom lane grep -i drop your_application_dir This was the first thing, I checked... if something, this is the thing, I'm 100% sure is not the case. The application doesn't manipulate with the database schema at all... so no CREATE/ALTER/DROP statements anywhere. On top there is the dissaperance from more databases at once (they are absolutelly no interconnected in any way - all live their own life) I don't say, it's gone by itself, I'm asking for help debuging this situation and hopefully find a solution. For the first time it happened, it had the same symptoms - this specific table was missing and transaction counter was wrapped-around. Now it seems to be the same but the transaction counters are far below the critical value... It happened four times already and the customer is starting to become crazy... the same as I do... Thank you, Tom, Brian and all who read it, for your time! Regards, -- Kamil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tables dissapearing
On Mon, Aug 27, 2007 at 06:37:17PM +0200, Kamil Srot wrote: I don't say, it's gone by itself, I'm asking for help debuging this situation and hopefully find a solution. For the first time it happened, it had the same symptoms - this specific table was missing and transaction counter was wrapped-around. Now it seems to be the same but the transaction counters are far below the critical value... You don't say explicitly, but when you say vacuum all tables you didn't specify any tables names right? You just typed vacuum. On each database... As for debugging, maybe something like: select xmin, age(xmin) from pg_class; Just to check the wraparound issue... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Tables dissapearing
Martijn van Oosterhout wrote: On Mon, Aug 27, 2007 at 06:37:17PM +0200, Kamil Srot wrote: I don't say, it's gone by itself, I'm asking for help debuging this situation and hopefully find a solution. For the first time it happened, it had the same symptoms - this specific table was missing and transaction counter was wrapped-around. Now it seems to be the same but the transaction counters are far below the critical value... You don't say explicitly, but when you say vacuum all tables you didn't specify any tables names right? You just typed vacuum. On each database... Correct...the script does echo vacuum full; | $PGDIR/bin/psql -U postgres $db for each database... Hope it's correct? As for debugging, maybe something like: select xmin, age(xmin) from pg_class; Just to check the wraparound issue... Maybe it's it... I did check the wraparound with: SELECT datname, age(datfrozenxid) FROM pg_database; In your SQL I see several rows with too high numbers! They are all internal like pg_toast_618854, views and also some application level indices etc. So it seems it's not enough to vacuum all DB's (templateX inclusive) Can you please advice how to correctly manually vacuum the pgsql? Thank you! Regards, -- Kamil
Re: [GENERAL] Tables dissapearing
Martijn van Oosterhout wrote: On Mon, Aug 27, 2007 at 06:57:54PM +0200, Kamil Srot wrote: Correct...the script does echo vacuum full; | $PGDIR/bin/psql -U postgres $db for each database... Hope it's correct? Well, I'd drop the full part, it tends to bloat indexes. Also, did you check it was actually completing (no errors)? Yes, it completes w/o errors... just VACUUM as output... OK, I'll drop the full part and do it less often... Maybe it's it... I did check the wraparound with: SELECT datname, age(datfrozenxid) FROM pg_database; In your SQL I see several rows with too high numbers! They are all internal like pg_toast_618854, views and also some application level indices etc. Depends what you mean by too high. Anything with XID 1 and 2 is not a problem, and age returns a really big number for them. Can you give some examples? High numbers is near by the bigint limit... 2 billions. Here you go: # select xmin, age(xmin) from pg_class; xmin|age ---+ 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 236838019 | 539453 2 | 2147483647 2 | 2147483647 231899309 |5478163 etc. Basically everythin has the same age 2147483647 with xmin = 1 or xmin = 2.. but the two lines shown... Actually the same problem happened several minutes ago :-( Thank you for any hint! Regards, -- Kamil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Tables dissapearing
On Mon, Aug 27, 2007 at 06:57:54PM +0200, Kamil Srot wrote: Correct...the script does echo vacuum full; | $PGDIR/bin/psql -U postgres $db for each database... Hope it's correct? Well, I'd drop the full part, it tends to bloat indexes. Also, did you check it was actually completing (no errors)? Maybe it's it... I did check the wraparound with: SELECT datname, age(datfrozenxid) FROM pg_database; In your SQL I see several rows with too high numbers! They are all internal like pg_toast_618854, views and also some application level indices etc. Depends what you mean by too high. Anything with XID 1 and 2 is not a problem, and age returns a really big number for them. Can you give some examples? Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Tables dissapearing
Kamil Srot wrote: Martijn van Oosterhout wrote: On Mon, Aug 27, 2007 at 06:57:54PM +0200, Kamil Srot wrote: Correct...the script does echo vacuum full; | $PGDIR/bin/psql -U postgres $db for each database... Hope it's correct? Well, I'd drop the full part, it tends to bloat indexes. Also, did you check it was actually completing (no errors)? Yes, it completes w/o errors... just VACUUM as output... OK, I'll drop the full part and do it less often... What user is doing it? Is it a superuser? If it's not, then there's your problem. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 If it wasn't for my companion, I believe I'd be having the time of my life (John Dunbar) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tables dissapearing
Alvaro Herrera wrote: Kamil Srot wrote: Martijn van Oosterhout wrote: On Mon, Aug 27, 2007 at 06:57:54PM +0200, Kamil Srot wrote: Correct...the script does echo vacuum full; | $PGDIR/bin/psql -U postgres $db for each database... Hope it's correct? Well, I'd drop the full part, it tends to bloat indexes. Also, did you check it was actually completing (no errors)? Yes, it completes w/o errors... just VACUUM as output... OK, I'll drop the full part and do it less often... What user is doing it? Is it a superuser? If it's not, then there's your problem. It's user postgres... the vacuum completes correctly... Thank you, -- Kamil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Tables dissapearing
On Mon, Aug 27, 2007 at 07:15:44PM +0200, Kamil Srot wrote: OK, I'll drop the full part and do it less often... This doesn't address your problem, but when you move from VACUUM FULL to VACUUM, you want to do it _more_ often, not less. But given what you've posted, I am not even a little bit convinced your script is working as you think it is. You're clearly not managing to vacuum the entire database sometimes. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tables dissapearing
Andrew Sullivan wrote: On Mon, Aug 27, 2007 at 07:15:44PM +0200, Kamil Srot wrote: OK, I'll drop the full part and do it less often... This doesn't address your problem, but when you move from VACUUM FULL to VACUUM, you want to do it _more_ often, not less. Sure, I ment it like I'll do the FULL vacuum less often than daily and do daily the plain vacuum command. But given what you've posted, I am not even a little bit convinced your script is working as you think it is. You're clearly not managing to vacuum the entire database sometimes. Well, I do list all databases with the command psql -U postgres -l -t and with some shell coding I do vacuum all shown databases... I don't think there is some problem in it... If you do see some, can you give me a hint? Thank you, -- Kamil ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Tables dissapearing
On Mon, Aug 27, 2007 at 10:03:04PM +0200, Kamil Srot wrote: Sure, I ment it like I'll do the FULL vacuum less often than daily and do daily the plain vacuum command. If you have your servers set up correctly, you should never need to perform VACUUM FULL. Well, I do list all databases with the command psql -U postgres -l -t and with some shell coding I do vacuum all shown databases... I don't think there is some problem in it... If you do see some, can you give me a hint? I don't think I've seen the script itself. Without seeing it, I can only guess. But you don't actually need to do that. You should be able to run vacuumdb -a, and get the result you need. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tables dissapearing
Andrew Sullivan wrote: On Mon, Aug 27, 2007 at 10:03:04PM +0200, Kamil Srot wrote: Sure, I ment it like I'll do the FULL vacuum less often than daily and do daily the plain vacuum command. If you have your servers set up correctly, you should never need to perform VACUUM FULL. I see. Thank you for the hint... Well, I do list all databases with the command psql -U postgres -l -t and with some shell coding I do vacuum all shown databases... I don't think there is some problem in it... If you do see some, can you give me a hint? I don't think I've seen the script itself. Without seeing it, I can only guess. But you don't actually need to do that. You should be able to run vacuumdb -a, and get the result you need. The script is very simple one: #!/bin/sh PGDIR=/opt/pgsql DATABASES=`$PGDIR/bin/psql -U postgres -l -t | cut -f 1 -d '|'` for db in $DATABASES do echo vacuum; | $PGDIR/bin/psql -U postgres $db done I can easily rewrite it to use the vacuumdb command, but I doubt it'll make any difference. Is there a way how to find out the problem is here? I scanned the log output of pgsql for wrap around but didn't found aby recent relevant entries... I'm pretty sure, I miss something stupid :-( Thank you very much for your help, -- Kamil
Re: [GENERAL] Tables dissapearing
On Mon, Aug 27, 2007 at 10:31:11PM +0200, Kamil Srot wrote: The script is very simple one: Well, I don't see anything obvious, but. . . I can easily rewrite it to use the vacuumdb command, but I doubt it'll make any difference. The point is that you don't have to rewrite it. Just run vacuumdb -a and it vacuums _all_ databases. Is there a way how to find out the problem is here? I scanned the log output of pgsql for wrap around but didn't found aby recent relevant entries... I'm pretty sure, I miss something stupid :-( Are you quite sure you're getting all your logs where you think you are? A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Tables dissapearing
Andrew Sullivan wrote: I can easily rewrite it to use the vacuumdb command, but I doubt it'll make any difference. The point is that you don't have to rewrite it. Just run vacuumdb -a and it vacuums _all_ databases. Oh, I have it now! It takes some time, but at the end, I'll understand :-D Sure, it's much better than coding some scripts etc. I already used it in my script... Is there a way how to find out the problem is here? I scanned the log output of pgsql for wrap around but didn't found aby recent relevant entries... I'm pretty sure, I miss something stupid :-( Are you quite sure you're getting all your logs where you think you are? Yes, the log is correct... I can see statement errors I did selecting from system tables few hours ago... I have default debug level... should I increase it? I really need to make sure, the next problem (if it's necessary to let it happen) will be the last one... :-( Thank you, -- Kamil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tables dissapearing
Kamil Srot [EMAIL PROTECTED] writes: # select xmin, age(xmin) from pg_class; xmin|age ---+ 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 236838019 | 539453 2 | 2147483647 2 | 2147483647 231899309 |5478163 etc. I see no indication here that there's anything wrong at all. The rows with xmin = 2 (ie, FrozenTransactionID) are evidently pg_class rows that have never been modified since the database was created. There isn't anything wrong with them. age() is returning INT_MAX for them to denote that they are infinitely far in the past, but they are in no danger of being invalidated by wraparound. The newer rows indicate that your current transaction counter is around 237 million, which is barely a tenth of the way to wraparound. While it's conceivable that it already wrapped around (ie, you've really executed 237 million plus some multiple of 2^32 transactions), I think it's more probable that no wraparound has happened and your problem is entirely unrelated to that. I believe this in part because having just one table disappear is not the likely result of an XID wraparound problem, and in part because 8.2 has got very strong defenses against allowing wraparound failure to occur: it will shut down first, and it will do a whole lot of bleating and autovacuuming before that. You haven't yet showed us any details of what happens. What exactly do you mean by the table disappearing? Can you select from it? Do you see a row for it in pg_class? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tables dissapearing
Tom Lane wrote: Kamil Srot [EMAIL PROTECTED] writes: # select xmin, age(xmin) from pg_class; xmin|age ---+ 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 236838019 | 539453 2 | 2147483647 2 | 2147483647 231899309 |5478163 etc. I see no indication here that there's anything wrong at all. The rows with xmin = 2 (ie, FrozenTransactionID) are evidently pg_class rows that have never been modified since the database was created. There isn't anything wrong with them. age() is returning INT_MAX for them to denote that they are infinitely far in the past, but they are in no danger of being invalidated by wraparound. I think the same, but I'm at the end of my ideas... You haven't yet showed us any details of what happens. What exactly do you mean by the table disappearing? Can you select from it? Do you see a row for it in pg_class? It's completelly gone... cannot do anything with this relation... I fixed it by creating this specific table from several hours old backup and it's fine (this table is nearly static)... I didn't check if the relation was in pg_class... but I have complete backup of database files after this issue from today and also from the last time it happened (but they was vacuumed few hour before the backup by nightly script, so the data can be changed in this snapshots)... but it it can help and you'll navigate me, I can fetch any data you want from it... Thank you, -- Kamil
Re: [GENERAL] Tables dissapearing
On Aug 27, 2007, at 4:08 PM, Tom Lane wrote: Kamil Srot [EMAIL PROTECTED] writes: # select xmin, age(xmin) from pg_class; xmin|age ---+ 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 2 | 2147483647 236838019 | 539453 2 | 2147483647 2 | 2147483647 231899309 |5478163 etc. I see no indication here that there's anything wrong at all. The rows with xmin = 2 (ie, FrozenTransactionID) are evidently pg_class rows that have never been modified since the database was created. There isn't anything wrong with them. age() is returning INT_MAX for them to denote that they are infinitely far in the past, but they are in no danger of being invalidated by wraparound. The newer rows indicate that your current transaction counter is around 237 million, which is barely a tenth of the way to wraparound. While it's conceivable that it already wrapped around (ie, you've really executed 237 million plus some multiple of 2^32 transactions), I think it's more probable that no wraparound has happened and your problem is entirely unrelated to that. I believe this in part because having just one table disappear is not the likely result of an XID wraparound problem, and in part because 8.2 has got very strong defenses against allowing wraparound failure to occur: it will shut down first, and it will do a whole lot of bleating and autovacuuming before that. You haven't yet showed us any details of what happens. What exactly do you mean by the table disappearing? Can you select from it? Do you see a row for it in pg_class? regards, tom lane 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. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Tables dissapearing
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 :-) 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... 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. Maybe the binary backups of all the DB files from the time of this problem can give some answers? Regards, -- Kamil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tables dissapearing
Kamil Srot wrote: Erik Jones 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 :-) 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... IMHO, it's not at all improbable, given that this software is connecting to the same databases you are seeing affected by this phenomenon. Not to mention that it's proprietary, so fewer eyes have gone over it.[1] I suggested earlier grepping for 'drop' in your application. Perhaps you should do the same on the CMS. [1] Not meant as a dig at non-free software. brian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tables dissapearing
On Mon, Aug 27, 2007 at 11:44:38PM +0200, Kamil Srot wrote: When this problem appeared for the first time, I had clearly the wraparound problem... I did vacuum it and partially restored the data... I don't think vacuum would fix a wrap around problem in the way you describe. I don't think you have a wrap problem. 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. No, it just doesn't work that way. Are these all shared on the same hardware? Are you sure you're not having other problems? A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tables dissapearing
Kamil Srot [EMAIL PROTECTED] writes: 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. The fact that the same thing happens concurrently in several independent databases is to me the strongest indication that it's something the client is doing. There isn't *any* mechanism within Postgres that would somehow cause similarly-named tables in different DBs to go south at the same time. I don't say that it couldn't be a PG bug, but I do say that the client is doing something similar in each database to trigger it. My advice is to turn on log_statement and acquire a trace (with timestamps) of all the SQL commands being executed across the whole system. Looking at what happens up to the instant the problem appears should provide some clues. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Tables dissapearing
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. 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? Also, how often has this happened? 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? Have you verified that the table's files are still on disk after it's disappeared? Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tables dissapearing
On 8/27/07, Kamil Srot [EMAIL PROTECTED] wrote: I really need to make sure, the next problem (if it's necessary to let it happen) will be the last one... :-( Have you set your log_statement to 'ddl' in postgresql.conf? That way you'll have a log of every single ddl that goes through the database. At least then when the table disappears again you can look in your logs to see if it was in fact a client app that did it. Also, look at taking away permission from anyone BUT the postgres superuser to drop objects. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tables dissapearing
brian wrote: Kamil Srot wrote: Erik Jones 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 :-) 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... IMHO, it's not at all improbable, given that this software is connecting to the same databases you are seeing affected by this phenomenon. Not to mention that it's proprietary, so fewer eyes have gone over it.[1] I suggested earlier grepping for 'drop' in your application. Perhaps you should do the same on the CMS. [1] Not meant as a dig at non-free software. Understood and I agree. I would accept the possibility to do the most stupid coding error in the world, but there is simply no statement manipulating with the schema in the whole project. To be sure, I searched thru the sources for /drop/i and didn't found anywhere but the pear framework of PHP (some constants and test patterns of user statements)... OK, I agree this can be run by some coincidence and so I have started to log all statements in the logfile... but still I doubt executing the drop statement is the problem... Regards, -- Kamil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tables dissapearing
Andrew Sullivan wrote: On Mon, Aug 27, 2007 at 11:44:38PM +0200, Kamil Srot wrote: When this problem appeared for the first time, I had clearly the wraparound problem... I did vacuum it and partially restored the data... I don't think vacuum would fix a wrap around problem in the way you describe. I don't think you have a wrap problem. I don't know much about this part of the pgsql... so I did follow some instructions in the list and did restore the data... at the end, I did dump/restore... I just wanted to point to the fakt that wraparound was there in the beginning (entries in log, etc.) 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. No, it just doesn't work that way. Are these all shared on the same hardware? Are you sure you're not having other problems? Yes, all affected databases rund under one pgsql server on the same hardware... I don't see any other problems so far. It's quite busy server runing apache, pgsql and php along with few scheduled jobs in the night... Regards, -- Kamil
Re: [GENERAL] Tables dissapearing
Kamil Srot wrote: brian wrote: IMHO, it's not at all improbable, given that this software is connecting to the same databases you are seeing affected by this phenomenon. Not to mention that it's proprietary, so fewer eyes have gone over it.[1] I suggested earlier grepping for 'drop' in your application. Perhaps you should do the same on the CMS. [1] Not meant as a dig at non-free software. Understood and I agree. I would accept the possibility to do the most stupid coding error in the world, but there is simply no statement manipulating with the schema in the whole project. To be sure, I searched thru the sources for /drop/i and didn't found anywhere but the pear framework of PHP (some constants and test patterns of user statements)... OK, I agree this can be run by some coincidence and so I have started to log all statements in the logfile... but still I doubt executing the drop statement is the problem... Just wondering: what's the name of this table? What's its function? I think you mentioned that it's pretty well static. But what is it holding? Maybe that'll provide a clue as to where to look. brian ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tables dissapearing
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tables dissapearing
Tom Lane wrote: Kamil Srot [EMAIL PROTECTED] writes: 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. The fact that the same thing happens concurrently in several independent databases is to me the strongest indication that it's something the client is doing. There isn't *any* mechanism within Postgres that would somehow cause similarly-named tables in different DBs to go south at the same time. I don't say that it couldn't be a PG bug, but I do say that the client is doing something similar in each database to trigger it. My advice is to turn on log_statement and acquire a trace (with timestamps) of all the SQL commands being executed across the whole system. Looking at what happens up to the instant the problem appears should provide some clues. OK, I did it... have good logging now, so if it'll happen again, I'll post relevant part of it to the list. Thank you for your help! -- Kamil
Re: [GENERAL] Tables dissapearing
brian wrote: Just wondering: what's the name of this table? What's its function? I think you mentioned that it's pretty well static. But what is it holding? Maybe that'll provide a clue as to where to look. The system is actually management of websiteeshop with webbased UI ... the table is called section_mapping and contains web addresses of all elements of the website in hirarchical structure... similar to directory structure with parents/children. It has 5 integer and one varchar attribute (the part of URL)... The data are changed only during administration of the system (adding/deleteng products in the eshop, publishing new documents, manipulating with the existing ones)... but it's accessed (read) each time anything is shown thru the web... so several times per displayed web page... Also if it would happend during administration of the content, the customer would scream to us right away, but we found it our several hours after it occured as our monitoring didn't expect to loose one single table :-( -- Kamil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Tables dissapearing
Kamil Srot [EMAIL PROTECTED] writes: Erik Jones wrote: 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... Make a note now of the table's relfilenode value (it'll be different in each database), and confirm that you see it in the filesystem. After the next disappearance, see if anything's still there. For background read http://www.postgresql.org/docs/8.2/static/storage.html Note that certain operations like TRUNCATE and CLUSTER change the relfilenode, so if you're using any of those then it might get harder to track where the file is. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Tables dissapearing
Tom Lane wrote: Kamil Srot [EMAIL PROTECTED] writes: Erik Jones wrote: 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... Make a note now of the table's relfilenode value (it'll be different in each database), and confirm that you see it in the filesystem. After the next disappearance, see if anything's still there. For background read http://www.postgresql.org/docs/8.2/static/storage.html OK, I have the filenames noted and I do confirm, they all does exist now under the base in the pgsql tree... Note that certain operations like TRUNCATE and CLUSTER change the relfilenode, so if you're using any of those then it might get harder to track where the file is. There is not any manipulation with the structure of the DB, so it'll stay the same... Thank you! -- Kamil
Re: [GENERAL] Tables dissapearing
On Aug 27, 2007, at 7:57 PM, Kamil Srot wrote: Tom Lane wrote: Kamil Srot [EMAIL PROTECTED] writes: Erik Jones wrote: 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... Make a note now of the table's relfilenode value (it'll be different in each database), and confirm that you see it in the filesystem. After the next disappearance, see if anything's still there. For background read http://www.postgresql.org/docs/8.2/ static/storage.html OK, I have the filenames noted and I do confirm, they all does exist now under the base in the pgsql tree... Note that certain operations like TRUNCATE and CLUSTER change the relfilenode, so if you're using any of those then it might get harder to track where the file is. There is not any manipulation with the structure of the DB, so it'll stay the same... Thank you! Also, I'd write a simple ping script to check for the table that runs every 5 seconds or so. Have it, at the very least, write out the timestamp of when the table disappears into a file. Better yet would be for it to send you an alert so you can check it out right away. With that *when* you'll know *where* in your logs to look to see what happened at that time. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tables dissapearing
Adrian Klaver wrote: I have a question. First a little history. Right now, the people who know better than I are fairly certain Postgres is not changing things on its own and the developer is certain the CMS software is not doing schema changes. As I understand it logging has been cranked up to test both those assumptions. My question is, how are legitimate schema changes done? Just wondering if there is a third party involved. Maybe someone discovered an SQL injection in your apps and is making fun of you by dropping tables. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tables dissapearing
On Monday 27 August 2007 5:57 pm, Kamil Srot wrote: Tom Lane wrote: Kamil Srot [EMAIL PROTECTED] writes: Erik Jones wrote: 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... Make a note now of the table's relfilenode value (it'll be different in each database), and confirm that you see it in the filesystem. After the next disappearance, see if anything's still there. For background read http://www.postgresql.org/docs/8.2/static/storage.html OK, I have the filenames noted and I do confirm, they all does exist now under the base in the pgsql tree... Note that certain operations like TRUNCATE and CLUSTER change the relfilenode, so if you're using any of those then it might get harder to track where the file is. There is not any manipulation with the structure of the DB, so it'll stay the same... Thank you! I have a question. First a little history. Right now, the people who know better than I are fairly certain Postgres is not changing things on its own and the developer is certain the CMS software is not doing schema changes. As I understand it logging has been cranked up to test both those assumptions. My question is, how are legitimate schema changes done? Just wondering if there is a third party involved. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Tables dissapearing
Erik Jones [EMAIL PROTECTED] writes: Also, I'd write a simple ping script to check for the table that runs every 5 seconds or so. I had gathered that the table was being touched constantly by his app, so that it'd be immediately obvious when it had gone away. If that's not so, then definitely do the above. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tables dissapearing
On Tue, Aug 28, 2007 at 02:03:29AM +0200, Kamil Srot wrote: The system is actually management of websiteeshop with webbased UI ... Um, are you sure you don't have a SQL-injection problem, and someone is doing Something Bad to you? A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 6: explain analyze is your friend
