Geek asks and answers

Pg_largeobject access to the hero

I am trying to clean up my postgres database on heroku where some large objects are out of control and I want to delete large objects that are no longer in use.

On my dev machine, I can do:

select distinct loid from pg_largeobject 
Where loid Not In (select id from table )

then I ran:

SELECT lo_unlink(loid)

for each of these identifiers.

But on heroku, I can’t run any highlighting on pg_largeobject, since even

select * from pg_largeobject limit 1;

gives me an error:

ERROR:  permission denied for relation pg_largeobject

Any suggestions on how to get around this, or even even why we don’t have read access on pg_largeobjectheroku?

+5
source share
1 answer

Since PostgreSQL 9.0, no superuser can not access pg_largeobject. This is described in the release notes:

(BLOB) GRANT/REVOKE (KaiGai Kohei)

. .

, - 8.4, , , .

heroku, , pg_dump, , OID , script lo_unlink , , script heroku.

Update:

, psql \dl , , pg_catalog.pg_largeobject_metadata OID :

SELECT oid as "ID",
  pg_catalog.pg_get_userbyid(lomowner) as "Owner",
  pg_catalog.obj_description(oid, 'pg_largeobject') as "Description"
  FROM pg_catalog.pg_largeobject_metadata   ORDER BY oid

, - 9.0+ :

select oid from pg_largeobject_metadata
 Where oid Not In (select id from table )

lomowner , .

+8

More articles:


All Articles