[DBAL-879] Sequence default value [PGSQL] Created: 29/Apr/14  Updated: 29/Apr/14

Status: Open
Project: Doctrine DBAL
Component/s: Drivers, Schema Managers
Affects Version/s: 2.4.2
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Mohammad Niknam Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: autoincrement, postgresql, sequence
Environment:

ArchLinux
PostgreSQL 9.3.4



 Description   

Hi
I'm using dbal to generate schmea from database via Schema-Manager. The problem is that my primary field 'id' have default value of 'nextval('test1_id_seq'::regclass)' but when I retrive columns using Doctrine\DBAL\Schema\AbstractSchemaManager::listTableDetails() or Doctrine\DBAL\Schema\Table::getColumns() , default value of the column 'id' is null.
In Doctrine\DBAL\Schema\PostgreSqlSchemaManager::_getPortableTableColumnDefinition() method at line 292 default value replaced with null, I don't know why but I guess It's because Driver compatibility.
Also Doctrine\DBAL\Schema\Sequence has no method to retrieve that table.
So I don't have the default value (pointing at sequence) and I can't find out what Sequence is linked to this table either.






[DBAL-863] [GH-564] [DBAL-630] Incorrect PostgreSQL boolean handling Created: 04/Apr/14  Updated: 30/Jun/14  Resolved: 27/Jun/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Incomplete Votes: 0
Labels: boolean, dbal, orm, postgresql

Issue Links:
Reference
relates to DBAL-931 pgSql boolean conversion Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of davividal:

Url: https://github.com/doctrine/dbal/pull/564

Message:

Working on PostgreSQL incorrect boolean handling when emulating prepared statements



 Comments   
Comment by Davi Koscianski Vidal [ 04/Apr/14 ]

This PR is related to http://www.doctrine-project.org/jira/browse/DBAL-630

Comment by Davi Koscianski Vidal [ 04/Apr/14 ]

The only issue with this PR is that it breaks Doctrine2 ORM. I already have a patch for that too, but I'm not sure on how to proceed.

Comment by Davi Koscianski Vidal [ 04/Apr/14 ]

I'm not breaking ORM anymore.

Comment by Doctrine Bot [ 27/Jun/14 ]

A related Github Pull-Request [GH-564] was closed:
https://github.com/doctrine/dbal/pull/564

Comment by Marco Pivetta [ 27/Jun/14 ]

Moved to PR https://github.com/doctrine/dbal/pull/625

Comment by Doctrine Bot [ 30/Jun/14 ]

A related Github Pull-Request [GH-625] was closed:
https://github.com/doctrine/dbal/pull/625





[DBAL-567] PDOPgSql should respect connection charset option Created: 23/Jul/13  Updated: 18/Dec/13  Resolved: 18/Dec/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: None
Fix Version/s: 2.5
Security Level: All

Type: Improvement Priority: Major
Reporter: Asmir Mustafic Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: postgresql
Environment:

postgres 9



 Description   

Add this piece of code to _constructPdoDsn method to respect charset option

 
if (isset($params['charset'])) {
     $dsn .= 'options=\'--client_encoding' . $params['charset'] . '\' ';
}


 Comments   
Comment by Asmir Mustafic [ 23/Jul/13 ]

This feature is documented here: http://www.php.net/manual/en/function.pg-connect.php





[DBAL-553] PostgreSQL JSON Type Created: 03/Jul/13  Updated: 18/Feb/14  Resolved: 29/Dec/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: 2.5
Security Level: All

Type: New Feature Priority: Minor
Reporter: Martin Prebio Assignee: Steve Müller
Resolution: Fixed Votes: 2
Labels: JSON, PostgreSQL

Issue Links:
Duplicate
is duplicated by DBAL-815 Returning a wrong field type for Post... Resolved

 Description   

Since version 9.2 PostgreSQL has a new data type for JSON fields: http://www.postgresql.org/docs/9.2/static/datatype-json.html

The current json_array datatype works on this field if it was not created by Doctrine but manually but it would be nice to support the field type also for schema generation.

