Thread: vacuuming not working?
Me again,
I have a problem with VACUUM. I searched the list and don't seem to see
anyone else reporting this behavior so I'm wondering if i'm doing something
wrong
Summary: Vacuuming does not seem to mark the space as "available for reuse"
as the documentation says.
Platform: Postgres 7.2.1 on RedHat 7.0
I have a table that I use to keep aggregate statistics for a website ...
This table is cleared and repopulated (DELETE FROM followed by a INSERT INTO
.. SELECT) periodically ... The table has ~500 records in it ...
The problem is that the file on disk is over 500 megs! and it appears to be
growing pretty much without bound ... I do a VACUUM every night (not a
VACUUM FULL, just a straight VACUUM) and it still grows .... Needless to
say, this is killing the performance of my application since the query to
delete the 500 rows is now taking over 2 minutes ...
I setup a sample script (with a bogus table with one field) to demonstrate
the problem ... It goes through and inserts 500 rows and then deletes the
500 rows ... every 10th iteration, it vacuums the table ... to run this
script, you'll need to have a table in your DB with over 500 rows
(BIG_TABLE) in it and you'll need to modify the RELFILENAME to get the
on-disk file size ...
Also, even issuing a VACUUM FULL on the table doesn't seem to reduce the
size of the file on disk (this worked in 7.1.3 for sure)
CREATE TABLE test_high_turnover(test_string VARCHAR(1000) NOT NULL);
Listing of test_high_turnover.sh
#!/bin/sh
PG_HOME=/usr/local/pgsql/bin
BIG_TABLE=<name of a big table in your database>
DB_NAME=<name of your database>
RELFILENAME=/data/pgdata/base/16566/3179174
I=0
while(( $I < $1 ))
do
let I+=+1
echo "Iteration #$I -- Relation file size: " `ls -s --format=s $RELFILENAME
| awk {'print $1'}`"KB"
$PG_HOME/psql -c "INSERT INTO test_high_turnover SELECT current_timestamp
|| ' ' || VERSION() FROM $BIG_TABLE LIMIT 500;" $DB_NAME
$PG_HOME/psql -c "DELETE FROM test_high_turnover; " $DB_NAME
if ((($I%10) == 0)) ; then
echo "Vacuuming ... "
$PG_HOME/psql -c "VACUUM VERBOSE ANALYZE test_high_turnover;" $DB_NAME
fi
sleep 1
done
Here's a sample run from my database
[postgres@TIMDA1 postgres]$ ./test_high_turnover.sh 500
Iteration #1 -- Relation file size: 4988KB
INSERT 0 500
DELETE 500
Iteration #2 -- Relation file size: 5052KB
INSERT 0 500
DELETE 500
Iteration #3 -- Relation file size: 5116KB
INSERT 0 500
DELETE 500
Iteration #4 -- Relation file size: 5180KB
INSERT 0 500
DELETE 500
Iteration #5 -- Relation file size: 5244KB
INSERT 0 500
DELETE 500
Iteration #6 -- Relation file size: 5316KB
INSERT 0 500
DELETE 500
Iteration #7 -- Relation file size: 5380KB
INSERT 0 500
DELETE 500
Iteration #8 -- Relation file size: 5444KB
INSERT 0 500
DELETE 500
Iteration #9 -- Relation file size: 5508KB
INSERT 0 500
[postgres@TIMDA1 postgres]$ psql patronmail
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
patronmail=# drop table test_high_turnover ;
DROP
patronmail=# CREATE TABLE test_high_turnover(test_string VARCHAR(1000) NOT
NULL);
CREATE
patronmail=# \q
[postgres@TIMDA1 postgres]$ pico test_high_turnover.sh
[postgres@TIMDA1 postgres]$ ./test_high_turnover.sh 500
Iteration #1 -- Relation file size: 0KB
INSERT 0 500
DELETE 500
Iteration #2 -- Relation file size: 76KB
INSERT 0 500
DELETE 500
Iteration #3 -- Relation file size: 140KB
INSERT 0 500
DELETE 500
Iteration #4 -- Relation file size: 204KB
INSERT 0 500
DELETE 500
Iteration #5 -- Relation file size: 268KB
INSERT 0 500
DELETE 500
Iteration #6 -- Relation file size: 332KB
INSERT 0 500
DELETE 500
Iteration #7 -- Relation file size: 404KB
INSERT 0 500
DELETE 500
Iteration #8 -- Relation file size: 468KB
INSERT 0 500
DELETE 500
Iteration #9 -- Relation file size: 532KB
INSERT 0 500
DELETE 500
Iteration #10 -- Relation file size: 596KB
INSERT 0 500
DELETE 500
Vacuuming ...
NOTICE: --Relation test_high_turnover--
NOTICE: Pages 82: Changed 9, Empty 0; Tup 5000: Vac 0, Keep 5000, UnUsed 0.
Total CPU 0.00s/0.01u sec elapsed 0.00 sec.
NOTICE: Analyzing test_high_turnover
VACUUM
Iteration #11 -- Relation file size: 660KB
INSERT 0 500
DELETE 500
Iteration #12 -- Relation file size: 732KB
INSERT 0 500
DELETE 500
Iteration #13 -- Relation file size: 796KB
INSERT 0 500
DELETE 500
Iteration #14 -- Relation file size: 860KB
INSERT 0 500
DELETE 500
Iteration #15 -- Relation file size: 924KB
INSERT 0 500
DELETE 500
Iteration #16 -- Relation file size: 988KB
INSERT 0 500
DELETE 500
Iteration #17 -- Relation file size: 1052KB
INSERT 0 500
DELETE 500
Iteration #18 -- Relation file size: 1124KB
INSERT 0 500
DELETE 500
Iteration #19 -- Relation file size: 1188KB
INSERT 0 500
DELETE 500
Iteration #20 -- Relation file size: 1252KB
INSERT 0 500
DELETE 500
Vacuuming ...
NOTICE: --Relation test_high_turnover--
NOTICE: Pages 164: Changed 9, Empty 0; Tup 10000: Vac 0, Keep 10000, UnUsed
0.
Total CPU 0.00s/0.01u sec elapsed 0.00 sec.
NOTICE: Analyzing test_high_turnover
VACUUM
Iteration #21 -- Relation file size: 1316KB
INSERT 0 500
DELETE 500
Iteration #22 -- Relation file size: 1380KB
INSERT 0 500
DELETE 500
Iteration #23 -- Relation file size: 1452KB
INSERT 0 500
DELETE 500
<SNIP ... you get the idea ... >
Iteration #74 -- Relation file size: 4788KB
INSERT 0 500
DELETE 500
Iteration #75 -- Relation file size: 4860KB
INSERT 0 500
DELETE 500
Iteration #76 -- Relation file size: 4924KB
INSERT 0 500
DELETE 500
Iteration #77 -- Relation file size: 4988KB
INSERT 0 500
DELETE 500
Iteration #78 -- Relation file size: 5052KB
INSERT 0 500
DELETE 500
Iteration #79 -- Relation file size: 5116KB
INSERT 0 500
DELETE 500
Iteration #80 -- Relation file size: 5188KB
INSERT 0 500
DELETE 500
Vacuuming ...
NOTICE: --Relation test_high_turnover--
NOTICE: Pages 655: Changed 9, Empty 0; Tup 40000: Vac 0, Keep 40000, UnUsed
0.
Total CPU 0.03s/0.03u sec elapsed 0.06 sec.
NOTICE: Analyzing test_high_turnover
VACUUM
Iteration #81 -- Relation file size: 5252KB
INSERT 0 500
DELETE 500
Iteration #82 -- Relation file size: 5316KB
INSERT 0 500
DELETE 500
Iteration #83 -- Relation file size: 5380KB
INSERT 0 500
DELETE 500
Iteration #84 -- Relation file size: 5444KB
INSERT 0 500
DELETE 500
Iteration #85 -- Relation file size: 5516KB
INSERT 0 500
DELETE 500
Iteration #86 -- Relation file size: 5580KB
INSERT 0 500
DELETE 500
What else should I be trying here?
-Dave
On Mon, 2002-04-08 at 10:40, David Esposito wrote:
> Me again,
>
> I have a problem with VACUUM. I searched the list and don't seem to see
> anyone else reporting this behavior so I'm wondering if i'm doing something
> wrong
> NOTICE: Pages 655: Changed 9, Empty 0; Tup 40000: Vac 0, Keep 40000, UnUsed
^^^^^^^^^^
The "Keep" number in VACUUM means some transaction can still see those
rows. Check for long-lived transactions hanging around.
That's why I isolated it down to a standalone example ... There is no other process looking at that table ... no foreign keys or other things that could cause those records to be "in use" ... That was the first thing that ran through my mind when my application was starting to get really slow ... Also, you'll see from my script that I exit and re-enter psql between each operation (INSERT, DELETE, VACUUM) so if the transaction wasn't committing, then the rows wouldn't exist the next time I entered ... Is there a query I can run to see who is holding those tuples which is causing VACUUM to leave them in place? -dave > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jeffrey W. Baker > Sent: Monday, April 08, 2002 2:45 PM > To: David Esposito > Cc: Postgres general mailing list > Subject: Re: [GENERAL] vacuuming not working? > > > On Mon, 2002-04-08 at 10:40, David Esposito wrote: > > Me again, > > > > I have a problem with VACUUM. I searched the list and don't seem to see > > anyone else reporting this behavior so I'm wondering if i'm > doing something > > wrong > > > NOTICE: Pages 655: Changed 9, Empty 0; Tup 40000: Vac 0, Keep > 40000, UnUsed > ^^^^^^^^^^ > > The "Keep" number in VACUUM means some transaction can still see those > rows. Check for long-lived transactions hanging around. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Is there some sort of time delay involved with VACUUM that I should be aware of? I ran a VACUUM FULL on the table that I had run the test on this morning and sure enough, it shrunk down to zero size (there were no rows in the table) but then I reran the test and then ran VACUUM FULL and it DID NOT shrink the file on disk ... Is there some sort of cache or something that's never getting timed out (the out of control 500 meg table file i made mention of, gets dumped and repopulated every 2 minutes ... is there something that's never getting removed from a LRU cache?) Here's my VACUUM command ... > -----Original Message----- > From: Andrew Sullivan [mailto:andrew@libertyrms.com]On Behalf Of Andrew > Sullivan > Sent: Monday, April 08, 2002 2:55 PM > To: David Esposito > Subject: Re: [GENERAL] vacuuming not working? > > > On Mon, Apr 08, 2002 at 01:40:48PM -0400, David Esposito wrote: > > Me again, > > > > I have a problem with VACUUM. I searched the list and don't seem to see > > anyone else reporting this behavior so I'm wondering if i'm > doing something > > wrong > > > > Summary: Vacuuming does not seem to mark the space as > "available for reuse" > > as the documentation says. > > You haven't read the documentation carefully enough, or else you're > reading old docs. > > > Platform: Postgres 7.2.1 on RedHat 7.0 > ^ > > The problem is that the file on disk is over 500 megs! and it > appears to be > > growing pretty much without bound ... I do a VACUUM every night (not a > > VACUUM FULL, just a straight VACUUM) > ^^^^^^^^^^^ > > Only the VACUUM FULL makes the disk space available for reuse (by > other programs; postgres will be able to reuse some of the space with > lazy VACUUM). > > A > > -- > ---- > Andrew Sullivan 87 Mowat Avenue > Liberty RMS Toronto, Ontario Canada > <andrew@libertyrms.info> M6K 3E3 > +1 416 646 3304 x110 >
damnit ... i hit send too soon .. ;)
blahdb=# vacuum full verbose analyze test_high_turnover;
NOTICE: --Relation test_high_turnover--
NOTICE: Pages 1061: Changed 0, reaped 101, Empty 0, New 0; Tup 60000: Vac
0, Keep/VTL 60000/0, UnUsed 5013, MinLen 123, MaxLen 125; Re-using:
Free/Avail. Space 756440/641816; EndEmpty/Avail. Pages 0/84.
CPU 0.09s/0.02u sec elapsed 0.10 sec.
NOTICE: Rel test_high_turnover: Pages: 1061 --> 981; Tuple(s) moved: 4989.
CPU 0.05s/0.04u sec elapsed 0.17 sec.
NOTICE: Analyzing test_high_turnover
VACUUM
> -----Original Message-----
> From: David Esposito [mailto:dvesposito@newnetco.com]
> Sent: Monday, April 08, 2002 3:09 PM
> To: Andrew Sullivan
> Cc: pgsql-general@postgresql.org
> Subject: RE: [GENERAL] vacuuming not working?
>
>
> Is there some sort of time delay involved with VACUUM that I
> should be aware of? I ran a VACUUM FULL on the table that I had
> run the test on this morning and sure enough, it shrunk down to
> zero size (there were no rows in the table) but then I reran the
> test and then ran VACUUM FULL and it DID NOT shrink the file on disk ...
>
> Is there some sort of cache or something that's never getting
> timed out (the out of control 500 meg table file i made mention
> of, gets dumped and repopulated every 2 minutes ... is there
> something that's never getting removed from a LRU cache?)
>
> Here's my VACUUM command ...
>
>
> > -----Original Message-----
> > From: Andrew Sullivan [mailto:andrew@libertyrms.com]On Behalf Of Andrew
> > Sullivan
> > Sent: Monday, April 08, 2002 2:55 PM
> > To: David Esposito
> > Subject: Re: [GENERAL] vacuuming not working?
> >
> >
> > On Mon, Apr 08, 2002 at 01:40:48PM -0400, David Esposito wrote:
> > > Me again,
> > >
> > > I have a problem with VACUUM. I searched the list and don't
> seem to see
> > > anyone else reporting this behavior so I'm wondering if i'm
> > doing something
> > > wrong
> > >
> > > Summary: Vacuuming does not seem to mark the space as
> > "available for reuse"
> > > as the documentation says.
> >
> > You haven't read the documentation carefully enough, or else you're
> > reading old docs.
> >
> > > Platform: Postgres 7.2.1 on RedHat 7.0
> > ^
> > > The problem is that the file on disk is over 500 megs! and it
> > appears to be
> > > growing pretty much without bound ... I do a VACUUM every night (not a
> > > VACUUM FULL, just a straight VACUUM)
> > ^^^^^^^^^^^
> >
> > Only the VACUUM FULL makes the disk space available for reuse (by
> > other programs; postgres will be able to reuse some of the space with
> > lazy VACUUM).
> >
> > A
> >
> > --
> > ----
> > Andrew Sullivan 87 Mowat Avenue
> > Liberty RMS Toronto, Ontario Canada
> > <andrew@libertyrms.info> M6K 3E3
> > +1 416 646 3304 x110
> >
"David Esposito" <dvesposito@newnetco.com> writes:
> That's why I isolated it down to a standalone example ... There is no other
> process looking at that table ... no foreign keys or other things that could
> cause those records to be "in use" ...
Whether there *is* something looking at that table is not the issue.
The issue is whether there is an open transaction old enough that if
it chose to look at the table, it would see now-deleted rows. If so,
VACUUM can't remove those rows, since it doesn't have any way to know
whether the old transaction will later choose to look.
Given your later message, I suspect there was such an open transaction
and it exited. There is not any really good way to look for this
situation, although the pg_stat_activity view (in 7.2) is better than
nothing. Perhaps in future releases, we should add columns to
pg_stat_activity that would indicate how old each backend's open
transaction is.
regards, tom lane
i took a peek at pg_stat_activity and it doesn't appear as though any of the processes are doing anything ... they all have blanks for "current_query" ... (when i'm vacuuming and when i'm running the populate/clear queries) Is there any way to get timestamps turned on in the log file? Is there a chance that this "defunct" transaction is cleaned when the transaction logs rotate? I see this in my log file but i haven't got the foggiest idea when it happened ... ;) ... and whether it resulted in me being able to vacuum the table successfully ... DEBUG: recycled transaction log file 0000000100000039 > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Monday, April 08, 2002 3:38 PM > To: David Esposito > Cc: Jeffrey W. Baker; Postgres general mailing list > Subject: Re: [GENERAL] vacuuming not working? > > > "David Esposito" <dvesposito@newnetco.com> writes: > > That's why I isolated it down to a standalone example ... There > is no other > > process looking at that table ... no foreign keys or other > things that could > > cause those records to be "in use" ... > > Whether there *is* something looking at that table is not the issue. > The issue is whether there is an open transaction old enough that if > it chose to look at the table, it would see now-deleted rows. If so, > VACUUM can't remove those rows, since it doesn't have any way to know > whether the old transaction will later choose to look. > > Given your later message, I suspect there was such an open transaction > and it exited. There is not any really good way to look for this > situation, although the pg_stat_activity view (in 7.2) is better than > nothing. Perhaps in future releases, we should add columns to > pg_stat_activity that would indicate how old each backend's open > transaction is. > > regards, tom lane
"David Esposito" <dvesposito@newnetco.com> writes: > i took a peek at pg_stat_activity and it doesn't appear as though any of the > processes are doing anything ... they all have blanks for "current_query" Read http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/monitoring-stats.html > Is there any way to get timestamps turned on in the log file? There's a flag in postgresql.conf. You might wanna turn on log_pid too. regards, tom lane
You can also do a ps -x to see current connections and whether they are performing a query, idle or idle in transaction. Greg ----- Original Message ----- From: "David Esposito" <dvesposito@newnetco.com> To: "Tom Lane" <tgl@sss.pgh.pa.us> Cc: "Jeffrey W. Baker" <jwbaker@acm.org>; "Postgres general mailing list" <pgsql-general@postgresql.org> Sent: Monday, April 08, 2002 3:47 PM Subject: Re: [GENERAL] vacuuming not working? > i took a peek at pg_stat_activity and it doesn't appear as though any of the > processes are doing anything ... they all have blanks for "current_query" > ... (when i'm vacuuming and when i'm running the populate/clear queries) > > Is there any way to get timestamps turned on in the log file? Is there a > chance that this "defunct" transaction is cleaned when the transaction logs > rotate? I see this in my log file but i haven't got the foggiest idea when > it happened ... ;) ... and whether it resulted in me being able to vacuum > the table successfully ... > > DEBUG: recycled transaction log file 0000000100000039 > > > > > -----Original Message----- > > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > Sent: Monday, April 08, 2002 3:38 PM > > To: David Esposito > > Cc: Jeffrey W. Baker; Postgres general mailing list > > Subject: Re: [GENERAL] vacuuming not working? > > > > > > "David Esposito" <dvesposito@newnetco.com> writes: > > > That's why I isolated it down to a standalone example ... There > > is no other > > > process looking at that table ... no foreign keys or other > > things that could > > > cause those records to be "in use" ... > > > > Whether there *is* something looking at that table is not the issue. > > The issue is whether there is an open transaction old enough that if > > it chose to look at the table, it would see now-deleted rows. If so, > > VACUUM can't remove those rows, since it doesn't have any way to know > > whether the old transaction will later choose to look. > > > > Given your later message, I suspect there was such an open transaction > > and it exited. There is not any really good way to look for this > > situation, although the pg_stat_activity view (in 7.2) is better than > > nothing. Perhaps in future releases, we should add columns to > > pg_stat_activity that would indicate how old each backend's open > > transaction is. > > > > regards, tom lane > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Ok, some further experimentation has proven that this problem does not manifest itself if there are ZERO connections to the database (aside from the actual connection doing the insert/delete/vacuum) ... The vacuum command correctly reclaims the space and the on-disk file never grows beyond a certain size ... So, here's the million dollar question ... and one that may or may not be a postgres question ... I normally have a pool of connections connected to postgres from my application server (jboss) ... they are all normally in the "idle in transaction" state (if i do a ps -ax, they are all listed as "idle in transaction") ... the question is, are these other connections actually holding open a transaction that would prevent the table from being properly vacuumed? if so, is there anything that i can do about it ... (yes, i suppose i need to figure out why the JDBC connections have transactions started before they're actually needed) ... and was this the case with postgres 7.1.3? but i figured i'd run it by the experts too ... ;) -dave > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane > Sent: Monday, April 08, 2002 3:53 PM > To: David Esposito > Cc: Jeffrey W. Baker; Postgres general mailing list > Subject: Re: [GENERAL] vacuuming not working? > > > "David Esposito" <dvesposito@newnetco.com> writes: > > i took a peek at pg_stat_activity and it doesn't appear as > though any of the > > processes are doing anything ... they all have blanks for > "current_query" > > Read > http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/monito > ring-stats.html > > > Is there any way to get timestamps turned on in the log file? > > There's a flag in postgresql.conf. You might wanna turn on log_pid too. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
"David Esposito" <dvesposito@newnetco.com> writes:
> So, here's the million dollar question ... and one that may or may not be a
> postgres question ... I normally have a pool of connections connected to
> postgres from my application server (jboss) ... they are all normally in the
> "idle in transaction" state (if i do a ps -ax, they are all listed as "idle
> in transaction") ... the question is, are these other connections actually
> holding open a transaction that would prevent the table from being properly
> vacuumed?
Yes. If the ps status display says it's in a transaction, then it is.
> if so, is there anything that i can do about it ... (yes, i
> suppose i need to figure out why the JDBC connections have transactions
> started before they're actually needed) ...
Darn if I know. You might try asking pgsql-jdbc list.
> and was this the case with postgres 7.1.3?
VACUUM has always acted that way, as far back as I've been involved.
regards, tom lane
On Mon, Apr 08, 2002 at 05:35:58PM -0400, Tom Lane wrote:
> "David Esposito" <dvesposito@newnetco.com> writes:
> > if so, is there anything that i can do about it ... (yes, i
> > suppose i need to figure out why the JDBC connections have transactions
> > started before they're actually needed) ...
>
> Darn if I know. You might try asking pgsql-jdbc list.
I _believe_ this is a feature of the autocommit=off setting. I think
you can turn autocommit on and start and end your transactions
yourself. Check the docs, though -- I've been shown to have faulty
memory.
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
> -----Original Message----- > From: Andrew Sullivan [mailto:andrew@libertyrms.com]On Behalf Of Andrew > Sullivan > Sent: Tuesday, April 09, 2002 3:39 PM > To: David Esposito > Subject: Re: [GENERAL] vacuuming not working? > > > On Mon, Apr 08, 2002 at 05:32:44PM -0400, David Esposito wrote: > > > So, here's the million dollar question ... and one that may or > may not be a > > postgres question ... I normally have a pool of connections connected to > > postgres from my application server (jboss) ... they are all > normally in the > > "idle in transaction" state (if i do a ps -ax, they are all > listed as "idle > > in transaction") > > That's your problem. > > > ... the question is, are these other connections actually > > holding open a transaction that would prevent the table from > being properly > > vacuumed? > > It _is_ being properly vacuumed. Nothing that was in the table at > the time the transaction started can be removed (because the idle > transaction _could_ be looking for it). > disclaimer: i've never looked at the source code and, unfortunately, do not have the time to do so ... so please, don't flog me ... but it seems as though the VACCUMING process is intended to bring the physical data storage in sync with the logical data storage ... that is, it removes pages on the disk that are no longer used by the database to store data ... so then, why would vacuum give a damn about other transactions that might be in progress? if everyone is using SERIALIZABLE for their isolation level, i could see why this might be necessary ... but since all of my transactions are using READ_COMMITTED, it shouldn't matter if i do a logical delete (DELETE FROM) followed by a vacuum (physical cleanup of the unused pages) as far as the other transactions are concerned ... there is no guarantee made to those transactions that the rows in my table will still exist when they attempt to query them ... (SELECT, UPDATE, DELETE, etc...) am I missing something here? ... -dave
It's not a fault of the JDBC driver, you're right that it won't hold open a transaction ... it's with my application server ... which, i unfortunately don't have control over ... for some reason it seems to keep all of its connections in an open transaction ... the really mysterious part is the way that it has always been ... and prior to my upgrade to 7.2.1, i ran a vacuum and a reindex every night and the space was reclaimed ... that's what brought this whole thing on ... something has changed ... -dave > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Andrew Sullivan > Sent: Tuesday, April 09, 2002 3:41 PM > To: PostgreSQL general list > Subject: Re: [GENERAL] vacuuming not working? > > > On Mon, Apr 08, 2002 at 05:35:58PM -0400, Tom Lane wrote: > > "David Esposito" <dvesposito@newnetco.com> writes: > > > > if so, is there anything that i can do about it ... (yes, i > > > suppose i need to figure out why the JDBC connections have > transactions > > > started before they're actually needed) ... > > > > Darn if I know. You might try asking pgsql-jdbc list. > > I _believe_ this is a feature of the autocommit=off setting. I think > you can turn autocommit on and start and end your transactions > yourself. Check the docs, though -- I've been shown to have faulty > memory. > > A > > -- > ---- > Andrew Sullivan 87 Mowat Avenue > Liberty RMS Toronto, Ontario Canada > <andrew@libertyrms.info> M6K 3E3 > +1 416 646 3304 x110 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Hi, 1) Does your app server rollback/commit first then open a new transaction and go idle? 2) Or does it rollback/commit then go idle. Then only begin when there's something to do. 3) Or it goes idle after selects etc without rollback/commit. As far as I know, 7.1.x vacuum in another connection will block for 3) but not 1). I haven't tested 7.2.1 yet, but you can test 1) by opening up two psql windows and doing a begin; select * from table; rollback; begin; and then try vacuum in another. You can do similar tests for the other situations. How do you do your vacuum and reindex? Good luck! Link. At 04:19 PM 4/9/02 -0400, David Esposito wrote: >It's not a fault of the JDBC driver, you're right that it won't hold open a >transaction ... it's with my application server ... which, i unfortunately >don't have control over ... for some reason it seems to keep all of its >connections in an open transaction ... > >the really mysterious part is the way that it has always been ... and prior >to my upgrade to 7.2.1, i ran a vacuum and a reindex every night and the >space was reclaimed ... that's what brought this whole thing on ... >something has changed ... > >-dave