Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-61008

Removal of Documents and Media results in removal of files from DLContent, but not in pg_largeobject table in PostgreSQL system catalog causing uncontrolled growth of the database

    Details

      Description

      Description:
      Client is using PostgreSQL as the database for Liferay 6.2, using DBStore for Documents and Media storage.. They observed that the database size growing rapidly although much content hadn't been added into Liferay. When the client checked their database, they noticed that the majority of space is taken up by PostgreSQL's pg_largeobject table

      run SELECT relname, (relpages * 8) / 1024 AS size_mb FROM pg_class ORDER BY relpages DESC; 

      It seems that Liferay is failing to clean up the related assets in postgres' pg_largeobject table which in turn causes the database to get bloated in spite of the file being deleted. In an out-of-box FileSystemStore, moving documents to the Recycle Bin and emptying the trash results in the removal of the actual file from the filesystem. In the case of DBStore and Postgres, the DLContent entry is removed, but the actual binary that is spanned throughout the pg_largeobject table is not.

      The client has noticed that the most prevalent files are LAR files that are created during temporary storage while performing a staging publish action. Portal-73 is in use in the client's environment and still reproducing the issue.

      Steps to reproduce:
      1. Upload 5.2 GB file to Documents and Media portlet
      2. Verify existence of the lone entry in DLContent
      3. Verify existence of 854272 rows created in the pg_largeobject table in relation to the entry in DLContent
      4. In the Documents and Media portlet, move the file uploaded in step 1 into the Recycle Bin
      5. Empty Recycle Bin
      6. Verify count for DLContent (0 rows returned, when there was originally 1 row)
      7. Verify count from pg_largeobject table

      Expected Result:
      0 rows are in existence, since the reference in DLContent has been deleted.

      Actual Results:
      854272 rows are still in existence although the DLContent record has been. This results in orphaned data.

      Branch:
      f23e57338265a6acfec6f9a5cce0d2ded801c029 - Issue Reproduced (minor error in accessing Documents and Media portlet, but just bring up Multiple Documents upload)

      Master:
      69bf0ebbf0197ef5dcdfea27c3d1825227d9af1d - Issue Reproduced
      fc1f3e8efdcdf47520d5eccc2bb4f64e97aae588 - UNABLE TO BUILD
      161e44a81e5dd209b5eabf5449f674da08e3f834 - UNABLE TO BUILD
      46e80018012ad473ed60589b4f4e3e706ebeaecf - UNABLE TO BUILD

        Attachments

        1. fix.png
          fix.png
          5 kB
        2. reproduce.png
          reproduce.png
          13 kB

          Issue Links

            Activity

              People

              Assignee:
              ian.song Ian Song (Inactive)
              Reporter:
              michael.bowerman Michael Bowerman
              Participants of an Issue:
              Recent user:
              Jose Jimenez
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since last comment:
                4 years, 18 weeks, 5 days ago

                  Packages

                  Version Package
                  6.2.X EE
                  7.0.0 Beta 5