Method string Translate(SqlCompilerContext context, SequenceDescriptor descriptor, SequenceDescriptorSection section) is not overridden in postgresql adapter. This leads to wrong ddl create table generation when calling SqlDdl.Create(...). Instead of creating PG SERIAL field type it creates standart MSSQL sql code for sequence: " ... bigint START WITH 1 INCREMENT BY 1 ...". This problem can be solved by using custom Xtensive.Sql.SqlValueType but this breaks encapsulation, because in this case I have to support PG database in my project codebase.
asked Aug 18 '16 at 02:00
Thank you for the bug report.
PostgreSQL provider wasn't designed to handle sequences as MS SQL Server provider does. PostgreSQL provider doesn't expect that sequence will be owned by a table column. It handles sequence as a database sequence, so such case when you creates SequenceDescriptor instance and set in as a sequence for table column won't work. We should have thrown an exception for such cases, and we will. It is not so hard to fix so, I think, we can fix it in current developing version. But we won't implement the same behavior as we did for MS SQL Server. Here below I will try to explain why we won't.
There is a difference between this two RDBMSs.
Auto-increment column in MS SQL Server is a combination of column of numeric type and sort of sequence. I'd say this is an internal sequence for a table, and each table can have only one such sequence. Declaration of this sequence has its own syntax and it's quite simple.
It makes compilation of such query very easy and compiler compile a SqlExprssion tree on one traverse.
In PostgreSQL there are two ways to create an auto-increment column - using special type SERIAL and declaring sequence as source of next values.
SERIAL type is just a type and it has no settings of value generation. Personally, I wouldn't say SERIAL type could be a result of SequenceDescriptor translation.
Another way is pretty much the same as in MS SQL Server, but with one important difference - the sequence which is used for value generation is database sequence and must be created before its first use in CREATE TABLE statement.
This difference makes it impossible to compile SqlExpression tree in one traverse. If we implemented such behavior we would have to break current query compilation, create a sequence, compile its creation to SQL statement and then start compilation of CREATE TABLE statement one more time. This option is not acceptable, moreover you have all you need to create auto-increment column for PostgreSQL but a bit differently.
answered Aug 22 '16 at 10:22
One more problem for this topic: System.NotSupportedException: Function 'LastAutoGeneratedId' is not supported. в Xtensive.Sql.Compiler.SqlTranslator.Translate(SqlFunctionType type) в Xtensive.Sql.Drivers.PostgreSql.v8_0.Translator.Translate(SqlFunctionType type)
answered Aug 29 '16 at 03:11