Postgresql sequence naming convention

[Matt Hauck]

I'm on squeryl 0.9.5-6, and I'm seeing squeryl use the wrong sequence name for my `SERIAL` primary key column's next value. i.e., it's using nextval('"s_users_id"') instead of the postgresql standard: nextval('"users_id_seq"') (same issue as here: http://stackoverflow.com/questions/12794427/squeryl-and-postgresqls-autoincrement) 

I'm not sure if the answer on that stackoverflow page is the official answer or not. I'm surprised that postgresql does not have support for such a basic thing in squeryl. I'm also a bit surprised, because looking at org.squeryl.adapters.PostgresqlAdapater, I see a `sequenceName` method that uses "seq_" as the prefix. Even though this is still not quite right, it is still not the same as "s_" which is what I'm seeing. 

Can anybody explain this?

[David Whittaker]

Hi Matt,

On Fri, Sep 13, 2013 at 2:08 PM, Matt Hauck <matt...@gmail.com> wrote:

I'm on squeryl 0.9.5-6, and I'm seeing squeryl use the wrong sequence name for my `SERIAL` primary key column's next value. i.e., it's using nextval('"s_users_id"') instead of the postgresql standard: nextval('"users_id_seq"') (same issue as here: http://stackoverflow.com/questions/12794427/squeryl-and-postgresqls-autoincrement) 

If you allow Squeryl to generate DDL for you, you won't need to manually specify your sequence names.  If you don't, then you'll need to either indicate them in the way the SO answer indicates, or use a custom PostgreSQLAdapter.  If you opt for the latter, you can find more information by searching the list.

 

I'm not sure if the answer on that stackoverflow page is the official answer or not. I'm surprised that postgresql does not have support for such a basic thing in squeryl.

There is full support for it.  You can specify a sequence name for each auto incremented column, or you can set a default naming convention through your own adapter.  It's not support that is missing, it's just a difference in convention between Squeryl and PostgreSQL.  I'm a pretty heavy Squeryl/Postgres user and I've never felt inconvenienced by this.  It has come up on the list more than once though... maybe it's time to add some Postgres specific documentation.

 

I'm also a bit surprised, because looking at org.squeryl.adapters.PostgresqlAdapater, I see a `sequenceName` method that uses "seq_" as the prefix. Even though this is still not quite right, it is still not the same as "s_" which is what I'm seeing.

I'm not sure what the sequenceName method is for, but the generated names come from https://github.com/max-l/Squeryl/blob/master/src/main/scala/org/squeryl/internals/DatabaseAdapter.scala#L497 

 

Can anybody explain this?

--
You received this message because you are subscribed to the Google Groups "Squeryl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to squeryl+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Vladimir Nicolici]

As I asked on another thread discussing this issue, why is Squeryl even trying to guess the sequence name? It should just skip that column when inserting and let the DB handle it.

 

The fact there is an actual sequence involved here is a PostgreSQL implementation detail. How is this handled for other databases, like MySQL, that do not use a normal sequence for auto-increment columns?

 

Some users not familiar with PostgreSQL implementation details may not even know a normal sequence is used for auto-incremented columns, and will have an even harder time finding the solution.

 

I prefer to create my database schema manually, and having to specify the sequence name manually for each table is annoying. Especially as a new user, since I have to lose a few hours first digging for the solution.

 

This looks like a bug, with an workaround, not something with "full support".

 

Sincerely,

Vlad

[Maxime Lévesque]

> The fact there is an actual sequence involved here is a PostgreSQL
> implementation detail. How is this handled for other databases, like MySQL,
> that do not use a normal sequence for auto-increment columns?

All supported DBs fall in only two categories : those that support
auto incremented fields, and those
that use sequence, the strategy is driven by the property :

https://github.com/max-l/Squeryl/blob/master/src/main/scala/org/squeryl/internals/DatabaseAdapter.scala#L279