This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking).  Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users may optionally specify a single unique index to merge on with aWITHIN `unique_index` specification, which is useful when there is aconcern about spuriously merging on the wrong unique index due to therebeing more than one would-be unique violation.  Otherwise, we UPDATE (orIGNORE) based on the first would-be unique violation detected, on theassumption that that is the only unique index where a violation couldappear.The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The optimizer imposes some restrictions on child auxiliary UPDATE plans,which make the plans comport with their parent to the extent requiredduring the executor stage.  One user-visible consequences of this isthat the special auxiliary UPDATE query cannot have subselects withinits targetlist or WHERE clause.  UPDATEs may not reference any othertable, and UPDATE FROM is disallowed.  INSERT's RETURNING clausecontinues to only project tuples actually inserted.

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking).  Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The optimizer imposes some restrictions on child auxiliary UPDATE plans,which make the plans comport with their parent to the extent requiredduring the executor stage.  One user-visible consequences of this isthat the special auxiliary UPDATE query cannot have subselects withinits targetlist or WHERE clause.  UPDATEs may not reference any othertable, and UPDATE FROM is disallowed.  INSERT's RETURNING clausecontinues to only project tuples actually inserted.

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking).  Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The optimizer imposes some restrictions on child auxiliary UPDATE plans,which make the plans comport with their parent to the extent requiredduring the executor stage.  One user-visible consequences of this isthat the special auxiliary UPDATE query cannot have subselects withinits targetlist or WHERE clause.  UPDATEs may not reference any othertable, and UPDATE FROM is disallowed.  INSERT's RETURNING clausecontinues to only project tuples actually inserted.

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking).  Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The optimizer imposes some restrictions on child auxiliary UPDATE plans,which make the plans comport with their parent to the extent requiredduring the executor stage.  One user-visible consequences of this isthat the special auxiliary UPDATE query cannot have subselects withinits targetlist or WHERE clause.  UPDATEs may not reference any othertable, and UPDATE FROM is disallowed.  INSERT's RETURNING clausecontinues to only project tuples actually inserted.

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking).  Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The implementation imposes some restrictions on child auxiliary UPDATEplans, which make the plans comport with their parent to the extentrequired during the executor stage.  One user-visible consequences ofthis is that the special auxiliary UPDATE query cannot have subselectswithin its targetlist or WHERE clause.  UPDATEs may not reference anyother table, and UPDATE FROM is disallowed.  INSERT's RETURNING clauseprojects tuples successfully inserted (in a later commit, it is made toproject tuples inserted and updated, though).

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking).  Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The implementation imposes some restrictions on child auxiliary UPDATEplans, which make the plans comport with their parent to the extentrequired during the executor stage.  One user-visible consequences ofthis is that the special auxiliary UPDATE query cannot have subselectswithin its targetlist or WHERE clause.  UPDATEs may not reference anyother table, and UPDATE FROM is disallowed.  INSERT's RETURNING clauseprojects tuples successfully inserted (in a later commit, it is made toproject tuples inserted and updated, though).

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking).  Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The implementation imposes some restrictions on child auxiliary UPDATEplans, which make the plans comport with their parent to the extentrequired during the executor stage.  One user-visible consequences ofthis is that the special auxiliary UPDATE query cannot have subselectswithin its targetlist or WHERE clause.  UPDATEs may not reference anyother table, and UPDATE FROM is disallowed.  INSERT's RETURNING clauseprojects tuples successfully inserted (in a later commit, it is made toproject tuples inserted and updated, though).

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking).  Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The implementation imposes some restrictions on child auxiliary UPDATEplans, which make the plans comport with their parent to the extentrequired during the executor stage.  One user-visible consequences ofthis is that the special auxiliary UPDATE query cannot have subselectswithin its targetlist or WHERE clause.  UPDATEs may not reference anyother table, and UPDATE FROM is disallowed.  INSERT's RETURNING clauseprojects tuples successfully inserted (in a later commit, it is made toproject tuples inserted and updated, though).

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking).  Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The implementation imposes some restrictions on child auxiliary UPDATEplans, which make the plans comport with their parent to the extentrequired during the executor stage.  One user-visible consequences ofthis is that the special auxiliary UPDATE query cannot have subselectswithin its targetlist or WHERE clause.  UPDATEs may not reference anyother table, and UPDATE FROM is disallowed.  INSERT's RETURNING clauseprojects tuples successfully inserted (in a later commit, it is made toproject tuples inserted and updated, though).

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking).  Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The implementation imposes some restrictions on child auxiliary UPDATEplans, which make the plans comport with their parent to the extentrequired during the executor stage.  One user-visible consequences ofthis is that the special auxiliary UPDATE query cannot have subselectswithin its targetlist or WHERE clause.  UPDATEs may not reference anyother table, and UPDATE FROM is disallowed.  INSERT's RETURNING clauseprojects tuples successfully inserted (in a later commit, it is made toproject tuples inserted and updated, though).

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking).  Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The implementation imposes some restrictions on child auxiliary UPDATEplans, which make the plans comport with their parent to the extentrequired during the executor stage.  One user-visible consequences ofthis is that the special auxiliary UPDATE query cannot have subselectswithin its targetlist or WHERE clause.  UPDATEs may not reference anyother table, and UPDATE FROM is disallowed.  INSERT's RETURNING clauseprojects tuples successfully inserted (in a later commit, it is made toproject tuples inserted and updated, though).

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described onhttps://wiki.postgresql.org/wiki/Value_locking).  Alternatively, we maygo to UPDATE, using the EvalPlanQual() mechanism to execute a specialauxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The implementation imposes some restrictions on child auxiliary UPDATEplans, which make the plans comport with their parent to the extentrequired during the executor stage.  One user-visible consequences ofthis is that the special auxiliary UPDATE query cannot have subselectswithin its targetlist or WHERE clause.  UPDATEs may not reference anyother table, and UPDATE FROM is disallowed.  INSERT's RETURNING clauseprojects tuples successfully inserted (in a later commit, it is made toproject tuples inserted and updated, though).

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described on the value locking Postgres Wiki page).Alternatively, we may go to UPDATE, using the EvalPlanQual() mechanismto execute a special auxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The implementation imposes some restrictions on child auxiliary UPDATEplans, which make the plans comport with their parent to the extentrequired during the executor stage.  One user-visible consequences ofthis is that the special auxiliary UPDATE query cannot have subselectswithin its targetlist or WHERE clause.  UPDATEs may not reference anyother table, and UPDATE FROM is disallowed.  INSERT's RETURNING clauseprojects tuples successfully inserted (in a later commit, it is made toproject tuples inserted and updated, though).

