On postgres 9.1 I have many errors in log that looks like:
PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "ct" LINE 5: WHERE (base.nid IN ('ct')) ^: SELECT revision.vid AS vid, base.uid AS uid, revision.title AS title, revision.log AS log, revision.status AS status, revision.comment AS comment, revision.promote AS promote, revision.sticky AS sticky, base.nid AS nid, base.type AS type, base.language AS language, base.created AS created, base.changed AS changed, base.tnid AS tnid, base.translate AS translate, revision.timestamp AS revision_timestamp, revision.uid AS revision_uid FROM {node} base INNER JOIN {node_revision} revision ON revision.vid = base.vid WHERE (base.nid IN (:db_condition_placeholder_0)) ; Array ( [:db_condition_placeholder_0] => ct ) w DrupalDefaultEntityController->load() (linia 196 z /includes/entity.inc).

This is related with postgresql casting. Postgresql can't cast 'ct' to integer.

Files: 
CommentFileSizeAuthor
#5 1932612_5_non_numeric_entity_ids.patch595 bytesgoogletorp
PASSED: [[SimpleTest]]: [MySQL] 40,946 pass(es).
[ View ]
#1 1932612_1_non_numeric_entity_ids.patch556 bytesgoogletorp
FAILED: [[SimpleTest]]: [MySQL] 40,006 pass(es), 23 fail(s), and 6,523 exception(s).
[ View ]

Comments

googletorp’s picture

Status:Active» Needs review
StatusFileSize
new556 bytes
FAILED: [[SimpleTest]]: [MySQL] 40,006 pass(es), 23 fail(s), and 6,523 exception(s).
[ View ]

I've seen this problem - with all drupal entities that do loading from an url argument.

So if you fx go to user/foo, drupal will try to load the user with id "foo" which will cause an exception in Postgres - MySQL will instead silently fail and return 0 results.

A simple solution is to enforce numeric ids when loading entities. This is not strictly required to have numeric ids for entities, but the field system (and many other things) require entity ids to be numeric. So enforcing it in the default entity class: DrupalDefaultEntityController, should be just fine.

I've attached a simple patch that fixes the issue by removing all non numeric ids in the load function before doing anything.

Status:Needs review» Needs work

The last submitted patch, 1932612_1_non_numeric_entity_ids.patch, failed testing.

googletorp’s picture

Version:7.19» 7.x-dev

Update drupal version

googletorp’s picture

Status:Needs work» Needs review
googletorp’s picture

StatusFileSize
new595 bytes
PASSED: [[SimpleTest]]: [MySQL] 40,946 pass(es).
[ View ]

Updated the patch, to work when loading all entities.

scottm316’s picture

Fixes the problem for me and I'm using postgres 8.4.16/Drupal 7.22.

Pancho’s picture

Status:Needs review» Closed (duplicate)

This is a duplicate of the D8 issue #1823494: Field API assumes serial/integer entity IDs, but the entity system does not, which seems to take the other direction allowing varchar IDs everywhere.
Let's see how this is solved there and then backport. I'm placing a note on the D8 issue.

ShadowMonster’s picture

It is still issue for 7.25 and changes from path must be applied

slippast’s picture

This issue is still hanging around in 7.26.

Good thing I noted this fix. This problem makes the Media module nearly impossible to use. On a side note, Drupal + Postgres = suffering.

EuroDomenii’s picture

Sorry to argue... Except this issue, Drupal 7 + Postgres works just fine, tested with a lot of modules. Even with Commerce Kickstart distribution is perfect.

stefan.r’s picture

Status:Closed (duplicate)» Needs review
stefan.r’s picture

Issue summary:View changes
Status:Needs review» Closed (duplicate)
leyonce’s picture

Using commercekickstart7.x.2.20 with postgres 9.1 on ubuntu 12.04. Nice Patch! It worked beautifully. Thanks.

Liam Morland’s picture

Issue tags:+PostgreSQL
tobiasb’s picture

Status:Closed (duplicate)» Needs review

I do not see the related issue therefore I re-opened this issue.

David_Rothstein’s picture

Status:Needs review» Closed (duplicate)

#7 points out that this is a duplicate of #1823494: Field API assumes serial/integer entity IDs, but the entity system does not, although I think it's really a duplicate of #1003788: PostgreSQL: PDOException:Invalid text representation when attempting to load an entity with a string or non-scalar ID instead. That issue is older and contains a patch that is farther along than this one (we can't completely assume that non-integer IDs are not allowed).