[ADMIN] upgrade 8.3.7 to 8.3.10 combined with ownership change

Hi Further to David Jantzen's post of 3/16/2010. We have decided to try to upgrade the database software from our old custom 8.3.7 instance to the std upstream 8.3.10 version. We have no hash indexes on interval types; we therefore anticipate that the migration will be a shutdown of the old ins

[ADMIN] optimizer behavior in the case of highly updated tables

I am looking for some specific information regarding optimizer behavior. We recently experienced a situation where a query that was previously using a btree lookup (efficient) SWITCHED to using seqscan/hash lookup. My questions would be: - Under what circumstances is the optimizer like

[ADMIN] how do i query the type of an object?

Is there some way to query the data-type of ''?

[ADMIN] plpgsql syntax question

Ok guys - there is something here that I am not seeing Can someone please set me straight. I am trying to create a cursor loop with an "update where current of " statement, but my syntax is wrong. Would one of you be able to tell me what I'm doing. Also: adding "for update" to the curs1 curs

Re: [ADMIN] plpgsql syntax question

> That syntax is fine ... in 8.4 and up. I think you are trying to use some > older server version, and not reading the right version of the manual. Thanks Mr Lane Yes - I was testing it on 8.3.10, but the manual was 8.4.2 :( -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)

[ADMIN] can you change pg_hba.conf and restart the listener on the fly?

Hi We are running an 8.3.10 instance. I need to make a change to pg_hba.conf on the fly. Is it possible to restart just the listener for the database without cycling the postmaster? Mr

[ADMIN] management of pg-schemas: pg_temp_n and pg_toast_n ?

This is occurring In an 8.4.2 pg database, and I have noticed it in 8.3.10 as well. We do a lot of work with temp tables here, and over time a lot of pg_temp_[n] schemas have appeared. Similarly, we notice build-up of pg_toast_[n] schemas. Is it ok to drop these if they contain no tables? Or is

[ADMIN] long-running autovacuum tasks

Version of pg server is 8.3.10 We have some very large tables (78 gb/ 60m rows, 132 gb/90m rows). Storage is mounted NFS on a netapp 3160 (pretty fast I/O). However, the tables have been autovacuuming for over 3 days (from querying pg-stat-activity). The current_query column value is : "autovacuum

Re: [ADMIN] long-running autovacuum tasks

From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Brad Nicholson Sent: Thursday, September 23, 2010 5:53 AM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] long-running autovacuum tasks On 10-09-22 06:18 PM, Mark Rostron wrote: Version of pg

[ADMIN] xid wraparound

When the XID wraps, at the moment it does so, unless you set vacuum_freeze_min_age to 0 and a vacuum has just been performed, is there not a chance that there will be some data loss? If it changes value from (2^32 -1) to (0), it's value is going to be less than SOME rows - the ones which have no

Re: [ADMIN] xid wraparound

> >> When the XID wraps, at the moment it does so, unless you set >> vacuum_freeze_min_age to 0 and a vacuum has just been performed, is >> there not a chance that there will be some data loss? > >No. XID comparisons are modulo 2^31. > Thanks - I'm still trying to wrap my mind around this (s

Re: [ADMIN] xid wraparound

> No, it is not. The XID space is continuous and circular. For any given XID, > there are 2^31-1 possible XIDs that are "before" it and 2^31-1 that are > "after" it (plus the special > FrozenXID value, which is always before everything else). There's no > absolute comparisons possible, only