[DBAL-1221] Wrong dsn when using postgresql with pdo Created: 02/May/15  Updated: 02/May/15  Resolved: 02/May/15

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

Type: Bug Priority: Major
Reporter: Karim Assignee: Steve Müller
Resolution: Duplicate Votes: 0
Labels: dbal, dsn, postgresql
Environment:

Symfony 2.6 on Linux Fedora 21 using PHP 5.6.8


Issue Links:
Duplicate
duplicates DBAL-1215 [GH-844] template1 as default databas... Resolved

 Description   

When I trying to create database using command line from Symfony framework, an error occurs :
[Doctrine\DBAL\Exception\ConnectionException]
An exception occured in driver: SQLSTATE[08006] [7] FATAL: database « myUserName » does not exists.

I tried to check what is wrong and I found that if in the class Doctrine\DBAL\Driver\PDOPgSql\Driver, in _constructPdoDsn method, i explicitly set the dbname like : $params['dbname'] = "myDb"; , it works.



 Comments   
Comment by Steve Müller [ 02/May/15 ]

Duplicate of DBAL-1215.





[DBAL-1215] [GH-844] template1 as default database for PostgreSQL Created: 29/Apr/15  Updated: 02/May/15  Resolved: 30/Apr/15

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: None
Fix Version/s: 2.6, 2.4.5, 2.5.2
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: dbname, pdo_pgsql, pgsql, postgresql, template1

Issue Links:
Duplicate
is duplicated by DBAL-1221 Wrong dsn when using postgresql with pdo Resolved

 Description   

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

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

Message:

Fixes (including but not limited to) https://github.com/doctrine/DoctrineBundle/issues/402 by connecting by default to 'template1' instead of the database with the same name as the user (PostgreSQL default in case of no dbname).



 Comments   
Comment by Doctrine Bot [ 30/Apr/15 ]

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





[DBAL-1190] [GH-829] Pgsql connection test with charset parameter Created: 02/Apr/15  Updated: 30/Apr/15

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

Type: Improvement Priority: Minor
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: charset, client_encoding, pdo_pgsql, pgbouncer, pgsql, postgresql, testing


 Description   

This issue is created automatically through a Github pull request on behalf of zeroedin-bill:

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

Message:

This PR adds 2 tests for connecting to pgsql via PDOPgSql, while using a charset parameter.

Related to #828, #823



 Comments   
Comment by Doctrine Bot [ 30/Apr/15 ]

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





[DBAL-1189] [GH-828] rehashed charset implementation to support old versions of postgresql Created: 02/Apr/15  Updated: 08/Apr/15  Resolved: 08/Apr/15

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.5, 2.5.1
Fix Version/s: 2.6, 2.5.2
Security Level: All

Type: Bug Priority: Minor
Reporter: Doctrine Bot Assignee: Bill Schaller
Resolution: Fixed Votes: 0
Labels: charset, client_encoding, connection, driver, dsn, pdo_pgsql, pgbouncer, pgsql, postgresql, set-names


 Description   

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

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

Message:

My previous client_encoding fix turned out not to work on older postgres versions – BUT, the options param isn't supported by pgbouncer at all. SO, because there isn't consistent behavior with setting the character set via DSN - this change utilizes `SET NAMES`. I used SET NAMES since that is SQL standard and supported by PG.

I confirmed `SET NAMES` is supported all the way back to the older doc PG has, 7.1: http://www.postgresql.org/docs/7.1/static/multibyte.html



 Comments   
Comment by Doctrine Bot [ 02/Apr/15 ]

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





[DBAL-1185] [GH-825] Add postgresql 9.4 for travis builds Created: 30/Mar/15  Updated: 02/Apr/15  Resolved: 02/Apr/15

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

Type: Improvement Priority: Minor
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: ci, pgsql, postgresql, testing, travis


 Description   

This issue is created automatically through a Github pull request on behalf of zeroedin-bill:

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

Message:



 Comments   
Comment by Doctrine Bot [ 02/Apr/15 ]

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





