Database_PDO_Connection Bulk Insert(PostgreSql9.6.1) [ERROR: current transaction is aborted, commands ignored until end of transaction block]

[Himakuma]

No description provided.

[emlynwest]

I think we're going to need a little bit more info before we can help.

[Himakuma]

Sorry, that is a submit mistake.

Bulk Insert Loop(1000×２回)
I want to skip 「$this->_connection->lastInsertId()」 or fixed value set when bulk insert.

■postgresql-Thu.log
ERROR: lastval is not yet defined in this session
statement: SELECT LASTVAL()
ERROR: current transaction is aborted, commands ignored until end of transaction block
statement: DEALLOCATE pdo_stmt_00000002

	/**
	 * Query the database
	 *
	 * @param integer $type
	 * @param string  $sql
	 * @param mixed   $as_object
	 *
	 * @return mixed
	 *
	 * @throws \Database_Exception
	 */
	public function query($type, $sql, $as_object)
	{
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
=============================================================================

	
		// Set the last query
		$this->last_query = $sql;

		if ($type === \DB::SELECT)
		{
			// Convert the result into an array, as PDOStatement::rowCount is not reliable
			if ($as_object === false)
			{
				$result = $result->fetchAll(\PDO::FETCH_ASSOC);
			}
			elseif (is_string($as_object))
			{
				$result = $result->fetchAll(\PDO::FETCH_CLASS, $as_object);
			}
			else
			{
				$result = $result->fetchAll(\PDO::FETCH_CLASS, 'stdClass');
			}

			// Return an iterator of results
			return new \Database_Result_Cached($result, $sql, $as_object);
		}
		elseif ($type === \DB::INSERT)
		{
			// Return a list of insert id and rows created
			return array(
				$this->_connection->lastInsertId(),         ← 「SELECT LASTVAL()」
				$result->rowCount(),
			);
		}
		elseif ($type === \DB::UPDATE or $type === \DB::DELETE)
		{
			// Return the number of rows affected
			return $result->errorCode() === '00000' ? $result->rowCount() : -1;
		}

		return $result->errorCode() === '00000' ? true : false;
	}

[Himakuma]

It's not real, but the sample code
※This image

$tableName = 'HogeTable';
$columns = array('col1', 'col2'.....);
$recodes = array(......);

$builder = DB::insert($tableName)->columns(columns);

$limitCount = 0;
foreach ($recodes as $recode) {

	// Set value in column
	$hogeCrud = Model_Hoge::forge(); // Model_crud Class
	$hogeCrud->col1 = $recode['col1'];
	$hogeCrud->col2 = $recode['col2'];
...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	// Set array in builder
	$builder->values($recodeData->to_array());

	// Execute every 1000
	$limitCount++;
	if ($limitCount === 1000) {
		// ↓↓↓ I get an error the second time
		$builder->execute();
		$limitCount = 0;
		$builder->reset();
		$builder->table($tableName);
		$builder->columns($columns);
	}
	
	
	
	
	
}

// Last execute
if (0 < $limitCount) {
	$builder->execute();
}

[Himakuma]

Sorry,
FuelPHP1.8

[WanWizard]

Not entirely sure what you are trying to do here. Postgres says

The reason you get this error is because you have entered a transaction and one of your SQL Queries failed, and you gobbled up that failure and ignored it. But that wasn't enough, THEN you used that same connection, using the SAME TRANSACTION to run another query. The exception gets thrown on the second, correctly formed query because you are using a broken transaction to do additional work. Postgresql by default stops you from doing this.

Instead of using

$builder->reset();
$builder->table($tableName);
$builder->columns($columns);

in your loop, do not re-use the same object, but create a new one. In other words, do this to initialize the object in your loop:

$builder = DB::insert($tableName)->columns(columns);

[Himakuma]

I want to do Bulk Insert.

Is CREATE SEQUENCE mandatory for INSERT?
"LastInsertId ()" will result in an error if there is no SEQUENCE.

Not bug?

[WanWizard]

I understand that, my point was that you're doing multiple inserts using the same object instead of destroying the object and creating a new one.

LastInsertId() will return false if there is no sequence.

[Himakuma]

OS:Windows7
Server:Apache/2.4.23
DB:PostgreSQL/9.6.1
PHP:7.1.1
FuelPHP:1.8

PostgreSQL 9.6 Manuals
「https://www.postgresql.org/docs/9.6/static/functions-sequence.html」

lastval
Return the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it refers to whichever sequence nextval was most recently applied to in the current session. It is an error to call lastval if nextval has not yet been called in the current session.

"LastInsertId ()" executes "SELECT LASTVAL ()".
CREATE SEQUENCE is mandatory for nextval.

Does FuelPHP 's PDO support PostgreSQL?
Which databases does FuelPHP PDO support?
Mysql only?

[Himakuma]

Should I make a dummy sequence to avoid errors?

[WanWizard]

Originally MySQL only. In 1.8, the database driver layer has been reworked so that specific drivers can be created. It is also possible now to create platform specific schemas (for DBUtil).

I started with Sqlite, and I have DBlib (Sybase) and SqlSrv drivers in development. Someone in the forum made Informix drivers and promissed to contribute them, but never did. And for some reason or other, nobody seems to use PostgreSQL enough to help with driver development. I can try to find some time next week to have a look at it.

[Himakuma]

Thank you.
Please.

If there is an extensible method with $ result as argument, I think that it can be used for other DB.
It is my imagination of INSERT.
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

/**
 * Query the database
 *
 * @param integer $type
 * @param string  $sql
 * @param mixed   $as_object
 *
 * @return mixed
 *
 * @throws \Database_Exception
 */
public function query($type, $sql, $as_object)
{

==========================================================================
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	// Set the last query
	$this->last_query = $sql;

	if ($type === \DB::SELECT)
	{
		// Convert the result into an array, as PDOStatement::rowCount is not reliable
		if ($as_object === false)
		{
			$result = $result->fetchAll(\PDO::FETCH_ASSOC);
		}
		elseif (is_string($as_object))
		{
			$result = $result->fetchAll(\PDO::FETCH_CLASS, $as_object);
		}
		else
		{
			$result = $result->fetchAll(\PDO::FETCH_CLASS, 'stdClass');
		}

		// Return an iterator of results
		return new \Database_Result_Cached($result, $sql, $as_object);
	}
	elseif ($type === \DB::INSERT)
	{
		// Return a list of insert id and rows created
		/** OLD!!!!
		return array(
			$this->_connection->lastInsertId(),
			$result->rowCount(),
		);
		*/

		/** NEW!!! */
		return $this->insert_result($result);
	}
	elseif ($type === \DB::UPDATE or $type === \DB::DELETE)
	{
		// Return the number of rows affected
		return $result->errorCode() === '00000' ? $result->rowCount() : -1;
	}

	return $result->errorCode() === '00000' ? true : false;
}

/** NEW method */
protected function insert_result($result) {
	return array(
		$this->_connection->lastInsertId(),
		$result->rowCount(),
	);
}