Virtual column does not exist #204
Comments
|
hi and thanks for the detailed report!
to further investigate, could you try hooking up sqlite locally and see whether the issue remains? |
|
@vobu thx for the quick reply! With sqlite it works just fine:
{
"features": {
"fiori_preview": true
},
"requires": {
"db": {
"kind": "sqlite",
"model": "*",
"credentials": {
"database": "my.db"
}
},
"cap-ratings": {
"kind": "odata",
"credentials": {
"path": "/ratings",
"requestTimeout": 30000
}
}
}
}[cds] - POST /ideas/$batch
[cds] - > NEW Ideas
[cds] - POST /ideas/$batch
[cds] - > READ Ideas(ID=9dbbf96f-4d80-4aea-a639-5ddf88075cd5,IsActiveEntity=false) {
'$select': 'HasActiveEntity,HasDraftEntity,ID,IsActiveEntity,createdAt,createdBy,description,modifiedAt,modifiedBy,title',
'$expand': 'DraftAdministrativeData($select=DraftIsProcessedByMe,DraftUUID,InProcessByUser)'
}
[ideas-service] - after READ Ideas {
ID: '9dbbf96f-4d80-4aea-a639-5ddf88075cd5',
createdAt: '2021-09-20T13:41:35.196Z',
createdBy: 'anonymous',
description: null,
modifiedAt: '2021-09-20T13:41:35.196Z',
modifiedBy: 'anonymous',
title: null,
DraftAdministrativeData: {
DraftUUID: '836165f5-f2f8-4997-abcb-8e58d78aff26',
DraftIsProcessedByMe: true,
InProcessByUser: 'anonymous'
},
IsActiveEntity: false,
HasActiveEntity: false,
HasDraftEntity: false
} |
ok. so let's narrow it further down by deploying to postgres w/o do things work with the |
|
Same issue. I have ran node ➜ /workspaces/cap-ideas $ npx cds-pg deploy app --to postgres
/> successfully deployed to ./capTables get created without the virtual
The issue comes up at runtime only: node ➜ /workspaces/cap-ideas $ npm run watch
> [email protected] watch /workspaces/cap-ideas
> cds watch
cds serve all --with-mocks --in-memory?
watching: cds,csn,csv,ts,mjs,cjs,js,json,properties,edmx,xml,env,css,gif,html,jpg,png,svg...
live reload enabled for browsers
[cds] - model loaded from 5 file(s):
./db/schema.cds
./srv/ideas-service.cds
./app/index.cds
./node_modules/@sap/cds/common.cds
./app/ideas/fiori-service.cds
[cds] - connect using bindings from: { registry: '~/.cds-services.json' }
[cds] - connect to db > postgres {
host: 'postgres-svc',
port: '5432',
user: 'slonik',
password: '...',
database: 'cap',
schema: 'public'
}
[cds] - serving IdeasService { at: '/ideas', impl: './srv/ideas-service.js' }
[cds] - launched in: 5.770s
[cds] - server loaded from { file: './srv/server.js' }
[cds] - server listening on { url: 'http:https://localhost:4004' }
[ terminate with ^C ]
[cds] - GET /$fiori-preview/IdeasService/Ideas?sap-ui-theme=sap_fiori_3_dark&sap-language=EN
[cds] - GET /appconfig/fioriSandboxConfig.json
[cds] - GET /$fiori-preview/IdeasService/Ideas/app/Component-preload.js
[cds] - GET /$fiori-preview/IdeasService/Ideas/app/Component-preload.js
[cds] - GET /$fiori-preview/IdeasService/Ideas/app/Component.js
[cds] - HEAD /ideas/
[cds] - GET /ideas/$metadata
[cds] - GET /sap/bc/lrep/flex/data/preview.Component?sap-language=EN
[cds] - GET /sap/bc/lrep/flex/settings
[cds] - POST /ideas/$batch
[cds] - > READ Ideas {
'$count': 'true',
'$filter': '(IsActiveEntity eq false or SiblingEntity/IsActiveEntity eq null)',
'$select': 'HasActiveEntity,ID,IsActiveEntity,createdAt,createdBy,description,modifiedAt,modifiedBy,rating,title',
'$skip': '0',
'$top': '30'
}
[cds] - GET /sap/opu/odata/sap/ESH_SEARCH_SRV/ServerInfos?$expand=Services/Capabilities
[ideas-service] - after READ Ideas []
[cds] - GET /sap/es/ina/GetServerInfo?_=1632218866265
[cds] - POST /ideas/$batch
[cds] - > NEW Ideas
[cds] - error: column "rating" does not exist
at Parser.parseErrorMessage (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:287:98)
at Parser.handlePacket (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:126:29)
at Parser.parse (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:39:38)
at Socket.<anonymous> (/workspaces/cap-ideas/node_modules/pg-protocol/dist/index.js:11:42)
at Socket.emit (events.js:400:28)
at addChunk (internal/streams/readable.js:290:12)
at readableAddChunk (internal/streams/readable.js:265:9)
at Socket.Readable.push (internal/streams/readable.js:204:10)
at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
at TCP.callbackTrampoline (internal/async_hooks.js:131:17) {
length: 107,
severity: 'ERROR',
code: '42703',
detail: undefined,
hint: undefined,
position: '176',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_relation.c',
line: '3504',
routine: 'errorMissingColumn',
id: '1132172',
level: 'ERROR',
timestamp: 1632218869595
}The Response: So it really looks like the service tries to create a new table row including the non existing virtual column. The stack trace hints Bests, Pascal |
|
After enabling debug logs for cds-pg the root cause becomes clear, it comes from the SELECT statement to retrieve the ID of the newly created entry after the actual insert: |
|
@sjvans: prepped a feature branch, see https://github.com/vobu/cds-pg/blob/b179c3e84e4f2ffb1f1942ce0b014569c62a20c3/lib/pg/Service.js#L94 |
|
@vobu @sjvans [cds] - POST /ideas/$batch
[cds] - > NEW Ideas
[cds-pg] - sql > INSERT INTO DRAFT_DraftAdministrativeData ( DraftUUID, CreationDateTime, CreatedByUser, LastChangeDateTime, LastChangedByUser, DraftIsCreatedByMe, DraftIsProcessedByMe, InProcessByUser ) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8 ) Returning *
[cds-pg] - values > [
'597fdede-cb58-46f2-97c1-c2e357592931',
'2021-09-23T07:58:36.041Z',
'anonymous',
'2021-09-23T07:58:36.041Z',
'anonymous',
true,
true,
'anonymous'
]
[cds-pg] - sql > INSERT INTO IdeasService_Ideas_drafts ( ID, DraftAdministrativeData_DraftUUID, IsActiveEntity, HasDraftEntity, HasActiveEntity, createdAt, createdBy, modifiedAt, modifiedBy ) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9 ) Returning *
[cds-pg] - values > [
'07b756d6-2b73-45ed-844e-1f92b3888289',
'597fdede-cb58-46f2-97c1-c2e357592931',
false,
false,
false,
'NOW ()',
'anonymous',
'NOW ()',
'anonymous'
]
[cds-pg] - sql > SELECT ID AS "ID", createdAt AS "createdAt", createdBy AS "createdBy", modifiedAt AS "modifiedAt", modifiedBy AS "modifiedBy", title AS "title", description AS "description", rating AS "rating", IsActiveEntity AS "IsActiveEntity", HasActiveEntity AS "HasActiveEntity", HasDraftEntity AS "HasDraftEntity", DraftAdministrativeData_DraftUUID AS "DraftAdministrativeData_DraftUUID" FROM IdeasService_Ideas_drafts ALIAS_1 WHERE ID = $1
[cds-pg] - values > [ '07b756d6-2b73-45ed-844e-1f92b3888289' ]
[cds] - error: column "rating" does not exist
at Parser.parseErrorMessage (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:287:98)
at Parser.handlePacket (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:126:29)
at Parser.parse (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:39:38)
at Socket.<anonymous> (/workspaces/cap-ideas/node_modules/pg-protocol/dist/index.js:11:42)
at Socket.emit (events.js:400:28)
at addChunk (internal/streams/readable.js:290:12)
at readableAddChunk (internal/streams/readable.js:265:9)
at Socket.Readable.push (internal/streams/readable.js:204:10)
at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
at TCP.callbackTrampoline (internal/async_hooks.js:131:17) {
length: 107,
severity: 'ERROR',
code: '42703',
detail: undefined,
hint: undefined,
position: '176',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_relation.c',
line: '3504',
routine: 'errorMissingColumn',
id: '1825034',
level: 'ERROR',
timestamp: 1632383916083
}There is still an issue with the select statement to retrieve the ID of the newly created row: |
not sure what has "changed entirely" in 5.4, but it will do so in 5.5 (eta next week). there, we will always select |
right on, i'll see that i can whip up a |
I am facing an issue with a virtual column.
Given
rating(./db/schema.cds):./srv/ideas-service.cds):./app/ideas/fiori-service.cds):this config
.cdsrc.json:{ "features": { "fiori_preview": true }, "requires": { "db": { "kind": "postgres" }, "postgres": { "impl": "cds-pg", "model": [ "app" ] }, "cap-ratings": { "kind": "odata", "credentials": { "path": "/ratings", "requestTimeout": 30000 } } }, "migrations": { "db": { "schema": { "default": "public", "clone": "_cdsdbm_clone", "reference": "_cdsdbm_ref" }, "deploy": { "tmpFile": "tmp/_autodeploy.json", "undeployFile": "db/undeploy.json" } } } }default-env.json{ "VCAP_SERVICES": { "postgres": [ { "name": "postgres", "label": "postgres", "tags": [ "postgres" ], "credentials": { "host": "postgres-svc", "port": "5432", "user": "***", "password": "***", "database": "cap", "schema": "public" } } ], "cap-ratings": [{ "name": "cap-ratings", "label": "cap-ratings", "tags": [ "cap-ratings" ], "credentials": { "url": "http:https://cap-ratings-svc:5005" } }] }, "DEBUG": "ideas-service" }I am running
npx cds-dbm deployto deploy the schema to the database.Now when running the fiori app preview and creating a new entity I am receiving an error:
Any ideas what could be the root cause and why the service tries to persist the virtual column to the database throwing an error?
Running the service with sqlite everything works as expected.
cds -vThanks in advance,
Pascal
The text was updated successfully, but these errors were encountered: