Problem start container geopython/pycsw:latest with postgresql and materialized view

[espdits]

I create docker compose and i modified the config file:

  pycsw:
    depends_on:
      - pgstac
    container_name: pycsw
    image: geopython/pycsw:latest
    environment:
        PYCSW_SERVER_URL: http://localhost:8888
        PYCSW_CONFIG: /etc/pycsw/default.yml
    ports:
      - "8888:8000"
      - "5678:5678"
    volumes:
      - ./pycsw/default.yml:/etc/pycsw/default.yml
      - ./pycsw/mappings.py:/etc/pycsw/mappings.py

with default.yml:

# =================================================================
#
# Authors: Tom Kralidis <[email protected]>
#          Angelos Tzotsos <[email protected]>
#
# Copyright (c) 2024 Tom Kralidis
# Copyright (c) 2024 Angelos Tzotsos
#
# Permission is hereby granted, free of charge, to any person
# obtaining a copy of this software and associated documentation
# files (the "Software"), to deal in the Software without
# restriction, including without limitation the rights to use,
# copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the
# Software is furnished to do so, subject to the following
# conditions:
#
# The above copyright notice and this permission notice shall be
# included in all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
# EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
# OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
# NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
# HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
# WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
# OTHER DEALINGS IN THE SOFTWARE.
#
# =================================================================

server:
    url: ${PYCSW_SERVER_URL}
    mimetype: application/xml; charset=UTF-8
    encoding: UTF-8
    language: en-US
    maxrecords: 10
    #ogc_schemas_location: http://foo
    pretty_print: true
    gzip_compresslevel: 9
    #domainquerytype: range
    #domaincounts: true
    #spatial_ranking: true
    #workers=2
    timeout: 30

logging:
    level: DEBUG
    #logfile: /tmp/pycsw.log

profiles:
    - apiso

federatedcatalogues:
    - http://catalog.data.gov/csw

manager:
    transactions: false
    allowed_ips:
        - 127.0.0.1
    csw_harvest_pagesize: 10

metadata:
    identification:
        title: pycsw Geospatial Catalogue
        description: pycsw is an OARec and OGC CSW server implementation written in Python
        keywords:
            - catalogue
            - discovery
            - metadata
        keywords_type: theme
        fees: None
        accessconstraints: None
        terms_of_service: https://creativecommons.org/licenses/by/4.0
        url: https://example.org
    license:
        name: CC-BY 4.0 license
        url: https://creativecommons.org/licenses/by/4.0
    provider:
        name: Organization Name
        url: https://pycsw.org
    contact:
        name: Lastname, Firstname
        position: Position Title
        address: Mailing Address
        city: City
        stateorprovince: Administrative Area
        postalcode: Zip or Postal Code
        country: Country
        phone: +xx-xxx-xxx-xxxx
        fax: +xx-xxx-xxx-xxxx
        email: [email protected]
        url: Contact URL
        hours: Mo-Fr 08:00-17:00
        instructions: During hours of service. Off on weekends.
        role: pointOfContact
    inspire:
        enabled: true
        languages_supported:
            - eng
            - gre
        default_language: eng
        date: YYYY-MM-DD
        gemet_keywords:
            - Utility and governmental services
        conformity_service: notEvaluated
        contact_name: Organization Name
        contact_email: Email Address
        temp_extent:
            begin: YYYY-MM-DD
            end: YYYY-MM-DD

repository:
    # sqlite
    database: postgresql://username:password@pgstac:5432/postgis
    # postgres
    #database: postgresql://username:password@localhost/pycsw
    # mysql
    #database: mysql://username:password@localhost/pycsw?charset=utf8
    table: pgstac.pycsw
    mappings: /etc/pycsw/mappings.py

    #filter: type = 'http://purl.org/dc/dcmitype/Dataset'
    max_retries: 5

mappings.py:

from sqlalchemy.schema import PrimaryKeyConstraint

MD_CORE_MODEL = {
    "column_constraints": (PrimaryKeyConstraint("identifier"),),
    "typename": "pycsw:CoreMetadata",
    "outputschema": "http://pycsw.org/metadata",
    "mappings": {
        "pycsw:Identifier": "identifier",
        "pycsw:Typename": "typename",
        "pycsw:Schema": "schema",
        "pycsw:MdSource": "mdsource",
        "pycsw:InsertDate": "insert_date",
        "pycsw:XML": "xml",
        "pycsw:Metadata": "metadata",
        "pycsw:MetadataType": "metadata_type",
        "pycsw:AnyText": "anytext",
        "pycsw:Language": "language",
        "pycsw:Title": "title",
        "pycsw:Abstract": "abstract",
        "pycsw:Edition": "edition",
        "pycsw:Keywords": "keywords",
        "pycsw:KeywordType": "keywordstype",
        "pycsw:Themes": "themes",
        "pycsw:Format": "format",
        "pycsw:Source": "source",
        "pycsw:Date": "date",
        "pycsw:Modified": "date_modified",
        "pycsw:Type": "type",
        "pycsw:BoundingBox": "wkt_geometry",
        "pycsw:CRS": "crs",
        "pycsw:AlternateTitle": "title_alternate",
        "pycsw:RevisionDate": "date_revision",
        "pycsw:CreationDate": "date_creation",
        "pycsw:PublicationDate": "date_publication",
        "pycsw:OrganizationName": "organization",
        "pycsw:SecurityConstraints": "securityconstraints",
        "pycsw:ParentIdentifier": "parentidentifier",
        "pycsw:TopicCategory": "topicategory",
        "pycsw:ResourceLanguage": "resourcelanguage",
        "pycsw:GeographicDescriptionCode": "geodescode",
        "pycsw:Denominator": "denominator",
        "pycsw:DistanceValue": "distancevalue",
        "pycsw:DistanceUOM": "distanceuom",
        "pycsw:TempExtent_begin": "time_begin",
        "pycsw:TempExtent_end": "time_end",
        "pycsw:ServiceType": "servicetype",
        "pycsw:ServiceTypeVersion": "servicetypeversion",
        "pycsw:Operation": "operation",
        "pycsw:CouplingType": "couplingtype",
        "pycsw:OperatesOn": "operateson",
        "pycsw:OperatesOnIdentifier": "operatesonidentifier",
        "pycsw:OperatesOnName": "operatesoname",
        "pycsw:Degree": "degree",
        "pycsw:AccessConstraints": "accessconstraints",
        "pycsw:OtherConstraints": "otherconstraints",
        "pycsw:Classification": "classification",
        "pycsw:ConditionApplyingToAccessAndUse": "conditionapplyingtoaccessanduse",
        "pycsw:Lineage": "lineage",
        "pycsw:ResponsiblePartyRole": "responsiblepartyrole",
        "pycsw:SpecificationTitle": "specificationtitle",
        "pycsw:SpecificationDate": "specificationdate",
        "pycsw:SpecificationDateType": "specificationdatetype",
        "pycsw:Creator": "creator",
        "pycsw:Publisher": "publisher",
        "pycsw:Contributor": "contributor",
        "pycsw:Relation": "relation",
        "pycsw:Platform": "platform",
        "pycsw:Instrument": "instrument",
        "pycsw:SensorType": "sensortype",
        "pycsw:CloudCover": "cloudcover",
        "pycsw:Bands": "bands",
        "pycsw:Links": "links",
        "pycsw:Contacts": "contacts",
    },
}

