Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Postgresql and id's #7

Closed
koenedaele opened this Issue · 5 comments

2 participants

Koen Van Daele Benjamin Eberlei
Koen Van Daele

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:

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.

Benjamin Eberlei
Owner

Yah the code hasnt been prepared for use with sequences yet. Do you want to contribute a PR to support that?

Doctrine ORM does that using the $platform->supportsSequences() feature to switch the code path.

Koen Van Daele

After poking around a bit in Doctrine\DBAL I think the problem lies there. The PostgreSqlPlatform doens't seem to do anything with sequences when executing _getCreateTableSql.

Benjamin Eberlei
Owner

Yes, the Workflow Schema object needs to create sequences for PostgreSQL.

What however works is using autoincrement boiling down to serial (Afaik).

I have to check it out.

Koen Van Daele

Sorry, didn't see your last remark before posting.

I'll have a look at how Doctrine deals with it and see what I can come up with.

Right now I'm not sure if PostgreSqlPlatform should add a serial column or an integer column with a default value from a sequence or just stay as it is. The Oracle Platform seems to handle sequences directly in the _getCreateTableSql method.

Benjamin Eberlei
Owner

Fixed in Pull-Request #10

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.