Re: BUG #15939: Postgres database size is growing due to oraphan objects

On Tue, 6 Aug 2019 at 16:29, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:

> Hi!
>
> On 05/08/2019 11:15, PG Bug reporting form wrote:
> > We have SSO based web login portal. Below is the our architecture
> details.
> >
> > User---> Load balancer(vADC)---> Tomcat Server 7.0.19(Web Server) --->
> Load
> > Balancer (vADC) -->
> > Jboss EAP 6.2(Application Server) --> Postgres 9.3 (Database)
>
> PostgreSQL 9.3 is very old, and is no longer supported. You should
> upgrade, although I doubt it would help with this particular problem.
>
> > Here Postgres will receive user request from jboss server for login. We
> > found that each time when users logged out of system orphan objects are
> > generating which is causing database growth. We checked our database
> tables
> > which having column type is OID/bytea. We found that there are only two
> > tables which is having column type OID/bytea. Surprisingly , These tables
> > dont have any data(Only two rows in each tables). These tables are not
> > generating any orphan objects(LO objects). Orphan objects are stored in
> > pg_largeobjects table. Due to orphan objects, size of pg_largeobject is
> > growing hence db so. We want to implement trigger based deletion of
> orphan
> > objects ( LO objects) . But for that we have to know which application
> > tables are generating orphan objects. How to identify application tables
> > which are generating orphan objects. Kindly guide.
>
> I don't quite understand where those Large Objects are coming from, if
> you're not using them in your application. Perhaps the JDBC driver is
> creating them behind your back? Enabling statement logging might give a
> clue (log_statement=all).
>
> No, we do some things for you but creating Large Objects is not one of them

Dave