Uploaded image for project: 'SonarQube'
  1. SonarQube
  2. SONAR-14303

Primary Key creation is not using the correct schema in pg_namespace (PostgreSql only)

    XMLWordPrintable

    Details

    • Edition:
      Community
    • Production Notes:
      None

      Description

      When starting up SonarQube for the first time on a fresh database, when specifying a specific schema, with the parameter currentSchema, like this:

      sonar.jdbc.url=jdbc:postgresql://localhost:5432/sonarqube?currentSchema=my_schema

      The correct schema is picked up for tables creation, but the primary key constraints are always registered in the table pg_namespace with the public default schema, ignoring the custom my_schema schema specified.

      Later, when performing a SonarQube upgrade, when a PK needs to be deleted, SonarQube migration job is searching for PK constraints in pg_constraint and pg_namespace tables, with this kind of query:

      SELECT conname
      FROM pg_constraint c
      JOIN pg_namespace n on c.connamespace = n.oid
      WHERE c.conrelid = (SELECT oid FROM pg_class WHERE relname LIKE '%table_name')
      AND n.nspname = 'schema_name'
      

      Because PK constraints are all registered on the public schema, it's not possible to retrieve the correct constraint name on a multi-schema environment.

       

      Steps to reproduce:

      Create a new sonarqube database

      • Create a new schema public2 on this database
      • Start SonarQube on this schema with
        sonar.jdbc.url=jdbc:postgresql://localhost:5432/sonarqube?currentSchema=my_schema
      • Notice that nspname is wrong:
        SELECT conname, nspname, *
        FROM pg_constraint c
        JOIN pg_namespace n on c.connamespace = n.oid
        

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              pierre.guillot Pierre Guillot
              Reporter:
              pierre.guillot Pierre Guillot
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Due:
                Created:
                Updated:
                Resolved: