xref: /dports/graphics/qgis/qgis-3.22.3/src/providers/postgres/qgspostgresprovider.cpp

/***************************************************************************
  qgspostgresprovider.cpp  -  QGIS data provider for PostgreSQL/PostGIS layers
                             -------------------
    begin                : 2004/01/07
    copyright            : (C) 2004 by Gary E.Sherman
    email                : sherman at mrcc.com
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/

#include "qgsapplication.h"
#include "qgsfeature.h"
#include "qgsfield.h"
#include "qgsgeometry.h"
#include "qgsmessageoutput.h"
#include "qgsmessagelog.h"
#include "qgsprojectstorageregistry.h"
#include "qgsrectangle.h"
#include "qgscoordinatereferencesystem.h"
#include "qgsxmlutils.h"
#include "qgsvectorlayer.h"
#include "qgsvectorlayerexporter.h"
#include "qgspostgresprovider.h"
#include "qgspostgresconn.h"
#include "qgspostgresconnpool.h"
#include "qgspostgresdataitems.h"
#include "qgspostgresfeatureiterator.h"
#include "qgspostgrestransaction.h"
#include "qgspostgreslistener.h"
#include "qgspostgresprojectstorage.h"
#include "qgspostgresproviderconnection.h"
#include "qgslogger.h"
#include "qgsfeedback.h"
#include "qgssettings.h"
#include "qgsstringutils.h"
#include "qgsjsonutils.h"

#include "qgspostgresprovider.h"
#include "qgsprovidermetadata.h"
#include "qgspostgresproviderconnection.h"

#include <QMessageBox>
#include <QRegularExpression>

const QString QgsPostgresProvider::POSTGRES_KEY = QStringLiteral( "postgres" );
const QString QgsPostgresProvider::POSTGRES_DESCRIPTION = QStringLiteral( "PostgreSQL/PostGIS data provider" );

static const QString EDITOR_WIDGET_STYLES_TABLE = QStringLiteral( "qgis_editor_widget_styles" );

inline qint64 PKINT2FID( qint32 x )
{
  return QgsPostgresUtils::int32pk_to_fid( x );
}

inline qint32 FID2PKINT( qint64 x )
{
  return QgsPostgresUtils::fid_to_int32pk( x );
}

static bool tableExists( QgsPostgresConn &conn, const QString &name )
{
  QgsPostgresResult res( conn.PQexec( "SELECT EXISTS ( SELECT oid FROM pg_catalog.pg_class WHERE relname=" + QgsPostgresConn::quotedValue( name ) + ")" ) );
  return res.PQgetvalue( 0, 0 ).startsWith( 't' );
}

static bool columnExists( QgsPostgresConn &conn, const QString &table, const QString &column )
{
  QgsPostgresResult res( conn.PQexec( "SELECT COUNT(*) FROM information_schema.columns WHERE table_name=" + QgsPostgresConn::quotedValue( table ) + " and column_name=" + QgsPostgresConn::quotedValue( column ) ) );
  return res.PQgetvalue( 0, 0 ).toInt() > 0;
}

QgsPostgresPrimaryKeyType
QgsPostgresProvider::pkType( const QgsField &f ) const
{
  switch ( f.type() )
  {
    case QVariant::LongLong:
      // PostgreSQL doesn't have native "unsigned" types.
      // Unsigned primary keys are emulated by the serial/bigserial
      // pseudo-types, in which autogenerated values are always > 0;
      // however, the database accepts manually inserted 0 and negative values
      // in these fields.
      return PktInt64;

    case QVariant::Int:
      return PktInt;

    default:
      return PktFidMap;
  }
}



QgsPostgresProvider::QgsPostgresProvider( QString const &uri, const ProviderOptions &options,
    QgsDataProvider::ReadFlags flags )
  : QgsVectorDataProvider( uri, options, flags )
  , mShared( new QgsPostgresSharedData )
{

  QgsDebugMsgLevel( QStringLiteral( "URI: %1 " ).arg( uri ), 2 );

  mUri = QgsDataSourceUri( uri );

  // populate members from the uri structure
  mSchemaName = mUri.schema();
  mTableName = mUri.table();
  mGeometryColumn = mUri.geometryColumn();
  mBoundingBoxColumn = mUri.param( "bbox" );
  if ( mBoundingBoxColumn.isEmpty() )
  {
    mBoundingBoxColumn = mGeometryColumn;
  }
  mSqlWhereClause = mUri.sql();
  mRequestedSrid = mUri.srid();
  mRequestedGeomType = mUri.wkbType();

  const QString checkUnicityKey { QStringLiteral( "checkPrimaryKeyUnicity" ) };
  if ( mUri.hasParam( checkUnicityKey ) )
  {

    if ( mUri.param( checkUnicityKey ).compare( QLatin1String( "0" ) )  == 0 )
    {
      mCheckPrimaryKeyUnicity = false;
    }
    else
    {
      mCheckPrimaryKeyUnicity = true;
    }
    if ( mReadFlags & QgsDataProvider::FlagTrustDataSource )
    {
      mCheckPrimaryKeyUnicity = false;
    }
  }

  if ( mSchemaName.isEmpty() && mTableName.startsWith( '(' ) && mTableName.endsWith( ')' ) )
  {
    mIsQuery = true;
    setQuery( mTableName );
    mTableName.clear();
  }
  else
  {
    mIsQuery = false;

    setQuery( ( !mSchemaName.isEmpty() ? quotedIdentifier( mSchemaName ) + '.' : QString() )
              + ( !mTableName.isEmpty() ? quotedIdentifier( mTableName ) : QString() ) );
  }

  mUseEstimatedMetadata = mUri.useEstimatedMetadata();
  if ( mReadFlags & QgsDataProvider::FlagTrustDataSource )
  {
    mUseEstimatedMetadata = true;
  }
  mSelectAtIdDisabled = mUri.selectAtIdDisabled();

  QgsDebugMsgLevel( QStringLiteral( "Connection info is %1" ).arg( mUri.connectionInfo( false ) ), 2 );
  QgsDebugMsgLevel( QStringLiteral( "Geometry column is: %1" ).arg( mGeometryColumn ), 2 );
  QgsDebugMsgLevel( QStringLiteral( "Schema is: %1" ).arg( mSchemaName ), 2 );
  QgsDebugMsgLevel( QStringLiteral( "Table name is: %1" ).arg( mTableName ), 2 );
  QgsDebugMsgLevel( QStringLiteral( "Query is: %1" ).arg( mQuery ), 2 );
  QgsDebugMsgLevel( QStringLiteral( "Where clause is: %1" ).arg( mSqlWhereClause ), 2 );

  // no table/query passed, the provider could be used to get tables
  if ( mQuery.isEmpty() )
  {
    return;
  }

  mConnectionRO = QgsPostgresConn::connectDb( mUri.connectionInfo( false ), true );
  if ( !mConnectionRO )
  {
    return;
  }

  if ( !hasSufficientPermsAndCapabilities() ) // check permissions and set capabilities
  {
    disconnectDb();
    return;
  }

  if ( !getGeometryDetails() ) // gets srid, geometry and data type
  {
    // the table is not a geometry table
    QgsMessageLog::logMessage( tr( "Invalid PostgreSQL layer" ), tr( "PostGIS" ) );
    disconnectDb();
    return;
  }

  // NOTE: mValid would be true after true return from
  // getGeometryDetails, see https://github.com/qgis/QGIS/issues/21807

  if ( mSpatialColType == SctTopoGeometry )
  {
    if ( !getTopoLayerInfo() ) // gets topology name and layer id
    {
      QgsMessageLog::logMessage( tr( "Invalid PostgreSQL topology layer" ), tr( "PostGIS" ) );
      mValid = false;
      disconnectDb();
      return;
    }
  }

  mLayerExtent.setMinimal();

  // set the primary key
  if ( !determinePrimaryKey() )
  {
    QgsMessageLog::logMessage( tr( "PostgreSQL layer has no primary key." ), tr( "PostGIS" ) );
    mValid = false;
    disconnectDb();
    return;
  }

  // Set the PostgreSQL message level so that we don't get the
  // 'there is no transaction in progress' warning.
#ifndef QGISDEBUG
  mConnectionRO->PQexecNR( QStringLiteral( "set client_min_messages to error" ) );
#endif

  setNativeTypes( mConnectionRO->nativeTypes() );

  QString key;
  switch ( mPrimaryKeyType )
  {
    case PktOid:
      key = QStringLiteral( "oid" );
      break;
    case PktTid:
      key = QStringLiteral( "tid" );
      break;
    case PktInt:
      Q_ASSERT( mPrimaryKeyAttrs.size() == 1 );
      Q_ASSERT( mPrimaryKeyAttrs[0] >= 0 && mPrimaryKeyAttrs[0] < mAttributeFields.count() );
      key = mAttributeFields.at( mPrimaryKeyAttrs.at( 0 ) ).name();
      break;
    case PktInt64:
    case PktUint64:
    case PktFidMap:
    {
      QString delim;
      const auto constMPrimaryKeyAttrs = mPrimaryKeyAttrs;
      for ( int idx : constMPrimaryKeyAttrs )
      {
        key += delim + mAttributeFields.at( idx ).name();
        delim = ',';
      }
    }
    break;
    case PktUnknown:
      QgsMessageLog::logMessage( tr( "PostgreSQL layer has unknown primary key type." ), tr( "PostGIS" ) );
      mValid = false;
      break;
  }

  if ( mValid )
  {
    mUri.setKeyColumn( key );
    setDataSourceUri( mUri.uri( false ) );
  }
  else
  {
    disconnectDb();
  }

  mLayerMetadata.setType( QStringLiteral( "dataset" ) );
  mLayerMetadata.setCrs( crs() );
}

QgsPostgresProvider::~QgsPostgresProvider()
{
  disconnectDb();

  QgsDebugMsgLevel( QStringLiteral( "deconstructing." ), 3 );
}


QgsAbstractFeatureSource *QgsPostgresProvider::featureSource() const
{
  return new QgsPostgresFeatureSource( this );
}

QgsPostgresConn *QgsPostgresProvider::connectionRO() const
{
  return mTransaction ? mTransaction->connection() : mConnectionRO;
}

void QgsPostgresProvider::setListening( bool isListening )
{
  if ( !mValid )
    return;

  if ( isListening && !mListener )
  {
    mListener.reset( QgsPostgresListener::create( mUri.connectionInfo( false ) ).release() );
    connect( mListener.get(), &QgsPostgresListener::notify, this, &QgsPostgresProvider::notify );
  }
  else if ( !isListening && mListener )
  {
    disconnect( mListener.get(), &QgsPostgresListener::notify, this, &QgsPostgresProvider::notify );
    mListener.reset();
  }
}

void QgsPostgresProvider::reloadProviderData()
{
  mShared->setFeaturesCounted( -1 );
  mLayerExtent.setMinimal();
}


QgsPostgresConn *QgsPostgresProvider::connectionRW()
{
  if ( mTransaction )
  {
    return mTransaction->connection();
  }
  else if ( !mConnectionRW )
  {
    mConnectionRW = QgsPostgresConn::connectDb( mUri.connectionInfo( false ), false );
  }
  return mConnectionRW;
}

QgsTransaction *QgsPostgresProvider::transaction() const
{
  return mTransaction;
}

QString QgsPostgresProvider::providerKey()
{
  return POSTGRES_KEY;
}

void QgsPostgresProvider::setTransaction( QgsTransaction *transaction )
{
  // static_cast since layers cannot be added to a transaction of a non-matching provider
  mTransaction = static_cast<QgsPostgresTransaction *>( transaction );
}

void QgsPostgresProvider::disconnectDb()
{
  if ( mConnectionRO )
  {
    mConnectionRO->unref();
    mConnectionRO = nullptr;
  }

  if ( mConnectionRW )
  {
    mConnectionRW->unref();
    mConnectionRW = nullptr;
  }
}

QString QgsPostgresProvider::quotedByteaValue( const QVariant &value )
{
  if ( value.isNull() )
    return QStringLiteral( "NULL" );

  const QByteArray ba = value.toByteArray();
  const unsigned char *buf = reinterpret_cast< const unsigned char * >( ba.constData() );
  QString param;
  param.reserve( ba.length() * 4 );
  for ( int i = 0; i < ba.length(); ++i )
  {
    param += QStringLiteral( "\\%1" ).arg( static_cast< int >( buf[i] ), 3, 8, QChar( '0' ) );
  }
  return QStringLiteral( "decode('%1','escape')" ).arg( param );
}

QString QgsPostgresProvider::storageType() const
{
  return QStringLiteral( "PostgreSQL database with PostGIS extension" );
}

QgsFeatureIterator QgsPostgresProvider::getFeatures( const QgsFeatureRequest &request ) const
{
  if ( !mValid )
  {
    QgsMessageLog::logMessage( tr( "Read attempt on an invalid PostgreSQL data source" ), tr( "PostGIS" ) );
    return QgsFeatureIterator();
  }

  QgsPostgresFeatureSource *featureSrc = static_cast<QgsPostgresFeatureSource *>( featureSource() );
  return QgsFeatureIterator( new QgsPostgresFeatureIterator( featureSrc, true, request ) );
}



QString QgsPostgresProvider::pkParamWhereClause( int offset, const char *alias ) const
{
  QString whereClause;

  QString aliased;
  if ( alias ) aliased = QStringLiteral( "%1." ).arg( alias );

  switch ( mPrimaryKeyType )
  {
    case PktTid:
      whereClause = QStringLiteral( "%2ctid=$%1" ).arg( offset ).arg( aliased );
      break;

    case PktOid:
      whereClause = QStringLiteral( "%2oid=$%1" ).arg( offset ).arg( aliased );
      break;

    case PktInt:
      Q_ASSERT( mPrimaryKeyAttrs.size() == 1 );
      whereClause = QStringLiteral( "%3%1=$%2" ).arg( quotedIdentifier( field( mPrimaryKeyAttrs[0] ).name() ) ).arg( offset ).arg( aliased );
      break;

    case PktInt64:
    case PktUint64:
    case PktFidMap:
    {
      QString delim;
      for ( int i = 0; i < mPrimaryKeyAttrs.size(); i++ )
      {
        int idx = mPrimaryKeyAttrs[i];
        QgsField fld = field( idx );

        whereClause += delim + QStringLiteral( "%3%1=$%2" ).arg( connectionRO()->fieldExpressionForWhereClause( fld ) ).arg( offset++ ).arg( aliased );
        delim = QStringLiteral( " AND " );
      }
    }
    break;

    case PktUnknown:
      Q_ASSERT( !"FAILURE: Primary key unknown" );
      whereClause = QStringLiteral( "NULL" );
      break;
  }

  if ( !mSqlWhereClause.isEmpty() )
  {
    if ( !whereClause.isEmpty() )
      whereClause += QLatin1String( " AND " );

    whereClause += '(' + mSqlWhereClause + ')';
  }

  return whereClause;
}

void QgsPostgresProvider::appendPkParams( QgsFeatureId featureId, QStringList &params ) const
{
  switch ( mPrimaryKeyType )
  {
    case PktOid:
      params << QString::number( featureId );
      break;

    case PktInt:
      params << QString::number( FID2PKINT( featureId ) );
      break;

    case PktTid:
      params << QStringLiteral( "'(%1,%2)'" ).arg( FID_TO_NUMBER( featureId ) >> 16 ).arg( FID_TO_NUMBER( featureId ) & 0xffff );
      break;

    case PktInt64:
    case PktUint64:
    case PktFidMap:
    {
      QVariantList pkVals = mShared->lookupKey( featureId );
      if ( !pkVals.isEmpty() )
      {
        Q_ASSERT( pkVals.size() == mPrimaryKeyAttrs.size() );
      }

      for ( int i = 0; i < mPrimaryKeyAttrs.size(); i++ )
      {
        if ( i < pkVals.size() )
        {
          params << pkVals[i].toString();
        }
        else
        {
          QgsDebugMsg( QStringLiteral( "FAILURE: Key value %1 for feature %2 not found." ).arg( mPrimaryKeyAttrs[i] ).arg( featureId ) );
          params << QStringLiteral( "NULL" );
        }
      }

      QgsDebugMsgLevel( QStringLiteral( "keys params: %1" ).arg( params.join( "; " ) ), 2 );
    }
    break;

    case PktUnknown:
      Q_ASSERT( !"FAILURE: Primary key unknown" );
      break;
  }
}


QString QgsPostgresProvider::whereClause( QgsFeatureId featureId ) const
{
  return QgsPostgresUtils::whereClause( featureId, mAttributeFields, connectionRO(), mPrimaryKeyType, mPrimaryKeyAttrs, mShared );
}

QString QgsPostgresProvider::whereClause( QgsFeatureIds featureIds ) const
{
  return QgsPostgresUtils::whereClause( featureIds, mAttributeFields, connectionRO(), mPrimaryKeyType, mPrimaryKeyAttrs, mShared );
}


QString QgsPostgresUtils::whereClause( QgsFeatureId featureId, const QgsFields &fields, QgsPostgresConn *conn, QgsPostgresPrimaryKeyType pkType, const QList<int> &pkAttrs, const std::shared_ptr<QgsPostgresSharedData> &sharedData )
{
  QString whereClause;

  switch ( pkType )
  {
    case PktTid:
      whereClause = QStringLiteral( "ctid='(%1,%2)'" )
                    .arg( FID_TO_NUMBER( featureId ) >> 16 )
                    .arg( FID_TO_NUMBER( featureId ) & 0xffff );
      break;

    case PktOid:
      whereClause = QStringLiteral( "oid=%1" ).arg( featureId );
      break;

    case PktInt:
      Q_ASSERT( pkAttrs.size() == 1 );
      whereClause = QStringLiteral( "%1=%2" ).arg( QgsPostgresConn::quotedIdentifier( fields.at( pkAttrs[0] ).name() ) ).arg( FID2PKINT( featureId ) );
      break;

    case PktInt64:
    case PktUint64:
    {
      Q_ASSERT( pkAttrs.size() == 1 );
      QVariantList pkVals = sharedData->lookupKey( featureId );
      if ( !pkVals.isEmpty() )
      {
        QgsField fld = fields.at( pkAttrs[0] );
        whereClause = conn->fieldExpression( fld );
        if ( !pkVals[0].isNull() )
          whereClause += '=' + pkVals[0].toString();
        else
          whereClause += QLatin1String( " IS NULL" );
      }
    }
    break;

    case PktFidMap:
    {
      QVariantList pkVals = sharedData->lookupKey( featureId );
      if ( !pkVals.isEmpty() )
      {
        Q_ASSERT( pkVals.size() == pkAttrs.size() );

        QString delim;
        for ( int i = 0; i < pkAttrs.size(); i++ )
        {
          int idx = pkAttrs[i];
          QgsField fld = fields.at( idx );

          whereClause += delim + conn->fieldExpressionForWhereClause( fld, pkVals[i].type() );
          if ( pkVals[i].isNull() )
            whereClause += QLatin1String( " IS NULL" );
          else
            whereClause += '=' + QgsPostgresConn::quotedValue( pkVals[i] ); // remove toString as it must be handled by quotedValue function

          delim = QStringLiteral( " AND " );
        }
      }
      else
      {
        QgsDebugMsg( QStringLiteral( "FAILURE: Key values for feature %1 not found." ).arg( featureId ) );
        whereClause = QStringLiteral( "NULL" );
      }
    }
    break;

    case PktUnknown:
      Q_ASSERT( !"FAILURE: Primary key unknown" );
      whereClause = QStringLiteral( "NULL" );
      break;
  }

  return whereClause;
}

QString QgsPostgresUtils::whereClause( const QgsFeatureIds &featureIds, const QgsFields &fields, QgsPostgresConn *conn, QgsPostgresPrimaryKeyType pkType, const QList<int> &pkAttrs, const std::shared_ptr<QgsPostgresSharedData> &sharedData )
{
  auto lookupKeyWhereClause = [ = ]
  {
    if ( featureIds.isEmpty() )
      return QString();

    //simple primary key, so prefer to use an "IN (...)" query. These are much faster then multiple chained ...OR... clauses
    QString delim;
    QString expr = QStringLiteral( "%1 IN (" ).arg( QgsPostgresConn::quotedIdentifier( fields.at( pkAttrs[0] ).name() ) );

    for ( const QgsFeatureId featureId : std::as_const( featureIds ) )
    {
      const QVariantList pkVals = sharedData->lookupKey( featureId );
      if ( !pkVals.isEmpty() )
      {
        expr += delim + QgsPostgresConn::quotedValue( pkVals.at( 0 ) );
        delim = ',';
      }
    }
    expr += ')';

    return expr;
  };

  switch ( pkType )
  {
    case PktOid:
    case PktInt:
    {
      QString expr;

      //simple primary key, so prefer to use an "IN (...)" query. These are much faster then multiple chained ...OR... clauses
      if ( !featureIds.isEmpty() )
      {
        QString delim;
        expr = QStringLiteral( "%1 IN (" ).arg( ( pkType == PktOid ? QStringLiteral( "oid" ) : QgsPostgresConn::quotedIdentifier( fields.at( pkAttrs[0] ).name() ) ) );

        for ( const QgsFeatureId featureId : std::as_const( featureIds ) )
        {
          expr += delim + FID_TO_STRING( ( pkType == PktOid ? featureId : FID2PKINT( featureId ) ) );
          delim = ',';
        }
        expr += ')';
      }

      return expr;
    }
    case PktInt64:
    case PktUint64:
      return lookupKeyWhereClause();

    case PktFidMap:
    case PktTid:
    case PktUnknown:
    {
      // on simple string primary key we can use IN
      if ( pkType == PktFidMap && pkAttrs.count() == 1 && fields.at( pkAttrs[0] ).type() == QVariant::String )
        return lookupKeyWhereClause();

      //complex primary key, need to build up where string
      QStringList whereClauses;
      for ( const QgsFeatureId featureId : std::as_const( featureIds ) )
      {
        whereClauses << whereClause( featureId, fields, conn, pkType, pkAttrs, sharedData );
      }
      return whereClauses.isEmpty() ? QString() : whereClauses.join( QLatin1String( " OR " ) ).prepend( '(' ).append( ')' );
    }
  }
  return QString(); //avoid warning
}

QString QgsPostgresUtils::andWhereClauses( const QString &c1, const QString &c2 )
{
  if ( c1.isEmpty() )
    return c2;
  if ( c2.isEmpty() )
    return c1;

  return QStringLiteral( "(%1) AND (%2)" ).arg( c1, c2 );
}

void QgsPostgresUtils::replaceInvalidXmlChars( QString &xml )
{
  static const QRegularExpression replaceRe { QStringLiteral( "([\x00-\x08\x0B-\x1F\x7F])" ) };
  QRegularExpressionMatchIterator it {replaceRe.globalMatch( xml ) };
  while ( it.hasNext() )
  {
    const QRegularExpressionMatch match { it.next() };
    const QChar c { match.captured( 1 ).at( 0 ) };
    xml.replace( c, QStringLiteral( "UTF-8[%1]" ).arg( c.unicode() ) );
  }
}

void QgsPostgresUtils::restoreInvalidXmlChars( QString &xml )
{
  static const QRegularExpression replaceRe { QStringLiteral( R"raw(UTF-8\[(\d+)\])raw" ) };
  QRegularExpressionMatchIterator it {replaceRe.globalMatch( xml ) };
  while ( it.hasNext() )
  {
    const QRegularExpressionMatch match { it.next() };
    bool ok;
    const ushort code { match.captured( 1 ).toUShort( &ok ) };
    if ( ok )
    {
      xml.replace( QStringLiteral( "UTF-8[%1]" ).arg( code ), QChar( code ) );
    }
  }
}

QString QgsPostgresProvider::filterWhereClause() const
{
  QString where;
  QString delim = QStringLiteral( " WHERE " );

  if ( !mSqlWhereClause.isEmpty() )
  {
    where += delim + '(' + mSqlWhereClause + ')';
    delim = QStringLiteral( " AND " );
  }

  if ( !mRequestedSrid.isEmpty() && ( mRequestedSrid != mDetectedSrid || mRequestedSrid.toInt() == 0 ) )
  {
    where += delim + QStringLiteral( "%1(%2%3)=%4" )
             .arg( connectionRO()->majorVersion() < 2 ? "srid" : "st_srid",
                   quotedIdentifier( mGeometryColumn ),
                   mSpatialColType == SctGeography ? "::geography" : "",
                   mRequestedSrid );
    delim = QStringLiteral( " AND " );
  }

  if ( mRequestedGeomType != QgsWkbTypes::Unknown && mRequestedGeomType != mDetectedGeomType )
  {
    where += delim + QgsPostgresConn::postgisTypeFilter( mGeometryColumn, ( QgsWkbTypes::Type )mRequestedGeomType, mSpatialColType == SctGeography );
    delim = QStringLiteral( " AND " );
  }

  return where;
}

void QgsPostgresProvider::setExtent( QgsRectangle &newExtent )
{
  mLayerExtent.setXMaximum( newExtent.xMaximum() );
  mLayerExtent.setXMinimum( newExtent.xMinimum() );
  mLayerExtent.setYMaximum( newExtent.yMaximum() );
  mLayerExtent.setYMinimum( newExtent.yMinimum() );
}

/**
 * Returns the feature type
 */
QgsWkbTypes::Type QgsPostgresProvider::wkbType() const
{
  return mRequestedGeomType != QgsWkbTypes::Unknown ? mRequestedGeomType : mDetectedGeomType;
}

QgsLayerMetadata QgsPostgresProvider::layerMetadata() const
{
  return mLayerMetadata;
}

QgsField QgsPostgresProvider::field( int index ) const
{
  if ( index < 0 || index >= mAttributeFields.count() )
  {
    QgsMessageLog::logMessage( tr( "FAILURE: Field %1 not found." ).arg( index ), tr( "PostGIS" ) );
    throw PGFieldNotFound();
  }

  return mAttributeFields.at( index );
}

QgsFields QgsPostgresProvider::fields() const
{
  return mAttributeFields;
}

QString QgsPostgresProvider::dataComment() const
{
  return mDataComment;
}


//! \todo XXX Perhaps this should be promoted to QgsDataProvider?
QString QgsPostgresProvider::endianString()
{
  switch ( QgsApplication::endian() )
  {
    case QgsApplication::NDR:
      return QStringLiteral( "NDR" );
    case QgsApplication::XDR:
      return QStringLiteral( "XDR" );
    default :
      return QStringLiteral( "Unknown" );
  }
}


struct PGTypeInfo
{
  QString typeName;
  QString typeType;
  QString typeElem;
  int typeLen;
};

bool QgsPostgresProvider::loadFields()
{

  // Clear cached information about enum values support
  mShared->clearSupportsEnumValuesCache();

  QString sql;
  QString attroidsFilter;

  if ( !mIsQuery )
  {
    QgsDebugMsgLevel( QStringLiteral( "Loading fields for table %1" ).arg( mTableName ), 2 );

    // Get the table description
    sql = QStringLiteral( "SELECT description FROM pg_description WHERE objoid=regclass(%1)::oid AND objsubid=0" ).arg( quotedValue( mQuery ) );
    QgsPostgresResult tresult( connectionRO()->PQexec( sql ) );
    if ( tresult.PQntuples() > 0 )
    {
      mDataComment = tresult.PQgetvalue( 0, 0 );
      mLayerMetadata.setAbstract( mDataComment );
    }
  }

  // Populate the field vector for this layer. The field vector contains
  // field name, type, length, and precision (if numeric)
  sql = QStringLiteral( "SELECT * FROM %1 LIMIT 0" ).arg( mQuery );

  QgsPostgresResult result( connectionRO()->PQexec( sql ) );

  QMap<Oid, QMap<int, QString> > fmtFieldTypeMap, descrMap, defValMap, identityMap, generatedMap;
  QMap<Oid, QMap<int, Oid> > attTypeIdMap;
  QMap<Oid, QMap<int, bool> > notNullMap, uniqueMap;
  if ( result.PQnfields() > 0 )
  {
    // Collect attribiute oids
    QSet<Oid> attroids;
    for ( int i = 0; i < result.PQnfields(); i++ )
    {
      Oid attroid = result.PQftype( i );
      attroids.insert( attroid );
    }

    // Collect table oids
    QSet<Oid> tableoids;
    for ( int i = 0; i < result.PQnfields(); i++ )
    {
      Oid tableoid = result.PQftable( i );
      if ( tableoid > 0 )
      {
        tableoids.insert( tableoid );
      }
    }

    if ( !tableoids.isEmpty() )
    {
      QStringList tableoidsList;
      const auto constTableoids = tableoids;
      for ( Oid tableoid : constTableoids )
      {
        tableoidsList.append( QString::number( tableoid ) );
      }

      QString tableoidsFilter = '(' + tableoidsList.join( QLatin1Char( ',' ) ) + ')';

      // Collect formatted field types
      sql = QStringLiteral(
              "SELECT attrelid, attnum, pg_catalog.format_type(atttypid,atttypmod), pg_catalog.col_description(attrelid,attnum), pg_catalog.pg_get_expr(adbin,adrelid), atttypid, attnotnull::int, indisunique::int%1%2"
              " FROM pg_attribute"
              " LEFT OUTER JOIN pg_attrdef ON attrelid=adrelid AND attnum=adnum"

              // find unique constraints if present. Text cast required to handle int2vector comparison. Distinct required as multiple unique constraints may exist
              " LEFT OUTER JOIN ( SELECT DISTINCT indrelid, indkey, indisunique FROM pg_index WHERE indisunique ) uniq ON attrelid=indrelid AND attnum::text=indkey::text "

              " WHERE attrelid IN %3"
            ).arg( connectionRO()->pgVersion() >= 100000 ? QStringLiteral( ", attidentity" ) : QString(),
                   connectionRO()->pgVersion() >= 120000 ? QStringLiteral( ", attgenerated" ) : QString(),
                   tableoidsFilter );

      QgsPostgresResult fmtFieldTypeResult( connectionRO()->PQexec( sql ) );
      for ( int i = 0; i < fmtFieldTypeResult.PQntuples(); ++i )
      {
        Oid attrelid = fmtFieldTypeResult.PQgetvalue( i, 0 ).toUInt();
        int attnum = fmtFieldTypeResult.PQgetvalue( i, 1 ).toInt(); // Int2
        QString formatType = fmtFieldTypeResult.PQgetvalue( i, 2 );
        QString descr = fmtFieldTypeResult.PQgetvalue( i, 3 );
        QString defVal = fmtFieldTypeResult.PQgetvalue( i, 4 );
        Oid attType = fmtFieldTypeResult.PQgetvalue( i, 5 ).toUInt();
        bool attNotNull = fmtFieldTypeResult.PQgetvalue( i, 6 ).toInt();
        bool uniqueConstraint = fmtFieldTypeResult.PQgetvalue( i, 7 ).toInt();
        QString attIdentity = connectionRO()->pgVersion() >= 100000 ? fmtFieldTypeResult.PQgetvalue( i, 8 ) : " ";

        // On PostgreSQL 12, the field pg_attribute.attgenerated is always filled with "s" if the field is generated,
        // with the possibility of other values in future releases. This indicates "STORED" generated fields.
        // The documentation for version 12 indicates that there is a future possibility of supporting virtual
        // generated values, which might make possible to have values other than "s" on pg_attribute.attgenerated,
        // which should be unimportant for QGIS if the user still won't be able to overwrite the column value.
        // See https://www.postgresql.org/docs/12/ddl-generated-columns.html
        QString attGenerated = connectionRO()->pgVersion() >= 120000 ? fmtFieldTypeResult.PQgetvalue( i, 9 ) : "";
        fmtFieldTypeMap[attrelid][attnum] = formatType;
        descrMap[attrelid][attnum] = descr;
        defValMap[attrelid][attnum] = defVal;
        attTypeIdMap[attrelid][attnum] = attType;
        notNullMap[attrelid][attnum] = attNotNull;
        uniqueMap[attrelid][attnum] = uniqueConstraint;
        identityMap[attrelid][attnum] = attIdentity.isEmpty() ? " " : attIdentity;
        generatedMap[attrelid][attnum] = attGenerated.isEmpty() ? QString() : defVal;

        // Also include atttype oid from pg_attribute, because PQnfields only returns basic type for for domains
        attroids.insert( attType );
      }
    }

    // Prepare filter for fetching pg_type info
    if ( !attroids.isEmpty() )
    {
      QStringList attroidsList;
      for ( Oid attroid : std::as_const( attroids ) )
      {
        attroidsList.append( QString::number( attroid ) );
      }
      attroidsFilter = QStringLiteral( "WHERE oid in (%1)" ).arg( attroidsList.join( ',' ) );
    }
  }

  // Collect type info
  sql = QStringLiteral( "SELECT oid,typname,typtype,typelem,typlen FROM pg_type %1" ).arg( attroidsFilter );
  QgsPostgresResult typeResult( connectionRO()->PQexec( sql ) );

  QMap<Oid, PGTypeInfo> typeMap;
  for ( int i = 0; i < typeResult.PQntuples(); ++i )
  {
    PGTypeInfo typeInfo =
    {
      /* typeName = */ typeResult.PQgetvalue( i, 1 ),
      /* typeType = */ typeResult.PQgetvalue( i, 2 ),
      /* typeElem = */ typeResult.PQgetvalue( i, 3 ),
      /* typeLen = */ typeResult.PQgetvalue( i, 4 ).toInt()
    };
    typeMap.insert( typeResult.PQgetvalue( i, 0 ).toUInt(), typeInfo );
  }

  QSet<QString> fields;
  mAttributeFields.clear();
  mIdentityFields.clear();
  for ( int i = 0; i < result.PQnfields(); i++ )
  {
    QString fieldName = result.PQfname( i );
    if ( fieldName == mGeometryColumn )
      continue;

    Oid fldtyp = result.PQftype( i );
    int fldMod = result.PQfmod( i );
    int fieldPrec = 0;
    Oid tableoid = result.PQftable( i );
    int attnum = result.PQftablecol( i );
    Oid atttypid = attTypeIdMap[tableoid][attnum];

    const PGTypeInfo &typeInfo = typeMap.value( fldtyp );
    QString fieldTypeName = typeInfo.typeName;
    QString fieldTType = typeInfo.typeType;
    int fieldSize = typeInfo.typeLen;

    bool isDomain = ( typeMap.value( atttypid ).typeType == QLatin1String( "d" ) );

    QString formattedFieldType = fmtFieldTypeMap[tableoid][attnum];
    QString originalFormattedFieldType = formattedFieldType;
    if ( isDomain )
    {
      // get correct formatted field type for domain
      sql = QStringLiteral( "SELECT format_type(%1, %2)" ).arg( fldtyp ).arg( fldMod );
      QgsPostgresResult fmtFieldModResult( connectionRO()->PQexec( sql ) );
      if ( fmtFieldModResult.PQntuples() > 0 )
      {
        formattedFieldType = fmtFieldModResult.PQgetvalue( 0, 0 );
      }
    }

    QString fieldComment = descrMap[tableoid][attnum];

    QVariant::Type fieldType = QVariant::Invalid;
    QVariant::Type fieldSubType = QVariant::Invalid;

    if ( fieldTType == QLatin1String( "b" ) )
    {
      bool isArray = fieldTypeName.startsWith( '_' );

      if ( isArray )
        fieldTypeName = fieldTypeName.mid( 1 );

      if ( fieldTypeName == QLatin1String( "int8" ) || fieldTypeName == QLatin1String( "serial8" ) )
      {
        fieldType = QVariant::LongLong;
        fieldSize = -1;
        fieldPrec = 0;
      }
      else if ( fieldTypeName == QLatin1String( "int2" ) || fieldTypeName == QLatin1String( "int4" ) ||
                fieldTypeName == QLatin1String( "oid" ) || fieldTypeName == QLatin1String( "serial" ) )
      {
        fieldType = QVariant::Int;
        fieldSize = -1;
        fieldPrec = 0;
      }
      else if ( fieldTypeName == QLatin1String( "real" ) || fieldTypeName == QLatin1String( "double precision" ) ||
                fieldTypeName == QLatin1String( "float4" ) || fieldTypeName == QLatin1String( "float8" ) )
      {
        fieldType = QVariant::Double;
        fieldSize = -1;
        fieldPrec = 0;
      }
      else if ( fieldTypeName == QLatin1String( "numeric" ) )
      {
        fieldType = QVariant::Double;

        if ( formattedFieldType == QLatin1String( "numeric" ) || formattedFieldType.isEmpty() )
        {
          fieldSize = -1;
          fieldPrec = 0;
        }
        else
        {
          const QRegularExpression re( QRegularExpression::anchoredPattern( QStringLiteral( "numeric\\((\\d+),(\\d+)\\)" ) ) );
          const QRegularExpressionMatch match = re.match( formattedFieldType );
          if ( match.hasMatch() )
          {
            fieldSize = match.captured( 1 ).toInt();
            fieldPrec = match.captured( 2 ).toInt();
          }
          else if ( formattedFieldType != QLatin1String( "numeric" ) )
          {
            QgsMessageLog::logMessage( tr( "Unexpected formatted field type '%1' for field %2" )
                                       .arg( formattedFieldType,
                                             fieldName ),
                                       tr( "PostGIS" ) );
            fieldSize = -1;
            fieldPrec = 0;
          }
        }
      }
      else if ( fieldTypeName == QLatin1String( "varchar" ) )
      {
        fieldType = QVariant::String;

        const QRegularExpression re( QRegularExpression::anchoredPattern( "character varying\\((\\d+)\\)" ) );
        const QRegularExpressionMatch match = re.match( formattedFieldType );
        if ( match.hasMatch() )
        {
          fieldSize = match.captured( 1 ).toInt();
        }
        else
        {
          fieldSize = -1;
        }
      }
      else if ( fieldTypeName == QLatin1String( "date" ) )
      {
        fieldType = QVariant::Date;
        fieldSize = -1;
      }
      else if ( fieldTypeName == QLatin1String( "time" ) )
      {
        fieldType = QVariant::Time;
        fieldSize = -1;
      }
      else if ( fieldTypeName == QLatin1String( "timestamp" ) || fieldTypeName == QLatin1String( "timestamptz" ) )
      {
        fieldType = QVariant::DateTime;
        fieldSize = -1;
      }
      else if ( fieldTypeName == QLatin1String( "bytea" ) )
      {
        fieldType = QVariant::ByteArray;
        fieldSize = -1;
      }
      else if ( fieldTypeName == QLatin1String( "text" ) ||
                fieldTypeName == QLatin1String( "citext" ) ||
                fieldTypeName == QLatin1String( "geometry" ) ||
                fieldTypeName == QLatin1String( "geography" ) ||
                fieldTypeName == QLatin1String( "inet" ) ||
                fieldTypeName == QLatin1String( "cidr" ) ||
                fieldTypeName == QLatin1String( "macaddr" ) ||
                fieldTypeName == QLatin1String( "macaddr8" ) ||
                fieldTypeName == QLatin1String( "money" ) ||
                fieldTypeName == QLatin1String( "ltree" ) ||
                fieldTypeName == QLatin1String( "uuid" ) ||
                fieldTypeName == QLatin1String( "xml" ) ||
                fieldTypeName.startsWith( QLatin1String( "time" ) ) ||
                fieldTypeName.startsWith( QLatin1String( "date" ) ) )
      {
        fieldType = QVariant::String;
        fieldSize = -1;
      }
      else if ( fieldTypeName == QLatin1String( "bpchar" ) )
      {
        // although postgres internally uses "bpchar", this is exposed to users as character in postgres
        fieldTypeName = QStringLiteral( "character" );

        fieldType = QVariant::String;

        const QRegularExpression re( QRegularExpression::anchoredPattern( "character\\((\\d+)\\)" ) );
        const QRegularExpressionMatch match = re.match( formattedFieldType );
        if ( match.hasMatch() )
        {
          fieldSize = match.captured( 1 ).toInt();
        }
        else
        {
          QgsDebugMsg( QStringLiteral( "Unexpected formatted field type '%1' for field %2" )
                       .arg( formattedFieldType,
                             fieldName ) );
          fieldSize = -1;
          fieldPrec = 0;
        }
      }
      else if ( fieldTypeName == QLatin1String( "char" ) )
      {
        fieldType = QVariant::String;

        const QRegularExpression re( QRegularExpression::anchoredPattern( QStringLiteral( "char\\((\\d+)\\)" ) ) );
        const QRegularExpressionMatch match = re.match( formattedFieldType );
        if ( match.hasMatch() )
        {
          fieldSize = match.captured( 1 ).toInt();
        }
        else
        {
          QgsMessageLog::logMessage( tr( "Unexpected formatted field type '%1' for field %2" )
                                     .arg( formattedFieldType,
                                           fieldName ) );
          fieldSize = -1;
          fieldPrec = 0;
        }
      }
      else if ( fieldTypeName == QLatin1String( "hstore" ) ||  fieldTypeName == QLatin1String( "json" ) || fieldTypeName == QLatin1String( "jsonb" ) )
      {
        fieldType = QVariant::Map;
        fieldSubType = QVariant::String;
        fieldSize = -1;
      }
      else if ( fieldTypeName == QLatin1String( "bool" ) )
      {
        // enum
        fieldType = QVariant::Bool;
        fieldSize = -1;
      }
      // PG 12 returns "name" type for some system table fields (e.g. information_schema.tables)
      else if ( fieldTypeName == QLatin1String( "name" ) )
      {
        fieldSubType = QVariant::String;
        fieldSize = 63;
      }
      else
      {
        // be tolerant in case of views: this might be a field used as a key
        const QgsPostgresProvider::Relkind type = relkind();
        if ( ( type == Relkind::View || type == Relkind::MaterializedView ) && parseUriKey( mUri.keyColumn( ) ).contains( fieldName ) )
        {
          // Assume it is convertible to text
          fieldType = QVariant::String;
          fieldSize = -1;
        }
        else if ( fieldTypeName == QLatin1String( "unknown" ) )
        {
          // Assume it is convertible to text
          fieldType = QVariant::String;
          fieldSize = -1;
        }
        else
        {
          QgsMessageLog::logMessage( tr( "Field %1 ignored, because of unsupported type %2" ).arg( fieldName, fieldTType ), tr( "PostGIS" ) );
          continue;
        }
      }

      if ( isArray )
      {
        fieldTypeName = '_' + fieldTypeName;
        fieldSubType = fieldType;
        fieldType = ( fieldType == QVariant::String ? QVariant::StringList : QVariant::List );
        fieldSize = -1;
      }
    }
    else if ( fieldTType == QLatin1String( "e" ) )
    {
      // enum
      fieldType = QVariant::String;
      fieldSize = -1;
    }
    else
    {
      QgsMessageLog::logMessage( tr( "Field %1 ignored, because of unsupported type %2" ).arg( fieldName, fieldTType ), tr( "PostGIS" ) );
      continue;
    }

    if ( fields.contains( fieldName ) )
    {
      QgsMessageLog::logMessage( tr( "Duplicate field %1 found\n" ).arg( fieldName ), tr( "PostGIS" ) );
      // In case of read-only query layers we can safely ignore the issue and rename the duplicated field
      if ( ! mIsQuery )
      {
        return false;
      }
      else
      {
        unsigned short int i = 1;
        while ( i < std::numeric_limits<unsigned short int>::max() )
        {
          const QString newName { QStringLiteral( "%1 (%2)" ).arg( fieldName ).arg( ++i ) };
          if ( ! fields.contains( newName ) )
          {
            fieldName = newName;
            break;
          }
        }
      }
    }

    fields << fieldName;

    if ( isDomain )
    {
      //field was defined using domain, so use domain type name for fieldTypeName
      fieldTypeName = originalFormattedFieldType;
    }

    mAttrPalIndexName.insert( i, fieldName );
    // If this is an identity field with constraints and there is no default, let's look for a sequence:
    // we might have a default value created by a sequence named <table>_<field>_seq
    if ( ! identityMap[tableoid ][ attnum ].isEmpty()
         && notNullMap[tableoid][ attnum ]
         && uniqueMap[tableoid][attnum]
         && defValMap[tableoid][attnum].isEmpty() )
    {
      const QString seqName { mTableName + '_' + fieldName + QStringLiteral( "_seq" ) };
      const QString seqSql = QStringLiteral( "SELECT c.oid "
                                             "  FROM pg_class c "
                                             "  LEFT JOIN pg_namespace n "
                                             "    ON ( n.oid = c.relnamespace ) "
                                             "  WHERE c.relkind = 'S' "
                                             "    AND c.relname = %1 "
                                             "    AND n.nspname = %2" )
                             .arg( quotedValue( seqName ) )
                             .arg( quotedValue( mSchemaName ) );
      QgsPostgresResult seqResult( connectionRO()->PQexec( seqSql ) );
      if ( seqResult.PQntuples() == 1 )
      {
        defValMap[tableoid][attnum] = QStringLiteral( "nextval(%1::regclass)" ).arg( quotedValue( seqName ) );
      }
    }

    mDefaultValues.insert( mAttributeFields.size(), defValMap[tableoid][attnum] );

    const QString generatedValue = generatedMap[tableoid][attnum];
    if ( !generatedValue.isNull() )
      mGeneratedValues.insert( mAttributeFields.size(), generatedValue );

    QgsField newField = QgsField( fieldName, fieldType, fieldTypeName, fieldSize, fieldPrec, fieldComment, fieldSubType );
    newField.setReadOnly( !generatedValue.isNull() );

    QgsFieldConstraints constraints;
    if ( notNullMap[tableoid][attnum] || ( mPrimaryKeyAttrs.size() == 1 && mPrimaryKeyAttrs[0] == i ) || identityMap[tableoid][attnum] != ' ' )
      constraints.setConstraint( QgsFieldConstraints::ConstraintNotNull, QgsFieldConstraints::ConstraintOriginProvider );
    if ( uniqueMap[tableoid][attnum] || ( mPrimaryKeyAttrs.size() == 1 && mPrimaryKeyAttrs[0] == i ) || identityMap[tableoid][attnum] != ' ' )
      constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
    newField.setConstraints( constraints );

    mIdentityFields.insert( mAttributeFields.size(), identityMap[tableoid][attnum][0].toLatin1() );
    mAttributeFields.append( newField );
  }

  setEditorWidgets();

  return true;
}

void QgsPostgresProvider::setEditorWidgets()
{
  if ( ! tableExists( *connectionRO(), EDITOR_WIDGET_STYLES_TABLE ) )
  {
    return;
  }

  QStringList quotedFnames;
  const QStringList fieldNames = mAttributeFields.names();
  for ( const QString &name : fieldNames )
  {
    quotedFnames << quotedValue( name );
  }

  // We expect the table to be created like this:
  //
  // CREATE TABLE qgis_editor_widget_styles (schema_name TEXT NOT NULL, table_name TEXT NOT NULL, field_name TEXT NOT NULL,
  //                                         type TEXT NOT NULL, config TEXT,
  //                                         PRIMARY KEY(schema_name, table_name, field_name));
  const QString sql = QStringLiteral( "SELECT field_name, type, config "
                                      "FROM %1 WHERE schema_name = %2 "
                                      "AND table_name = %3 "
                                      "AND field_name IN ( %4 )" ) .
                      arg( EDITOR_WIDGET_STYLES_TABLE, quotedValue( mSchemaName ),
                           quotedValue( mTableName ), quotedFnames.join( "," ) );
  QgsPostgresResult result( connectionRO()->PQexec( sql ) );
  for ( int i = 0; i < result.PQntuples(); ++i )
  {
    if ( result.PQgetisnull( i, 2 ) ) continue; // config can be null and it's OK

    const QString &configTxt = result.PQgetvalue( i, 2 );
    const QString &type = result.PQgetvalue( i, 1 );
    const QString &fname = result.PQgetvalue( i, 0 );
    QVariantMap config;
    QDomDocument doc;
    if ( doc.setContent( configTxt ) )
    {
      config = QgsXmlUtils::readVariant( doc.documentElement() ).toMap();
    }
    else
    {
      QgsMessageLog::logMessage(
        tr( "Cannot parse widget configuration for field %1.%2.%3\n" )
        .arg( mSchemaName, mTableName, fname ), tr( "PostGIS" )
      );
      continue;
    }

    // Set corresponding editor widget
    for ( auto &field : mAttributeFields )
    {
      if ( field.name() == fname )
      {
        field.setEditorWidgetSetup( QgsEditorWidgetSetup( type, config ) );
        break;
      }
    }
  }
}

bool QgsPostgresProvider::hasSufficientPermsAndCapabilities()
{
  QgsDebugMsgLevel( QStringLiteral( "Checking for permissions on the relation" ), 2 );

  mEnabledCapabilities = QgsVectorDataProvider::Capability::ReloadData;

  QgsPostgresResult testAccess;
  if ( !mIsQuery )
  {
    // Check that we can read from the table (i.e., we have select permission).
    QString sql = QStringLiteral( "SELECT * FROM %1 LIMIT 1" ).arg( mQuery );
    QgsPostgresResult testAccess( connectionRO()->PQexec( sql ) );
    if ( testAccess.PQresultStatus() != PGRES_TUPLES_OK )
    {
      QgsMessageLog::logMessage( tr( "Unable to access the %1 relation.\nThe error message from the database was:\n%2.\nSQL: %3" )
                                 .arg( mQuery,
                                       testAccess.PQresultErrorMessage(),
                                       sql ), tr( "PostGIS" ) );
      return false;
    }

    bool inRecovery = false;

    if ( connectionRO()->pgVersion() >= 90000 )
    {
      testAccess = connectionRO()->PQexec( QStringLiteral( "SELECT pg_is_in_recovery()" ) );
      if ( testAccess.PQresultStatus() != PGRES_TUPLES_OK || testAccess.PQgetvalue( 0, 0 ) == QLatin1String( "t" ) )
      {
        QgsMessageLog::logMessage( tr( "PostgreSQL is still in recovery after a database crash\n(or you are connected to a (read-only) standby server).\nWrite accesses will be denied." ), tr( "PostGIS" ) );
        inRecovery = true;
      }
    }

    // postgres has fast access to features at id (thanks to primary key / unique index)
    // the latter flag is here just for compatibility
    if ( !mSelectAtIdDisabled )
    {
      mEnabledCapabilities |= QgsVectorDataProvider::SelectAtId;
    }

    if ( !inRecovery )
    {
      if ( connectionRO()->pgVersion() >= 80400 )
      {
        sql = QString( "SELECT "
                       "has_table_privilege(%1,'DELETE'),"
                       "has_any_column_privilege(%1,'UPDATE'),"
                       "%2"
                       "has_table_privilege(%1,'INSERT'),"
                       "current_schema()" )
              .arg( quotedValue( mQuery ),
                    mGeometryColumn.isNull()
                    ? QStringLiteral( "'f'," )
                    : QStringLiteral( "has_column_privilege(%1,%2,'UPDATE')," )
                    .arg( quotedValue( mQuery ),
                          quotedValue( mGeometryColumn ) )
                  );
      }
      else
      {
        sql = QString( "SELECT "
                       "has_table_privilege(%1,'DELETE'),"
                       "has_table_privilege(%1,'UPDATE'),"
                       "has_table_privilege(%1,'UPDATE'),"
                       "has_table_privilege(%1,'INSERT'),"
                       "current_schema()" )
              .arg( quotedValue( mQuery ) );
      }

      testAccess = connectionRO()->PQexec( sql );
      if ( testAccess.PQresultStatus() != PGRES_TUPLES_OK )
      {
        QgsMessageLog::logMessage( tr( "Unable to determine table access privileges for the %1 relation.\nThe error message from the database was:\n%2.\nSQL: %3" )
                                   .arg( mQuery,
                                         testAccess.PQresultErrorMessage(),
                                         sql ),
                                   tr( "PostGIS" ) );
        return false;
      }


      if ( testAccess.PQgetvalue( 0, 0 ) == QLatin1String( "t" ) )
      {
        // DELETE
        mEnabledCapabilities |= QgsVectorDataProvider::DeleteFeatures | QgsVectorDataProvider::FastTruncate;
      }

      if ( testAccess.PQgetvalue( 0, 1 ) == QLatin1String( "t" ) )
      {
        // UPDATE
        mEnabledCapabilities |= QgsVectorDataProvider::ChangeAttributeValues;
      }

      if ( testAccess.PQgetvalue( 0, 2 ) == QLatin1String( "t" ) )
      {
        // UPDATE
        mEnabledCapabilities |= QgsVectorDataProvider::ChangeGeometries;
      }

      if ( testAccess.PQgetvalue( 0, 3 ) == QLatin1String( "t" ) )
      {
        // INSERT
        mEnabledCapabilities |= QgsVectorDataProvider::AddFeatures;
      }

      if ( mSchemaName.isEmpty() )
        mSchemaName = testAccess.PQgetvalue( 0, 4 );

      sql = QString( "SELECT 1 FROM pg_class,pg_namespace WHERE "
                     "pg_class.relnamespace=pg_namespace.oid AND "
                     "%3 AND "
                     "relname=%1 AND nspname=%2" )
            .arg( quotedValue( mTableName ),
                  quotedValue( mSchemaName ),
                  connectionRO()->pgVersion() < 80100 ? "pg_get_userbyid(relowner)=current_user" : "pg_has_role(relowner,'MEMBER')" );
      testAccess = connectionRO()->PQexec( sql );
      if ( testAccess.PQresultStatus() == PGRES_TUPLES_OK && testAccess.PQntuples() == 1 )
      {
        mEnabledCapabilities |= QgsVectorDataProvider::AddAttributes | QgsVectorDataProvider::DeleteAttributes | QgsVectorDataProvider::RenameAttributes;
      }
    }
  }
  else
  {
    // Check if the sql is a select query
    if ( !mQuery.startsWith( '(' ) && !mQuery.endsWith( ')' ) )
    {
      QgsMessageLog::logMessage( tr( "The custom query is not a select query." ), tr( "PostGIS" ) );
      return false;
    }

    // get a new alias for the subquery
    int index = 0;
    QString alias;
    QRegularExpression regex;
    do
    {
      alias = QStringLiteral( "subQuery_%1" ).arg( QString::number( index++ ) );
      QString pattern = QStringLiteral( "(\\\"?)%1\\1" ).arg( QgsStringUtils::qRegExpEscape( alias ) );
      regex.setPattern( pattern );
      regex.setPatternOptions( QRegularExpression::CaseInsensitiveOption );
    }
    while ( mQuery.contains( regex ) );

    // convert the custom query into a subquery
    setQuery( QStringLiteral( "%1 AS %2" )
              .arg( mQuery,
                    quotedIdentifier( alias ) ) );

    QString sql = QStringLiteral( "SELECT * FROM %1 LIMIT 1" ).arg( mQuery );

    testAccess = connectionRO()->PQexec( sql );
    if ( testAccess.PQresultStatus() != PGRES_TUPLES_OK )
    {
      QgsMessageLog::logMessage( tr( "Unable to execute the query.\nThe error message from the database was:\n%1.\nSQL: %2" )
                                 .arg( testAccess.PQresultErrorMessage(),
                                       sql ), tr( "PostGIS" ) );
      return false;
    }

    if ( !mSelectAtIdDisabled )
    {
      mEnabledCapabilities = QgsVectorDataProvider::SelectAtId;
    }
  }

  // supports geometry simplification on provider side
  mEnabledCapabilities |= ( QgsVectorDataProvider::SimplifyGeometries | QgsVectorDataProvider::SimplifyGeometriesWithTopologicalValidation );

  //supports transactions
  mEnabledCapabilities |= QgsVectorDataProvider::TransactionSupport;

  // supports circular geometries
  mEnabledCapabilities |= QgsVectorDataProvider::CircularGeometries;

  // supports layer metadata
  mEnabledCapabilities |= QgsVectorDataProvider::ReadLayerMetadata;

  if ( ( mEnabledCapabilities & QgsVectorDataProvider::ChangeGeometries ) &&
       ( mEnabledCapabilities & QgsVectorDataProvider::ChangeAttributeValues ) &&
       mSpatialColType != SctTopoGeometry )
  {
    mEnabledCapabilities |= QgsVectorDataProvider::ChangeFeatures;
  }

  return true;
}

