DMS migration of oracle sequences to postgres - potential pitfall with cache

[hanspetter]

We note that when sequences are migrated from oracle, the sequences on postgres are declared with a cache parameter of 20, which is the default in oracle.

There is a significant difference in how caching is done in oracle and postgres, in that for oracle, the caching is done on the database server, so that there are no issues with ordering of sequences between concurrent sessions to the database. However, in postgres, the sequences are cached on the client-side (next x numbers in the sequence are reserved for a specific session).

In our case, this introduced rather severe errors in the application data, because parts of our business logic required that the newest version of an object had the highest version number. When the same object was updated in multiple sessions, the ordering became wrong because of the caching of sequences in the sessions.

I would strongly suggest that for schema migration, the caching default for sequences in postgres should always be 1.

[jaydubu]

Hi @hanspetter,

I understand that you have clarifications or issues with the CREATE SEQUENCE default cache value in Oracle and PostgreSQL. You may refer to the official Oracle and PostgreSQL documentations below to better understand the differences. Please note to utilize the links with caution since this is not maintained by Google and could be inaccurate or outdated:

CACHE key differences:

• Oracle: while the minimum value for this parameter is 2, it is true that the default cache value is 20 if you omit both CACHE and NOCACHE
• PostgreSQL: this is an OPTIONAL clause. The minimum value is 1. If not specified, the default value is also 1. Sample code from @Littlefoot:

  create sequence my_seq​

If you think otherwise, you can submit a feature request to have it default to “1”. While I can’t provide a timeline for when this enhancement might be available, I recommend keeping an eye on the issue tracker and checking the release notes for the latest updates.

I hope the above information is helpful.

[hanspetter]

I will clarify the problem:

If you create a sequence in Oracle with 

CREATE SEQUENCE MYSEQUENCE;

It will be created with the default cache size of 20, which is a sensible default in Oracle.

In DMS schema migration , the sequence will be created like

CREATE SEQUENCE MYSEQUENCE CACHE 20;

which is not a sensible default in postgres if you have concurrent usage of the sequence values and is dependent upon correct ordering.