ERROR: duplicate key value violates unique constraint

[ukcb]

Because of a small error in a database query, the whole etherpad server crashes.
It does not look very robust.

I use version 1.7 with postgresqlpool.

etherpad.log
....
[2018-11-04 16:09:13.999] [ERROR] console - error: duplicate key value violates unique constraint "store_pkey"
at Connection.parseE (/home/etherpad/etherpad-lite/src/node_modules/pg/lib/connection.js:569:11)
at Connection.parseMessage (/home/etherpad/etherpad-lite/src/node_modules/pg/lib/connection.js:396:17)
at Socket. (/home/etherpad/etherpad-lite/src/node_modules/pg/lib/connection.js:132:22)
at Socket.emit (events.js:182:13)
at Socket.EventEmitter.emit (domain.js:442:20)
at addChunk (_stream_readable.js:283:12)
at readableAddChunk (_stream_readable.js:264:11)
at Socket.Readable.push (_stream_readable.js:219:10)
at Pipe.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
....
postgresql.log
....
2018-11-04 16:09:13 CET [29577-1] etherpad@etherpad ERROR: duplicate key value violates unique constraint "store_pkey"
2018-11-04 16:09:13 CET [29577-2] etherpad@etherpad DETAIL: Key (key)=(globalAuthor:a.xxxxxxxxxxxxxxx) already exists.
2018-11-04 16:09:13 CET [29577-3] etherpad@etherpad CONTEXT: SQL statement "INSERT INTO store(key,value) VALUES( $1, $2 )"
PL/pgSQL function ueberdb_insert_or_update(character varying,text) line 1 at SQL statement
2018-11-04 16:09:13 CET [29577-4] etherpad@etherpad STATEMENT: SELECT ueberdb_insert_or_update($1,$2)
....

[ukcb]

Here is my database configuration:

/* postgrespool - connection via postgresql socket */
    "dbType" : "postgrespool",
    "dbSettings" : {
        "user"    : "etherpad",
        "host"    : "/run/postgresql",
        "password": "",
        "database": "etherpad",
        "max": 20,
        "min": 8,
        "charset" : "utf8mb4"
    },

[ukcb]

Here I found something: ether/ueberDB#117

[muxator]

@ukcb, did your problem happen during table creation or in "steady state" (when the serivce was already running since a long time)?

Regarding the use of the stored function ueberdb_insert_or_update() to perform an UPSERT, I have proposed a version that made use of feature detection in ether/ueberDB#99, to bypass the function creation and use the native functionality on compatible PostreSQL versions (code here), but it was not merged.

[ukcb]

@muxator The error occurs only after a few days runtime. The database table is of course already there, because the pad has been used for some time. I use postgresql 9.6.