If have a SERIAL column on my table and insert a value, the column gets automatically populated but if I call SELECT lastval()
to get the value afterwards, even though it’s the same session, I get the error «lastval is not yet defined in this session». This works in Postgres but is an error in CockroachDB. Why is that and how do I fix it?
asked Aug 18, 2022 at 18:44
lastval()
itself works the same in CockroachDB and Postgres—it returns the most recent value generated by nextval()
in the same SQL session, and returns that error if it was never called. The difference is CockroachDB’s default implementation of the SERIAL keyword. Postgres implements this by creating a sequence and implicitly calling nextval on it whenever you insert into the table. CockroachDB instead calls unique_rowid()
, which is more performant but doesn’t populate lastval. You can get compatible behavior by setting the serial_normalization
variable to virtual_sequence
before creating tables with SERIAL columns, and/or modifying existing serial columns to use a virtual sequence.
For example,
CREATE SEQUENCE dummy_seq VIRTUAL;
ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('dummy_seq');
Or you can avoid the extra trip to the database entirely by using a RETURNING clause on your insert.
answered Aug 18, 2022 at 18:44
histocrathistocrat
2,18512 silver badges20 bronze badges
Oops, sorry for not making this as detailed as it should be.
I’m using;
PostgreSql 9.6.1
CI 3.1.6
PHP 7.0.*
I think the problem is CI not being able to cast MySql syntax to PostgreSql as my app allows user install on various database platforms. However the database tables are being created using the Db Forge class, I had to manually check for the database type and use the appropriate syntax as BIGINT, TINYINT and LONGTEXT aren’t available for PostgreSql dbs.
When I checked the apps data store, the tables were created but then no data was inserted (the error popped out after the tables were created), so the error must be associated with data insertion (auto increment).
Here’s a sample code:
$id = $this->db->insert_id();
$data = [
[
'option_name' => 'site_name',
'option_value' => 'test app',
'option_status' => '1'
],
// Other entries
]
if ( $this->db->table_exists( 'table_name' ) {
$this->db->insert_batch( 'options', $data );
return true;
} else {
return false;
}
The functions currval(regclass)
and lastval()
only make sense after nextval()
has been used in the same session (not transaction!). Concurrency control in PostgreSQL is reason behind this behavior.
If you are just curious about the current state of the sequence (not the last serial ID actually used in the session), you can always just call nextval()
:
SELECT nextval(pg_get_serial_sequence('foo', 'id'));
Returns the next serial ID, which had not been used, yet. This carries the flaw of actually consuming the returned ID (which should not matter in a proper relational design). But that side effect can be avoided. In Postgres, serial
and IDENTITY
columns are implemented using a SEQUENCE
internally, which happens to be a table-like object that can be queried like a table. If you are curious about the current state of a SEQUENCE
, just use SELECT
:
SELECT * FROM public.rss_sub_source_id_seq;
(Requires the SELECT
privilege on the sequence, while the above mentioned sequence functions require the USAGE
privilege.)
You need to know the name of the sequence for this, and you already know how to get it: pg_get_serial_sequence('rss_sub_source', 'id')
. It’s easy enough to guess, too. See:
- PostgreSQL next value of the sequences?
For ad-hoc use, just compile the query by hand. Or you can use gexec
in psql:
SELECT 'SELECT last_value - CASE WHEN is_called THEN 0 ELSE 1 END FROM ' || pg_get_serial_sequence('b2', 'big_id')gexec
See:
- Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
For automated (or regular) use, you need dynamic SQL to use the text value returned by the function as SQL identifier. Here is a simple custom function to do that. Also factored in is_called
:
CREATE OR REPLACE FUNCTION sequence_currval(_tbl text, _col text, OUT currval bigint)
LANGUAGE plpgsql AS
$func$
BEGIN
EXECUTE 'SELECT last_value - CASE WHEN is_called THEN 0 ELSE 1 END FROM ' || pg_get_serial_sequence(_tbl, _col)
INTO currval;
END
$func$;
Call:
SELECT sequence_currval('foo', 'id');
This is safe against SQLi because pg_get_serial_sequence()
returns the identifier safely double-quoted as needed.
The function returns the last value that has been consumed from this sequence. (Does not mean it made its way into the table.) The next one will be greater. (Assumes a default sequence with INCREMENT
1!)
It does not consume a serial ID and does not require that nextval()
has been called in the same session.
Consider this demo:
db<>fiddle here
I have Postgresql 9.5
When you try to migrate
Uncaught exception PDOException: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: lastval is not yet defined in this session
Callstack:
#0 /var/www/fuel/core/classes/database/pdo/connection.php(307): PDO->lastInsertId()
#1 /var/www/fuel/core/classes/database/query.php(314): FuelCoreDatabase_PDO_Connection->query(2, ‘INSERT INTO «mi…’, false)
#2 /var/www/fuel/core/classes/migrate.php(350): FuelCoreDatabase_Query->execute(Object(FuelCoreDatabase_PDO_Connection))
#3 /var/www/fuel/core/classes/migrate.php(323): FuelCoreMigrate::write_install(‘default’, ‘app’, ‘032_create_chec…’)
#4 /var/www/fuel/core/classes/migrate.php(144): FuelCoreMigrate::run(Array, ‘default’, ‘app’, ‘up’)
#5 /var/www/fuel/core/classes/migrate.php(168): FuelCoreMigrate::version(NULL, ‘default’, ‘app’, false)
#6 /var/www/fuel/core/tasks/migrate.php(283): FuelCoreMigrate::latest(‘default’, ‘app’, false)
#7 /var/www/fuel/core/tasks/migrate.php(197): FuelTasksMigrate::_run(‘default’, ‘app’)
#8 /var/www/fuel/core/base56.php(37): FuelTasksMigrate->__call(‘_run’, Array)
#9 /var/www/fuel/packages/oil/classes/refine.php(108): call_fuel_func_array(Array, Array)
#10 [internal function]: OilRefine::run(‘\Fuel\Tasks\Mig…’, Array)
#11 /var/www/fuel/packages/oil/classes/command.php(126): call_user_func(‘Oil\Refine::run’, ‘migrate’, Array)
#12 /var/www/oil(68): OilCommand::init(Array)
#13 {main}
-
Corda
Newbie -
-
Posts: 2
Threads: 1
Joined: Jan 2022Reputation:
0
01-28-2022, 04:55 AM
(This post was last modified: 01-28-2022, 04:56 AM by Corda.)
Dear fellow CodeIgniter Developer,
I found an issue with Codeigniter 4 (Version: 4.1.8) Model Insert Function with PostgreSQL.
I tried to search in google and did not find someone who has the same issue, so I post this new topic here.
I usually use MySQL with CodeIgniter 4 and the Model feature is work perfectly.
I must build an app using PostgreSQL and I tried to use the Model feature too with this database. Because the Model feature is perfect to manage the data.
I am using:
PHP 7.4.27
Apache 2.4.48
PostgreSQL 14.1
CodeIgniter 4.1.8
When I tried to insert data using Model, this error always appears:
Code:
<br />
<b>Fatal error</b>: Uncaught CodeIgniterFormatExceptionsFormatException: Failed to parse json string, error: "Type is not supported". in /Users/macuser/Sites/postgre-api/system/Format/JSONFormatter.php: 41
Stack trace:
#0 /Users/macuser/Sites/postgre-api/system/Format/JSONFormatter.php(41): CodeIgniterFormatExceptionsFormatException: :forInvalidJSON('Type is not sup...')
#1 /Users/macuser/Sites/postgre-api/system/API/ResponseTrait.php(341): CodeIgniterFormatJSONFormatter->format(Array)
#2 /Users/macuser/Sites/postgre-api/system/API/ResponseTrait.php(99): CodeIgniterDebugExceptions->format(Array)
#3 /Users/macuser/Sites/postgre-api/system/Debug/Exceptions.php(115): CodeIgniterDebugExceptions->respond(Array,
500)
#4 [internal function
]: CodeIgniterDebugExceptions->exceptionHandler(Object(ErrorException))
#5 {main
}
thrown in <b>/Users/macuser/Sites/postgre-api/system/Format/JSONFormatter.php</b> on line <b>41</b><br />
{
"title": "ErrorException",
"type": "ErrorException",
"code": 500,
"message": "Uncaught CodeIgniter\Format\Exceptions\FormatException: Failed to parse json string, error: "Type is not supported". in /Users/macuser/Sites/postgre-api/system/Format/JSONFormatter.php:41nStack trace:n#0 /Users/macuser/Sites/postgre-api/system/Format/JSONFormatter.php(41): CodeIgniter\Format\Exceptions\FormatException::forInvalidJSON('Type is not sup...')n#1 /Users/macuser/Sites/postgre-api/system/API/ResponseTrait.php(341): CodeIgniter\Format\JSONFormatter->format(Array)n#2 /Users/macuser/Sites/postgre-api/system/API/ResponseTrait.php(99): CodeIgniter\Debug\Exceptions->format(Array)n#3 /Users/macuser/Sites/postgre-api/system/Debug/Exceptions.php(115): CodeIgniter\Debug\Exceptions->respond(Array, 500)n#4 [internal function]: CodeIgniter\Debug\Exceptions->exceptionHandler(Object(ErrorException))n#5 {main}n thrown",
"file": "/Users/macuser/Sites/postgre-api/system/Format/JSONFormatter.php",
"line": 41,
"trace": [
{
"function": "shutdownHandler",
"class": "CodeIgniter\Debug\Exceptions",
"type": "->",
"args": []
}
]
}
The data was inserted, but the application always return this error. So I think this is not good at all.
When I tried to use query builder without a model in Controller, the insert function works well. The data was inserted and the application did not return this error. It always returns an error if using the Model insert function.
I tried to use the Model update and Model delete function and it works well. No error was returned. So I got confused why only the Model insert function returns the error.
After doing some research about the error, I found that an error in
Code:
#1 /Users/macuser/Sites/postgre-api/system/API/ResponseTrait.php(341): CodeIgniterFormatJSONFormatter->format(Array)
I tried to put a var_dump in here:
PHP Code:
if ($mime !== 'application/json') {
// Recursively convert objects into associative arrays
// Conversion not required for JSONFormatter
$data = json_decode(json_encode($data), true);
}
var_dump($data);
return $this->formatter->format($data);
Then I found this error:
Code:
[
"title"
]=>
string(14) "ErrorException"
[
"type"
]=>
string(14) "ErrorException"
[
"code"
]=>
int(500)
[
"message"
]=>
string(76) "pg_query(): Query failed: ERROR: lastval is not yet defined in this session"
[
"file"
]=>
string(77) "/Users/macuser/Sites/postgre-api/system/Database/postgre/Connection.php"
[
"line"
]=>
int(135)
I tried to search in google and I found that the Primary Key must be SERIAL type.
I tried to rebuild the table with serial and enable the auto-increment, but it generates an error because I must put an integer into the insert function for the Primary Key column.
Because of that, I tried to insert with a manual increment for the Primary Key column and it still generates the lastval error. The data was inserted into the database, but it generates lastval error.
So I conclude that this error is not about the SERIAL type in PostgreSQL. Because either SERIAL or VARCHAR type for Primary Key still generate the same error.
Am I doing something wrong here?
Here are my codes:
BaseController:
PHP Code:
<?phpnamespace AppControllers;
use
CodeIgniterController;
use CodeIgniterHTTPCLIRequest;
use CodeIgniterHTTPIncomingRequest;
use CodeIgniterHTTPRequestInterface;
use CodeIgniterHTTPResponseInterface;
use PsrLogLoggerInterface;
use CodeIgniterAPIResponseTrait;/**
* Class BaseController
*
* BaseController provides a convenient place for loading components
* and performing functions that are needed by all your controllers.
* Extend this class in any new controllers:
* class Home extends BaseController
*
* For security be sure to declare any new methods as protected or private.
*/
class BaseController extends Controller
{
use ResponseTrait;
/**
* Instance of the main Request object.
*
* @var CLIRequest|IncomingRequest
*/
protected $request; /**
* An array of helpers to be loaded automatically upon
* class instantiation. These helpers will be available
* to all other controllers that extend BaseController.
*
* @var array
*/
protected $helpers = []; protected $validation = ''; function __construct () {
$this->request = service('request');
$this->validation = ConfigServices::validation();
} /**
* Constructor.
*/
public function initController(RequestInterface $request, ResponseInterface $response, LoggerInterface $logger)
{
// Do Not Edit This Line
parent::initController($request, $response, $logger); // Preload any models, libraries, etc, here. // E.g.: $this->session = ConfigServices::session();
}
}
Home.php Controller:
PHP Code:
<?php
namespace AppControllersArticle;
use
AppControllersBaseController;
use AppModelsArticleArticleModel;
use DateTime;
class
Home extends BaseController {
private $articleModel;
function __construct () {
parent::__construct();
$this->articleModel = new ArticleModel();
} function index () {
var_dump($this->articleModel->getData('data'));
} function detail () {
var_dump($this->articleModel->getData('detailData', ['id' => 'A61f3cb258b852']));
} function insert () {
$objDateTime = new DateTime();
if ($this->articleModel->insertData('insertData', [
'title' => 'Article',
'content' => '<h1>Hello Article</h1>',
'publishDate' => $objDateTime->format('Y-m-d'),
'order' => 0,
'show' => true,
'archive' => false
]) === false) {
return $this->fail($this->articleModel->generateErrorData());
} else {
return $this->respondCreated([], 'Article Insert Success');
}
} function update () {
$objDateTime = new DateTime();
if ($this->articleModel->updateData('updateData', [
'id' => 'A61f3cb258b852',
'title' => 'Article Updated',
'content' => '<h1>Hello Article Updated</h1>',
'publishDate' => $objDateTime->format('Y-m-d'),
'show' => false
]) === false) {
return $this->fail($this->articleModel->generateErrorData());
} else {
return $this->respondUpdated([], 'Article Update Success');
}
} function delete () {
$this->articleModel->deleteData('deleteData', [
'id' => 'A61f3cb258b852'
]);
}
}
?>
BaseModel.php Model:
PHP Code:
<?php
namespace AppModels;
use
CodeIgniterModel;
class
BaseModel extends Model {
protected $useTimestamps = true;
protected $createdField = 'created_at';
protected $updatedField = 'updated_at';
protected $deletedField = 'deleted_at'; protected $skipValidation = false; protected $tableAlias = []; protected $imagePath = '';
protected $uploadImageConfig = [
'imageQuality' => 90,
'imageWidth' => 1024,
'imageHeight' => 0
]; function generateErrorData () {
$errorsData = $this->errors();
$result = []; if (isset($errorsData) && $errorsData !== '' && count($errorsData) > 0) {
foreach ($errorsData as $key => $errorsDataRow):
$keyResult = array_search($key, $this->tableAlias);
if ($keyResult !== false) {
$result[$keyResult] = $errorsDataRow;
}
endforeach;
} return $result;
} function getImagePath () {
return $this->imagePath;
} function getUploadImageConfig () {
return $this->uploadImageConfig;
} function _convertNullToEmptyString ($data) {
if (is_array($data)) {
foreach ($data as $key => $dataRow):
if ($dataRow === null) {
$data[$key] = '';
}
endforeach;
}
return $data;
}
}
ArticleModel.php Model:
PHP Code:
<?php
namespace AppModelsArticle;
use
AppModelsBaseModel;
use DateTime;
class
ArticleModel extends BaseModel {
protected $table = 'article_ms';
protected $primaryKey = 'article_id';
protected $returnType = 'AppEntitiesArticleArticle';
protected $useSoftDeletes = false; protected $allowedFields = [
'article_id',
'article_title',
'article_content',
'article_publish_date',
'article_order',
'article_show',
'article_archive'
]; protected $validationRules = [
'article_id' => [
'label' => 'ID',
'rules' => 'required'
],
'article_title' => [
'label' => 'title',
'rules' => 'required'
],
'article_content' => [
'label' => 'Content',
'rules' => 'required'
],
'article_publish_date' => [
'label' => 'Publish Date',
'rules' => 'required'
]
]; protected $tableAlias = [
'title' => 'article_title',
'content' => 'article_content',
'publishDate' => 'article_publish_date',
'order' => 'article_order',
'show' => 'article_show',
'archive' => 'article_archive'
]; protected $imagePath = './img/article/'; function __construct () {
parent::__construct();
} function getData ($flag = '', $data = [], $convertLabel = '') {
$result = [];
switch ($flag) {
case 'data':
$result = $this->findAll();
break;
case 'detailData':
$result = $this->where('article_id', $data['id'])->first();
break;
}
return $this->convertData($convertLabel, $result);
} function convertData ($flag = '', $data) {
$result = [];
switch ($flag) {
default:
$result = $data;
break;
} return $result;
} function insertData ($flag = '', $data) {
switch ($flag) {
case 'insertData':
return $this->insert(new AppEntitiesArticleArticle([
'article_id' => uniqid('A'),
'article_title' => $data['title'],
'article_content' => $data['content'],
'article_publish_date' => $data['publishDate'],
'article_order' => $data['order'],
'article_show' => $data['show'],
'article_archive' => $data['archive']
]));
break;
}
} function updateData ($flag = '', $data) {
switch ($flag) {
case 'updateData':
return $this->update($data['id'], new AppEntitiesArticleArticle([
'article_title' => $data['title'],
'article_content' => $data['content'],
'article_publish_date' => $data['publishDate'],
'article_show' => $data['show']
]));
break;
}
} function deleteData ($flag = '', $data) {
switch ($flag) {
case 'deleteData':
$this->delete($data['id']);
break;
}
}
}
?>
Article Migration File:
PHP Code:
<?phpnamespace AppDatabaseMigrations;
use
CodeIgniterDatabaseMigration;
class
Article extends Migration
{
public function up()
{
// article_ms
$this->forge->addField([
'article_id' => [
'type' => 'VARCHAR',
'constraint' => 255
],
'article_title' => [
'type' => 'VARCHAR',
'constraint' => 255
],
'article_content' => [
'type' => 'TEXT'
],
'article_publish_date' => [
'type' => 'DATE'
],
'article_order' => [
'type' => 'BIGINT'
],
'article_show' => [
'type' => 'BOOLEAN'
],
'article_archive' => [
'type' => 'BOOLEAN'
],
'created_at' => [
'type' => 'DATETIME',
'null' => true
],
'updated_at' => [
'type' => 'DATETIME',
'null' => true
],
'deleted_at' => [
'type' => 'DATETIME',
'null' => true
]
]);
$this->forge->addKey('article_id', true);
$this->forge->createTable('article_ms');
} public function down()
{
// article_ms
$this->forge->dropTable('article_ms');
}
}
I need help with this issue. Hope this topic will become a good discussion and can solve the issue.
CI 4.1.8
PgSQL 12.2
SERIAL works.
If your table does not use autoincrement then set the protected property useAutoIncrement to false.
-
Corda
Newbie -
-
Posts: 2
Threads: 1
Joined: Jan 2022Reputation:
0
01-29-2022, 05:16 AM
(This post was last modified: 01-29-2022, 05:18 AM by Corda.)
(01-28-2022, 03:01 PM)iRedds Wrote: CI 4.1.8
PgSQL 12.2
SERIAL works.
If your table does not use autoincrement then set the protected property useAutoIncrement to false.
WOW!!!!
You are the BEST!
Thank you so much for useAutoIncrement solutions.
I got it now.
Because I use VARCHAR for the Primary Key, the CodeIgniter automatically runs the useAutoIncrement if I don’t set it as false.
If I use BIGINT or BIGSERIAL for the Primary Key, the useAutoIncrement must be true.
If I use VARCHAR for the Primary Key, the useAutoIncrement must be false.
This works well if I set the useAutoIncrement correctly.
Thank you so much for the solutions. You are the BEST!!!
ORA-08002 means that you don’t have any sequence value cached in your session, so you can’t check the current value (CURRVAL) of the sequence object. Let’s see an example of using a sequence.
SQL> conn sh/sh
Connected.
SQL> create sequence cust_no;
Sequence created.
SQL> select cust_no.currval from dual;
select cust_no.currval from dual
*
ERROR at line 1:
ORA-08002: sequence CUST_NO.CURRVAL is not yet defined in this session
As you can see, the newly created sequence did not correctly respond our request. This is because not only it’s a new sequence, but also no sequence value is cached in this session.
Solution
You have to initialize or make the pointer move by using the pseudocolumn NEXTVAL of the sequence, then your session will cache the sequence value CURRVAL for you.
SQL> select cust_no.nextval from dual;
NEXTVAL
----------
1
SQL> select cust_no.currval from dual;
CURRVAL
----------
1
The problem is solved.
Sometimes, NEXTVAL of a sequence may be abused by someone or applications, therefore, CURRVAL could be out of our expectations. there’re some ways to reset the sequence to the exact number we need.