[Bucardo-general] Add Sync Error

[Ed Snajder]

I really appreciate the responses so far!
I think we're close! It looks like perhaps the variable 'tablename' as a similar conflict? By the way, the source DB is 8.3.12. Below is the logged error:

2012-04-18 01:39:05 EDT postgres 127.0.0.1(59486):26689 ERROR:  syntax error at or near "$1" at character 47
2012-04-18 01:39:05 EDT postgres 127.0.0.1(59486):26689 QUERY:  INSERT INTO bucardo.bucardo_truncate_trigger( $1 ,sname,tname,sync) VALUES ( $2 ,  $3 ,  $4 ,  $5 )
2012-04-18 01:39:05 EDT postgres 127.0.0.1(59486):26689 CONTEXT:  SQL statement in PL/PgSQL function "bucardo_note_truncation" near line 6
2012-04-18 01:39:05 EDT postgres 127.0.0.1(59486):26689 STATEMENT:   CREATE OR REPLACE FUNCTION bucardo.bucardo_note_truncation()
	RETURNS TRIGGER
	LANGUAGE plpgsql
	SECURITY DEFINER
	AS $clone$
	DECLARE
	  tablename TEXT;
	  myst TEXT;
	BEGIN
	  INSERT INTO bucardo.bucardo_truncate_trigger(tablename,sname,tname,sync)
	    VALUES (TG_RELID, TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0]);
	   SELECT INTO tablename
	    bucardo.bucardo_tablename_maker(TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, 'delta_');
	  myst = 'TRUNCATE TABLE bucardo.' || tablename;
	  EXECUTE myst;
	   SELECT INTO tablename
	    bucardo.bucardo_tablename_maker(TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, 'track_');
	  myst = 'TRUNCATE TABLE bucardo.' || tablename;
	  EXECUTE myst;
	   -- Not strictly necessary, but nice to have a clean slate
	  SELECT INTO tablename
	    bucardo.bucardo_tablename_maker(TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, 'stage_');
	  myst = 'TRUNCATE TABLE bucardo.' || tablename;
	  EXECUTE myst;
	   RETURN NEW;
	END;
	$clone$;

Let  me know if I can provide any other information.
Thanks!
Ed
-----Original Message-----
From: Greg Sabino Mullane [mailto:greg at endpoint.com] 
Sent: Tuesday, April 17, 2012 3:06 PM
To: Ed Snajder
Cc: bucardo-general at bucardo.org
Subject: Re: [Bucardo-general] Add Sync Error

I think I found the problem - we were using 'tname' as both a local plpgsql variable and as a column name. I just pushed a fix in 6ba621aaeb88f301a752e8813e4af8964f346829. The diff to bucardo.schema is also attached to this email.

--
Greg Sabino Mullane greg at endpoint.com
End Point Corporation
PGP Key: 0x14964AC8