balderdashy / balderdashy/sails
Uniqueness constraints fail when using non-english locale in PostgreSQL
- Dominant language
- JavaScript
- Stars
- 22.8k
- Forks
- 1.9k
- PR merge metrics
- No merged PRs in 30d
Description
**Sails version**: 1.0.2
**Node version**: 8.12.0
**NPM version**: 6.4.1
**DB adapter name**: postgresql
**DB adapter version**: 1.0.1
**Operating system**: Ubuntu 18.04
Using the official postgres adapter with a simple model like this:
### User.js
```javascript
module.exports = {
tableName: 'user',
attributes: {
emailAddress: {
type: 'string',
required: true,
unique: true,
isEmail: true,
maxLength: 200
}
}
```
I'm getting an error when trying to insert a new user with an already registered email:
```javascript
await User.create(Object.assign({
emailAddress: 'test@domain.tld'
}));
await User.create(Object.assign({
emailAddress: 'test@domain.tld'
}))
.intercept('E_UNIQUE', 'emailAlreadyInUse')
.fetch();
```
```
TypeError: Cannot read property '1' of null
at Object.parseNativeQueryError (/home/projectpath/node_modules/machinepack-postgresql/machines/parse-native-query-error.js:89:38)
at wrapper (/home/projectpath/node_modules/@sailshq/lodash/lib/index.js:3275:19)
at Deferred._handleExec (/home/projectpath/node_modules/machine/lib/private/help-build-machine.js:1076:19)
at Deferred.exec (/home/projectpath/node_modules/parley/lib/private/Deferred.js:286:10)
at Deferred.now (/home/projectpath/node_modules/parley/lib/private/Deferred.js:592:8)
at Deferred.now (/home/projectpath/node_modules/machine/lib/private/help-build-machine.js:1317:51)
at Deferred.execSync (/home/projectpath/node_modules/machine/lib/private/help-build-machine.js:1530:23)
at Object.queryFailed (/home/projectpath/node_modules/sails-postgresql/helpers/private/query/run-query.js:59:12)
at /home/projectpath/node_modules/machine/lib/private/help-build-machine.js:1509:52
at proceedToFinalAfterExecLC (/home/projectpath/node_modules/parley/lib/private/Deferred.js:1149:14)
at proceedToInterceptsAndChecks (/home/projectpath/node_modules/parley/lib/private/Deferred.js:909:12)
at proceedToAfterExecSpinlocks (/home/projectpath/node_modules/parley/lib/private/Deferred.js:841:10)
at /home/projectpath/node_modules/parley/lib/private/Deferred.js:303:7
at /home/projectpath/node_modules/machine/lib/private/help-build-machine.js:952:35
at Function.handlerCbs.(anonymous function) [as queryFailed] (/home/projectpath/node_modules/machine/lib/private/help-build-machine.js:945:28)
at Query.query (/home/projectpath/node_modules/machinepack-postgresql/machines/send-native-query.js:116:22)
at Query.handleError (/home/projectpath/node_modules/pg/lib/query.js:131:17)
at Connection. (/home/projectpath/node_modules/pg/lib/client.js:180:26)
at emitOne (events.js:116:13)
at Connection.emit (events.js:211:7)
at Socket. (/home/projectpath/node_modules/pg/lib/connection.js:121:12)
at emitOne (events.js:116:13)
at Socket.emit (events.js:211:7)
at addChunk (_stream_readable.js:263:12)
at readableAddChunk (_stream_readable.js:250:11)
at Socket.Readable.push (_stream_readable.js:208:10)
at TCP.onread (net.js:601:20)
```
Looking into ```parse-native-query-error.js```, I've found that the error is categorized by using a regex on the original message:
https://github.com/sailshq/machinepack-postgresql/blob/1e9b1f8d5dbdca74add8340572bac60efd3cef81/machines/parse-native-query-error.js
```javascript
if (_.isString(err.detail)) {
var matches = err.detail.match(/Key \((.*)\)=\((.*)\) already exists\.$/);
var matchedAttrName = matches[1].replace(/^\"(.*)\"/g, '$1');
footprint.keys.push(matchedAttrName);
}
```
The regex fails if the postgres locale uses a different language. For example, the message in Spanish installations is like this:
```
Ya existe la llave ("emailAddress")=(test@domain.tld).
```
Probably using just ```\((.*)\)=\((.*)\)``` in the regex will work, but I've not tried. A quick workaround is to edit the postgres setting ```lc_messages``` which in Ubuntu is located at ```/etc/postgresql/VERSION/main/postgresql.conf```:
```conf
lc_messages = 'en_US.UTF-8' # locale for system error message
```
Contributor guide
Research direction
Start with machinepack-postgresql/machines/parse-native-query-error.js and inspect how the localized PostgreSQL detail is parsed. Reproduce the duplicate email insertion with a non-English PostgreSQL locale; done means it no longer throws a TypeError and the duplicate is reported through the E_UNIQUE interception.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, node.js, postgresql
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 42/100