On Fri, 8 Jan 2010, Richard Yen wrote:

> Hello,
>
> Just wanted to see if anyone else has experienced this with postgres
> 8.4 and slony 2.0.3 rc3...seems like double quotes are being removed
> from queries.  In my syslog output below, you will see double quotes
> around "window" because in postgres 8.4, it seems that "window" is a
> reserved word.

Yes I am seeing this as well.

If I am replicating from a 8.3 master to a 9.0 (I'd expect the same with 
8.4) slave and I've compiled slony to use the ScanKeywordLookup function.

"window" wouldn't be a keyword in the 8.3 environment but it is when 
replicating to 8.4+.  We are invoking ScanKeywordLookup on the master.

I've also noticed that the autoconf stuff for ScanKeywordLookup seems to be 
a bit broken.

1) I don't see the SCANKEYWORDLOOKUP_x being defined in config.h.in
2) I don't think config/acx_libpq.m4 is actually checking for the 1 argument 
version of the function.

The attached patch should address 1 and 2, but leaves open the question of 
how to quote keywords that are added to in versions beyond the master. 
Disable ScanKeywordLookup (and always quote) or maintain our own list of 
'new' keywords are the two options that immediately come to mind.


Steve




>
>> Jan  8 14:09:39 global-db1 postgres[30423]: [19-1] 2010-01-08
>> 14:09:39.612 PST [user=USER,db=DB IPADDR PID:30423 XID:0]LOG:
>> execute dbdpg_p4760_2: INSERT
>> Jan  8 14:09:39 global-db1 postgres[30423]: [19-2]  INTO node (id,
>> description, pid_host, pid_host_secondary, pid_port, "window",
>> disk_host, disk_host_secondary, disk_port,
>> Jan  8 14:09:39 global-db1 postgres[30423]: [19-3]  rep_disk_dir,
>> rep_disk_host, rep_disk_host_secondary, rep_disk_port, is_opt_out,
>> collection_name)
>> Jan  8 14:09:39 global-db1 postgres[30423]: [19-4]      SELECT $1,
>> $2, pid_host, pid_host_secondary, pid_port, "window", disk_host,
>> disk_host_secondary, disk_port, rep_disk_dir,
>> Jan  8 14:09:39 global-db1 postgres[30423]: [19-5]  rep_disk_host,
>> rep_disk_host_secondary, rep_disk_port, true, 'submitted_work'
>> Jan  8 14:09:39 global-db1 postgres[30423]: [19-6]      FROM   node
>> WHERE id = $3
>> Jan  8 14:09:39 global-db1 postgres[30423]: [19-7]
>> Jan  8 14:09:39 global-db1 postgres[30423]: [19-8] 2010-01-08
>> 14:09:39.612 PST [user=USER,db=DB IPADDR PID:30423 XID:0]DETAIL:
>> parameters: $1 = '545', $2
>> Jan  8 14:09:39 global-db1 postgres[30423]: [19-9]  = 'Institution
>> Name', $3 = '1'
>
> Apparently, the insertion works, but when it is to be replicated to
> the subscribers, it fails because the double quotes around "window"
> are removed:
>
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-1] 2010-01-08
>> 14:19:00 PST ERROR  remoteWorkerThread_1: "insert into
>> "ip_global"."node"
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-2]
>> (id
>> ,description
>> ,created_time
>> ,is_private
>> ,is_opt_out
>> ,pid_host
>> ,pid_host_secondary,pid_port,window,disk_host,disk_host_secondary,di
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-3]
>> sk_port
>> ,crawl_rep_host
>> ,crawl_rep_port
>> ,is_active
>> ,rep_disk_dir
>> ,rep_disk_host,rep_disk_host_secondary,rep_disk_port,crawl_rep_disk_
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-4]
>> host
>> ,crawl_rep_disk_dir
>> ,crawl_rep_disk_port
>> ,crawl_pid_host
>> ,crawl_pid_port,crawl_pid_host_secondary,collection,collection_name)
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-5]  values
>> ('545','Institution Name','2010-01-08
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-6]
>> 14:09:39.60761-08','f','t','xxx','xxx','3100','4','xxx
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-7]
>> ','xxx,'4010',NULL,NULL,'t','papers/regular/','xxx','xxx
>> Jan  8 14:19:00 global-db3 slon[24792]:
>> [5000640
>> -8],'4010',NULL,NULL,NULL,'xxx','4101','xxx',NULL,'submitted_work');
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-9] " ERROR:  syntax
>> error at or near "window"
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-10] LINE
>> 1: ...e,is_opt_out,pid_host,pid_host_secondary,pid_port,window,dis...
>> Jan  8 14:19:00 global-db3 slon[24792]:
>> [5000640
>> -11]                                                              ^
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-12]  -
>> qualification was: where log_origin = 1 and (  (
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-13]  log_tableid in
>> (1,2,3,4,5,6,7)
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-14]     and
>> (log_txid < '143242577' and
>> "pg_catalog".txid_visible_in_snapshot(log_txid,
>> '143242577:143242577:'))
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-15]     and
>> (log_txid >= '143242563' or log_txid IN (select * from
>> "pg_catalog".txid_snapshot_xip('143242563:143242563:')))
>> Jan  8 14:19:00 global-db3 slon[24792]: [5000640-16] ) )
>
> Can someone confirm that this is indeed a bug?
> --Richard Yen
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: scankeywords.diff
Type: text/x-diff
Size: 1311 bytes
Desc: 
Url : http://lists.slony.info/pipermail/slony1-general/attachments/20100214/d23bd147/attachment.diff