[BUGS] Missing tables in postgresql 7.2.4
Hi, we recently discovered on our production database an a little bit bizarre problem (after two years stable operations). Some tables are simply missing, or sometimes the affected table(s) is/are there but not listed in pg_tables. An example (I am looking for the table kog_blasthit_tables): dev_db=# select version(); version - PostgreSQL 7.2.4 on sparc-sun-solaris2.8, compiled by GCC gcc (GCC) 3.1 (1 row) dev_db=# \d kog_ kog_blasthits_template kog_hits_obj_2374_q411_db7 kog_hits_obj_1016_q344_db7 kog_hits_obj_2396_q833_db7 kog_hits_obj_1341_q475_db7 kog_hits_obj_2491_q505_db7 kog_hits_obj_1362_q412_db7 kog_hits_obj_2516_q900_db7 kog_hits_obj_1364_q413_db7 kog_hits_obj_2559_q78_db7 kog_hits_obj_1584_q570_db7 kog_hits_obj_822_q369_db7 kog_hits_obj_1604_q574_db7 kog_hits_obj_834_q371_db7 kog_hits_obj_1660_q608_db7 kog_hits_obj_846_q375_db7 kog_hits_obj_1725_q650_db7 kog_hits_obj_880_q339_db7 kog_hits_obj_1737_q605_db7 kog_info kog_hits_obj_2186_q686_db7 kog_org // No kog_blasthit_tables !!! BUT it seems to be there: dev_db=# select * FROM kog_blasthit_tables LIMIT 1; id | object_id | query_set_id | db_set_id | num_of_query_sequences | table_name | table_desc | number_of_entries | blast_parameters | blast_prg | evalue_cutoff | owner | project |created| status | number_of_unique_entries | reciprocal_first_id | reciprocal_second_id -+---+--+---++++---+---+---+---+--+-+---+--+--+-+-- 398 | 1341 | 475 | 7 | 26187 | kog_hits_obj_1341_q475_db7 | na | 0 | -a 1 -m 7 -T F -F T -p blastx -e 0.10 | kogblast | 0.1 | mbeckste | 23 | 2004-02-26 18:36:48.048881+01 | finished |27426 | -1 | -1 (1 row) dev_db=# // The table seems to be there but not in pg_tables dev_db=# select * FROM pg_tables WHERE tablename='kog_blasthit_tables'; tablename | tableowner | hasindexes | hasrules | hastriggers ---+++--+- (0 rows) dev_db=# \d pg_ta // nothing but dev_db=# select count(*) FROM pg_tables; count --- 875 (1 row) The case which is even worth, is that the table is missing completely. We have a catalog table that keeps track of generated tables, so we know that it has to be there. The postmaster logs (with high debug level) show no DROP TABLE between the time of the CREATE TABLE statement of the affected table and the time where we noticed that it is missing. I know that this is probably not enough information for a precise analysis. May be someone can give me a hint how to further investigate this issue. regards Michael -- -- Dipl.-Inform. Michael Beckstette Office: M3-129 AG-PI / Technische Fakultaet EMail:[EMAIL PROTECTED] Universitaet Bielefeld Fon: +49-521-106-2914 Postfach 100131Fax: +49-521-106-6411 D-33501 BIELEFELD Germany ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #1658: Error download of odbc
The following bug has been logged online: Bug reference: 1658 Logged by: Marcio Roberto Chiaveli Email address: [EMAIL PROTECTED] PostgreSQL version: 7.2 Operating system: windows Description:Error download of odbc Details: When doing download of the driver ODBC, when I try descompacta-lo (unzip) him me of the the following mistake message: Cannot open file: it donates not uppear to be the valid archive. If you downloaded this file try downloading the file again. What cannot this happening? ---(end of broadcast)--- TIP 3: 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
[BUGS] BUG #1657: Database don't start
The following bug has been logged online: Bug reference: 1657 Logged by: Miguel Higuera Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Operating system: Windows XP Description:Database don't start Details: Hi I have Install Postgresql 8 on Windows Xp Profesional but the service don't start. I Uninstall the antivirus and the firewall but it is unusless. Any sugestion is welcome Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #1659: primary key accepts null value
The following bug has been logged online: Bug reference: 1659 Logged by: İbrahim Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.2 Operating system: Windows XP sp1 Description:primary key accepts null value Details: i m using pg 8.0.2 in a project with Visual Studio.NET at Windows Platform but when i send a null value to my Prmary Key column it accepts that value. I think it is a hard problem and i cant delete the null valued primary key column by PgAdmin3 can you help me about this? Thanks İbrahim BAYER Computer Engineer ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] Bug Report with Postgres 7.4 on AIX 5.3
The AIX 5.3 provide the structure sockaddr_storage :
struct sockaddr_storage {
ushort_t__ss_family;/* address family */
char__ss_pad1[_SS_PAD1SIZE]; /* pad up to alignment
field */
#if defined(__64BIT__) || (defined(_ALL_SOURCE) && defined(_LONG_LONG))
int64_t __ss_align; /* field to force desired structure
*/
/* storage alignment */
#else
int __ss_align[2];
#endif
char__ss_pad2[_SS_PAD2SIZE];
/* pad to achieve desired size */
};
In Postgres the structure SockAddr is the following :
typedef struct
{
struct sockaddr_storage addr;
ACCEPT_TYPE_ARG3 salen;
} SockAddr
In Postgress this structure sockaddr_storage is filled with the structure
sockaddr_un but the size of sockaddr_storage
is less then the size of sockaddr_un and cause a memory overflow !
Do you have any idea how to find a workaround ?
Vincent Vanwynsberghe
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: mardi 10 mai 2005 18:46
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]; pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] Bug Report with Postgres 7.4 on AIX 5.3
>
>
> Vincent Vanwynsberghe <[EMAIL PROTECTED]> writes:
> > In our platform the sizeof of struct sockaddr_un is 1025 and
> the sizeof of
> > SockAddr is 144.
>
> Doesn't AIX provide struct sockaddr_storage? That struct has to be at
> least as large as any of the other platform-specific sockaddr structs.
>
> regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [BUGS] Missing tables in postgresql 7.2.4
"Michael Beckstette" <[EMAIL PROTECTED]> writes: > we recently discovered on our production database an a little bit bizarre > problem (after two years stable operations). Some tables are simply missing, > or > sometimes the affected table(s) is/are there but not listed in pg_tables. This sounds a bit like a transaction ID wraparound problem. Have you been vacuuming your whole database on a reasonable schedule? The missing tables might conceivably be old enough that their pg_class rows have wrapped around "into the future". It'd be useful to look at SELECT datname, age(datfrozenxid) FROM pg_database; regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] Bug Report with Postgres 7.4 on AIX 5.3
Vincent Vanwynsberghe <[EMAIL PROTECTED]> writes: > The AIX 5.3 provide the structure sockaddr_storage : > ... > In Postgress this structure sockaddr_storage is filled with the structure > sockaddr_un but the size of sockaddr_storage > is less then the size of sockaddr_un and cause a memory overflow ! > Do you have any idea how to find a workaround ? Report this bug to IBM: the AIX headers are defining the structs wrong. You can quote RFC 3493 - Basic Socket Interface Extensions for IPv6 section 3.10: One simple addition to the sockets API that can help application writers is the "struct sockaddr_storage". This data structure can simplify writing code that is portable across multiple address families and platforms. This data structure is designed with the following goals. - Large enough to accommodate all supported protocol-specific address structures. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Missing tables in postgresql 7.2.4
Hi Tom, this is the output from 'SELECT datname, age(datfrozenxid) FROM pg_database;' datname | age -+- xgc | -1950241750 dev_db | -1886587214 template1 | -1884294460 template0 | -1884294460 promo_db| -1884294460 snap_db_new | -1884294460 gendev_db | 1887538137 (7 rows) dev_db=# The affected DB is 'dev_db', although it looks like the others except 'gendev_db' have a wraparound problem too (?). To answer your question about the VACUUM: We VACUUM FULL a few tables with a high amount of INSERT/DEL operations once per hour, but I have to admit that we perform a VACUUM of the whole DB not on a regulary basis. I think the last one was several monthes ago. Further on we use transactions at several places and we have at least 20 transactions per minute. Does now a normal VACUUM FULL of the whole DB(s) fix our problem? Michael On May 11, 11:51am, Tom Lane wrote: > Subject: Re: [BUGS] Missing tables in postgresql 7.2.4 > "Michael Beckstette" <[EMAIL PROTECTED]> writes: > > we recently discovered on our production database an a little bit bizarre > > problem (after two years stable operations). Some tables are simply missing, or > > sometimes the affected table(s) is/are there but not listed in pg_tables. > > This sounds a bit like a transaction ID wraparound problem. Have you > been vacuuming your whole database on a reasonable schedule? The > missing tables might conceivably be old enough that their pg_class rows > have wrapped around "into the future". It'd be useful to look at > SELECT datname, age(datfrozenxid) FROM pg_database; > > regards, tom lane -- -- Dipl.-Inform. Michael Beckstette Office: M3-129 AG-PI / Technische Fakultaet EMail:[EMAIL PROTECTED] Universitaet Bielefeld Fon: +49-521-106-2914 Postfach 100131Fax: +49-521-106-6411 D-33501 BIELEFELD Germany ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] Missing tables in postgresql 7.2.4
Hi, after reading the docs (I know it was a little bit late), I am now relatively sure that I trapped into a transaction ID wraparound problem. For me its now a little bit unclear, how to proceed in order to minimize the caused damage. I checked all tables in the affected DB. Till now, the results are as follows: 6 user tables are completely lost. 8 user tables are not listed in pg_tables but still accessible by a SELECT. The 6 completely lost tables are not so dramatical, because they contain only static data, that I can restore from the development system. But what happens with the 8 tables that are still accessable, but not listed in pg_tables, after a VACUUM? Will they be removed completely or 'reinserted' into pg_tables? Does anyone has an advise how to proceed in this situation? Regards Michael On May 11, 6:11pm, Michael Beckstette wrote: > Subject: Re: [BUGS] Missing tables in postgresql 7.2.4 > Hi Tom, > > this is the output from 'SELECT datname, age(datfrozenxid) FROM pg_database;' > >datname | age > -+- > xgc | -1950241750 > dev_db | -1886587214 > template1 | -1884294460 > template0 | -1884294460 > promo_db| -1884294460 > snap_db_new | -1884294460 > gendev_db | 1887538137 > (7 rows) > > dev_db=# > > The affected DB is 'dev_db', although it looks like the others except > 'gendev_db' have a wraparound problem too (?). To answer your question about > the VACUUM: We VACUUM FULL a few tables with a high amount of INSERT/DEL > operations once per hour, but I have to admit that we perform a VACUUM of the > whole DB not on a regulary basis. I think the last one was several monthes ago. > Further on we use transactions at several places and we have at least 20 > transactions per minute. > > Does now a normal VACUUM FULL of the whole DB(s) fix our problem? > > Michael > > > On May 11, 11:51am, Tom Lane wrote: > > Subject: Re: [BUGS] Missing tables in postgresql 7.2.4 > > "Michael Beckstette" <[EMAIL PROTECTED]> writes: > > > we recently discovered on our production database an a little bit bizarre > > > problem (after two years stable operations). Some tables are simply > missing, or > > > sometimes the affected table(s) is/are there but not listed in pg_tables. > > > > This sounds a bit like a transaction ID wraparound problem. Have you > > been vacuuming your whole database on a reasonable schedule? The > > missing tables might conceivably be old enough that their pg_class rows > > have wrapped around "into the future". It'd be useful to look at > > SELECT datname, age(datfrozenxid) FROM pg_database; > > > > regards, tom lane > > > > -- > > -- > Dipl.-Inform. Michael Beckstette Office: M3-129 > AG-PI / Technische Fakultaet EMail:[EMAIL PROTECTED] > Universitaet Bielefeld Fon: +49-521-106-2914 > Postfach 100131Fax: +49-521-106-6411 > D-33501 BIELEFELD > Germany > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq >-- End of excerpt from Michael Beckstette -- -- Dipl.-Inform. Michael Beckstette Office: M3-129 AG-PI / Technische Fakultaet EMail:[EMAIL PROTECTED] Universitaet Bielefeld Fon: +49-521-106-2914 Postfach 100131Fax: +49-521-106-6411 D-33501 BIELEFELD Germany ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1660: Growing used memory and critical performance loss
The following bug has been logged online: Bug reference: 1660 Logged by: Bogdan Matei Email address: [EMAIL PROTECTED] PostgreSQL version: 7.3.2 Operating system: Red Hat 9.0 (everything standard distribution) Description:Growing used memory and critical performance loss Details: Hello dear PostgreSQL community, As short presentation about me: my name is Bogdan Matei,a software engineer at INGC - a small company from Lausanne and from about 6 months i'm facing a very interesting, but weird problem. We have a standard Red Hat 9.0 Linux operating system, installed on Dell Power Edge 1600 SC - an Intel Xeon 2.8 GHz, RAID 1 with 7200 RPM hard disks speed,2 x 512MB dual channel memory and even all this server is dedicated to be used by a single application, some king of chat by SMS from time to time it delivers inacceptable performance. This is shown in simple queries that last very long time (sometimes more then 3 minutes) and a permanent growing used physical memory, until Postgresql reaches the linux swap when the speed fails almost to nothing and a large number of "postmaster" proceses are running in the same time. These are the efects. First of all we thought that is our application that consumes all the performance of Postgresql, with a predilection in the peak time of traffic, we improved it very well but even so there is no change. What is weird is that the only solution that we found is to reconfigure and recreate the database at every 2-3 days. When is imediately created the memory used is about 180MB then it constantly grows. When it comes to risc to enter the swap we give a reboot to the pc and the memory is now 400+MB, depending on the uptime. Even with these reboots (which we thought are cleaning the memory leaks) in about 3 days the memory used is about 800-1000MB and then we have to start over with a dump of the database (it is quite small, a few table of about 6000 records) and recreation of pg_data and database. Then is working good again, but with the same weird scenario. We studied lot of documentation, optimised the database, tried lots of configuration options and setups, make lots of analyzis but we reached nothing. Now i'm asking you if you have some ideas... For every information you need or/and even for access on our server you may contact me anytime and i hope you'll do:-(... I thank you very much and seek your opinions about this case, Bogdan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] Missing tables in postgresql 7.2.4
"Michael Beckstette" <[EMAIL PROTECTED]> writes: > The 6 completely lost tables are not so dramatical, because they contain only > static data, that I can restore from the development system. But what happens > with the 8 tables that are still accessable, but not listed in pg_tables, > after > a VACUUM? Will they be removed completely or 'reinserted' into pg_tables? > Does anyone has an advise how to proceed in this situation? What I would recommend as a first step is to stop the postmaster and then take a tarball backup of the entire $PGDATA tree. This will at least provide a chance to go back if subsequent tries mess things up completely. After that, I'd try a plain VACUUM (not FULL, not FREEZE) of pg_class and see if that restores the missing tables to view in pg_tables. If it does, go ahead and do a database-wide plain VACUUM, and you should be OK. If it doesn't, we'll need to think of another plan. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug Report with Postgres 7.4 on AIX 5.3
On 2005-05-11, Vincent Vanwynsberghe <[EMAIL PROTECTED]> wrote:
> The AIX 5.3 provide the structure sockaddr_storage :
>
> struct sockaddr_storage {
> ushort_t__ss_family;/* address family */
> char__ss_pad1[_SS_PAD1SIZE]; /* pad up to alignment
> field */
> #if defined(__64BIT__) || (defined(_ALL_SOURCE) && defined(_LONG_LONG))
> int64_t __ss_align; /* field to force desired structure
> */
> /* storage alignment */
> #else
> int __ss_align[2];
> #endif
> char__ss_pad2[_SS_PAD2SIZE];
> /* pad to achieve desired size */
> };
If the size of sockaddr_storage is less than the size of sockaddr_un
(or any sockaddr_* structure) then this is a bug in AIX, because the
Unix standards clearly specify that sockaddr_storage must be both sized
and aligned such that a sockaddr_* struct for any supported protocol
can be stored there.
See the entry for in the Headers chapter of the
Base Definitions volume of the SUSv3.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1660: Growing used memory and critical performance loss
On Wed, May 11, 2005 at 04:56:28PM +0100, Bogdan Matei wrote: > These are the efects. First of all we thought that is our application that > consumes all the performance of Postgresql, with a predilection in the peak > time of traffic, we improved it very well but even so there is no change. > What is weird is that the only solution that we found is to reconfigure and > recreate the database at every 2-3 days. When is imediately created the > memory used is about 180MB then it constantly grows. The question that jumps at me is, what is your vacuum strategy? -- Alvaro Herrera () "World domination is proceeding according to plan"(Andrew Morton) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Missing tables in postgresql 7.2.4
Hi, On May 11, 2:39pm, Tom Lane wrote: > What I would recommend as a first step is to stop the postmaster and > then take a tarball backup of the entire $PGDATA tree. This will at > least provide a chance to go back if subsequent tries mess things up > completely. Done. This was probably the biggest tar ball I have ever build (~450GB) ;) > > After that, I'd try a plain VACUUM (not FULL, not FREEZE) of pg_class > and see if that restores the missing tables to view in pg_tables. Done. The tables that were missing in pg_tables (but accessible with SELECT) are now listed in pg_tables. > If it does, go ahead and do a database-wide plain VACUUM, and you > should be OK. Done. As far as I can tell, everything is OK again. Thanks a lot Tom! P.S.:A TODO for me: CRON Script for weekly VACUUM ;) Michael -- -- Dipl.-Inform. Michael Beckstette Office: M3-129 AG-PI / Technische Fakultaet EMail:[EMAIL PROTECTED] Universitaet Bielefeld Fon: +49-521-106-2914 Postfach 100131Fax: +49-521-106-6411 D-33501 BIELEFELD Germany ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Missing tables in postgresql 7.2.4
P.S.:A TODO for me: CRON Script for weekly VACUUM ;) on heavy use databases, mine generally does a light vacuum every 4 hours, and a once a day full on everything. also, a weekly full reindex on a really really heavy use systems like this one message board server I ad-mangle something like... 7 */4 * * * vacuumdb busy_database >/dev/null 2>/dev/null 37 0 * * * vacuumdb -a -z >/dev/null 2>/dev/null 37 2 * * Sun reindexdb -a > /dev/null 2>/dev/null many might say this is total overkill, I dunno. keeps this server happy. btw, reindexdb is a script from contrib. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Missing tables in postgresql 7.2.4
"Michael Beckstette" <[EMAIL PROTECTED]> writes: > On May 11, 2:39pm, Tom Lane wrote: >> If it does, go ahead and do a database-wide plain VACUUM, and you >> should be OK. > Done. As far as I can tell, everything is OK again. Sweet ;-) In the words of my former business partner, a private pilot with more hours aloft than many airline captains: "Walked away from another one ..." For the benefit of onlookers, the gambit being played here went like this: the missing pg_class rows must have fairly recently wrapped around the 2G transaction mark with respect to the current XID counter. That made them "in the future" not "in the past" as far as normal queries go. However, a VACUUM will freeze-as-good any tuples that are "in the past" with respect to the vacuum freeze time, which for a plain VACUUM is 1G transactions ago. So as long as Michael notices he has a problem within 1 billion transactions of having a problem, he can get out of it. I cannot claim that this behavior was operating-as-designed, because I'm pretty sure we hadn't thought it through when planning the wraparound XID behavior. But we walked away from another one. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Do dropdb and createdb read password from .pgpass file?
Hi, I'm using the pg_dump and pg_restore client applications to implement our database backup-restore strategies. Wrt this, inorder to be able to autoamate the backup process (run it as a cron job), I'm using the .pgpass file for automating the password input. This is working for pg_dump. My restore script does a full restore - I first drop the existing database using dropdb, create a new database using createdb and then use pg_restore to restore the database from the backup file created (using pg_dump) I find that while pg_dump and pg_restore take/read the password from the .pgpass file, dropdb and createdb do not. For these, i'm still having to key in the password. The permissions for the .pgpass is 0600 as expected. Can anyone please let me know why this is happening? Thanks. -Anu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
