Until recently, pg_relation_size used SearchSysCache to locate the
relation to examine, and calculated the file location from that
information. Starting with dbsize.c V1.5 (committed after Beta2),
relation_open(.., AccessShareLock) is used. This is very unfortunate
because it will not allow to observe a table growing while it is
populated, e.g. with a lengthy COPY; pg_relation_size will be blocked.
After reverting to 1.4, everything was fine again.

Can we have this reverted/fixed?

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Until recently, pg_relation_size used SearchSysCache to locate the
> relation to examine, and calculated the file location from that
> information. Starting with dbsize.c V1.5 (committed after Beta2),
> relation_open(.., AccessShareLock) is used. This is very unfortunate
> because it will not allow to observe a table growing while it is
> populated, e.g. with a lengthy COPY; pg_relation_size will be blocked.

Nonsense.

> After reverting to 1.4, everything was fine again.
> Can we have this reverted/fixed?

Can we have the actual problem explained?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
>
>>Until recently, pg_relation_size used SearchSysCache to locate the
>>relation to examine, and calculated the file location from that
>>information. Starting with dbsize.c V1.5 (committed after Beta2),
>>relation_open(.., AccessShareLock) is used. This is very unfortunate
>>because it will not allow to observe a table growing while it is
>>populated, e.g. with a lengthy COPY; pg_relation_size will be blocked.
>
>
> Nonsense.

Ahem.

I'm running Slony against a big replication set. While slon runs COPY
foo(colnamelist) FROM STDIN, I can't execute pg_relation_size(foo_oid).
pg_locks will show that the AccessShareLock on foo is not granted.

Problem is gone with reverted dbsize.c

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Tom Lane wrote:
>> Nonsense.

> Ahem.

> I'm running Slony against a big replication set. While slon runs COPY
> foo(colnamelist) FROM STDIN, I can't execute pg_relation_size(foo_oid).
> pg_locks will show that the AccessShareLock on foo is not granted.

That's only possible if Slony is taking AccessExclusive lock; if so,
your gripe is properly directed to the Slony folks, not to
pg_relation_size which is acting as a good database citizen should.
Certainly a plain COPY command does not take AccessExclusive.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
>
>>Tom Lane wrote:
>>
>>>Nonsense.
>
>
>>Ahem.
>
>
>>I'm running Slony against a big replication set. While slon runs COPY
>>foo(colnamelist) FROM STDIN, I can't execute pg_relation_size(foo_oid).
>>pg_locks will show that the AccessShareLock on foo is not granted.
>
>
> That's only possible if Slony is taking AccessExclusive lock; if so,
> your gripe is properly directed to the Slony folks, not to
> pg_relation_size which is acting as a good database citizen should.

More precisely, it executes TRUNCATE;COPY at the same time; there might
be additional locks to prevent using the table. Still, I see no reason
why pg_relation_size shouldn't continue to use SearchSysCache as id did
for years now. There's no sense in using locking mechanisms on table foo
while reading file system data; pg_class is sufficient to locate the
table's files.

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Tom Lane wrote:
>> That's only possible if Slony is taking AccessExclusive lock; if so,
>> your gripe is properly directed to the Slony folks, not to
>> pg_relation_size which is acting as a good database citizen should.

> More precisely, it executes TRUNCATE;COPY at the same time; there might
> be additional locks to prevent using the table. Still, I see no reason
> why pg_relation_size shouldn't continue to use SearchSysCache as id did
> for years now. There's no sense in using locking mechanisms on table foo
> while reading file system data; pg_class is sufficient to locate the
> table's files.

The fact that the contrib version did things incorrectly for years is
no justification for not fixing it at the time it's taken into the core.
You have to have a lock to ensure that the table even exists, let alone
that you are looking at the right set of disk files.

In the above example, the contrib code would have not done the right
thing at all --- if I'm not mistaken, it would have kept handing back
the size of the original, pre-TRUNCATE file, since the new pg_class
row with the new relfilenode isn't committed yet. So it wouldn't have
done what you wish anyway.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane wrote:

> In the above example, the contrib code would have not done the right
> thing at all --- if I'm not mistaken, it would have kept handing back
> the size of the original, pre-TRUNCATE file, since the new pg_class
> row with the new relfilenode isn't committed yet. So it wouldn't have
> done what you wish anyway.

It wouldn't have worked anyway because it used the Oid to search the
file, not the relfilenode.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Alvaro Herrera wrote:
>
> The problem with the original coding was that it used the table Oid to
> look up the file name, which is wrong. (Test it with a table that has
> been clustered or an index that has been reindexed.)

Um, can't test at the moment. The oldcode used pg_class->relfilnode,
which delivers "Name of the on-disk file of this relation" according to
the docs. What's wrong with that?

regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
>
>>Tom Lane wrote:
>>
>>>That's only possible if Slony is taking AccessExclusive lock; if so,
>>>your gripe is properly directed to the Slony folks, not to
>>>pg_relation_size which is acting as a good database citizen should.
>
>
>>More precisely, it executes TRUNCATE;COPY at the same time; there might
>>be additional locks to prevent using the table. Still, I see no reason
>>why pg_relation_size shouldn't continue to use SearchSysCache as id did
>>for years now. There's no sense in using locking mechanisms on table foo
>>while reading file system data; pg_class is sufficient to locate the
>>table's files.
>
>
> The fact that the contrib version did things incorrectly for years is
> no justification for not fixing it at the time it's taken into the core.
> You have to have a lock to ensure that the table even exists, let alone
> that you are looking at the right set of disk files.

This would require a lock on pg_class, not table foo, no?

> In the above example, the contrib code would have not done the right
> thing at all --- if I'm not mistaken, it would have kept handing back
> the size of the original, pre-TRUNCATE file, since the new pg_class
> row with the new relfilenode isn't committed yet.

Hm, I see the issue. Interesting enough, I *do* see the size growing.
OTOH, when running BEGIN;TRUNCATE against a test table and retrieving
pg_relation_size returns the previous relfilenode and size as expected.

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Andreas Pflug wrote:
> Until recently, pg_relation_size used SearchSysCache to locate the
> relation to examine, and calculated the file location from that
> information. Starting with dbsize.c V1.5 (committed after Beta2),
> relation_open(.., AccessShareLock) is used. This is very unfortunate
> because it will not allow to observe a table growing while it is
> populated, e.g. with a lengthy COPY; pg_relation_size will be blocked.
> After reverting to 1.4, everything was fine again.

The diff:
http://projects.commandprompt.com/pr...hangeset/23120

The problem with the original coding was that it used the table Oid to
look up the file name, which is wrong. (Test it with a table that has
been clustered or an index that has been reindexed.)

We could use a SysCache on filenode, if there was one. Unfortunately I
don't think we have it.

> Can we have this reverted/fixed?

If you can see a way without reintroducing the old bugs, let me know.


--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster