beberlei/Doctrine-Workflow — Issue #6: Unable to define workflow on postgresql
Author: koenedaele
Created: 2011-10-14 08:53:40+00:00
State: open

Hi,

I'm testing out this project with a postgresql database and came across the following:

```
PHP Fatal error: Uncaught exception 'ezcWorkflowDefinitionStorageException' with message 'Error while persisting workflow: SQLSTATE[0A000]: Feature not supported: 7 ERROR: SELECT FOR UPDATE/SHARE is not allowed with aggregate functions' in /home/vandaeko/Projecten/doctrine_flow/docworkflow/lib/DoctrineExtensions/Workflow/DefinitionStorage.php:385
Stack trace:
#0 /home/vandaeko/Projecten/doctrine_flow/docworkflow/lib/DoctrineExtensions/Workflow/WorkflowManager.php(139): DoctrineExtensions\Workflow\DefinitionStorage->save(Object(ezcWorkflow))
#1 /home/vandaeko/Projecten/doctrine_flow/test_define.php(90): DoctrineExtensions\Workflow\WorkflowManager->save(Object(ezcWorkflow))
#2 {main}
thrown in /home/vandaeko/Projecten/doctrine_flow/docworkflow/lib/DoctrineExtensions/Workflow/DefinitionStorage.php on line 385
```

From the postgresql log file:
```
2011-10-14 10:28:47 CEST LOG: statement: BEGIN
2011-10-14 10:28:47 CEST ERROR: SELECT FOR UPDATE/SHARE is not allowed with aggregate functions
2011-10-14 10:28:47 CEST STATEMENT: SELECT MAX(workflow_version) AS version FROM wf_workflow WHERE workflow_name = $1 FOR UPDATE
2011-10-14 10:28:47 CEST LOG: statement: ROLLBACK
```
Basically, postgresql doenst allow FOR UPDATE with aggregate functions like MAX. I've currently fixed this by using a subselect to select the max version.

I've currently changed the code in DefinitionStorage::getCurrentVersion to the following:
```php
$sql = "SELECT workflow_version AS version FROM " . $this->options->workflowTable() . " ".
"WHERE workflow_name = ? " .
" AND workflow_version = ( SELECT MAX(workflow_version) FROM " . $this->options->workflowTable( ) . " WHERE workflow_name = ? )" .
$platform->getForUpdateSQL();
```

Not sure if it's the ideal solution, but i allows me to get along with trying your project out.