FIXED: backend crash on DELETE, reproducible locally

[Karsten Hilbert]

For the record:

Regarding backend crash when DELETEing tuples older than
a recent ALTER TABLE ADD COLUMN:

> > > On Fri, Nov 02, 2018 at 11:56:58PM -0400, Tom Lane wrote:
> > >> I was feeling baffled about this, but it suddenly occurs to me that maybe
> > >> the bug fixed in 040a1df61/372102b81 explains this.
> > 
> > > So, I guess I can work around the issue by the above
> > > manoeuvre and report back once 040a1df61/372102b81 is
> > > released.
>
> I will, at any rate, report back when the existing fix is released.

Now that PG11.1 is released on Debian

postgresql-11:
  Installiert:   11.1-1
  Installationskandidat: 11.1-1
  Versionstabelle:
 *** 11.1-1 500
500 http://httpredir.debian.org/debian unstable/main i386 
Packages
100 /var/lib/dpkg/status
 11.0-1+b1 990
990 http://httpredir.debian.org/debian buster/main i386 Packages

I can report that my issued is fixed by that version.

Thanks to all,
Karsten
-- 
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Full list of operations that constitute a "maintenance" operation?

[Lance Luvaul]

Hi all, I've read on the Postgres documentation for 'maintenance_work_mem'
that VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY are considered
maintenance operations, but are there others?  For example I use ALTER
TABLE ADD COLUMN and ALTER TABLE SET LOGGED in my scripts... are
they maintenance operations that would cause a maintenance_work_mem-sized
chunk of memory (or more than 1 such chunk) to be allocated?  Is there a
complete list somewhere?  Thanks!

Re: ERROR: found multixact from before relminmxid

[Adrien NAYRAT]

On 11/7/18 1:21 PM, Alexandre Arruda wrote:
The best solution that I have found is kick all connections and execute 
a select for update to /dev/null in the affected tables, i.e.:


psql -o /dev/null -c "select * from table for update" database

After this, the vacuum is executed w/o problems again.

Best regards,

Alexandre


Thanks Alexandre, I will try.

Re: Full list of operations that constitute a "maintenance" operation?

[Laurenz Albe]

Lance Luvaul wrote:
> Hi all, I've read on the Postgres documentation for 'maintenance_work_mem' 
> that VACUUM, CREATE INDEX,
> and ALTER TABLE ADD FOREIGN KEY are considered maintenance operations, but 
> are there others?
> For example I use ALTER TABLE ADD COLUMN and ALTER TABLE SET LOGGED in my 
> scripts...
> are they maintenance operations that would cause a maintenance_work_mem-sized 
> chunk of memory
> (or more than 1 such chunk) to be allocated?  Is there a complete list 
> somewhere?  Thanks!

Trawling the source shows that it is used for index operations like CREATE 
INDEX and REINDEX,
but also for ALTER TABLE ADD FOREIGN KEY, VACUUM and CLUSTER.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

index only scan question

[Daniel Westermann]

Hi quick

question: Given these steps:


postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;
    QUERY PLAN    
--
 Index Only Scan using i2 on t1 (actual time=0.056..0.058 rows=1 loops=1)
   Index Cond: (b = 5)
   Heap Fetches: 0
   Buffers: shared hit=4
 Planning time: 0.421 ms
 Execution time: 0.111 ms
(6 rows)

postgres=# update t1 set a = 30 where b = 5;
UPDATE 1
postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;
    QUERY PLAN    
--
 Index Only Scan using i2 on t1 (actual time=0.039..0.042 rows=1 loops=1)
   Index Cond: (b = 5)
   Heap Fetches: 2
   Buffers: shared hit=5
 Planning time: 0.176 ms
 Execution time: 0.082 ms

The 2 heap fetches for the second run are clear to me, because of the pointer 
from the old version of the row to the new one. But why does the next execution 
only need one heap fetch?


postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;
    QUERY PLAN    
--
 Index Only Scan using i2 on t1 (actual time=0.046..0.049 rows=1 loops=1)
   Index Cond: (b = 5)
   Heap Fetches: 1
   Buffers: shared hit=5
 Planning time: 0.194 ms
 Execution time: 0.097 ms

Is that because of some sort of caching?

Thanks in advance
Daniel







  
   

Re: index only scan question

[Laurenz Albe]

Daniel Westermann wrote:
> question: Given these steps:
> 
> 
> postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;
> QUERY PLAN
> --
>  Index Only Scan using i2 on t1 (actual time=0.056..0.058 rows=1 loops=1)
>Index Cond: (b = 5)
>Heap Fetches: 0
>Buffers: shared hit=4
>  Planning time: 0.421 ms
>  Execution time: 0.111 ms
> (6 rows)
> 
> postgres=# update t1 set a = 30 where b = 5;
> UPDATE 1
> postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;
> QUERY PLAN
> --
>  Index Only Scan using i2 on t1 (actual time=0.039..0.042 rows=1 loops=1)
>Index Cond: (b = 5)
>Heap Fetches: 2
>Buffers: shared hit=5
>  Planning time: 0.176 ms
>  Execution time: 0.082 ms
> 
> The 2 heap fetches for the second run are clear to me, because of the pointer 
> from the old version of the row to the new one. But why does the next 
> execution only need one heap fetch?
> 
> 
> postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;
> QUERY PLAN
> --
>  Index Only Scan using i2 on t1 (actual time=0.046..0.049 rows=1 loops=1)
>Index Cond: (b = 5)
>Heap Fetches: 1
>Buffers: shared hit=5
>  Planning time: 0.194 ms
>  Execution time: 0.097 ms
> 
> Is that because of some sort of caching?

