Thread: pg_attribute always grow...
Is there any reason why pg_attribute could always grow ( size in relpages... ) even if number of tuples don't grow... The indexes pg_attribute_relid_attnam_index and pg_attribute_relid_attnum_index grow and I don't see any way to vacuum them without restarting postgres with -O and -P options and launching REINDEX command. Could it be due to the fact that I create and drop many temporary tables in stored procedures ? Please help!!!! Thanks Patrick Fiche email : patrick.fiche@aqsacom.com tél : 01 69 29 36 18
Patrick Fiche wrote: > Is there any reason why pg_attribute could always grow ( size in > relpages... ) even if number of tuples don't grow... > The indexes pg_attribute_relid_attnam_index and > pg_attribute_relid_attnum_index grow and I don't see any way to vacuum them > without restarting postgres with -O and -P options and launching REINDEX > command. > Could it be due to the fact that I create and drop many temporary tables in > stored procedures ? Yes, it could very well be that. I am quite embarassed by our need to reindex. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Will the same occur if I create real tables instead of temporary tables as for most of them, there is no concurrent access and no need to have the same table in two different connexions... What's the real performance difference between a temporary table and other tables... Thanks Patrick Fiche email : patrick.fiche@aqsacom.com tél : 01 69 29 36 18 -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruce Momjian Sent: Thursday, October 17, 2002 8:58 PM To: Patrick FICHE Cc: PostgreSQL - General (E-mail) Subject: Re: [GENERAL] pg_attribute always grow... Patrick Fiche wrote: > Is there any reason why pg_attribute could always grow ( size in > relpages... ) even if number of tuples don't grow... > The indexes pg_attribute_relid_attnam_index and > pg_attribute_relid_attnum_index grow and I don't see any way to vacuum them > without restarting postgres with -O and -P options and launching REINDEX > command. > Could it be due to the fact that I create and drop many temporary tables in > stored procedures ? Yes, it could very well be that. I am quite embarassed by our need to reindex. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
Sorry, real and temporary tables are going to have the same problems. --------------------------------------------------------------------------- Patrick Fiche wrote: > Will the same occur if I create real tables instead of temporary tables as > for most of them, there is no concurrent access and no need to have the same > table in two different connexions... > What's the real performance difference between a temporary table and other > tables... > > Thanks > > Patrick Fiche > email : patrick.fiche@aqsacom.com > t?l : 01 69 29 36 18 > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruce Momjian > Sent: Thursday, October 17, 2002 8:58 PM > To: Patrick FICHE > Cc: PostgreSQL - General (E-mail) > Subject: Re: [GENERAL] pg_attribute always grow... > > > Patrick Fiche wrote: > > Is there any reason why pg_attribute could always grow ( size in > > relpages... ) even if number of tuples don't grow... > > The indexes pg_attribute_relid_attnam_index and > > pg_attribute_relid_attnum_index grow and I don't see any way to vacuum > them > > without restarting postgres with -O and -P options and launching REINDEX > > command. > > Could it be due to the fact that I create and drop many temporary tables > in > > stored procedures ? > > Yes, it could very well be that. I am quite embarassed by our need to > reindex. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
I am having the same problems. I too do a lot of creation and dropping of temp tables from within functions. Should I reindex every night? ----- Original Message ----- From: "Bruce Momjian" <pgman@candle.pha.pa.us> To: <patrick.fiche@aqsacom.com> Cc: "PostgreSQL - General (E-mail)" <pgsql-general@postgresql.org> Sent: Friday, October 18, 2002 4:58 AM Subject: Re: [GENERAL] pg_attribute always grow... > Patrick Fiche wrote: > > Is there any reason why pg_attribute could always grow ( size in > > relpages... ) even if number of tuples don't grow... > > The indexes pg_attribute_relid_attnam_index and > > pg_attribute_relid_attnum_index grow and I don't see any way to vacuum them > > without restarting postgres with -O and -P options and launching REINDEX > > command. > > Could it be due to the fact that I create and drop many temporary tables in > > stored procedures ? > > Yes, it could very well be that. I am quite embarassed by our need to > reindex. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
Is there any plan in the near future to change this ? -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruce Momjian Sent: Friday, October 18, 2002 5:42 PM To: Patrick FICHE Cc: PostgreSQL - General (E-mail) Subject: Re: [GENERAL] pg_attribute always grow... Sorry, real and temporary tables are going to have the same problems. --------------------------------------------------------------------------- Patrick Fiche wrote: > Will the same occur if I create real tables instead of temporary tables as > for most of them, there is no concurrent access and no need to have the same > table in two different connexions... > What's the real performance difference between a temporary table and other > tables... > > Thanks > > Patrick Fiche > email : patrick.fiche@aqsacom.com > t?l : 01 69 29 36 18 > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruce Momjian > Sent: Thursday, October 17, 2002 8:58 PM > To: Patrick FICHE > Cc: PostgreSQL - General (E-mail) > Subject: Re: [GENERAL] pg_attribute always grow... > > > Patrick Fiche wrote: > > Is there any reason why pg_attribute could always grow ( size in > > relpages... ) even if number of tuples don't grow... > > The indexes pg_attribute_relid_attnam_index and > > pg_attribute_relid_attnum_index grow and I don't see any way to vacuum > them > > without restarting postgres with -O and -P options and launching REINDEX > > command. > > Could it be due to the fact that I create and drop many temporary tables > in > > stored procedures ? > > Yes, it could very well be that. I am quite embarassed by our need to > reindex. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Mon, Oct 21, 2002 at 11:58:56AM +0200, Patrick Fiche wrote: > Is there any plan in the near future to change this ? Yes, I'm working on this. -- Alvaro Herrera (<alvherre[a]atentus.com>) "La victoria es para quien se atreve a estar solo"
That's good news... Do you have any approximative delay ? -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Alvaro Herrera Sent: Monday, October 21, 2002 5:16 PM To: Patrick FICHE Cc: 'PostgreSQL - General (E-mail)' Subject: Re: [GENERAL] pg_attribute always grow... On Mon, Oct 21, 2002 at 11:58:56AM +0200, Patrick Fiche wrote: > Is there any plan in the near future to change this ? Yes, I'm working on this. -- Alvaro Herrera (<alvherre[a]atentus.com>) "La victoria es para quien se atreve a estar solo" ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly