Обсуждение: Setting a table to be ignored by autovacuum

 
Hi,
 
I'm trying to have this table ignored by the autovacuum process.
It wasn't created with this in mind, hoping there is still a way?
 
Thanks,
Chris
 
alter table schema.table SET (autovacuum_enabled = false);
 
ERROR:  unrecognized parameter "autovacuum_enabled"

---

Windows® phone-your Windows stuff, on the go. See more.

On 18/02/10 17:20, Chris Barnes wrote:
>
> I'm trying to have this table ignored by the autovacuum process.
>
> It wasn't created with this in mind, hoping there is still a way?

> alter table schema.table SET (autovacuum_enabled = false);
>
> ERROR:  unrecognized parameter "autovacuum_enabled"

Close, but it's classed under storage parameters. You'll want to see the
SQL reference entry for "CREATE TABLE".

You might want to consider just turning the settings down rather than
off completely too.

--
   Richard Huxton
   Archonet Ltd

Hrmm.. I think the OP's syntax is correct, but he's probably using a version older than 8.4, when support for per-table autovacuum_enabled was added.

On HEAD:

test=# CREATE TABLE foo (a int);
CREATE TABLE
test=# alter table foo SET ( autovacuum_enabled=false) ;
ALTER TABLE
test=# \d+ foo
                  Table "public.foo"
 Column |  Type   | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
 a      | integer |           | plain   |
Has OIDs: no
Options: autovacuum_enabled=false


 Josh

 
Right you are, I'm due to upgrade end of month on this system.
Here I was thinking 8.4. Sorry for the spam.
 
Chris
 
 
[postgres@pgprd01:~/pgcheck]$ psql
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
postgres=#
 

 

---

From: schmiddy@gmail.com
Date: Thu, 18 Feb 2010 12:42:52 -0500
Subject: Re: [GENERAL] Setting a table to be ignored by autovacuum
To: dev@archonet.com
CC: compuguruchrisbarnes@hotmail.com; pgsql-general@postgresql.org



Hrmm.. I think the OP's syntax is correct, but he's probably using a version older than 8.4, when support for per-table autovacuum_enabled was added.

On HEAD:

test=# CREATE TABLE foo (a int);
CREATE TABLE
test=# alter table foo SET ( autovacuum_enabled=false) ;
ALTER TABLE
test=# \d+ foo
                  Table "public.foo"
 Column |  Type   | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
 a      | integer |           | plain   |
Has OIDs: no
Options: autovacuum_enabled=false


 Josh

---

All your Hotmail contacts on your phone. Try it now.

Chris Barnes escribió:
>
>
>
> Right you are, I'm due to upgrade end of month on this system.
>
> Here I was thinking 8.4. Sorry for the spam.

You can "INSERT INTO pg_autovacuum VALUES ('schema.table'::regclass,
false, -1, -1, ...);" in previous versions.



--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Thanks Alvaro,
 
Hopefully it will stop my locking issue when I have high volume of changes on this table and vacuum starts.
 
Thx
 
Chris   :)
 
> Date: Thu, 18 Feb 2010 16:55:24 -0300
> From: alvherre@commandprompt.com
> To: compuguruchrisbarnes@hotmail.com
> CC: schmiddy@gmail.com; dev@archonet.com; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Setting a table to be ignored by autovacuum
>
> Chris Barnes escribió:
> >
> >
> >
> > Right you are, I'm due to upgrade end of month on this system.
> >
> > Here I was thinking 8.4. Sorry for the spam.
>
> You can "INSERT INTO pg_autovacuum VALUES ('schema.table'::regclass,
> false, -1, -1, ...);" in previous versions.
>
>
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Windows® phone-your Windows stuff, on the go. See more.

On 18 Feb 2010, at 18:47, Chris Barnes wrote:

> Right you are, I'm due to upgrade end of month on this system.
> Here I was thinking 8.4. Sorry for the spam.
>
> Chris
>
>
> [postgres@pgprd01:~/pgcheck]$ psql
> Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help with psql commands
>        \g or terminate with semicolon to execute query
>        \q to quit
> postgres=#

Actually that just tells that you're using an 8.3 _client_. It doesn't tell what server-version you're using.
For the server version do:

deploy=# SELECT version();
                                                                version
                

----------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.1 on i386-apple-darwin10.0.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc.
build5646), 64-bit 
(1 row)


Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.


!DSPAM:737,4b7e74ef10441772699480!

It is...
 
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
postgres=# SELECT version();
                                                 version                                                 
----------------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9)
(1 row)
postgres=#
 
> Subject: Re: [GENERAL] Setting a table to be ignored by autovacuum
> From: dalroi@solfertje.student.utwente.nl
> Date: Fri, 19 Feb 2010 12:24:24 +0100
> CC: schmiddy@gmail.com; dev@archonet.com; pgsql-general@postgresql.org
> To: compuguruchrisbarnes@hotmail.com
>
> On 18 Feb 2010, at 18:47, Chris Barnes wrote:
>
> > Right you are, I'm due to upgrade end of month on this system.
> > Here I was thinking 8.4. Sorry for the spam.
> >
> > Chris
> >
> >
> > [postgres@pgprd01:~/pgcheck]$ psql
> > Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
> > Type: \copyright for distribution terms
> > \h for help with SQL commands
> > \? for help with psql commands
> > \g or terminate with semicolon to execute query
> > \q to quit
> > postgres=#
>
> Actually that just tells that you're using an 8.3 _client_. It doesn't tell what server-version you're using.
> For the server version do:
>
> deploy=# SELECT version();
> version
> ----------------------------------------------------------------------------------------------------------------------------------------
> PostgreSQL 8.4.1 on i386-apple-darwin10.0.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5646), 64-bit
> (1 row)
>
>
> Alban Hertroys
>
> --
> Screwing up is the best way to attach something to the ceiling.
>
>
> !DSPAM:1029,4b7e74ee10441497119330!
>
>

---

Live connected with Hotmail on your phone. Learn more.