Error lastval is not yet defined in this session

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 ...

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

histocrat's user avatar

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

histocrat's user avatar

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}

  • Offline
    Corda
    Newbie

  • *
  • Posts: 2


    Threads: 1
    Joined: Jan 2022

    Reputation:
    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: &quot;Type is not supported&quot;. 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-&gt;format(Array)
#2 /Users/macuser/Sites/postgre-api/system/API/ResponseTrait.php(99): CodeIgniterDebugExceptions-&gt;format(Array)
#3 /Users/macuser/Sites/postgre-api/system/Debug/Exceptions.php(115): CodeIgniterDebugExceptions-&gt;respond(Array,
500)
#4 [internal function
]: CodeIgniterDebugExceptions-&gt;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-&gt;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 $requestResponseInterface $responseLoggerInterface $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.

  • Offline
    Corda
    Newbie

  • *
  • Posts: 2


    Threads: 1
    Joined: Jan 2022

    Reputation:
    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.

Понравилась статья? Поделить с друзьями:
  • Error last breath sans
  • Error language plpgsql does not exist
  • Error language plpgsql already exists
  • Error language not supported
  • Error language german found when expecting spanish