Includes documentation for executor README.  A high-level handling ofapproachpostgres#2 to value locking also appears there, since in contrast withdesignpostgres#1, that is something that lives in the head of the executor.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative path may be taken.  Thestatement may alternatively IGNORE the tuple being inserted withoutraising an error, or go to UPDATE the existing tuple whose value isduplicated by a value within one single tuple proposed for insertion.The implementation loops until either an insert or an UPDATE/IGNOREoccurs.  No existing tuple may be affected more than once per INSERT.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described on the value locking Postgres Wiki page).Alternatively, we may go to UPDATE, using the EvalPlanQual() mechanismto execute a special auxiliary plan.READ COMMITTED isolation level is permitted to UPDATE a tuple even whereno version is visible to the command's MVCC snapshot.  Similarly, anyquery predicate associated with the UPDATE portion of the new statementneed only satisfy an already locked, conclusively committed and visibleconflict tuple.  When the predicate isn't satisfied, the tuple is stilllocked, which implies that at READ COMMITTED, a tuple may be lockedwithout any version being visible to the command's MVCC snapshot.Users specify a single unique index to take the alternative path on,which is inferred from a set of user-supplied column names (orexpressions).  This is mandatory for the ON CONFLICT UPDATE variant,which should address concerns about spuriously taking an incorrectalternative ON CONFLICT path (i.e. the wrong unique index is used forarbitration of whether or not to take the alternative path) due to therebeing more than one would-be unique violation.  Previous revisions ofthe patch didn't mandate this.  However, we may still IGNORE based onthe first would-be unique violation detected, on the assumption that itdoesn't particularly matter where it originated from for that variant(iff the user didn't make a point of indicated his or her intent).The auxiliary ModifyTable plan used by the UPDATE portion of the newstatement is not formally a subplan of its parent INSERT ModifyTableplan.  Rather, it's an independently planned subquery, whose executionis tightly driven by its parent.  Special auxiliary state pertaining tothe auxiliary UPDATE is tracked by its parent through all stages ofquery execution.The implementation imposes some restrictions on child auxiliary UPDATEplans, which make the plans comport with their parent to the extentrequired during the executor stage.  One user-visible consequences ofthis is that the special auxiliary UPDATE query cannot have subselectswithin its targetlist or WHERE clause.  UPDATEs may not reference anyother table, and UPDATE FROM is disallowed.  INSERT's RETURNING clauseprojects tuples successfully inserted (in a later commit, it is made toproject tuples inserted and updated, though).

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative IGNORE path may be taken(the tuple slot proposed for insertion is skipped without raising anerror).  The implementation loops until either an insert occurs, or aconclusively committed conflicting tuple is determined to exist.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described on the value locking Postgres Wiki page).Users optionally specify a single unique index to take the alternativepath on, which is inferred from a set of user-supplied column names (orexpressions).Speculative (heap) insertions are WAL-logged in two steps:  One recordrelates to an initial intent to insert, while a second record affirmsthat that attempt was ultimately successful (i.e. no conflicts wheredetected when inserting into constraint-related indexes).  Logicaldecoding decodes the second record that affirms the insert, reporting itas a regular insert change.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative IGNORE path may be taken(the tuple slot proposed for insertion is skipped without raising anerror).  The implementation loops until either an insert occurs, or aconclusively committed conflicting tuple is determined to exist.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described on the value locking Postgres Wiki page).Users optionally specify a single unique index to take the alternativepath on, which is inferred from a set of user-supplied column names (orexpressions).Speculative (heap) insertions are WAL-logged in two steps:  One recordrelates to an initial intent to insert, while a second minimal recordsimply affirms that that attempt was ultimately successful (i.e. noconflicts where detected when inserting into constraint-relatedindexes).  In this revision, logical decoding does not rely on thepresence of this second record to affirm that a speculative insertionsucceeded, though; it relies on the *absence* on an (internal) "superdeletion" record.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative IGNORE path may be taken(the tuple slot proposed for insertion is skipped without raising anerror).  The implementation loops until either an insert occurs, or aconclusively committed conflicting tuple is determined to exist.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described on the value locking Postgres Wiki page).Users optionally specify a single unique index to take the alternativepath on, which is inferred from a set of user-supplied column names (orexpressions).Speculative (heap) insertions are WAL-logged in two steps:  One recordrelates to an initial intent to insert, while a second minimal recordsimply affirms that that attempt was ultimately successful (i.e. noconflicts where detected when inserting into constraint-relatedindexes).  In this revision, logical decoding does not rely on thepresence of this second record to affirm that a speculative insertionsucceeded, though; it relies on the *absence* on an (internal) "superdeletion" record.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative IGNORE path may be taken(the tuple slot proposed for insertion is skipped without raising anerror).  The implementation loops until either an insert occurs, or aconclusively committed conflicting tuple is determined to exist.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described on the value locking Postgres Wiki page).Users optionally specify a single unique index to take the alternativepath on, which is inferred from a set of user-supplied column names (orexpressions).Speculative (heap) insertions are WAL-logged in two steps:  One recordrelates to an initial intent to insert, while a second minimal recordsimply affirms that that attempt was ultimately successful (i.e. noconflicts where detected when inserting into constraint-relatedindexes).  In this revision, logical decoding does not rely on thepresence of this second record to affirm that a speculative insertionsucceeded, though; it relies on the *absence* on an (internal) "superdeletion" record.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative IGNORE path may be taken(the tuple slot proposed for insertion is skipped without raising anerror).  The implementation loops until either an insert occurs, or aconclusively committed conflicting tuple is determined to exist.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described on the value locking Postgres Wiki page).Users optionally specify a single unique index to take the alternativepath on, which is inferred from a set of user-supplied column names (orexpressions).Speculative (heap) insertions are WAL-logged in two steps:  One recordrelates to an initial intent to insert, while a second minimal recordsimply confirms that that attempt was ultimately successful (i.e. noconflicts where detected when inserting into constraint-relatedindexes).  In this revision, logical decoding does not rely on thepresence of this second record to affirm that a speculative insertionsucceeded, though; it relies on the *absence* on an (internal) "superdeletion" record.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative IGNORE path may be taken(the tuple slot proposed for insertion is skipped without raising anerror).  The implementation loops until either an insert occurs, or aconclusively committed conflicting tuple is determined to exist.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described on the value locking Postgres Wiki page).Users optionally specify a single unique index to take the alternativepath on, which is inferred from a set of user-supplied column names (orexpressions).Speculative (heap) insertions are WAL-logged in two steps:  One recordrelates to an initial intent to insert, while a second minimal recordsimply confirms that that attempt was ultimately successful (i.e. noconflicts where detected when inserting into constraint-relatedindexes).  In this revision, logical decoding does not rely on thepresence of this second record to affirm that a speculative insertionsucceeded, though; it relies on the *absence* on an (internal) "superdeletion" record.

