pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWierded error in recursive function; debugging ideas?
5+ messages / 2 participants
[nested] [flat]
* Wierded error in recursive function; debugging ideas?
@ 2004-08-11 04:56 Josh Berkus <josh@agliodbs.com>
2004-08-11 05:05 ` Re: Wierded error in recursive function; debugging ideas? Joe Conway <mail@joeconway.com>
2004-08-11 22:18 ` Re: Wierded error in recursive function; debugging ideas? Joe Conway <mail@joeconway.com>
0 siblings, 2 replies; 5+ messages in thread
From: Josh Berkus @ 2004-08-11 04:56 UTC (permalink / raw)
To: pgsql-sql; +Cc: mail@joeconway.com
Folks,
I'm seeing this bizarre, unreproducable error in my logs:
[2] Wrong datatype for second argument in call to in_array
SQL: SELECT sf_event_decendants(66645,111)
The problem is that it's proven completely impossible to reproduce this error
at test time; it only seems to happen in production, and then only one out of
about 200 times the function is called (although when the function errors it
does seem to error 3-5 times in a row).
sf_event_descendants is a recursive function which maps out which child events
of the current event are visible to the current user. It does use arrays.
I did try tinkering with some of the functions internals without apparent
effect.
I also checked for in_array and it's not a visible built-in function. Is this
maybe a PostgreSQL bug? Version is 7.4.1
--
-Josh Berkus
"A developer of Very Little Brain"
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Wierded error in recursive function; debugging ideas?
2004-08-11 04:56 Wierded error in recursive function; debugging ideas? Josh Berkus <josh@agliodbs.com>
@ 2004-08-11 05:05 ` Joe Conway <mail@joeconway.com>
2004-08-11 17:58 ` Re: Wierded error in recursive function; debugging ideas? Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 5+ messages in thread
From: Joe Conway @ 2004-08-11 05:05 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
Josh Berkus wrote:
> I'm seeing this bizarre, unreproducable error in my logs:
>
> [2] Wrong datatype for second argument in call to in_array
> SQL: SELECT sf_event_decendants(66645,111)
> I also checked for in_array and it's not a visible built-in function. Is this
> maybe a PostgreSQL bug? Version is 7.4.1
>
There is no in_array() function in Postgres that I'm aware of -- you
sure that isn't array_in()? The rest of that error message doesn't seem
to be there in 7.4 either. Can we see the function?
Joe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Wierded error in recursive function; debugging ideas?
2004-08-11 04:56 Wierded error in recursive function; debugging ideas? Josh Berkus <josh@agliodbs.com>
2004-08-11 05:05 ` Re: Wierded error in recursive function; debugging ideas? Joe Conway <mail@joeconway.com>
@ 2004-08-11 17:58 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2004-08-11 17:58 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-sql
Joe,
> There is no in_array() function in Postgres that I'm aware of -- you
> sure that isn't array_in()?
Yep. That's a cut-and-paste of the exact log message.
> The rest of that error message doesn't seem
> to be there in 7.4 either. Can we see the function?
Sure:
CREATE OR REPLACE FUNCTION "sf_event_decendants" (integer,integer)
RETURNS text AS '
DECLARE v_event ALIAS for $1;
v_user ALIAS for $2;
child_list INT[];
sub_child TEXT;
child_rec RECORD;
p_status INT;
contfrom INT;
BEGIN
child_list := ARRAY [ 0 ];
SELECT status INTO p_status
FROM events WHERE event_id = v_event;
IF p_status < 0 THEN
p_status = -99;
ELSE
p_status = 0;
END IF;
FOR child_rec IN SELECT event_id FROM events
WHERE parent_id = v_event AND status > p_status LOOP
child_list := child_rec.event_id || child_list;
IF v_user <> 0 THEN
IF if_get_lock(v_user, ''events'', child_rec.event_id, NULL) <> ''OK'' THEN
RETURN ''LOCKED: One or more of the child events of the current event are
locked by '' ||
''another user at this time. You cannot proceed.'';
END IF;
END IF;
END LOOP;
FOR child_rec IN SELECT event_id FROM events
WHERE (continued_id = v_event or event_id = COALESCE(contfrom, 0)) and status
> p_status LOOP
child_list := child_rec.event_id || child_list;
IF v_user <> 0 THEN
IF if_get_lock(v_user, ''events'', child_rec.event_id, NULL) <> ''OK'' THEN
RETURN ''LOCKED: One or more of the child events of the current event are
locked by '' ||
''another user at this time. You cannot proceed.'';
END IF;
END IF;
END LOOP;
IF child_list = ARRAY[0] THEN
RETURN ''0'';
END IF;
FOR child_rec IN SELECT event_id FROM events
WHERE status > p_status
AND event_id = ANY ( child_list )
LOOP
sub_child := sf_event_decendants(child_rec.event_id, v_user);
IF sub_child <> ''0'' THEN
child_list := child_list || string_to_array(sub_child, '','')::INT[];
IF v_user <> 0 THEN
IF if_get_lock(v_user, ''events'', child_rec.event_id, NULL) <> ''OK'' THEN
RETURN ''LOCKED: One or more of the child events of the current event are
locked by '' ||
''another user at this time. You cannot proceed.'';
END IF;
END IF;
END IF;
END LOOP;
RETURN array_to_string(child_list, '','');
END;' LANGUAGE 'plpgsql';
--
-Josh Berkus
"A developer of Very Little Brain"
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Wierded error in recursive function; debugging ideas?
2004-08-11 04:56 Wierded error in recursive function; debugging ideas? Josh Berkus <josh@agliodbs.com>
@ 2004-08-11 22:18 ` Joe Conway <mail@joeconway.com>
2004-08-11 22:52 ` Re: Wierded error in recursive function; debugging ideas? Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 5+ messages in thread
From: Joe Conway @ 2004-08-11 22:18 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
Josh Berkus wrote:
> [2] Wrong datatype for second argument in call to in_array
> SQL: SELECT sf_event_decendants(66645,111)
>
Are you sure this message isn't coming from some PHP middleware, e.g.
peardb or something. See:
http://us2.php.net/manual/en/function.in-array.php
Joe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Wierded error in recursive function; debugging ideas?
2004-08-11 04:56 Wierded error in recursive function; debugging ideas? Josh Berkus <josh@agliodbs.com>
2004-08-11 22:18 ` Re: Wierded error in recursive function; debugging ideas? Joe Conway <mail@joeconway.com>
@ 2004-08-11 22:52 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2004-08-11 22:52 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-sql
Joe,
> Are you sure this message isn't coming from some PHP middleware, e.g.
> peardb or something. See:
> http://us2.php.net/manual/en/function.in-array.php
Hmmmmm ... possible. Will check with my PHP guy.
Would explain why I've not been able to track down the error.
--
-Josh Berkus
"A developer of Very Little Brain"
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-08-11 22:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-11 04:56 Wierded error in recursive function; debugging ideas? Josh Berkus <josh@agliodbs.com>
2004-08-11 05:05 ` Joe Conway <mail@joeconway.com>
2004-08-11 17:58 ` Josh Berkus <josh@agliodbs.com>
2004-08-11 22:18 ` Joe Conway <mail@joeconway.com>
2004-08-11 22:52 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox