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

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

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

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