This non-standard INSERT clause allows DML statement authors to specifythat in the event of each of any of the tuples being insertedduplicating an existing tuple in terms of a value or set of valuesconstrained by a unique index, an alternative IGNORE path may be taken(the tuple slot proposed for insertion is skipped without raising anerror).  The implementation loops until either an insert occurs, or aconclusively committed conflicting tuple is determined to exist.This is implemented using a new infrastructure called "speculativeinsertion".  (The approach to "Value locking" presenting here followsdesignpostgres#2, as described on the value locking Postgres Wiki page).Users optionally specify a single unique index to take the alternativepath on, which is inferred from a set of user-supplied column names (orexpressions).Speculative (heap) insertions are WAL-logged in two steps:  One recordrelates to an initial intent to insert, while a second minimal recordsimply confirms that that attempt was ultimately successful (i.e. noconflicts where detected when inserting into constraint-relatedindexes).  In this revision, logical decoding does not rely on thepresence of this second record to affirm that a speculative insertionsucceeded, though; it relies on the *absence* on an (internal) "superdeletion" record.

My previous attempt at doing so, in80abbeb, was not sufficient. While thatfixed the problem for bufmgr.c and lwlock.c , s_lock.c still has non-constantexpressions in the struct initializer, because the file/line/functioninformation comes from the caller of s_lock().Give up on using a macro, and use a static inline instead.Discussion: 4369.1460435533@sss.pgh.pa.us

refresh_by_match_merge() has some issues in the way it builds a SQLquery to construct the "diff" table:1. It doesn't require the selected unique index(es) to be indimmediate.2. It doesn't pay attention to the particular equality semantics enforcedby a given index, but just assumes that they must be those of the columndatatype's default btree opclass.3. It doesn't check that the indexes are btrees.4. It's insufficiently careful to ensure that the parser will pick theintended operator when parsing the query.  (This would have been asecurity bug beforeCVE-2018-1058.)5. It's not careful about indexes on system columns.The way tofix#4 is to make use of the existing code in ri_triggers.cfor generating an arbitrary binary operator clause.  I chose to movethat to ruleutils.c, since that seems a more reasonable place to beexporting such functionality from than ri_triggers.c.While#1,#3, and#5 are just latent given existing feature restrictions,and#2 doesn't arise in the core system for lack of alternate opclasseswith different equality behaviors,#4 seems like an issue worthback-patching.  That's the bulk of the change anyway, so just back-patchthe whole thing to 9.4 where this code was introduced.Discussion:https://postgr.es/m/13836.1521413227@sss.pgh.pa.us

refresh_by_match_merge() has some issues in the way it builds a SQLquery to construct the "diff" table:1. It doesn't require the selected unique index(es) to be indimmediate.2. It doesn't pay attention to the particular equality semantics enforcedby a given index, but just assumes that they must be those of the columndatatype's default btree opclass.3. It doesn't check that the indexes are btrees.4. It's insufficiently careful to ensure that the parser will pick theintended operator when parsing the query.  (This would have been asecurity bug beforeCVE-2018-1058.)5. It's not careful about indexes on system columns.The way tofix#4 is to make use of the existing code in ri_triggers.cfor generating an arbitrary binary operator clause.  I chose to movethat to ruleutils.c, since that seems a more reasonable place to beexporting such functionality from than ri_triggers.c.While#1,#3, and#5 are just latent given existing feature restrictions,and#2 doesn't arise in the core system for lack of alternate opclasseswith different equality behaviors,#4 seems like an issue worthback-patching.  That's the bulk of the change anyway, so just back-patchthe whole thing to 9.4 where this code was introduced.Discussion:https://postgr.es/m/13836.1521413227@sss.pgh.pa.us

refresh_by_match_merge() has some issues in the way it builds a SQLquery to construct the "diff" table:1. It doesn't require the selected unique index(es) to be indimmediate.2. It doesn't pay attention to the particular equality semantics enforcedby a given index, but just assumes that they must be those of the columndatatype's default btree opclass.3. It doesn't check that the indexes are btrees.4. It's insufficiently careful to ensure that the parser will pick theintended operator when parsing the query.  (This would have been asecurity bug beforeCVE-2018-1058.)5. It's not careful about indexes on system columns.The way tofix#4 is to make use of the existing code in ri_triggers.cfor generating an arbitrary binary operator clause.  I chose to movethat to ruleutils.c, since that seems a more reasonable place to beexporting such functionality from than ri_triggers.c.While#1,#3, and#5 are just latent given existing feature restrictions,and#2 doesn't arise in the core system for lack of alternate opclasseswith different equality behaviors,#4 seems like an issue worthback-patching.  That's the bulk of the change anyway, so just back-patchthe whole thing to 9.4 where this code was introduced.Discussion:https://postgr.es/m/13836.1521413227@sss.pgh.pa.us

refresh_by_match_merge() has some issues in the way it builds a SQLquery to construct the "diff" table:1. It doesn't require the selected unique index(es) to be indimmediate.2. It doesn't pay attention to the particular equality semantics enforcedby a given index, but just assumes that they must be those of the columndatatype's default btree opclass.3. It doesn't check that the indexes are btrees.4. It's insufficiently careful to ensure that the parser will pick theintended operator when parsing the query.  (This would have been asecurity bug beforeCVE-2018-1058.)5. It's not careful about indexes on system columns.The way tofix#4 is to make use of the existing code in ri_triggers.cfor generating an arbitrary binary operator clause.  I chose to movethat to ruleutils.c, since that seems a more reasonable place to beexporting such functionality from than ri_triggers.c.While#1,#3, and#5 are just latent given existing feature restrictions,and#2 doesn't arise in the core system for lack of alternate opclasseswith different equality behaviors,#4 seems like an issue worthback-patching.  That's the bulk of the change anyway, so just back-patchthe whole thing to 9.4 where this code was introduced.Discussion:https://postgr.es/m/13836.1521413227@sss.pgh.pa.us

refresh_by_match_merge() has some issues in the way it builds a SQLquery to construct the "diff" table:1. It doesn't require the selected unique index(es) to be indimmediate.2. It doesn't pay attention to the particular equality semantics enforcedby a given index, but just assumes that they must be those of the columndatatype's default btree opclass.3. It doesn't check that the indexes are btrees.4. It's insufficiently careful to ensure that the parser will pick theintended operator when parsing the query.  (This would have been asecurity bug beforeCVE-2018-1058.)5. It's not careful about indexes on system columns.The way tofix#4 is to make use of the existing code in ri_triggers.cfor generating an arbitrary binary operator clause.  I chose to movethat to ruleutils.c, since that seems a more reasonable place to beexporting such functionality from than ri_triggers.c.While#1,#3, and#5 are just latent given existing feature restrictions,and#2 doesn't arise in the core system for lack of alternate opclasseswith different equality behaviors,#4 seems like an issue worthback-patching.  That's the bulk of the change anyway, so just back-patchthe whole thing to 9.4 where this code was introduced.Discussion:https://postgr.es/m/13836.1521413227@sss.pgh.pa.us

Isolates new regression tests from the SortSupport implementation forinet/cidr in#2 into a separate branch based on master. This helps tocheck that the results are the same with or without the new SortSupportroutines.

The original setup for dependencies of partitioned objects hadserious problems:1. It did not verify that a drop cascading to a partition-child objectalso cascaded to at least one of the object's partition parents.  Now,normally a child object would share all its dependencies with one oranother parent (e.g. a child index's opclass dependencies would be sharedwith the parent index), so that this oversight is usually harmless.But if some dependency failed to fit this pattern, the child could bedropped while all its parents remain, creating a logically brokensituation.  (It's easy to construct artificial cases that break it,such as attaching an unrelated extension dependency to the child objectand then dropping the extension.  I'm not sure if any less-artificialcases exist.)2. Management of partition dependencies during ATTACH/DETACH PARTITIONwas complicated and buggy; for example, after detaching a partitiontable it was possible to create cases where a formerly-child indexshould be dropped and was not, because the correct set of dependencieshad not been reconstructed.Less seriously, because multiple partition relationships wererepresented identically in pg_depend, there was an order-of-traversaldependency on which partition parent was cited in error messages.We also had some pre-existing order-of-traversal hazards for errormessages related to internal and extension dependencies.  This iscosmetic to users but causes testing problems.Tofix#1, add a check at the end of the partition tree traversalto ensure that at least one partition parent got deleted.  Tofix#2,establish a new policy that partition dependencies are in addition to,not instead of, a child object's usual dependencies; in this wayATTACH/DETACH PARTITION need not cope with adding or removing theusual dependencies.To fix the cosmetic problem, distinguish between primary and secondarypartition dependency entries in pg_depend, by giving them differentdeptypes.  (They behave identically except for having differentpriorities for being cited in error messages.)  This means that theformer 'I' dependency type is replaced with new 'P' and 'S' types.This also fixes a longstanding bug that after handling an internaldependency by recursing to the owning object, findDependentObjectsdid not verify that the current target was now scheduled for deletion,and did not apply the current recursion level's objflags to it.Perhaps that should be back-patched; but in the back branches itwould only matter if some concurrent transaction had removed theinternal-linkage pg_depend entry before the recursive call found it,or the recursive call somehow failed to find it, both of which seemunlikely.Catversion bump because the contents of pg_depend change forpartitioning relationships.Patch HEAD only.  It's annoying that we're not fixing#2 in v11,but there seems no practical way to do so given that the problemis exactly a poor choice of what entries to put in pg_depend.We can't really fix that while staying compatible with what'sin pg_depend in existing v11 installations.Discussion:https://postgr.es/m/CAH2-Wzkypv1R+teZrr71U23J578NnTBt2X8+Y=Odr4pOdW1rXg@mail.gmail.com

