Обсуждение: pg_am access in simple transaction?

Hi,
I'm doing simple tests with lsof on data catalog with bash script:

#!/bin/bash
for i in {0..2000}
do
    psql -U postgres -c '*BEGIN; select pg_sleep(30); COMMIT*'&
done

and i measure number of open files and what files are affected by specific
command.
Lsof has shown me that the only file that was open during this test was:
data/base/13328/2601, which is pg_catalog table pg_am.

Why postgres opens a table that stores information about relation (index)
access methods, while none index evidently is in use?








--
View this message in context: http://postgresql.nabble.com/pg-am-access-in-simple-transaction-tp5931974.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 11/25/2016 04:40 AM, pinker wrote:
> Hi,
> I'm doing simple tests with lsof on data catalog with bash script:
>
> #!/bin/bash
> for i in {0..2000}
> do
>     psql -U postgres -c '*BEGIN; select pg_sleep(30); COMMIT*'&
> done
>
> and i measure number of open files and what files are affected by specific
> command.
> Lsof has shown me that the only file that was open during this test was:
> data/base/13328/2601, which is pg_catalog table pg_am.
>
> Why postgres opens a table that stores information about relation (index)
> access methods, while none index evidently is in use?

I can not replicate using 50 clients instead of 2000. I suspect either
has to do with the extreme number of clients or it is an artifact of
from some other process.

>
>
>
>
>
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/pg-am-access-in-simple-transaction-tp5931974.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver-4 wrote
> I can not replicate using 50 clients instead of 2000. I suspect either
> has to do with the extreme number of clients or it is an artifact of
> from some other process.

And I have replicated it with 50 clients as well... lsof output:

51 data/base/13328/2601

command: watch 'lsof -e /run/user/1001/gvfs +D data|awk "{print
\$NF}"|sort|uniq -c|sort -nr'

Maybe our versions of PostgreSQL differs? I use "PostgreSQL 9.5.4 on
x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.1.1 20160621 (Red Hat
6.1.1-3), 64-bit"




--
View this message in context: http://postgresql.nabble.com/pg-am-access-in-simple-transaction-tp5931974p5931991.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 11/25/2016 07:04 AM, pinker wrote:
> Adrian Klaver-4 wrote
>> I can not replicate using 50 clients instead of 2000. I suspect either
>> has to do with the extreme number of clients or it is an artifact of
>> from some other process.
>
> And I have replicated it with 50 clients as well... lsof output:
>
> 51 data/base/13328/2601
>
> command: watch 'lsof -e /run/user/1001/gvfs +D data|awk "{print
> \$NF}"|sort|uniq -c|sort -nr'
>
> Maybe our versions of PostgreSQL differs? I use "PostgreSQL 9.5.4 on
> x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.1.1 20160621 (Red Hat
> 6.1.1-3), 64-bit"

No the the versions are the same. It is PEBKAC issue, I was logged in as
wrong user. Running your watch command(minus the -e part which my
version of lsof does not understand) while the Bash script is running
gets the same results. A little digging found that it is used in psql by
describe.c and tab-complete.c:


https://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=src/bin/psql;h=6b1147ce68192ca381ff4b8221b28fc904176190;hb=HEAD

So are other system catalogs, not sure why this one is showing up?

>
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/pg-am-access-in-simple-transaction-tp5931974p5931991.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

pinker <pinker@onet.eu> writes:
> I'm doing simple tests with lsof on data catalog with bash script:

> #!/bin/bash
> for i in {0..2000}
> do
>     psql -U postgres -c '*BEGIN; select pg_sleep(30); COMMIT*'&
> done

> and i measure number of open files and what files are affected by specific
> command.
> Lsof has shown me that the only file that was open during this test was:
> data/base/13328/2601, which is pg_catalog table pg_am.

> Why postgres opens a table that stores information about relation (index)
> access methods, while none index evidently is in use?

I'd put this in the category of "nothing to see here, move along".

The call of pg_sleep() is going to require looking in pg_proc, which
will be an indexed lookup, which will require opening pg_proc indexes,
which will require looking in pg_am.  Your notion that no indexes are
in use is wrong on its face.

Now in most situations, unless you'd just started the database,
all the necessary pages would already be in shared buffers so that
there would be no need for any actual file access.  However, we always
scan pg_am using a seqscan, which is ok because there are so few entries
(and necessary because otherwise we'd have an infinite recursion problem).
Seqscan startup includes probing to see how many pages the table contains,
which is going to require an lseek, which requires an open file.  So I
think that probably explains why you see that file opened and no others.
There are other scenarios where file access would occur, of course, but
this seems like a plausible explanation.

            regards, tom lane