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

Question

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

son2005 opened this issue 8 months ago · 2 comments

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

Answer 1 · 2024-04-11T10:19:28.000Z

Hi @son2005,

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

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

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

add another field to Books
add data in the csv for the newly added field
cds build --profile pg
DEBUG=sql cds deploy --profile pg (enabled env variable DEBUG=sql for more verbose output

❯ 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!

Answer 2 · 2024-04-22T06:59:32.000Z

closing this due to inactivity.

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