Re: Question about DB VACUUM - Mailing list pgsql-admin

BTW, the connection I shutdown, had not read, written or deleted any
large objects. It had read and written to other tables. This is causing
me concern as I am using a thread pool to provide access to the data in
the large object table, and this seems to imply I have to close each
connection after reading/writing or deleting a large object in order for
me to truly reclaim unused space when I issue my periodic vacuum
command.

Chris

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Chris White
(cjwhite)
Sent: Tuesday, October 07, 2003 11:09 PM
To: 'Tom Lane'
Cc: 'Robert Treat'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Question about DB VACUUM


Tom,

I have found that vacuum only truly gets back all the tuples when there
are no other connections to the database. I found I had a connection to
the database which was doing nothing, only when I dropped this
connection did the vacuum give back all the tuples and reduce the pages
to zero, until then pages grew:

Vacuum with another connection to db:
aesop=#  vacuum verbose pg_largeobject;
NOTICE:  --Relation pg_largeobject--
NOTICE:  Index pg_largeobject_loid_pn_index: Pages 2824; Tuples 231:
Deleted 670.
        CPU 0.23s/0.02u sec elapsed 0.25 sec.
NOTICE:  Removed 670 tuples in 40 pages.
        CPU 0.02s/0.01u sec elapsed 0.02 sec.
NOTICE:  Pages 40: Changed 40, Empty 0; Tup 231: Vac 670, Keep 0, UnUsed
0.
        Total CPU 0.25s/0.03u sec elapsed 0.30 sec.
VACUUM

Vacuum with no ther connection to db:
aesop=#  vacuum verbose pg_largeobject;
NOTICE:  --Relation pg_largeobject--
NOTICE:  Index pg_largeobject_loid_pn_index: Pages 2824; Tuples 0:
Deleted 231.
        CPU 0.21s/0.02u sec elapsed 0.23 sec.
NOTICE:  Removed 231 tuples in 16 pages.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Pages 40: Changed 16, Empty 0; Tup 0: Vac 231, Keep 0, UnUsed
670.
        Total CPU 0.21s/0.03u sec elapsed 0.24 sec.
NOTICE:  Truncated 40 --> 0 pages.
        CPU 0.00s/0.00u sec elapsed 0.01 sec.
VACUUM

Chris

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, October 06, 2003 9:24 PM
To: cjwhite@cisco.com
Cc: 'Robert Treat'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Question about DB VACUUM


"Chris White \(cjwhite\)" <cjwhite@cisco.com> writes:
> Okay now I understand what is going on. I have a second thread which
> is being used to read these objects out of the database to present to
> the user, and because large objects can only be accessed in a
> transaction mode I have not closed the transaction on this thread.
> Should I do a commit or rollback to terminate the transaction, once I
> have closed the large object, even though I have not done any
> modifications to the large objects?

Yes, if you need to reclaim the space from recently-deleted large
objects.  Under ordinary scenarios I'd not think that you have to be
real tense about this.  However, if your reader thread was in the habit
of holding open the same transaction for hours or days, that would be a
Bad Thing ...

            regards, tom lane


---------------------------(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