PostgresSQL / Primary Key / Generator

[mikkoerva]

Using PostgresSQL and when applying changes to server, get this error:

Found with google that PostgresSQL doesn’t use generators, it makes AutoInc values at “behind”.

[DonaldShimoda]

Do you try inserting outside databastract? In pgadmin by example? If don work there will not work on databastract either.

[mikkoerva]

Yes, in pgAdmin can add rows to table and PK get created. I tried PK field with two ways, created as SERIAL and also constrains type set as IDENTITY but with same results.

[mikkoerva]

Only for testing i removed Id field from Schema. Now Delphi app adds record to DB and Id(PK) field gets uniq ID.

But this is not solution, just tested can i add records with Relativity Server.

[mikkoerva]

Got it working. Changed in Schema Id field Data Type to Integer from AutoInc and set Read-Only to true.

[mikkoerva]

Come to my mind that if this solution is no good if you have Master/Detail relationships in schema. Might be that requires AutoInc as field type like in documentation is mentioned?

[antonk]

Hello

This solution is not good because this way Data abstract cannot find out ID of the newly inserted row. This means that Master-Detail relations cannot be processed.

A bit of theory / explanations of decisions made:
Data Abstract supports 2 different kinds of AutoInc fields (depending on the underlying database features set):

• “Native” AutoIncs where values are generated by the database on insert. Then Data Abstract retrieves ID value and uses it in master-detail relations. A good sample of this approach is MS SQL
• Generator-based AutoIncs. Here Data Abstract firs asks database for a new generator value and then uses it explicitly in the INSERT statement.

First approach requires a reliable and safe way to retrieve ID of the just inserted AutoInc row. F.e. for MS SQL we use SCOPE_IDENTITY for this purpose.

At first glance PostgreSQL provides something that looks like a ‘native’ AutoInc support with its

CREATE TABLE table_name(
    id SERIAL
);

Unfortunately there is a catch: under the hood this is actually a generator (sample taken from www.postgresqltutorial.com):

CREATE SEQUENCE table_name_id_seq;

CREATE TABLE table_name (
    id integer NOT NULL DEFAULT nextval('table_name_id_seq')
);

ALTER SEQUENCE table_name_id_seq
OWNED BY table_name.id;

What’s even worse, Data Abstract cannot treat PostgreSQL AutoIncs as ‘native’ ones because there is no way to reliably retrieve last inserted value without using PostgreSQL-specific insert command handling. This would be not so good solution because Data Abstract has to support different databases, so adding specific code for each (some) of them will eventually result in something very error-prone.

So Data Abstract (and so Relativity Server) uses generator-based approach for PostgreSQL.
In other words what you need to do is to define the table + generator (like in the second SQL snippet) and provide this generator name.
Schema allows to provide the generator name in 2 places:

1. When you select a table field you can provide Generator Name there:
   
2. When you select a table statement you can select Generator Name there (note that this time this is a dropdown list so you don’t have to type the name there manually):
   

Regards