Errors with PostgreSQL

Thanks for the patch. I've committed the backtick fix, as it sure is better without it. As to the others...
- I've nothing against changing to signed ints, but a quick scan of other modules shows that CCK and comment, for example use it. Do they suffer from the same problems on Postgres as well?
- Removing GROUP BY... it doesn't look like it does anything useful, but I would appreciate someone else testing.

I've no access to Postgres to test this. Since the patch works for you, please just continue using it. Other PostgreSQL users, please test it, and post here with results. I will feel much better about committing when someone else has tested it.

Also if you're going to re-roll this patch check out http://drupal.org/patch

Leaving as code needs review, for everything except backticks.

Note in README.txt added

Glad to hear it, thank you for testing!

There should not be installation errors when using the 'unsigned' option for 'int' fields via the schema API for PostgreSQL so the fix here should not entail changing the table definition. I will take a look at installing the module on PostgreSQL myself to check as I develop on PostgreSQL and never had a problem installing modules in d6 with the unsigned int declaration.

As for the GROUP BY the question is whether or not the GROUP BY is needed. I know that alex.k took over the module so it might not be super clear why the grouping is in there but if it does need to be in there then the fix for PostgreSQL support is to add the fields in the SELECT clause to the GROUP BY clause as that's a requirement for PostgreSQL.

http://www.postgresql.org/docs/8.3/static/sql-select.html

'When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column.'

As far as that GROUP BY, I'm not really sure why it is there. I agree with the docs you quote, it's not being used properly here, but happens to work with MySQL. Really the only use of GROUP BY would be to use aggregate functions. Here I guess it is used to remove duplicate values. When grouping on rid, you won't get more than one row for each relationship - so it does the job, but is not the correct way.

I'm just hesitant to remove it if PostgreSQL happens to work with it, too, as it's the most-often used function of u_r. It would need some testcases written to really be sure behaviors don't change when GROUP BY is removed.

Latest CVS installs just fine on PostgreSQL 8.3. I'm a bit skeptical about the original issue poster's problem as I also just now bootstrapped a PostgreSQL 7.4 server and installed on that too w/o trouble.

Drupal 6 supports PostgreSQL 7 and 8 and as such has implemented support for unsigned INT in PostgreSQL in the schema API via adding CHECK constraints on columns defined with 'unsigned'.

I would not suggest changing the table field definitions as I don't believe there is a problem. If the original poster can provide additional information then it deserves a 2nd look.

The issue referenced #256001: pgsql driver does not handle unsigned numeric fields does not refer to fields defined as 'int' but rather to 'numeric' and 'float' so that should not be relevant here.

I've set as needs more info for problem #1 and the GROUP BY issue I would consider active only in the sense that it should be verified that no grouping is needed.

Marked #341232: not compatible with Postgresql? and #260941: PgSQL 7.4 SQL syntax as duplicates as they are all related to the GROUP BY issue with PostgreSQL. Patches coming soon to this issue.

Ok first set of patches.

These patches fix up a couple queries with NOT IN clauses that were bailing on PostgreSQL due to the way they were constructed. The patches make use of db_placeholders() in d6 and an equivalent function in d5.

And here's the big patch that addresses the GROUP BY issue. As long as it seems that the GROUP BY is needed or at least desired, then this patch approach will get the job done.

looks like i have to add the rest of the columns in user_relationships...this patch fixes a problem that came up with blocks.

And once more to handle block order by RAND().

Thanks for patch ...work on postgresql 8.1 drupal 6.9

other error warning while adding relationship