feat(cypher): Implement pattern grammar

Due to how pg_size_pretty(bigint) was implemented, it's possible that whengiven a negative number of bytes that the returning value would not matchthe equivalent positive return value when given the equivalent positivenumber of bytes.  This was due to two separate issues.1. The function used bit shifting to convert the number of bytes intolarger units.  The rounding performed by bit shifting is not the same asdividing.  For example -3 >> 1 = -2, but -3 / 2 = -1.  These twooperations are only equivalent with positive numbers.2. The half_rounded() macro rounded towards positive infinity.  This meantthat negative numbers rounded towards zero and positive numbers roundedaway from zero.Here wefix#1 by dividing the values instead of bit shifting.  Wefix#2by adjusting the half_rounded macro always to round away from zero.Additionally, adjust the pg_size_pretty(numeric) function to be moreexplicit that it's using division rather than bit shifting.  A casualobserver might have believed bit shifting was used due to a staticfunction being named numeric_shift_right.  However, that function wascalculating the divisor from the number of bits and performed division.Here we make that more clear.  This change is just cosmetic and does notaffect the return value of the numeric version of the function.Here we also add a set of regression tests both versions ofpg_size_pretty() which test the values directly before and after thefunction switches to the next unit.This bug was introduced in8a1fab3. Prior to that negative values werealways displayed in bytes.Author: Dean Rasheed, David RowleyDiscussion:https://postgr.es/m/CAEZATCXnNW4HsmZnxhfezR5FuiGgp+mkY4AzcL5eRGO4fuadWg@mail.gmail.comBackpatch-through: 9.6, where the bug was introduced.

