search

cap-js / cds-dbs

Monorepo for SQL Database Services for CAP
https://cap.cloud.sap/docs/
Apache License 2.0
37 stars 12 forks source link

PostgreSQL - Bug identified in cds deploy command during schema evolution process #556

Closed son2005 closed 7 months ago

son2005 commented 8 months ago

Description of erroneous behaviour

Hello, I've identified a bug with the cds deploy command. In step 4 of Automatic Schema Evolution, the command instructs to 'Fill in initial data from provided .csv files using UPSERT commands.' However, upon running cds deploy for the second time, it utilizes INSERT commands instead of UPSERT commands. Link to Automatic Schema Evolution

My test entity

entity Test {
key name : String;
}

When re-deploying to PostgreSQL DB we get the following errors:

 MyProjects> cds deploy --profile pg 
  > init from db\data\TestService-Test.csv 
/> successfully deployed to localhost:5432 

 MyProjects>cds deploy --profile pg 
  > init from db\data\TestService-Test.csv 
/> deployment to localhost:5432 failed

error: in cds.deploy(): ENTITY_ALREADY_EXISTS
Query {
  INSERT: {
    into: 'TestService.Test',
    columns: [ 'name' ],
    rows: [
      [ 'S/4' ],     [ 'NetWeaver' ],
      [ 'IBP' ],     [ 'SteamPunk' ],
      [ 'SCSD' ],    [ 'UICB4H' ],
      [ 'GRC-ACS' ], [ 'GRC-RMPC' ],
      [ 'GTS' ],     [ 'HCM' ],
      [ 'CARAB' ],   [ 'PLMSI' ],
      [ 'EHSM' ],    [ 'ACM' ]
    ]
  }
}
    at MyProjects\node_modules\pg\lib\client.js:526:17
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Object.run (MyProjects\node_modules\@cap-js\postgres\lib\PostgresService.js:152:24)
    at async Promise.all (index 0)
    at async PostgresService.onINSERT (MyProjects\node_modules\@cap-js\db-service\lib\SQLService.js:159:31)
    at async PostgresService.onINSERT (MyProjects\node_modules\@cap-js\postgres\lib\PostgresService.js:313:14)
    at async next (MyProjects\node_modules\@sap\cds\lib\srv\srv-dispatch.js:79:17)
    at async next (MyProjects\node_modules\@sap\cds\lib\srv\srv-dispatch.js:79:17)
    at async next (MyProjects\node_modules\@sap\cds\lib\srv\srv-dispatch.js:79:17)
    at async PostgresService.handle (MyProjects\node_modules\@sap\cds\lib\srv\srv-dispatch.js:77:10) {
  length: 217,
  severity: 'ERROR',
  code: 400,
  detail: 'Key (name)=(S/4) already exists.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'TestService_test',
  column: undefined,
  dataType: undefined,
  constraint: 'TestService_test_pkey',
  file: 'nbtinsert.c',
  line: '673',
  routine: '_bt_check_unique',
  originalMessage: 'duplicate key value violates unique constraint "TestService_test_pkey"'
}

Details about your project

Your Project Name no show
@cap-js/cds-types 0.2.0
@cap-js/postgres 1.6.0
@sap/cds 7.7.2
@sap/cds-compiler 4.8.0
@sap/cds-dk 7.7.2
@sap/cds-dk (global) 7.7.2
@sap/cds-fiori 1.2.3
@sap/cds-foss 5.0.0
@sap/cds-mtxs 1.16.0
@sap/eslint-plugin-cds 2.6.7
Node.js v18.16.1
patricebender commented 7 months ago

Hi @son2005,

I have tried to re-produce the issue with the bookshop. On my side, everything works:

  1. cds build --profile pg:
❯ cds build --profile pg
building project [/Users/patricebender/SAPDevelop/dev/cap/samples/bookshop], clean [true]
cds-dk [7.8.0], cds [7.7.1], compiler [4.3.2], home [/Users/patricebender/SAPDevelop/dev/cap/samples/node_modules/@sap/cds]