bool QgsPostgresProvider::determinePrimaryKey()
{
  if ( !loadFields() )
  {
    return false;
  }

  // check to see if there is an unique index on the relation, which
  // can be used as a key into the table. Primary keys are always
  // unique indices, so we catch them as well.

  QString sql;
  if ( !mIsQuery )
  {
    sql = QStringLiteral( "SELECT count(*) FROM pg_inherits WHERE inhparent=%1::regclass" ).arg( quotedValue( mQuery ) );
    QgsDebugMsgLevel( QStringLiteral( "Checking whether %1 is a parent table" ).arg( sql ), 2 );
    QgsPostgresResult res( connectionRO()->PQexec( sql ) );
    bool isParentTable( res.PQntuples() == 0 || res.PQgetvalue( 0, 0 ).toInt() > 0 );

    sql = QStringLiteral( "SELECT indexrelid FROM pg_index WHERE indrelid=%1::regclass AND (indisprimary OR indisunique) ORDER BY CASE WHEN indisprimary THEN 1 ELSE 2 END LIMIT 1" ).arg( quotedValue( mQuery ) );
    QgsDebugMsgLevel( QStringLiteral( "Retrieving first primary or unique index: %1" ).arg( sql ), 2 );

    res = connectionRO()->PQexec( sql );
    QgsDebugMsgLevel( QStringLiteral( "Got %1 rows." ).arg( res.PQntuples() ), 2 );

    QStringList log;

    // no primary or unique indices found
    if ( res.PQntuples() == 0 )
    {
      QgsDebugMsgLevel( QStringLiteral( "Relation has no primary key -- investigating alternatives" ), 2 );

      // Two options here. If the relation is a table, see if there is
      // an oid column that can be used instead.
      // If the relation is a view try to find a suitable column to use as
      // the primary key.

      const QgsPostgresProvider::Relkind type = relkind();

      if ( type == Relkind::OrdinaryTable || type == Relkind::PartitionedTable )
      {
        QgsDebugMsgLevel( QStringLiteral( "Relation is a table. Checking to see if it has an oid column." ), 2 );

        mPrimaryKeyAttrs.clear();
        mPrimaryKeyType = PktUnknown;

        if ( connectionRO()->pgVersion() >= 100000 )
        {
          // If there is an generated id on the table, use that instead,
          sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attidentity IN ('a','d') AND attrelid=regclass(%1) LIMIT 1" ).arg( quotedValue( mQuery ) );
          res = connectionRO()->PQexec( sql );
          if ( res.PQntuples() == 1 )
          {
            // Could warn the user here that performance will suffer if
            // attribute isn't indexed (and that they may want to add a
            // primary key to the table)
            int idx = fieldNameIndex( res.PQgetvalue( 0, 0 ) );
            mPrimaryKeyType = pkType( mAttributeFields.at( idx ) );
            mPrimaryKeyAttrs << idx;
          }
        }

        if ( mPrimaryKeyType == PktUnknown )
        {
          // If there is an oid on the table, use that instead,
          sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attname='oid' AND attrelid=regclass(%1)" ).arg( quotedValue( mQuery ) );

          res = connectionRO()->PQexec( sql );
          if ( res.PQntuples() == 1 )
          {
            // Could warn the user here that performance will suffer if
            // oid isn't indexed (and that they may want to add a
            // primary key to the table)
            mPrimaryKeyType = PktOid;
          }
        }

        if ( mPrimaryKeyType == PktUnknown )
        {
          sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attname='ctid' AND attrelid=regclass(%1)" ).arg( quotedValue( mQuery ) );

          res = connectionRO()->PQexec( sql );
          if ( res.PQntuples() == 1 )
          {
            mPrimaryKeyType = PktTid;

            QgsMessageLog::logMessage( tr( "Primary key is ctid - changing of existing features disabled (%1; %2)" ).arg( mGeometryColumn, mQuery ) );
            mEnabledCapabilities &= ~( QgsVectorDataProvider::DeleteFeatures | QgsVectorDataProvider::ChangeAttributeValues | QgsVectorDataProvider::ChangeGeometries | QgsVectorDataProvider::ChangeFeatures );
          }
        }

        if ( mPrimaryKeyType == PktUnknown )
        {
          QgsMessageLog::logMessage( tr( "The table has no column suitable for use as a key. QGIS requires a primary key, a PostgreSQL oid column or a ctid for tables." ), tr( "PostGIS" ) );
        }
      }
      else if ( type == Relkind::View || type == Relkind::MaterializedView || type == Relkind::ForeignTable )
      {
        determinePrimaryKeyFromUriKeyColumn();
      }
      else
      {
        const QMetaEnum metaEnum( QMetaEnum::fromType<Relkind>() );
        QString typeName = metaEnum.valueToKey( type );
        QgsMessageLog::logMessage( tr( "Unexpected relation type '%1'." ).arg( typeName ), tr( "PostGIS" ) );
      }
    }
    else
    {
      // have a primary key or unique index
      QString indrelid = res.PQgetvalue( 0, 0 );
      sql = QStringLiteral( "SELECT attname,attnotnull FROM pg_index,pg_attribute WHERE indexrelid=%1 AND indrelid=attrelid AND pg_attribute.attnum=any(pg_index.indkey)" ).arg( indrelid );

      QgsDebugMsgLevel( "Retrieving key columns: " + sql, 2 );
      res = connectionRO()->PQexec( sql );
      QgsDebugMsgLevel( QStringLiteral( "Got %1 rows." ).arg( res.PQntuples() ), 2 );

      bool mightBeNull = false;
      QString primaryKey;
      QString delim;

      mPrimaryKeyType = PktFidMap; // map by default, will downgrade if needed
      for ( int i = 0; i < res.PQntuples(); i++ )
      {
        QString name = res.PQgetvalue( i, 0 );
        if ( res.PQgetvalue( i, 1 ).startsWith( 'f' ) )
        {
          QgsMessageLog::logMessage( tr( "Unique column '%1' doesn't have a NOT NULL constraint." ).arg( name ), tr( "PostGIS" ) );
          mightBeNull = true;
        }

        primaryKey += delim + quotedIdentifier( name );
        delim = ',';

        int idx = fieldNameIndex( name );
        if ( idx == -1 )
        {
          QgsDebugMsgLevel( "Skipping " + name, 2 );
          continue;
        }
        QgsField fld = mAttributeFields.at( idx );

        // Always use PktFidMap for multi-field keys
        mPrimaryKeyType = i ? PktFidMap : pkType( fld );

        mPrimaryKeyAttrs << idx;
      }

      if ( ( mightBeNull || isParentTable ) && !mUseEstimatedMetadata && !uniqueData( primaryKey ) )
      {
        QgsMessageLog::logMessage( tr( "Ignoring key candidate because of NULL values or inheritance" ), tr( "PostGIS" ) );
        mPrimaryKeyType = PktUnknown;
        mPrimaryKeyAttrs.clear();
      }
    }
  }
  else
  {
    determinePrimaryKeyFromUriKeyColumn();
  }

  if ( mPrimaryKeyAttrs.size() == 1 )
  {
    //primary keys are unique, not null
    QgsFieldConstraints constraints = mAttributeFields.at( mPrimaryKeyAttrs[0] ).constraints();
    constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
    constraints.setConstraint( QgsFieldConstraints::ConstraintNotNull, QgsFieldConstraints::ConstraintOriginProvider );
    mAttributeFields[ mPrimaryKeyAttrs[0] ].setConstraints( constraints );
  }

  mValid = mPrimaryKeyType != PktUnknown;

  return mValid;
}

/* static */
QStringList QgsPostgresProvider::parseUriKey( const QString &key )
{
  if ( key.isEmpty() ) return QStringList();

  QStringList cols;

  // remove quotes from key list
  if ( key.startsWith( '"' ) && key.endsWith( '"' ) )
  {
    int i = 1;
    QString col;
    while ( i < key.size() )
    {
      if ( key[i] == '"' )
      {
        if ( i + 1 < key.size() && key[i + 1] == '"' )
        {
          i++;
        }
        else
        {
          cols << col;
          col.clear();

          if ( ++i == key.size() )
            break;

          Q_ASSERT( key[i] == ',' );
          i++;
          Q_ASSERT( key[i] == '"' );
          i++;
          col.clear();
          continue;
        }
      }

      col += key[i++];
    }
  }
  else if ( key.contains( ',' ) )
  {
    cols = key.split( ',' );
  }
  else
  {
    cols << key;
  }

  return cols;
}

void QgsPostgresProvider::determinePrimaryKeyFromUriKeyColumn()
{
  QString primaryKey = mUri.keyColumn();
  mPrimaryKeyType = PktUnknown;

  if ( !primaryKey.isEmpty() )
  {
    const QStringList cols = parseUriKey( primaryKey );

    primaryKey.clear();
    QString del;
    for ( const QString &col : cols )
    {
      primaryKey += del + quotedIdentifier( col );
      del = QStringLiteral( "," );
    }

    for ( const QString &col : cols )
    {
      int idx = fieldNameIndex( col );
      if ( idx < 0 )
      {
        QgsMessageLog::logMessage( tr( "Key field '%1' for view/query not found." ).arg( col ), tr( "PostGIS" ) );
        mPrimaryKeyAttrs.clear();
        break;
      }

      mPrimaryKeyAttrs << idx;
    }

    if ( !mPrimaryKeyAttrs.isEmpty() )
    {
      bool unique = true;
      if ( mCheckPrimaryKeyUnicity )
      {
        unique = uniqueData( primaryKey );
      }

      if ( mUseEstimatedMetadata || unique )
      {
        mPrimaryKeyType = PktFidMap; // Map by default
        if ( mPrimaryKeyAttrs.size() == 1 )
        {
          QgsField fld = mAttributeFields.at( mPrimaryKeyAttrs.at( 0 ) );
          mPrimaryKeyType = pkType( fld );
        }
      }
      else
      {
        QgsMessageLog::logMessage( tr( "Primary key field '%1' for view/query not unique." ).arg( primaryKey ), tr( "PostGIS" ) );
      }
    }
    else
    {
      QgsMessageLog::logMessage( tr( "Keys for view/query undefined." ), tr( "PostGIS" ) );
    }
  }
  else
  {
    QgsMessageLog::logMessage( tr( "No key field for view/query given." ), tr( "PostGIS" ) );
  }
}

bool QgsPostgresProvider::uniqueData( const QString &quotedColNames )
{
  // Check to see if the given columns contain unique data
  QString sql = QStringLiteral( "SELECT count(distinct (%1))=count((%1)) FROM %2%3" )
                .arg( quotedColNames,
                      mQuery,
                      filterWhereClause() );

  QgsPostgresResult unique( connectionRO()->PQexec( sql ) );

  if ( unique.PQresultStatus() != PGRES_TUPLES_OK )
  {
    pushError( unique.PQresultErrorMessage() );
    return false;
  }
  return unique.PQntuples() == 1 && unique.PQgetvalue( 0, 0 ).startsWith( 't' );
}

// Returns the minimum value of an attribute
QVariant QgsPostgresProvider::minimumValue( int index ) const
{
  try
  {
    // get the field name
    QgsField fld = field( index );
    QString sql = QStringLiteral( "SELECT min(%1) AS %1 FROM %2" )
                  .arg( quotedIdentifier( fld.name() ),
                        mQuery );

    if ( !mSqlWhereClause.isEmpty() )
    {
      sql += QStringLiteral( " WHERE %1" ).arg( mSqlWhereClause );
    }

    sql = QStringLiteral( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ), sql );

    QgsPostgresResult rmin( connectionRO()->PQexec( sql ) );
    return convertValue( fld.type(), fld.subType(), rmin.PQgetvalue( 0, 0 ), fld.typeName() );
  }
  catch ( PGFieldNotFound )
  {
    return QVariant( QString() );
  }
}

// Returns the list of unique values of an attribute
QSet<QVariant> QgsPostgresProvider::uniqueValues( int index, int limit ) const
{
  QSet<QVariant> uniqueValues;

  try
  {
    // get the field name
    QgsField fld = field( index );
    QString sql = QStringLiteral( "SELECT DISTINCT %1 FROM %2" )
                  .arg( quotedIdentifier( fld.name() ),
                        mQuery );

    if ( !mSqlWhereClause.isEmpty() )
    {
      sql += QStringLiteral( " WHERE %1" ).arg( mSqlWhereClause );
    }

    sql += QStringLiteral( " ORDER BY %1" ).arg( quotedIdentifier( fld.name() ) );

    if ( limit >= 0 )
    {
      sql += QStringLiteral( " LIMIT %1" ).arg( limit );
    }

    sql = QStringLiteral( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ), sql );

    QgsPostgresResult res( connectionRO()->PQexec( sql ) );
    if ( res.PQresultStatus() == PGRES_TUPLES_OK )
    {
      for ( int i = 0; i < res.PQntuples(); i++ )
        uniqueValues.insert( convertValue( fld.type(), fld.subType(), res.PQgetvalue( i, 0 ), fld.typeName() ) );
    }
  }
  catch ( PGFieldNotFound )
  {
  }
  return uniqueValues;
}

QStringList QgsPostgresProvider::uniqueStringsMatching( int index, const QString &substring, int limit, QgsFeedback *feedback ) const
{
  QStringList results;

  try
  {
    // get the field name
    QgsField fld = field( index );
    QString sql = QStringLiteral( "SELECT DISTINCT %1 FROM %2 WHERE" )
                  .arg( quotedIdentifier( fld.name() ),
                        mQuery );

    if ( !mSqlWhereClause.isEmpty() )
    {
      sql += QStringLiteral( " ( %1 ) AND " ).arg( mSqlWhereClause );
    }

    sql += QStringLiteral( " %1::text ILIKE '%%2%'" ).arg( quotedIdentifier( fld.name() ), substring );


    sql += QStringLiteral( " ORDER BY %1" ).arg( quotedIdentifier( fld.name() ) );

    if ( limit >= 0 )
    {
      sql += QStringLiteral( " LIMIT %1" ).arg( limit );
    }

    sql = QStringLiteral( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ), sql );

    QgsPostgresResult res( connectionRO()->PQexec( sql ) );
    if ( res.PQresultStatus() == PGRES_TUPLES_OK )
    {
      for ( int i = 0; i < res.PQntuples(); i++ )
      {
        results << ( convertValue( fld.type(), fld.subType(), res.PQgetvalue( i, 0 ), fld.typeName() ) ).toString();
        if ( feedback && feedback->isCanceled() )
          break;
      }
    }
  }
  catch ( PGFieldNotFound )
  {
  }
  return results;
}

void QgsPostgresProvider::enumValues( int index, QStringList &enumList ) const
{

  if ( index < 0 || index >= mAttributeFields.count() )
    return;

  if ( ! mShared->fieldSupportsEnumValuesIsSet( index ) )
  {
    mShared->setFieldSupportsEnumValues( index, true );
  }
  else if ( ! mShared->fieldSupportsEnumValues( index ) )
  {
    return;
  }

  //find out type of index
  const QString fieldName = mAttributeFields.at( index ).name();
  QString typeName = mAttributeFields.at( index ).typeName();

  // Remove schema extension from typeName
  typeName.remove( QRegularExpression( "^([^.]+\\.)+" ) );

  //is type an enum?
  const QString typeSql = QStringLiteral( "SELECT typtype FROM pg_type WHERE typname=%1" ).arg( quotedValue( typeName ) );
  QgsPostgresResult typeRes( connectionRO()->PQexec( typeSql ) );
  if ( typeRes.PQresultStatus() != PGRES_TUPLES_OK || typeRes.PQntuples() < 1 )
  {
    mShared->setFieldSupportsEnumValues( index, false );
    return;
  }

  const QString typtype = typeRes.PQgetvalue( 0, 0 );
  if ( typtype.compare( QLatin1String( "e" ), Qt::CaseInsensitive ) == 0 )
  {
    //try to read enum_range of attribute
    if ( !parseEnumRange( enumList, fieldName ) )
    {
      mShared->setFieldSupportsEnumValues( index, false );
    }
  }
  else
  {
    //is there a domain check constraint for the attribute?
    if ( !parseDomainCheckConstraint( enumList, fieldName ) )
    {
      mShared->setFieldSupportsEnumValues( index, false );
    }
  }
}

bool QgsPostgresProvider::parseEnumRange( QStringList &enumValues, const QString &attributeName ) const
{
  enumValues.clear();

  QString enumRangeSql = QStringLiteral( "SELECT enumlabel FROM pg_catalog.pg_enum WHERE enumtypid=(SELECT atttypid::regclass FROM pg_attribute WHERE attrelid=%1::regclass AND attname=%2)" )
                         .arg( quotedValue( mQuery ),
                               quotedValue( attributeName ) );
  QgsPostgresResult enumRangeRes( connectionRO()->PQexec( enumRangeSql ) );
  if ( enumRangeRes.PQresultStatus() != PGRES_TUPLES_OK )
    return false;

  for ( int i = 0; i < enumRangeRes.PQntuples(); i++ )
  {
    enumValues << enumRangeRes.PQgetvalue( i, 0 );
  }

  return true;
}

bool QgsPostgresProvider::parseDomainCheckConstraint( QStringList &enumValues, const QString &attributeName ) const
{
  enumValues.clear();

  //is it a domain type with a check constraint?
  QString domainSql = QStringLiteral( "SELECT domain_name, domain_schema FROM information_schema.columns WHERE table_name=%1 AND column_name=%2" ).arg( quotedValue( mTableName ), quotedValue( attributeName ) );
  QgsPostgresResult domainResult( connectionRO()->PQexec( domainSql ) );
  if ( domainResult.PQresultStatus() == PGRES_TUPLES_OK && domainResult.PQntuples() > 0 && !domainResult.PQgetvalue( 0, 0 ).isNull() )
  {
    QString domainCheckDefinitionSql;
    if ( connectionRO()->pgVersion() < 120000 )
    {
      domainCheckDefinitionSql = QStringLiteral( ""
                                 "SELECT consrc FROM pg_constraint "
                                 "  WHERE contypid =("
                                 "    SELECT oid FROM pg_type "
                                 "      WHERE typname = %1 "
                                 "      AND typnamespace =("
                                 "        SELECT oid FROM pg_namespace WHERE nspname = %2"
                                 "      )"
                                 "    )" )
                                 .arg( quotedValue( domainResult.PQgetvalue( 0, 0 ) ) )
                                 .arg( quotedValue( domainResult.PQgetvalue( 0, 1 ) ) );

    }
    else
    {
      domainCheckDefinitionSql = QStringLiteral( ""
                                 "SELECT pg_catalog.pg_get_constraintdef( ( "
                                 "  SELECT oid FROM pg_constraint WHERE contypid = ( "
                                 "    SELECT oid FROM pg_type "
                                 "      WHERE typname = %1 "
                                 "      AND typnamespace =("
                                 "        SELECT oid FROM pg_namespace WHERE nspname = %2"
                                 "      )"
                                 "    )"
                                 "  ), true );" )
                                 .arg( quotedValue( domainResult.PQgetvalue( 0, 0 ) ) )
                                 .arg( quotedValue( domainResult.PQgetvalue( 0, 1 ) ) );

    }

    QgsPostgresResult domainCheckRes( connectionRO()->PQexec( domainCheckDefinitionSql ) );
    if ( domainCheckRes.PQresultStatus() == PGRES_TUPLES_OK && domainCheckRes.PQntuples() > 0 )
    {
      QString checkDefinition = domainCheckRes.PQgetvalue( 0, 0 );

      //we assume that the constraint is of the following form:
      //(VALUE = ANY (ARRAY['a'::text, 'b'::text, 'c'::text, 'd'::text]))
      //normally, PostgreSQL creates that if the constraint has been specified as 'VALUE in ('a', 'b', 'c', 'd')
      const thread_local QRegularExpression definitionRegExp( "VALUE\\s*=\\s*ANY\\s*\\(\\s*ARRAY\\s*\\[" );
      int anyPos = checkDefinition.indexOf( definitionRegExp );
      int arrayPosition = checkDefinition.lastIndexOf( QLatin1String( "ARRAY[" ) );
      int closingBracketPos = checkDefinition.indexOf( ']', arrayPosition + 6 );

      if ( anyPos == -1 || anyPos >= arrayPosition )
      {
        return false; //constraint has not the required format
      }

      if ( arrayPosition != -1 )
      {
        QString valueList = checkDefinition.mid( arrayPosition + 6, closingBracketPos );
#if QT_VERSION < QT_VERSION_CHECK(5, 15, 0)
        const QStringList commaSeparation = valueList.split( ',', QString::SkipEmptyParts );
#else
        const QStringList commaSeparation = valueList.split( ',', Qt::SkipEmptyParts );
#endif
        QStringList::const_iterator cIt = commaSeparation.constBegin();
        for ( ; cIt != commaSeparation.constEnd(); ++cIt )
        {
          //get string between ''
          int beginQuotePos = cIt->indexOf( '\'' );
          int endQuotePos = cIt->lastIndexOf( '\'' );
          if ( beginQuotePos != -1 && ( endQuotePos - beginQuotePos ) > 1 )
          {
            enumValues << cIt->mid( beginQuotePos + 1, endQuotePos - beginQuotePos - 1 );
          }
        }
      }
      return true;
    }
  }
  return false;
}

// Returns the maximum value of an attribute
QVariant QgsPostgresProvider::maximumValue( int index ) const
{
  try
  {
    // get the field name
    QgsField fld = field( index );
    QString sql = QStringLiteral( "SELECT max(%1) AS %1 FROM %2" )
                  .arg( quotedIdentifier( fld.name() ),
                        mQuery );

    if ( !mSqlWhereClause.isEmpty() )
    {
      sql += QStringLiteral( " WHERE %1" ).arg( mSqlWhereClause );
    }

    sql = QStringLiteral( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ), sql );

    QgsPostgresResult rmax( connectionRO()->PQexec( sql ) );

    return convertValue( fld.type(), fld.subType(), rmax.PQgetvalue( 0, 0 ), fld.typeName() );
  }
  catch ( PGFieldNotFound )
  {
    return QVariant( QString() );
  }
}


bool QgsPostgresProvider::isValid() const
{
  return mValid;
}

QString QgsPostgresProvider::defaultValueClause( int fieldId ) const
{
  QString defVal = mDefaultValues.value( fieldId, QString() );

  // with generated columns (PostgreSQL 12+), the provider will ALWAYS evaluate the default values.
  // The only acceptable value for such columns on INSERT or UPDATE clauses is the keyword "DEFAULT".
  // Here, we return the expression used to generate the field value, so the
  // user can see what is happening when inserting a new feature.
  // On inserting a new feature or updating a generated field, this is
  // omitted from the generated queries.
  // See https://www.postgresql.org/docs/12/ddl-generated-columns.html
  if ( mGeneratedValues.contains( fieldId ) )
  {
    return defVal;
  }

  if ( !providerProperty( EvaluateDefaultValues, false ).toBool() && !defVal.isEmpty() )
  {
    return defVal;
  }

  return QString();
}

QVariant QgsPostgresProvider::defaultValue( int fieldId ) const
{
  QString defVal = mDefaultValues.value( fieldId, QString() );

  if ( providerProperty( EvaluateDefaultValues, false ).toBool() && !defVal.isEmpty() )
  {
    QgsField fld = field( fieldId );

    QgsPostgresResult res( connectionRO()->PQexec( QStringLiteral( "SELECT %1" ).arg( defVal ) ) );

    if ( res.result() )
    {
      return convertValue( fld.type(), fld.subType(), res.PQgetvalue( 0, 0 ), fld.typeName() );
    }
    else
    {
      pushError( tr( "Could not execute query" ) );
      return QVariant();
    }
  }

  return QVariant();
}

