Reversing a schema that uses IDENTITY to create an auto incrementing primary key in Postgres appears to not flag the column as autoIncrement="true". So if I create a record, save() it, and try to reload() it throws an error, and if I try to getId() instead of reloading the id Is null.
If I edit the schema.xml and tell it that the column is autoIncrement, then propel uses SELECT nextval('schema.sequence_name') to fetch the id, but this throws a permission error because IDENTITY does not create permissions on the sequence for the user by default because it is not necessary. When fetching the ID by hand in postgres, I simply put RETURNING id at the end of my INSERT statement and this doesn't require any permissions like nextval() would.
Propel could improve its compatibility by supporting GENERATED BY DEFAULT AS IDENTITY (INCREMENT .... ) in Postgres and then fetching primary keys using RETURNING instead of nextval(sequence_name)
Reversing a schema that uses IDENTITY to create an auto incrementing primary key in Postgres appears to not flag the column as
autoIncrement="true". So if I create a record, save() it, and try to reload() it throws an error, and if I try to getId() instead of reloading the id Is null.If I edit the schema.xml and tell it that the column is autoIncrement, then propel uses
SELECT nextval('schema.sequence_name')to fetch the id, but this throws a permission error because IDENTITY does not create permissions on the sequence for the user by default because it is not necessary. When fetching the ID by hand in postgres, I simply putRETURNING idat the end of my INSERT statement and this doesn't require any permissions likenextval()would.Propel could improve its compatibility by supporting
GENERATED BY DEFAULT AS IDENTITY (INCREMENT .... )in Postgres and then fetching primary keys usingRETURNINGinstead ofnextval(sequence_name)