Re: [HACKERS] vacuumlo - use a cursor

On Sun, Jul 7, 2013 at 9:30 AM, Josh Kupershmidt wrote: > On Mon, Nov 12, 2012 at 5:14 PM, Andrew Dunstan wrote: >> vacuumlo is rather simpleminded about dealing with the list of LOs to be >> removed - it just fetches them as a straight resultset. For one of my our >> this resulted in an out of m

Re: [HACKERS] vacuumlo - use a cursor

On Mon, Nov 12, 2012 at 5:14 PM, Andrew Dunstan wrote: > vacuumlo is rather simpleminded about dealing with the list of LOs to be > removed - it just fetches them as a straight resultset. For one of my our > this resulted in an out of memory condition. Wow, they must have had a ton of LOs. With a

Re: [HACKERS] vacuumlo - use a cursor

On Sat, Jun 29, 2013 at 12:21:11PM -0400, Andrew Dunstan wrote: > > On 06/29/2013 11:35 AM, Bruce Momjian wrote: > >On Sat, Jun 29, 2013 at 11:33:54AM -0400, Andrew Dunstan wrote: > >>Nobody seemed interested. But I do think it's a good idea still. > >Well, if no one replied, and you thought it wa

Re: [HACKERS] vacuumlo - use a cursor

On 06/29/2013 11:35 AM, Bruce Momjian wrote: On Sat, Jun 29, 2013 at 11:33:54AM -0400, Andrew Dunstan wrote: Nobody seemed interested. But I do think it's a good idea still. Well, if no one replied, and you thought it was a good idea, then it was a good idea. ;-) I try not to assume that

Re: [HACKERS] vacuumlo - use a cursor

On 06/29/2013 08:35 AM, Bruce Momjian wrote: On Sat, Jun 29, 2013 at 11:33:54AM -0400, Andrew Dunstan wrote: Nobody seemed interested. But I do think it's a good idea still. Well, if no one replied, and you thought it was a good idea, then it was a good idea. ;-) I think it is a good id

Re: [HACKERS] vacuumlo - use a cursor

On Sat, Jun 29, 2013 at 11:33:54AM -0400, Andrew Dunstan wrote: > > Nobody seemed interested. But I do think it's a good idea still. Well, if no one replied, and you thought it was a good idea, then it was a good idea. ;-) -- Bruce Momjian http://momjian.us EnterpriseDB

Re: [HACKERS] vacuumlo - use a cursor

Nobody seemed interested. But I do think it's a good idea still. cheers andrew On 06/29/2013 11:23 AM, Bruce Momjian wrote: Is there a reason this patch was not applied? --- On Mon, Nov 12, 2012 at 05:14:57PM -0500, A

Re: [HACKERS] vacuumlo - use a cursor

Is there a reason this patch was not applied? --- On Mon, Nov 12, 2012 at 05:14:57PM -0500, Andrew Dunstan wrote: > vacuumlo is rather simpleminded about dealing with the list of LOs > to be removed - it just fetches them as

[HACKERS] vacuumlo - use a cursor

vacuumlo is rather simpleminded about dealing with the list of LOs to be removed - it just fetches them as a straight resultset. For one of my our this resulted in an out of memory condition. The attached patch tries to remedy that by using a cursor instead. If this is wanted I will add it to t

Re: [HACKERS] vacuumlo issue

Robert Haas writes: > On Tue, Mar 20, 2012 at 11:50 AM, Tom Lane wrote: >> I see that patch has not made it to any released versions yet. >> Is it too late to rethink the design? I propose (a) redefining it >> as committing after every N objects, and (b) having a limit of 1000 >> or so objects b

Re: [HACKERS] vacuumlo issue

> > I think you are asking for this option: > > -l LIMIT stop after removing LIMIT large objects > > which was added in b69f2e36402aaa. Thank you for informing about -l option in 9.2. Can I build/use this contrib with older pg versions i.e. pg 9.1 ? . Thanks. > Uh, no, actually that flag s

Re: [HACKERS] vacuumlo issue

On Tue, Mar 20, 2012 at 11:50 AM, Tom Lane wrote: > Josh Kupershmidt writes: >> On Tue, Mar 20, 2012 at 7:53 AM, Tom Lane wrote: >>> I'm not entirely convinced that that was a good idea. However, so far >>> as vacuumlo is concerned, the only reason this is a problem is that >>> vacuumlo goes out

