Обсуждение: [GENERAL] Block duplications in a shared buffers

Hi All,
I was analysing shared buffers content and noticed that exactly the same
disk block appears there many times with different or the same usagecount.
What's the cause of that? It's because of transactions? 

SELECT count(*), relfilenode, relblocknumber, array_agg(usagecount) usagecount, array_agg(isdirty)    isdirty,
array_agg(bufferid)  bufferid
 
FROM fo_main.buffercache
WHERE added = 'some_date'
GROUP BY relfilenode, relblocknumber
HAVING count(*) > 1
ORDER BY 1 DESC;

Sample result:

<http://www.postgresql-archive.org/file/t342733/Auswahl_830.png> 




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pinker <pinker@onet.eu> writes:
> I was analysing shared buffers content and noticed that exactly the same
> disk block appears there many times with different or the same usagecount.

Postgres would be completely broken if that were true, because
modifications made to one copy would fail to propagate to other copies.
I don't know where your data came from, but it can't be an accurate
representation of the instantaneous state of the buffer cache.

... actually, after looking at your query, I wonder whether the issue
is that you're failing to include database and tablespace in the
grouping key.  relfilenode isn't guaranteed unique across directories.
The fork number can matter, too.
        regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane-2 wrote
> Postgres would be completely broken if that were true, because
> modifications made to one copy would fail to propagate to other copies.
> I don't know where your data came from, but it can't be an accurate
> representation of the instantaneous state of the buffer cache.
> 
> ... actually, after looking at your query, I wonder whether the issue
> is that you're failing to include database and tablespace in the
> grouping key.  relfilenode isn't guaranteed unique across directories.
> The fork number can matter, too.

thank you I'll add those columns to my query




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thank you Tom, you were right. I needed to group by all the columns:
reldatabase, reltablespace, relforknumber too. Now all of them are unique.
Thank you for clearing this out :)



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general