Due to how pg_size_pretty(bigint) was implemented, it's possible that whengiven a negative number of bytes that the returning value would not matchthe equivalent positive return value when given the equivalent positivenumber of bytes.  This was due to two separate issues.1. The function used bit shifting to convert the number of bytes intolarger units.  The rounding performed by bit shifting is not the same asdividing.  For example -3 >> 1 = -2, but -3 / 2 = -1.  These twooperations are only equivalent with positive numbers.2. The half_rounded() macro rounded towards positive infinity.  This meantthat negative numbers rounded towards zero and positive numbers roundedaway from zero.Here wefix#1 by dividing the values instead of bit shifting.  Wefix#2by adjusting the half_rounded macro always to round away from zero.Additionally, adjust the pg_size_pretty(numeric) function to be moreexplicit that it's using division rather than bit shifting.  A casualobserver might have believed bit shifting was used due to a staticfunction being named numeric_shift_right.  However, that function wascalculating the divisor from the number of bits and performed division.Here we make that more clear.  This change is just cosmetic and does notaffect the return value of the numeric version of the function.Here we also add a set of regression tests both versions ofpg_size_pretty() which test the values directly before and after thefunction switches to the next unit.This bug was introduced in8a1fab3. Prior to that negative values werealways displayed in bytes.Author: Dean Rasheed, David RowleyDiscussion:https://postgr.es/m/CAEZATCXnNW4HsmZnxhfezR5FuiGgp+mkY4AzcL5eRGO4fuadWg@mail.gmail.comBackpatch-through: 9.6, where the bug was introduced.

