Re: Question on error code selection in conflict detection

On Thu, Jun 12, 2025 at 12:39 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Wed, Jun 11, 2025 at 7:33 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> >
> > On Tue, Jun 10, 2025 at 2:09 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > Can we instead try to use other suitable existing error codes?
> >
> > Why?
> >
> > I mean, I'm not 100% against using existing error codes, but I feel
> > like we might be distorting the meanings of the existing error codes.
> > If we used new error codes, then people could test for those and know
> > that they would get exactly these conditions and nothing else.
> >
>
> I am okay with introducing a new error code as well. However, for some
> cases like UPDATE_MISSING, DELETE_MISSING, the existing code
> ERRCODE_NO_DATA_FOUND seems to be an exact match. The LOG message
> appears when we don't find the row to be updated or deleted while
> applying changes. This can happen if someone deletes the required rows
> on the subscriber. This case is similar to unique_key_violation where
> we report ERRCODE_UNIQUE_VIOLATION when, during apply, we found the
> row with the same key exists (for example, cases like INSERT_EXISTS or
> UPDATE_EXISTS). So, I can't think of a reason to use a different
> error_code for these cases.
>
> Now, the error_code proposed for the other two cases
> UPDATE_ORIGIN_DIFFERS, DELETE_ORIGIN_DIFFERS is debatable. The
> situation in these cases is that the row exists but differs from the
> expected state (already changed by a different origin). As of now, for
> these cases, we LOG the message and update the existing rows. I
> thought of using ERRCODE_TRIGGERED_DATA_CHANGE_VIOLATION because we
> are using it for the case where the tuple to be updated is already
> updated/deleted, which is also the case here, but the reason for the
> 'already update/delete' is different. So, this is not a perfect match,
> but it is worth considering.
>
> The other code worth considering is
> ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE for UPDATE_ORIGIN_DIFFERS and
> DELETE_ORIGIN_DIFFERS cases. We use this error code when we try to
> perform some operation, but the required object (say a tuple) is not
> in the expected state. Currently, we use it for tuples in the
> following cases:
>
> ereport(ERROR,
> (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> errmsg("attempted to delete invisible tuple")));
>
> ereport(ERROR,
> (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> errmsg("attempted to update invisible tuple")));
>
> ereport(ERROR,
> (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> errmsg_internal("attempted to overwrite invisible tuple")));
>
> ereport(ERROR,
> (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> errmsg("tuple to be updated was already modified by an
> operation triggered by the current command")));
>
> As mentioned above, the current case under discussion also falls in
> the same category, where the tuple to be updated/deleted is not in an
> expected state.
>

We could consider introducing new error codes for origin_differs cases
in the following classes:

1) Class 55 — Object Not In Prerequisite State.
This class currently includes errors like
object_not_in_prerequisite_state, object_in_use,
unsafe_new_enum_value_usage etc. We can consider adding origin_differs
to this list.

2) Class 42 — Syntax Error or Access Rule Violation. This class
currently includes errors like datatype_mismatch, collation_mismatch,
wrong_object_type etc. We can consider adding origin_mismatch to this
list.

Please note that while Class 42 is a SQL-defined class (as indicated
in errcodes.txt, see [1]), it does allow for implementation-defined
additions. So adding origin_mismatch should be acceptable under those
provisions.

Thoughts?

[1]:
# Classes that begin with 0-4 or A-H are defined by the
# standard. Within such a class, subclass values defined by the
# standard must begin with 0-4 or A-H. To define a new error code,
# ensure that it is either in an "implementation-defined class" (it
# begins with 5-9 or I-Z), or its subclass falls outside the range of
# error codes that could be present in future versions of the
# standard (i.e. the subclass value begins with 5-9 or I-Z).

thanks
Shveta