[postgis-users] PostGIS 1.5.3 vs conditional trigger in non-public schema on PG 9.1.3 and PG 9.0.6 ...

[James Robinson]

Hello folks,

	Not 100% sure if this belongs here or over in pgsql-bugs, but best to start with y'all.

	I have a table with a point geometry column which also has trigger-maintained materialized relationships to spatial shape tables. I then have two triggers bound to the table to maintain the materialized relationships --- one on insert if the row was inserted with a point, and the other firing on update if and only if the point itself has changed (via WHEN). The 'create trigger' statements are as follows:

	-- hook up on insert if point was provided
	create trigger insert_manage_derivatives
		before insert on test.geocode
		for each row
		when (NEW.point is not null)
		execute procedure test.update_derivative();

	-- Likewise on update, but only need to recompute derivatives
	-- when PostGIS point has changed (possibly different coords).
	-- Note the 'when' clause depending upon operator from .
	create trigger update_manage_derivatives
		before update on test.geocode
		for each row
		when (NEW.point is distinct from OLD.point)
		execute procedure test.update_derivative();

	Note that the table is not in the public schema.

	Getting this installed worked fine, but pg_dump'ing and restoring the table is problematic --- the second trigger (the one with the WHEN clause) fails to restore:

	ERROR: operator is not unique: public.geometry = public.geometry
	LINE 1: ...E UPDATE ON geocode FOR EACH ROW WHEN ((new.point IS DISTINC... 
	HINT: Could not choose a best candidate operator. You might need to add explicit type casts. 

	Attached is a SQL file to reproduce the issue, it creates a new database and installs postgis-1.5(3) into said DB (you may need to tweak the paths to the postgis installation scripts), then builds similar tables and triggers. The initial SQL plays fine, but dumping and restoring fails to reproduce that trigger:

	% pg_dump test_db > test_db_dump.sql
	% psql -c "create database test_db2;"
	% psql -f test.dump.sql test_db2
	....
	psql:test.dump.sql:15420: ERROR: operator is not unique: public.geometry = public.geometry
	LINE 1: ...E UPDATE ON geocode FOR EACH ROW WHEN ((new.point IS DISTINC... ^
	HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
	ALTER TABLE
	REVOKE
	REVOKE
	GRANT
	GRANT 
	% 


Can y'all reproduce this, and would you characterize it as an issue with pg_dump? It seems to be related to how pg_dump is manipulating the search path prior to restoring the trigger --- if I rewrite the directly preceding search path from:
	SET search_path = test, pg_catalog;
to
	SET search_path = test, public, pg_catalog;

then the trigger gets installed properly.

In the mean time, I'll rewrite the actual trigger not need a WHEN clause, but this would have been nice.

Thanks for your time and great software!


-------------- next part --------------
A non-text attachment was scrubbed...
Name: test_case.sql
Type: application/octet-stream
Size: 2597 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120315/fcd5750c/attachment.obj>
-------------- next part --------------


----
James Robinson
Socialserve.com