bool QgsPostgresProvider::skipConstraintCheck( int fieldIndex, QgsFieldConstraints::Constraint, const QVariant &value ) const
{
  if ( providerProperty( EvaluateDefaultValues, false ).toBool() )
  {
    return !mDefaultValues.value( fieldIndex ).isEmpty();
  }
  else
  {
    // stricter check - if we are evaluating default values only on commit then we can only bypass the check
    // if the attribute values matches the original default clause
    return mDefaultValues.contains( fieldIndex ) && mDefaultValues.value( fieldIndex ) == value.toString() && !value.isNull();
  }
}

QString QgsPostgresProvider::paramValue( const QString &fieldValue, const QString &defaultValue ) const
{
  if ( fieldValue.isNull() )
    return QString();

  if ( fieldValue == defaultValue && !defaultValue.isNull() )
  {
    QgsPostgresResult result( connectionRO()->PQexec( QStringLiteral( "SELECT %1" ).arg( defaultValue ) ) );
    if ( result.PQresultStatus() != PGRES_TUPLES_OK )
      throw PGException( result );

    return result.PQgetvalue( 0, 0 );
  }

  return fieldValue;
}


/* private */
bool QgsPostgresProvider::getTopoLayerInfo()
{
  QString sql = QString( "SELECT t.name, l.layer_id "
                         "FROM topology.layer l, topology.topology t "
                         "WHERE l.topology_id = t.id AND l.schema_name=%1 "
                         "AND l.table_name=%2 AND l.feature_column=%3" )
                .arg( quotedValue( mSchemaName ),
                      quotedValue( mTableName ),
                      quotedValue( mGeometryColumn ) );
  QgsPostgresResult result( connectionRO()->PQexec( sql ) );
  if ( result.PQresultStatus() != PGRES_TUPLES_OK )
  {
    throw PGException( result ); // we should probably not do this
  }
  if ( result.PQntuples() < 1 )
  {
    QgsMessageLog::logMessage( tr( "Could not find topology of layer %1.%2.%3" )
                               .arg( quotedValue( mSchemaName ),
                                     quotedValue( mTableName ),
                                     quotedValue( mGeometryColumn ) ),
                               tr( "PostGIS" ) );
    return false;
  }
  mTopoLayerInfo.topologyName = result.PQgetvalue( 0, 0 );
  mTopoLayerInfo.layerId = result.PQgetvalue( 0, 1 ).toLong();
  return true;
}

/* private */
void QgsPostgresProvider::dropOrphanedTopoGeoms()
{
  QString sql = QString( "DELETE FROM %1.relation WHERE layer_id = %2 AND "
                         "topogeo_id NOT IN ( SELECT id(%3) FROM %4.%5 )" )
                .arg( quotedIdentifier( mTopoLayerInfo.topologyName ) )
                .arg( mTopoLayerInfo.layerId )
                .arg( quotedIdentifier( mGeometryColumn ),
                      quotedIdentifier( mSchemaName ),
                      quotedIdentifier( mTableName ) )
                ;

  QgsDebugMsgLevel( "TopoGeom orphans cleanup query: " + sql, 2 );

  connectionRW()->PQexecNR( sql );
}

QString QgsPostgresProvider::geomParam( int offset ) const
{
  QString geometry;

  bool forceMulti = false;

  if ( mSpatialColType != SctTopoGeometry )
  {
    forceMulti = QgsWkbTypes::isMultiType( wkbType() );
  }

  if ( mSpatialColType == SctTopoGeometry )
  {
    geometry += QLatin1String( "toTopoGeom(" );
  }

  if ( forceMulti )
  {
    geometry += connectionRO()->majorVersion() < 2 ? "multi(" : "st_multi(";
  }

  geometry += QStringLiteral( "%1($%2%3,%4)" )
              .arg( connectionRO()->majorVersion() < 2 ? "geomfromwkb" : "st_geomfromwkb" )
              .arg( offset )
              .arg( connectionRO()->useWkbHex() ? "" : "::bytea",
                    mRequestedSrid.isEmpty() ? mDetectedSrid : mRequestedSrid );

  if ( forceMulti )
  {
    geometry += ')';
  }

  if ( mSpatialColType == SctTopoGeometry )
  {
    geometry += QStringLiteral( ",%1,%2)" )
                .arg( quotedValue( mTopoLayerInfo.topologyName ) )
                .arg( mTopoLayerInfo.layerId );
  }

  return geometry;
}

bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )
{
  if ( flist.isEmpty() )
    return true;

  if ( mIsQuery )
    return false;

  QgsPostgresConn *conn = connectionRW();
  if ( !conn )
  {
    return false;
  }
  conn->lock();

  bool returnvalue = true;

  try
  {
    conn->begin();

    // Prepare the INSERT statement
    QString insert = QStringLiteral( "INSERT INTO %1(" ).arg( mQuery );
    QString values;
    QString delim;
    int offset = 1;

    QStringList defaultValues;
    QList<int> fieldId;

    if ( !mGeometryColumn.isNull() )
    {
      insert += quotedIdentifier( mGeometryColumn );

      values += geomParam( offset++ );

      delim = ',';
    }

    // Optimization: if we have a single primary key column whose default value
    // is a sequence, and that none of the features have a value set for that
    // column, then we can completely omit inserting it.
    bool skipSinglePKField = false;
    bool overrideIdentity = false;

    if ( ( mPrimaryKeyType == PktInt || mPrimaryKeyType == PktInt64 || mPrimaryKeyType == PktFidMap || mPrimaryKeyType == PktUint64 ) )
    {
      if ( mPrimaryKeyAttrs.size() == 1 &&
           defaultValueClause( mPrimaryKeyAttrs[0] ).startsWith( "nextval(" ) )
      {
        bool foundNonEmptyPK = false;
        int idx = mPrimaryKeyAttrs[0];
        QString defaultValue = defaultValueClause( idx );
        for ( int i = 0; i < flist.size(); i++ )
        {
          QgsAttributes attrs2 = flist[i].attributes();
          QVariant v2 = attrs2.value( idx, QVariant( QVariant::Int ) );
          // a PK field with a sequence val is auto populate by QGIS with this default
          // we are only interested in non default values
          if ( !v2.isNull() && v2.toString() != defaultValue )
          {
            foundNonEmptyPK = true;
            break;
          }
        }
        skipSinglePKField = !foundNonEmptyPK;
      }

      if ( !skipSinglePKField )
      {
        for ( int idx : mPrimaryKeyAttrs )
        {
          if ( mIdentityFields[idx] == 'a' )
            overrideIdentity = true;
          insert += delim + quotedIdentifier( field( idx ).name() );
          values += delim + QStringLiteral( "$%1" ).arg( defaultValues.size() + offset );
          delim = ',';
          fieldId << idx;
          defaultValues << defaultValueClause( idx );
        }
      }
    }

    QgsAttributes attributevec = flist[0].attributes();

    // look for unique attribute values to place in statement instead of passing as parameter
    // e.g. for defaults
    for ( int idx = 0; idx < attributevec.count(); ++idx )
    {
      QVariant v = attributevec.value( idx, QVariant( QVariant::Int ) ); // default to NULL for missing attributes
      if ( skipSinglePKField && idx == mPrimaryKeyAttrs[0] )
        continue;
      if ( fieldId.contains( idx ) )
        continue;

      if ( idx >= mAttributeFields.count() )
        continue;

      QString fieldname = mAttributeFields.at( idx ).name();

      if ( !mGeneratedValues.value( idx, QString() ).isEmpty() )
      {
        QgsDebugMsg( QStringLiteral( "Skipping field %1 (idx %2) which is GENERATED." ).arg( fieldname, QString::number( idx ) ) );
        continue;
      }

      QString fieldTypeName = mAttributeFields.at( idx ).typeName();

      QgsDebugMsgLevel( "Checking field against: " + fieldname, 2 );

      if ( fieldname.isEmpty() || fieldname == mGeometryColumn )
        continue;

      int i;
      for ( i = 1; i < flist.size(); i++ )
      {
        QgsAttributes attrs2 = flist[i].attributes();
        QVariant v2 = attrs2.value( idx, QVariant( QVariant::Int ) ); // default to NULL for missing attributes

        if ( v2 != v )
          break;
      }

      insert += delim + quotedIdentifier( fieldname );

      if ( mIdentityFields[idx] == 'a' )
        overrideIdentity = true;

      QString defVal = defaultValueClause( idx );

      if ( i == flist.size() )
      {
        if ( qgsVariantEqual( v, defVal ) )
        {
          if ( defVal.isNull() )
          {
            values += delim + "NULL";
          }
          else
          {
            values += delim + defVal;
          }
        }
        else if ( fieldTypeName == QLatin1String( "geometry" ) )
        {
          values += QStringLiteral( "%1%2(%3)" )
                    .arg( delim,
                          connectionRO()->majorVersion() < 2 ? "geomfromewkt" : "st_geomfromewkt",
                          quotedValue( v.toString() ) );
        }
        else if ( fieldTypeName == QLatin1String( "geography" ) )
        {
          values += QStringLiteral( "%1st_geographyfromewkt(%2)" )
                    .arg( delim,
                          quotedValue( v.toString() ) );
        }
        else if ( fieldTypeName == QLatin1String( "jsonb" ) )
        {
          values += delim + quotedJsonValue( v ) + QStringLiteral( "::jsonb" );
        }
        else if ( fieldTypeName == QLatin1String( "json" ) )
        {
          values += delim + quotedJsonValue( v ) + QStringLiteral( "::json" );
        }
        else if ( fieldTypeName == QLatin1String( "bytea" ) )
        {
          values += delim + quotedByteaValue( v );
        }
        //TODO: convert arrays and hstore to native types
        else
        {
          values += delim + quotedValue( v );
        }
      }
      else
      {
        // value is not unique => add parameter
        if ( fieldTypeName == QLatin1String( "geometry" ) )
        {
          values += QStringLiteral( "%1%2($%3)" )
                    .arg( delim,
                          connectionRO()->majorVersion() < 2 ? "geomfromewkt" : "st_geomfromewkt" )
                    .arg( defaultValues.size() + offset );
        }
        else if ( fieldTypeName == QLatin1String( "geography" ) )
        {
          values += QStringLiteral( "%1st_geographyfromewkt($%2)" )
                    .arg( delim )
                    .arg( defaultValues.size() + offset );
        }
        else
        {
          values += QStringLiteral( "%1$%2" )
                    .arg( delim )
                    .arg( defaultValues.size() + offset );
        }
        defaultValues.append( defVal );
        fieldId.append( idx );
      }

      delim = ',';
    }

    insert += QStringLiteral( ") %1VALUES (%2)" ).arg( overrideIdentity ? "OVERRIDING SYSTEM VALUE " : "" ).arg( values );

    if ( !( flags & QgsFeatureSink::FastInsert ) )
    {
      if ( mPrimaryKeyType == PktFidMap || mPrimaryKeyType == PktInt || mPrimaryKeyType == PktInt64 || mPrimaryKeyType == PktUint64 )
      {
        insert += QLatin1String( " RETURNING " );

        QString delim;
        const auto constMPrimaryKeyAttrs = mPrimaryKeyAttrs;
        for ( int idx : constMPrimaryKeyAttrs )
        {
          insert += delim + quotedIdentifier( mAttributeFields.at( idx ).name() );
          delim = ',';
        }
      }
    }

    QgsDebugMsgLevel( QStringLiteral( "prepare addfeatures: %1" ).arg( insert ), 2 );
    QgsPostgresResult stmt( conn->PQprepare( QStringLiteral( "addfeatures" ), insert, fieldId.size() + offset - 1, nullptr ) );

    if ( stmt.PQresultStatus() != PGRES_COMMAND_OK )
      throw PGException( stmt );

    for ( QgsFeatureList::iterator features = flist.begin(); features != flist.end(); ++features )
    {
      QgsAttributes attrs = features->attributes();

      QStringList params;
      if ( !mGeometryColumn.isNull() )
      {
        appendGeomParam( features->geometry(), params );
      }

      params.reserve( fieldId.size() );
      for ( int i = 0; i < fieldId.size(); i++ )
      {
        int attrIdx = fieldId[i];
        QVariant value = attrIdx < attrs.length() ? attrs.at( attrIdx ) : QVariant( QVariant::Int );

        QString v;
        if ( value.isNull() )
        {
          QgsField fld = field( attrIdx );
          v = paramValue( defaultValues[ i ], defaultValues[ i ] );
          features->setAttribute( attrIdx, convertValue( fld.type(), fld.subType(), v, fld.typeName() ) );
        }
        else
        {
          // the conversion functions expects the list as a string, so convert it
          if ( value.type() == QVariant::StringList )
          {
            QStringList list_vals = value.toStringList();
            // all strings need to be double quoted to allow special postgres
            // array characters such as {, or whitespace in the string
            // but we need to escape all double quotes and backslashes
            list_vals.replaceInStrings( "\\", "\\\\" );
            list_vals.replaceInStrings( "\"", "\\\"" );
            v = QStringLiteral( "{\"" ) + value.toStringList().join( QLatin1String( "\",\"" ) ) + QStringLiteral( "\"}" );
          }
          else if ( value.type() == QVariant::List )
          {
            v = "{" + value.toStringList().join( "," ) + "}";
          }
          else
          {
            v = paramValue( value.toString(), defaultValues[ i ] );
          }

          if ( v != value.toString() )
          {
            QgsField fld = field( attrIdx );
            features->setAttribute( attrIdx, convertValue( fld.type(), fld.subType(), v, fld.typeName() ) );
          }
        }

        params << v;
      }

      QgsPostgresResult result( conn->PQexecPrepared( QStringLiteral( "addfeatures" ), params ) );

      if ( !( flags & QgsFeatureSink::FastInsert ) && result.PQresultStatus() == PGRES_TUPLES_OK )
      {
        for ( int i = 0; i < mPrimaryKeyAttrs.size(); ++i )
        {
          const int idx = mPrimaryKeyAttrs.at( i );
          const QgsField fld = mAttributeFields.at( idx );
          features->setAttribute( idx, convertValue( fld.type(), fld.subType(), result.PQgetvalue( 0, i ), fld.typeName() ) );
        }
      }
      else if ( result.PQresultStatus() != PGRES_COMMAND_OK )
        throw PGException( result );

      if ( !( flags & QgsFeatureSink::FastInsert ) && mPrimaryKeyType == PktOid )
      {
        features->setId( result.PQoidValue() );
        QgsDebugMsgLevel( QStringLiteral( "new fid=%1" ).arg( features->id() ), 4 );
      }
    }

    if ( !( flags & QgsFeatureSink::FastInsert ) )
    {
      // update feature ids
      if ( mPrimaryKeyType == PktInt || mPrimaryKeyType == PktInt64 || mPrimaryKeyType == PktFidMap || mPrimaryKeyType == PktUint64 )
      {
        for ( QgsFeatureList::iterator features = flist.begin(); features != flist.end(); ++features )
        {
          QgsAttributes attrs = features->attributes();

          if ( mPrimaryKeyType == PktInt )
          {
            features->setId( PKINT2FID( STRING_TO_FID( attrs.at( mPrimaryKeyAttrs.at( 0 ) ) ) ) );
          }
          else
          {
            QVariantList primaryKeyVals;

            const auto constMPrimaryKeyAttrs = mPrimaryKeyAttrs;
            for ( int idx : constMPrimaryKeyAttrs )
            {
              primaryKeyVals << attrs.at( idx );
            }

            features->setId( mShared->lookupFid( primaryKeyVals ) );
          }
          QgsDebugMsgLevel( QStringLiteral( "new fid=%1" ).arg( features->id() ), 4 );
        }
      }
    }

    conn->PQexecNR( QStringLiteral( "DEALLOCATE addfeatures" ) );

    returnvalue &= conn->commit();
    if ( mTransaction )
      mTransaction->dirtyLastSavePoint();

    mShared->addFeaturesCounted( flist.size() );
  }
  catch ( PGException &e )
  {
    pushError( tr( "PostGIS error while adding features: %1" ).arg( e.errorMessage() ) );
    conn->rollback();
    conn->PQexecNR( QStringLiteral( "DEALLOCATE addfeatures" ) );
    returnvalue = false;
  }

  conn->unlock();
  return returnvalue;
}

bool QgsPostgresProvider::deleteFeatures( const QgsFeatureIds &ids )
{
  if ( ids.isEmpty() )
    return true;

  bool returnvalue = true;

  if ( mIsQuery )
  {
    QgsDebugMsg( QStringLiteral( "Cannot delete features (is a query)" ) );
    return false;
  }

  QgsPostgresConn *conn = connectionRW();
  if ( !conn )
  {
    return false;
  }
  conn->lock();

  try
  {
    conn->begin();

    QgsFeatureIds chunkIds;
    const int countIds = ids.size();
    int i = 0;
    for ( QgsFeatureIds::const_iterator it = ids.constBegin(); it != ids.constEnd(); ++it )
    {
      // create chunks of fids to delete, the last chunk may be smaller
      chunkIds.insert( *it );
      i++;
      if ( chunkIds.size() < 5000 && i < countIds )
        continue;

      const QString sql = QStringLiteral( "DELETE FROM %1 WHERE %2" )
                          .arg( mQuery, whereClause( chunkIds ) );
      QgsDebugMsgLevel( "delete sql: " + sql, 2 );

      //send DELETE statement and do error handling
      QgsPostgresResult result( conn->PQexec( sql ) );
      if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
        throw PGException( result );

      for ( QgsFeatureIds::const_iterator chunkIt = chunkIds.constBegin(); chunkIt != chunkIds.constEnd(); ++chunkIt )
      {
        mShared->removeFid( *chunkIt );
      }
      chunkIds.clear();
    }

    returnvalue &= conn->commit();
    if ( mTransaction )
      mTransaction->dirtyLastSavePoint();

    if ( mSpatialColType == SctTopoGeometry )
    {
      // NOTE: in presence of multiple TopoGeometry objects
      //       for the same table or when deleting a Geometry
      //       layer _also_ having a TopoGeometry component,
      //       orphans would still be left.
      // TODO: decouple layer from table and signal table when
      //       records are added or removed
      dropOrphanedTopoGeoms();
    }

    mShared->addFeaturesCounted( -ids.size() );
  }
  catch ( PGException &e )
  {
    pushError( tr( "PostGIS error while deleting features: %1" ).arg( e.errorMessage() ) );
    conn->rollback();
    returnvalue = false;
  }

  conn->unlock();
  return returnvalue;
}

bool QgsPostgresProvider::truncate()
{
  bool returnvalue = true;

  if ( mIsQuery )
  {
    QgsDebugMsg( QStringLiteral( "Cannot truncate (is a query)" ) );
    return false;
  }

  QgsPostgresConn *conn = connectionRW();
  if ( !conn )
  {
    return false;
  }
  conn->lock();

  try
  {
    conn->begin();

    QString sql = QStringLiteral( "TRUNCATE %1" ).arg( mQuery );
    QgsDebugMsgLevel( "truncate sql: " + sql, 2 );

    //send truncate statement and do error handling
    QgsPostgresResult result( conn->PQexec( sql ) );
    if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
      throw PGException( result );

    returnvalue &= conn->commit();
    if ( mTransaction )
      mTransaction->dirtyLastSavePoint();

    if ( returnvalue )
    {
      if ( mSpatialColType == SctTopoGeometry )
      {
        // NOTE: in presence of multiple TopoGeometry objects
        //       for the same table or when deleting a Geometry
        //       layer _also_ having a TopoGeometry component,
        //       orphans would still be left.
        // TODO: decouple layer from table and signal table when
        //       records are added or removed
        dropOrphanedTopoGeoms();
      }
      mShared->clear();
    }
  }
  catch ( PGException &e )
  {
    pushError( tr( "PostGIS error while truncating: %1" ).arg( e.errorMessage() ) );
    conn->rollback();
    returnvalue = false;
  }

  conn->unlock();
  return returnvalue;
}

bool QgsPostgresProvider::addAttributes( const QList<QgsField> &attributes )
{
  bool returnvalue = true;

  if ( mIsQuery )
    return false;

  if ( attributes.isEmpty() )
    return true;

  QgsPostgresConn *conn = connectionRW();
  if ( !conn )
  {
    return false;
  }
  conn->lock();

  try
  {
    conn->begin();

    QString delim;
    QString sql = QStringLiteral( "ALTER TABLE %1 " ).arg( mQuery );
    for ( QList<QgsField>::const_iterator iter = attributes.begin(); iter != attributes.end(); ++iter )
    {
      QString type = iter->typeName();
      if ( type == QLatin1String( "char" ) || type == QLatin1String( "varchar" ) )
      {
        if ( iter->length() > 0 )
          type = QStringLiteral( "%1(%2)" ).arg( type ).arg( iter->length() );
      }
      else if ( type == QLatin1String( "numeric" ) || type == QLatin1String( "decimal" ) )
      {
        if ( iter->length() > 0 && iter->precision() > 0 )
          type = QStringLiteral( "%1(%2,%3)" ).arg( type ).arg( iter->length() ).arg( iter->precision() );
      }
      sql.append( QStringLiteral( "%1ADD COLUMN %2 %3" ).arg( delim, quotedIdentifier( iter->name() ), type ) );
      delim = ',';
    }

    //send sql statement and do error handling
    QgsPostgresResult result( conn->PQexec( sql ) );
    if ( result.PQresultStatus() != PGRES_COMMAND_OK )
      throw PGException( result );

    for ( QList<QgsField>::const_iterator iter = attributes.begin(); iter != attributes.end(); ++iter )
    {
      if ( !iter->comment().isEmpty() )
      {
        sql = QStringLiteral( "COMMENT ON COLUMN %1.%2 IS %3" )
              .arg( mQuery,
                    quotedIdentifier( iter->name() ),
                    quotedValue( iter->comment() ) );
        result = conn->PQexec( sql );
        if ( result.PQresultStatus() != PGRES_COMMAND_OK )
          throw PGException( result );
      }
    }

    returnvalue &= conn->commit();
    if ( mTransaction )
      mTransaction->dirtyLastSavePoint();
  }
  catch ( PGException &e )
  {
    pushError( tr( "PostGIS error while adding attributes: %1" ).arg( e.errorMessage() ) );
    conn->rollback();
    returnvalue = false;
  }

  loadFields();
  conn->unlock();
  return returnvalue;
}

bool QgsPostgresProvider::deleteAttributes( const QgsAttributeIds &ids )
{
  bool returnvalue = true;

  if ( mIsQuery )
    return false;

  QgsPostgresConn *conn = connectionRW();
  if ( !conn )
  {
    return false;
  }
  conn->lock();

  try
  {
    conn->begin();

    QList<int> idsList = ids.values();
    std::sort( idsList.begin(), idsList.end(), std::greater<int>() );

    for ( auto iter = idsList.constBegin(); iter != idsList.constEnd(); ++iter )
    {
      int index = *iter;
      if ( index < 0 || index >= mAttributeFields.count() )
        continue;

      QString column = mAttributeFields.at( index ).name();
      QString sql = QStringLiteral( "ALTER TABLE %1 DROP COLUMN %2" )
                    .arg( mQuery,
                          quotedIdentifier( column ) );

      //send sql statement and do error handling
      QgsPostgresResult result( conn->PQexec( sql ) );
      if ( result.PQresultStatus() != PGRES_COMMAND_OK )
        throw PGException( result );

      //delete the attribute from mAttributeFields
      mAttributeFields.remove( index );
    }

    returnvalue &= conn->commit();
    if ( mTransaction )
      mTransaction->dirtyLastSavePoint();
  }
  catch ( PGException &e )
  {
    pushError( tr( "PostGIS error while deleting attributes: %1" ).arg( e.errorMessage() ) );
    conn->rollback();
    returnvalue = false;
  }

  loadFields();
  conn->unlock();
  return returnvalue;
}

bool QgsPostgresProvider::renameAttributes( const QgsFieldNameMap &renamedAttributes )
{
  if ( mIsQuery )
    return false;


  QString sql = QStringLiteral( "BEGIN;" );

  QgsFieldNameMap::const_iterator renameIt = renamedAttributes.constBegin();
  bool returnvalue = true;
  for ( ; renameIt != renamedAttributes.constEnd(); ++renameIt )
  {
    int fieldIndex = renameIt.key();
    if ( fieldIndex < 0 || fieldIndex >= mAttributeFields.count() )
    {
      pushError( tr( "Invalid attribute index: %1" ).arg( fieldIndex ) );
      return false;
    }
    if ( mAttributeFields.indexFromName( renameIt.value() ) >= 0 )
    {
      //field name already in use
      pushError( tr( "Error renaming field %1: name '%2' already exists" ).arg( fieldIndex ).arg( renameIt.value() ) );
      return false;
    }

    sql += QStringLiteral( "ALTER TABLE %1 RENAME COLUMN %2 TO %3;" )
           .arg( mQuery,
                 quotedIdentifier( mAttributeFields.at( fieldIndex ).name() ),
                 quotedIdentifier( renameIt.value() ) );
  }
  sql += QLatin1String( "COMMIT;" );

  QgsPostgresConn *conn = connectionRW();
  if ( !conn )
  {
    return false;
  }
  conn->lock();

  try
  {
    conn->begin();
    //send sql statement and do error handling
    QgsPostgresResult result( conn->PQexec( sql ) );
    if ( result.PQresultStatus() != PGRES_COMMAND_OK )
      throw PGException( result );
    returnvalue = conn->commit();
    if ( mTransaction )
      mTransaction->dirtyLastSavePoint();
  }
  catch ( PGException &e )
  {
    pushError( tr( "PostGIS error while renaming attributes: %1" ).arg( e.errorMessage() ) );
    conn->rollback();
    returnvalue = false;
  }

  loadFields();
  conn->unlock();
  return returnvalue;
}

