Topic: Problem with Membergroups & Permissions

[ChaosX128]

Hi I just successfully installed this software on my site (ver 2.0.2).  I'm using PostgreSQL

I'm still trying to figure out how to set up everything the way I want it and have encounter 2 problems so far in regards to errors.

The first error occurs in the "Forum » Administration Center » Membergroups » Edit Membergroups" section.

I can add Membergroups and see them listed, but whenever I go to the Edit Membergroups I get this error:

ERROR: function find_in_set(smallint, character varying) is not unique
HINT: Could not choose a best candidate function. You may need to add explicit type casts.
File: /home/chaosxfo/public_html/forum/Sources/Subs-Membergroups.php
Line: 736

No idea what to do there...

2nd error occurs in the "Forum » Administration Center » Permissions » General Permissions" section.

I get the error:

ERROR: function find_in_set(smallint, character varying) is not unique
HINT: Could not choose a best candidate function. You may need to add explicit type casts.
File: /home/chaosxfo/public_html/forum/Sources/ManagePermissions.php
Line: 306

No idea what to do here either.

If anyone could please help or explain to me why or what these errors mean, I'd really appreciate it.

Thanks.

[emanuele]

Hello ChaosX128 and welcome to sm.org!

find_in_set is a custom function created by SMF during the install.
Two find_in_set functions are created for two different type of data (string and integers).

What version of postgre are you using?

[ChaosX128]

Hello ChaosX128 and welcome to sm.org!

find_in_set is a custom function created by SMF during the install.
Two find_in_set functions are created for two different type of data (string and integers).

What version of postgre are you using?

It says version 8.1.23 on the phpPgAdmin page.

[Oldiesmann]

If you go into phpPgAdmin and view the functions for that particular database, how many different "find_in_set" functions do you see listed?

There should only be two - one for "integer, character varying" and one for "text, text".

[ChaosX128]

I wasn't sure exactly how to fix this problem... It only had two functions listed with the name "find_in_set" as you described.

However, I just reinstalled using mySQL instead and it works fine now.

Maybe a problem with the way I set up the PostgreSQL, idk... 

[ChaosX128]

Ok so I decided to do a fresh install in a test directory just to see if this a bug using PostgreSQL.

One "find_in_set" function is "text, text" and the other is "integer, text"

Is that supposed to be the same as character varying?

[emanuele]

The two find_in_set should be used by postgre depending on the type of data sent (integers or text).

[Oldiesmann]

Did some more testing with this.

The error is due to PostgreSQL's habit of being extremely picky with data types...

The first parameter is a "smallint", but we don't have a function to explicitly handle a parameter of that type. PostgreSQL can automatically handle conversion from "smallint" to "integer" and from "smallint" to "text". The problem here is that PostgreSQL doesn't know which function to use.

The simple solution is to just add yet another find_in_set function which explicitly handles smallint:

Code: [Select]

CREATE OR REPLACE FUNCTION FIND_IN_SET(needle smallint, haystack text) RETURNS integer AS '
SELECT i AS result
FROM generate_series(1, array_upper(string_to_array($2,'',''), 1)) AS g(i)
WHERE  (string_to_array($2,'',''))[i] = CAST($1 AS text)
UNION ALL
SELECT 0
LIMIT 1'
LANGUAGE 'sql';
That will fix the issue. I'll commit the change in a bit.

[emanuele]

For the record: committed (ff3655588dc23fe5cfd2bb2ab8735b1d11a30d88).