Re: [HACKERS] vacuumlo issue

Josh Kupershmidt writes: > On Tue, Mar 20, 2012 at 7:53 AM, Tom Lane wrote: >> I'm not entirely convinced that that was a good idea. However, so far >> as vacuumlo is concerned, the only reason this is a problem is that >> vacuumlo goes out of its way to do all the large-object deletions in a >>

Re: [HACKERS] vacuumlo issue

On Tue, Mar 20, 2012 at 7:53 AM, Tom Lane wrote: > I'm not entirely convinced that that was a good idea.  However, so far > as vacuumlo is concerned, the only reason this is a problem is that > vacuumlo goes out of its way to do all the large-object deletions in a > single transaction.  What's th

Re: [HACKERS] vacuumlo issue

MUHAMMAD ASIF wrote: > We have noticed the following issue with vacuumlo database that have millions > of record > in pg_largeobject i.e. [...] > ~/work/pg/postgresql-9.1.2/inst$ bin/vacuumlo vacuumlo_test > > WARNING:  out of shared memory > Failed to remove lo 36726: ERROR:  out of shared mem

Re: [HACKERS] vacuumlo issue

MUHAMMAD ASIF writes: > We have noticed the following issue with vacuumlo database that have millions > of record in pg_largeobject i.e. >WARNING: out of shared memoryFailed to remove lo 155987:ERROR: out > of shared memory HINT: You might need to increase > max_locks_per_transacti

Re: [HACKERS] vacuumlo issue

I have reformatted the mail, sorry for inconvenience. Thanks. We have noticed the following issue with vacuumlo database that have millions of record in pg_largeobject i.e. WARNING: out of shared memory Failed to remove lo 155987:ERROR: out of shared memory HINT: You m

[HACKERS] vacuumlo issue

Hi, We have noticed the following issue with vacuumlo database that have millions of record in pg_largeobject i.e.    WARNING:  out of shared memoryFailed to remove lo 155987:    ERROR:  out of shared memory   HINT:  You might need to increase max_locks_per_transaction. Why do we need to increa

Re: [HACKERS] vacuumlo patch

On Sun, Aug 7, 2011 at 7:53 PM, Tim wrote: > Thanks Josh, > I like the ability to bail out on PQTRANS_INERROR, and I think it's a small > enough fix to be appropriate to include in this patch. > I did consider it before but did not implement it because I am still new to > pgsql-hackers and did not

Re: [HACKERS] vacuumlo patch

Thanks Josh, I like the ability to bail out on PQTRANS_INERROR, and I think it's a small enough fix to be appropriate to include in this patch. I did consider it before but did not implement it because I am still new to pgsql-hackers and did not know how off-the-cuff. So thanks for the big improvem

Re: [HACKERS] vacuumlo patch

On Sun, Aug 7, 2011 at 3:54 AM, Tim wrote: > > Excerpts from Josh's message On Sun, Aug 7, 2011 at 2:36 AM: >> >> could we figure out what that limit should be based on >> max_locks_per_transaction? > > It would be nice to implement via "-l max" instead of making users do it > manually or somethin

Re: [HACKERS] vacuumlo patch

Excerpts from Peter's message On Sun, Aug 7, 2011 at 3:49 AM: > Please put the new option 'l' in some sensible order in the code and the > help output (normally alphabetical). Also, there should probably be > some update to the documentation. > I have alphabetized the help output, and one piece

Re: [HACKERS] vacuumlo patch

Excerpts from Josh's message On Sun, Aug 7, 2011 at 2:36 AM: > could we figure out what that limit should be based on > max_locks_per_transaction? It would be nice to implement via "-l max" instead of making users do it manually or something like this "-l $(grep "max_locks_per_transaction.*=" po

Re: [HACKERS] vacuumlo patch

On sön, 2011-08-07 at 00:41 -0400, Tim wrote: > Thanks for help. Attached is a patch including changes suggested in your > comments. Please put the new option 'l' in some sensible order in the code and the help output (normally alphabetical). Also, there should probably be some update to the docu

Re: [HACKERS] vacuumlo patch

On Sun, Aug 7, 2011 at 12:41 AM, Tim wrote: > Hi Josh, > > Thanks for help. Attached is a patch including changes suggested in your > comments. > > Excerpts from Josh's message On Sat, Aug 6, 2011 at 9:57 PM: >> >>  1. It wasn't clear to me whether you're OK with Aron's suggested >> tweak, please

Re: [HACKERS] vacuumlo patch

Hi Josh, Thanks for help. Attached is a patch including changes suggested in your comments. Excerpts from Josh's message On Sat, Aug 6, 2011 at 9:57 PM: > > 1. It wasn't clear to me whether you're OK with Aron's suggested > tweak, please include it in your patch if so. > I decided to and inclu

Re: [HACKERS] vacuumlo patch

On Tue, Jul 26, 2011 at 12:18 PM, Timothy D. F. Lewis wrote: > I'm not sure what David did for me so as per Roberts suggestion I have added > this patch to the commit fest. > I'm hoping I have not put this patch in more than one workflow. Hi Tim, I would be willing to review this patch for the n

Re: [HACKERS] vacuumlo patch

On Tue, Jul 26, 2011 at 12:18:31PM -0400, Timothy D. F. Lewis wrote: > On Mon, Jul 25, 2011 at 7:28 PM, Robert Haas wrote: > > > On Mon, Jul 25, 2011 at 9:37 AM, Tim wrote: > > > Updated the patch to also apply when the no-action flag is enabled. > > > > You may want to read this: > > > > http:/

Re: [HACKERS] vacuumlo patch

I'm not sure what David did for me so as per Roberts suggestion I have addedthis patch to the commit fest. I'm hoping I have not put this patch in more than one workflow

Re: [HACKERS] vacuumlo patch

Hi Aron, Thanks for the input. The "small change" you suggest would change the behavior of the patch which I would prefer not to do as I have reasons for the previous behavior. Because you gave no reasons and "stop after removing LIMIT LOs" was not changed to "stop after attempting to remove LIMIT

Re: [HACKERS] vacuumlo patch

> Excerpts from Aron's message of mar jul 26 04:18:55 -0400 2011: >> Here's another small change to the patch, it works fine for me and it quite >> saved my day. >> >> I try to submit the patch by email. > > There's a problem with this patch: long lines are being wrapped by > your email client, w

Re: [HACKERS] vacuumlo patch

Hi Tim, I have to correct my previous answer, my change does not alter the behavior of your patch significantly. > The difference: > In your version of the patch vacuumlo will stop after N lo_unlink(OID) > attempts. > The previous behavior of the patch is that vacuumlo will stop after N > succ

Re: [HACKERS] vacuumlo patch

Hi Tim, you correctly assumed I was just trying to clean up the code. There was no reason to change the behavior, so please ignore my change to the patch. Aron On 26.07.2011, at 15:44, Tim Lewis wrote: > Hi Aron, > > Thanks for the input. The "small change" you suggest would change the > be

Re: [HACKERS] vacuumlo patch

Excerpts from Aron's message of mar jul 26 04:18:55 -0400 2011: > Here's another small change to the patch, it works fine for me and it quite > saved my day. > > I try to submit the patch by email. There's a problem with this patch: long lines are being wrapped by your email client, which makes i

Re: [HACKERS] vacuumlo patch

Here's another small change to the patch, it works fine for me and it quite saved my day. I try to submit the patch by email. diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c index f6e2a28..1f88d72 100644 --- a/contrib/vacuumlo/vacuumlo.c +++ b/contrib/vacuumlo/vacuumlo.c @

Re: [HACKERS] vacuumlo patch

On Mon, Jul 25, 2011 at 9:37 AM, Tim wrote: > Updated the patch to also apply when the no-action flag is enabled. You may want to read this: http://wiki.postgresql.org/wiki/Submitting_a_Patch And add your patch here: https://commitfest.postgresql.org/action/commitfest_view/open -- Robert Haa

Re: [HACKERS] vacuumlo patch

Updated the patch to also apply when the no-action flag is enabled. git diff HEAD -- contrib/vacuumlo/vacuumlo.c diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c index f6e2a28..8e9c342 100644 --- a/contrib/vacuumlo/vacuumlo.c +++ b/contrib/vacuumlo/vacuumlo.c @@ -48,6 +48,7 @

