update sequence when inserting record with fixed id?

[ghost]

Created by Dieter Plaetinck, 15th Apr 2010. (originally Lighthouse ticket #584):

---

when using sequences on a database that supports it (eg postgresql) (or serial columnt type).
if you:

• add a new record with a specific id, eg '1' (such as is generally recommended for fixtures, see http://book.cakephp.org/view/1201/Preparing-test-data). the record gets inserted (assuming it validates and does not violate constraints) but the sequence nextval stays the same. by default nextval is 1 (also Cake's test suite does ALTER SEQUENCE .. RESTART WITH 1 queries)
• want to insert a new record that has no id specified (this is what you usually do when doing $Model->save()), the primary key will we duplicate and violate the unique constraint, because it uses the nextval (which is 1)

I'm not sure what the recommended approach should be.

• discourage specific 'id' setting, even when running tests? this makes things a bit harder, but not problematic.
• automatically update the sequence nextval when 'id' is specified? maybe as an optional setting for models?
• make it the applications job to enhance the model (maybe with a behavior) to update the sequence?

[ghost]

25th Nov 2010, porkaria said:

---

ow Juan da uma força aí hahaha =[

[ghost]

26th Nov 2010, Jose Lorenzo Rodríguez said:

---

Please do not assign ticket to anyone in particular

[ghost]

24th Jun 2011, Mark Story said:

---

I don't think we'll be able to address sequences or modify all of the testsuite to not use specific id's as that makes defining joins in the fixtures almost impossible. Moving to Future, as its a hardish problem, that could rely on api changes.

[ghost]

7th Oct 2011, Brad Koch said:

---

I'm running into this issue in the test case scenario Dieter mentioned. The best workaround I've found is to make the IDs of any explicitly declared records sufficiently high enough so that you don't have collisions, thus allowing you to be certain of what the IDs are without causing errors. Dealing with out of sync sequences is just difficult in general..

[ghost]

7th Oct 2011, Jose Lorenzo Rodríguez said:

---

Another workaround it to not declare the id value in the fixture records, that way they will always start with 1. We could use a patch and a testcase if you feel like you're able to finally kill this bug :)

I guess I could try to do it too later after 2.0 is out.

[ghost]

7th Oct 2011, Brad Koch said:

---

Well, there's a couple questions to answer before deciding to write a patch.

It's the postgres autoincrementation methodology that has the flaw, not cake. The issue obviously occurs when id values are inserted that exceed the sequence value. The only framework level fixes are to either:
a) Take responsibility for autoincrementation away from Postgres and give it to cake (which breaks autoincrementation for other apps inserting into the database)
b) Babysit the sequence value by making sure that it has not fallen behind before executing every save/update/query call.

The question that needs to be answered first is whether it's the framework's role to take on this responsibility. If the framework does pick up this responsibility, we'd be adding a bit of overhead to every query. If not, it's just something Postgres users need to be aware of and work around by not ever explicitly declaring ids.

[ghost]

7th Oct 2011, Jose Lorenzo Rodríguez said:

---

Yeah, you are right. The framework is just sticking to what postgres expects
to happen. If you use the postgres console and insert a new id by hand, then
you risk the sequence to be out of sync. Perhaps provide a good trigger for
auto setting the sequence id, and not make cake responsible for this?

It should not be hard to write such trigger, and could be offered as part of
the documentation for using cake with postgres.

[ghost]

25th Oct 2011, Brad Koch said:

---

FYI, I'm working on a trigger as my time allows. Just need to work out a couple issues and run it by some other postgres people first. I'll post it when I've got something to share.