On Thu, Feb 23, 2017 at 2:42 AM, Bill Moran
wrote:
> On Wed, 22 Feb 2017 13:19:11 -0800
> Jeff Janes wrote:
>
> > On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure
> wrote:
> > >
> > > On Thursday, February 16, 2017, Tom Lane wrote:
> > >
> > >> Tim Bellis writes:
> > >> > Even though this is a
On Wed, Feb 22, 2017 at 3:19 PM, Jeff Janes wrote:
> On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure wrote:
>> On Thursday, February 16, 2017, Tom Lane wrote:
>>>
>>> Tim Bellis writes:
>>> > Even though this is a read only query, is it also expected to be
>>> > blocked behind the vacuum? Is th
On Wed, 22 Feb 2017 13:19:11 -0800
Jeff Janes wrote:
> On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure wrote:
> >
> > On Thursday, February 16, 2017, Tom Lane wrote:
> >
> >> Tim Bellis writes:
> >> > Even though this is a read only query, is it also expected to be
> >> blocked behind the vacu
On Fri, Feb 17, 2017 at 10:36 AM, Tim Bellis
wrote:
>
>
>
>
> *From:* Jeff Janes [mailto:jeff.ja...@gmail.com]
> *Sent:* 17 February 2017 02:59
> *To:* Tim Bellis
> *Cc:* pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] Autovacuum stuck for hours, blocking que
On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure wrote:
>
>
> On Thursday, February 16, 2017, Tom Lane wrote:
>
>> Tim Bellis writes:
>> > Even though this is a read only query, is it also expected to be
>> blocked behind the vacuum? Is there a way of getting indexes for a table
>> which won't b
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: 16 February 2017 22:40
To: Tim Bellis
Cc: Adrian Klaver ; pgsql-general@postgresql.org;
Alvaro Herrera ; Scott Marlowe
Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries
Tim Bellis writes
On Thursday, February 16, 2017, Tom Lane wrote:
> Tim Bellis > writes:
> > Even though this is a read only query, is it also expected to be blocked
> behind the vacuum? Is there a way of getting indexes for a table which
> won't be blocked behind a vacuum?
>
> It's not the vacuum that's blocking
On 02/17/2017 11:54 PM, Michael Paquier wrote:
On Sat, Feb 18, 2017 at 1:32 PM, Scott Marlowe wrote:
Yes it can. Truncate has been rollbackable for a while now.
Per the docs:
https://www.postgresql.org/docs/9.6/static/sql-truncate.html
"TRUNCATE is transaction-safe with respect to the data in
On Sat, Feb 18, 2017 at 1:32 PM, Scott Marlowe wrote:
> Yes it can. Truncate has been rollbackable for a while now.
Per the docs:
https://www.postgresql.org/docs/9.6/static/sql-truncate.html
"TRUNCATE is transaction-safe with respect to the data in the tables:
the truncation will be safely rolled
On Fri, Feb 17, 2017 at 1:38 PM, Rakesh Kumar
wrote:
> LOCK TABLE yourtable ;
> CREATE TEMPORARY TABLE keep AS SELECT * FROM yourtable WHERE ;
> TRUNCATE yourtable;
> INSERT INTO yourtable SELECT * from keep;
> COMMIT;
> ===
> the above snippet assumes truncate in PG can be in a transaction. In ot
LOCK TABLE yourtable ;
CREATE TEMPORARY TABLE keep AS SELECT * FROM yourtable WHERE ;
TRUNCATE yourtable;
INSERT INTO yourtable SELECT * from keep;
COMMIT;
===
the above snippet assumes truncate in PG can be in a transaction. In other
words, while truncate by itself
is atomic, it can't be rolled b
e;
INSERT INTO yourtable SELECT * from keep;
COMMIT;
Best regards,
-hannes
-Original Message-
From: Hannes Erven [mailto:han...@erven.at]
Sent: 17 February 2017 11:47
To: pgsql-general@postgresql.org
Cc: Tim Bellis
Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking querie
From: Jeff Janes [mailto:jeff.ja...@gmail.com]
Sent: 17 February 2017 02:59
To: Tim Bellis
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries
On Wed, Feb 15, 2017 at 9:30 AM, Tim Bellis
mailto:tim.bel...@metaswitch.com>> wrote:
I
.@erven.at]
Sent: 17 February 2017 11:47
To: pgsql-general@postgresql.org
Cc: Tim Bellis
Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries
Hi Tim,
Am 2017-02-15 um 18:30 schrieb Tim Bellis:
> I have a postgres 9.3.4 database table which (intermittently but
> reliably
Hi Tim,
Am 2017-02-15 um 18:30 schrieb Tim Bellis:
I have a postgres 9.3.4 database table which (intermittently but reliably)
> gets into a state where queries get blocked indefinitely
> [..]
Notes:
- This database table is used for about 6 million row writes per day,
> all of which a
On Wed, Feb 15, 2017 at 9:30 AM, Tim Bellis
wrote:
> I have a postgres 9.3.4 database table which (intermittently but reliably)
> gets into a state where queries get blocked indefinitely (at least for many
> hours) behind an automatic vacuum. I was under the impression that vacuum
> should never
Tom Lane wrote:
> Also you might want to look into how you got into a situation where
> you have an anti-wraparound vacuum that's taking so long to run.
If there are ALTERs running all the time, regular (non-anti-wraparound)
vacuums would be canceled and never get a chance to run. Eventually,
au
Tim Bellis writes:
> Even though this is a read only query, is it also expected to be blocked
> behind the vacuum? Is there a way of getting indexes for a table which won't
> be blocked behind a vacuum?
It's not the vacuum that's blocking your read-only queries. It's the
ALTER TABLE, which nee
On 02/16/2017 08:45 AM, Tim Bellis wrote:
Thank you all - that's really useful :-)
The other query that gets blocked behind the vacuum is the below (truncated).
This query is generated by jdbc in this method:
org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getIndexInfo(AbstractJdbc2DatabaseM
Thank you all - that's really useful :-)
The other query that gets blocked behind the vacuum is the below (truncated).
This query is generated by jdbc in this method:
org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getIndexInfo(AbstractJdbc2DatabaseMetaData.java:4023)
Even though this is a re
Scott Marlowe wrote:
> Then a ddl gets in line. It has to wait on the vacuum, and the vacuum,
> set to run super slow. And everybody waits. On vacuum.
Note that this is normally not seen, because autovacuum cancels itself
when somebody is blocked behind it -- until the table reaches the
freeze_ma
On Wed, Feb 15, 2017 at 3:26 PM, Scott Marlowe wrote:
> On Wed, Feb 15, 2017 at 10:30 AM, Tim Bellis
> wrote:
>> I have a postgres 9.3.4 database table which (intermittently but reliably)
>> gets into a state where queries get blocked indefinitely (at least for many
>> hours) behind an automat
On Wed, Feb 15, 2017 at 10:30 AM, Tim Bellis wrote:
> I have a postgres 9.3.4 database table which (intermittently but reliably)
> gets into a state where queries get blocked indefinitely (at least for many
> hours) behind an automatic vacuum. I was under the impression that vacuum
> should nev
On 02/15/2017 09:30 AM, Tim Bellis wrote:
I have a postgres 9.3.4 database table which (intermittently but reliably) gets
into a state where queries get blocked indefinitely (at least for many hours)
behind an automatic vacuum. I was under the impression that vacuum should never
take any block
I have a postgres 9.3.4 database table which (intermittently but reliably) gets
into a state where queries get blocked indefinitely (at least for many hours)
behind an automatic vacuum. I was under the impression that vacuum should never
take any blocking locks for any significant period of time
25 matches
Mail list logo
