CDS file deployment to Postgres fails if it table already exists

[nikolaykanov]

Description of erroneous behaviour

We're deploying tables to Postgres via cds. Deployment itself runs correctly, but when it is executed a second time, i.e. during upgrade of our app, one of the tables throws an error. The table itself is:

entity CASBIN_RULE {
    key ID: Integer64 @sql.append: 'GENERATED BY DEFAULT AS IDENTITY(START WITH 1 INCREMENT BY 1)';
    PTYPE: String(100) not null;
    V0: String(500);
    V1: String(500);
    V2: String(500);
    V3: String(500);
    V4: String(500);
    V5: String(500);
};

Error message:

error: relation "casbin_rule" already exists
    at /app/node_modules/pg/lib/client.js:526:17
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Object.run (/app/node_modules/@cap-js/postgres/lib/PostgresService.js:156:26)
    at async next (/app/node_modules/@sap/cds/lib/srv/srv-dispatch.js:68:17)
    at async PostgresService.handle (/app/node_modules/@sap/cds/lib/srv/srv-dispatch.js:66:10)
    at async Promise.all (index 0)
    at async deploy.schema (/app/node_modules/@sap/cds/lib/dbs/cds-deploy.js:116:3)
    at async /app/node_modules/@sap/cds/lib/dbs/cds-deploy.js:37:19
    at async Object.to (/app/node_modules/@sap/cds/lib/dbs/cds-deploy.js:36:7)
    at async Object.deploy_to_sql [as deploy] (/app/node_modules/@sap/cds-dk/bin/deploy/to-postgres.js:6:9) {
  length: 105,
  severity: 'ERROR',
  code: '42P07',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'heap.c',
  line: '1200',
  routine: 'heap_create_with_catalog',
  query: 'CREATE TABLE CASBIN_RULE (\n' +
    '  ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH 1 INCREMENT BY 1),\n' +
    '  PTYPE VARCHAR(100) NOT NULL,\n' +
    '  V0 VARCHAR(500),\n' +
    '  V1 VARCHAR(500),\n' +
    '  V2 VARCHAR(500),\n' +
    '  V3 VARCHAR(500),\n' +
    '  V4 VARCHAR(500),\n' +
    '  V5 VARCHAR(500),\n' +
    '  PRIMARY KEY(ID)\n' +
    ');\n' +
    ' ^'
}

Detailed steps to reproduce

1. sample docker-compose:

   services:
   db:
   image: postgres:14.1-alpine
   restart: always
   command: -c 'max_connections=300'
   environment:
     - POSTGRES_USER=postgres
     - POSTGRES_PASSWORD=postgres
     - PGUSER=postgres
   ports:
     - '5442:5432'
   volumes:
     - db:/var/lib/postgresql/data
   healthcheck:
     test: [ "CMD", "pg_isready" ]
   dbinit:
   image: node:slim
   command: tail -F anything
   depends_on:
     - db
   environment:
     - 'CDS_CONFIG={"requires":{"db":{"kind":"postgres", "credentials":{ "host":"db", "port":5432, "user":"postgres", "password":"postgres" }}}}'
   volumes:
   db:
   driver: local

2. Open a shell to the dbinit image and create package.json:

   {
   "name": "lama-cds-deployer",
   "version": "1.0.3",
   "description": "Deploys LaMa CDS files",
   "main": "index.js",
   "scripts": {
   "test": "echo \"Error: no test specified\" && exit 1"
   },
   "author": "SAP SE",
   "license": "UNLICENSED",
   "dependencies": {
   "@cap-js/postgres": "^1.4.1",
   "@sap/cds": "^7.5.1",
   "@sap/cds-dk": "^7.5.1"
   },
   "devDependencies": {
   "@cap-js/sqlite": "^1.4.0"
   }
   }

3. Create security.cds file under /models with the CASBIN_RULE entity from above.
4. Deploy it /usr/local/bin/npx cds deploy /models/security.cds --profile pg - it works.
5. Run step 5 again - it fails with the error above.

If you so this with a simpler entity such as the one below, it works fine.

entity BC_VC_ALERT {
  key ENTITY_ID : String(300) not null default ' ';
  key SEVERITY : Integer not null default 0;
  CREATION_TIME : Timestamp not null;
  ALERT_TEXT : String(1000) default ' ';
  LOG_HEADER_REF : Integer not null default 0;
};

Details about your project

Your Project Name	https://github.com/your/repo
@sap/cds	^7.5.1
@sap/cds-dk	^7.5.1
@cap-js/postgres	^1.4.1

[patricebender]

Hi,

thanks for the sample. I followed all your steps and was not able to reproduce your issue:

# cd models
# touch security.cds
# vim security.cds
# cd ..
# cat models/security.cds
entity CASBIN_RULE {
    key ID: Integer64 @sql.append: 'GENERATED BY DEFAULT AS IDENTITY(START WITH 1 INCREMENT BY 1)';
    PTYPE: String(100) not null;
    V0: String(500);
    V1: String(500);
    V2: String(500);
    V3: String(500);
    V4: String(500);
    V5: String(500);
};

# /usr/local/bin/npx cds deploy /models/security.cds --profile pg
/> successfully deployed to db:5432 

# /usr/local/bin/npx cds deploy /models/security.cds --profile pg
/> successfully deployed to db:5432 

# /usr/local/bin/npx cds --version                                                 

@cap-js/cds-types: 0.2.0
@cap-js/postgres: 1.10.0
@cap-js/sqlite: 1.7.3
@sap/cds: 7.9.5
@sap/cds-compiler: 4.9.8
@sap/cds-dk: 7.9.8
@sap/cds-dk (global): 7.9.8
@sap/cds-fiori: 1.2.7
@sap/cds-foss: 5.0.1
@sap/cds-mtxs: 1.18.3
@sap/eslint-plugin-cds: 3.0.5
Node.js: v22.9.0
home: /node_modules/@sap/cds
lama-cds-deployer: 1.0.3

please upgrade to the latest cds versions and try again.

BR Patrice