[DBAL-1183] [GH-823] fix client_encoding setting to support pgbouncer Created: 27/Mar/15  Updated: 27/Mar/15  Resolved: 27/Mar/15

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

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: charset, client_encoding, pgbouncer, pgsql, postgresql

Issue Links:
Reference
relates to DBAL-567 PDOPgSql should respect connection ch... Resolved

 Description   

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

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

Message:

The current release doesn't allow connecting to pgbcouner when specifying the charset due to the lack of support for the 'options' parameter. The fix is to use the client_encoding option directly instead of passing it through the options parameter.

This exact same bug was fixed in node.js's PG driver the exact same way.

https://github.com/brianc/node-postgres/pull/356

I've confirmed this fix works with pgbouncer.



 Comments   
Comment by Doctrine Bot [ 27/Mar/15 ]

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





[DBAL-1168] Schema's getMigrateFromSql always adds CREATE SCHEMA Created: 11/Mar/15  Updated: 28/May/15

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

Type: Bug Priority: Major
Reporter: Varga Bence Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: postgresql, schematool
Environment:

Postgresql



 Description   

I originally posted this to Migrations; noticing that all the generated down() methods start with a "CREATE SCHEMA public" line.

Inspecting the return from Schema#getMigrateFromSql it indeed contains the create statement.



 Comments   
Comment by Adam Sentner [ 19/May/15 ]

I am also having this issue. The down() method always adds: $this->addSql('CREATE SCHEMA public');

Same environment, also using Postgres.

Any chance this is on anyone's radar for a release in the near future?

Comment by Albert Casademont [ 28/May/15 ]

Hit by this too. The problem seems to be that the "public" namespace is not added to the table names by default and hence the diff between what postgres says (a "public" schema is created by default in the DB) and what our schema says.

I tried to solve this with a workaround by prepending "public." to all table names. It works for the first migration but then in the next migration will try to delete all tables without the "public." and create them again. So that's not working!

The solution is assuming that there's always a default 'public' namespace in the Schema.php class.





[DBAL-1163] Pagination issue with order by statement Created: 05/Mar/15  Updated: 05/Mar/15

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

Type: Bug Priority: Major
Reporter: Vahe Shadunts Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: paginator, postgresql
Environment:

linux, PostgreSQL



 Description   

For example I have 2 entities (family, person) with ManyToOne(familyId) association in Person entity, and OneToMany(mappedBy="familyId") in Family Entity (Full class definitions are in the end of description)

And this is my DQL part

$query = $em -> createQuery('
select e0, e1 from TestPagingBundle:Family e0 join e0.people e1
order by e1.firstName asc
');
$query -> setMaxResults(2);

This is working perfectly when the ordered firstNames are from different families.

But the paginator class generates 3 queries, 1st for fetching count, second for id's 3rd is a general query for data.

This is the 2nd Query:

SELECT DISTINCT id0, first_name3
FROM (
SELECT f0_.id AS id0, f0_.name AS name1, p1_.id AS id2, p1_.first_name AS first_name3, p1_.last_name AS last_name4
FROM family f0_
INNER JOIN person p1_ ON f0_.id = p1_.family_id
ORDER BY p1_.first_name ASC
)dctrn_result
ORDER BY first_name3 ASC
LIMIT 2

So in the select statement in this query there are distinctly selected 2 fields, id and first_name.

And if we'll have 2 people in one family which names are Aaron and Abraham, this query result will be

id | name
-------------------
1 | Aaron
1 | Abraham
-------------------

So we have fetched only one id from families instead of 2, which we wanted to select.

Then the where statement of 3rd query will be where id in ( ? ) with params [1,1], and we are getting 1 Family instead of 2.

----------------
class Family

{ /** * @var integer * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="IDENTITY") */ private $id; /** * @var string * * @ORM\Column(name="name", type="string", length=256, nullable=true) */ private $name; /** * @OneToMany(targetEntity="Person", mappedBy="familyId") **/ private $people; }

class Person

{ /** * @var integer * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="IDENTITY") */ private $id; /** * @ManyToOne(targetEntity="Family") * @JoinColumn(name="family_id", referencedColumnName="id") **/ private $familyId; /** * @var string * * @ORM\Column(name="first_name", type="string", length=256, nullable=false) */ private $firstName; /** * @var string * * @ORM\Column(name="last_name", type="string", length=256, nullable=true) */ private $lastName; }




[DBAL-1143]  PostgreSQL PostgreSQL94 Created: 31/Jan/15  Updated: 31/Jan/15

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

Type: Task Priority: Minor
Reporter: Konstantin Nizhinskiy Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: postgresql


 Description   

Add new type jsonb
doc:
http://info.enterprisedb.com/rs/enterprisedb/images/EDB_White_Paper_Using_the_NoSQL_Features_in_Postgres.pdf






[DBAL-1142] [GH-796] Add tsvector type support in PostgreSQL Platform Created: 31/Jan/15  Updated: 10/Feb/15  Resolved: 10/Feb/15

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

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: mapping, pgsql, postgresql, tsvector, type

Issue Links:
Duplicate
is duplicated by DBAL-1141 [GH-795] Add tsvector type support in... Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of believer-ufa:

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

Message:

This conversation continued in this request: https://github.com/doctrine/dbal/pull/795

I created test case and changed field type to "text". Test passes)



 Comments   