{
  build: {
    target: 'gen',
    tasks: [
      { for: 'nodejs', src: 'srv', options: { model: ['db', 'srv', 'app'] }},
      { for: 'postgres', src: 'db', options: { model: ['db', 'srv', 'app'] }}
    ]
  }
}

done > wrote output to:
   gen/pg/db/csn.json
   gen/pg/db/data
   gen/pg/package.json
   gen/srv/index.js
   gen/srv/package.json
   gen/srv/srv/_i18n/i18n.json
   gen/srv/srv/admin-service.js
   gen/srv/srv/cat-service.js
   gen/srv/srv/csn.json
   gen/srv/srv/odata/v4/AdminService.xml
   gen/srv/srv/odata/v4/CatalogService.xml
   gen/srv/srv/odata/v4/UserService.xml
   gen/srv/srv/user-service.js

build completed in 239 ms
  1. cds deploy --profile pg
❯ cds deploy --profile pg
  > init from db/init.js 
  > init from db/data/sap.capire.bookshop-Genres.csv 
  > init from db/data/sap.capire.bookshop-Books.texts.csv 
  > init from db/data/sap.capire.bookshop-Books.csv 
  > init from db/data/sap.capire.bookshop-Authors.csv 
/> successfully deployed to localhost:5432
  1. add another field to Books
  2. add data in the csv for the newly added field
  3. cds build --profile pg
  4. DEBUG=sql cds deploy --profile pg (enabled env variable DEBUG=sql for more verbose output
```log ❯ DEBUG=sql cds deploy --profile pg [sql] - BEGIN [sql] - SELECT 1 from pg_tables WHERE tablename = 'cds_model' and schemaname = current_schema() {} [sql] - SELECT csn from cds_model {} [sql] - UPDATE cds_model SET csn = $1 [ '{"namespace":"sap.capire.bookshop","definitions":{"sap.capire.bookshop.Books":{"kind":"entity","elements":{"createdAt":{"@cds.on.insert":{"=":"$now"},"@UI.HiddenFilter":true,"@Core.Immutable":true,"@title":"{i18n>CreatedAt}","@readonly":true,"type":"cds.Timestamp","@cds.persistence.name":"CREATEDAT"},"createdBy":{"@cds.on.insert":{"=":"$user"},"@UI.HiddenFilter":true,"@Core.Immutable":true,"@title":"{i18n>CreatedBy}","@readonly":true,"@description":"{i18n>UserID.Description}","type":"cds.String","length":255,"@cds.persistence.name":"CREATEDBY"},"modifiedAt":{"@cds.on.insert":{"=":"$now"},"@cds.on.update":{"=":"$now"},"@UI.HiddenFilter":true,"@title":"{i18n>ChangedAt}","@readonly":true,"type":"cds.Timestamp","@cds.persistence.name":"MODIFIEDAT"},"modifiedBy":{"@cds.on.insert":{"=":"$user"},"@cds.on.update":{"=":"$user"},"@UI.HiddenFilter":true,"@title":"{i18n>ChangedBy}","@readonly":true,"@description":"{i18n>UserID.Description}","type":"cds.String","length":255,"@cds.persistence.name":"MODIFIEDBY"},"ID":{"key":true,"type":"cds.Integer","@cds.persistence.name":"ID"},"title":{"@mandatory":true,"type":"cds.String","length":111,"@cds.persistence.name":"TITLE"},"title2":{"@mandatory":true,"type":"cds.String","length":111,"@cds.persistence.name":"TITLE2"},"descr":{"type":"cds.String","length":1111,"@cds.persistence.name":"DESCR"},"author":{"@mandatory":true,"type":"cds.Association","target":"sap.capire.bookshop.Authors","keys":[{"ref":["ID"],"as":"ID","$generatedFieldName":"author_ID"}],"on":[{"ref":["author","ID"]},"=",{"ref":["author_ID"]}],"@cds.persistence.name":"AUTHOR"},"author_ID":{"type":"cds.Integer","@odata.foreignKey4":"author","@cds.persistence.name":"AUTHOR_ID"},"genre":{"type":"cds.Association","target":"sap.capire.bookshop.Genres","keys":[{"ref":["ID"],"as":"ID","$generatedFieldName":"genre_ID"}],"on":[{"ref":["genre","ID"]},"=",{"ref":["genre_ID"]}],"@cds.persistence.name":"GENRE"},"genre_ID":{"type":"cds.Integer","@odata.foreignKey4":"genre","@cds.persistence.name":"GENRE_ID"},"stock":{"type":"cds.Integer","@cds.persistence.name":"STOCK"},"price":{"type":"cds.Decimal","@cds.persistence.name":"PRICE"},"currency":{"@title":"{i18n>Currency}","@description":"{i18n>CurrencyCode.Description}","type":"cds.Association","target":"sap.common.Currencies","keys":[{"ref":["code"],"as":"code","$generatedFieldName":"currency_code"}],"on":[{"ref":["currency","code"]},"=",{"ref":["currency_code"]}],"@cds.persistence.name":"CURRENCY"},"currency_code":{"type":"cds.String","length":3,"@odata.foreignKey4":"currency","@cds.persistence.name":"CURRENCY_CODE"},"image":{"@Core.MediaType":"image/png","type":"cds.LargeBinary","@cds.persistence.name":"IMAGE"},"manyStruct":{"type":"cds.LargeString","@cds.persistence.name":"MANYSTRUCT"},"texts":{"type":"cds.Composition","cardinality":{"max":"*"},"target":"sap.capire.bookshop.Books.texts","on":[{"ref":["texts","ID"]},"=",{"ref":["ID"]}],"@cds.persistence.name":"TEXTS"},"localized":{"type":"cds.Association","target":"sap.capire.bookshop.Books.texts","on":[{"ref":["localized","ID"]},"=",{"ref":["ID"]},"and",{"ref":["localized","locale"]},"=",{"ref":["$user","locale"]}],"@cds.persistence.name":"LOCALIZED"}},"@cds.persistence.name":"SAP_CAPIRE_BOOKSHOP_BOOKS"},"sap.capire.bookshop.Authors":{"kind":"entity","elements":{"createdAt":{"@cds.on.insert":{"=":"$now"},"@UI.HiddenFilter":true,"@Core.Immutable":true,"@title":"{i18n>CreatedAt}","@readonly":true,"type":"cds.Timestamp","@cds.persistence.name":"CREATEDAT"},"createdBy":{"@cds.on.insert":{"=":"$user"},"@UI.HiddenFilter":true,"@Core.Immutable":true,"@title":"{i18n>CreatedBy}","@readonly":true,"@description":"{i18n>UserID.Description}","type":"cds.String","length":255,"@cds.persistence.name":"CREATEDBY"},"modifiedAt":{"@cds.on.insert":{"=":"$now"},"@cds.on.update":{"=":"$now"},"@UI.HiddenFilter":true,"@title":"{i18n>ChangedAt}","@readonly":true,"type":"cds.Timestamp","@cds.persistence.name":"MODIFIEDAT"},"modifiedBy":{"@cds.on.insert":{"=":"$user"},"@cds.on.update":{"=":"$user"},"@UI.HiddenFilter":true,"@title":"{i18n>ChangedBy}","@readonly":true,"@description":"{i18n>UserID.Description}","type":"cds.String","length":255,"@cds.persistence.name":"MODIFIEDBY"},"ID":{"key":true,"type":"cds.Integer","@cds.persistence.name":"ID"},"name":{"@mandatory":true,"type":"cds.String","length":111,"@cds.persistence.name":"NAME"},"dateOfBirth":{"type":"cds.Date","@cds.persistence.name":"DATEOFBIRTH"},"dateOfDeath":{"type":"cds.Date","@cds.persistence.name":"DATEOFDEATH"},"placeOfBirth":{"type":"cds.String","length":255,"@cds.persistence.name":"PLACEOFBIRTH"},"placeOfDeath":{"type":"cds.String","length":255,"@cds.persistence.name":"PLACEOFDEATH"},"books":{"type":"cds.Association","cardinality":{"max":"*"},"target":"sap.capire.bookshop.Books","on":[{"ref":["books","author_ID"]},"=",{"ref":["ID"]}],"@cds.persistence.name":"BOOKS"},"number":{"type":"cds.Int16","@cds.persistence.name":"NUMBER"}},"@cds.persistence.name":"SAP_CAPIRE_BOOKSHOP_AUTHORS"},"sap.capire.bookshop.Genres":{"kind":"entity","@cds.autoexpose":true,"@UI.Identification":[{"Value":{"=":"name"}}],"@cds.odata.valuelist":true,"elements":{"name":{"@title":"{i18n>Name}","type":"cds.String","length":255,"@cds.persistence.name":"NAME"},"descr":{"@title":"{i18n>Description}","type":"cds.String","length":1000,"@cds.persistence.name":"DESCR"},"ID":{"key":true,"type":"cds.Integer","@cds.persistence.name":"ID"},"parent":{"type":"cds.Association","target":"sap.capire.bookshop.Genres","keys":[{"ref":["ID"],"as":"ID","$generatedFieldName":"parent_ID"}],"on":[{"ref":["parent","ID"]},"=",{"ref":["parent_ID"]}],"@cds.persistence.name":"PARENT"},"parent_ID":{"type":"cds.Integer","@odata.foreignKey4":"parent","@cds.persistence.name":"PARENT_ID"},"children":{"type":"cds.Composition","cardinality":{"max":"*"},"target":"sap.capire.bookshop.Genres","on":[{"ref":["children","parent_ID"]},"=",{"ref":["ID"]}],"@cds.persistence.name":"CHILDREN"},"texts":{"type":"cds.Composition","cardinality":{"max":"*"},"target":"sap.capire.bookshop.Genres.texts","on":[{"ref":["texts","ID"]},"=",{"ref":["ID"]}],"@cds.persistence.name":"TEXTS"},"localized":{"type":"cds.Association","target":"sap.capire.bookshop.Genres.texts","on":[{"ref":["localized","ID"]},"=",{"ref":["ID"]},"and",{"ref":["localized","locale"]},"=",{"ref":["$user","locale"]}],"@cds.persistence.name":"LOCALIZED"}},"@cds.persistence.name":"SAP_CAPIRE_BOOKSHOP_GENRES"},"AdminService":{"kind":"service","@requires":"admin","@path":"/admin"},"AdminService.Books":{"kind":"entity","elements":{"createdAt":{"@cds.on.insert":{"=":"$now"},"@UI.HiddenFilter":true,"@Core.Immutable":true,"@title":"{i18n>CreatedAt}","@readonly":true,"type":"cds.Timestamp","@cds.persistence.name":"CREATEDAT"},"createdBy":{"@cds.on.insert":{"=":"$user"},"@UI.HiddenFilter":true,"@Core.Immutable":true,"@title":"{i18n>CreatedBy}","@readonly":true,"@description":"{i18n>UserID.Description}","type":"cds.String","length":255,"@cds.persistence.name":"CREATEDBY"},"modifiedAt":{"@cds.on.insert":{"=":"$now"},"@cds.on.update":{"=":"$now"},"@UI.HiddenFilter":true,"@title":"{i18n>ChangedAt}","@readonly":true,"type":"cds.Timestamp","@cds.persistence.name":"MODIFIEDAT"},"modifiedBy":{"@cds.on.insert":{"=":"$user"},"@cds.on.update":{"=":"$user"},"@UI.HiddenFilter":true,"@title":"{i18n>ChangedBy}","@readonly":true,"@description":"{i18n>UserID.Description}","type":"cds.String","length":255,"@cds.persistence.name":"MODIFIEDBY"},"ID":{"key":true,"type":"cds.Integer","@cds.persistence.name":"ID"},"title":{"@mandatory":true,"type":"cds.String","length":111,"@cds.persistence.name":"TITLE"},"title2":{"@mandatory":true,"type":"cds.String","length":111,"@cds.persistence.name":"TITLE2"},"descr":{"type":"cds.String","length":1111,"@cds.persistence.name":"DESCR"},"author":{"@mandatory":true,"type":"cds.Association","target":"AdminService.Authors","keys":[{"ref":["ID"],"as":"ID","$generatedFieldName":"author_ID"}],"on":[{"ref":["author","ID"]},"=",{"ref":["author_ID"]}],"@cds.persistence.name":"AUTHOR"},"author_ID":{"type":"cds.Integer","@odata.foreignKey4":"author","@cds.persistence.name":"AUTHOR_ID"},"genre":{"type":"cds.Association","target":"AdminService.Genres","keys":[{"ref":["ID"],"as":"ID","$generatedFieldName":"genre_ID"}],"on":[{"ref":["genre","ID"]},"=",{"ref":["genre_ID"]}],"@cds.persistence.name":"GENRE"},"genre_ID":{"type":"cds.Integer","@odata.foreignKey4":"genre","@cds.persistence.name":"GENRE_ID"},"stock":{"type":"cds.Integer","@cds.persistence.name":"STOCK"},"price":{"type":"cds.Decimal","@cds.persistence.name":"PRICE"},"currency":{"@title":"{i18n>Currency}","@description":"{i18n>CurrencyCode.Description}","type":"cds.Association","target":"AdminService.Currencies","keys":[{"ref":["code"],"as":"code","$generatedFieldName":"currency_code"}],"on":[{"ref":["currency","code"]},"=",{"ref":["currency_code"]}],"@cds.persistence.name":"CURRENCY"},"currency_code":{"type":"cds.String","length":3,"@odata.foreignKey4":"currency","@cds.persistence.name":"CURRENCY_CODE"},"image":{"@Core.MediaType":"image/png","type":"cds.LargeBinary","@cds.persistence.name":"IMAGE"},"manyStruct":{"type":"cds.LargeString","@cds.persistence.name":"MANYSTRUCT"},"texts":{"type":"cds.Composition","cardinality":{"max":"*"},"target":"AdminService.Books.texts","on":[{"ref":["texts","ID"]},"=",{"ref":["ID"]}],"@cds.persistence.name":"TEXTS"},"localized":{"type":"cds.Association","target":"AdminService.Books.texts","on":[{"ref":["localized","ID"]},"=",{"ref":["ID"]},"and",{"ref":["localized","locale"]},"=",{"ref":["$user","locale"]}],"@cds.persistence.name":"LOCALIZED"}},"@cds.persistence.name":"ADMINSERVICE_BOOKS","projection":{"from":{"ref":["sap.capire.bookshop.Books"],"as":"Books_0"},"columns":[{"ref":["Books_0","createdAt"]},{"ref":["Books_0","createdBy"]},{"ref":["Books_0","modifiedAt"]},{"ref":["Books_0","modifiedBy"]},{"ref":["Books_0","ID"]},{"ref":["Books_0","title"]},{"ref":["Books_0","title2"]},{"ref":["Books_0","descr"]},{"ref":["___author'... 70506 more characters ] > init from db/init.js > init from db/data/sap.capire.bookshop-Genres.csv [sql] - INSERT INTO sap_capire_bookshop_Genres (ID,parent_ID,name) SELECT CAST(value->>0 as integer),CAST(value->>1 as integer),value->>2 FROM jsonb_array_elements($1::jsonb) WHERE true ON CONFLICT(ID) DO UPDATE SET parent_ID = excluded.parent_ID,name = excluded.name [ [ Readable { _readableState: [ReadableState], _events: [Object: null prototype] {}, _eventsCount: 0, _maxListeners: undefined, _read: [Function (anonymous)], _destroy: [Function (anonymous)], type: 'json', [Symbol(kCapture)]: false } ] ] > init from db/data/sap.capire.bookshop-Books.texts.csv [sql] - INSERT INTO sap_capire_bookshop_Books_texts (ID,locale,title,descr) SELECT CAST(value->>0 as integer),value->>1,value->>2,value->>3 FROM jsonb_array_elements($1::jsonb) WHERE true ON CONFLICT(locale,ID) DO UPDATE SET title = excluded.title,descr = excluded.descr [ [ Readable { _readableState: [ReadableState], _events: [Object: null prototype] {}, _eventsCount: 0, _maxListeners: undefined, _read: [Function (anonymous)], _destroy: [Function (anonymous)], type: 'json', [Symbol(kCapture)]: false } ] ] > init from db/data/sap.capire.bookshop-Books.csv [sql] - INSERT INTO sap_capire_bookshop_Books (ID,title,title2,descr,author_ID,stock,price,currency_code,genre_ID,createdAt,createdBy,modifiedAt,modifiedBy) SELECT CAST(value->>0 as integer),value->>1,value->>2,value->>3,CAST(value->>4 as integer),CAST(value->>5 as integer),CAST(value->>6 as decimal),value->>7,CAST(value->>8 as integer),CAST(value->>9 as TIMESTAMP),value->>10,CAST(value->>11 as TIMESTAMP),value->>12 FROM jsonb_array_elements($1::jsonb) WHERE true ON CONFLICT(ID) DO UPDATE SET title = excluded.title,title2 = excluded.title2,descr = excluded.descr,author_ID = excluded.author_ID,stock = excluded.stock,price = excluded.price,currency_code = excluded.currency_code,genre_ID = excluded.genre_ID,createdAt = excluded.createdAt,createdBy = excluded.createdBy,modifiedAt = excluded.modifiedAt,modifiedBy = excluded.modifiedBy [ [ Readable { _readableState: [ReadableState], _events: [Object: null prototype] {}, _eventsCount: 0, _maxListeners: undefined, _read: [Function (anonymous)], _destroy: [Function (anonymous)], type: 'json', [Symbol(kCapture)]: false } ] ] > init from db/data/sap.capire.bookshop-Authors.csv [sql] - INSERT INTO sap_capire_bookshop_Authors (ID,name,dateOfBirth,placeOfBirth,dateOfDeath,placeOfDeath,createdAt,createdBy,modifiedAt,modifiedBy) SELECT CAST(value->>0 as integer),value->>1,CAST(value->>2 as DATE),value->>3,CAST(value->>4 as DATE),value->>5,CAST(value->>6 as TIMESTAMP),value->>7,CAST(value->>8 as TIMESTAMP),value->>9 FROM jsonb_array_elements($1::jsonb) WHERE true ON CONFLICT(ID) DO UPDATE SET name = excluded.name,dateOfBirth = excluded.dateOfBirth,placeOfBirth = excluded.placeOfBirth,dateOfDeath = excluded.dateOfDeath,placeOfDeath = excluded.placeOfDeath,createdAt = excluded.createdAt,createdBy = excluded.createdBy,modifiedAt = excluded.modifiedAt,modifiedBy = excluded.modifiedBy [ [ Readable { _readableState: [ReadableState], _events: [Object: null prototype] {}, _eventsCount: 0, _maxListeners: undefined, _read: [Function (anonymous)], _destroy: [Function (anonymous)], type: 'json', [Symbol(kCapture)]: false } ] ] [sql] - COMMIT /> successfully deployed to localhost:5432 ```

this workflow also succeeds if no field has been changed (step 3. and 4. could be skipped)

❯ cds --version
@cap-js/cds-types: 0.3.0
@cap-js/postgres: 1.6.0
@cap-js/sqlite: 1.5.1
@capire/bookshop: 1.0.0
@sap/cds: 7.7.1
@sap/cds-compiler: 4.3.2
@sap/cds-dk: 7.8.0
@sap/cds-dk (global): 7.8.0
@sap/cds-fiori: 1.2.3
@sap/cds-foss: 5.0.0
@sap/cds-mtxs: 1.16.0
@sap/eslint-plugin-cds: 2.6.8
Node.js: v18.18.2
home: /Users/patricebender/SAPDevelop/dev/cap/samples/node_modules/@sap/cds

could you please add a sample repository with detailed steps to reproduce the issue? I suspect in the data a primary key is duplicated as indicated by the error message in your log 'duplicate key value violates unique constraint "TestService_test_pkey"'

Thanks!

patricebender commented 7 months ago

closing this due to inactivity.

If the issue persists, feel free to get back to us with a reproducible sample. Thanks.