8000 [HttpFoundation] Use UPSERT for sessions stored in PgSql >= 9.5 by nicolas-grekas · Pull Request #19048 · symfony/symfony · GitHub
[go: up one dir, main page]
Skip to content

[HttpFoundation] Use UPSERT for sessions stored in PgSql >= 9.5 #19048

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 1 commit into from
Jun 14, 2016
Merged

[HttpFoundation] Use UPSERT for sessions stored in PgSql >= 9.5 #19048

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
8000
Diff view
13 changes: 8 additions & 5 deletions src/Symfony/Bridge/Doctrine/HttpFoundation/DbalSessionHandler.php 8000
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -180,7 +180,7 @@ public function write($sessionId, $data)
$updateStmt->bindValue(':time', time(), \PDO::PARAM_INT);
$updateStmt->execute();

// When MERGE is not supported, like in Postgres, we have to use this approach that can result in
// When MERGE is not supported, like in Postgres < 9.5, we have to use this approach that can result in
// duplicate key errors when the same session is written simultaneously. We can just catch such an
// error and re-execute the update. This is similar to a serializable transaction with retry logic
// on serialization failures but without the overhead and without possible false positives due to
Expand Down Expand Up @@ -224,11 +224,11 @@ private function getMergeSql()
{
$platform = $this->con->getDatabasePlatform()->getName();

switch ($platform) {
case 'mysql':
switch (true) {
Copy link
Member Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

bonus fix: https://github.com/symfony/symfony/pull/19048/files#diff-e6a5cc5d66fa241233b3314fcd49f228R236 is dead code today

case 'mysql' === $platform:
return "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time) ".
"ON DUPLICATE KEY UPDATE $this->dataCol = VALUES($this->dataCol), $this->timeCol = VALUES($this->timeCol)";
case 'oracle':
case 'oracle' === $platform:
// DUAL is Oracle specific dummy table
return "MERGE INTO $this->table USING DUAL ON ($this->idCol = :id) ".
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time) ".
Expand All @@ -239,8 +239,11 @@ private function getMergeSql()
return "MERGE INTO $this->table WITH (HOLDLOCK) USING (SELECT 1 AS dummy) AS src ON ($this->idCol = :id) ".
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time) ".
"WHEN MATCHED THEN UPDATE SET $this->dataCol = :data, $this->timeCol = :time;";
case 'sqlite':
case 'sqlite' === $platform:
return "INSERT OR REPLACE INTO $this->table ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time)";
case 'postgresql' === $platform && version_compare($this->con->getServerVersion(), '9.5', '>='):
return "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->timeCol) VALUES (:id, :data, :time) ".
"ON CONFLICT ($this->idCol) DO UPDATE SET ($this->dataCol, $this->timeCol) = (:data, :time) WHERE $this->idCol = :id";
}
}
}
13 changes: 8 additions & 5 deletions src/Symfony/Component/HttpFoundation/Session/Storage/Handler/PdoSessionHandler.php
View file
Open in desktop
< 615A td id="diff-fb1dcfa62129a037c11aebcf43264282a5c8e2da5d9409aa8f4f3281659b3dd1R674" data-line-number="674" class="blob-num blob-num-context js-linkable-line-number js-blob-rnum">
Original file line number Diff line number Diff line change
Expand Up @@ -347,7 +347,7 @@ p 8000 ublic function write($sessionId, $data)
$updateStmt->bindValue(':time', time(), \PDO::PARAM_INT);
$updateStmt->execute();

// When MERGE is not supported, like in Postgres, we have to use this approach that can result in
// When MERGE is not supported, like in Postgres < 9.5, we have to use this approach that can result in
// duplicate key errors when the same session is written simultaneously (given the LOCK_NONE behavior).
// We can just catch such an error and re-execute the update. This is similar to a serializable
// transaction with retry logic on serialization failures but without the overhead and without possible
Expand Down Expand Up @@ -659,11 +659,11 @@ private function getSelectSql()
*/
private function getMergeSql()
{
switch ($this->driver) {
case 'mysql':
switch (true) {
Copy link
Member Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

https://github.com/symfony/symfony/pull/19048/files#diff-5b96aee9c7c07a0e18d1fd32f7a3028eR671 dead code today

case 'mysql' === $this->driver:
return "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
"ON DUPLICATE KEY UPDATE $this->dataCol = VALUES($this->dataCol), $this->lifetimeCol = VALUES($this->lifetimeCol), $this->timeCol = VALUES($this->timeCol)";
case 'oci':
case 'oci' === $this->driver:
// DUAL is Oracle specific dummy table
return "MERGE INTO $this->table USING DUAL ON ($this->idCol = :id) ".
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
Expand All @@ -674,8 +674,11 @@ private function getMergeSql()
return "MERGE INTO $this->table WITH (HOLDLOCK) USING (SELECT 1 AS dummy) AS src ON ($this->idCol = :id) ".
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
"WHEN MATCHED THEN UPDATE SET $this->dataCol = :data, $this->lifetimeCol = :lifetime, $this->timeCol = :time;";
case 'sqlite':
case 'sqlite' === $this->driver:
return "INSERT OR REPLACE INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time)";
case 'pgsql' === $this->driver && version_compare($this->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '9.5', '>='):
return "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
"ON CONFLICT ($this->idCol) DO UPDATE SET ($this->dataCol, $this->lifetimeCol, $this->timeCol) = (:data, :lifetime, :time) WHERE $this->idCol = :id";
}
}

Expand Down
0