I can offer a Pull Request for this change but I have not found a method for determining the database version.



 Comments   
Comment by Daniel Londero [ 18/Jul/13 ]

It would be great to have JSON datatype! In the mean time the solution is creating a "Custom Mapping Type", right?

Comment by Steve Müller [ 22/Dec/13 ]

Benjamin Eberlei We could adopt the GuidType implementation here with checking on the platform if it supports native json type and use that instead. What do you think?

Comment by Steve Müller [ 27/Dec/13 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/469

Comment by Doctrine Bot [ 29/Dec/13 ]

A related Github Pull-Request [GH-469] was closed:
https://github.com/doctrine/dbal/pull/469

Comment by Benedikt Allendorf [ 16/Feb/14 ]

It seems that this feature is already in the documentation (http://doctrine-dbal.readthedocs.org/en/latest/reference/types.html#mapping-matrix) although it is only included in the next version of DBAL (2.5).





[DBAL-525] PostgreSQL - getDateTimeFormatString() missing Created: 22/May/13  Updated: 29/Dec/13  Resolved: 29/Dec/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.4
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Jan Pecek Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: datetime, format, postgresql
Environment:

PostgreSQL Database


Issue Links:
Duplicate
duplicates DBAL-415 [GH-248] Enable multiple postgresql d... Resolved

 Description   

Using PostgreSQL and column with "timestamp without time zone" returns datetime in format Y-m-d H:i:s.u but not always. If u is zero the PHP driver returns Y-m-d H:i:s format only. Specification of function getDateTimeFormatString is missing for this case too. It's required to resolve described different two formats and add getDateTimeFormatString with usec to postgres platform
It's similar as DBAL-397 but for not time zone datetimes. It throws the same exception as in linked issue.



 Comments   
Comment by Steve Müller [ 23/Dec/13 ]

Jan Pecek can you please test if this is fixed in current master? Benjamin Eberlei supplied a patch for this in commit: https://github.com/doctrine/dbal/commit/feedcfae9a03aab01ef147128807e9c6e463e0ab

Comment by Benjamin Eberlei [ 29/Dec/13 ]

Duplicate of DBAL-415





[DBAL-477] Just doublequote all schema names and field names in PostgreSQL sql command generation, and the same for MySQL Created: 28/Mar/13  Updated: 24/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: Platforms, Schema Managers
Affects Version/s: 2.3.2
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: jos de witte Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mysql, postgresql
Environment:

Any PostgreSQL environment


Issue Links:
Reference
is referenced by DBAL-96 Make approach towards identifier quot... Open

 Description   

Generation of any SQL command to the database (From entities or migration versions) does not quote all the reserved keywords (For example a fieldname `right`.

Simple fix that always works: double-quote dbname, schemaname and fieldname

e.g "dbsecurity"."userschema"."users" or "tblusers"

MySQL : use the ` sign.

e.g `security`.`users` or `tblusers` (No support for schemas since I last checked some time ago)



 Comments   
Comment by Steve Müller [ 24/Jun/13 ]

If those are reserved keywords, they should be added to the "PostgreSQLKeywords" class and they will be quoted by Doctrine. As far as I can see those keywords mentioned are not present in this class. Maybe there is something missing?





[DBAL-474] SchemaManager / Connection on PostgreSQL platform does not respect filterExpression for sequences Created: 27/Mar/13  Updated: 16/May/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.2.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: jos de witte Assignee: Steve Müller
Resolution: Unresolved Votes: 1
Labels: postgresql, schematool
Environment:

Windows & Linux



 Description   

Dear Symfony team,

the filterExpression on AbstractSchemaManager seems not to work for sequences.

This only happens under postgres.

It seems the way the sequences are handled are the culprit: It tries to get min_value etc of sequences without matching sequence names to the filter expression in advance.

If for example access to the sequences is denied, (Different schema without permissions for the current entity manager), any higher-level ORM operations like generating migration versions fail.

--------------------- UPDATE

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.



 Comments   
Comment by Benjamin Eberlei [ 14/Apr/13 ]

Can you paste an exception trace? I see that filtering is applied to sequences, but your description seems to indicate this happens due to an SQL query much earlier?

Comment by jos de witte [ 24/Apr/13 ]

Dear Benjamin,

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.

Comment by Steve Müller [ 06/Jan/14 ]

jos de witte I think your issue got fixed in commit: https://github.com/doctrine/dbal/commit/8beb732fe9d5cd40a0d677f250d2be325482744f
This patch was first introduced in 2.3. Can you please confirm that this is fixed? Otherwise can you please provide a concrete example so that we can reproduce you issue?

Comment by Arnout Standaert [ 29/Jan/14 ]

I believe we are bumping into the same issue. Our webapp uses Migrations, but for our webapp we are limited to a certain schema within a bigger PostgreSQL database. We only have permissions on our own schema and public.
Now, listSequences in AbstractSchemaManager does filter the asset names correctly with the mentioned fix.

But the problem is with the step before, _getPortableSequencesList (see line 135 of AbstractSchemaManager):

        return $this->filterAssetNames($this->_getPortableSequencesList($sequences));

This function goes out and does a _getPortableSequenceDefinition on every sequence in the unfiltered list. For every sequence, the _getPortableSequenceDefinition in PostgreSqlSchemaManager performs a SELECT:

        $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));

Now, our user role in the database doesn't have SELECT permissions on these sequences in other schemas, so the migration fails with a privilege error.

There should be some kind of filtering on the sequence list BEFORE the SELECT statement in the _getPortableSequenceDefinition function are performed, no?

Comment by Arnout Standaert [ 30/Jan/14 ]

I currently have a workaround running locally, which filters the sequences list before creating the PortableSequence's. This is probably hackish and a poor workaround, just posting here as a temporary solution and further illustration of the problem.

Altered line 135 in AbstractSchemaManager:

        return $this->_getPortableSequencesList($this->filterSequenceNames($sequences));

I added function filterSequenceNames() in AbstractSchemaManager for appropriate sequence filtering:

    /**
     * Filters sequence names if they are configured to return only a subset of all
     * the found elements.
     *5
     * @param array $sequenceNames
     *
     * @return array
     */
    protected function filterSequenceNames($sequenceNames)
    {
        $filterExpr = $this->getFilterSchemaAssetsExpression();
        if ( ! $filterExpr) {
            return $sequenceNames;
        }
        
        return array_values ( array_filter($sequences, function ($sequenceName) use ($filterExpr) {
                $sequenceName = $sequenceName["schemaname"].".".$sequenceName["relname"];
                return preg_match($filterExpr, $sequenceName);
            })
        );

    }

After these modifications, doctrine:migrations:migrate operations complete succesfully, even with our limited-permission database account.

Comment by Steve Müller [ 21/Feb/14 ]

Arnout Standaert Your fix looks promising and reasonable. Can you create a PR on the DBAL repo for that?

Comment by Viktor Sidochenko [ 15/Mar/14 ]

Why this fix is not in master?

Comment by Steve Müller [ 15/Mar/14 ]

Viktor Sidochenko because nobody has fixed it yet Feel free to provide a patch on GitHub.

Comment by Arnout Standaert [ 17/Mar/14 ]

I haven't gotten around to doing the PR on GitHub yet, I'm not yet too familiar with that.
I'll try to find some time for this the coming days.

Comment by Viktor Sidochenko [ 17/Mar/14 ]

Will be good. I`m not professional developer to make patches to upstream. So just voted for this issue.

Comment by Steve Müller [ 19/Mar/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/546
jos de witte, Arnout Standaert, Viktor Sidochenko can you please test if the supplied PR fixes the problem?

Comment by Doctrine Bot [ 16/May/14 ]

A related Github Pull-Request [GH-546] was closed:
https://github.com/doctrine/dbal/pull/546





[DBAL-420] Schema Drop SQL incorrect on PostgreSQL with entities with GeneratedValue(strategy="IDENTITY") Created: 23/Jan/13  Updated: 14/Apr/13  Resolved: 14/Apr/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.3.2
Fix Version/s: 2.3.4
Security Level: All

Type: Bug Priority: Major
Reporter: Adam Ashley Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: postgresql, schematool
Environment:

Symfony 2.1, PHP5.4, PostgreSQL 9.1 on Ubuntu 12.04



 Description   

This problem is probably related to #DBAL-54. However that was closed by the raiser as he changed his Entity model and it went away.

When schema drop is run the following error occurs:
Doctrine\DBAL\DBALException: An exception occurred while executing 'DROP SEQUENCE radacct_radacctid_seq':

SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR: cannot drop sequence radacct_radacctid_seq because other objects depend on it
DETAIL: default for table radacct column radacctid depends on sequence radacct_radacctid_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.

The source of this problem is the difference between strategy="IDENTITY" and strategy="SEQUENCE"

With SEQUENCE doctrine creates the table schema with field type BIGINT and no specified. It then creates a seperate sequence and as far as I can tell takes care of getting and inserting the next id number itself.

With IDENTITY doctrine creates the table schema with field type BIGSERIAL and no specified default. Now postgres automatically creates a sequence and creates the column with type BIGINT and sets the DEFAULT to the pgpsql statement required to get the nextval from the sequence.

At this point the two differently configured tables will work successfully and identically, except SEQUENCE tables will only get a correct new ID when run through the doctrine code while IDENTITY tables will get the correct new ID whenever an insert is done to the table.

Because in the case of an IDENTITY field postgresql creates the field with a default value refering to the sequence the sequence can not be deleted before the table reference is removed.

For my case I need the IDENTITY fields to work as we have a RADIUS server that needs to insert into one table which is managed and mapped to an entity in Doctrine.

Swapping the order of DROP TABLE and DROP SEQUENCE commands in Doctrine/DBAL/Schema/Visitor/DropSchemaSqlCollector.php in getQueries() line 159. Does not work as a quick fix. The following error occurs as the sequence is quite correctly be dropped along with the table.

Doctrine\DBAL\DBALException: An exception occurred while executing 'DROP SEQUENCE radacct_radacctid_seq':

SQLSTATE[42P01]: Undefined table: 7 ERROR: sequence "radacct_radacctid_seq" does not exist



 Comments   
Comment by Adam Ashley [ 24/Jan/13 ]

This issue also appears to affect Doctrine_Migrations. Generated migrations try to drop and recreate automatically generated sequences associated with SERIAL fields making a mess of the database.

Comment by Benjamin Eberlei [ 22/Mar/13 ]

A related Github Pull-Request [GH-289] was opened
https://github.com/doctrine/dbal/pull/289

Comment by Adrien Crivelli [ 12/Apr/13 ]

@Adam Ashley, could you test whether https://github.com/doctrine/dbal/pull/289 solve your issue ?

Comment by Doctrine Bot [ 14/Apr/13 ]

A related Github Pull-Request [GH-289] was closed:
https://github.com/doctrine/dbal/pull/289





[DBAL-406] PostgreSqlSchemaManager::tablesExist() misses schema-qualified table names if they exist in the first schema on the search path Created: 07/Jan/13  Updated: 07/Jan/13

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.3.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Roger Hunwicks Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: postgresql
Environment:

Postgresql 9.1



 Description   

Please see https://github.com/doctrine/migrations/issues/99 for additional background.

To reproduce:

CREATE SCHEMA test_schema;
CREATE TABLE test_schema.test_table (test_column TEXT);

Then in Doctrine:

Unable to find source-code formatter for language: php. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
$connection->executeUpdate('SET search_path=test_schema;');
$result = $connection->getSchemaManager()->tablesExist(array('test_schema.test_table'));

$result is false when it should be true.

The error occurs because PostgreSqlSchemaManager returns the bare table name from getPortableTablesList() if the schema is the first one in the search path.

The full explanation is...

AbstractSchemaManager::tablesExist() calls $this->getPortableTablesList() before checking if the tables exist.

PostgreSqlSchemaManager overrides this in _getPortableTableDefinition() by comparing the schema for the table with the search path for the connection. If the table schema is the first one in the search path, then it returns the bare table name, if it isn't then it returns the schema-qualified table name (i.e. schema.table).

tablesExist() does an array_intersect to check that all the tables in the search array exist in the database.

If one of the tables in the search array was schema-qualified but also in the first schema on the search path, then you end up checking:

array_intersect(array('test_schema.test_table'), array('test_table'))

which fails.

One way to fix it would be to override tablesExist() in PostgreSqlSchemaManager so that it passes the search array through getPortableTableDefinition() before doing the array_intersect:

Unable to find source-code formatter for language: php. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
    /**
     * Return true if all the given tables exist.
     *
     * @param array $tableNames
     * @return bool
     */
    public function tablesExist($tableNames)
    {
        foreach ($tableNames as $key => $tableName) {
            if (strpos($tableName, '.') !== false) {
                $tableName = explode('.', $tableName, 2);
                $tableNames[$key] = $this->_getPortableTableDefinition(array('schema_name'=>$tableName[0], 'table_name'=>$tableName[1]));
            }
        }
        return parent::tablesExist($tableNames);
    }

I'm happy to provide a PR on GitHub if you want.






[DBAL-397] PostgreSQL - getDateTimeTzFormatString() Created: 12/Dec/12  Updated: 17/Dec/12  Resolved: 16/Dec/12

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Phill Pafford Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: datetime, dql, format, postgresql
Environment:

PostgreSQL Database



 Description   

In:

  • vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php

The function:

getDateTimeTzFormatString()

only returns one datetime format:


    /**
     * {@inheritDoc}
     */
    public function getDateTimeTzFormatString()
    {
        //return 'Y-m-d H:i:sO'; // original format
        return 'Y-m-d H:i:s.uO'; // format also needed
    }


Here is the error I get using the original format:

Could not convert database value "2012-12-07 16:01:52.580789-05" to Doctrine Type datetimetz. Expected format: Y-m-d H:i:s.O

If I use this format: 'Y-m-d H:i:s.uO' it works.



 Comments   
Comment by Phill Pafford [ 12/Dec/12 ]

adding html tags for code to display correctly

Comment by Benjamin Eberlei [ 16/Dec/12 ]

DateTimeTz is created as 'TIMESTAMP(0) WITH TIME ZONE' on DBAL. If you have 'TIMESTAMP(6) WITH TIME ZONE' instead, then you have to create your own datatye.

Comment by Phill Pafford [ 17/Dec/12 ]

Why would this be a new data type? I understand how to fix the problem with creating my own data type to handle the formatting issue and would even suggest just overriding the data type with


        Type::overrideType('datetimetz', 'Doctrine\DBAL\Types\VarDateTimeType');
    Type::overrideType('datetime', 'Doctrine\DBAL\Types\VarDateTimeType');

But it's not a new data type, it's a formatting issue.





[DBAL-368] array and object types should use BLOB, not CLOB, to store serialized data Created: 19/Oct/12  Updated: 20/Dec/13  Resolved: 23/Jan/13

Status: Closed
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.2.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Karsten Dambekalns Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: postgresql


 Description   

When using array or object types, PHP's serialize() is used. Since that can return NUL bytes, using a text type will fail at least on PostgreSQL (just search the web for issues of that kind…).

So ArrayType and ObjectType should return a BLOB definition instead of a CLOB definition to be binary-safe.



 Comments   
Comment by Karsten Dambekalns [ 19/Oct/12 ]

See DBAL-369 for the suggested fix

Comment by Marco Pivetta [ 23/Jan/13 ]

Karsten Dambekalns DBAL-368 is this issue. What's the one you were referring to?

Comment by Marco Pivetta [ 23/Jan/13 ]

Nvm, it was DBAL-369

Comment by Marco Pivetta [ 23/Jan/13 ]

Moved to DBAL-369

Comment by Doctrine Bot [ 20/Dec/13 ]

A related Github Pull-Request [GH-219] was closed:
https://github.com/doctrine/dbal/pull/219





Generated at Fri Jul 25 10:48:04 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.