phalcon
[BUG]: Postgres currval of sequence is not yet defined in this session
This is something general to php and PDO, what happens is if you set an id manually to a record (on an empty table only I think), it will put out this error if you try and persist a record with "autoincrement".
Create a record with manual id, try and create a new record without id.
ERROR: currval of sequence "ord_id" is not yet defined in this session
I do not know if it's something that the framework is supposed to have some degree of mitigation of this issue. For that reason I am putting this has an issue so I do not forget that this has happened.
references https://github.com/php/php-src/pull/2014 https://github.com/phalcon/cphalcon/issues/1134
one possible fix is to force commands to the database
SELECT NEXTVAL('<column_sequence>'),
NEXTVAL('<column_sequence>');
Hi there. If you want to use PostgreSQL, you should get first familiar with it.
The function i've updated on PHP allow you 2 things:
- the original behaviour of getting the current value BEFORE doing something from the sequence.
- The current value of the latest input on your session, which is translated to "transaction".
If you ever worked with Oracle or PostgreSQL you would be familiar with those 2 things.
Back in 2006-7, whatever, much before, I've created a library named POP (PHP Object Persistence) that addressed those problems on those 2 DBs by adding triggers to ON INSERT. The lib is old as hell but... could be useful to someone who wants to play with those 2 DBs.
https://sourceforge.net/projects/popphp/files/
I've could only release it to the public after few years, so, dates there are 2009 or something, but it's much older than that. I was done as soon as 5.3 was out.
If you still think there is a bug there, go to PostgreSQL and say they are wrong. :-) Good luck.
(BTW: I'm the guy who finally updated PHP to get LastId after a long time of it being available...)
https://sourceforge.net/p/popphp/code/HEAD/tree/drivers/oci.php
https://sourceforge.net/p/popphp/code/HEAD/tree/drivers/pgsql.php
Have fun
@phackwer thanks, I am a bit rusty in Oracle, IMO the DB is the best manager for concurrency issues although it could have a performance hit, it should be an option to have that tradeoff, but again I am just speculating that this might be for either performance or legacy.
The issue I got into was also present when you add a row with the id, so if I insert with id=10, the serial would not update the counter, I only tested with empty databases, if the problem does not happen after the database is populated, then I think that the trigger would be only needed if the database is empty and the serial has not started.
I will take a look and try to adapt has much has possible.
Once again thanks! Stay tune for version 6.
Again: Oracle and PostgreSQL don't do auto increments. You may have sequences out as an isolated item. You may even add triggers ON INSERT to them, but, again, they won't be very helpful. How LastID behaves on PostgreSQL is basically to get you the last inserted one that came from the sequence. That's not on PHP side. This is PostgreSQL. Now, if there is a bug where Postgres has a serial that is not incremented to the value you pushed, it's probably because it is expecting that people reads from the sequence and then do their insert. So, if there are 2 transactions, one reads 31, the other 32, 32 completes first, 31 is still valid, and you can't "rollback" the sequence, as then the next one would try 32 that already exists.
So, those DBs try to handle higher volume of data by NOT caring about sequences. it's a transactional problem, not a DB problem. This is why you can GET the last id, or you can burn one fron the sequence and use it by imposing it, but you can't really expect to get last if without having a transaction.
You should test with real DBs (not production, but big DBs), like in countries with 200 million people and a health system to attend all of them, or revenue system to attend all of them.
Getting last insert id with no transaction in place yet can be handy, but it's not suitable for big problems. Can you imagine a GPS tracking system with 20mi users waiting to get the next available number as the sequence is locked by 20000 concurrent transactions waiting to get the next sequence? :-) This us the moment where NoSQL was born: write it, make it identifiable, index later.
Again: if you having issues with getting the ID for the NEXT transaction, then your code is depending in the wrong premisses. You can only rely on the IDs of current transactions.
PS: BTW, I love Phalcon. :-D I saw this ticket related to the code I've done for PHP 7 and followed. Postgres and Oracle are hard on the PHP devs used to MySQL/Maria and MSSQL. It does take a token from us. I've got used to it over 24 years of PHP, but still wonder why can't it be simpler as it is with MySQL... Only when you get a project with over 2000 I/O per sec you start to understand why.
As I can remember, CURRVAL only works with the sequence name... Which means CURRVAL is basically a SELECT FROM SEQUENCE.
Maybe there could be a try to LASTVAL, then try CURRVAL if there is seq_name, and then... BOOM.
Anyway, the PRs are here: https://github.com/php/php-src/pulls?q=is%3Apr+author%3Aphackwer+merged
And this was 2016... Hope you find the problem there. It's not a very hard to read code and probably there are little changes now.
@phackwer yeah... well I am more used to mysql, it does take that problem out of your hands, like I said, implementation or lack there of may be related to performance or legacy. The issue I ran into wasn't even with big databases, actually it was a clean DB for the phpunit/codeception tests. I inserted one element id value 10, then tried to auto add and it gave me that error. I have to read better on Pgsql inner working with sequences and what you did to figure out what can be done in the framework.
I know that the sequence comes from the DB, has for autoincrement, in mysql you have last inserted id for that reason.
yeah I would expect that in Big databases you would use another system like UUID to avoid such problems, or NoSQL, hopefully I will understand the issue when I get a database that large. I manage to do > 2000K req/s in Mysql with InnoDb, I will try and compare it with pgsql one day.
Thanks for the advice, stay tunned :+1: