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

Current schema is not specified when querying indexes from PostgreSQL

    Details

      Description

      A single PostgreSQL database may contain multiple schemas. All the indexes defined in the given database are available in the pg_indexes view, defining a schemaname column for separation.

      During the upgrade some indexes are dropped, then the verification phase finds them (as we don't specify the target schema), but the attempt to drop them fails as they don't exist in the current schema.

      Steps to reproduce

      1) install Postgresql 11.x and pgadmin3/pgadmin4 in your local 
      2) in Postgresql, create an admin user with name: admin pw: admin
      3) in Postgresql, create an empty database with 2 schemas in the same database (schema names: dxp1, dxp2)

      #Populate the 2 schemas with default liferay data of vanilla DXP 7.1 (FP17):
      4) do a full startup then shutdown of vanilla DXP 7.1 (FP17) connected to dxp1 using currentSchema parameter:

      jdbc.default.driverClassName=org.postgresql.Driver
       jdbc.default.url=jdbc:postgresql://localhost:5432/DBname?currentSchema=dxp1
       jdbc.default.username=admin
       jdbc.default.password=admin

      5) do the same using the other schema (dxp2)
      optional 6) create a full backup of this database

      #prepare for and execute the upgrade
      7) configure the upgrade tool, pointing to one of the schemas in portal-upgrade-database.properties:

      jdbc.default.url=jdbc:postgresql://localhost:5432/DBname?currentSchema=dxp1

      8) execute a full upgrade
      Actual result: Exceptions complaining that some database indexes do not exist. Example below, full upgrade log attached:

      WARN [main][StartupHelper:140] org.postgresql.util.PSQLException: ERROR: index "ix_35e3e7c6" does not exist

      Expected: the db indexes are handled correctly during the upgrade

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              summer.zhang Summer Zhang
              Reporter:
              istvan.sajtos Istvan Sajtos
              Participants of an Issue:
              Recent user:
              Yunlin "Steven" Sun
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since last comment:
                33 weeks, 3 days ago

                  Packages

                  Version Package
                  7.1.10 DXP FP19
                  7.1.10.5 SP5
                  7.1.X
                  7.2.10 DXP FP7
                  7.2.X
                  7.3.4 CE GA5
                  7.3.10 DXP GA1
                  Master