- edited description
Sequence do not works when using custom schemas in PostgreSQL
Sequences is not using schema declared in Table attribute, and then it raises an exception when inserting new object to database with the message "relation does not exists".
[Entity]
[Table('contact', 'custom_schema')]
[Sequence('contact_id_contact_seq', 1, 1)]
TContact = class(TCustomContact)
public
[AutoGenerated]
[Column('id_contact', [cpRequired, cpPrimaryKey, cpNotNull], 19, 0)]
property Id;
[Column('nome', [cpRequired, cpNotNull], 100)]
property Nome;
[Column('apelido', [], 100)]
property Apelido;
end;
FDataConnection := TMainDatabaseModule.Create(nil);
FConnection := TFireDACConnectionAdapter.Create(FDataConnection.Connection);
FConnection.QueryLanguage := qlPostgreSQL;
FConnection.AutoFreeConnection := False;
FConnection.Connect;
FSession := TSession.Create(FConnection);
FContato := TContato.Create;
FContato.Nome := 'John Doe';
FContato.Apelido := 'JD';
Transaction := FSession.BeginTransaction;
FSession.Save(FContato);
Transaction.Commit;
#!
EPgNativeException with message '[FireDAC][Phys][PG][libpq] ERROR: relation "contact_id_contact_seq" does not exist'.
The workaround is simple, add the schema in the sequence attribute declaration.
[Entity]
[Table('contact', 'custom_schema')]
[Sequence('custom_schema.contact_id_contact_seq', 1, 1)] // workaround here
TContact = class(TCustomContact)
public
It is not a big deal, but since the schema is already declared, it should be used.
It took me awhile to find out why it was not working from beginning.
Comments (9)
-
reporter -
repo owner Is it the case that the sequence has to be in the same schema as the table?
Taking the schema if specified from the table attribute would not only complicate things (looking from one attribute to another one) but also be possibly incorrect.
-
reporter Yes, the sequence is in the same schema as table.
From PostgreSQL docs: If a schema name is given then the sequence is created in the specified schema. Otherwise it is created in the current schema.
That was the case, the sequence was created without specify schema, the table schema is assumed, I think it is a common practice.
Design the database, define the ID as BigSerial and the sequence will be automatically generated by the tool, I cannot see why I would define a different schema only for the sequences.
The custom schema is the same name as the database login role.
CREATE SEQUENCE cad_contato_id_contato_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
I agree with you that it should not get info from another attribute.
Maybe add an optional schema property to Sequence attribute should be enough. What you think about that?
-
repo owner - changed milestone to Future version
- changed component to Persistence
- marked as enhancement
-
repo owner - changed version to 1.2 (develop)
-
repo owner - edited description
- changed status to open
-
repo owner - changed version to 1.2
-
repo owner - changed status to resolved
added schema parameter for SequenceAttribute (fixes
#154)→ <<cset bc5530e40a92>>
-
repo owner - removed milestone
Removing milestone: Future version (automated comment)
- Log in to comment