Thread: 7.1 grant/revoke speed

7.1 grant/revoke speed

From
Kyle
Date:
Any reason why I should expect grants and/or revokes to be slower under
7.1RC3?

I have a script that grants all our privileges and it takes about 5 to
10 times longer to run than it did under 7.0.3.


Attachment

Need help with EXECUTE function

From
"Josh Berkus"
Date:
Folks (esp Jan, Tom & Michael):

I have a search function I'm testing, which uses the EXECUTE function to
perform a dynamic set of string comparisons according to whcih criteria
the user passes along.  Unfortunately, this requires me to triple-nest
my quotes .... and I can't seem to get it right.  No matter how I play
with the function, it keeps blowing up due to "unterminated strings".
This happens even if I terminate the function short of the EXECUTE
statement.

Can someone *please* give me some pointers?

Function text:

create function fn_search_orders ( INT4, VARCHAR, INT2, VARCHAR, DATE,
VARCHAR,INT4, INT4, VARCHAR, VARCHAR, INT4 )
RETURNS int4 AS '
DECLAREv_client ALIAS for $1;v_clname ALIAS for $2;v_status ALIAS for $3;v_datesearch ALIAS for $4;v_start ALIAS for
$5;v_addressALIAS for $6;v_contact ALIAS for $7;v_staff_usq ALIAS for $8;v_staff_name ALIAS for $9;v_temps ALIAS for
$10;v_temp_usqALIAS for $11;search_id INT4;query_string VARCHAR;where_string VARCHAR;search_count INT4;
 
BEGINsearch_id := NEXTVAL(''search_sq'');query_string := ''INSERT INTO searches ( search_sq, usq ) SELECT '' ||
CAST(search_id AS VARCHAR) 
||  '', usq FROM sv_orders WHERE '';where_string := '''';IF v_client > 0 THEN    where_string := '' AND client_usq = ''
||CAST(v_client AS varchar);END IF;IF trim(v_clname) <> '''' THEN    where_string := where_string || '' AND client_name
~*'''''''' ||
 
v_clname || 
'''''';END IF;IF v_status <> 0 THEN    where_string := where_string || '' AND status = '' || CAST(v_status AS
VARCHAR);ELSE    where_string := where_string || '' AND status > 0'';END IF;IF v_start > ''1950-01-01''::DATE THEN
IFv_datesearch = ''BEFORE'' THEN        where_string := where_string || '' AND start_date < '''''''' || 
 
to_char(v_start, ''YYYY-MM-DD'') || '''''';    ELSE        where_string := where_string || '' AND start_date > ''''''''
||
 
to_char(v_start, 
''YYYY-MM-DD'') || '''''';    END IF;END IF;IF trim(v_address) <> '''' THEN    where_string := where_string || '' AND
order_address~* '''''''' ||
 
v_address 
|| '''''';END IF;IF v_staff_usq > 0 THEN    where_string := where_string || '' AND resp_staff_usq = '' ||
CAST(v_staff_usq AS VARCHAR);END IF;IF trim(v_staff) <> '''' THEN    where_string := where_string || '' AND staff_name
~*'''''''' ||
 
v_staff || 
'''''';END IF;IF trim(v_contact) <> '''' THEN    where_string := where_string || '' AND order_contact ~* '''''''' ||
v_contact 
|| '''''';END IF;IF trim(v_temps) <> '''' THEN    where_string := where_string || '' AND list_temps ~* '''''''' ||
v_temps || 
'''''';END IF;IF v_temp_usq > 0 THEN    where_string := where_string || '' AND usq IN(SELECT order_usq FROM
assignments WHERE candidate_usq = '' || CAST(v_temp_usq AS VARCHAR) ||
'')'';END IF;where_string := substr(where_string, 5);EXECUTE query_string || where_string;SELECT count(*) INTO
search_countFROMsearches WHERE search_sq = search_id;IF search_count > 0 THEN    RETURN search_id;ELSE    RETURN 0;END
IF;
END;'
LANGUAGE 'plpgsql';

-Josh Berkus





______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


Re: Need help with EXECUTE function

From
Cedar Cox
Date:
Attached is the (I think) corrected version..  If you do like I said and
cut the number in half you see fairly quickly why it didn't work.  I'm
sending yours back so you can easily run a diff to see what I did.  Let me
know if this (attached "cedars") works.

-Cedar

On Sat, 7 Apr 2001, Josh Berkus wrote:

> Folks (esp Jan, Tom & Michael):
> 
> I have a search function I'm testing, which uses the EXECUTE function to
> perform a dynamic set of string comparisons according to whcih criteria
> the user passes along.  Unfortunately, this requires me to triple-nest
> my quotes .... and I can't seem to get it right.  No matter how I play
> with the function, it keeps blowing up due to "unterminated strings".
> This happens even if I terminate the function short of the EXECUTE
> statement.
> 
> Can someone *please* give me some pointers?
> 
> Function text:
> 
*snip*

Re: 7.1 grant/revoke speed

From
Tom Lane
Date:
Kyle <kyle@actarg.com> writes:
> Any reason why I should expect grants and/or revokes to be slower under
> 7.1RC3?

> I have a script that grants all our privileges and it takes about 5 to
> 10 times longer to run than it did under 7.0.3.

Seems unlikely that the problem is with the grants/revokes per se;
probably the slowdown is elsewhere.  Have you looked for changes in
the EXPLAIN results for the queries being used?
        regards, tom lane