* warning: pg_query() [function.pg-query]: Query failed: ERROR: operator does not exist: ` character varying HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. in /home/forum/drupal-6.9/includes/database.pgsql.inc on line 139.
* user warning: query: SELECT name FROM user_relationship_types WHERE LOWER(`name`) = LOWER('fan') AND rtid != 0 in /home/forum/drupal-6.9/sites/all/modules/user_relationships/user_relationships_ui/user_relationships_ui.admin_actions.inc on line 19.

regarding #14, the backticks were removed already in CVS:

http://cvs.drupal.org/viewvc.py/drupal/contributions/modules/user_relati...

OK, next eror in psql

warning: pg_query() [function.pg-query]: Query failed: ERROR: column "user_relationship_types.name" must appear in the GROUP BY clause or be used in an aggregate function in /home/forum/drupal-6.9/includes/database.pgsql.inc on line 139.

while try to add frind

It looks like GROUP BY needs to go, it will make everyone's life easier.

@alex.k how about I go ahead and rewrite the patch to simply purge the GROUP BY?

I was going to try and do it, so if you're busy then don't worry about it :) Let me know.

I'm waiting for that

Looking into eliminating GROUP BY

Actually I just removed "GROUP BY rid" from the query, and everything seemed to work well. Since it only contains inner joins to bring in relationship type info and requester/requestee user data, I don't see how duplicates can come about. Committed all your other changes as well. Thanks.

There are a few other issues I'm running into at the moment. Let me go through them and report back here after fixes have been submitted.

Attached patch addresses one PostgreSQL issue but also addressed a secondary issue that came up when trying to fix the PostgreSQL error.

The change for PostgreSQL was simply to remove the ORDER BY clause in the query referenced in the path. The query is a COUNT query so there's no need for an ORDER BY. I also made use of the db_placeholders() where needed.

The problem I ran into which this patch also addresses is a problem with $current_relationships and the use of the 'rtid' as the key to use in the array $current_relationships as returned by user_relationships_load().

The invocation of user_relationships_load in this snippet will return an array of relationship objects with the relationship ID (rid) as the array key. That is the default for user_relationships_load if no sort is passed as a parameter.

However the code that follows assumes that the keys for the $current_relationships array are the relationship type ID (rtid). So I ran into failures in the code that followed the user_relationships_load call since the keys were rids and not rtids.

attached patch passes in a parameter to user_relationships_load to set the rtid as the sort key.

My explanation here might be a bit vague but the patch should shed light on what I mean.

Alex I see that you pushed a change to user_relationships_ui.actions.inc which makes this patch out of date already. Please review and see if the changes here make sense and if so then I can re-roll to reflect the changes in your last commit.

@jaydub looks good, please commit. Thanks!

Ok added in the last part of the patch that was not already added. This is the removal of the ORDER BY with the COUNT query and the addition of db_placeholders in that particular query.

Thank you!

I got the following error when I tried to send a friend request to a user:

warning: pg_query() [function.pg-query]: Query failed: ERROR: invalid input syntax for integer: "" in path/database.pgsql.inc on line 139.
user warning: query: SELECT DISTINCT ur.rid,ur.*,urt.* FROM user_relationships ur INNER JOIN user_relationship_types urt USING ( rtid ) WHERE ((ur.requester_id = '' AND ur.requestee_id = '') OR (urt.is_oneway <> 0 AND ur.requestee_id = '' AND ur.requester_id = '')) in path/user_relationships_api.api.inc on line 340.
warning: pg_query() [function.pg-query]: Query failed: ERROR: invalid input syntax for integer: "" in path/database.pgsql.inc on line 139.
user warning: query: SELECT DISTINCT ur.rid,ur.*,urt.* FROM user_relationships ur INNER JOIN user_relationship_types urt USING ( rtid ) WHERE ((ur.requester_id = '' AND ur.requestee_id = '') OR (urt.is_oneway <> 0 AND ur.requestee_id = '' AND ur.requester_id = '')) in path/user_relationships_api.api.inc on line 340.

It seemed to actually work fine though, and the relationship looks like it is working normally, just thought I should let you know.

Hmm, if they query was quoted verbatim, it looks like the IDs were not substituted at all? Perhaps this is from a custom block, or something else that was happening on the same page.

I think the problem exists for both PSQL and MySQL users ... lucky MySQL-users, MySQL seams not complain about the empty ID's.

The reason for the missing ID's:
The function user_relationships_ui_request (file: user_relationships_ui.forms.inc) calls user_relationships_ui_request_form with the user-ID's as parameters

  $form = user_relationships_ui_request_form($requester->uid, $requestee->uid, $form_state);

The documentation of the function user_relationships_ui_request_form does not say what type it expects (user object or just id).
This function calls _user_relationships_ui_get_requestable_rtypes (which expects according to the function documentation an object or an id) and this function calls user_relationships_load, but with the parameter $requester->uid:

  $current_relationships = user_relationships_load(array('between' => array($requester->uid, $requestee->uid)), array('sort' => 'rtid'));

Attention: $requester is already the ID and not an object (the same for $requestee).

I suggest for user_relationships_ui_request to use:

  $form = user_relationships_ui_request_form($requester, $requestee, $form_state);

because the function _user_relationships_ui_get_requestable_rtypes also calls user_relationships_api_can_request and this function is working with an object (although it also accepts id's).

Patch with above changes attached!

CHANGED Status to "needs review"

Committed http://drupal.org/cvs?commit=309106. Thank you.