CCK, Views Field Aliases too long for PostgreSQL [#371711]

I've been getting strange PHP notices from added a cck field called project_screenshot to a content type of mine. After backtracing for awhile I discovered this extract from a var_dump:

object(stdClass)[100]
          public 'nid' => string '29' (length=2)
          public 'node_title' => string 'PHP Webquest' (length=12)
          public 'node_revisions_teaser' => string '<p>PHP Webquest is a Webquest Generator that allows teachers to create webquests without the need of writing any HTML code or using web page editors. The program supports images uploading, and resizes images is neccesary. A HTML editor is provided in order to format the texts for the pages.</p>

<p> </p>' (length=306)
          public 'node_revisions_format' => string '4' (length=1)
          public 'node_data_field_project_screenshot_field_project_screenshot_fid' => null
          public 'node_data_field_project_screenshot_field_project_screenshot_lis' => null
          public 'node_data_field_project_screenshot_field_project_screenshot_dat' => null
          public 'node_data_field_project_screenshot_nid' => null
          public 'node_type' => string 'project' (length=7)

you'll notice that some of the keys from the this are truncated a bit short of the full name. node_data_field_project_screenshot_field_project_screenshot_dat should be node_data_field_project_screenshot_field_project_screenshot_data. Whilst a small difference, its enough to create these notices and what ever else issues that that may cause. At first I thought it was a PHP issue but this extract comes from $views->result which is a result from a database.

PostgreSQL was truncating the keys to 63 chars:

 select 'hello' as node_data_field_project_screenshot_field_project_screenshot_data;
NOTICE:  identifier "node_data_field_project_screenshot_field_project_screenshot_data" will be truncated to "node_data_field_project_screenshot_field_project_screenshot_dat"
 node_data_field_project_screenshot_field_project_screenshot_dat 
-----------------------------------------------------------------
 hello
(1 row)

Where to from here? If someone can point me in the right direction I can try fixing this, in the meantime, all that can be done it shorting the lenght of the cck field names

Comments

Comment #1

halcyonCorsair commented 9 February 2009 at 23:22

http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html#SQL-SY... mentions how to change this limit before compiling postgresql, however that is not a very good solution for most people.

What direction do people feel should be taken re: aliasing? ie. truncated table aliases, or non-obvious aliases such as alias1, alias2, etc?

Comment #2

pokadan commented 18 March 2009 at 11:08

Truncated field aliases is probably not the solution. Consider the above Views generated query :

SELECT node.nid AS nid,
node.title AS node_title,
node_data_field_category_description.field_category_description_value AS node_data_field_category_description_field_category_description_value,
node_data_field_category_description.field_category_description_format AS node_data_field_category_description_field_category_description_format,
node.type AS node_type,
node.vid AS node_vid
FROM node node
LEFT JOIN content_type_categoryinfo node_data_field_category_description ON node.vid = node_data_field_category_description.vid
WHERE node.type in ('categoryinfo')
ORDER BY node_title ASC

You would end up with two identically named aliases node_data_field_category_description_field_category_description
(wouldn't work)

Comment #3

thekevinday commented 8 April 2009 at 19:39

subscribing

Comment #4

josh waihi commented 7 July 2009 at 02:19

Issue tags:

+PostgreSQL Code Sprint

Lets get a solution rocking here, marking for PostgreSQL Code Sprint

Comment #5

axolx commented 16 August 2009 at 00:53

Running into the same issue. While not an elegant solution, I was able to get around this by leveraging hook_views_pre_execute somewhat like this:

function mymodule_views_pre_execute(&$view) {
    if($view->name !="problem_view") return; 
    $f = "/node_node_data_field_really_long_alias_that_postgres_was_not_liking_at_all/";
    $r = 'nndf';
    $view->build_info['query'] = preg_replace($f, $r, $view->build_info['query']) ;
    $view->build_info['count_query'] = preg_replace($f, $r, $view->build_info['count_query']);
}

Hope this helps someone someday :)

Comment #6

0xAFFE commented 23 November 2009 at 11:58

I have this issue too. I think the best spot to fix this issue, would be the views-module itself, where the aliases are generated. Maybe if the db-driver is postgres and the alias is > 63 chars, views generates an alias like ...foo_blar_blub__1 and ...foo_blar_blub__2, if they have identical endings.

Comment #7

0xAFFE commented 27 November 2009 at 18:09

I have written a patch. Please check http://drupal.org/node/571548#comment-2312818

Comment #8

josh waihi commented 21 December 2009 at 04:24

+        $safe_alias_prefix = substr($field['alias'],0,60);

That doesn't seem right, if anything the alias should be shorted from the end rather than from the beginning, for example, consider these aliases:

some_really_long_module_alias_actual_table_name_nid
some_really_long_module_alias_actual_table_name_vid
some_really_long_module_alias_actual_table_name_title

only the ends are different, if you cut them off then you have a problem.

Comment #9

0xAFFE commented 4 January 2010 at 11:11

Thats a valid point Josh, but it is already covered via the $safe_alias_counter. So the aliases are some_really_long_module_alias_actual_0, some_really_long_module_alias_actual_1, some_really_long_module_alias_actual_2.