PostgreSqlCombProvider not sequential in Postgresql

[Winches]

When using RT.Comb.Provider.PostgreSql , the generated Guids are not in sorted order in Postgresql 13.3.

            using var context = new TDbContext();
            for (int i = 0; i < 20; i++)
            {
                var entity = new CombGuidEntity
                {
                    Id = Provider.PostgreSql.Create(),
                    Sequence = i + 1
                };
                context.Add(entity);
            }
            context.SaveChanges();

id(uuid)	sequence(int4)
017c5af4-2617-4ae0-98f0-98086eef50ca	1
017c5af4-2b83-49b1-b643-24e6f3070551	18
017c5af4-2b81-49fb-88f8-25640c96d998	17
017c5af4-2b7f-42f0-919d-99f6098c42b3	16
017c5af4-2b7d-4606-b2d7-d9b2f45efce7	15
017c5af4-2b7b-4ad0-8357-93fc27d8b942	14
017c5af4-2b79-4fde-87a6-88a8e50eab56	13
017c5af4-2b77-4cd0-8832-d707c1b564ae	12
017c5af4-2b75-455f-8652-308d00a4fd7f	11
017c5af4-2b73-4542-aa93-c9bbeacc22dc	10
017c5af4-2b71-44a5-8583-486a37343a78	9
017c5af4-2b6f-4f27-869a-331cff9848d0	8
017c5af4-2b6d-400a-9b38-48fe422ac145	7
017c5af4-2b6b-4b1a-8d64-67bb34f24a04	6
017c5af4-2b69-4cb6-af43-738d0539f9ff	5
017c5af4-2b67-4a99-9111-2ba09802c266	4
017c5af4-2b65-44d4-9c4c-1a62d65f3ccf	3
017c5af4-2b63-42f9-995e-d7850262de6c	2
017c5af4-2b85-4b71-8803-33a133404423	19
017c5af4-2b87-4780-b18d-00772d031e97	20

[richardtallent]

Hi!

The COMB values above are in the correct order (i.e., the value of the GUIDs follows the same order as the value of the IDs). You can see this in the first 6 hex bytes of each GUID. That's the same order PostgreSql will use when you ORDER BY [id].

I'm guessing what you're showing above is the order in which the records were inserted into the database, i.e., by selecting from the table in an unordered fashion (without ORDER BY and without first issuing a CLUSTER command on the table).

The insert order is determined by your database code. It appears you're using Entity Framework? I don't use EF, but my understanding is that DbContext.SaveChanges() does not guarantee that records are saved in the same order they are added to the list being saved.

Instead (again, based on my limited understanding), EF attempts to create a dependency graph of the saved objects so it can save them in an order that guarantees referential integrity.

The problem is, the creation of that dependency graph, even if it finds no dependencies, does not preserve the original save order. In fact, in this case, it nearly reverses them, which is what you might expect if EF is using a tree structure of some sort and then using Preorder or similar traversal to save them.

I don't think there's a way to override EF's insertion order. Since PostgreSql doesn't support clustered indexes (or didn't the last time I used it), that means you will need to ORDER BY [id] to retrieve the records in the intended order.

If you need your PostgreSql table to have its records stored in ID order (whether you're using a UUID/COMB or not), you'll need to bypass EF and issue the insert commands more directly through Npgsql. But even then, updates to those records later will not necessarily preserve that order.

Hope that helps!

[Winches]

Hi!

The COMB values above are in the correct order (i.e., the value of the GUIDs follows the same order as the value of the IDs). You can see this in the first 6 hex bytes of each GUID. That's the same order PostgreSql will use when you ORDER BY [id].

I'm guessing what you're showing above is the order in which the records were inserted into the database, i.e., by selecting from the table in an unordered fashion (without ORDER BY and without first issuing a CLUSTER command on the table).

The insert order is determined by your database code. It appears you're using Entity Framework? I don't use EF, but my understanding is that DbContext.SaveChanges() does not guarantee that records are saved in the same order they are added to the list being saved.

Instead (again, based on my limited understanding), EF attempts to create a dependency graph of the saved objects so it can save them in an order that guarantees referential integrity.

The problem is, the creation of that dependency graph, even if it finds no dependencies, does not preserve the original save order. In fact, in this case, it nearly reverses them, which is what you might expect if EF is using a tree structure of some sort and then using Preorder or similar traversal to save them.

I don't think there's a way to override EF's insertion order. Since PostgreSql doesn't support clustered indexes (or didn't the last time I used it), that means you will need to ORDER BY [id] to retrieve the records in the intended order.

If you need your PostgreSql table to have its records stored in ID order (whether you're using a UUID/COMB or not), you'll need to bypass EF and issue the insert commands more directly through Npgsql. But even then, updates to those records later will not necessarily preserve that order.

Hope that helps!

Thanks for helpful reply. Your guessing is right.I'm inserting records with Entity Framework Core for sequential Guid test.The problem is as you said the insert order of entity is different with original order.