Fork me on GitHub
Subscribe 3

Ticket #384 (fixed bug)

ReplaceQueries don't work properly with PostgreSQL

  • Created: 2011-03-31 14:59:59
  • Reported by: Reines
  • Assigned to: Franz
  • Milestone: 2.0-alpha1
  • Component: database
  • Priority: normal

PostgreSQL doesn't have an equivalent of REPLACE INTO. At the moment the query we have will only perform an insert if there isn't a matching row - but this isn't quite the same as it doesn't update any existing rows.

The consensus seems to be we need to use either multiple queries or a PL/pgSQL function - either way it is going to be messy. … Constructs


Reines 2011-03-31 15:01:42

  • Summary changed from ReplaceQueries don't work right with PostgreSQL to ReplaceQueries don't work properly with PostgreSQL.

Franz 2011-10-31 15:48:35

  • Owner set to Franz.

Franz 2011-11-03 14:35:17

I found this answer on StackOverflow, which seems to be exactly what we want.

Unfortunately that means we will run two queries, but there is no other way. Also, we can pass them to the database in just one go, which means we'll get rid of the slowest part anyway, so things should be fine.

Franz 2011-11-03 14:49:09

Commit 99fbea8 to database master

#384: Properly implement REPLACE INTO queries for PostgreSQL (using two steps).
I also fixed their behaviour for standard SQL.

Franz 2011-11-03 15:17:23

Commit 88e80c6 to fluxbb fluxbb-2.0

Update REPLACE INTO queries to use new structure.

Loosely related to #384.

Franz 2011-11-03 22:55:39

Commit 8d31877 to database master

#384: Change REPLACE queries to only run once.
This change also splits out the UPDATE / INSERT queries for PostgreSQL into two separate queries (instead of sending them to the database together).
The hook: the INSERT query is only executed if necessary (no rows were affected during UPDATE).

Franz 2011-11-03 23:10:47

Commit 056c5de to database master

#384: Forget my "hook". Both queries have to be executed with PostgreSQL, as the UPDATE query could simply change nothing, even though there is a row.

Franz 2011-11-03 23:20:27

Ok, I more or less fixed this now.

One problem remains, and that is concurrency: The current approach can lead to race conditions (though probably not in the use cases in FluxBB). There are ways to prevent this - is it worth it?

Franz 2011-11-03 23:25:50

Commit dc4961b to database master

Also return 1 or 2 for REPLACE INTO queries with PostgreSQL.
Weird behaviour, but standard.
Related to #384.

Franz 2011-11-07 13:56:40

  • Status changed from open to fixed.

Well, I'll mark this as done.

At some point, we will probably have to revisit the concurrency issue.

Franz 2011-11-09 22:42:57

This was fixed by now.