We have a table such as
create table pages ( id uuid primary key not null, parent_id uuid not null references pages(id), shortname text not null, shorturl text not null, constraint globally_unique_shorturl unique (shorturl) constraint locally_unique_shortname unique (parent_id,shortname) )
where the logical intent is to keep a collection of web pages where the full URL for each page is constructed from
shortname fields defined by
parent_id references. Root of the system points to itself with
parent_id. As such, any given child must have unique shortname per parent (constraint locally_unique_shortname). In addition, we have globally unique shorturl (think
bit.ly) that obviously must be globally unique for the whole table. The target is to keep both shortname and shorturl as short as possible and as such, we generate short random identifiers for shorturl. In addition, the shortname is selectable by user to allow system to create semantic URLs for every page.
When I'm trying to add a new page to the system (or modify existing page), we do it in a transaction and we create new
SAVEPOINT immediately before trying to add a new page. If we get error
ERROR: duplicate key value violates unique constraint 'XYZ'
we know that we need to rollback to savepoint and then figure out if
shortname should be modified before retrying. Note that if
shorturl fails, we can generate a new one automatically, but if
shortname fails, we need to prompt user for a new value, so we really need to know which constraint failed. (In addition, this query fails pretty seldom so I'd prefer not having to try
SELECT with the new value to figure out if it's already taken because this mostly succeeds without any extra queries. Also note that such
SELECT would still race against other concurrent transactions and I'd still end up with the same issue every now and then.) We're using serializable transactions, in case it makes a difference.
However, this causes PostgreSQL to always store this error in the server log even when the code obviously handles this correctly (rollback is emitted immediately the query fails). These errors spam the log to make other possible real problems harder to notice. Obviously, I can just fall back to filtering the log before trying to monitor it, but that would feel pretty poor solution.
To workaround this, Disable log ERROR: duplicate key value violates unique constraint suggests that I should use syntax
INSERT ... ON CONFLICT DO NOTHING. This would be otherwise fine (I could try to do insert and if no rows were affected I can assume that I have a conflict and nothing will be added to server log) but I'm missing info about which constraint failed when there are multiple possible constraints.
Is there any nice way to solve this without having to modify source code of PostgreSQL or load some custom extension in PostgreSQL?