I'd say the old index tuple was killed during the first scan:
https://www.cybertec-postgresql.com/en/killed-index-tuples/

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Full list of operations that constitute a "maintenance" operation?

[Andrew Gierth]

> "Lance" == Lance Luvaul  writes:

 Lance> Hi all, I've read on the Postgres documentation for
 Lance> 'maintenance_work_mem' that VACUUM, CREATE INDEX, and ALTER
 Lance> TABLE ADD FOREIGN KEY are considered maintenance operations, but
 Lance> are there others? For example I use ALTER TABLE ADD COLUMN and
 Lance> ALTER TABLE SET LOGGED in my scripts... are they maintenance
 Lance> operations that would cause a maintenance_work_mem-sized chunk
 Lance> of memory (or more than 1 such chunk) to be allocated?

There are essentially two classes of operations that use
maintenance_work_mem:

1. Specific operations: non-full VACUUM (for the deleted tid list),
CLUSTER (for sorting the table content), ALTER ... ADD FOREIGN KEY (for
the validation query, which might want to use sorts or a hashjoin).
(It's not impossible, though it should be rare, for an FK validation to
use two chunks of maintenance_work_mem - with the right table sizes and
data types, the validation query could plan as a merge anti-join with
explicit sorts on both sides.)

2. Anything that (re)builds an index for any reason. This includes a
large set of operations: CREATE INDEX and REINDEX are obvious, likewise
VACUUM FULL and CLUSTER, but also any form of ALTER TABLE that rewrites
the heap (which _includes_ SET LOGGED, but does not include adding a
column with no default, or in pg11+ adding a column with a default). It
also includes non-concurrent refresh of a materialized view. (TRUNCATE
also technically rewrites the table heap, but since the new heap is
empty, memory consumption during reindex is not an issue.) ALTER TABLE
to add a unique, primary key or exclusion constraint also creates an
index to implement the constraint with (unless USING INDEX was used to
adopt an existing index) so that counts too.

-- 
Andrew (irc:RhodiumToad)

Re: index only scan question

[Andreas Kretschmer]

Am 09.11.2018 um 13:58 schrieb Daniel Westermann:

Is that because of some sort of caching?


no, but vacuum updated the visibility map in the meantime.

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: index only scan question

[Daniel Westermann]

>Am 09.11.2018 um 13:58 schrieb Daniel Westermann:
>> Is that because of some sort of caching?

>no, but vacuum updated the visibility map in the meantime.

No, it do not, double checked that with:
select pg_visibility_map('t1'::regclass, 0);

Re: index only scan question

[Daniel Westermann]

>I'd say the old index tuple was killed during the first scan:
>https://www.cybertec-postgresql.com/en/killed-index-tuples/

Thanks Laurenz, I will check that

Re: index only scan question

[Daniel Westermann]

>I'd say the old index tuple was killed during the first scan:
>https://www.cybertec-postgresql.com/en/killed-index-tuples/
... from your blog: "Whenever an index scan fetches a heap tuple only to find 
that it is dead (that the entire “HOT chain” of tuples is dead, to be more 
precise), it marks the index tuple as “killed”. Then future index scans can 
simply ignore it.

Re: index only scan question

[Daniel Westermann]

sorry, hit the wrong key

>I'd say the old index tuple was killed during the first scan:
>https://www.cybertec-postgresql.com/en/killed-index-tuples/

... from your blog: "Whenever an index scan fetches a heap tuple only to find 
that it is dead (that the entire “HOT chain” of tuples is dead, to be more 
precise), it marks the index tuple as “killed”. Then future index scans can 
simply ignore it.

I understand that, but in my case the chain is not dead so this does not 
explain the difference. Do I miss something?

Regards
Daniel

Re: index only scan question

[Laurenz Albe]

Daniel Westermann wrote:
> >I'd say the old index tuple was killed during the first scan:
> >https://www.cybertec-postgresql.com/en/killed-index-tuples/
> 
> ... from your blog: "Whenever an index scan fetches a heap tuple only to find 
> that it is dead
> (that the entire “HOT chain” of tuples is dead, to be more precise), it marks 
> the index tuple
> as “killed”. Then future index scans can simply ignore it.
> 
> I understand that, but in my case the chain is not dead so this does not 
> explain the difference.
> Do I miss something?

I assume that the UPDATE was not HOT, because the first scan had to fetch two 
tuples.

After the UPDATE, the original tuple was dead (the HOT chain consists
only of a single tuple here, because it was no HOT update).
The first index scan detects that and marks the index tuple as killed.
The second index scan only visits the new tuple.

Yours,
Laurenz Albe