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"
_________________________________________________________________
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
On Thu, Feb 18, 2010 at 12:37 PM, Richard Huxton <dev@archonet.com> wrote:
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".
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
On Thu, Feb 18, 2010 at 12:37 PM, Richard Huxton <dev@archonet.com> wrote:
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".
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
_________________________________________________________________
Introducing Windows® phone.
http://go.microsoft.com/?linkid=9708122
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 autovacuumChris 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
_________________________________________________________________
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: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.comOn 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!
_________________________________________________________________
Check your Hotmail from your phone.
http://go.microsoft.com/?linkid=9708121