On 7/6/2014 9:06 AM, Tom Lane wrote:
David Wall <d.wall@xxxxxxxxxxxx> writes:
There's one row in pg_largeobject_metadata per large object. The rows in
pg_largeobject represent 2KB "pages" of large objects (so it looks like
your large objects are averaging only 8KB-10KB apiece). The "metadata"
table was added in 9.0 to carry ownership and access permission data for
each large object.
Thanks for that insight.
That metadata table is what first got me when I upgraded from 8.3 to 9.2
when there were all sorts of LO permission errors. I have found that I
get the same sort of issue when I migrate from one system to another,
presumably because the id of the owner has changed, though I use the
same name each time. I've taken to doing the large object permission
assignment after every restore "just to be safe," but it has the
drawback that I often have to set max_locks_per_transaction to a very
high number (40000) for the restore, and then I comment it back out once
it's done and restart. That number is less than the number of LOs by a
long shot, so I'm not sure an optimal number is, but I think at 20000 I
ran out during the re-permissioning of LOs.
It could be that when I restore, the objects take on the permission of
the DB admin user (i.e. postgres) since it has full permissions for
creating everything. But I'd prefer that the objects all take on the
ownership of the DB app user, which of course has more limited
permissions, but otherwise is the user that does all of the
inserts/updates/deletes/selects. I'm not sure if I can create users in
new databases with the same id when I'm using the same name or not.
I think this report confirms something we'd worried about during 9.0
development, which was whether pg_dump wouldn't have issues with
sufficiently many large objects. At the time we'd taught it to handle LOs
as if they were full-fledged database objects, since that was the easiest
way to piggyback on its existing machinery for handling ownership and
permissions; but that's rather expensive for objects that don't really
need all the trappings of, eg, dependency tracking. We'd done some
measurements that seemed to indicate that the overhead wasn't awful for
medium-size numbers of large objects, but I'm not sure we tried it for
millions of 'em.
I guess the good news is that it's only being a bit slow for you and not
falling over completely. Still, it seems like some more work is indicated
in this area.
Yes, it takes 3 hours to do the backup, which is generally okay. It was
just surprising that I could restore in 2 hours <smile>.
David