Due to how pg_size_pretty(bigint) was implemented, it's possible that whengiven a negative number of bytes that the returning value would not matchthe equivalent positive return value when given the equivalent positivenumber of bytes.  This was due to two separate issues.1. The function used bit shifting to convert the number of bytes intolarger units.  The rounding performed by bit shifting is not the same asdividing.  For example -3 >> 1 = -2, but -3 / 2 = -1.  These twooperations are only equivalent with positive numbers.2. The half_rounded() macro rounded towards positive infinity.  This meantthat negative numbers rounded towards zero and positive numbers roundedaway from zero.Here wefix#1 by dividing the values instead of bit shifting.  Wefix#2by adjusting the half_rounded macro always to round away from zero.Additionally, adjust the pg_size_pretty(numeric) function to be moreexplicit that it's using division rather than bit shifting.  A casualobserver might have believed bit shifting was used due to a staticfunction being named numeric_shift_right.  However, that function wascalculating the divisor from the number of bits and performed division.Here we make that more clear.  This change is just cosmetic and does notaffect the return value of the numeric version of the function.Here we also add a set of regression tests both versions ofpg_size_pretty() which test the values directly before and after thefunction switches to the next unit.This bug was introduced in8a1fab3. Prior to that negative values werealways displayed in bytes.Author: Dean Rasheed, David RowleyDiscussion:https://postgr.es/m/CAEZATCXnNW4HsmZnxhfezR5FuiGgp+mkY4AzcL5eRGO4fuadWg@mail.gmail.comBackpatch-through: 9.6, where the bug was introduced.

Due to how pg_size_pretty(bigint) was implemented, it's possible that whengiven a negative number of bytes that the returning value would not matchthe equivalent positive return value when given the equivalent positivenumber of bytes.  This was due to two separate issues.1. The function used bit shifting to convert the number of bytes intolarger units.  The rounding performed by bit shifting is not the same asdividing.  For example -3 >> 1 = -2, but -3 / 2 = -1.  These twooperations are only equivalent with positive numbers.2. The half_rounded() macro rounded towards positive infinity.  This meantthat negative numbers rounded towards zero and positive numbers roundedaway from zero.Here wefix#1 by dividing the values instead of bit shifting.  Wefix#2by adjusting the half_rounded macro always to round away from zero.Additionally, adjust the pg_size_pretty(numeric) function to be moreexplicit that it's using division rather than bit shifting.  A casualobserver might have believed bit shifting was used due to a staticfunction being named numeric_shift_right.  However, that function wascalculating the divisor from the number of bits and performed division.Here we make that more clear.  This change is just cosmetic and does notaffect the return value of the numeric version of the function.Here we also add a set of regression tests both versions ofpg_size_pretty() which test the values directly before and after thefunction switches to the next unit.This bug was introduced in8a1fab3. Prior to that negative values werealways displayed in bytes.Author: Dean Rasheed, David RowleyDiscussion:https://postgr.es/m/CAEZATCXnNW4HsmZnxhfezR5FuiGgp+mkY4AzcL5eRGO4fuadWg@mail.gmail.comBackpatch-through: 9.6, where the bug was introduced.