i created my materialized view with this script:

CREATE MATERIALIZED VIEW IF NOT EXISTS postgis.pgstac.pycsw as 
SELECT
 i.id AS identifier,
   NULL as typename,
     NULL as schema,
       null as mdsource,
         ((i.content -> 'properties')::jsonb ->> 'ingestion_time')::text as insert_date,
  null as xml,
  null as metadata,
  null as metadata_type,
           null as anytext,
  null as language,
  i.id AS title,
    null as abstract,
  null as edition,
  (
    SELECT string_agg(value::text, ',')
    FROM jsonb_array_elements_text(c.content->'keywords') AS arr(value)
  ) AS keywords,
    null as keywordstype,
  null as themes,
  null as format,
  null as source,
 i.datetime AS date,
   null as date_modified,
  null as type,
  st_astext(st_envelope(i.geometry)) as wkt_geometry,
  st_srid(geometry) as crs,
  ((i.content -> 'properties')::jsonb ->> 'tile_id')::text as title_alternate,
  null as date_revision,
  ((i.content -> 'properties')::jsonb ->> 'ingestion_time')::text as date_creation,
  ((i.content -> 'properties')::jsonb ->> 'ingestion_time')::text as date_pubblication,
  null AS organization,
    null as securityconstraints,
  ((i.content -> 'properties')::jsonb ->> 'parent_id')::text as parentidentifier,
  i.collection as topicategory,
  null as resourcelanguage,
  null as geodescode,
  null as denominator,
  null as distancevalue,
  null as distanceuom,
  i.datetime as time_begin,
  i.end_datetime as time_end,
    null as servicetype,
  null as servicetypeversion,
  null as operation,
  null as couplingtype,
  null as operateson,
  null as operatesonidentifier,
  null as operatesoname,
  null as degree,
  null as accessconstraints,
  null as otherconstraints,
  null as classification,
  null as conditionapplyingtoaccessanduse,
  null as lineage,
  null as responsiblepartyrole,
  null as specificationtitle,
  null as specificationdate,
  null as specificationdatetype,
  null as creator,
  null as publisher,
  null as contributor,
  null as relation,
  ((i.content -> 'properties')::jsonb ->> 'platform')::text as platform,
  null as instrument,
  ((i.content -> 'properties')::jsonb ->> 'sensor')::text as sensortype,
  null as cloudcover,
  null as bands,
  null as links,
  null as contacts
from postgis.pgstac.items i 
JOIN postgis.pgstac.collections c ON i.collection = c.id

why i have this error?

INFO:pycsw.core.repository:creating new engine: postgresql://username:password@pgstac/postgis
INFO:pycsw.core.repository:binding ORM to existing database
Traceback (most recent call last):
  File "/usr/local/bin/entrypoint.py", line 138, in <module>
    launch_pycsw(config, workers=workers, reload=args.reload)
  File "/usr/local/bin/entrypoint.py", line 83, in launch_pycsw
    repo = Repository(database, StaticContext(), table=table)
  File "/home/pycsw/pycsw/pycsw/core/repository.py", line 133, in __init__
    self.dataset = type(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/decl_api.py", line 76, in __init__
    _as_declarative(reg, cls, dict_)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/decl_base.py", line 126, in _as_declarative
    return _MapperConfig.setup_mapping(registry, cls, dict_, None, {})
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/decl_base.py", line 183, in setup_mapping
    return cfg_cls(registry, cls_, dict_, table, mapper_kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/decl_base.py", line 335, in __init__
    self._early_mapping(mapper_kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/decl_base.py", line 215, in _early_mapping
    self.map(mapper_kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/decl_base.py", line 1047, in map
    mapper_cls(self.cls, self.local_table, **self.mapper_args),
  File "<string>", line 2, in __init__
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 375, in warned
    return fn(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py", line 693, in __init__
    self._configure_pks()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py", line 1400, in _configure_pks
    raise sa_exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Mapper mapped class dataset->pycsw could not assemble any primary key columns for mapped table 'pycsw'

I'm going crazy

[tomkralidis]

SQLAlchemy mentions issues with finding a primary key. In psql, what does a \dt <table> display?