Comment by Doctrine Bot [ 01/Feb/15 ]

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

Comment by Doctrine Bot [ 10/Feb/15 ]

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

Comment by Doctrine Bot [ 10/Feb/15 ]

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





[DBAL-1141] [GH-795] Add tsvector type support in PostgreSQL Platform Created: 31/Jan/15  Updated: 10/Feb/15  Resolved: 10/Feb/15

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

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Duplicate Votes: 0
Labels: mapping, pgsql, postgresql, tsvector, type

Issue Links:
Duplicate
duplicates DBAL-1142 [GH-796] Add tsvector type support in... Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of believer-ufa:

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

Message:

Just one simple string. Maybe add this to code?



 Comments   
Comment by Doctrine Bot [ 10/Feb/15 ]

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





[DBAL-1100] [GH-762] PostgreSQL needs explicitly closed connection in functional test Created: 03/Jan/15  Updated: 03/Jan/15  Resolved: 03/Jan/15

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.5, 2.4.3
Fix Version/s: 2.6, 2.5.1
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: connection, postgresql, process-isolation, tests


 Description   

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

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

Message:

The test `Doctrine\Tests\DBAL\Functional\ConnectionTest::testConnectWithoutExplicitDatabaseName` prevents further tests in the suite from being run as the connection hangs around in PostgreSQL.

After this particular test, when the next `DROP DATABASE` is run, the log shows:

> 2015-01-03 00:21:28 PST ERROR: database "doctrine_tests" is being accessed by other users
> 2015-01-03 00:21:28 PST DETAIL: There is 1 other session using the database.
> 2015-01-03 00:21:28 PST STATEMENT: DROP DATABASE doctrine_tests

And the next functional test to be run produces something like:

> 1) Doctrine\Tests\DBAL\Functional\ConnectionTest::testPingDoesTriggersConnect
> Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'CREATE DATABASE doctrine_tests':
>
> SQLSTATE[42P04]: Duplicate database: 7 ERROR: database "doctrine_tests" already exists
>
> ...

This is with the following:

  • Debian Linux
  • PHP 5.5.9
  • PostgreSQL 9.3.5
  • PHPUnit 4.0.20
  • Process Isolation = off

Turning process isolation on does resolve the issue, but I assume that shouldn't be required. This doesn't seem to affect Travis.



 Comments   
Comment by Doctrine Bot [ 03/Jan/15 ]

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

Comment by Doctrine Bot [ 03/Jan/15 ]

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





[DBAL-1093] [GH-757] Fix creating and dropping database on PostgreSQL Created: 26/Dec/14  Updated: 13/Jan/15  Resolved: 27/Dec/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: None
Fix Version/s: 2.4.4, 2.6, 2.5.1
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: create, database, drop, pgsql, postgresql

Issue Links:
Reference
is referenced by DBAL-1114 Problem with drop database on PostgreSQL Open

 Description   

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

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

Message:

There is some useless code in the schema manager for PostgreSQL for creating and dropping a database. Moreover if either of those operations fails, the schema manager stays in an inconsistent state because the temporary connection that is set in the schema manager is not reverted back to the original connection.



 Comments   
Comment by Doctrine Bot [ 26/Dec/14 ]

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

Comment by Doctrine Bot [ 27/Dec/14 ]

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





[DBAL-1090] [GH-754] Changing string to fixed string is not recognized in PostgreSQL Platform Created: 21/Dec/14  Updated: 26/Dec/14  Resolved: 26/Dec/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: 2.4.4, 2.6, 2.5.1
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: dbal, postgresql


 Description   

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

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

Message:

The following change of an entity using PostgreSQL is not recognized by Doctrine DBAL:
from

    /**
     * @ORM\Column(type="string", name="name", length=2)
     */
    public $name;

to

    /**
     * @ORM\Column(type="string", name="name", length=2, options={"fixed"=true})
     */
    public $name;

A schema-update should change the column from `character varying(2)` to `character(2)` but instead acts like no changes were made on the column.

With this PR, I've extended the `PostgreSqlPlatform` to detect changes of the fixed option of a column.



 Comments   
Comment by Doctrine Bot [ 26/Dec/14 ]

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





[DBAL-1087] [GH-751] Length of fixed string type (char) is ignored on Postgre schema update Created: 19/Dec/14  Updated: 31/Dec/14  Resolved: 31/Dec/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: None
Fix Version/s: 2.4.4, 2.6, 2.5.1
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: dbal, postgresql, schemamanager


 Description   

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

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

Message:

This PR should fix an issue for the fixed string type columns using PostgreSQL.

I've created a basic Symfony 2.6. Application and using a PostgreSQL 9.3. database. In this dummy project, I've created the following Entity:

<?php

namespace Acme\DemoBundle\Entity;

use Doctrine\ORM\Mapping AS ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="test")
 */
class Test
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer", name="id")
     * @ORM\GeneratedValue
     */
    public $id;

    /**
     * @ORM\Column(type="string", name="name", length=2, options={"fixed" = true})
     */
    public $name;
}

The table is successfully created after a schema update, but when another schema update is executed without any changes to the entity, an ALTER query is executed:

ALTER TABLE test ALTER name TYPE CHAR(2);

After I searched in the code of doctrine dbal, I discovered that the length of the char column is not fetched correctly in `PostgreSqlSchemaManager::_getPortableTableColumnDefinition()`. In my example, length results as `null` and will be later set to 255 in the `Comparator`, which isn't the correct length from the database column. The comparation of the schemas results in a change of char-length.

For simplicity, I extended the if-condition in `PostgreSqlSchemaManager` to:

if (strtolower($tableColumn['type']) === 'varchar' || strtolower($tableColumn['type']) === 'bpchar') {

The fix I've contributed does help to parse the length in my case, but when I look into `PostgreSqlPlatform::initializeDoctrineTypeMappings()` there could be other types with the same problem, that are mapped to the doctrine string type.

// ... part of PostgreSqlPlatform::initializeDoctrineTypeMappings()
            'varchar'       => 'string',
            'interval'      => 'string',
            '_varchar'      => 'string',
            'char'          => 'string',
            'bpchar'        => 'string',
            'inet'          => 'string',

I guess we need to cover `char` and the others too.

`PostgreSqlSchemaManagerTest` also doesn't cover existing code. How is this going to be tested? Does the SchemaManagers need some tests in general?



 Comments   
Comment by Doctrine Bot [ 31/Dec/14 ]

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





[DBAL-1048] Function based index Created: 20/Nov/14  Updated: 13/Feb/15

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

Type: New Feature Priority: Minor
Reporter: Grégoire Paris Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: postgresql


 Description   

I would like to create a function-based index, but it does not seem to be possible ATM. The final goal is to be able to have case insensitive string filters with Postgresql without losing performance.

I tried this mapping :

  indexes:
        my_entity_name_index:
            columns: [ LOWER(name) ]

But here is what doctrine answers :

[Doctrine\DBAL\Schema\SchemaException]

There is no column with name 'LOWER(name)' on table 'my_entity'.



 Comments   
Comment by Marco Pivetta [ 20/Nov/14 ]

I don't think that we can provide platform-specific index column name support here.

Comment by Michael Lambert [ 02/Jan/15 ]

To me this problem is caused by the bigger issue of some DBs defaulting to case-sensitive and some to case-insensitive. From my searching I have been unable to find a solution to make doctrine operate the same across different collations/databases.

Perhaps the issue would be better described as:
Doctrine does not provide consistency between case-sensitive and case insensitive collations/databases

IMHO it would be very beneficial if Doctrine implemented some method for consistency.

I'm not sure if this is the right place to go into more detail, so I'll refrain for now.

Comment by Steve Müller [ 02/Jan/15 ]

Could you please clarify a bit on "From my searching I have been unable to find a solution to make doctrine operate the same across different collations/databases."? What exactly is broken in Doctrine? Can you give examples?

Comment by Michael Lambert [ 02/Jan/15 ]

Nothing is broken, it could just be improved. Having said that, I don't think this would be a minor task.

The issue I am having that lead me to this issue is that like in mysql != like in postgres. Mysql like == postgres ilike.

Additionally, postgres requires a citext for case insensitive text, mysql relies on the db/table collation.

The only way to make a case insensitive unique key in postgres (at least as far as I could find) is to use a function based index.

I hope this helps define the scope of the case sensitivity issues that would be somewhat solved by adding a function based index or a method to enforce case sensitivity /insensitivity.

(sorry for any auto correct failures, I'm on a mobile device on a train)

Comment by Grégoire Paris [ 13/Feb/15 ]

When I create the index manually, without resorting to Doctrine, it does not detect it when updating the schema (might be a bug that was fixed in the latest version IIRC), so I guess this is a workaround in the meantime, but a dangerous one because one can't safely upgrade. Any other ideas ?





[DBAL-1042] [GH-724] Enable PostGIS extension for PostgreSQL tests Created: 10/Nov/14  Updated: 03/Jan/15  Resolved: 03/Jan/15

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

Type: New Feature Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Won't Fix Votes: 0
Labels: extension, pgsql, postgis, postgresql, travis


 Description   

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

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

Message:

This is more like a proposal. Since PostGIS is one of the most popular extensions of PostgreSQL, it might be a good idea to enable it on Travis and make sure DBAL works with it.

At the moment, DBAL's Schema Tool does not work with PostGIS because it cannot handle some VIEWS added by PostGIS.

See: https://travis-ci.org/jsor/dbal/jobs/40528527



 Comments   
Comment by Doctrine Bot [ 10/Nov/14 ]

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

Comment by Doctrine Bot [ 03/Jan/15 ]

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





[DBAL-1033] [GH-716] Do not TRIM() parentheses around partial indexe conditions Created: 05/Nov/14  Updated: 03/Jan/15  Resolved: 03/Jan/15

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms, Schema Managers
Affects Version/s: 2.5
Fix Version/s: 2.6, 2.5.1
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: ddl, index, partial, pgsql, postgresql


 Description   

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

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

Message:

Because PostgreSQL will return the expression with a lot of
parentheses we cannot TRIM() them easily. It is easier and more
correct to adapt to what PostgreSQL returns. That means the declaration
of partial indexes must be updated as follow:

Before:
``options=

{"where": "other_id IS NULL"}

``

After:
``options=

{"where": "(other_id IS NULL)"}

``

And fore more complexe conditions, that would be:
``options=

{"where": "(((id IS NOT NULL) AND (other_id IS NULL)) AND (name IS NULL))"}

``



 Comments   
Comment by Doctrine Bot [ 03/Jan/15 ]

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





[DBAL-1021] [GH-702] [DBAL 930] Only introspect accessible schema objects in PostgreSQL Created: 23/Oct/14  Updated: 05/Nov/14  Resolved: 05/Nov/14

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

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: ddl, postgresql, schema

Issue Links:
Reference
is referenced by DBAL-930 [GH-626] Update PostgreSqlPlatform.php Resolved

 Description   

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

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

Message:

Supersedes #626.

  • Rebased with master.
  • Adopted logic for `getListNamespacesSQL()` and `getListViewsSQL()`.

If anyone has an idea of how to test this, please let me know. Otherwise I would be fine without tests... :S



 Comments   
Comment by Doctrine Bot [ 05/Nov/14 ]

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





[DBAL-1020] Postgres and using Schema tool throws cardinality errors Created: 22/Oct/14  Updated: 23/Oct/14

Status: Awaiting Feedback
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.5
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: Dominic Watson Assignee: Marco Pivetta
Resolution: Unresolved Votes: 0
Labels: ddl, postgresql

Attachments: PNG File 6ZeW_jJFUbOCx5uGJ2feANtAsZOr72zhDL4szJ6p5VE.png     File pg_catalog_pg_class.csv    

 Description   

Postgres: 9.3.5.0 (Postgres App for OSX) w/ PostGIS extensions
doctrine/common: 2.4.x-dev ae92d076442e27b6910dd86a1292a8867cf5cfe4
doctrine/dbal: dev-master 1c9c24a7e2295b71249ae2a719ce38861fccd551
creof/doctrine2-spatial: https://github.com/intellix/doctrine2-spatial 4023ca8fbe703043012c31d6df26b9bc7b0a972d

It seems every now and again when I come to use the schema-tool I'm getting exceptions which can only be fixed by dropping the database and recreating from scratch.

The following SQL looks to be generated here: \Doctrine\DBAL\Platforms\AbstractPlatform::getListTableForeignKeysSQL

SELECT quote_ident(r.conname) as conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef
                  FROM pg_catalog.pg_constraint r
                  WHERE r.conrelid =
                  (
                      SELECT c.oid
                      FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
                      WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'state' AND n.nspname = ANY(string_to_array((select replace(replace(setting,'"$user"',user),' ','') from pg_catalog.pg_settings where name = 'search_path'),',')) AND n.oid = c.relnamespace
                  )
                  AND r.contype = 'f'

The full stack trace is as follows:

 ---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~--
  Dropping database schema...
      ./bin/doctrine-module orm:schema-tool:drop --force --verbose
---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~--
Dropping database schema...


                                                                                                                                                                                                       
  [Doctrine\DBAL\Exception\DriverException]                                                                                                                                                            
  An exception occurred while executing 'SELECT quote_ident(r.conname) as conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef                                                              
                    FROM pg_catalog.pg_constraint r                                                                                                                                                    
                    WHERE r.conrelid =                                                                                                                                                                 
                    (                                                                                                                                                                                  
                        SELECT c.oid                                                                                                                                                                   
                        FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n                                                                                                                          
                        WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'state' AND n.nspname = ANY(string_to_array((select replace(replace(setting,'"$user"'  
  ,user),' ','') from pg_catalog.pg_settings where name = 'search_path'),',')) AND n.oid = c.relnamespace                                                                                              
                    )                                                                                                                                                                                  
                    AND r.contype = 'f'':                                                                                                                                                              
  SQLSTATE[21000]: Cardinality violation: 7 ERROR:  more than one row returned by a subquery used as an expression                                                                                     
                                                                                                                                                                                                       


Exception trace:
 () at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php:82
 Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:116
 Doctrine\DBAL\DBALException::driverExceptionDuringQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:833
 Doctrine\DBAL\Connection->executeQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:761
 Doctrine\DBAL\Connection->fetchAll() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:319
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:284
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:268
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:1039
 Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:783
 Doctrine\ORM\Tools\SchemaTool->getDropSchemaSQL() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:727
 Doctrine\ORM\Tools\SchemaTool->dropSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/DropCommand.php:100
 Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand->executeSchemaCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:65
 Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:252
 Symfony\Component\Console\Command\Command->run() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:891
 Symfony\Component\Console\Application->doRunCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module.php:58
 include() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module:4




                                                                                                                    
  [Doctrine\DBAL\Driver\PDOException]                                                                               
  SQLSTATE[21000]: Cardinality violation: 7 ERROR:  more than one row returned by a subquery used as an expression  
                                                                                                                    


Exception trace:
 () at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:94
 Doctrine\DBAL\Driver\PDOConnection->query() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:830
 Doctrine\DBAL\Connection->executeQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:761
 Doctrine\DBAL\Connection->fetchAll() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:319
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:284
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:268
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:1039
 Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:783
 Doctrine\ORM\Tools\SchemaTool->getDropSchemaSQL() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:727
 Doctrine\ORM\Tools\SchemaTool->dropSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/DropCommand.php:100
 Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand->executeSchemaCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:65
 Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:252
 Symfony\Component\Console\Command\Command->run() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:891
 Symfony\Component\Console\Application->doRunCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module.php:58
 include() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module:4




                                                                                                                    
  [PDOException]                                                                                                    
  SQLSTATE[21000]: Cardinality violation: 7 ERROR:  more than one row returned by a subquery used as an expression  
                                                                                                                    


Exception trace:
 () at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:92
 PDO->query() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:92
 Doctrine\DBAL\Driver\PDOConnection->query() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:830
 Doctrine\DBAL\Connection->executeQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:761
 Doctrine\DBAL\Connection->fetchAll() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:319
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:284
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:268
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:1039
 Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:783
 Doctrine\ORM\Tools\SchemaTool->getDropSchemaSQL() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:727
 Doctrine\ORM\Tools\SchemaTool->dropSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/DropCommand.php:100
 Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand->executeSchemaCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:65
 Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:252
 Symfony\Component\Console\Command\Command->run() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:891
 Symfony\Component\Console\Application->doRunCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module.php:58
 include() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module:4


orm:schema-tool:drop [--dump-sql] [-f|--force] [--full-database]


 Comments   
Comment by Marco Pivetta [ 22/Oct/14 ]

What are the contents of pg_catalog.pg_class ?

Comment by Dominic Watson [ 22/Oct/14 ]

Uploaded CSV of that table

Comment by Dominic Watson [ 22/Oct/14 ]

After running the subquery as suggested in IRC:

  SELECT c.oid                                                                                                                                                                   
                        FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n                                                                                                                          
                        WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'state' AND n.nspname = ANY(string_to_array((select replace(replace(setting,'"$user"'  
  ,user),' ','') from pg_catalog.pg_settings where name = 'search_path'),',')) AND n.oid = c.relnamespace  

oid
-------
40152
39687

Comment by Marco Pivetta [ 22/Oct/14 ]

Can you run the query:

SELECT
    c.*
FROM
   pg_catalog.pg_class c, pg_catalog.pg_namespace n
WHERE
    n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
    AND c.relname = 'state'
    AND n.nspname = ANY(string_to_array((
        select replace(replace(setting,'"$user"', user), ' ', '')
        from pg_catalog.pg_settings
        where name = 'search_path'
    ),','))
    AND n.oid = c.relnamespace
Comment by Dominic Watson [ 22/Oct/14 ]
  relname varchar,
  relnamespace oid,
  reltype oid,
  reloftype oid,
  relowner oid,
  relam oid,
  relfilenode oid,
  reltablespace oid,
  relpages int,
  reltuples real,
  relallvisible int,
  reltoastrelid oid,
  reltoastidxid oid,
  relhasindex bool,
  relisshared bool,
  relpersistence char(1),
  relkind char(1),
  relnatts smallint,
  relchecks smallint,
  relhasoids bool,
  relhaspkey bool,
  relhasrules bool,
  relhastriggers bool,
  relhassubclass bool,
  relispopulated bool,
  relfrozenxid xid,
  relminmxid xid,
  relacl _aclitem,
  reloptions _text

state,2200,40154,0,10,0,40152,0,0,0,0,0,0,true,false,p,r,2,0,false,true,false,true,false,true,6694,1,NULL,NULL
state,39587,39689,0,10,0,39687,0,0,0,0,39694,0,true,false,p,r,15,3,false,true,false,false,false,true,6629,1,NULL,NULL
Comment by Dominic Watson [ 22/Oct/14 ]

My ZF2 onBootstrap as well, in case it changes anything:

Unable to find source-code formatter for language: php. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
<?php
namespace Flatscanner;

use Doctrine\ORM\Mapping\UnderscoreNamingStrategy;
use ZF\Apigility\Provider\ApigilityProviderInterface;
use Zend\Uri\UriFactory;
use Doctrine\DBAL\Types\Type;

class Module implements ApigilityProviderInterface
{
    public function getConfig()
    {
        return include __DIR__ . '/../../config/module.config.php';
    }

    public function onBootstrap($e)
    {
        Type::addType('geometry', 'CrEOF\Spatial\DBAL\Types\GeometryType');
        Type::addType('point', 'CrEOF\Spatial\DBAL\Types\Geometry\PointType');
        UriFactory::registerScheme('chrome-extension', 'Zend\Uri\Uri');

        // Set naming strategy
        $em = $e->getTarget()->getServiceManager()->get('doctrine.entitymanager.orm_default');
        $em->getConnection()->getDatabasePlatform()->registerDoctrineTypeMapping("_text", "text"); // assuming it is a text LOB
        $em->getConfiguration()->setNamingStrategy(new UnderscoreNamingStrategy(CASE_LOWER));
    }

    public function getAutoloaderConfig()
    {
        return array(
            'ZF\Apigility\Autoloader' => array(
                'namespaces' => array(
                    __NAMESPACE__ => __DIR__,
                ),
            ),
        );
    }
}
Comment by Steve Müller [ 23/Oct/14 ]

Most probably also affects 2.4 as the codebase has not changed at the critical places. Possibly 2.3 is also affected by this. Could need a check.





[DBAL-983] [GH-670] Handle default values for boolean, datetime, and datetimetz columns in PostgreSQL Created: 27/Aug/14  Updated: 25/Jan/15  Resolved: 25/Jan/15

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

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Incomplete Votes: 0
Labels: date, datetime, postgresql, type


 Description   

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

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

Message:

When dealing with legacy schema it would be nice to be able to map default values and not have the schema spit out `ALTER` statements each time. This works correctly today for basic integer and string columns, but does not handle columns with special types such as `boolean` or `datetime`.

For example, the following statement:

`ALTER TABLE test_table ALTER test_column SET DEFAULT CURRENT_TIMESTAMP;`

Results in a column definition like:

`test_column | timestamp with time zone | not null default now()`

However, repeating the same schema generation will result in the same `ALTER` statement each time, because it will always detect that the default value has changed. The same is true for boolean columns.

This simple change prevents this situation from happening and correctly detects that the column default has not changed. It is specific to PostgreSQL.



 Comments   
Comment by Doctrine Bot [ 28/Aug/14 ]

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

Comment by Doctrine Bot [ 25/Jan/15 ]

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





[DBAL-930] [GH-626] Update PostgreSqlPlatform.php Created: 29/Jun/14  Updated: 23/Oct/14  Resolved: 23/Oct/14

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

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Incomplete Votes: 0
Labels: ddl, postgresql, schema

Issue Links:
Reference
relates to DBAL-1021 [GH-702] [DBAL 930] Only introspect a... Resolved

 Description   

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

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

Message:

If the database have different schemes, with objects, that the actual logged in user has no rights, the existing statements will collect all objects (sequences and tables) and try to read them in later steps. This will throws exceptions. The reason for that is the fact, that both procedures getListSequencesList() and getListTablesSQL() will receive all known database objects from postgres catalogs. But the actual logged-in user, maby has no read permissions to object inside other scheme-owner. The additional parts inside both sql-statements will reduce the result to only objects that the user are able to see.



 Comments   
Comment by Doctrine Bot [ 23/Oct/14 ]

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





[DBAL-879] Sequence default value [PGSQL] Created: 29/Apr/14  Updated: 26/Oct/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.



 Comments   
Comment by Steve Müller [ 26/Oct/14 ]

Can you please provide a code example of how you create table + sequence and retrieve it?





[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: 27/Mar/15  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


Issue Links:
Reference
is referenced by DBAL-1183 [GH-823] fix client_encoding setting ... Resolved

 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 Tue Jul 07 17:47:33 UTC 2015 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.