Обсуждение: 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
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?Close, but it's classed under storage parameters. You'll want to see the SQL reference entry for "CREATE TABLE".alter table schema.table SET (autovacuum_enabled = false);
ERROR: unrecognized parameter "autovacuum_enabled"
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?Close, but it's classed under storage parameters. You'll want to see the SQL reference entry for "CREATE TABLE".alter table schema.table SET (autovacuum_enabled = false);
ERROR: unrecognized parameter "autovacuum_enabled"
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.
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.
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.