bool QgsPostgresProvider::changeAttributeValues( const QgsChangedAttributesMap &attr_map )
{
  bool returnvalue = true;

  if ( mIsQuery )
    return false;

  if ( attr_map.isEmpty() )
    return true;

  QgsPostgresConn *conn = connectionRW();
  if ( !conn )
    return false;

  conn->lock();

  try
  {
    conn->begin();

    // cycle through the features
    for ( QgsChangedAttributesMap::const_iterator iter = attr_map.constBegin(); iter != attr_map.constEnd(); ++iter )
    {
      QgsFeatureId fid = iter.key();

      // skip added features
      if ( FID_IS_NEW( fid ) )
        continue;

      const QgsAttributeMap &attrs = iter.value();
      if ( attrs.isEmpty() )
        continue;

      QString sql = QStringLiteral( "UPDATE %1 SET " ).arg( mQuery );

      bool pkChanged = false;

      // cycle through the changed attributes of the feature
      QString delim;
      int numChangedFields = 0;
      for ( QgsAttributeMap::const_iterator siter = attrs.constBegin(); siter != attrs.constEnd(); ++siter )
      {
        try
        {
          QgsField fld = field( siter.key() );

          pkChanged = pkChanged || mPrimaryKeyAttrs.contains( siter.key() );

          if ( mGeneratedValues.contains( siter.key() ) )
          {
            QgsLogger::warning( tr( "Changing the value of GENERATED field %1 is not allowed." ).arg( fld.name() ) );
            continue;
          }

          numChangedFields++;

          sql += delim + QStringLiteral( "%1=" ).arg( quotedIdentifier( fld.name() ) );
          delim = ',';

          QString defVal = defaultValueClause( siter.key() );
          if ( qgsVariantEqual( *siter, defVal ) )
          {
            sql += defVal.isNull() ? "NULL" : defVal;
          }
          else if ( fld.typeName() == QLatin1String( "geometry" ) )
          {
            sql += QStringLiteral( "%1(%2)" )
                   .arg( connectionRO()->majorVersion() < 2 ? "geomfromewkt" : "st_geomfromewkt",
                         quotedValue( siter->toString() ) );
          }
          else if ( fld.typeName() == QLatin1String( "geography" ) )
          {
            sql += QStringLiteral( "st_geographyfromewkt(%1)" )
                   .arg( quotedValue( siter->toString() ) );
          }
          else if ( fld.typeName() == QLatin1String( "jsonb" ) )
          {
            sql += QStringLiteral( "%1::jsonb" )
                   .arg( quotedJsonValue( siter.value() ) );
          }
          else if ( fld.typeName() == QLatin1String( "json" ) )
          {
            sql += QStringLiteral( "%1::json" )
                   .arg( quotedJsonValue( siter.value() ) );
          }
          else if ( fld.typeName() == QLatin1String( "bytea" ) )
          {
            sql += quotedByteaValue( siter.value() );
          }
          else
          {
            sql += quotedValue( *siter );
          }
        }
        catch ( PGFieldNotFound )
        {
          // Field was missing - shouldn't happen
        }
      }

      sql += QStringLiteral( " WHERE %1" ).arg( whereClause( fid ) );

      // Don't try to UPDATE an empty set of values (might happen if the table only has GENERATED fields,
      // or if the user only changed GENERATED fields in the form/attribute table.
      if ( numChangedFields > 0 )
      {
        QgsPostgresResult result( conn->PQexec( sql ) );
        if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
          throw PGException( result );
      }
      else // let the user know that no field was actually changed
      {
        QgsLogger::warning( tr( "No fields were updated on the database." ) );
      }

      // update feature id map if key was changed
      // PktInt64 also uses a fid map even if it is a stand alone field.
      if ( pkChanged && ( mPrimaryKeyType == PktFidMap || mPrimaryKeyType == PktInt64 ) )
      {
        QVariantList k = mShared->removeFid( fid );

        int keyCount = std::min( mPrimaryKeyAttrs.size(), k.size() );

        for ( int i = 0; i < keyCount; i++ )
        {
          int idx = mPrimaryKeyAttrs.at( i );
          if ( !attrs.contains( idx ) )
            continue;

          k[i] = attrs[ idx ];
        }

        mShared->insertFid( fid, k );
      }
    }

    returnvalue &= conn->commit();
    if ( mTransaction )
      mTransaction->dirtyLastSavePoint();
  }
  catch ( PGException &e )
  {
    pushError( tr( "PostGIS error while changing attributes: %1" ).arg( e.errorMessage() ) );
    conn->rollback();
    returnvalue = false;
  }

  conn->unlock();
  return returnvalue;
}

void QgsPostgresProvider::appendGeomParam( const QgsGeometry &geom, QStringList &params ) const
{
  if ( geom.isNull() )
  {
    params << QString();
    return;
  }

  QString param;

  QgsGeometry convertedGeom( convertToProviderType( geom ) );
  QByteArray wkb( !convertedGeom.isNull() ? convertedGeom.asWkb() : geom.asWkb() );
  const unsigned char *buf = reinterpret_cast< const unsigned char * >( wkb.constData() );
  int wkbSize = wkb.length();

  for ( int i = 0; i < wkbSize; ++i )
  {
    if ( connectionRO()->useWkbHex() )
      param += QStringLiteral( "%1" ).arg( ( int ) buf[i], 2, 16, QChar( '0' ) );
    else
      param += QStringLiteral( "\\%1" ).arg( ( int ) buf[i], 3, 8, QChar( '0' ) );
  }
  params << param;
}

