Re: [PATCHES] tiny patch to make vacuumdb -a's database order match pg_dumpall

[Bruce Momjian]

Dan Thomas wrote:
 Hiya,
 
 I've been having trouble running vacuumdb -a and pg_dumpall
 concurrently because they run through the databases in a different
 order (so dumpall was getting stuck behind vacuum's lock, and my
 firewall was rather unhelpfully closing the idle connection). I can't
 see a good reason for them to be using a different order, and as it
 will only affect those that have created new databases since restoring
 from a dump, may not be instantly obvious. It appears slightly more
 thought has gone into pg_dumpall's code (in that it actually includes
 an ORDER BY), so I elected to fiddle with vacuumdb.
 
 I hope this is in the correct format (this is the first patch I've
 submitted for anything), please let me know if I've done something
 daft.
 
 Dan
 
 237c237
result = executeQuery(conn, SELECT datname FROM pg_database
 WHERE datallowconn;, progname, echo);
 ---
result = executeQuery(conn, SELECT datname FROM pg_database WHERE 
  datallowconn ORDER BY 1;, progname, echo);

OK, ORDER BY added for 8.3.

-- 
  Bruce Momjian  [EMAIL PROTECTED]  http://momjian.us
  EnterpriseDB   http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/bin/scripts/vacuumdb.c
===
RCS file: /cvsroot/pgsql/src/bin/scripts/vacuumdb.c,v
retrieving revision 1.15
diff -c -c -r1.15 vacuumdb.c
*** src/bin/scripts/vacuumdb.c	5 Jan 2007 22:19:50 -	1.15
--- src/bin/scripts/vacuumdb.c	13 Feb 2007 17:37:09 -
***
*** 234,240 
  	int			i;
  
  	conn = connectDatabase(postgres, host, port, username, password, progname);
! 	result = executeQuery(conn, SELECT datname FROM pg_database WHERE datallowconn;, progname, echo);
  	PQfinish(conn);
  
  	for (i = 0; i  PQntuples(result); i++)
--- 234,240 
  	int			i;
  
  	conn = connectDatabase(postgres, host, port, username, password, progname);
! 	result = executeQuery(conn, SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;, progname, echo);
  	PQfinish(conn);
  
  	for (i = 0; i  PQntuples(result); i++)

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PATCHES] tiny patch to make vacuumdb -a's database order match pg_dumpall

[Tom Lane]

Dan Thomas [EMAIL PROTECTED] writes:
 I've been having trouble running vacuumdb -a and pg_dumpall
 concurrently because they run through the databases in a different
 order (so dumpall was getting stuck behind vacuum's lock, and my
 firewall was rather unhelpfully closing the idle connection).

Um, whaddya mean dumpall was getting stuck behind vacuum's lock?
A plain vacuum doesn't take any locks that would block pg_dump.

While the proposed patch looks harmless enough, I'm unconvinced that
it will solve your problem, or even quite what the problem is.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PATCHES] tiny patch to make vacuumdb -a's database order match pg_dumpall

[Dan Thomas]

Um, whaddya mean dumpall was getting stuck behind vacuum's lock?
A plain vacuum doesn't take any locks that would block pg_dump.


Dammit, just looked and the setup I originally encountered the problem
on and tracked it down to the vacuum process, and it is indeed set up
to perform a full vacuum.. I've incorrectly assumed the problem I'm
having now (with a normal vac) was for the same reason.


While the proposed patch looks harmless enough, I'm unconvinced that
it will solve your problem, or even quite what the problem is.


Yes, sorry about that, it does indeed appear that whatever is causing
my dumpall process to die isn't PG's fault.

Though I still think it makes a *bit* of sense to have vacuumdb use
the same order as pg_dumpall (clusterdb too now I think about it),
it's obviously not as much of an issue as I originally thought, and
not the source of my problem, which is a shame :)

Dan

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq