beberlei/Doctrine-Workflow — Issue #7: Postgresql and id's
Author: koenedaele
Created: 2011-10-14 09:17:27+00:00
State: open

When trying to save a workflow definition I get the following:

```
PHP Fatal error: Uncaught exception 'ezcWorkflowDefinitionStorageException' with message 'Error while persisting workflow: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "workflow_id" violates not-null constraint' 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 logs:
```
2011-10-14 11:02:13 CEST LOG: statement: BEGIN
2011-10-14 11:02:13 CEST LOG: execute pdo_stmt_00000001: SELECT workflow_version AS version FROM wf_workflow WHERE workflow_name = $1 AND workflow_version = ( SELECT MAX(workflow_version) FROM wf_workflow WHERE workflow_name = $2 )FOR UPDATE
2011-10-14 11:02:13 CEST DETAIL: parameters: $1 = 'Test', $2 = 'Test'
2011-10-14 11:02:13 CEST LOG: statement: DEALLOCATE pdo_stmt_00000001
2011-10-14 11:02:13 CEST LOG: execute pdo_stmt_00000002: UPDATE wf_workflow SET workflow_outdated = $1 WHERE workflow_name = $2
2011-10-14 11:02:13 CEST DETAIL: parameters: $1 = '1', $2 = 'Test'
2011-10-14 11:02:13 CEST LOG: statement: DEALLOCATE pdo_stmt_00000002
2011-10-14 11:02:13 CEST LOG: execute pdo_stmt_00000003: INSERT INTO wf_workflow (workflow_name, workflow_version, workflow_created, workflow_outdated) VALUES ($1, $2, $3, $4)
2011-10-14 11:02:13 CEST DETAIL: parameters: $1 = 'Test', $2 = '1', $3 = '2011-10-14 11:02:13', $4 = '0'
2011-10-14 11:02:13 CEST ERROR: null value in column "workflow_id" violates not-null constraint
2011-10-14 11:02:13 CEST STATEMENT: INSERT INTO wf_workflow (workflow_name, workflow_version, workflow_created, workflow_outdated) VALUES ($1, $2, $3, $4)
2011-10-14 11:02:13 CEST LOG: statement: DEALLOCATE pdo_stmt_00000003
2011-10-14 11:02:13 CEST ERROR: current transaction is aborted, commands ignored until end of transaction block
2011-10-14 11:02:13 CEST STATEMENT: DEALLOCATE pdo_stmt_00000003
2011-10-14 11:02:13 CEST LOG: statement: ROLLBACK
```

The tables that are being generated by the SchemaBuilder do not contain a serial field as primary key, but an integer. So this record ends up without a primary key.

Postgresql uses sequences. I generally first get an id from a sequence and then use this id to insert a record in a table. I normally define tables as following:

```sql
CREATE SEQUENCE test_id_seq;

CREATE TABLE test
(
id INTEGER NOT NULL DEFAULT nextval('test_id_seq')
)
```

The easiest option in this case would probably be to define the sequences and then specify the sequence name in the call to lastInsertId.

I'm not sure if this problem isn't partially the responsability of Doctrine DBAL. It's the first time I've used doctrine DBAL, so I have no Idea if it generally mixes well with postgresql.