bool QgsPostgresProvider::changeGeometryValues( const QgsGeometryMap &geometry_map )
{

  if ( mIsQuery || mGeometryColumn.isNull() )
    return false;

  QgsPostgresConn *conn = connectionRW();
  if ( !conn )
  {
    return false;
  }
  conn->lock();

  bool returnvalue = true;

  try
  {
    // Start the PostGIS transaction
    conn->begin();

    QString update;
    QgsPostgresResult result;

    if ( mSpatialColType == SctTopoGeometry )
    {
      // We will create a new TopoGeometry object with the new shape.
      // Later, we'll replace the old TopoGeometry with the new one,
      // to avoid orphans and retain higher level in an eventual
      // hierarchical definition
      update = QStringLiteral( "SELECT id(%1) FROM %2 o WHERE %3" )
               .arg( geomParam( 1 ),
                     mQuery,
                     pkParamWhereClause( 2 ) );

      QString getid = QStringLiteral( "SELECT id(%1) FROM %2 WHERE %3" )
                      .arg( quotedIdentifier( mGeometryColumn ),
                            mQuery,
                            pkParamWhereClause( 1 ) );

      QgsDebugMsgLevel( "getting old topogeometry id: " + getid, 2 );

      result = connectionRO()->PQprepare( QStringLiteral( "getid" ), getid, 1, nullptr );
      if ( result.PQresultStatus() != PGRES_COMMAND_OK )
      {
        QgsDebugMsg( QStringLiteral( "Exception thrown due to PQprepare of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
                     .arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( getid ) );
        throw PGException( result );
      }

      QString replace = QString( "UPDATE %1 SET %2="
                                 "( topology_id(%2),layer_id(%2),$1,type(%2) )"
                                 "WHERE %3" )
                        .arg( mQuery,
                              quotedIdentifier( mGeometryColumn ),
                              pkParamWhereClause( 2 ) );
      QgsDebugMsgLevel( "TopoGeom swap: " + replace, 2 );
      result = conn->PQprepare( QStringLiteral( "replacetopogeom" ), replace, 2, nullptr );
      if ( result.PQresultStatus() != PGRES_COMMAND_OK )
      {
        QgsDebugMsg( QStringLiteral( "Exception thrown due to PQprepare of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
                     .arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( replace ) );
        throw PGException( result );
      }

    }
    else
    {
      update = QStringLiteral( "UPDATE %1 SET %2=%3 WHERE %4" )
               .arg( mQuery,
                     quotedIdentifier( mGeometryColumn ),
                     geomParam( 1 ),
                     pkParamWhereClause( 2 ) );
    }

    QgsDebugMsgLevel( "updating: " + update, 2 );

    result = conn->PQprepare( QStringLiteral( "updatefeatures" ), update, 2, nullptr );
    if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
    {
      QgsDebugMsg( QStringLiteral( "Exception thrown due to PQprepare of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
                   .arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( update ) );
      throw PGException( result );
    }

    QgsDebugMsgLevel( QStringLiteral( "iterating over the map of changed geometries..." ), 2 );

    for ( QgsGeometryMap::const_iterator iter = geometry_map.constBegin();
          iter != geometry_map.constEnd();
          ++iter )
    {
      QgsDebugMsgLevel( "iterating over feature id " + FID_TO_STRING( iter.key() ), 2 );

      // Save the id of the current topogeometry
      long old_tg_id = -1;
      if ( mSpatialColType == SctTopoGeometry )
      {
        QStringList params;
        appendPkParams( iter.key(), params );
        result = connectionRO()->PQexecPrepared( QStringLiteral( "getid" ), params );
        if ( result.PQresultStatus() != PGRES_TUPLES_OK )
        {
          QgsDebugMsg( QStringLiteral( "Exception thrown due to PQexecPrepared of 'getid' returning != PGRES_TUPLES_OK (%1 != expected %2)" )
                       .arg( result.PQresultStatus() ).arg( PGRES_TUPLES_OK ) );
          throw PGException( result );
        }
        // TODO: watch out for NULL, handle somehow
        old_tg_id = result.PQgetvalue( 0, 0 ).toLong();
        QgsDebugMsgLevel( QStringLiteral( "Old TG id is %1" ).arg( old_tg_id ), 2 );
      }

      QStringList params;
      appendGeomParam( *iter, params );
      appendPkParams( iter.key(), params );

      result = conn->PQexecPrepared( QStringLiteral( "updatefeatures" ), params );
      if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
        throw PGException( result );

      if ( mSpatialColType == SctTopoGeometry )
      {
        long new_tg_id = result.PQgetvalue( 0, 0 ).toLong(); // new topogeo_id

        // Replace old TopoGeom with new TopoGeom, so that
        // any hierarchically defined TopoGeom will still have its
        // definition and we'll leave no orphans
        QString replace = QString( "DELETE FROM %1.relation WHERE "
                                   "layer_id = %2 AND topogeo_id = %3" )
                          .arg( quotedIdentifier( mTopoLayerInfo.topologyName ) )
                          .arg( mTopoLayerInfo.layerId )
                          .arg( old_tg_id );
        result = conn->PQexec( replace );
        if ( result.PQresultStatus() != PGRES_COMMAND_OK )
        {
          QgsDebugMsg( QStringLiteral( "Exception thrown due to PQexec of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
                       .arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( replace ) );
          throw PGException( result );
        }
        // TODO: use prepared query here
        replace = QString( "UPDATE %1.relation SET topogeo_id = %2 "
                           "WHERE layer_id = %3 AND topogeo_id = %4" )
                  .arg( quotedIdentifier( mTopoLayerInfo.topologyName ) )
                  .arg( old_tg_id )
                  .arg( mTopoLayerInfo.layerId )
                  .arg( new_tg_id );
        QgsDebugMsgLevel( "relation swap: " + replace, 2 );
        result = conn->PQexec( replace );
        if ( result.PQresultStatus() != PGRES_COMMAND_OK )
        {
          QgsDebugMsg( QStringLiteral( "Exception thrown due to PQexec of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
                       .arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( replace ) );
          throw PGException( result );
        }
      } // if TopoGeometry

    } // for each feature

    conn->PQexecNR( QStringLiteral( "DEALLOCATE updatefeatures" ) );
    if ( mSpatialColType == SctTopoGeometry )
    {
      connectionRO()->PQexecNR( QStringLiteral( "DEALLOCATE getid" ) );
      conn->PQexecNR( QStringLiteral( "DEALLOCATE replacetopogeom" ) );
    }

    returnvalue &= conn->commit();
    if ( mTransaction )
      mTransaction->dirtyLastSavePoint();
  }
  catch ( PGException &e )
  {
    pushError( tr( "PostGIS error while changing geometry values: %1" ).arg( e.errorMessage() ) );
    conn->rollback();
    conn->PQexecNR( QStringLiteral( "DEALLOCATE updatefeatures" ) );
    if ( mSpatialColType == SctTopoGeometry )
    {
      connectionRO()->PQexecNR( QStringLiteral( "DEALLOCATE getid" ) );
      conn->PQexecNR( QStringLiteral( "DEALLOCATE replacetopogeom" ) );
    }
    returnvalue = false;
  }

  conn->unlock();

  QgsDebugMsgLevel( QStringLiteral( "leaving." ), 4 );

  return returnvalue;
}

bool QgsPostgresProvider::changeFeatures( const QgsChangedAttributesMap &attr_map,
    const QgsGeometryMap &geometry_map )
{
  Q_ASSERT( mSpatialColType != SctTopoGeometry );

  bool returnvalue = true;

  if ( mIsQuery )
    return false;

  if ( attr_map.isEmpty() )
    return true;

  QgsPostgresConn *conn = connectionRW();
  if ( !conn )
    return false;

  conn->lock();

  try
  {
    conn->begin();

    QgsFeatureIds ids( qgis::listToSet( attr_map.keys() ) );
    ids |= qgis::listToSet( geometry_map.keys() );

    // cycle through the features
    const auto constIds = ids;
    for ( QgsFeatureId fid : constIds )
    {
      // skip added features
      if ( FID_IS_NEW( fid ) )
        continue;

      const QgsAttributeMap &attrs = attr_map.value( fid );
      if ( attrs.isEmpty() && !geometry_map.contains( fid ) )
        continue;

      QString sql = QStringLiteral( "UPDATE %1 SET " ).arg( mQuery );

      bool pkChanged = false;

      // cycle through the changed attributes of the feature
      QString delim;
      int numChangedFields = 0;

      for ( QgsAttributeMap::const_iterator siter = attrs.constBegin(); siter != attrs.constEnd(); ++siter )
      {
        try
        {
          QgsField fld = field( siter.key() );

          pkChanged = pkChanged || mPrimaryKeyAttrs.contains( siter.key() );

          if ( mGeneratedValues.contains( siter.key() ) )
          {
            QgsLogger::warning( tr( "Changing the value of GENERATED field %1 is not allowed." ).arg( fld.name() ) );
            continue;
          }

          numChangedFields++;

          sql += delim + QStringLiteral( "%1=" ).arg( quotedIdentifier( fld.name() ) );
          delim = ',';

          if ( fld.typeName() == QLatin1String( "geometry" ) )
          {
            sql += QStringLiteral( "%1(%2)" )
                   .arg( connectionRO()->majorVersion() < 2 ? "geomfromewkt" : "st_geomfromewkt",
                         quotedValue( siter->toString() ) );
          }
          else if ( fld.typeName() == QLatin1String( "geography" ) )
          {
            sql += QStringLiteral( "st_geographyfromewkt(%1)" )
                   .arg( quotedValue( siter->toString() ) );
          }
          else if ( fld.typeName() == QLatin1String( "jsonb" ) )
          {
            sql += QStringLiteral( "%1::jsonb" )
                   .arg( quotedJsonValue( siter.value() ) );
          }
          else if ( fld.typeName() == QLatin1String( "json" ) )
          {
            sql += QStringLiteral( "%1::json" )
                   .arg( quotedJsonValue( siter.value() ) );
          }
          else if ( fld.typeName() == QLatin1String( "bytea" ) )
          {
            sql += quotedByteaValue( siter.value() );
          }
          else
          {
            sql += quotedValue( *siter );
          }
        }
        catch ( PGFieldNotFound )
        {
          // Field was missing - shouldn't happen
        }
      }

      if ( !geometry_map.contains( fid ) )
      {
        // Don't try to UPDATE an empty set of values (might happen if the table only has GENERATED fields,
        // or if the user only changed GENERATED fields in the form/attribute table.
        if ( numChangedFields > 0 )
        {
          sql += QStringLiteral( " WHERE %1" ).arg( whereClause( fid ) );

          QgsPostgresResult result( conn->PQexec( sql ) );
          if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
            throw PGException( result );
        }
        else // let the user know that nothing has actually changed
        {
          QgsLogger::warning( tr( "No fields/geometries were updated on the database." ) );
        }
      }
      else
      {
        sql += QStringLiteral( "%1%2=%3" ).arg( delim, quotedIdentifier( mGeometryColumn ), geomParam( 1 ) );
        sql += QStringLiteral( " WHERE %1" ).arg( whereClause( fid ) );

        QgsPostgresResult result( conn->PQprepare( QStringLiteral( "updatefeature" ), sql, 1, nullptr ) );
        if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
        {
          QgsDebugMsg( QStringLiteral( "Exception thrown due to PQprepare of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
                       .arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( sql ) );
          throw PGException( result );
        }

        QStringList params;
        const QgsGeometry &geom = geometry_map[ fid ];
        appendGeomParam( geom, params );

        result = conn->PQexecPrepared( QStringLiteral( "updatefeature" ), params );
        if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
        {
          conn->rollback();
          conn->PQexecNR( QStringLiteral( "DEALLOCATE updatefeature" ) );
          throw PGException( result );
        }

        conn->PQexecNR( QStringLiteral( "DEALLOCATE updatefeature" ) );
      }

      // update feature id map if key was changed
      // PktInt64 also uses a fid map even though it is a single field.
      if ( pkChanged && ( mPrimaryKeyType == PktFidMap || mPrimaryKeyType == PktInt64 ) )
      {
        QVariantList k = mShared->removeFid( fid );

        for ( int i = 0; i < mPrimaryKeyAttrs.size(); i++ )
        {
          int idx = mPrimaryKeyAttrs.at( i );
          if ( !attrs.contains( idx ) )
            continue;

          k[i] = attrs[ idx ];
        }

        mShared->insertFid( fid, k );
      }
    }

    returnvalue &= conn->commit();
    if ( mTransaction )
      mTransaction->dirtyLastSavePoint();
  }
  catch ( PGException &e )
  {
    pushError( tr( "PostGIS error while changing attributes: %1" ).arg( e.errorMessage() ) );
    conn->rollback();
    returnvalue = false;
  }

  conn->unlock();

  QgsDebugMsgLevel( QStringLiteral( "leaving." ), 4 );

  return returnvalue;
}

QgsAttributeList QgsPostgresProvider::attributeIndexes() const
{
  QgsAttributeList lst;
  lst.reserve( mAttributeFields.count() );
  for ( int i = 0; i < mAttributeFields.count(); ++i )
    lst.append( i );
  return lst;
}

QgsVectorDataProvider::Capabilities QgsPostgresProvider::capabilities() const
{
  return mEnabledCapabilities;
}

QgsFeatureSource::SpatialIndexPresence QgsPostgresProvider::hasSpatialIndex() const
{
  QgsPostgresProviderConnection conn( mUri.uri(), QVariantMap() );
  try
  {
    return conn.spatialIndexExists( mUri.schema(), mUri.table(), mUri.geometryColumn() ) ? SpatialIndexPresent : SpatialIndexNotPresent;
  }
  catch ( QgsProviderConnectionException & )
  {
    return SpatialIndexUnknown;
  }
}

bool QgsPostgresProvider::setSubsetString( const QString &theSQL, bool updateFeatureCount )
{
  if ( theSQL.trimmed() == mSqlWhereClause )
    return true;

  QString prevWhere = mSqlWhereClause;

  mSqlWhereClause = theSQL.trimmed();

  QString sql = QStringLiteral( "SELECT * FROM %1" ).arg( mQuery );

  if ( !mSqlWhereClause.isEmpty() )
  {
    sql += QStringLiteral( " WHERE %1" ).arg( mSqlWhereClause );
  }

  sql += QLatin1String( " LIMIT 0" );

  QgsPostgresResult res( connectionRO()->PQexec( sql ) );
  if ( res.PQresultStatus() != PGRES_TUPLES_OK )
  {
    pushError( res.PQresultErrorMessage() );
    mSqlWhereClause = prevWhere;
    return false;
  }

#if 0
  // FIXME
  if ( mPrimaryKeyType == PktInt && !uniqueData( primaryKeyAttr ) )
  {
    sqlWhereClause = prevWhere;
    return false;
  }
#endif

  // Update datasource uri too
  mUri.setSql( theSQL );
  // Update yet another copy of the uri. Why are there 3 copies of the
  // uri? Perhaps this needs some rationalisation.....
  setDataSourceUri( mUri.uri( false ) );

  if ( updateFeatureCount )
  {
    reloadData();
  }
  else
  {
    mLayerExtent.setMinimal();
    emit dataChanged();
  }

  return true;
}

/**
 * Returns the feature count
 */
long long QgsPostgresProvider::featureCount() const
{
  long long featuresCounted = mShared->featuresCounted();
  if ( featuresCounted >= 0 )
    return featuresCounted;

  // See: https://github.com/qgis/QGIS/issues/25285 - QGIS crashes on featureCount())
  if ( ! connectionRO() )
  {
    return 0;
  }

  // get total number of features
  QString sql;

  // use estimated metadata even when there is a where clause,
  // although we get an incorrect feature count for the subset
  // - but make huge dataset usable.
  long long num = -1;
  if ( !mIsQuery && mUseEstimatedMetadata )
  {
    if ( relkind() == Relkind::View && connectionRO()->pgVersion() >= 90000 )
    {
      // parse explain output to estimate feature count
      // we don't use pg_class reltuples because it returns 0 for view
      sql = QStringLiteral( "EXPLAIN (FORMAT JSON) SELECT 1 FROM %1%2" ).arg( mQuery, filterWhereClause() );
      QgsPostgresResult result( connectionRO()->PQexec( sql ) );

      const QString json = result.PQgetvalue( 0, 0 );
      const QVariantList explain = QgsJsonUtils::parseJson( json ).toList();
      const QVariantMap countPlan = !explain.isEmpty() ? explain[0].toMap().value( "Plan" ).toMap() : QVariantMap();
      const QVariant nbRows = countPlan.value( "Plan Rows" );

      if ( nbRows.isValid() )
        num = nbRows.toLongLong();
      else
        QgsLogger::warning( QStringLiteral( "Cannot parse JSON explain result to estimate feature count (%1) : %2" ).arg( sql, json ) );
    }
    else
    {
      sql = QStringLiteral( "SELECT reltuples::bigint FROM pg_catalog.pg_class WHERE oid=regclass(%1)::oid" ).arg( quotedValue( mQuery ) );
      QgsPostgresResult result( connectionRO()->PQexec( sql ) );
      num = result.PQgetvalue( 0, 0 ).toLongLong();
    }
  }
  else
  {
    sql = QStringLiteral( "SELECT count(*) FROM %1%2" ).arg( mQuery, filterWhereClause() );
    QgsPostgresResult result( connectionRO()->PQexec( sql ) );

    QgsDebugMsgLevel( "number of features as text: " + result.PQgetvalue( 0, 0 ), 2 );

    num = result.PQgetvalue( 0, 0 ).toLongLong();
  }

  mShared->setFeaturesCounted( num );

  QgsDebugMsgLevel( "number of features: " + QString::number( num ), 2 );

  return num;
}

bool QgsPostgresProvider::empty() const
{
  QString sql = QStringLiteral( "SELECT EXISTS (SELECT * FROM %1%2 LIMIT 1)" ).arg( mQuery, filterWhereClause() );
  QgsPostgresResult res( connectionRO()->PQexec( sql ) );
  if ( res.PQresultStatus() != PGRES_TUPLES_OK )
  {
    pushError( res.PQresultErrorMessage() );
    return false;
  }

  return res.PQgetvalue( 0, 0 ) != QLatin1String( "t" );
}

QgsRectangle QgsPostgresProvider::extent() const
{
  if ( !isValid() || mGeometryColumn.isNull() )
    return QgsRectangle();

  if ( mSpatialColType == SctGeography )
    return QgsRectangle( -180.0, -90.0, 180.0, 90.0 );

  if ( mLayerExtent.isEmpty() )
  {
    QString sql;
    QgsPostgresResult result;
    QString ext;

    // get the extents
    if ( !mIsQuery && mUseEstimatedMetadata )
    {
      // do stats exists?
      sql = QStringLiteral( "SELECT count(*) FROM pg_stats WHERE schemaname=%1 AND tablename=%2 AND attname=%3" )
            .arg( quotedValue( mSchemaName ),
                  quotedValue( mTableName ),
                  quotedValue( mGeometryColumn ) );
      result = connectionRO()->PQexec( sql );
      if ( result.PQresultStatus() == PGRES_TUPLES_OK && result.PQntuples() == 1 )
      {
        if ( result.PQgetvalue( 0, 0 ).toInt() > 0 )
        {
          sql = QStringLiteral( "SELECT reltuples::bigint FROM pg_catalog.pg_class WHERE oid=regclass(%1)::oid" ).arg( quotedValue( mQuery ) );
          result = connectionRO()->PQexec( sql );
          if ( result.PQresultStatus() == PGRES_TUPLES_OK
               && result.PQntuples() == 1
               && result.PQgetvalue( 0, 0 ).toLong() > 0 )
          {
            sql = QStringLiteral( "SELECT %1(%2,%3,%4)" )
                  .arg( connectionRO()->majorVersion() < 2 ? "estimated_extent" :
                        ( connectionRO()->majorVersion() == 2 && connectionRO()->minorVersion() < 1 ? "st_estimated_extent" : "st_estimatedextent" ),
                        quotedValue( mSchemaName ),
                        quotedValue( mTableName ),
                        quotedValue( mGeometryColumn ) );
            result = mConnectionRO->PQexec( sql );
            if ( result.PQresultStatus() == PGRES_TUPLES_OK && result.PQntuples() == 1 && !result.PQgetisnull( 0, 0 ) )
            {
              ext = result.PQgetvalue( 0, 0 );

              // fix for what might be a PostGIS bug: when the extent crosses the
              // dateline extent() returns -180 to 180 (which appears right), but
              // estimated_extent() returns eastern bound of data (>-180) and
              // 180 degrees.
              if ( !ext.startsWith( QLatin1String( "-180 " ) ) && ext.contains( QLatin1String( ",180 " ) ) )
              {
                ext.clear();
              }
            }
          }
          else
          {
            // no features => ignore estimated extent
            ext.clear();
          }
        }
      }
      else
      {
        QgsDebugMsgLevel( QStringLiteral( "no column statistics for %1.%2.%3" ).arg( mSchemaName, mTableName, mGeometryColumn ), 2 );
      }
    }

    if ( ext.isEmpty() )
    {
      sql = QStringLiteral( "SELECT %1(%2%3) FROM %4%5" )
            .arg( connectionRO()->majorVersion() < 2 ? "extent" : "st_extent",
                  quotedIdentifier( mBoundingBoxColumn ),
                  mSpatialColType == SctPcPatch ? "::geometry" : "",
                  mQuery,
                  filterWhereClause() );

      result = connectionRO()->PQexec( sql );
      if ( result.PQresultStatus() != PGRES_TUPLES_OK )
        connectionRO()->PQexecNR( QStringLiteral( "ROLLBACK" ) );
      else if ( result.PQntuples() == 1 && !result.PQgetisnull( 0, 0 ) )
        ext = result.PQgetvalue( 0, 0 );
    }

    if ( !ext.isEmpty() )
    {
      QgsDebugMsgLevel( "Got extents using: " + sql, 2 );

      const QRegularExpression rx( "\\((.+) (.+),(.+) (.+)\\)" );
      const QRegularExpressionMatch match = rx.match( ext );
      if ( match.hasMatch() )
      {
        mLayerExtent.setXMinimum( match.captured( 1 ).toDouble() );
        mLayerExtent.setYMinimum( match.captured( 2 ).toDouble() );
        mLayerExtent.setXMaximum( match.captured( 3 ).toDouble() );
        mLayerExtent.setYMaximum( match.captured( 4 ).toDouble() );
      }
      else
      {
        QgsMessageLog::logMessage( tr( "result of extents query invalid: %1" ).arg( ext ), tr( "PostGIS" ) );
      }
    }

    QgsDebugMsgLevel( "Set extents to: " + mLayerExtent.toString(), 2 );
  }

  return mLayerExtent;
}

void QgsPostgresProvider::updateExtents()
{
  mLayerExtent.setMinimal();
}

bool QgsPostgresProvider::getGeometryDetails()
{
  if ( mGeometryColumn.isNull() )
  {
    mDetectedGeomType = QgsWkbTypes::NoGeometry;
    mValid = true;
    return true;
  }

  QgsPostgresResult result;
  QString sql;

  QString schemaName = mSchemaName;
  QString tableName = mTableName;
  QString geomCol = mGeometryColumn;
  QString geomColType;

  // Trust the datasource config means that we used requested geometry type and srid
  // We only need to get the spatial column type
  if ( ( mReadFlags & QgsDataProvider::FlagTrustDataSource ) &&
       mRequestedGeomType != QgsWkbTypes::Unknown &&
       !mRequestedSrid.isEmpty() )
  {
    if ( mIsQuery )
    {
      sql = QStringLiteral(
              "SELECT t.typname FROM pg_type t inner join (SELECT pg_typeof(%1) typeof FROM %2 LIMIT 1) g ON oid = g.typeof"
            ).arg( quotedIdentifier( geomCol ), mQuery );
    }
    else
    {
      sql = QStringLiteral(
              "SELECT t.typname FROM pg_type t inner join (SELECT pg_typeof(%1) typeof FROM %2.%3 LIMIT 1) g ON oid = g.typeof"
            ).arg( quotedIdentifier( geomCol ),
                   quotedIdentifier( schemaName ),
                   quotedIdentifier( tableName ) );
    }
    QgsDebugMsgLevel( QStringLiteral( "Getting the spatial column type: %1" ).arg( sql ), 2 );

    result = connectionRO()->PQexec( sql );
    if ( PGRES_TUPLES_OK == result.PQresultStatus() )
    {
      geomColType = result.PQgetvalue( 0, 0 );

      // Get spatial col type
      if ( geomColType == QLatin1String( "geometry" ) )
        mSpatialColType = SctGeometry;
      else if ( geomColType == QLatin1String( "geography" ) )
        mSpatialColType = SctGeography;
      else if ( geomColType == QLatin1String( "topogeometry" ) )
        mSpatialColType = SctTopoGeometry;
      else if ( geomColType == QLatin1String( "pcpatch" ) )
        mSpatialColType = SctPcPatch;
      else
        mSpatialColType = SctNone;

      // Use requested geometry type and srid
      mDetectedGeomType = mRequestedGeomType;
      mDetectedSrid     = mRequestedSrid;
      mValid = true;
      return true;
    }
    else
    {
      mValid = false;
      return false;
    }
  }

  if ( mIsQuery )
  {
    sql = QStringLiteral( "SELECT %1 FROM %2 LIMIT 0" ).arg( quotedIdentifier( mGeometryColumn ), mQuery );

    QgsDebugMsgLevel( QStringLiteral( "Getting geometry column: %1" ).arg( sql ), 2 );

    QgsPostgresResult result( connectionRO()->PQexec( sql ) );
    if ( PGRES_TUPLES_OK == result.PQresultStatus() )
    {
      Oid tableoid = result.PQftable( 0 );
      int column = result.PQftablecol( 0 );

      result = connectionRO()->PQexec( sql );
      if ( tableoid > 0 && PGRES_TUPLES_OK == result.PQresultStatus() )
      {
        sql = QStringLiteral( "SELECT pg_namespace.nspname,pg_class.relname FROM pg_class,pg_namespace WHERE pg_class.relnamespace=pg_namespace.oid AND pg_class.oid=%1" ).arg( tableoid );
        result = connectionRO()->PQexec( sql );

        if ( PGRES_TUPLES_OK == result.PQresultStatus() && 1 == result.PQntuples() )
        {
          schemaName = result.PQgetvalue( 0, 0 );
          tableName = result.PQgetvalue( 0, 1 );

          sql = QStringLiteral( "SELECT a.attname, t.typname FROM pg_attribute a, pg_type t WHERE a.attrelid=%1 AND a.attnum=%2 AND a.atttypid = t.oid" ).arg( tableoid ).arg( column );
          result = connectionRO()->PQexec( sql );
          if ( PGRES_TUPLES_OK == result.PQresultStatus() && 1 == result.PQntuples() )
          {
            geomCol = result.PQgetvalue( 0, 0 );
            geomColType = result.PQgetvalue( 0, 1 );
            if ( geomColType == QLatin1String( "geometry" ) )
              mSpatialColType = SctGeometry;
            else if ( geomColType == QLatin1String( "geography" ) )
              mSpatialColType = SctGeography;
            else if ( geomColType == QLatin1String( "topogeometry" ) )
              mSpatialColType = SctTopoGeometry;
            else if ( geomColType == QLatin1String( "pcpatch" ) )
              mSpatialColType = SctPcPatch;
            else
              mSpatialColType = SctNone;
          }
          else
          {
            schemaName = mSchemaName;
            tableName = mTableName;
          }
        }
      }
      else
      {
        schemaName.clear();
        tableName = mQuery;
      }
    }
    else
    {
      mValid = false;
      return false;
    }
  }

  QString detectedType;
  QString detectedSrid;
  if ( !schemaName.isEmpty() )
  {
    // check geometry columns
    sql = QStringLiteral( "SELECT upper(type),srid,coord_dimension FROM geometry_columns WHERE f_table_name=%1 AND f_geometry_column=%2 AND f_table_schema=%3" )
          .arg( quotedValue( tableName ),
                quotedValue( geomCol ),
                quotedValue( schemaName ) );

    QgsDebugMsgLevel( QStringLiteral( "Getting geometry column: %1" ).arg( sql ), 2 );
    result = connectionRO()->PQexec( sql );
    QgsDebugMsgLevel( QStringLiteral( "Geometry column query returned %1 rows" ).arg( result.PQntuples() ), 2 );

    if ( result.PQntuples() == 1 )
    {
      detectedType = result.PQgetvalue( 0, 0 );

      // Do not override the SRID if set in the data source URI
      if ( detectedSrid.isEmpty() )
      {
        detectedSrid = result.PQgetvalue( 0, 1 );
      }

      QString dim = result.PQgetvalue( 0, 2 );
      if ( dim == QLatin1String( "3" ) && !detectedType.endsWith( 'M' ) )
        detectedType += QLatin1Char( 'Z' );
      else if ( dim == QLatin1String( "4" ) )
        detectedType += QLatin1String( "ZM" );

      QString ds = result.PQgetvalue( 0, 1 );
      if ( ds != QLatin1String( "0" ) ) detectedSrid = ds;
      mSpatialColType = SctGeometry;
    }
    else
    {
      connectionRO()->PQexecNR( QStringLiteral( "COMMIT" ) );
    }

    if ( detectedType.isEmpty() )
    {
      // check geography columns
      sql = QStringLiteral( "SELECT upper(type),srid FROM geography_columns WHERE f_table_name=%1 AND f_geography_column=%2 AND f_table_schema=%3" )
            .arg( quotedValue( tableName ),
                  quotedValue( geomCol ),
                  quotedValue( schemaName ) );

      QgsDebugMsgLevel( QStringLiteral( "Getting geography column: %1" ).arg( sql ), 2 );
      result = connectionRO()->PQexec( sql, false );
      QgsDebugMsgLevel( QStringLiteral( "Geography column query returned %1" ).arg( result.PQntuples() ), 2 );

      if ( result.PQntuples() == 1 )
      {
        QString dt = result.PQgetvalue( 0, 0 );
        if ( dt != "GEOMETRY" ) detectedType = dt;
        QString ds = result.PQgetvalue( 0, 1 );
        if ( ds != "0" ) detectedSrid = ds;
        mSpatialColType = SctGeography;
      }
      else
      {
        connectionRO()->PQexecNR( QStringLiteral( "COMMIT" ) );
      }
    }

    if ( detectedType.isEmpty() && connectionRO()->hasTopology() )
    {
      // check topology.layer
      sql = QString( "SELECT CASE "
                     "WHEN l.feature_type = 1 THEN 'MULTIPOINT' "
                     "WHEN l.feature_type = 2 THEN 'MULTILINESTRING' "
                     "WHEN l.feature_type = 3 THEN 'MULTIPOLYGON' "
                     "WHEN l.feature_type = 4 THEN 'GEOMETRYCOLLECTION' "
                     "END AS type, t.srid FROM topology.layer l, topology.topology t "
                     "WHERE l.topology_id = t.id AND l.schema_name=%3 "
                     "AND l.table_name=%1 AND l.feature_column=%2" )
            .arg( quotedValue( tableName ),
                  quotedValue( geomCol ),
                  quotedValue( schemaName ) );

      QgsDebugMsgLevel( QStringLiteral( "Getting TopoGeometry column: %1" ).arg( sql ), 2 );
      result = connectionRO()->PQexec( sql, false );
      QgsDebugMsgLevel( QStringLiteral( "TopoGeometry column query returned %1" ).arg( result.PQntuples() ), 2 );

      if ( result.PQntuples() == 1 )
      {
        detectedType = result.PQgetvalue( 0, 0 );
        detectedSrid = result.PQgetvalue( 0, 1 );
        mSpatialColType = SctTopoGeometry;
      }
      else
      {
        connectionRO()->PQexecNR( QStringLiteral( "COMMIT" ) );
      }
    }

    if ( detectedType.isEmpty() && connectionRO()->hasPointcloud() )
    {
      // check pointcloud columns
      sql = QStringLiteral( "SELECT 'POLYGON',srid FROM pointcloud_columns WHERE \"table\"=%1 AND \"column\"=%2 AND \"schema\"=%3" )
            .arg( quotedValue( tableName ),
                  quotedValue( geomCol ),
                  quotedValue( schemaName ) );

      QgsDebugMsgLevel( QStringLiteral( "Getting pointcloud column: %1" ).arg( sql ), 2 );
      result = connectionRO()->PQexec( sql, false );
      QgsDebugMsgLevel( QStringLiteral( "Pointcloud column query returned %1" ).arg( result.PQntuples() ), 2 );

      if ( result.PQntuples() == 1 )
      {
        detectedType = result.PQgetvalue( 0, 0 );
        detectedSrid = result.PQgetvalue( 0, 1 );
        mSpatialColType = SctPcPatch;
      }
      else
      {
        connectionRO()->PQexecNR( QStringLiteral( "COMMIT" ) );
      }
    }

    if ( mSpatialColType == SctNone )
    {
      sql = QString( "SELECT t.typname FROM "
                     "pg_attribute a, pg_class c, pg_namespace n, pg_type t "
                     "WHERE a.attrelid=c.oid AND c.relnamespace=n.oid "
                     "AND a.atttypid=t.oid "
                     "AND n.nspname=%3 AND c.relname=%1 AND a.attname=%2" )
            .arg( quotedValue( tableName ),
                  quotedValue( geomCol ),
                  quotedValue( schemaName ) );
      QgsDebugMsgLevel( QStringLiteral( "Getting column datatype: %1" ).arg( sql ), 2 );
      result = connectionRO()->PQexec( sql, false );
      QgsDebugMsgLevel( QStringLiteral( "Column datatype query returned %1" ).arg( result.PQntuples() ), 2 );
      if ( result.PQntuples() == 1 )
      {
        geomColType = result.PQgetvalue( 0, 0 );
        if ( geomColType == QLatin1String( "geometry" ) )
          mSpatialColType = SctGeometry;
        else if ( geomColType == QLatin1String( "geography" ) )
          mSpatialColType = SctGeography;
        else if ( geomColType == QLatin1String( "topogeometry" ) )
          mSpatialColType = SctTopoGeometry;
        else if ( geomColType == QLatin1String( "pcpatch" ) )
          mSpatialColType = SctPcPatch;
      }
      else
      {
        connectionRO()->PQexecNR( QStringLiteral( "COMMIT" ) );
      }
    }
  }
  else
  {
    sql = QStringLiteral( "SELECT %1 FROM %2 LIMIT 0" ).arg( quotedIdentifier( mGeometryColumn ), mQuery );
    result = connectionRO()->PQexec( sql );
    if ( PGRES_TUPLES_OK == result.PQresultStatus() )
    {
      sql = QStringLiteral( "SELECT (SELECT t.typname FROM pg_type t WHERE oid = %1), upper(postgis_typmod_type(%2)), postgis_typmod_srid(%2)" )
            .arg( QString::number( result.PQftype( 0 ) ), QString::number( result.PQfmod( 0 ) ) );
      result = connectionRO()->PQexec( sql, false );
      if ( result.PQntuples() == 1 )
      {
        geomColType  = result.PQgetvalue( 0, 0 );
        detectedType = result.PQgetvalue( 0, 1 );
        detectedSrid = result.PQgetvalue( 0, 2 );
        if ( geomColType == QLatin1String( "geometry" ) )
          mSpatialColType = SctGeometry;
        else if ( geomColType == QLatin1String( "geography" ) )
          mSpatialColType = SctGeography;
        else if ( geomColType == QLatin1String( "topogeometry" ) )
          mSpatialColType = SctTopoGeometry;
        else if ( geomColType == QLatin1String( "pcpatch" ) )
          mSpatialColType = SctPcPatch;
        else
        {
          detectedType = mRequestedGeomType == QgsWkbTypes::Unknown ? QString() : QgsPostgresConn::postgisWkbTypeName( mRequestedGeomType );
          detectedSrid = mRequestedSrid;
        }
      }
      else
      {
        connectionRO()->PQexecNR( QStringLiteral( "COMMIT" ) );
        detectedType = mRequestedGeomType == QgsWkbTypes::Unknown ? QString() : QgsPostgresConn::postgisWkbTypeName( mRequestedGeomType );
      }
    }
    else
    {
      mValid = false;
      return false;
    }
  }

  mDetectedGeomType = QgsPostgresConn::wkbTypeFromPostgis( detectedType );
  mDetectedSrid     = detectedSrid;

  if ( mDetectedGeomType == QgsWkbTypes::Unknown )
  {

    QgsPostgresLayerProperty layerProperty;
    if ( !mIsQuery )
    {
      layerProperty.schemaName = schemaName;
      layerProperty.tableName  = tableName;
    }
    else
    {
      layerProperty.schemaName.clear();
      layerProperty.tableName  = mQuery;
    }
    layerProperty.geometryColName = mGeometryColumn;
    layerProperty.geometryColType = mSpatialColType;

    QString delim;

    if ( !mSqlWhereClause.isEmpty() )
    {
      layerProperty.sql += delim + '(' + mSqlWhereClause + ')';
      delim = QStringLiteral( " AND " );
    }

    connectionRO()->retrieveLayerTypes( layerProperty, mUseEstimatedMetadata );

    mSpatialColType = layerProperty.geometryColType;

    if ( layerProperty.size() == 0 )
    {
      // no data - so take what's requested/detected
      if ( mRequestedGeomType == QgsWkbTypes::Unknown || mDetectedSrid.isEmpty() )
      {
        QgsMessageLog::logMessage( tr( "Geometry type and srid for empty column %1 of %2 undefined." ).arg( mGeometryColumn, mQuery ) );
      }
    }
    else
    {
      int i;
      for ( i = 0; i < layerProperty.size(); i++ )
      {
        QgsWkbTypes::Type wkbType = layerProperty.types.at( i );

        if ( ( wkbType != QgsWkbTypes::Unknown && ( mRequestedGeomType == QgsWkbTypes::Unknown || mRequestedGeomType == wkbType ) ) &&
             ( mRequestedSrid.isEmpty() || layerProperty.srids.at( i ) == mRequestedSrid.toInt() ) )
          break;
      }

      // requested type && srid is available
      if ( i < layerProperty.size() )
      {
        if ( layerProperty.size() == 1 )
        {
          // only what we requested is available
          mDetectedGeomType = layerProperty.types.at( 0 );
          mDetectedSrid     = QString::number( layerProperty.srids.at( 0 ) );
        }
      }
      else
      {
        // geometry type undetermined or not unrequested
        QgsMessageLog::logMessage( tr( "Feature type or srid for %1 of %2 could not be determined or was not requested." ).arg( mGeometryColumn, mQuery ) );
      }
    }
  }

  QgsDebugMsgLevel( QStringLiteral( "Detected SRID is %1" ).arg( mDetectedSrid ), 2 );
  QgsDebugMsgLevel( QStringLiteral( "Requested SRID is %1" ).arg( mRequestedSrid ), 2 );
  QgsDebugMsgLevel( QStringLiteral( "Detected type is %1" ).arg( mDetectedGeomType ), 2 );
  QgsDebugMsgLevel( QStringLiteral( "Requested type is %1" ).arg( mRequestedGeomType ), 2 );

  mValid = ( mDetectedGeomType != QgsWkbTypes::Unknown || mRequestedGeomType != QgsWkbTypes::Unknown )
           && ( !mDetectedSrid.isEmpty() || !mRequestedSrid.isEmpty() );

  if ( !mValid )
    return false;

  QgsDebugMsgLevel( QStringLiteral( "Spatial column type is %1" ).arg( QgsPostgresConn::displayStringForGeomType( mSpatialColType ) ), 2 );

  return mValid;
}

bool QgsPostgresProvider::convertField( QgsField &field, const QMap<QString, QVariant> *options )
{
  //determine field type to use for strings
  QString stringFieldType = QStringLiteral( "varchar" );
  if ( options && options->value( QStringLiteral( "dropStringConstraints" ), false ).toBool() )
  {
    //drop string length constraints by using PostgreSQL text type for strings
    stringFieldType = QStringLiteral( "text" );
  }

  QString fieldType = stringFieldType; //default to string
  int fieldSize = field.length();
  int fieldPrec = field.precision();
  switch ( field.type() )
  {
    case QVariant::LongLong:
      fieldType = QStringLiteral( "int8" );
      fieldPrec = 0;
      break;

    case QVariant::DateTime:
      fieldType = QStringLiteral( "timestamp without time zone" );
      break;

    case QVariant::Time:
      fieldType = QStringLiteral( "time" );
      break;

    case QVariant::String:
      fieldType = stringFieldType;
      fieldPrec = 0;
      break;

    case QVariant::Int:
      fieldType = QStringLiteral( "int4" );
      fieldPrec = 0;
      break;

    case QVariant::Date:
      fieldType = QStringLiteral( "date" );
      fieldPrec = 0;
      break;

    case QVariant::Map:
      fieldType = field.typeName();
      if ( fieldType.isEmpty() )
        fieldType = QStringLiteral( "hstore" );
      fieldPrec = 0;
      break;

    case QVariant::StringList:
      fieldType = QStringLiteral( "_text" );
      fieldPrec = 0;
      break;

    case QVariant::List:
    {
      QgsField sub( QString(), field.subType(), QString(), fieldSize, fieldPrec );
      if ( !convertField( sub, nullptr ) ) return false;
      fieldType = "_" + sub.typeName();
      fieldPrec = 0;
      break;
    }

    case QVariant::Double:
      if ( fieldSize > 18 )
      {
        fieldType = QStringLiteral( "numeric" );
        fieldSize = -1;
      }
      else
      {
        fieldType = QStringLiteral( "float8" );
      }
      fieldPrec = 0;
      break;

    case QVariant::Bool:
      fieldType = QStringLiteral( "bool" );
      fieldPrec = 0;
      fieldSize = -1;
      break;

    case QVariant::ByteArray:
      fieldType = QStringLiteral( "bytea" );
      fieldPrec = 0;
      break;

    default:
      return false;
  }

  field.setTypeName( fieldType );
  field.setLength( fieldSize );
  field.setPrecision( fieldPrec );
  return true;
}


void postgisGeometryType( QgsWkbTypes::Type wkbType, QString &geometryType, int &dim )
{
  dim = 2;
  QgsWkbTypes::Type flatType = QgsWkbTypes::flatType( wkbType );
  geometryType = QgsWkbTypes::displayString( flatType ).toUpper();
  switch ( flatType )
  {
    case QgsWkbTypes::Unknown:
      geometryType = QStringLiteral( "GEOMETRY" );
      break;

    case QgsWkbTypes::NoGeometry:
      geometryType.clear();
      dim = 0;
      break;

    default:
      break;
  }

  if ( QgsWkbTypes::hasZ( wkbType ) && QgsWkbTypes::hasM( wkbType ) )
  {
    dim = 4;
  }
  else if ( QgsWkbTypes::hasZ( wkbType ) )
  {
    dim = 3;
  }
  else if ( QgsWkbTypes::hasM( wkbType ) )
  {
    geometryType += QLatin1Char( 'M' );
    dim = 3;
  }
  else if ( wkbType >= QgsWkbTypes::Point25D && wkbType <= QgsWkbTypes::MultiPolygon25D )
  {
    dim = 3;
  }
}

Qgis::VectorExportResult QgsPostgresProvider::createEmptyLayer( const QString &uri,
    const QgsFields &fields,
    QgsWkbTypes::Type wkbType,
    const QgsCoordinateReferenceSystem &srs,
    bool overwrite,
    QMap<int, int> *oldToNewAttrIdxMap,
    QString *errorMessage,
    const QMap<QString, QVariant> *options )
{
  // populate members from the uri structure
  QgsDataSourceUri dsUri( uri );

  QString schemaName = dsUri.schema();
  QString tableName = dsUri.table();

  QString geometryColumn = dsUri.geometryColumn();
  QString geometryType;

  QString primaryKey = dsUri.keyColumn();
  QString primaryKeyType;

  QStringList pkList;
  QStringList pkType;

  QString schemaTableName;
  if ( !schemaName.isEmpty() )
  {
    schemaTableName += quotedIdentifier( schemaName ) + '.';
  }
  schemaTableName += quotedIdentifier( tableName );

  QgsDebugMsgLevel( QStringLiteral( "Connection info is: %1" ).arg( dsUri.connectionInfo( false ) ), 2 );
  QgsDebugMsgLevel( QStringLiteral( "Geometry column is: %1" ).arg( geometryColumn ), 2 );
  QgsDebugMsgLevel( QStringLiteral( "Schema is: %1" ).arg( schemaName ), 2 );
  QgsDebugMsgLevel( QStringLiteral( "Table name is: %1" ).arg( tableName ), 2 );

  // create the table
  QgsPostgresConn *conn = QgsPostgresConn::connectDb( dsUri.connectionInfo( false ), false );
  if ( !conn )
  {
    if ( errorMessage )
      *errorMessage = QObject::tr( "Connection to database failed" );
    return Qgis::VectorExportResult::ErrorConnectionFailed;
  }

  // get the pk's name and type
  // Try to find a PK candidate from numeric NOT NULL / UNIQUE columns
  if ( primaryKey.isEmpty() )
  {
    for ( const auto &field : std::as_const( fields ) )
    {
      if ( field.isNumeric() &&
           ( field.constraints().constraints() & QgsFieldConstraints::Constraint::ConstraintUnique ) &&
           ( field.constraints().constraints() & QgsFieldConstraints::Constraint::ConstraintNotNull ) &&
           ( field.constraints().constraints() & QgsFieldConstraints::ConstraintOrigin::ConstraintOriginProvider ) )
      {
        primaryKey = field.name();
        break;
      }
    }
  }

  // if no pk name was passed or guessed, define the new pk field name
  if ( primaryKey.isEmpty() )
  {
    int index = 0;
    QString pk = primaryKey = QStringLiteral( "id" );
    for ( int fldIdx = 0; fldIdx < fields.count(); ++fldIdx )
    {
      if ( fields.at( fldIdx ).name() == primaryKey )
      {
        // it already exists, try again with a new name
        primaryKey = QStringLiteral( "%1_%2" ).arg( pk ).arg( index++ );
        fldIdx = -1; // it is incremented in the for loop, i.e. restarts at 0
      }
    }

    pkList = QStringList( primaryKey );
    pkType = QStringList( QStringLiteral( "serial" ) );
  }
  else
  {
    pkList = parseUriKey( primaryKey );
    const auto constPkList = pkList;
    for ( const QString &col : constPkList )
    {
      // search for the passed field
      QString type;
      for ( int fldIdx = 0; fldIdx < fields.count(); ++fldIdx )
      {
        if ( fields[fldIdx].name() == col )
        {
          // found, get the field type
          QgsField fld = fields[fldIdx];
          if ( convertField( fld, options ) )
          {
            type = fld.typeName();
            break;
          }
        }
      }
      if ( type.isEmpty() ) type = QStringLiteral( "serial" );
      else
      {
        // if the pk field's type is one of the postgres integer types,
        // use the equivalent autoincremental type (serialN)
        if ( primaryKeyType == QLatin1String( "int2" ) || primaryKeyType == QLatin1String( "int4" ) )
        {
          primaryKeyType = QStringLiteral( "serial" );
        }
        else if ( primaryKeyType == QLatin1String( "int8" ) )
        {
          primaryKeyType = QStringLiteral( "serial8" );
        }
      }
      pkType << type;
    }
  }

  try
  {
    conn->PQexecNR( QStringLiteral( "BEGIN" ) );

    // We want a valid schema name ...
    if ( schemaName.isEmpty() )
    {
      QString sql = QString( "SELECT current_schema" );
      QgsPostgresResult result( conn->PQexec( sql ) );
      if ( result.PQresultStatus() != PGRES_TUPLES_OK )
        throw PGException( result );
      schemaName = result.PQgetvalue( 0, 0 );
      if ( schemaName.isEmpty() )
      {
        schemaName = QStringLiteral( "public" );
      }
    }

    QString sql = QString( "SELECT 1"
                           " FROM pg_class AS cls JOIN pg_namespace AS nsp"
                           " ON nsp.oid=cls.relnamespace "
                           " WHERE cls.relname=%1 AND nsp.nspname=%2" )
                  .arg( quotedValue( tableName ),
                        quotedValue( schemaName ) );

    QgsPostgresResult result( conn->PQexec( sql ) );
    if ( result.PQresultStatus() != PGRES_TUPLES_OK )
      throw PGException( result );

    bool exists = result.PQntuples() > 0;

    if ( exists && overwrite )
    {
      // delete the table if exists, then re-create it
      QString sql = QString( "SELECT DropGeometryTable(%1,%2)"
                             " FROM pg_class AS cls JOIN pg_namespace AS nsp"
                             " ON nsp.oid=cls.relnamespace "
                             " WHERE cls.relname=%2 AND nsp.nspname=%1" )
                    .arg( quotedValue( schemaName ),
                          quotedValue( tableName ) );

      result = conn->PQexec( sql );
      if ( result.PQresultStatus() != PGRES_TUPLES_OK )
        throw PGException( result );
    }

    sql = QStringLiteral( "CREATE TABLE %1(" ) .arg( schemaTableName );
    QString pk;
    for ( int i = 0; i < pkList.size(); ++i )
    {
      QString col = pkList[i];
      const QString &type = pkType[i];

      if ( options && options->value( QStringLiteral( "lowercaseFieldNames" ), false ).toBool() )
      {
        col = col.toLower();
      }
      else
      {
        col = quotedIdentifier( col ); // no need to quote lowercase field
      }

      if ( i )
      {
        pk  += QLatin1Char( ',' );
        sql += QLatin1Char( ',' );
      }

      pk += col;
      sql += col + " " + type;
    }
    sql += QStringLiteral( ", PRIMARY KEY (%1) )" ) .arg( pk );

    result = conn->PQexec( sql );
    if ( result.PQresultStatus() != PGRES_COMMAND_OK )
      throw PGException( result );

    // get geometry type, dim and srid
    int dim = 2;
    long srid = srs.postgisSrid();

    postgisGeometryType( wkbType, geometryType, dim );

    // create geometry column
    if ( !geometryType.isEmpty() )
    {
      sql = QStringLiteral( "SELECT AddGeometryColumn(%1,%2,%3,%4,%5,%6)" )
            .arg( quotedValue( schemaName ),
                  quotedValue( tableName ),
                  quotedValue( geometryColumn ) )
            .arg( srid )
            .arg( quotedValue( geometryType ) )
            .arg( dim );

      result = conn->PQexec( sql );
      if ( result.PQresultStatus() != PGRES_TUPLES_OK )
        throw PGException( result );
    }
    else
    {
      geometryColumn.clear();
    }

    conn->PQexecNR( QStringLiteral( "COMMIT" ) );
  }
  catch ( PGException &e )
  {
    if ( errorMessage )
      *errorMessage = QObject::tr( "Creation of data source %1 failed: \n%2" )
                      .arg( schemaTableName,
                            e.errorMessage() );

    conn->PQexecNR( QStringLiteral( "ROLLBACK" ) );
    conn->unref();
    return Qgis::VectorExportResult::ErrorCreatingLayer;
  }
  conn->unref();

  QgsDebugMsgLevel( QStringLiteral( "layer %1 created" ).arg( schemaTableName ), 2 );

  // use the provider to edit the table
  dsUri.setDataSource( schemaName, tableName, geometryColumn, QString(), primaryKey );

  QgsDataProvider::ProviderOptions providerOptions;
  QgsDataProvider::ReadFlags flags = QgsDataProvider::ReadFlags();
  std::unique_ptr< QgsPostgresProvider > provider = std::make_unique< QgsPostgresProvider >( dsUri.uri( false ), providerOptions, flags );
  if ( !provider->isValid() )
  {
    if ( errorMessage )
      *errorMessage = QObject::tr( "Loading of the layer %1 failed" ).arg( schemaTableName );

    return Qgis::VectorExportResult::ErrorInvalidLayer;
  }

  QgsDebugMsgLevel( QStringLiteral( "layer loaded" ), 2 );

  // add fields to the layer
  if ( oldToNewAttrIdxMap )
    oldToNewAttrIdxMap->clear();

  if ( fields.size() > 0 )
  {
    int offset = 1;

    // get the list of fields
    QList<QgsField> flist;
    for ( int fldIdx = 0; fldIdx < fields.count(); ++fldIdx )
    {
      QgsField fld = fields.at( fldIdx );

      if ( fld.name() == geometryColumn )
      {
        //the "lowercaseFieldNames" option does not affect the name of the geometry column, so we perform
        //this test before converting the field name to lowercase
        QgsDebugMsgLevel( QStringLiteral( "Found a field with the same name of the geometry column. Skip it!" ), 2 );
        continue;
      }

      if ( options && options->value( QStringLiteral( "lowercaseFieldNames" ), false ).toBool() )
      {
        //convert field name to lowercase
        fld.setName( fld.name().toLower() );
      }

      int pkIdx = -1;
      for ( int i = 0; i < pkList.size(); ++i )
      {
        QString col = pkList[i];
        if ( options && options->value( QStringLiteral( "lowercaseFieldNames" ), false ).toBool() )
        {
          //convert field name to lowercase (TODO: avoid doing this
          //over and over)
          col = col.toLower();
        }
        if ( fld.name() == col )
        {
          pkIdx = i;
          break;
        }
      }
      if ( pkIdx >= 0 )
      {
        oldToNewAttrIdxMap->insert( fldIdx, pkIdx );
        continue;
      }

      if ( !( options && options->value( QStringLiteral( "skipConvertFields" ), false ).toBool() ) && !convertField( fld, options ) )
      {
        if ( errorMessage )
          *errorMessage = QObject::tr( "Unsupported type for field %1" ).arg( fld.name() );

        return Qgis::VectorExportResult::ErrorAttributeTypeUnsupported;
      }

      QgsDebugMsgLevel( QStringLiteral( "creating field #%1 -> #%2 name %3 type %4 typename %5 width %6 precision %7" )
                        .arg( fldIdx ).arg( offset )
                        .arg( fld.name(), QVariant::typeToName( fld.type() ), fld.typeName() )
                        .arg( fld.length() ).arg( fld.precision() ), 2
                      );

      flist.append( fld );
      if ( oldToNewAttrIdxMap )
        oldToNewAttrIdxMap->insert( fldIdx, offset++ );
    }

    if ( !provider->addAttributes( flist ) )
    {
      if ( errorMessage )
        *errorMessage = QObject::tr( "Creation of fields failed:\n%1" ).arg( provider->errors().join( '\n' ) );

      return Qgis::VectorExportResult::ErrorAttributeCreationFailed;
    }

    QgsDebugMsgLevel( QStringLiteral( "Done creating fields" ), 2 );
  }
  return Qgis::VectorExportResult::Success;
}

QgsCoordinateReferenceSystem QgsPostgresProvider::crs() const
{
  QgsCoordinateReferenceSystem srs;
  int srid = mRequestedSrid.isEmpty() ? mDetectedSrid.toInt() : mRequestedSrid.toInt();

  {
    static QMutex sMutex;
    QMutexLocker locker( &sMutex );
    static QMap<int, QgsCoordinateReferenceSystem> sCrsCache;
    if ( sCrsCache.contains( srid ) )
      srs = sCrsCache.value( srid );
    else
    {
      QgsPostgresConn *conn = connectionRO();
      if ( conn )
      {
        QgsPostgresResult result( conn->PQexec( QStringLiteral( "SELECT auth_name, auth_srid, srtext, proj4text FROM spatial_ref_sys WHERE srid=%1" ).arg( srid ) ) );
        if ( result.PQresultStatus() == PGRES_TUPLES_OK )
        {
          const QString authName = result.PQgetvalue( 0, 0 );
          const QString authSRID = result.PQgetvalue( 0, 1 );
          const QString srText = result.PQgetvalue( 0, 2 );
          bool ok = false;
          if ( authName == QLatin1String( "EPSG" ) || authName == QLatin1String( "ESRI" ) )
          {
            ok = srs.createFromUserInput( authName + ':' + authSRID );
          }
          if ( !ok && !srText.isEmpty() )
          {
            ok = srs.createFromUserInput( srText );
          }
          if ( !ok )
            srs = QgsCoordinateReferenceSystem::fromProj( result.PQgetvalue( 0, 3 ) );
          sCrsCache.insert( srid, srs );
        }
      }
    }
  }
  return srs;
}

QString QgsPostgresProvider::subsetString() const
{
  return mSqlWhereClause;
}

QString QgsPostgresProvider::getTableName()
{
  return mTableName;
}

size_t QgsPostgresProvider::layerCount() const
{
  return 1;                   // XXX need to return actual number of layers
} // QgsPostgresProvider::layerCount()


QString  QgsPostgresProvider::name() const
{
  return POSTGRES_KEY;
} //  QgsPostgresProvider::name()

QString  QgsPostgresProvider::description() const
{
  QString pgVersion( tr( "PostgreSQL version: unknown" ) );
  QString postgisVersion( tr( "unknown" ) );

  if ( auto *lConnectionRO = connectionRO() )
  {
    QgsPostgresResult result;

    result = lConnectionRO->PQexec( QStringLiteral( "SELECT version()" ) );
    if ( result.PQresultStatus() == PGRES_TUPLES_OK )
    {
      pgVersion = result.PQgetvalue( 0, 0 );
    }

    result = lConnectionRO->PQexec( QStringLiteral( "SELECT postgis_version()" ) );
    if ( result.PQresultStatus() == PGRES_TUPLES_OK )
    {
      postgisVersion = result.PQgetvalue( 0, 0 );
    }
  }
  else
  {
    pgVersion = tr( "PostgreSQL not connected" );
  }

  return tr( "PostgreSQL/PostGIS provider\n%1\nPostGIS %2" ).arg( pgVersion, postgisVersion );
} //  QgsPostgresProvider::description()

static void jumpSpace( const QString &txt, int &i )
{
  while ( i < txt.length() && txt.at( i ).isSpace() )
    ++i;
}

QString QgsPostgresProvider::getNextString( const QString &txt, int &i, const QString &sep )
{
  jumpSpace( txt, i );
  if ( i < txt.length() && txt.at( i ) == '"' )
  {
    const thread_local QRegularExpression stringRe( QRegularExpression::anchoredPattern( "^\"((?:\\\\.|[^\"\\\\])*)\".*" ) );
    const QRegularExpressionMatch match = stringRe.match( txt.mid( i ) );
    if ( !match.hasMatch() )
    {
      QgsMessageLog::logMessage( tr( "Cannot find end of double quoted string: %1" ).arg( txt ), tr( "PostGIS" ) );
      return QString();
    }
    i += match.captured( 1 ).length() + 2;
    jumpSpace( txt, i );
    if ( !QStringView{txt}.mid( i ).startsWith( sep ) && i < txt.length() )
    {
      QgsMessageLog::logMessage( tr( "Cannot find separator: %1" ).arg( txt.mid( i ) ), tr( "PostGIS" ) );
      return QString();
    }
    i += sep.length();
    return match.captured( 1 ).replace( QLatin1String( "\\\"" ), QLatin1String( "\"" ) ).replace( QLatin1String( "\\\\" ), QLatin1String( "\\" ) );
  }
  else
  {
    int start = i;
    for ( ; i < txt.length(); i++ )
    {
      if ( QStringView{txt}.mid( i ).startsWith( sep ) )
      {
        QStringView v( QStringView{txt}.mid( start, i - start ) );
        i += sep.length();
        return v.trimmed().toString();
      }
    }
    return QStringView{txt}.mid( start, i - start ).trimmed().toString();
  }
}

QVariant QgsPostgresProvider::parseHstore( const QString &txt )
{
  QVariantMap result;
  int i = 0;
  while ( i < txt.length() )
  {
    QString key = getNextString( txt, i, QStringLiteral( "=>" ) );
    QString value = getNextString( txt, i, QStringLiteral( "," ) );
    if ( key.isNull() || value.isNull() )
    {
      QgsMessageLog::logMessage( tr( "Error parsing hstore: %1" ).arg( txt ), tr( "PostGIS" ) );
      break;
    }
    result.insert( key, value );
  }

  return result;
}

QVariant QgsPostgresProvider::parseJson( const QString &txt )
{
  return QgsJsonUtils::parseJson( txt );
}

QVariant QgsPostgresProvider::parseOtherArray( const QString &txt, QVariant::Type subType, const QString &typeName )
{
  int i = 0;
  QVariantList result;
  while ( i < txt.length() )
  {
    const QString value = getNextString( txt, i, QStringLiteral( "," ) );
    if ( value.isNull() )
    {
      QgsMessageLog::logMessage( tr( "Error parsing array: %1" ).arg( txt ), tr( "PostGIS" ) );
      break;
    }
    result.append( QgsPostgresProvider::convertValue( subType, QVariant::Invalid, value, typeName ) );
  }
  return result;
}

QVariant QgsPostgresProvider::parseStringArray( const QString &txt )
{
  int i = 0;
  QStringList result;
  while ( i < txt.length() )
  {
    const QString value = getNextString( txt, i, QStringLiteral( "," ) );
    if ( value.isNull() )
    {
      QgsMessageLog::logMessage( tr( "Error parsing array: %1" ).arg( txt ), tr( "PostGIS" ) );
      break;
    }
    result.append( value );
  }
  return result;
}

QVariant QgsPostgresProvider::parseMultidimensionalArray( const QString &txt )
{
  QStringList result;
  if ( !txt.startsWith( '{' ) || !txt.endsWith( '}' ) )
  {
    QgsMessageLog::logMessage( tr( "Error parsing array, missing curly braces: %1" ).arg( txt ), tr( "PostGIS" ) );
    return result;
  }

  QStringList values;
  QString text = txt;
  while ( !text.isEmpty() )
  {
    bool escaped = false;
    int openedBrackets = 1;
    int i = 0;
    while ( i < text.length()  && openedBrackets > 0 )
    {
      ++i;

      if ( text.at( i ) == '}' && !escaped ) openedBrackets--;
      else if ( text.at( i ) == '{' && !escaped ) openedBrackets++;

      escaped = !escaped ? text.at( i ) == '\\' : false;
    }

    values.append( text.left( ++i ) );
    i = text.indexOf( ',', i );
    i = i > 0 ? text.indexOf( '{', i ) : -1;
    if ( i == -1 )
      break;

    text = text.mid( i );
  }
  return values;

}

QVariant QgsPostgresProvider::parseArray( const QString &txt, QVariant::Type type, QVariant::Type subType, const QString &typeName )
{
  if ( !txt.startsWith( '{' ) || !txt.endsWith( '}' ) )
  {
    if ( !txt.isEmpty() )
      QgsMessageLog::logMessage( tr( "Error parsing array, missing curly braces: %1" ).arg( txt ), tr( "PostGIS" ) );
    return QVariant( type );
  }
  QString inner = txt.mid( 1, txt.length() - 2 );
  if ( ( type == QVariant::StringList || type == QVariant::List ) && inner.startsWith( "{" ) )
    return parseMultidimensionalArray( inner );
  else if ( type == QVariant::StringList )
    return parseStringArray( inner );
  else
    return parseOtherArray( inner, subType, typeName );
}

QVariant QgsPostgresProvider::convertValue( QVariant::Type type, QVariant::Type subType, const QString &value, const QString &typeName )
{
  QVariant result;
  switch ( type )
  {
    case QVariant::Map:
      if ( typeName == QLatin1String( "json" ) || typeName == QLatin1String( "jsonb" ) )
        result = parseJson( value );
      else
        result = parseHstore( value );
      break;
    case QVariant::StringList:
    case QVariant::List:
      result = parseArray( value, type, subType, typeName );
      break;
    case QVariant::Bool:
      if ( value == QChar( 't' ) )
        result = true;
      else if ( value == QChar( 'f' ) )
        result = false;
      else
        result = QVariant( type );
      break;
    default:
      result = value;
      if ( !result.convert( type ) || value.isNull() )
        result = QVariant( type );
      break;
  }

  return result;
}

QList<QgsVectorLayer *> QgsPostgresProvider::searchLayers( const QList<QgsVectorLayer *> &layers, const QString &connectionInfo, const QString &schema, const QString &tableName )
{
  QList<QgsVectorLayer *> result;
  const auto constLayers = layers;
  for ( QgsVectorLayer *layer : constLayers )
  {
    const QgsPostgresProvider *pgProvider = qobject_cast<QgsPostgresProvider *>( layer->dataProvider() );
    if ( pgProvider &&
         pgProvider->mUri.connectionInfo( false ) == connectionInfo && pgProvider->mSchemaName == schema && pgProvider->mTableName == tableName )
    {
      result.append( layer );
    }
  }
  return result;
}

QList<QgsRelation> QgsPostgresProvider::discoverRelations( const QgsVectorLayer *target, const QList<QgsVectorLayer *> &layers ) const
{
  QList<QgsRelation> result;

  // Silently skip if this is a query layer or for some obscure reason there are no table and schema name
  if ( mIsQuery || mTableName.isEmpty() || mSchemaName.isEmpty() )
  {
    return result;
  }

  // Skip less silently if layer is not valid
  if ( !mValid )
  {
    QgsLogger::warning( tr( "Error discovering relations of %1: invalid layer" ).arg( mQuery ) );
    return result;
  }


  QString sql(
    "WITH foreign_keys AS "
    "  ( SELECT c.conname, "
    "           c.conrelid, "
    "           c.confrelid, "
    "           unnest(c.conkey) AS conkey, "
    "           unnest(c.confkey) AS confkey, "
    "     (SELECT relname "
    "      FROM pg_catalog.pg_class "
    "      WHERE oid = c.conrelid) as referencing_table, "
    "     (SELECT relname "
    "      FROM pg_catalog.pg_class "
    "      WHERE oid = c.confrelid) as referenced_table, "
    "     (SELECT relnamespace::regnamespace::text "
    "      FROM pg_catalog.pg_class "
    "      WHERE oid = c.confrelid) as constraint_schema "
    "   FROM pg_constraint c "
    "   WHERE contype = 'f' "
    "     AND c.conrelid::regclass = " +
    QgsPostgresConn::quotedValue( QString( QgsPostgresConn::quotedIdentifier( mSchemaName ) +
                                  '.' +
                                  QgsPostgresConn::quotedIdentifier( mTableName ) ) ) +
    "::regclass ) "
    "SELECT fk.conname as constraint_name, "
    "       a.attname as column_name, "
    "       fk.constraint_schema, "
    "       referenced_table as table_name, "
    "       af.attname as column_name, "
    "       fk.confkey as ordinal_position "
    "FROM foreign_keys fk "
    "JOIN pg_attribute af ON af.attnum = fk.confkey "
    "AND af.attrelid = fk.confrelid "
    "JOIN pg_attribute a ON a.attnum = conkey "
    "AND a.attrelid = fk.conrelid "
    "ORDER BY fk.confrelid, "
    "         fk.conname ;"
  );

  QgsPostgresResult sqlResult( connectionRO()->PQexec( sql ) );
  if ( sqlResult.PQresultStatus() != PGRES_TUPLES_OK )
  {
    QgsLogger::warning( "Error getting the foreign keys of " + mTableName );
    return result;
  }

  int nbFound = 0;
  QList<QString> refTableFound;
  for ( int row = 0; row < sqlResult.PQntuples(); ++row )
  {
    const QString name = sqlResult.PQgetvalue( row, 0 );
    const QString fkColumn = sqlResult.PQgetvalue( row, 1 );
    QString refSchema = sqlResult.PQgetvalue( row, 2 );
    QString refTable = sqlResult.PQgetvalue( row, 3 );
    // Strip quotes
    if ( refTable.startsWith( '"' ) && refTable.endsWith( '"' ) )
    {
      refTable = refTable.mid( 1, refTable.length() - 2 );
    }
    if ( refSchema.startsWith( '"' ) && refSchema.endsWith( '"' ) )
    {
      refSchema = refSchema.mid( 1, refSchema.length() - 2 );
    }
    const QString refColumn = sqlResult.PQgetvalue( row, 4 );
    const QString position = sqlResult.PQgetvalue( row, 5 );
    if ( ( position == QLatin1String( "1" ) ) || ( nbFound == 0 ) || ( !refTableFound.contains( refTable ) ) )
    {
      // first reference field => try to find if we have layers for the referenced table
      const QList<QgsVectorLayer *> foundLayers = searchLayers( layers, mUri.connectionInfo( false ), refSchema, refTable );
      const auto constFoundLayers = foundLayers;
      for ( const QgsVectorLayer *foundLayer : constFoundLayers )
      {
        QgsRelation relation;
        relation.setName( name );
        relation.setReferencingLayer( target->id() );
        relation.setReferencedLayer( foundLayer->id() );
        relation.addFieldPair( fkColumn, refColumn );
        relation.generateId();
        if ( relation.isValid() )
        {
          result.append( relation );
          ++nbFound;
          refTableFound.append( refTable );
        }
        else
        {
          QgsLogger::warning( "Invalid relation for " + name );
        }
      }
    }
    else
    {
      // multi reference field => add the field pair to all the referenced layers found
      const QList<QgsVectorLayer *> foundLayers = searchLayers( layers, mUri.connectionInfo( false ), refSchema, refTable );
      for ( int i = 0; i < nbFound; ++i )
      {
        for ( const QgsVectorLayer *foundLayer : foundLayers )
        {
          if ( result[result.size() - 1 - i].referencedLayerId() == foundLayer->id() )
          {
            result[result.size() - 1 - i].addFieldPair( fkColumn, refColumn );
          }
        }
      }
    }
  }
  return result;
}

QgsAttrPalIndexNameHash QgsPostgresProvider::palAttributeIndexNames() const
{
  return mAttrPalIndexName;
}

void QgsPostgresProvider::setQuery( const QString &query )
{
  mQuery = query;

  mKind = Relkind::NotSet;
}

QgsPostgresProvider::Relkind QgsPostgresProvider::relkind() const
{
  if ( mKind != Relkind::NotSet )
    return mKind;

  if ( mIsQuery || !connectionRO() )
  {
    mKind = Relkind::Unknown;
  }
  else
  {
    QString sql = QStringLiteral( "SELECT relkind FROM pg_class WHERE oid=regclass(%1)::oid" ).arg( quotedValue( mQuery ) );
    QgsPostgresResult res( connectionRO()->PQexec( sql ) );
    QString type = res.PQgetvalue( 0, 0 );

    mKind = Relkind::Unknown;

    if ( type == QLatin1String( "r" ) )
    {
      mKind = Relkind::OrdinaryTable;
    }
    else if ( type == QLatin1String( "i" ) )
    {
      mKind = Relkind::Index;
    }
    else if ( type == QLatin1String( "s" ) )
    {
      mKind = Relkind::Sequence;
    }
    else if ( type == QLatin1String( "v" ) )
    {
      mKind = Relkind::View;
    }
    else if ( type == QLatin1String( "m" ) )
    {
      mKind = Relkind::MaterializedView;
    }
    else if ( type == QLatin1String( "c" ) )
    {
      mKind = Relkind::CompositeType;
    }
    else if ( type == QLatin1String( "t" ) )
    {
      mKind = Relkind::ToastTable;
    }
    else if ( type == QLatin1String( "f" ) )
    {
      mKind = Relkind::ForeignTable;
    }
    else if ( type == QLatin1String( "p" ) )
    {
      mKind = Relkind::PartitionedTable;
    }
  }

  return mKind;
}

bool QgsPostgresProvider::hasMetadata() const
{
  bool hasMetadata = true;
  QgsPostgresProvider::Relkind kind = relkind();

  if ( kind == Relkind::View || kind == Relkind::MaterializedView )
  {
    hasMetadata = false;
  }

  return hasMetadata;
}

QgsDataProvider *QgsPostgresProviderMetadata::createProvider( const QString &uri, const QgsDataProvider::ProviderOptions &options, QgsDataProvider::ReadFlags flags )
{
  return new QgsPostgresProvider( uri, options, flags );
}

QList< QgsDataItemProvider * > QgsPostgresProviderMetadata::dataItemProviders() const
{
  QList<QgsDataItemProvider *> providers;
  providers << new QgsPostgresDataItemProvider;
  return providers;
}

// ---------------------------------------------------------------------------

Qgis::VectorExportResult QgsPostgresProviderMetadata::createEmptyLayer(
  const QString &uri,
  const QgsFields &fields,
  QgsWkbTypes::Type wkbType,
  const QgsCoordinateReferenceSystem &srs,
  bool overwrite,
  QMap<int, int> &oldToNewAttrIdxMap,
  QString &errorMessage,
  const QMap<QString, QVariant> *options )
{
  return QgsPostgresProvider::createEmptyLayer(
           uri, fields, wkbType, srs, overwrite,
           &oldToNewAttrIdxMap, &errorMessage, options
         );
}

bool QgsPostgresProviderMetadata::saveStyle( const QString &uri, const QString &qmlStyleIn, const QString &sldStyleIn,
    const QString &styleName, const QString &styleDescription,
    const QString &uiFileContent, bool useAsDefault, QString &errCause )
{
  QgsDataSourceUri dsUri( uri );

  // Replace invalid XML characters
  QString qmlStyle { qmlStyleIn };
  QgsPostgresUtils::replaceInvalidXmlChars( qmlStyle );
  QString sldStyle { sldStyleIn };
  QgsPostgresUtils::replaceInvalidXmlChars( sldStyle );

  QgsPostgresConn *conn = QgsPostgresConn::connectDb( dsUri.connectionInfo( false ), false );
  if ( !conn )
  {
    errCause = QObject::tr( "Connection to database failed" );
    return false;
  }

  if ( !tableExists( *conn, QStringLiteral( "layer_styles" ) ) )
  {
    QgsPostgresResult res( conn->PQexec( "CREATE TABLE layer_styles("
                                         "id SERIAL PRIMARY KEY"
                                         ",f_table_catalog varchar"
                                         ",f_table_schema varchar"
                                         ",f_table_name varchar"
                                         ",f_geometry_column varchar"
                                         ",styleName text"
                                         ",styleQML xml"
                                         ",styleSLD xml"
                                         ",useAsDefault boolean"
                                         ",description text"
                                         ",owner varchar(63) DEFAULT CURRENT_USER"
                                         ",ui xml"
                                         ",update_time timestamp DEFAULT CURRENT_TIMESTAMP"
                                         ",type varchar"
                                         ")" ) );
    if ( res.PQresultStatus() != PGRES_COMMAND_OK )
    {
      errCause = QObject::tr( "Unable to save layer style. It's not possible to create the destination table on the database. Maybe this is due to table permissions (user=%1). Please contact your database admin" ).arg( dsUri.username() );
      conn->unref();
      return false;
    }
  }
  else
  {
    if ( !columnExists( *conn, QStringLiteral( "layer_styles" ), QStringLiteral( "type" ) ) )
    {
      QgsPostgresResult res( conn->PQexec( "ALTER TABLE layer_styles ADD COLUMN type varchar NULL" ) );
      if ( res.PQresultStatus() != PGRES_COMMAND_OK )
      {
        errCause = QObject::tr( "Unable to add column type to layer_styles table. Maybe this is due to table permissions (user=%1). Please contact your database admin" ).arg( dsUri.username() );
        conn->unref();
        return false;
      }
    }
  }

  if ( dsUri.database().isEmpty() ) // typically when a service file is used
  {
    dsUri.setDatabase( conn->currentDatabase() );
  }

  QString uiFileColumn;
  QString uiFileValue;
  if ( !uiFileContent.isEmpty() )
  {
    uiFileColumn = QStringLiteral( ",ui" );
    uiFileValue = QStringLiteral( ",XMLPARSE(DOCUMENT %1)" ).arg( QgsPostgresConn::quotedValue( uiFileContent ) );
  }

  const QString wkbTypeString = QgsPostgresConn::quotedValue( QgsWkbTypes::geometryDisplayString( QgsWkbTypes::geometryType( dsUri.wkbType() ) ) );

  // Note: in the construction of the INSERT and UPDATE strings the qmlStyle and sldStyle values
  // can contain user entered strings, which may themselves include %## values that would be
  // replaced by the QString.arg function.  To ensure that the final SQL string is not corrupt these
  // two values are both replaced in the final .arg call of the string construction.

  QString sql = QString( "INSERT INTO layer_styles("
                         "f_table_catalog,f_table_schema,f_table_name,f_geometry_column,styleName,styleQML,styleSLD,useAsDefault,description,owner,type%12"
                         ") VALUES ("
                         "%1,%2,%3,%4,%5,XMLPARSE(DOCUMENT %16),XMLPARSE(DOCUMENT %17),%8,%9,%10,%11%13"
                         ")" )
                .arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
                .arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
                .arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
                .arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
                .arg( QgsPostgresConn::quotedValue( styleName.isEmpty() ? dsUri.table() : styleName ) )
                .arg( useAsDefault ? "true" : "false" )
                .arg( QgsPostgresConn::quotedValue( styleDescription.isEmpty() ? QDateTime::currentDateTime().toString() : styleDescription ) )
                .arg( "CURRENT_USER" )
                .arg( uiFileColumn )
                .arg( uiFileValue )
                .arg( wkbTypeString )
                // Must be the final .arg replacement - see above
                .arg( QgsPostgresConn::quotedValue( qmlStyle ),
                      QgsPostgresConn::quotedValue( sldStyle ) );

  QString checkQuery = QString( "SELECT styleName"
                                " FROM layer_styles"
                                " WHERE f_table_catalog=%1"
                                " AND f_table_schema=%2"
                                " AND f_table_name=%3"
                                " AND f_geometry_column=%4"
                                " AND (type=%5 OR type IS NULL)"
                                " AND styleName=%6" )
                       .arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
                       .arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
                       .arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
                       .arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
                       .arg( wkbTypeString )
                       .arg( QgsPostgresConn::quotedValue( styleName.isEmpty() ? dsUri.table() : styleName ) );

  QgsPostgresResult res( conn->PQexec( checkQuery ) );
  if ( res.PQntuples() > 0 )
  {
    if ( QMessageBox::question( nullptr, QObject::tr( "Save style in database" ),
                                QObject::tr( "A style named \"%1\" already exists in the database for this layer. Do you want to overwrite it?" )
                                .arg( styleName.isEmpty() ? dsUri.table() : styleName ),
                                QMessageBox::Yes | QMessageBox::No ) == QMessageBox::No )
    {
      errCause = QObject::tr( "Operation aborted. No changes were made in the database" );
      conn->unref();
      return false;
    }

    sql = QString( "UPDATE layer_styles"
                   " SET useAsDefault=%1"
                   ",styleQML=XMLPARSE(DOCUMENT %12)"
                   ",styleSLD=XMLPARSE(DOCUMENT %13)"
                   ",description=%4"
                   ",owner=%5"
                   ",type=%2"
                   " WHERE f_table_catalog=%6"
                   " AND f_table_schema=%7"
                   " AND f_table_name=%8"
                   " AND f_geometry_column=%9"
                   " AND styleName=%10"
                   " AND (type=%2 OR type IS NULL)" )
          .arg( useAsDefault ? "true" : "false" )
          .arg( wkbTypeString )
          .arg( QgsPostgresConn::quotedValue( styleDescription.isEmpty() ? QDateTime::currentDateTime().toString() : styleDescription ) )
          .arg( "CURRENT_USER" )
          .arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
          .arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
          .arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
          .arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
          .arg( QgsPostgresConn::quotedValue( styleName.isEmpty() ? dsUri.table() : styleName ) )
          // Must be the final .arg replacement - see above
          .arg( QgsPostgresConn::quotedValue( qmlStyle ),
                QgsPostgresConn::quotedValue( sldStyle ) );
  }

  if ( useAsDefault )
  {
    QString removeDefaultSql = QString( "UPDATE layer_styles"
                                        " SET useAsDefault=false"
                                        " WHERE f_table_catalog=%1"
                                        " AND f_table_schema=%2"
                                        " AND f_table_name=%3"
                                        " AND f_geometry_column=%4"
                                        " AND (type=%5 OR type IS NULL)" )
                               .arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
                               .arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
                               .arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
                               .arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
                               .arg( wkbTypeString );

    sql = QStringLiteral( "BEGIN; %1; %2; COMMIT;" ).arg( removeDefaultSql, sql );
  }

  res = conn->PQexec( sql );

  bool saved = res.PQresultStatus() == PGRES_COMMAND_OK;
  if ( !saved )
    errCause = QObject::tr( "Unable to save layer style. It's not possible to insert a new record into the style table. Maybe this is due to table permissions (user=%1). Please contact your database administrator." ).arg( dsUri.username() );

  conn->unref();

  return saved;
}


QString QgsPostgresProviderMetadata::loadStyle( const QString &uri, QString &errCause )
{
  QgsDataSourceUri dsUri( uri );
  QString selectQmlQuery;

  QgsPostgresConn *conn = QgsPostgresConn::connectDb( dsUri.connectionInfo( false ), false );
  if ( !conn )
  {
    errCause = QObject::tr( "Connection to database failed" );
    return QString();
  }

  if ( dsUri.database().isEmpty() ) // typically when a service file is used
  {
    dsUri.setDatabase( conn->currentDatabase() );
  }

  if ( !tableExists( *conn, QStringLiteral( "layer_styles" ) ) )
  {
    conn->unref();
    return QString();
  }

  QString geomColumnExpr;
  if ( dsUri.geometryColumn().isEmpty() )
  {
    geomColumnExpr = QStringLiteral( "IS NULL" );
  }
  else
  {
    geomColumnExpr = QStringLiteral( "=" ) + QgsPostgresConn::quotedValue( dsUri.geometryColumn() );
  }

  QString wkbTypeString = QgsPostgresConn::quotedValue( QgsWkbTypes::geometryDisplayString( QgsWkbTypes::geometryType( dsUri.wkbType() ) ) );

  // support layer_styles without type column < 3.14
  if ( !columnExists( *conn, QStringLiteral( "layer_styles" ), QStringLiteral( "type" ) ) )
  {
    selectQmlQuery = QString( "SELECT styleQML"
                              " FROM layer_styles"
                              " WHERE f_table_catalog=%1"
                              " AND f_table_schema=%2"
                              " AND f_table_name=%3"
                              " AND f_geometry_column %4"
                              " ORDER BY CASE WHEN useAsDefault THEN 1 ELSE 2 END"
                              ",update_time DESC LIMIT 1" )
                     .arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
                     .arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
                     .arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
                     .arg( geomColumnExpr );
  }
  else
  {
    selectQmlQuery = QString( "SELECT styleQML"
                              " FROM layer_styles"
                              " WHERE f_table_catalog=%1"
                              " AND f_table_schema=%2"
                              " AND f_table_name=%3"
                              " AND f_geometry_column %4"
                              " AND (type=%5 OR type IS NULL)"
                              " ORDER BY CASE WHEN useAsDefault THEN 1 ELSE 2 END"
                              ",update_time DESC LIMIT 1" )
                     .arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
                     .arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
                     .arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
                     .arg( geomColumnExpr )
                     .arg( wkbTypeString );
  }

  QgsPostgresResult result( conn->PQexec( selectQmlQuery ) );

  QString style = result.PQntuples() == 1 ? result.PQgetvalue( 0, 0 ) : QString();
  conn->unref();

  QgsPostgresUtils::restoreInvalidXmlChars( style );

  return style;
}

int QgsPostgresProviderMetadata::listStyles( const QString &uri, QStringList &ids, QStringList &names,
    QStringList &descriptions, QString &errCause )
{
  QgsDataSourceUri dsUri( uri );

  QgsPostgresConn *conn = QgsPostgresConn::connectDb( dsUri.connectionInfo( false ), false );
  if ( !conn )
  {
    errCause = QObject::tr( "Connection to database failed using username: %1" ).arg( dsUri.username() );
    return -1;
  }

  if ( dsUri.database().isEmpty() ) // typically when a service file is used
  {
    dsUri.setDatabase( conn->currentDatabase() );
  }

  QString wkbTypeString = QgsPostgresConn::quotedValue( QgsWkbTypes::geometryDisplayString( QgsWkbTypes::geometryType( dsUri.wkbType() ) ) );

  QString selectRelatedQuery = QString( "SELECT id,styleName,description"
                                        " FROM layer_styles"
                                        " WHERE f_table_catalog=%1"
                                        " AND f_table_schema=%2"
                                        " AND f_table_name=%3"
                                        " AND %4"
                                        " AND (type=%5 OR type IS NULL)"
                                        " ORDER BY useasdefault DESC, update_time DESC" )
                               .arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
                               .arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
                               .arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
                               .arg( dsUri.geometryColumn().isEmpty() ? "f_geometry_column is NULL" :
                                     QString( "f_geometry_column=%1" ).arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) ) )
                               .arg( wkbTypeString );

  QgsPostgresResult result( conn->PQexec( selectRelatedQuery ) );
  if ( result.PQresultStatus() != PGRES_TUPLES_OK )
  {
    QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( selectRelatedQuery ) );
    errCause = QObject::tr( "Error executing the select query for related styles. The query was logged" );
    conn->unref();
    return -1;
  }

  int numberOfRelatedStyles = result.PQntuples();
  for ( int i = 0; i < numberOfRelatedStyles; i++ )
  {
    ids.append( result.PQgetvalue( i, 0 ) );
    names.append( result.PQgetvalue( i, 1 ) );
    descriptions.append( result.PQgetvalue( i, 2 ) );
  }

  QString selectOthersQuery = QString( "SELECT id,styleName,description"
                                       " FROM layer_styles"
                                       " WHERE NOT (f_table_catalog=%1 AND f_table_schema=%2 AND f_table_name=%3 AND f_geometry_column=%4 AND type=%5)"
                                       " ORDER BY update_time DESC" )
                              .arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
                              .arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
                              .arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
                              .arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
                              .arg( wkbTypeString );

  result = conn->PQexec( selectOthersQuery );
  if ( result.PQresultStatus() != PGRES_TUPLES_OK )
  {
    QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( selectOthersQuery ) );
    errCause = QObject::tr( "Error executing the select query for unrelated styles. The query was logged" );
    conn->unref();
    return -1;
  }

  for ( int i = 0; i < result.PQntuples(); i++ )
  {
    ids.append( result.PQgetvalue( i, 0 ) );
    names.append( result.PQgetvalue( i, 1 ) );
    descriptions.append( result.PQgetvalue( i, 2 ) );
  }

  conn->unref();

  return numberOfRelatedStyles;
}

bool QgsPostgresProviderMetadata::deleteStyleById( const QString &uri, QString styleId, QString &errCause )
{
  QgsDataSourceUri dsUri( uri );
  bool deleted;

  QgsPostgresConn *conn = QgsPostgresConn::connectDb( dsUri.connectionInfo( false ), false );
  if ( !conn )
  {
    errCause = QObject::tr( "Connection to database failed using username: %1" ).arg( dsUri.username() );
    deleted = false;
  }
  else
  {
    QString deleteStyleQuery = QStringLiteral( "DELETE FROM layer_styles WHERE id=%1" ).arg(
                                 QgsPostgresConn::quotedValue( styleId ) );
    QgsPostgresResult result( conn->PQexec( deleteStyleQuery ) );
    if ( result.PQresultStatus() != PGRES_COMMAND_OK )
    {
      QgsDebugMsg(
        QString( "PQexec of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
        .arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( deleteStyleQuery ) );
      QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( deleteStyleQuery ) );
      errCause = QObject::tr( "Error executing the delete query. The query was logged" );
      deleted = false;
    }
    else
    {
      deleted = true;
    }
    conn->unref();
  }
  return deleted;
}

QString QgsPostgresProviderMetadata::getStyleById( const QString &uri, QString styleId, QString &errCause )
{
  QgsDataSourceUri dsUri( uri );

  QgsPostgresConn *conn = QgsPostgresConn::connectDb( dsUri.connectionInfo( false ), false );
  if ( !conn )
  {
    errCause = QObject::tr( "Connection to database failed using username: %1" ).arg( dsUri.username() );
    return QString();
  }

  QString style;
  QString selectQmlQuery = QStringLiteral( "SELECT styleQml FROM layer_styles WHERE id=%1" ).arg( QgsPostgresConn::quotedValue( styleId ) );
  QgsPostgresResult result( conn->PQexec( selectQmlQuery ) );
  if ( result.PQresultStatus() == PGRES_TUPLES_OK )
  {
    if ( result.PQntuples() == 1 )
      style = result.PQgetvalue( 0, 0 );
    else
      errCause = QObject::tr( "Consistency error in table '%1'. Style id should be unique" ).arg( QLatin1String( "layer_styles" ) );
  }
  else
  {
    QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( selectQmlQuery ) );
    errCause = QObject::tr( "Error executing the select query. The query was logged" );
  }

  conn->unref();

  QgsPostgresUtils::restoreInvalidXmlChars( style );

  return style;
}

QgsTransaction *QgsPostgresProviderMetadata::createTransaction( const QString &connString )
{
  return new QgsPostgresTransaction( connString );
}

QMap<QString, QgsAbstractProviderConnection *> QgsPostgresProviderMetadata::connections( bool cached )
{
  return connectionsProtected<QgsPostgresProviderConnection, QgsPostgresConn>( cached );
}

QgsAbstractProviderConnection *QgsPostgresProviderMetadata::createConnection( const QString &uri, const QVariantMap &configuration )
{
  return new QgsPostgresProviderConnection( uri, configuration );
}

void QgsPostgresProviderMetadata::deleteConnection( const QString &name )
{
  deleteConnectionProtected<QgsPostgresProviderConnection>( name );
}

void QgsPostgresProviderMetadata::saveConnection( const QgsAbstractProviderConnection *conn,  const QString &name )
{
  saveConnectionProtected( conn, name );
}

QgsAbstractProviderConnection *QgsPostgresProviderMetadata::createConnection( const QString &name )
{
  return new QgsPostgresProviderConnection( name );
}


QgsPostgresProjectStorage *gPgProjectStorage = nullptr;   // when not null it is owned by QgsApplication::projectStorageRegistry()

void QgsPostgresProviderMetadata::initProvider()
{
  Q_ASSERT( !gPgProjectStorage );
  gPgProjectStorage = new QgsPostgresProjectStorage;
  QgsApplication::projectStorageRegistry()->registerProjectStorage( gPgProjectStorage );  // takes ownership
}

void QgsPostgresProviderMetadata::cleanupProvider()
{
  QgsApplication::projectStorageRegistry()->unregisterProjectStorage( gPgProjectStorage );  // destroys the object
  gPgProjectStorage = nullptr;

  QgsPostgresConnPool::cleanupInstance();
}


// ----------

void QgsPostgresSharedData::addFeaturesCounted( long long diff )
{
  QMutexLocker locker( &mMutex );

  if ( mFeaturesCounted >= 0 )
    mFeaturesCounted += diff;
}

void QgsPostgresSharedData::ensureFeaturesCountedAtLeast( long long fetched )
{
  QMutexLocker locker( &mMutex );

  /* only updates the feature count if it was already once.
   * Otherwise, this would lead to false feature count if
   * an existing project is open at a restrictive extent.
   */
  if ( mFeaturesCounted > 0 && mFeaturesCounted < fetched )
  {
    QgsDebugMsgLevel( QStringLiteral( "feature count adjusted from %1 to %2" ).arg( mFeaturesCounted ).arg( fetched ), 2 );
    mFeaturesCounted = fetched;
  }
}

long long QgsPostgresSharedData::featuresCounted()
{
  QMutexLocker locker( &mMutex );
  return mFeaturesCounted;
}

void QgsPostgresSharedData::setFeaturesCounted( long long count )
{
  QMutexLocker locker( &mMutex );
  mFeaturesCounted = count;
}


QgsFeatureId QgsPostgresSharedData::lookupFid( const QVariantList &v )
{
  QMutexLocker locker( &mMutex );

  QMap<QVariantList, QgsFeatureId>::const_iterator it = mKeyToFid.constFind( v );

  if ( it != mKeyToFid.constEnd() )
  {
    return it.value();
  }

  mFidToKey.insert( ++mFidCounter, v );
  mKeyToFid.insert( v, mFidCounter );

  return mFidCounter;
}


QVariantList QgsPostgresSharedData::removeFid( QgsFeatureId fid )
{
  QMutexLocker locker( &mMutex );

  QVariantList v = mFidToKey[ fid ];
  mFidToKey.remove( fid );
  mKeyToFid.remove( v );
  return v;
}

void QgsPostgresSharedData::insertFid( QgsFeatureId fid, const QVariantList &k )
{
  QMutexLocker locker( &mMutex );

  mFidToKey.insert( fid, k );
  mKeyToFid.insert( k, fid );
}

QVariantList QgsPostgresSharedData::lookupKey( QgsFeatureId featureId )
{
  QMutexLocker locker( &mMutex );

  QMap<QgsFeatureId, QVariantList>::const_iterator it = mFidToKey.constFind( featureId );
  if ( it != mFidToKey.constEnd() )
    return it.value();
  return QVariantList();
}

void QgsPostgresSharedData::clear()
{
  QMutexLocker locker( &mMutex );
  mFidToKey.clear();
  mKeyToFid.clear();
  mFeaturesCounted = -1;
  mFidCounter = 0;
}

void QgsPostgresSharedData::clearSupportsEnumValuesCache()
{
  QMutexLocker locker( &mMutex );
  mFieldSupportsEnumValues.clear();
}

bool QgsPostgresSharedData::fieldSupportsEnumValuesIsSet( int index )
{
  QMutexLocker locker( &mMutex );
  return mFieldSupportsEnumValues.contains( index );
}

bool QgsPostgresSharedData::fieldSupportsEnumValues( int index )
{
  QMutexLocker locker( &mMutex );
  return mFieldSupportsEnumValues.contains( index ) && mFieldSupportsEnumValues[ index ];
}

void QgsPostgresSharedData::setFieldSupportsEnumValues( int index, bool isSupported )
{
  QMutexLocker locker( &mMutex );
  mFieldSupportsEnumValues[ index ] = isSupported;
}


QgsPostgresProviderMetadata::QgsPostgresProviderMetadata()
  : QgsProviderMetadata( QgsPostgresProvider::POSTGRES_KEY, QgsPostgresProvider::POSTGRES_DESCRIPTION )
{
}

#ifndef HAVE_STATIC_PROVIDERS
QGISEXTERN QgsProviderMetadata *providerMetadataFactory()
{
  return new QgsPostgresProviderMetadata();
}
#endif


QVariantMap QgsPostgresProviderMetadata::decodeUri( const QString &uri ) const
{
  const QgsDataSourceUri dsUri { uri };
  QVariantMap uriParts;

  if ( ! dsUri.database().isEmpty() )
    uriParts[ QStringLiteral( "dbname" ) ] = dsUri.database();
  if ( ! dsUri.host().isEmpty() )
    uriParts[ QStringLiteral( "host" ) ] = dsUri.host();
  if ( ! dsUri.port().isEmpty() )
    uriParts[ QStringLiteral( "port" ) ] = dsUri.port();
  if ( ! dsUri.service().isEmpty() )
    uriParts[ QStringLiteral( "service" ) ] = dsUri.service();
  if ( ! dsUri.username().isEmpty() )
    uriParts[ QStringLiteral( "username" ) ] = dsUri.username();
  if ( ! dsUri.password().isEmpty() )
    uriParts[ QStringLiteral( "password" ) ] = dsUri.password();
  if ( ! dsUri.authConfigId().isEmpty() )
    uriParts[ QStringLiteral( "authcfg" ) ] = dsUri.authConfigId();
  if ( dsUri.wkbType() != QgsWkbTypes::Type::Unknown )
    uriParts[ QStringLiteral( "type" ) ] = dsUri.wkbType();

  if ( uri.contains( QStringLiteral( "selectatid=" ), Qt::CaseSensitivity::CaseInsensitive ) )
    uriParts[ QStringLiteral( "selectatid" ) ] = ! dsUri.selectAtIdDisabled();

  if ( ! dsUri.table().isEmpty() )
    uriParts[ QStringLiteral( "table" ) ] = dsUri.table();
  if ( ! dsUri.schema().isEmpty() )
    uriParts[ QStringLiteral( "schema" ) ] = dsUri.schema();
  if ( ! dsUri.keyColumn().isEmpty() )
    uriParts[ QStringLiteral( "key" ) ] = dsUri.keyColumn();
  if ( ! dsUri.srid().isEmpty() )
    uriParts[ QStringLiteral( "srid" ) ] = dsUri.srid();

  if ( uri.contains( QStringLiteral( "estimatedmetadata=" ), Qt::CaseSensitivity::CaseInsensitive ) )
    uriParts[ QStringLiteral( "estimatedmetadata" ) ] = dsUri.useEstimatedMetadata();

  if ( uri.contains( QStringLiteral( "sslmode=" ), Qt::CaseSensitivity::CaseInsensitive ) )
    uriParts[ QStringLiteral( "sslmode" ) ] = dsUri.sslMode();

  if ( ! dsUri.sql().isEmpty() )
    uriParts[ QStringLiteral( "sql" ) ] = dsUri.sql();
  if ( ! dsUri.geometryColumn().isEmpty() )
    uriParts[ QStringLiteral( "geometrycolumn" ) ] = dsUri.geometryColumn();

  return uriParts;
}


QString QgsPostgresProviderMetadata::encodeUri( const QVariantMap &parts ) const
{
  QgsDataSourceUri dsUri;
  if ( parts.contains( QStringLiteral( "dbname" ) ) )
    dsUri.setDatabase( parts.value( QStringLiteral( "dbname" ) ).toString() );
  if ( parts.contains( QStringLiteral( "port" ) ) )
    dsUri.setParam( QStringLiteral( "port" ), parts.value( QStringLiteral( "port" ) ).toString() );
  if ( parts.contains( QStringLiteral( "host" ) ) )
    dsUri.setParam( QStringLiteral( "host" ), parts.value( QStringLiteral( "host" ) ).toString() );
  if ( parts.contains( QStringLiteral( "service" ) ) )
    dsUri.setParam( QStringLiteral( "service" ), parts.value( QStringLiteral( "service" ) ).toString() );
  if ( parts.contains( QStringLiteral( "username" ) ) )
    dsUri.setUsername( parts.value( QStringLiteral( "username" ) ).toString() );
  if ( parts.contains( QStringLiteral( "password" ) ) )
    dsUri.setPassword( parts.value( QStringLiteral( "password" ) ).toString() );
  if ( parts.contains( QStringLiteral( "authcfg" ) ) )
    dsUri.setAuthConfigId( parts.value( QStringLiteral( "authcfg" ) ).toString() );
  if ( parts.contains( QStringLiteral( "type" ) ) )
    dsUri.setParam( QStringLiteral( "type" ), QgsWkbTypes::displayString( static_cast<QgsWkbTypes::Type>( parts.value( QStringLiteral( "type" ) ).toInt() ) ) );
  if ( parts.contains( QStringLiteral( "selectatid" ) ) )
    dsUri.setParam( QStringLiteral( "selectatid" ), parts.value( QStringLiteral( "selectatid" ) ).toString() );
  if ( parts.contains( QStringLiteral( "table" ) ) )
    dsUri.setTable( parts.value( QStringLiteral( "table" ) ).toString() );
  if ( parts.contains( QStringLiteral( "schema" ) ) )
    dsUri.setSchema( parts.value( QStringLiteral( "schema" ) ).toString() );
  if ( parts.contains( QStringLiteral( "key" ) ) )
    dsUri.setParam( QStringLiteral( "key" ), parts.value( QStringLiteral( "key" ) ).toString() );
  if ( parts.contains( QStringLiteral( "srid" ) ) )
    dsUri.setSrid( parts.value( QStringLiteral( "srid" ) ).toString() );
  if ( parts.contains( QStringLiteral( "estimatedmetadata" ) ) )
    dsUri.setParam( QStringLiteral( "estimatedmetadata" ), parts.value( QStringLiteral( "estimatedmetadata" ) ).toString() );
  if ( parts.contains( QStringLiteral( "sslmode" ) ) )
    dsUri.setParam( QStringLiteral( "sslmode" ), QgsDataSourceUri::encodeSslMode( static_cast<QgsDataSourceUri::SslMode>( parts.value( QStringLiteral( "sslmode" ) ).toInt( ) ) ) );
  if ( parts.contains( QStringLiteral( "sql" ) ) )
    dsUri.setSql( parts.value( QStringLiteral( "sql" ) ).toString() );
  if ( parts.contains( QStringLiteral( "checkPrimaryKeyUnicity" ) ) )
    dsUri.setParam( QStringLiteral( "checkPrimaryKeyUnicity" ), parts.value( QStringLiteral( "checkPrimaryKeyUnicity" ) ).toString() );
  if ( parts.contains( QStringLiteral( "geometrycolumn" ) ) )
    dsUri.setGeometryColumn( parts.value( QStringLiteral( "geometrycolumn" ) ).toString() );
  return dsUri.uri( false );
}