Sequence do not works when using custom schemas in PostgreSQL

[Cesar Romero]

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.

[Stefan Glienke]

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.

[Cesar Romero]

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?

[Stefan Glienke]

added schema parameter for SequenceAttribute (fixes #154)

→ <<cset bc5530e40a92>>

[Stefan Glienke]

Removing milestone: Future version (automated comment)