Due to how pg_size_pretty(bigint) was implemented, it's possible that whengiven a negative number of bytes that the returning value would not matchthe equivalent positive return value when given the equivalent positivenumber of bytes.  This was due to two separate issues.1. The function used bit shifting to convert the number of bytes intolarger units.  The rounding performed by bit shifting is not the same asdividing.  For example -3 >> 1 = -2, but -3 / 2 = -1.  These twooperations are only equivalent with positive numbers.2. The half_rounded() macro rounded towards positive infinity.  This meantthat negative numbers rounded towards zero and positive numbers roundedaway from zero.Here wefix#1 by dividing the values instead of bit shifting.  Wefix#2by adjusting the half_rounded macro always to round away from zero.Additionally, adjust the pg_size_pretty(numeric) function to be moreexplicit that it's using division rather than bit shifting.  A casualobserver might have believed bit shifting was used due to a staticfunction being named numeric_shift_right.  However, that function wascalculating the divisor from the number of bits and performed division.Here we make that more clear.  This change is just cosmetic and does notaffect the return value of the numeric version of the function.Here we also add a set of regression tests both versions ofpg_size_pretty() which test the values directly before and after thefunction switches to the next unit.This bug was introduced in8a1fab3. Prior to that negative values werealways displayed in bytes.Author: Dean Rasheed, David RowleyDiscussion:https://postgr.es/m/CAEZATCXnNW4HsmZnxhfezR5FuiGgp+mkY4AzcL5eRGO4fuadWg@mail.gmail.comBackpatch-through: 9.6, where the bug was introduced.

Due to how pg_size_pretty(bigint) was implemented, it's possible that whengiven a negative number of bytes that the returning value would not matchthe equivalent positive return value when given the equivalent positivenumber of bytes.  This was due to two separate issues.1. The function used bit shifting to convert the number of bytes intolarger units.  The rounding performed by bit shifting is not the same asdividing.  For example -3 >> 1 = -2, but -3 / 2 = -1.  These twooperations are only equivalent with positive numbers.2. The half_rounded() macro rounded towards positive infinity.  This meantthat negative numbers rounded towards zero and positive numbers roundedaway from zero.Here wefix#1 by dividing the values instead of bit shifting.  Wefix#2by adjusting the half_rounded macro always to round away from zero.Additionally, adjust the pg_size_pretty(numeric) function to be moreexplicit that it's using division rather than bit shifting.  A casualobserver might have believed bit shifting was used due to a staticfunction being named numeric_shift_right.  However, that function wascalculating the divisor from the number of bits and performed division.Here we make that more clear.  This change is just cosmetic and does notaffect the return value of the numeric version of the function.Here we also add a set of regression tests both versions ofpg_size_pretty() which test the values directly before and after thefunction switches to the next unit.This bug was introduced in8a1fab3. Prior to that negative values werealways displayed in bytes.Author: Dean Rasheed, David RowleyDiscussion:https://postgr.es/m/CAEZATCXnNW4HsmZnxhfezR5FuiGgp+mkY4AzcL5eRGO4fuadWg@mail.gmail.comBackpatch-through: 9.6, where the bug was introduced.

Due to how pg_size_pretty(bigint) was implemented, it's possible that whengiven a negative number of bytes that the returning value would not matchthe equivalent positive return value when given the equivalent positivenumber of bytes.  This was due to two separate issues.1. The function used bit shifting to convert the number of bytes intolarger units.  The rounding performed by bit shifting is not the same asdividing.  For example -3 >> 1 = -2, but -3 / 2 = -1.  These twooperations are only equivalent with positive numbers.2. The half_rounded() macro rounded towards positive infinity.  This meantthat negative numbers rounded towards zero and positive numbers roundedaway from zero.Here wefix#1 by dividing the values instead of bit shifting.  Wefix#2by adjusting the half_rounded macro always to round away from zero.Additionally, adjust the pg_size_pretty(numeric) function to be moreexplicit that it's using division rather than bit shifting.  A casualobserver might have believed bit shifting was used due to a staticfunction being named numeric_shift_right.  However, that function wascalculating the divisor from the number of bits and performed division.Here we make that more clear.  This change is just cosmetic and does notaffect the return value of the numeric version of the function.Here we also add a set of regression tests both versions ofpg_size_pretty() which test the values directly before and after thefunction switches to the next unit.This bug was introduced in8a1fab3. Prior to that negative values werealways displayed in bytes.Author: Dean Rasheed, David RowleyDiscussion:https://postgr.es/m/CAEZATCXnNW4HsmZnxhfezR5FuiGgp+mkY4AzcL5eRGO4fuadWg@mail.gmail.comBackpatch-through: 9.6, where the bug was introduced.