Re: [HACKERS] vacuumlo patch

Hi Álvaro, thanks for the response. Here is the requested "diff with 3 lines of context": git diff HEAD -- contrib/vacuumlo/vacuumlo.c diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c index f6e2a28..b7c7d64 100644 --- a/contrib/vacuumlo/vacuumlo.c +++ b/contrib/vacuumlo/vacu

Re: [HACKERS] vacuumlo patch

Excerpts from Tim's message of dom jul 24 14:48:08 -0400 2011: > Please consider adding this minor patch, or offering improvements. > > *Problem*: vacuumlo required PostgreSQL to use more locks per transaction > than possible resulting in an error and a log file full of "ignored until > end of tra

[HACKERS] vacuumlo patch

Please consider adding this minor patch, or offering improvements. *Problem*: vacuumlo required PostgreSQL to use more locks per transaction than possible resulting in an error and a log file full of "ignored until end of transaction" warnings. (max_locks_per_transaction is limited by shmmax which

[HACKERS] vacuumlo doesn't seems to have help

Postresql 8.1.0: I was looking what does vacuumlo provide in contrib, but it seems there is a problem with messages given by this program: [EMAIL PROTECTED] regress]$ /usr/bin/vacuumlo vacuumlo: missing required argument: database name Try 'vacuumlo -?' for help. [EMAIL PROTECTED] regress]$ /usr

Re: [HACKERS] vacuumlo.

> > > > IIRC vacuumlo doesn't take the type lo(see contrib/lo) into > > > > account. I'm suspicious if vacuumlo is reliable. > > > > > > This was my round about way of asking if something to combat this issue > > > can be placed in the to do list. :) > > > > Added to TODO: > > > > * Impr

Re: [HACKERS] vacuumlo.

Bruce Momjian wrote: > > > > > > Can you see a scenario where a programmer would forget to delete the > > > > > data from pg_largeobject and the database becoming very large filled > > > > > with orphaned large objects? > > > > > > > > Sure. My point wasn't that the functionality isn't needed, it

Re: [HACKERS] vacuumlo.

> > > > Can you see a scenario where a programmer would forget to delete the > > > > data from pg_largeobject and the database becoming very large filled > > > > with orphaned large objects? > > > > > > Sure. My point wasn't that the functionality isn't needed, it's that > > > I'm not sure vacuu

Re: [HACKERS] vacuumlo.

> > > Can you see a scenario where a programmer would forget to delete the > > > data from pg_largeobject and the database becoming very large filled > > > with orphaned large objects? > > > > Sure. My point wasn't that the functionality isn't needed, it's that > > I'm not sure vacuumlo does it

Re: [HACKERS] vacuumlo.

Tom Lane wrote: > > Grant <[EMAIL PROTECTED]> writes: > > Can you see a scenario where a programmer would forget to delete the > > data from pg_largeobject and the database becoming very large filled > > with orphaned large objects? > > Sure. My point wasn't that the functionality isn't needed,

Re: [HACKERS] vacuumlo.

Grant <[EMAIL PROTECTED]> writes: > Can you see a scenario where a programmer would forget to delete the > data from pg_largeobject and the database becoming very large filled > with orphaned large objects? Sure. My point wasn't that the functionality isn't needed, it's that I'm not sure vacuuml

Re: [HACKERS] vacuumlo.

> > Is it possible to get [vacuumlo] included in the main vacuumdb program for > > support to vacuum orphaned large objects? > > Hmm. I'm not convinced that vacuumlo is ready for prime time... > in particular, how safe is it in the presence of concurrent > transactions that might be adding or re

Re: [HACKERS] vacuumlo.

Grant <[EMAIL PROTECTED]> writes: > Is it possible to get [vacuumlo] included in the main vacuumdb program for > support to vacuum orphaned large objects? Hmm. I'm not convinced that vacuumlo is ready for prime time... in particular, how safe is it in the presence of concurrent transactions that

[HACKERS] vacuumlo.

I sent the email below to the creator of contrib/vacuumlo/ with no reply just yet. Is it possible to get his code included in the main vacuumdb program for support to vacuum orphaned large objects? Or... Any suggestions, what do people think? Thanks. -- Forwarded message -- Dat