Re: Sequence Access Methods, round two

On Thu, Feb 08, 2024 at 04:06:36PM +0100, Peter Eisentraut wrote:
> On 19.01.24 00:27, Michael Paquier wrote:
>> The reason why this stuff has bumped into my desk is that we have no
>> good solution in-core for globally-distributed transactions for
>> active-active deployments. First, anything we have needs to be
>> plugged into default expressions of attributes like with [1] or [2],
>> or a tweak is to use sequence values that are computed with different
>> increments to avoid value overlaps across nodes. Both of these
>> require application changes, which is meh for a bunch of users.
>
> I don't follow how these require "application changes". I guess it depends
> on where you define the boundary of the "application".

Yep. There's a dependency to that.

> The cited solutions
> require that you specify a different default expression for "id" columns.
> Is that part of the application side? How would your solution work on that
> level? AFAICT, you'd still need to specify the sequence AM when you create
> the sequence or identity column. So you'd need to modify the DDL code in
> any case.

One idea is to rely on a GUC to control what is the default sequence
AM when taking the DefineRelation() path, so as the sequence AM
attached to a sequence is known for any DDL operation that may create
one internally, including generated columns. The patch set does that
with default_sequence_access_method, including support for
pg_dump[all] and pg_restore to give the possibility to one to force a
new default or just dump data without a specific AM (this uses SET
commands in-between the CREATE/ALTER commands).
--
Michael