views database update warnings on postgresql

subscribing to this. Query needs to be something like UPDATE {views_object_cache} SET data = CAST(data_old AS TEXT)

I've tested and confirmed that this patch fixes the issue above. However this is a Core issue and not a view issue.

There is a duplicate issue post over at http://drupal.org/node/370957

I'm glad that you have found the problem and fixed it for future generations,, but exactly how would someone fix the error if they run into it now and it is causing their site to malfunction?

@sriddering,

Open includes/database.pgsql.inc, and find this around line 915:
$ret[] = update_sql("UPDATE {". $table ."} SET $field_new = ". $field ."_old");

and replace it with:

$map =  db_type_map();
$typecast = $map[$spec['type'] . ':' . $spec['size']];
$ret[] = update_sql('UPDATE {' . $table . '} SET ' . $field_new . ' = CAST(' . $field . '_old AS ' . $typecast . ')');

Make sure you back up the file before editing just in case you make a mistake and break your site.

@sriddering: untill this patch gets commited, people that use db_change_column (i think thats what its called) in such a way will lose the old data unfortunately since this isn't done in a transaction. no way to get it back :( fortunately for this context, the data isn't that important and views will still be ok nevertheless

Since this breaks the 2.2 to 2.3 upgrade for pgsql users I'm upping this to critical.

@merlinofchaos: that may explain the lack of interest :D

It may be that we need to have ti fixed for D7 and then backported, except I have no idea how different db_change_column even is in D7.

hmm, this maybe problematic for situations, for example, converting INT to CHAR:

psql> select CAST(57 AS CHAR);
 bpchar 
--------
 5
(1 row)

however it is how PostgreSQL deals with such a situation. I'm sure we can be any more accurate without a whole bunch of logic that says IF casting from type x to type y do this ELSE IF and so on.

Josh informed me that he hasn't run the pgsql tests to make sure this doesn't break them. Marking this down to code needs review until that happens.

better. fixed stuff. message I wrote did post before :(

Ok, committed to HEAD. Needs porting to 6.x.

somethings up with commenting and posting patches at the same time :(

Let's not loose the code comment in Drupal 6!

@Gabor Hojtsy you mean you want comments to describe whats happening?

Why is the code comment in the Drupal 7 patch would not be applicable to Drupal 6? How can you make be applicable?

+comments

Fixed code style according to D6 standards (whitespace around concatenation), and committed this one: http://drupal.org/cvs?commit=222930 Thanks!