[Bucardo-general] Patch to Fix Pg_class Trigger Disable/Enable & Reserved Word Table Names Problem

[Ben Allen]

In PostgreSQL 8.2, with pg_class trigger disabling/enabling and table  
names that are reserved words like set and sequence, there is a bug  
that the SQL executed doesn't actually disable/enable triggers. This  
is because in the SQL query where the table name is used its set as  
'"reserved_word"'. Note the double quotes, surrounded by single  
quotes. This is because of code around line 3832 in Bucardo.pm uses $_- 
 >{safetable}, which is smart enough to know that the word is  
reserved, and such puts it in quotes. The problem being when used in a  
SQL query as part of a WHERE statement the table name just needs to  
have single quotes even if the word is reserved. My fix to this is to  
use regex to remove the quotes from the start and end of the string of  
$_->{safetable}. Another option would be to use $_->{tablename}. I'm  
not sure which would be preferable, as I'm not familiar with what is  
done to the name the table to make it safe other than putting the name  
in double quotes. If thats the only thing ever done to the string,  
than likely tablename should be used. Patch is attached.

Also, I haven't looked through the rest of code base to see if there  
is any other instance of this bug, but it seems likely there may be a  
few other occurrences.

Example of bad query, notice two instances of '"set"':

UPDATE pg_catalog.pg_class
SET    reltriggers = (SELECT count(*) FROM pg_catalog.pg_trigger WHERE  
tgrelid = pg_catalog.pg_class.oid),relhasrules = CASE WHEN (SELECT  
COUNT(*) FROM pg_catalog.pg_rules WHERE schemaname='public' AND  
tablename='"set"') > 0THEN true ELSE false END
FROM   pg_catalog.pg_namespace
WHERE  pg_catalog.pg_namespace.oid = relnamespace
AND    nspname = 'public'
AND    relname = '"set"'

Regards,

Ben Allen
-------------- next part --------------
A non-text attachment was scrubbed...
Name: safetable_pg_class_trigger.patch
Type: application/octet-stream
Size: 1748 bytes
Desc: not available
Url : https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20090317/f8af8fd3/attachment.obj