pg_upgrade diffs on WIndows
I realized this morning that I might have been a bit cavalier in using
dos2unix to smooth away differences in the dumpfiles produced by
pg_upgrade. Attached is a dump of the diff if this isn't done, with
Carriage Returns printed as '*' to make them visible. As can be seen, in
function bodies dump2 has the Carriage Returns doubled. I have not had
time to delve into how this comes about, and I need to attend to some
income-producing activity for a bit, but I'd like to get it cleaned up
ASAP. We are under the hammer for 9.2, so any help other people can give
on this would be appreciated.
cheers
andrew
Attachments:
diff.outtext/plain; charset=UTF-8; name=diff.outDownload
--- tmp_check/dump1.sql 2012-09-04 14:55:08 -0400
+++ tmp_check/dump2.sql 2012-09-04 14:55:48 -0400
@@ -672,16 +672,16 @@
*
CREATE FUNCTION add_group(grp anyarray, ad anyelement, size integer) RETURNS anyarray*
LANGUAGE plpgsql IMMUTABLE*
- AS $$*
-begin*
- if grp is null then*
- return array[ad];*
- end if;*
- if array_upper(grp, 1) < size then*
- return grp || ad;*
- end if;*
- return grp;*
-end;*
+ AS $$**
+begin**
+ if grp is null then**
+ return array[ad];**
+ end if;**
+ if array_upper(grp, 1) < size then**
+ return grp || ad;**
+ end if;**
+ return grp;**
+end;**
$$;*
*
*
@@ -715,8 +715,8 @@
*
CREATE FUNCTION array_to_set(anyarray) RETURNS SETOF record*
LANGUAGE sql IMMUTABLE STRICT*
- AS $_$*
- select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i*
+ AS $_$**
+ select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i**
$_$;*
*
*
@@ -739,14 +739,14 @@
*
CREATE FUNCTION binary_coercible(oid, oid) RETURNS boolean*
LANGUAGE sql STABLE STRICT*
- AS $_$*
-SELECT ($1 = $2) OR*
- EXISTS(select 1 from pg_catalog.pg_cast where*
- castsource = $1 and casttarget = $2 and*
- castmethod = 'b' and castcontext = 'i') OR*
- ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND*
- EXISTS(select 1 from pg_catalog.pg_type where*
- oid = $1 and typelem != 0 and typlen = -1))*
+ AS $_$**
+SELECT ($1 = $2) OR**
+ EXISTS(select 1 from pg_catalog.pg_cast where**
+ castsource = $1 and casttarget = $2 and**
+ castmethod = 'b' and castcontext = 'i') OR**
+ ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND**
+ EXISTS(select 1 from pg_catalog.pg_type where**
+ oid = $1 and typelem != 0 and typlen = -1))**
$_$;*
*
*
@@ -758,10 +758,10 @@
*
CREATE FUNCTION bleat(integer) RETURNS integer*
LANGUAGE plpgsql*
- AS $_$*
-begin*
- raise notice 'bleat %', $1;*
- return $1;*
+ AS $_$**
+begin**
+ raise notice 'bleat %', $1;**
+ return $1;**
end$_$;*
*
*
@@ -773,26 +773,26 @@
*
CREATE FUNCTION blockme() RETURNS integer*
LANGUAGE plpgsql*
- AS $$*
-declare x int;*
-begin*
- x := 1;*
- insert into foo values(x);*
- begin*
- x := x + 1;*
- insert into foo values(x);*
- -- we assume this will take longer than 2 seconds:*
- select count(*) into x from tenk1 a, tenk1 b, tenk1 c;*
- exception*
- when others then*
- raise notice 'caught others?';*
- return -1;*
- when query_canceled then*
- raise notice 'nyeah nyeah, can''t stop me';*
- x := x * 10;*
- end;*
- insert into foo values(x);*
- return x;*
+ AS $$**
+declare x int;**
+begin**
+ x := 1;**
+ insert into foo values(x);**
+ begin**
+ x := x + 1;**
+ insert into foo values(x);**
+ -- we assume this will take longer than 2 seconds:**
+ select count(*) into x from tenk1 a, tenk1 b, tenk1 c;**
+ exception**
+ when others then**
+ raise notice 'caught others?';**
+ return -1;**
+ when query_canceled then**
+ raise notice 'nyeah nyeah, can''t stop me';**
+ x := x * 10;**
+ end;**
+ insert into foo values(x);**
+ return x;**
end$$;*
*
*
@@ -826,18 +826,18 @@
*
CREATE FUNCTION cache_test(integer) RETURNS integer*
LANGUAGE plpgsql*
- AS $_$*
-declare total int;*
-begin*
- create temp table t1(f1 int);*
- insert into t1 values($1);*
- insert into t1 values(11);*
- insert into t1 values(12);*
- insert into t1 values(13);*
- select sum(f1) into total from t1;*
- drop table t1;*
- return total;*
-end*
+ AS $_$**
+declare total int;**
+begin**
+ create temp table t1(f1 int);**
+ insert into t1 values($1);**
+ insert into t1 values(11);**
+ insert into t1 values(12);**
+ insert into t1 values(13);**
+ select sum(f1) into total from t1;**
+ drop table t1;**
+ return total;**
+end**
$_$;*
*
*
@@ -849,9 +849,9 @@
*
CREATE FUNCTION cache_test_2() RETURNS integer*
LANGUAGE plpgsql*
- AS $$*
-begin*
- return f1 from v1;*
+ AS $$**
+begin**
+ return f1 from v1;**
end$$;*
*
*
@@ -863,15 +863,15 @@
*
CREATE FUNCTION cachebug() RETURNS void*
LANGUAGE plpgsql*
- AS $$*
-declare r int;*
-begin*
- drop table if exists temptable cascade;*
- create temp table temptable as select * from generate_series(1,3) as f1;*
- create temp view vv as select * from temptable;*
- for r in select * from vv loop*
- raise notice '%', r;*
- end loop;*
+ AS $$**
+declare r int;**
+begin**
+ drop table if exists temptable cascade;**
+ create temp table temptable as select * from generate_series(1,3) as f1;**
+ create temp view vv as select * from temptable;**
+ for r in select * from vv loop**
+ raise notice '%', r;**
+ end loop;**
end$$;*
*
*
@@ -916,12 +916,12 @@
*
CREATE FUNCTION city_delete() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- DELETE FROM city_table WHERE city_id = OLD.city_id;*
- if NOT FOUND then RETURN NULL; end if;*
- RETURN OLD;*
-end;*
+ AS $$**
+begin**
+ DELETE FROM city_table WHERE city_id = OLD.city_id;**
+ if NOT FOUND then RETURN NULL; end if;**
+ RETURN OLD;**
+end;**
$$;*
*
*
@@ -933,31 +933,31 @@
*
CREATE FUNCTION city_insert() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-declare*
- ctry_id int;*
-begin*
- if NEW.country_name IS NOT NULL then*
- SELECT country_id, continent INTO ctry_id, NEW.continent*
- FROM country_table WHERE country_name = NEW.country_name;*
- if NOT FOUND then*
- raise exception 'No such country: "%"', NEW.country_name;*
- end if;*
- else*
- NEW.continent := NULL;*
- end if;*
-*
- if NEW.city_id IS NOT NULL then*
- INSERT INTO city_table*
- VALUES(NEW.city_id, NEW.city_name, NEW.population, ctry_id);*
- else*
- INSERT INTO city_table(city_name, population, country_id)*
- VALUES(NEW.city_name, NEW.population, ctry_id)*
- RETURNING city_id INTO NEW.city_id;*
- end if;*
-*
- RETURN NEW;*
-end;*
+ AS $$**
+declare**
+ ctry_id int;**
+begin**
+ if NEW.country_name IS NOT NULL then**
+ SELECT country_id, continent INTO ctry_id, NEW.continent**
+ FROM country_table WHERE country_name = NEW.country_name;**
+ if NOT FOUND then**
+ raise exception 'No such country: "%"', NEW.country_name;**
+ end if;**
+ else**
+ NEW.continent := NULL;**
+ end if;**
+**
+ if NEW.city_id IS NOT NULL then**
+ INSERT INTO city_table**
+ VALUES(NEW.city_id, NEW.city_name, NEW.population, ctry_id);**
+ else**
+ INSERT INTO city_table(city_name, population, country_id)**
+ VALUES(NEW.city_name, NEW.population, ctry_id)**
+ RETURNING city_id INTO NEW.city_id;**
+ end if;**
+**
+ RETURN NEW;**
+end;**
$$;*
*
*
@@ -969,31 +969,31 @@
*
CREATE FUNCTION city_update() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-declare*
- ctry_id int;*
-begin*
- if NEW.country_name IS DISTINCT FROM OLD.country_name then*
- SELECT country_id, continent INTO ctry_id, NEW.continent*
- FROM country_table WHERE country_name = NEW.country_name;*
- if NOT FOUND then*
- raise exception 'No such country: "%"', NEW.country_name;*
- end if;*
-*
- UPDATE city_table SET city_name = NEW.city_name,*
- population = NEW.population,*
- country_id = ctry_id*
- WHERE city_id = OLD.city_id;*
- else*
- UPDATE city_table SET city_name = NEW.city_name,*
- population = NEW.population*
- WHERE city_id = OLD.city_id;*
- NEW.continent := OLD.continent;*
- end if;*
-*
- if NOT FOUND then RETURN NULL; end if;*
- RETURN NEW;*
-end;*
+ AS $$**
+declare**
+ ctry_id int;**
+begin**
+ if NEW.country_name IS DISTINCT FROM OLD.country_name then**
+ SELECT country_id, continent INTO ctry_id, NEW.continent**
+ FROM country_table WHERE country_name = NEW.country_name;**
+ if NOT FOUND then**
+ raise exception 'No such country: "%"', NEW.country_name;**
+ end if;**
+**
+ UPDATE city_table SET city_name = NEW.city_name,**
+ population = NEW.population,**
+ country_id = ctry_id**
+ WHERE city_id = OLD.city_id;**
+ else**
+ UPDATE city_table SET city_name = NEW.city_name,**
+ population = NEW.population**
+ WHERE city_id = OLD.city_id;**
+ NEW.continent := OLD.continent;**
+ end if;**
+**
+ if NOT FOUND then RETURN NULL; end if;**
+ RETURN NEW;**
+end;**
$$;*
*
*
@@ -1005,11 +1005,11 @@
*
CREATE FUNCTION doubledecrement(p1 pos_int) RETURNS pos_int*
LANGUAGE plpgsql*
- AS $$*
-declare v pos_int := 1;*
-begin*
- v := p1 - 1;*
- return v - 1;*
+ AS $$**
+declare v pos_int := 1;**
+begin**
+ v := p1 - 1;**
+ return v - 1;**
end$$;*
*
*
@@ -1021,12 +1021,12 @@
*
CREATE FUNCTION dummy_update_func() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-BEGIN*
- RAISE NOTICE 'dummy_update_func(%) called: action = %, old = %, new = %',*
- TG_ARGV[0], TG_OP, OLD, NEW;*
- RETURN NEW;*
-END;*
+ AS $$**
+BEGIN**
+ RAISE NOTICE 'dummy_update_func(%) called: action = %, old = %, new = %',**
+ TG_ARGV[0], TG_OP, OLD, NEW;**
+ RETURN NEW;**
+END;**
$$;*
*
*
@@ -1143,24 +1143,24 @@
*
CREATE FUNCTION execute_into_test(character varying) RETURNS record*
LANGUAGE plpgsql*
- AS $_$*
-declare*
- _r record;*
- _rt eifoo%rowtype;*
- _v eitype;*
- i int;*
- j int;*
- k int;*
-begin*
- execute 'insert into '||$1||' values(10,15)';*
- execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;*
- raise notice '% %', _r.i, _r.y;*
- execute 'select * from '||$1||' limit 1' into _rt;*
- raise notice '% %', _rt.i, _rt.y;*
- execute 'select *, 20 from '||$1||' limit 1' into i, j, k;*
- raise notice '% % %', i, j, k;*
- execute 'select 1,2' into _v;*
- return _v;*
+ AS $_$**
+declare**
+ _r record;**
+ _rt eifoo%rowtype;**
+ _v eitype;**
+ i int;**
+ j int;**
+ k int;**
+begin**
+ execute 'insert into '||$1||' values(10,15)';**
+ execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;**
+ raise notice '% %', _r.i, _r.y;**
+ execute 'select * from '||$1||' limit 1' into _rt;**
+ raise notice '% %', _rt.i, _rt.y;**
+ execute 'select *, 20 from '||$1||' limit 1' into i, j, k;**
+ raise notice '% % %', i, j, k;**
+ execute 'select 1,2' into _v;**
+ return _v;**
end; $_$;*
*
*
@@ -1205,30 +1205,30 @@
*
CREATE FUNCTION for_vect() RETURNS void*
LANGUAGE plpgsql*
- AS $_$*
-<<lbl>>declare a integer; b varchar; c varchar; r record;*
-begin*
- -- fori*
- for i in 1 .. 3 loop*
- raise notice '%', i;*
- end loop;*
- -- fore with record var*
- for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop*
- raise notice '% % %', r.aa, r.bb, r.cc;*
- end loop;*
- -- fore with single scalar*
- for a in select gs from generate_series(1,4) gs loop*
- raise notice '%', a;*
- end loop;*
- -- fore with multiple scalars*
- for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop*
- raise notice '% % %', a, b, c;*
- end loop;*
- -- using qualified names in fors, fore is enabled, disabled only for fori*
- for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop*
- raise notice '% % %', a, b, c;*
- end loop;*
-end;*
+ AS $_$**
+<<lbl>>declare a integer; b varchar; c varchar; r record;**
+begin**
+ -- fori**
+ for i in 1 .. 3 loop**
+ raise notice '%', i;**
+ end loop;**
+ -- fore with record var**
+ for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop**
+ raise notice '% % %', r.aa, r.bb, r.cc;**
+ end loop;**
+ -- fore with single scalar**
+ for a in select gs from generate_series(1,4) gs loop**
+ raise notice '%', a;**
+ end loop;**
+ -- fore with multiple scalars**
+ for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop**
+ raise notice '% % %', a, b, c;**
+ end loop;**
+ -- using qualified names in fors, fore is enabled, disabled only for fori**
+ for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop**
+ raise notice '% % %', a, b, c;**
+ end loop;**
+end;**
$_$;*
*
*
@@ -1374,8 +1374,8 @@
*
CREATE FUNCTION make_table() RETURNS void*
LANGUAGE sql*
- AS $$*
- CREATE TABLE created_table AS SELECT * FROM int8_tbl;*
+ AS $$**
+ CREATE TABLE created_table AS SELECT * FROM int8_tbl;**
$$;*
*
*
@@ -1398,13 +1398,13 @@
*
CREATE FUNCTION multi_datum_use(p1 integer) RETURNS boolean*
LANGUAGE plpgsql*
- AS $_$*
-declare*
- x int;*
- y int;*
-begin*
- select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1;*
- return x = y;*
+ AS $_$**
+declare**
+ x int;**
+ y int;**
+begin**
+ select into x,y unique1/p1, unique1/$1 from tenk1 group by unique1/p1;**
+ return x = y;**
end$_$;*
*
*
@@ -1417,11 +1417,11 @@
CREATE FUNCTION myfunc(integer) RETURNS text*
LANGUAGE plpgsql*
SET work_mem TO '1MB'*
- AS $_$*
-begin*
- set work_mem = '2MB';*
- perform 1/$1;*
- return current_setting('work_mem');*
+ AS $_$**
+begin**
+ set work_mem = '2MB';**
+ perform 1/$1;**
+ return current_setting('work_mem');**
end $_$;*
*
*
@@ -1433,8 +1433,8 @@
*
CREATE FUNCTION myleast(VARIADIC anyarray) RETURNS anyelement*
LANGUAGE sql IMMUTABLE STRICT*
- AS $_$*
- select min($1[i]) from generate_subscripts($1,1) g(i)*
+ AS $_$**
+ select min($1[i]) from generate_subscripts($1,1) g(i)**
$_$;*
*
*
@@ -1446,20 +1446,20 @@
*
CREATE FUNCTION namedparmcursor_test1(integer, integer) RETURNS boolean*
LANGUAGE plpgsql*
- AS $_$*
-declare*
- c1 cursor (param1 int, param12 int) for select * from rc_test where a > param1 and b > param12;*
- nonsense record;*
-begin*
- open c1(param12 := $2, param1 := $1);*
- fetch c1 into nonsense;*
- close c1;*
- if found then*
- return true;*
- else*
- return false;*
- end if;*
-end*
+ AS $_$**
+declare**
+ c1 cursor (param1 int, param12 int) for select * from rc_test where a > param1 and b > param12;**
+ nonsense record;**
+begin**
+ open c1(param12 := $2, param1 := $1);**
+ fetch c1 into nonsense;**
+ close c1;**
+ if found then**
+ return true;**
+ else**
+ return false;**
+ end if;**
+end**
$_$;*
*
*
@@ -1471,20 +1471,20 @@
*
CREATE FUNCTION namedparmcursor_test2(integer, integer) RETURNS boolean*
LANGUAGE plpgsql*
- AS $_$*
-declare*
- c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;*
- nonsense record;*
-begin*
- open c1(param1 := $1, $2);*
- fetch c1 into nonsense;*
- close c1;*
- if found then*
- return true;*
- else*
- return false;*
- end if;*
-end*
+ AS $_$**
+declare**
+ c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;**
+ nonsense record;**
+begin**
+ open c1(param1 := $1, $2);**
+ fetch c1 into nonsense;**
+ close c1;**
+ if found then**
+ return true;**
+ else**
+ return false;**
+ end if;**
+end**
$_$;*
*
*
@@ -1496,12 +1496,12 @@
*
CREATE FUNCTION namedparmcursor_test7() RETURNS void*
LANGUAGE plpgsql*
- AS $$*
-declare*
- c1 cursor (p1 int, p2 int) for*
- select * from tenk1 where thousand = p1 and tenthous = p2;*
-begin*
- open c1 (p2 := 77, p1 := 42/0);*
+ AS $$**
+declare**
+ c1 cursor (p1 int, p2 int) for**
+ select * from tenk1 where thousand = p1 and tenthous = p2;**
+begin**
+ open c1 (p2 := 77, p1 := 42/0);**
end $$;*
*
*
@@ -1513,16 +1513,16 @@
*
CREATE FUNCTION namedparmcursor_test8() RETURNS integer*
LANGUAGE plpgsql*
- AS $$*
-declare*
- c1 cursor (p1 int, p2 int) for*
- select count(*) from tenk1 where thousand = p1 and tenthous = p2;*
- n int4;*
-begin*
- open c1 (77 -- test*
- , 42);*
- fetch c1 into n;*
- return n;*
+ AS $$**
+declare**
+ c1 cursor (p1 int, p2 int) for**
+ select count(*) from tenk1 where thousand = p1 and tenthous = p2;**
+ n int4;**
+begin**
+ open c1 (77 -- test**
+ , 42);**
+ fetch c1 into n;**
+ return n;**
end $$;*
*
*
@@ -1534,17 +1534,17 @@
*
CREATE FUNCTION namedparmcursor_test9(p1 integer) RETURNS integer*
LANGUAGE plpgsql*
- AS $$*
-declare*
- c1 cursor (p1 int, p2 int, debug int) for*
- select count(*) from tenk1 where thousand = p1 and tenthous = p2*
- and four = debug;*
- p2 int4 := 1006;*
- n int4;*
-begin*
- open c1 (p1 := p1, p2 := p2, debug := 2);*
- fetch c1 into n;*
- return n;*
+ AS $$**
+declare**
+ c1 cursor (p1 int, p2 int, debug int) for**
+ select count(*) from tenk1 where thousand = p1 and tenthous = p2**
+ and four = debug;**
+ p2 int4 := 1006;**
+ n int4;**
+begin**
+ open c1 (p1 := p1, p2 := p2, debug := 2);**
+ fetch c1 into n;**
+ return n;**
end $$;*
*
*
@@ -1578,10 +1578,10 @@
*
CREATE FUNCTION noticetrigger() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- raise notice 'noticetrigger % %', new.f1, new.data;*
- return null;*
+ AS $$**
+begin**
+ raise notice 'noticetrigger % %', new.f1, new.data;**
+ return null;**
end $$;*
*
*
@@ -1641,25 +1641,25 @@
*
CREATE FUNCTION perform_test_func() RETURNS void*
LANGUAGE plpgsql*
- AS $$*
-BEGIN*
- IF FOUND then*
- INSERT INTO perform_test VALUES (100, 100);*
- END IF;*
-*
- PERFORM simple_func(5);*
-*
- IF FOUND then*
- INSERT INTO perform_test VALUES (100, 100);*
- END IF;*
-*
- PERFORM simple_func(50);*
-*
- IF FOUND then*
- INSERT INTO perform_test VALUES (100, 100);*
- END IF;*
-*
- RETURN;*
+ AS $$**
+BEGIN**
+ IF FOUND then**
+ INSERT INTO perform_test VALUES (100, 100);**
+ END IF;**
+**
+ PERFORM simple_func(5);**
+**
+ IF FOUND then**
+ INSERT INTO perform_test VALUES (100, 100);**
+ END IF;**
+**
+ PERFORM simple_func(50);**
+**
+ IF FOUND then**
+ INSERT INTO perform_test VALUES (100, 100);**
+ END IF;**
+**
+ RETURN;**
END;$$;*
*
*
@@ -1671,14 +1671,14 @@
*
CREATE FUNCTION physically_coercible(oid, oid) RETURNS boolean*
LANGUAGE sql STABLE STRICT*
- AS $_$*
-SELECT ($1 = $2) OR*
- EXISTS(select 1 from pg_catalog.pg_cast where*
- castsource = $1 and casttarget = $2 and*
- castmethod = 'b') OR*
- ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND*
- EXISTS(select 1 from pg_catalog.pg_type where*
- oid = $1 and typelem != 0 and typlen = -1))*
+ AS $_$**
+SELECT ($1 = $2) OR**
+ EXISTS(select 1 from pg_catalog.pg_cast where**
+ castsource = $1 and casttarget = $2 and**
+ castmethod = 'b') OR**
+ ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND**
+ EXISTS(select 1 from pg_catalog.pg_type where**
+ oid = $1 and typelem != 0 and typlen = -1))**
$_$;*
*
*
@@ -1690,44 +1690,44 @@
*
CREATE FUNCTION pslot_backlink_view(character) RETURNS text*
LANGUAGE plpgsql*
- AS $_$*
-<<outer>>*
-declare*
- rec record;*
- bltype char(2);*
- retval text;*
-begin*
- select into rec * from PSlot where slotname = $1;*
- if not found then*
- return '';*
- end if;*
- if rec.backlink = '' then*
- return '-';*
- end if;*
- bltype := substr(rec.backlink, 1, 2);*
- if bltype = 'PL' then*
- declare*
- rec record;*
- begin*
- select into rec * from PLine where slotname = "outer".rec.backlink;*
- retval := 'Phone line ' || trim(rec.phonenumber);*
- if rec.comment != '' then*
- retval := retval || ' (';*
- retval := retval || rec.comment;*
- retval := retval || ')';*
- end if;*
- return retval;*
- end;*
- end if;*
- if bltype = 'WS' then*
- select into rec * from WSlot where slotname = rec.backlink;*
- retval := trim(rec.slotname) || ' in room ';*
- retval := retval || trim(rec.roomno);*
- retval := retval || ' -> ';*
- return retval || wslot_slotlink_view(rec.slotname);*
- end if;*
- return rec.backlink;*
-end;*
+ AS $_$**
+<<outer>>**
+declare**
+ rec record;**
+ bltype char(2);**
+ retval text;**
+begin**
+ select into rec * from PSlot where slotname = $1;**
+ if not found then**
+ return '';**
+ end if;**
+ if rec.backlink = '' then**
+ return '-';**
+ end if;**
+ bltype := substr(rec.backlink, 1, 2);**
+ if bltype = 'PL' then**
+ declare**
+ rec record;**
+ begin**
+ select into rec * from PLine where slotname = "outer".rec.backlink;**
+ retval := 'Phone line ' || trim(rec.phonenumber);**
+ if rec.comment != '' then**
+ retval := retval || ' (';**
+ retval := retval || rec.comment;**
+ retval := retval || ')';**
+ end if;**
+ return retval;**
+ end;**
+ end if;**
+ if bltype = 'WS' then**
+ select into rec * from WSlot where slotname = rec.backlink;**
+ retval := trim(rec.slotname) || ' in room ';**
+ retval := retval || trim(rec.roomno);**
+ retval := retval || ' -> ';**
+ return retval || wslot_slotlink_view(rec.slotname);**
+ end if;**
+ return rec.backlink;**
+end;**
$_$;*
*
*
@@ -1739,35 +1739,35 @@
*
CREATE FUNCTION pslot_slotlink_view(character) RETURNS text*
LANGUAGE plpgsql*
- AS $_$*
-declare*
- psrec record;*
- sltype char(2);*
- retval text;*
-begin*
- select into psrec * from PSlot where slotname = $1;*
- if not found then*
- return '';*
- end if;*
- if psrec.slotlink = '' then*
- return '-';*
- end if;*
- sltype := substr(psrec.slotlink, 1, 2);*
- if sltype = 'PS' then*
- retval := trim(psrec.slotlink) || ' -> ';*
- return retval || pslot_backlink_view(psrec.slotlink);*
- end if;*
- if sltype = 'HS' then*
- retval := comment from Hub H, HSlot HS*
- where HS.slotname = psrec.slotlink*
- and H.name = HS.hubname;*
- retval := retval || ' slot ';*
- retval := retval || slotno::text from HSlot*
- where slotname = psrec.slotlink;*
- return retval;*
- end if;*
- return psrec.slotlink;*
-end;*
+ AS $_$**
+declare**
+ psrec record;**
+ sltype char(2);**
+ retval text;**
+begin**
+ select into psrec * from PSlot where slotname = $1;**
+ if not found then**
+ return '';**
+ end if;**
+ if psrec.slotlink = '' then**
+ return '-';**
+ end if;**
+ sltype := substr(psrec.slotlink, 1, 2);**
+ if sltype = 'PS' then**
+ retval := trim(psrec.slotlink) || ' -> ';**
+ return retval || pslot_backlink_view(psrec.slotlink);**
+ end if;**
+ if sltype = 'HS' then**
+ retval := comment from Hub H, HSlot HS**
+ where HS.slotname = psrec.slotlink**
+ and H.name = HS.hubname;**
+ retval := retval || ' slot ';**
+ retval := retval || slotno::text from HSlot**
+ where slotname = psrec.slotlink;**
+ return retval;**
+ end if;**
+ return psrec.slotlink;**
+end;**
$_$;*
*
*
@@ -1790,11 +1790,11 @@
*
CREATE FUNCTION raise_test1(integer) RETURNS integer*
LANGUAGE plpgsql*
- AS $_$*
-begin*
- raise notice 'This message has too many parameters!', $1;*
- return $1;*
-end;*
+ AS $_$**
+begin**
+ raise notice 'This message has too many parameters!', $1;**
+ return $1;**
+end;**
$_$;*
*
*
@@ -1806,11 +1806,11 @@
*
CREATE FUNCTION raise_test2(integer) RETURNS integer*
LANGUAGE plpgsql*
- AS $_$*
-begin*
- raise notice 'This message has too few parameters: %, %, %', $1, $1;*
- return $1;*
-end;*
+ AS $_$**
+begin**
+ raise notice 'This message has too few parameters: %, %, %', $1, $1;**
+ return $1;**
+end;**
$_$;*
*
*
@@ -1844,14 +1844,14 @@
*
CREATE FUNCTION recurse(double precision) RETURNS double precision*
LANGUAGE plpgsql*
- AS $_$*
-begin*
- if ($1 > 0) then*
- return sql_recurse($1 - 1);*
- else*
- return $1;*
- end if;*
-end;*
+ AS $_$**
+begin**
+ if ($1 > 0) then**
+ return sql_recurse($1 - 1);**
+ else**
+ return $1;**
+ end if;**
+end;**
$_$;*
*
*
@@ -1863,15 +1863,15 @@
*
CREATE FUNCTION recursion_test(integer, integer) RETURNS text*
LANGUAGE plpgsql*
- AS $_$*
-DECLARE rslt text;*
-BEGIN*
- IF $1 <= 0 THEN*
- rslt = CAST($2 AS TEXT);*
- ELSE*
- rslt = CAST($1 AS TEXT) || ',' || recursion_test($1 - 1, $2);*
- END IF;*
- RETURN rslt;*
+ AS $_$**
+DECLARE rslt text;**
+BEGIN**
+ IF $1 <= 0 THEN**
+ rslt = CAST($2 AS TEXT);**
+ ELSE**
+ rslt = CAST($1 AS TEXT) || ',' || recursion_test($1 - 1, $2);**
+ END IF;**
+ RETURN rslt;**
END;$_$;*
*
*
@@ -1883,11 +1883,11 @@
*
CREATE FUNCTION refcursor_test1(refcursor) RETURNS refcursor*
LANGUAGE plpgsql*
- AS $_$*
-begin*
- perform return_refcursor($1);*
- return $1;*
-end*
+ AS $_$**
+begin**
+ perform return_refcursor($1);**
+ return $1;**
+end**
$_$;*
*
*
@@ -1899,20 +1899,20 @@
*
CREATE FUNCTION refcursor_test2(integer, integer) RETURNS boolean*
LANGUAGE plpgsql*
- AS $_$*
-declare*
- c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;*
- nonsense record;*
-begin*
- open c1($1, $2);*
- fetch c1 into nonsense;*
- close c1;*
- if found then*
- return true;*
- else*
- return false;*
- end if;*
-end*
+ AS $_$**
+declare**
+ c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;**
+ nonsense record;**
+begin**
+ open c1($1, $2);**
+ fetch c1 into nonsense;**
+ close c1;**
+ if found then**
+ return true;**
+ else**
+ return false;**
+ end if;**
+end**
$_$;*
*
*
@@ -1935,24 +1935,24 @@
*
CREATE FUNCTION reraise_test() RETURNS void*
LANGUAGE plpgsql*
- AS $$*
-BEGIN*
- BEGIN*
- RAISE syntax_error;*
- EXCEPTION*
- WHEN syntax_error THEN*
- BEGIN*
- raise notice 'exception % thrown in inner block, reraising', sqlerrm;*
- RAISE;*
- EXCEPTION*
- WHEN OTHERS THEN*
- raise notice 'RIGHT - exception % caught in inner block', sqlerrm;*
- END;*
- END;*
-EXCEPTION*
- WHEN OTHERS THEN*
- raise notice 'WRONG - exception % caught in outer block', sqlerrm;*
-END;*
+ AS $$**
+BEGIN**
+ BEGIN**
+ RAISE syntax_error;**
+ EXCEPTION**
+ WHEN syntax_error THEN**
+ BEGIN**
+ raise notice 'exception % thrown in inner block, reraising', sqlerrm;**
+ RAISE;**
+ EXCEPTION**
+ WHEN OTHERS THEN**
+ raise notice 'RIGHT - exception % caught in inner block', sqlerrm;**
+ END;**
+ END;**
+EXCEPTION**
+ WHEN OTHERS THEN**
+ raise notice 'WRONG - exception % caught in outer block', sqlerrm;**
+END;**
$$;*
*
*
@@ -1964,14 +1964,14 @@
*
CREATE FUNCTION ret_query1(OUT integer, OUT integer) RETURNS SETOF record*
LANGUAGE plpgsql*
- AS $_$*
-begin*
- $1 := -1;*
- $2 := -2;*
- return next;*
- return query select x + 1, x * 10 from generate_series(0, 10) s (x);*
- return next;*
-end;*
+ AS $_$**
+begin**
+ $1 := -1;**
+ $2 := -2;**
+ return next;**
+ return query select x + 1, x * 10 from generate_series(0, 10) s (x);**
+ return next;**
+end;**
$_$;*
*
*
@@ -1983,11 +1983,11 @@
*
CREATE FUNCTION ret_query2(lim integer) RETURNS SETOF record_type*
LANGUAGE plpgsql*
- AS $$*
-begin*
- return query select md5(s.x::text), s.x, s.x > 0*
- from generate_series(-8, lim) s (x) where s.x % 2 = 0;*
-end;*
+ AS $$**
+begin**
+ return query select md5(s.x::text), s.x, s.x > 0**
+ from generate_series(-8, lim) s (x) where s.x % 2 = 0;**
+end;**
$$;*
*
*
@@ -1999,11 +1999,11 @@
*
CREATE FUNCTION return_refcursor(rc refcursor) RETURNS refcursor*
LANGUAGE plpgsql*
- AS $$*
-begin*
- open rc for select a from rc_test;*
- return rc;*
-end*
+ AS $$**
+begin**
+ open rc for select a from rc_test;**
+ return rc;**
+end**
$$;*
*
*
@@ -2037,13 +2037,13 @@
*
CREATE FUNCTION serializable_update_trig() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-declare*
- rec record;*
-begin*
- new.description = 'updated in trigger';*
- return new;*
-end;*
+ AS $$**
+declare**
+ rec record;**
+begin**
+ new.description = 'updated in trigger';**
+ return new;**
+end;**
$$;*
*
*
@@ -2066,14 +2066,14 @@
*
CREATE FUNCTION simple_func(integer) RETURNS boolean*
LANGUAGE plpgsql*
- AS $_$*
-BEGIN*
- IF $1 < 20 THEN*
- INSERT INTO perform_test VALUES ($1, $1 + 10);*
- RETURN TRUE;*
- ELSE*
- RETURN FALSE;*
- END IF;*
+ AS $_$**
+BEGIN**
+ IF $1 < 20 THEN**
+ INSERT INTO perform_test VALUES ($1, $1 + 10);**
+ RETURN TRUE;**
+ ELSE**
+ RETURN FALSE;**
+ END IF;**
END;$_$;*
*
*
@@ -2085,7 +2085,7 @@
*
CREATE FUNCTION sql_if(boolean, anyelement, anyelement) RETURNS anyelement*
LANGUAGE sql*
- AS $_$*
+ AS $_$**
select case when $1 then $2 else $3 end $_$;*
*
*
@@ -2163,39 +2163,39 @@
*
CREATE FUNCTION test_found() RETURNS boolean*
LANGUAGE plpgsql*
- AS $$*
- declare*
- begin*
- insert into found_test_tbl values (1);*
- if FOUND then*
- insert into found_test_tbl values (2);*
- end if;*
-*
- update found_test_tbl set a = 100 where a = 1;*
- if FOUND then*
- insert into found_test_tbl values (3);*
- end if;*
-*
- delete from found_test_tbl where a = 9999; -- matches no rows*
- if not FOUND then*
- insert into found_test_tbl values (4);*
- end if;*
-*
- for i in 1 .. 10 loop*
- -- no need to do anything*
- end loop;*
- if FOUND then*
- insert into found_test_tbl values (5);*
- end if;*
-*
- -- never executes the loop*
- for i in 2 .. 1 loop*
- -- no need to do anything*
- end loop;*
- if not FOUND then*
- insert into found_test_tbl values (6);*
- end if;*
- return true;*
+ AS $$**
+ declare**
+ begin**
+ insert into found_test_tbl values (1);**
+ if FOUND then**
+ insert into found_test_tbl values (2);**
+ end if;**
+**
+ update found_test_tbl set a = 100 where a = 1;**
+ if FOUND then**
+ insert into found_test_tbl values (3);**
+ end if;**
+**
+ delete from found_test_tbl where a = 9999; -- matches no rows**
+ if not FOUND then**
+ insert into found_test_tbl values (4);**
+ end if;**
+**
+ for i in 1 .. 10 loop**
+ -- no need to do anything**
+ end loop;**
+ if FOUND then**
+ insert into found_test_tbl values (5);**
+ end if;**
+**
+ -- never executes the loop**
+ for i in 2 .. 1 loop**
+ -- no need to do anything**
+ end loop;**
+ if not FOUND then**
+ insert into found_test_tbl values (6);**
+ end if;**
+ return true;**
end;$$;*
*
*
@@ -2207,17 +2207,17 @@
*
CREATE FUNCTION test_ret_rec_dyn(integer) RETURNS record*
LANGUAGE plpgsql*
- AS $_$*
-DECLARE*
- retval RECORD;*
-BEGIN*
- IF $1 > 10 THEN*
- SELECT INTO retval 5, 10, 15;*
- RETURN retval;*
- ELSE*
- SELECT INTO retval 50, 5::numeric, 'xxx'::text;*
- RETURN retval;*
- END IF;*
+ AS $_$**
+DECLARE**
+ retval RECORD;**
+BEGIN**
+ IF $1 > 10 THEN**
+ SELECT INTO retval 5, 10, 15;**
+ RETURN retval;**
+ ELSE**
+ SELECT INTO retval 50, 5::numeric, 'xxx'::text;**
+ RETURN retval;**
+ END IF;**
END;$_$;*
*
*
@@ -2229,20 +2229,20 @@
*
CREATE FUNCTION test_ret_set_rec_dyn(integer) RETURNS SETOF record*
LANGUAGE plpgsql*
- AS $_$*
-DECLARE*
- retval RECORD;*
-BEGIN*
- IF $1 > 10 THEN*
- SELECT INTO retval 5, 10, 15;*
- RETURN NEXT retval;*
- RETURN NEXT retval;*
- ELSE*
- SELECT INTO retval 50, 5::numeric, 'xxx'::text;*
- RETURN NEXT retval;*
- RETURN NEXT retval;*
- END IF;*
- RETURN;*
+ AS $_$**
+DECLARE**
+ retval RECORD;**
+BEGIN**
+ IF $1 > 10 THEN**
+ SELECT INTO retval 5, 10, 15;**
+ RETURN NEXT retval;**
+ RETURN NEXT retval;**
+ ELSE**
+ SELECT INTO retval 50, 5::numeric, 'xxx'::text;**
+ RETURN NEXT retval;**
+ RETURN NEXT retval;**
+ END IF;**
+ RETURN;**
END;$_$;*
*
*
@@ -2254,14 +2254,14 @@
*
CREATE FUNCTION test_ret_set_scalar(integer, integer) RETURNS SETOF integer*
LANGUAGE plpgsql*
- AS $_$*
-DECLARE*
- i int;*
-BEGIN*
- FOR i IN $1 .. $2 LOOP*
- RETURN NEXT i + 1;*
- END LOOP;*
- RETURN;*
+ AS $_$**
+DECLARE**
+ i int;**
+BEGIN**
+ FOR i IN $1 .. $2 LOOP**
+ RETURN NEXT i + 1;**
+ END LOOP;**
+ RETURN;**
END;$_$;*
*
*
@@ -2297,14 +2297,14 @@
*
CREATE FUNCTION test_table_func_rec() RETURNS SETOF found_test_tbl*
LANGUAGE plpgsql*
- AS $$*
-DECLARE*
- rec RECORD;*
-BEGIN*
- FOR rec IN select * from found_test_tbl LOOP*
- RETURN NEXT rec;*
- END LOOP;*
- RETURN;*
+ AS $$**
+DECLARE**
+ rec RECORD;**
+BEGIN**
+ FOR rec IN select * from found_test_tbl LOOP**
+ RETURN NEXT rec;**
+ END LOOP;**
+ RETURN;**
END;$$;*
*
*
@@ -2316,14 +2316,14 @@
*
CREATE FUNCTION test_table_func_row() RETURNS SETOF found_test_tbl*
LANGUAGE plpgsql*
- AS $$*
-DECLARE*
- row found_test_tbl%ROWTYPE;*
-BEGIN*
- FOR row IN select * from found_test_tbl LOOP*
- RETURN NEXT row;*
- END LOOP;*
- RETURN;*
+ AS $$**
+DECLARE**
+ row found_test_tbl%ROWTYPE;**
+BEGIN**
+ FOR row IN select * from found_test_tbl LOOP**
+ RETURN NEXT row;**
+ END LOOP;**
+ RETURN;**
END;$$;*
*
*
@@ -2335,19 +2335,19 @@
*
CREATE FUNCTION test_variable_storage() RETURNS text*
LANGUAGE plpgsql*
- AS $$*
-declare x text;*
-begin*
- x := '1234';*
- begin*
- x := x || '5678';*
- -- force error inside subtransaction SPI context*
- perform trap_zero_divide(-100);*
- exception*
- when others then*
- x := x || '9012';*
- end;*
- return x;*
+ AS $$**
+declare x text;**
+begin**
+ x := '1234';**
+ begin**
+ x := x || '5678';**
+ -- force error inside subtransaction SPI context**
+ perform trap_zero_divide(-100);**
+ exception**
+ when others then**
+ x := x || '9012';**
+ end;**
+ return x;**
end$$;*
*
*
@@ -2403,38 +2403,38 @@
*
CREATE FUNCTION tg_backlink_a() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-declare*
- dummy integer;*
-begin*
- if tg_op = 'INSERT' then*
- if new.backlink != '' then*
- dummy := tg_backlink_set(new.backlink, new.slotname);*
- end if;*
- return new;*
- end if;*
- if tg_op = 'UPDATE' then*
- if new.backlink != old.backlink then*
- if old.backlink != '' then*
- dummy := tg_backlink_unset(old.backlink, old.slotname);*
- end if;*
- if new.backlink != '' then*
- dummy := tg_backlink_set(new.backlink, new.slotname);*
- end if;*
- else*
- if new.slotname != old.slotname and new.backlink != '' then*
- dummy := tg_slotlink_set(new.backlink, new.slotname);*
- end if;*
- end if;*
- return new;*
- end if;*
- if tg_op = 'DELETE' then*
- if old.backlink != '' then*
- dummy := tg_backlink_unset(old.backlink, old.slotname);*
- end if;*
- return old;*
- end if;*
-end;*
+ AS $$**
+declare**
+ dummy integer;**
+begin**
+ if tg_op = 'INSERT' then**
+ if new.backlink != '' then**
+ dummy := tg_backlink_set(new.backlink, new.slotname);**
+ end if;**
+ return new;**
+ end if;**
+ if tg_op = 'UPDATE' then**
+ if new.backlink != old.backlink then**
+ if old.backlink != '' then**
+ dummy := tg_backlink_unset(old.backlink, old.slotname);**
+ end if;**
+ if new.backlink != '' then**
+ dummy := tg_backlink_set(new.backlink, new.slotname);**
+ end if;**
+ else**
+ if new.slotname != old.slotname and new.backlink != '' then**
+ dummy := tg_slotlink_set(new.backlink, new.slotname);**
+ end if;**
+ end if;**
+ return new;**
+ end if;**
+ if tg_op = 'DELETE' then**
+ if old.backlink != '' then**
+ dummy := tg_backlink_unset(old.backlink, old.slotname);**
+ end if;**
+ return old;**
+ end if;**
+end;**
$$;*
*
*
@@ -2446,54 +2446,54 @@
*
CREATE FUNCTION tg_backlink_set(myname character, blname character) RETURNS integer*
LANGUAGE plpgsql*
- AS $$*
-declare*
- mytype char(2);*
- link char(4);*
- rec record;*
-begin*
- mytype := substr(myname, 1, 2);*
- link := mytype || substr(blname, 1, 2);*
- if link = 'PLPL' then*
- raise exception*
- 'backlink between two phone lines does not make sense';*
- end if;*
- if link in ('PLWS', 'WSPL') then*
- raise exception*
- 'direct link of phone line to wall slot not permitted';*
- end if;*
- if mytype = 'PS' then*
- select into rec * from PSlot where slotname = myname;*
- if not found then*
- raise exception '% does not exist', myname;*
- end if;*
- if rec.backlink != blname then*
- update PSlot set backlink = blname where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- if mytype = 'WS' then*
- select into rec * from WSlot where slotname = myname;*
- if not found then*
- raise exception '% does not exist', myname;*
- end if;*
- if rec.backlink != blname then*
- update WSlot set backlink = blname where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- if mytype = 'PL' then*
- select into rec * from PLine where slotname = myname;*
- if not found then*
- raise exception '% does not exist', myname;*
- end if;*
- if rec.backlink != blname then*
- update PLine set backlink = blname where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- raise exception 'illegal backlink beginning with %', mytype;*
-end;*
+ AS $$**
+declare**
+ mytype char(2);**
+ link char(4);**
+ rec record;**
+begin**
+ mytype := substr(myname, 1, 2);**
+ link := mytype || substr(blname, 1, 2);**
+ if link = 'PLPL' then**
+ raise exception**
+ 'backlink between two phone lines does not make sense';**
+ end if;**
+ if link in ('PLWS', 'WSPL') then**
+ raise exception**
+ 'direct link of phone line to wall slot not permitted';**
+ end if;**
+ if mytype = 'PS' then**
+ select into rec * from PSlot where slotname = myname;**
+ if not found then**
+ raise exception '% does not exist', myname;**
+ end if;**
+ if rec.backlink != blname then**
+ update PSlot set backlink = blname where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ if mytype = 'WS' then**
+ select into rec * from WSlot where slotname = myname;**
+ if not found then**
+ raise exception '% does not exist', myname;**
+ end if;**
+ if rec.backlink != blname then**
+ update WSlot set backlink = blname where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ if mytype = 'PL' then**
+ select into rec * from PLine where slotname = myname;**
+ if not found then**
+ raise exception '% does not exist', myname;**
+ end if;**
+ if rec.backlink != blname then**
+ update PLine set backlink = blname where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ raise exception 'illegal backlink beginning with %', mytype;**
+end;**
$$;*
*
*
@@ -2505,45 +2505,45 @@
*
CREATE FUNCTION tg_backlink_unset(character, character) RETURNS integer*
LANGUAGE plpgsql*
- AS $_$*
-declare*
- myname alias for $1;*
- blname alias for $2;*
- mytype char(2);*
- rec record;*
-begin*
- mytype := substr(myname, 1, 2);*
- if mytype = 'PS' then*
- select into rec * from PSlot where slotname = myname;*
- if not found then*
- return 0;*
- end if;*
- if rec.backlink = blname then*
- update PSlot set backlink = '' where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- if mytype = 'WS' then*
- select into rec * from WSlot where slotname = myname;*
- if not found then*
- return 0;*
- end if;*
- if rec.backlink = blname then*
- update WSlot set backlink = '' where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- if mytype = 'PL' then*
- select into rec * from PLine where slotname = myname;*
- if not found then*
- return 0;*
- end if;*
- if rec.backlink = blname then*
- update PLine set backlink = '' where slotname = myname;*
- end if;*
- return 0;*
- end if;*
-end*
+ AS $_$**
+declare**
+ myname alias for $1;**
+ blname alias for $2;**
+ mytype char(2);**
+ rec record;**
+begin**
+ mytype := substr(myname, 1, 2);**
+ if mytype = 'PS' then**
+ select into rec * from PSlot where slotname = myname;**
+ if not found then**
+ return 0;**
+ end if;**
+ if rec.backlink = blname then**
+ update PSlot set backlink = '' where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ if mytype = 'WS' then**
+ select into rec * from WSlot where slotname = myname;**
+ if not found then**
+ return 0;**
+ end if;**
+ if rec.backlink = blname then**
+ update WSlot set backlink = '' where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ if mytype = 'PL' then**
+ select into rec * from PLine where slotname = myname;**
+ if not found then**
+ return 0;**
+ end if;**
+ if rec.backlink = blname then**
+ update PLine set backlink = '' where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+end**
$_$;*
*
*
@@ -2555,13 +2555,13 @@
*
CREATE FUNCTION tg_chkbacklink() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if new.backlink isnull then*
- new.backlink := '';*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if new.backlink isnull then**
+ new.backlink := '';**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -2573,13 +2573,13 @@
*
CREATE FUNCTION tg_chkslotlink() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if new.slotlink isnull then*
- new.slotlink := '';*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if new.slotlink isnull then**
+ new.slotlink := '';**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -2591,13 +2591,13 @@
*
CREATE FUNCTION tg_chkslotname() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if substr(new.slotname, 1, 2) != tg_argv[0] then*
- raise exception 'slotname must begin with %', tg_argv[0];*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if substr(new.slotname, 1, 2) != tg_argv[0] then**
+ raise exception 'slotname must begin with %', tg_argv[0];**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -2609,19 +2609,19 @@
*
CREATE FUNCTION tg_hslot_bd() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-declare*
- hubrec record;*
-begin*
- select into hubrec * from Hub where name = old.hubname;*
- if not found then*
- return old;*
- end if;*
- if old.slotno > hubrec.nslots then*
- return old;*
- end if;*
- raise exception 'no manual manipulation of HSlot';*
-end;*
+ AS $$**
+declare**
+ hubrec record;**
+begin**
+ select into hubrec * from Hub where name = old.hubname;**
+ if not found then**
+ return old;**
+ end if;**
+ if old.slotno > hubrec.nslots then**
+ return old;**
+ end if;**
+ raise exception 'no manual manipulation of HSlot';**
+end;**
$$;*
*
*
@@ -2633,33 +2633,33 @@
*
CREATE FUNCTION tg_hslot_biu() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-declare*
- sname text;*
- xname HSlot.slotname%TYPE;*
- hubrec record;*
-begin*
- select into hubrec * from Hub where name = new.hubname;*
- if not found then*
- raise exception 'no manual manipulation of HSlot';*
- end if;*
- if new.slotno < 1 or new.slotno > hubrec.nslots then*
- raise exception 'no manual manipulation of HSlot';*
- end if;*
- if tg_op = 'UPDATE' and new.hubname != old.hubname then*
- if count(*) > 0 from Hub where name = old.hubname then*
- raise exception 'no manual manipulation of HSlot';*
- end if;*
- end if;*
- sname := 'HS.' || trim(new.hubname);*
- sname := sname || '.';*
- sname := sname || new.slotno::text;*
- if length(sname) > 20 then*
- raise exception 'HSlot slotname "%" too long (20 char max)', sname;*
- end if;*
- new.slotname := sname;*
- return new;*
-end;*
+ AS $$**
+declare**
+ sname text;**
+ xname HSlot.slotname%TYPE;**
+ hubrec record;**
+begin**
+ select into hubrec * from Hub where name = new.hubname;**
+ if not found then**
+ raise exception 'no manual manipulation of HSlot';**
+ end if;**
+ if new.slotno < 1 or new.slotno > hubrec.nslots then**
+ raise exception 'no manual manipulation of HSlot';**
+ end if;**
+ if tg_op = 'UPDATE' and new.hubname != old.hubname then**
+ if count(*) > 0 from Hub where name = old.hubname then**
+ raise exception 'no manual manipulation of HSlot';**
+ end if;**
+ end if;**
+ sname := 'HS.' || trim(new.hubname);**
+ sname := sname || '.';**
+ sname := sname || new.slotno::text;**
+ if length(sname) > 20 then**
+ raise exception 'HSlot slotname "%" too long (20 char max)', sname;**
+ end if;**
+ new.slotname := sname;**
+ return new;**
+end;**
$$;*
*
*
@@ -2671,25 +2671,25 @@
*
CREATE FUNCTION tg_hslot_bu() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if new.slotname != old.slotname or new.hubname != old.hubname then*
- delete from HSlot where slotname = old.slotname;*
- insert into HSlot (*
- slotname,*
- hubname,*
- slotno,*
- slotlink*
- ) values (*
- new.slotname,*
- new.hubname,*
- new.slotno,*
- new.slotlink*
- );*
- return null;*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if new.slotname != old.slotname or new.hubname != old.hubname then**
+ delete from HSlot where slotname = old.slotname;**
+ insert into HSlot (**
+ slotname,**
+ hubname,**
+ slotno,**
+ slotlink**
+ ) values (**
+ new.slotname,**
+ new.hubname,**
+ new.slotno,**
+ new.slotlink**
+ );**
+ return null;**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -2701,27 +2701,27 @@
*
CREATE FUNCTION tg_hub_a() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-declare*
- hname text;*
- dummy integer;*
-begin*
- if tg_op = 'INSERT' then*
- dummy := tg_hub_adjustslots(new.name, 0, new.nslots);*
- return new;*
- end if;*
- if tg_op = 'UPDATE' then*
- if new.name != old.name then*
- update HSlot set hubname = new.name where hubname = old.name;*
- end if;*
- dummy := tg_hub_adjustslots(new.name, old.nslots, new.nslots);*
- return new;*
- end if;*
- if tg_op = 'DELETE' then*
- dummy := tg_hub_adjustslots(old.name, old.nslots, 0);*
- return old;*
- end if;*
-end;*
+ AS $$**
+declare**
+ hname text;**
+ dummy integer;**
+begin**
+ if tg_op = 'INSERT' then**
+ dummy := tg_hub_adjustslots(new.name, 0, new.nslots);**
+ return new;**
+ end if;**
+ if tg_op = 'UPDATE' then**
+ if new.name != old.name then**
+ update HSlot set hubname = new.name where hubname = old.name;**
+ end if;**
+ dummy := tg_hub_adjustslots(new.name, old.nslots, new.nslots);**
+ return new;**
+ end if;**
+ if tg_op = 'DELETE' then**
+ dummy := tg_hub_adjustslots(old.name, old.nslots, 0);**
+ return old;**
+ end if;**
+end;**
$$;*
*
*
@@ -2733,21 +2733,21 @@
*
CREATE FUNCTION tg_hub_adjustslots(hname character, oldnslots integer, newnslots integer) RETURNS integer*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if newnslots = oldnslots then*
- return 0;*
- end if;*
- if newnslots < oldnslots then*
- delete from HSlot where hubname = hname and slotno > newnslots;*
- return 0;*
- end if;*
- for i in oldnslots + 1 .. newnslots loop*
- insert into HSlot (slotname, hubname, slotno, slotlink)*
- values ('HS.dummy', hname, i, '');*
- end loop;*
- return 0;*
-end*
+ AS $$**
+begin**
+ if newnslots = oldnslots then**
+ return 0;**
+ end if;**
+ if newnslots < oldnslots then**
+ delete from HSlot where hubname = hname and slotno > newnslots;**
+ return 0;**
+ end if;**
+ for i in oldnslots + 1 .. newnslots loop**
+ insert into HSlot (slotname, hubname, slotno, slotlink)**
+ values ('HS.dummy', hname, i, '');**
+ end loop;**
+ return 0;**
+end**
$$;*
*
*
@@ -2759,24 +2759,24 @@
*
CREATE FUNCTION tg_iface_biu() RETURNS trigger*
LANGUAGE plpgsql*
- AS $_$*
-declare*
- sname text;*
- sysrec record;*
-begin*
- select into sysrec * from system where name = new.sysname;*
- if not found then*
- raise exception $q$system "%" does not exist$q$, new.sysname;*
- end if;*
- sname := 'IF.' || new.sysname;*
- sname := sname || '.';*
- sname := sname || new.ifname;*
- if length(sname) > 20 then*
- raise exception 'IFace slotname "%" too long (20 char max)', sname;*
- end if;*
- new.slotname := sname;*
- return new;*
-end;*
+ AS $_$**
+declare**
+ sname text;**
+ sysrec record;**
+begin**
+ select into sysrec * from system where name = new.sysname;**
+ if not found then**
+ raise exception $q$system "%" does not exist$q$, new.sysname;**
+ end if;**
+ sname := 'IF.' || new.sysname;**
+ sname := sname || '.';**
+ sname := sname || new.ifname;**
+ if length(sname) > 20 then**
+ raise exception 'IFace slotname "%" too long (20 char max)', sname;**
+ end if;**
+ new.slotname := sname;**
+ return new;**
+end;**
$_$;*
*
*
@@ -2788,25 +2788,25 @@
*
CREATE FUNCTION tg_iface_bu() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if new.slotname != old.slotname then*
- delete from IFace where slotname = old.slotname;*
- insert into IFace (*
- slotname,*
- sysname,*
- ifname,*
- slotlink*
- ) values (*
- new.slotname,*
- new.sysname,*
- new.ifname,*
- new.slotlink*
- );*
- return null;*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if new.slotname != old.slotname then**
+ delete from IFace where slotname = old.slotname;**
+ insert into IFace (**
+ slotname,**
+ sysname,**
+ ifname,**
+ slotlink**
+ ) values (**
+ new.slotname,**
+ new.sysname,**
+ new.ifname,**
+ new.slotlink**
+ );**
+ return null;**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -2818,11 +2818,11 @@
*
CREATE FUNCTION tg_pfield_ad() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- delete from PSlot where pfname = old.name;*
- return old;*
-end;*
+ AS $$**
+begin**
+ delete from PSlot where pfname = old.name;**
+ return old;**
+end;**
$$;*
*
*
@@ -2834,13 +2834,13 @@
*
CREATE FUNCTION tg_pfield_au() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if new.name != old.name then*
- update PSlot set pfname = new.name where pfname = old.name;*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if new.name != old.name then**
+ update PSlot set pfname = new.name where pfname = old.name;**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -2852,23 +2852,23 @@
*
CREATE FUNCTION tg_phone_bu() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if new.slotname != old.slotname then*
- delete from PHone where slotname = old.slotname;*
- insert into PHone (*
- slotname,*
- comment,*
- slotlink*
- ) values (*
- new.slotname,*
- new.comment,*
- new.slotlink*
- );*
- return null;*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if new.slotname != old.slotname then**
+ delete from PHone where slotname = old.slotname;**
+ insert into PHone (**
+ slotname,**
+ comment,**
+ slotlink**
+ ) values (**
+ new.slotname,**
+ new.comment,**
+ new.slotlink**
+ );**
+ return null;**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -2880,25 +2880,25 @@
*
CREATE FUNCTION tg_pline_bu() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if new.slotname != old.slotname then*
- delete from PLine where slotname = old.slotname;*
- insert into PLine (*
- slotname,*
- phonenumber,*
- comment,*
- backlink*
- ) values (*
- new.slotname,*
- new.phonenumber,*
- new.comment,*
- new.backlink*
- );*
- return null;*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if new.slotname != old.slotname then**
+ delete from PLine where slotname = old.slotname;**
+ insert into PLine (**
+ slotname,**
+ phonenumber,**
+ comment,**
+ backlink**
+ ) values (**
+ new.slotname,**
+ new.phonenumber,**
+ new.comment,**
+ new.backlink**
+ );**
+ return null;**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -2910,17 +2910,17 @@
*
CREATE FUNCTION tg_pslot_biu() RETURNS trigger*
LANGUAGE plpgsql*
- AS $_$*
-declare*
- pfrec record;*
- ps alias for new;*
-begin*
- select into pfrec * from PField where name = ps.pfname;*
- if not found then*
- raise exception $$Patchfield "%" does not exist$$, ps.pfname;*
- end if;*
- return ps;*
-end;*
+ AS $_$**
+declare**
+ pfrec record;**
+ ps alias for new;**
+begin**
+ select into pfrec * from PField where name = ps.pfname;**
+ if not found then**
+ raise exception $$Patchfield "%" does not exist$$, ps.pfname;**
+ end if;**
+ return ps;**
+end;**
$_$;*
*
*
@@ -2932,25 +2932,25 @@
*
CREATE FUNCTION tg_pslot_bu() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if new.slotname != old.slotname then*
- delete from PSlot where slotname = old.slotname;*
- insert into PSlot (*
- slotname,*
- pfname,*
- slotlink,*
- backlink*
- ) values (*
- new.slotname,*
- new.pfname,*
- new.slotlink,*
- new.backlink*
- );*
- return null;*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if new.slotname != old.slotname then**
+ delete from PSlot where slotname = old.slotname;**
+ insert into PSlot (**
+ slotname,**
+ pfname,**
+ slotlink,**
+ backlink**
+ ) values (**
+ new.slotname,**
+ new.pfname,**
+ new.slotlink,**
+ new.backlink**
+ );**
+ return null;**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -2962,11 +2962,11 @@
*
CREATE FUNCTION tg_room_ad() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- delete from WSlot where roomno = old.roomno;*
- return old;*
-end;*
+ AS $$**
+begin**
+ delete from WSlot where roomno = old.roomno;**
+ return old;**
+end;**
$$;*
*
*
@@ -2978,13 +2978,13 @@
*
CREATE FUNCTION tg_room_au() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if new.roomno != old.roomno then*
- update WSlot set roomno = new.roomno where roomno = old.roomno;*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if new.roomno != old.roomno then**
+ update WSlot set roomno = new.roomno where roomno = old.roomno;**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -2996,38 +2996,38 @@
*
CREATE FUNCTION tg_slotlink_a() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-declare*
- dummy integer;*
-begin*
- if tg_op = 'INSERT' then*
- if new.slotlink != '' then*
- dummy := tg_slotlink_set(new.slotlink, new.slotname);*
- end if;*
- return new;*
- end if;*
- if tg_op = 'UPDATE' then*
- if new.slotlink != old.slotlink then*
- if old.slotlink != '' then*
- dummy := tg_slotlink_unset(old.slotlink, old.slotname);*
- end if;*
- if new.slotlink != '' then*
- dummy := tg_slotlink_set(new.slotlink, new.slotname);*
- end if;*
- else*
- if new.slotname != old.slotname and new.slotlink != '' then*
- dummy := tg_slotlink_set(new.slotlink, new.slotname);*
- end if;*
- end if;*
- return new;*
- end if;*
- if tg_op = 'DELETE' then*
- if old.slotlink != '' then*
- dummy := tg_slotlink_unset(old.slotlink, old.slotname);*
- end if;*
- return old;*
- end if;*
-end;*
+ AS $$**
+declare**
+ dummy integer;**
+begin**
+ if tg_op = 'INSERT' then**
+ if new.slotlink != '' then**
+ dummy := tg_slotlink_set(new.slotlink, new.slotname);**
+ end if;**
+ return new;**
+ end if;**
+ if tg_op = 'UPDATE' then**
+ if new.slotlink != old.slotlink then**
+ if old.slotlink != '' then**
+ dummy := tg_slotlink_unset(old.slotlink, old.slotname);**
+ end if;**
+ if new.slotlink != '' then**
+ dummy := tg_slotlink_set(new.slotlink, new.slotname);**
+ end if;**
+ else**
+ if new.slotname != old.slotname and new.slotlink != '' then**
+ dummy := tg_slotlink_set(new.slotlink, new.slotname);**
+ end if;**
+ end if;**
+ return new;**
+ end if;**
+ if tg_op = 'DELETE' then**
+ if old.slotlink != '' then**
+ dummy := tg_slotlink_unset(old.slotlink, old.slotname);**
+ end if;**
+ return old;**
+ end if;**
+end;**
$$;*
*
*
@@ -3039,84 +3039,84 @@
*
CREATE FUNCTION tg_slotlink_set(character, character) RETURNS integer*
LANGUAGE plpgsql*
- AS $_$*
-declare*
- myname alias for $1;*
- blname alias for $2;*
- mytype char(2);*
- link char(4);*
- rec record;*
-begin*
- mytype := substr(myname, 1, 2);*
- link := mytype || substr(blname, 1, 2);*
- if link = 'PHPH' then*
- raise exception*
- 'slotlink between two phones does not make sense';*
- end if;*
- if link in ('PHHS', 'HSPH') then*
- raise exception*
- 'link of phone to hub does not make sense';*
- end if;*
- if link in ('PHIF', 'IFPH') then*
- raise exception*
- 'link of phone to hub does not make sense';*
- end if;*
- if link in ('PSWS', 'WSPS') then*
- raise exception*
- 'slotlink from patchslot to wallslot not permitted';*
- end if;*
- if mytype = 'PS' then*
- select into rec * from PSlot where slotname = myname;*
- if not found then*
- raise exception '% does not exist', myname;*
- end if;*
- if rec.slotlink != blname then*
- update PSlot set slotlink = blname where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- if mytype = 'WS' then*
- select into rec * from WSlot where slotname = myname;*
- if not found then*
- raise exception '% does not exist', myname;*
- end if;*
- if rec.slotlink != blname then*
- update WSlot set slotlink = blname where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- if mytype = 'IF' then*
- select into rec * from IFace where slotname = myname;*
- if not found then*
- raise exception '% does not exist', myname;*
- end if;*
- if rec.slotlink != blname then*
- update IFace set slotlink = blname where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- if mytype = 'HS' then*
- select into rec * from HSlot where slotname = myname;*
- if not found then*
- raise exception '% does not exist', myname;*
- end if;*
- if rec.slotlink != blname then*
- update HSlot set slotlink = blname where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- if mytype = 'PH' then*
- select into rec * from PHone where slotname = myname;*
- if not found then*
- raise exception '% does not exist', myname;*
- end if;*
- if rec.slotlink != blname then*
- update PHone set slotlink = blname where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- raise exception 'illegal slotlink beginning with %', mytype;*
-end;*
+ AS $_$**
+declare**
+ myname alias for $1;**
+ blname alias for $2;**
+ mytype char(2);**
+ link char(4);**
+ rec record;**
+begin**
+ mytype := substr(myname, 1, 2);**
+ link := mytype || substr(blname, 1, 2);**
+ if link = 'PHPH' then**
+ raise exception**
+ 'slotlink between two phones does not make sense';**
+ end if;**
+ if link in ('PHHS', 'HSPH') then**
+ raise exception**
+ 'link of phone to hub does not make sense';**
+ end if;**
+ if link in ('PHIF', 'IFPH') then**
+ raise exception**
+ 'link of phone to hub does not make sense';**
+ end if;**
+ if link in ('PSWS', 'WSPS') then**
+ raise exception**
+ 'slotlink from patchslot to wallslot not permitted';**
+ end if;**
+ if mytype = 'PS' then**
+ select into rec * from PSlot where slotname = myname;**
+ if not found then**
+ raise exception '% does not exist', myname;**
+ end if;**
+ if rec.slotlink != blname then**
+ update PSlot set slotlink = blname where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ if mytype = 'WS' then**
+ select into rec * from WSlot where slotname = myname;**
+ if not found then**
+ raise exception '% does not exist', myname;**
+ end if;**
+ if rec.slotlink != blname then**
+ update WSlot set slotlink = blname where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ if mytype = 'IF' then**
+ select into rec * from IFace where slotname = myname;**
+ if not found then**
+ raise exception '% does not exist', myname;**
+ end if;**
+ if rec.slotlink != blname then**
+ update IFace set slotlink = blname where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ if mytype = 'HS' then**
+ select into rec * from HSlot where slotname = myname;**
+ if not found then**
+ raise exception '% does not exist', myname;**
+ end if;**
+ if rec.slotlink != blname then**
+ update HSlot set slotlink = blname where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ if mytype = 'PH' then**
+ select into rec * from PHone where slotname = myname;**
+ if not found then**
+ raise exception '% does not exist', myname;**
+ end if;**
+ if rec.slotlink != blname then**
+ update PHone set slotlink = blname where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ raise exception 'illegal slotlink beginning with %', mytype;**
+end;**
$_$;*
*
*
@@ -3128,65 +3128,65 @@
*
CREATE FUNCTION tg_slotlink_unset(character, character) RETURNS integer*
LANGUAGE plpgsql*
- AS $_$*
-declare*
- myname alias for $1;*
- blname alias for $2;*
- mytype char(2);*
- rec record;*
-begin*
- mytype := substr(myname, 1, 2);*
- if mytype = 'PS' then*
- select into rec * from PSlot where slotname = myname;*
- if not found then*
- return 0;*
- end if;*
- if rec.slotlink = blname then*
- update PSlot set slotlink = '' where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- if mytype = 'WS' then*
- select into rec * from WSlot where slotname = myname;*
- if not found then*
- return 0;*
- end if;*
- if rec.slotlink = blname then*
- update WSlot set slotlink = '' where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- if mytype = 'IF' then*
- select into rec * from IFace where slotname = myname;*
- if not found then*
- return 0;*
- end if;*
- if rec.slotlink = blname then*
- update IFace set slotlink = '' where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- if mytype = 'HS' then*
- select into rec * from HSlot where slotname = myname;*
- if not found then*
- return 0;*
- end if;*
- if rec.slotlink = blname then*
- update HSlot set slotlink = '' where slotname = myname;*
- end if;*
- return 0;*
- end if;*
- if mytype = 'PH' then*
- select into rec * from PHone where slotname = myname;*
- if not found then*
- return 0;*
- end if;*
- if rec.slotlink = blname then*
- update PHone set slotlink = '' where slotname = myname;*
- end if;*
- return 0;*
- end if;*
-end;*
+ AS $_$**
+declare**
+ myname alias for $1;**
+ blname alias for $2;**
+ mytype char(2);**
+ rec record;**
+begin**
+ mytype := substr(myname, 1, 2);**
+ if mytype = 'PS' then**
+ select into rec * from PSlot where slotname = myname;**
+ if not found then**
+ return 0;**
+ end if;**
+ if rec.slotlink = blname then**
+ update PSlot set slotlink = '' where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ if mytype = 'WS' then**
+ select into rec * from WSlot where slotname = myname;**
+ if not found then**
+ return 0;**
+ end if;**
+ if rec.slotlink = blname then**
+ update WSlot set slotlink = '' where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ if mytype = 'IF' then**
+ select into rec * from IFace where slotname = myname;**
+ if not found then**
+ return 0;**
+ end if;**
+ if rec.slotlink = blname then**
+ update IFace set slotlink = '' where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ if mytype = 'HS' then**
+ select into rec * from HSlot where slotname = myname;**
+ if not found then**
+ return 0;**
+ end if;**
+ if rec.slotlink = blname then**
+ update HSlot set slotlink = '' where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+ if mytype = 'PH' then**
+ select into rec * from PHone where slotname = myname;**
+ if not found then**
+ return 0;**
+ end if;**
+ if rec.slotlink = blname then**
+ update PHone set slotlink = '' where slotname = myname;**
+ end if;**
+ return 0;**
+ end if;**
+end;**
$_$;*
*
*
@@ -3198,13 +3198,13 @@
*
CREATE FUNCTION tg_system_au() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if new.name != old.name then*
- update IFace set sysname = new.name where sysname = old.name;*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if new.name != old.name then**
+ update IFace set sysname = new.name where sysname = old.name;**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -3216,13 +3216,13 @@
*
CREATE FUNCTION tg_wslot_biu() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if count(*) = 0 from Room where roomno = new.roomno then*
- raise exception 'Room % does not exist', new.roomno;*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if count(*) = 0 from Room where roomno = new.roomno then**
+ raise exception 'Room % does not exist', new.roomno;**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -3234,25 +3234,25 @@
*
CREATE FUNCTION tg_wslot_bu() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- if new.slotname != old.slotname then*
- delete from WSlot where slotname = old.slotname;*
- insert into WSlot (*
- slotname,*
- roomno,*
- slotlink,*
- backlink*
- ) values (*
- new.slotname,*
- new.roomno,*
- new.slotlink,*
- new.backlink*
- );*
- return null;*
- end if;*
- return new;*
-end;*
+ AS $$**
+begin**
+ if new.slotname != old.slotname then**
+ delete from WSlot where slotname = old.slotname;**
+ insert into WSlot (**
+ slotname,**
+ roomno,**
+ slotlink,**
+ backlink**
+ ) values (**
+ new.slotname,**
+ new.roomno,**
+ new.slotlink,**
+ new.backlink**
+ );**
+ return null;**
+ end if;**
+ return new;**
+end;**
$$;*
*
*
@@ -3264,25 +3264,25 @@
*
CREATE FUNCTION trap_matching_test(integer) RETURNS integer*
LANGUAGE plpgsql*
- AS $_$*
-declare x int;*
- sx smallint;*
- y int;*
-begin*
- begin -- start a subtransaction*
- x := 100 / $1;*
- sx := $1;*
- select into y unique1 from tenk1 where unique2 =*
- (select unique2 from tenk1 b where ten = $1);*
- exception*
- when data_exception then -- category match*
- raise notice 'caught data_exception';*
- x := -1;*
- when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then*
- raise notice 'caught numeric_value_out_of_range or cardinality_violation';*
- x := -2;*
- end;*
- return x;*
+ AS $_$**
+declare x int;**
+ sx smallint;**
+ y int;**
+begin**
+ begin -- start a subtransaction**
+ x := 100 / $1;**
+ sx := $1;**
+ select into y unique1 from tenk1 where unique2 =**
+ (select unique2 from tenk1 b where ten = $1);**
+ exception**
+ when data_exception then -- category match**
+ raise notice 'caught data_exception';**
+ x := -1;**
+ when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then**
+ raise notice 'caught numeric_value_out_of_range or cardinality_violation';**
+ x := -2;**
+ end;**
+ return x;**
end$_$;*
*
*
@@ -3294,28 +3294,28 @@
*
CREATE FUNCTION trap_zero_divide(integer) RETURNS integer*
LANGUAGE plpgsql*
- AS $_$*
-declare x int;*
- sx smallint;*
-begin*
- begin -- start a subtransaction*
- raise notice 'should see this';*
- x := 100 / $1;*
- raise notice 'should see this only if % <> 0', $1;*
- sx := $1;*
- raise notice 'should see this only if % fits in smallint', $1;*
- if $1 < 0 then*
- raise exception '% is less than zero', $1;*
- end if;*
- exception*
- when division_by_zero then*
- raise notice 'caught division_by_zero';*
- x := -1;*
- when NUMERIC_VALUE_OUT_OF_RANGE then*
- raise notice 'caught numeric_value_out_of_range';*
- x := -2;*
- end;*
- return x;*
+ AS $_$**
+declare x int;**
+ sx smallint;**
+begin**
+ begin -- start a subtransaction**
+ raise notice 'should see this';**
+ x := 100 / $1;**
+ raise notice 'should see this only if % <> 0', $1;**
+ sx := $1;**
+ raise notice 'should see this only if % fits in smallint', $1;**
+ if $1 < 0 then**
+ raise exception '% is less than zero', $1;**
+ end if;**
+ exception**
+ when division_by_zero then**
+ raise notice 'caught division_by_zero';**
+ x := -1;**
+ when NUMERIC_VALUE_OUT_OF_RANGE then**
+ raise notice 'caught numeric_value_out_of_range';**
+ x := -2;**
+ end;**
+ return x;**
end$_$;*
*
*
@@ -3327,10 +3327,10 @@
*
CREATE FUNCTION trigger_func() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-BEGIN*
- RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;*
- RETURN NULL;*
+ AS $$**
+BEGIN**
+ RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;**
+ RETURN NULL;**
END;$$;*
*
*
@@ -3342,10 +3342,10 @@
*
CREATE FUNCTION trigtest() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-begin*
- raise notice '% % % %', TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL;*
- return new;*
+ AS $$**
+begin**
+ raise notice '% % % %', TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL;**
+ return new;**
end;$$;*
*
*
@@ -3368,10 +3368,10 @@
*
CREATE FUNCTION user_relns() RETURNS SETOF name*
LANGUAGE sql*
- AS $$select relname*
- from pg_class c, pg_namespace n*
- where relnamespace = n.oid and*
- (nspname !~ 'pg_.*' and nspname <> 'information_schema') and*
+ AS $$select relname**
+ from pg_class c, pg_namespace n**
+ where relnamespace = n.oid and**
+ (nspname !~ 'pg_.*' and nspname <> 'information_schema') and**
relkind <> 'i' $$;*
*
*
@@ -3383,43 +3383,43 @@
*
CREATE FUNCTION view_trigger() RETURNS trigger*
LANGUAGE plpgsql*
- AS $$*
-declare*
- argstr text := '';*
-begin*
- for i in 0 .. TG_nargs - 1 loop*
- if i > 0 then*
- argstr := argstr || ', ';*
- end if;*
- argstr := argstr || TG_argv[i];*
- end loop;*
-*
- raise notice '% % % % (%)', TG_RELNAME, TG_WHEN, TG_OP, TG_LEVEL, argstr;*
-*
- if TG_LEVEL = 'ROW' then*
- if TG_OP = 'INSERT' then*
- raise NOTICE 'NEW: %', NEW;*
- INSERT INTO main_table VALUES (NEW.a, NEW.b);*
- RETURN NEW;*
- end if;*
-*
- if TG_OP = 'UPDATE' then*
- raise NOTICE 'OLD: %, NEW: %', OLD, NEW;*
- UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b;*
- if NOT FOUND then RETURN NULL; end if;*
- RETURN NEW;*
- end if;*
-*
- if TG_OP = 'DELETE' then*
- raise NOTICE 'OLD: %', OLD;*
- DELETE FROM main_table WHERE a = OLD.a AND b = OLD.b;*
- if NOT FOUND then RETURN NULL; end if;*
- RETURN OLD;*
- end if;*
- end if;*
-*
- RETURN NULL;*
-end;*
+ AS $$**
+declare**
+ argstr text := '';**
+begin**
+ for i in 0 .. TG_nargs - 1 loop**
+ if i > 0 then**
+ argstr := argstr || ', ';**
+ end if;**
+ argstr := argstr || TG_argv[i];**
+ end loop;**
+**
+ raise notice '% % % % (%)', TG_RELNAME, TG_WHEN, TG_OP, TG_LEVEL, argstr;**
+**
+ if TG_LEVEL = 'ROW' then**
+ if TG_OP = 'INSERT' then**
+ raise NOTICE 'NEW: %', NEW;**
+ INSERT INTO main_table VALUES (NEW.a, NEW.b);**
+ RETURN NEW;**
+ end if;**
+**
+ if TG_OP = 'UPDATE' then**
+ raise NOTICE 'OLD: %, NEW: %', OLD, NEW;**
+ UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b;**
+ if NOT FOUND then RETURN NULL; end if;**
+ RETURN NEW;**
+ end if;**
+**
+ if TG_OP = 'DELETE' then**
+ raise NOTICE 'OLD: %', OLD;**
+ DELETE FROM main_table WHERE a = OLD.a AND b = OLD.b;**
+ if NOT FOUND then RETURN NULL; end if;**
+ RETURN OLD;**
+ end if;**
+ end if;**
+**
+ RETURN NULL;**
+end;**
$$;*
*
*
@@ -3431,33 +3431,33 @@
*
CREATE FUNCTION wait_for_stats() RETURNS void*
LANGUAGE plpgsql*
- AS $$*
-declare*
- start_time timestamptz := clock_timestamp();*
- updated bool;*
-begin*
- -- we don't want to wait forever; loop will exit after 30 seconds*
- for i in 1 .. 300 loop*
-*
- -- check to see if indexscan has been sensed*
- SELECT (st.idx_scan >= pr.idx_scan + 1) INTO updated*
- FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr*
- WHERE st.relname='tenk2' AND cl.relname='tenk2';*
-*
- exit when updated;*
-*
- -- wait a little*
- perform pg_sleep(0.1);*
-*
- -- reset stats snapshot so we can test again*
- perform pg_stat_clear_snapshot();*
-*
- end loop;*
-*
- -- report time waited in postmaster log (where it won't change test output)*
- raise log 'wait_for_stats delayed % seconds',*
- extract(epoch from clock_timestamp() - start_time);*
-end*
+ AS $$**
+declare**
+ start_time timestamptz := clock_timestamp();**
+ updated bool;**
+begin**
+ -- we don't want to wait forever; loop will exit after 30 seconds**
+ for i in 1 .. 300 loop**
+**
+ -- check to see if indexscan has been sensed**
+ SELECT (st.idx_scan >= pr.idx_scan + 1) INTO updated**
+ FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr**
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';**
+**
+ exit when updated;**
+**
+ -- wait a little**
+ perform pg_sleep(0.1);**
+**
+ -- reset stats snapshot so we can test again**
+ perform pg_stat_clear_snapshot();**
+**
+ end loop;**
+**
+ -- report time waited in postmaster log (where it won't change test output)**
+ raise log 'wait_for_stats delayed % seconds',**
+ extract(epoch from clock_timestamp() - start_time);**
+end**
$$;*
*
*
@@ -3480,49 +3480,49 @@
*
CREATE FUNCTION wslot_slotlink_view(character) RETURNS text*
LANGUAGE plpgsql*
- AS $_$*
-declare*
- rec record;*
- sltype char(2);*
- retval text;*
-begin*
- select into rec * from WSlot where slotname = $1;*
- if not found then*
- return '';*
- end if;*
- if rec.slotlink = '' then*
- return '-';*
- end if;*
- sltype := substr(rec.slotlink, 1, 2);*
- if sltype = 'PH' then*
- select into rec * from PHone where slotname = rec.slotlink;*
- retval := 'Phone ' || trim(rec.slotname);*
- if rec.comment != '' then*
- retval := retval || ' (';*
- retval := retval || rec.comment;*
- retval := retval || ')';*
- end if;*
- return retval;*
- end if;*
- if sltype = 'IF' then*
- declare*
- syrow System%RowType;*
- ifrow IFace%ROWTYPE;*
- begin*
- select into ifrow * from IFace where slotname = rec.slotlink;*
- select into syrow * from System where name = ifrow.sysname;*
- retval := syrow.name || ' IF ';*
- retval := retval || ifrow.ifname;*
- if syrow.comment != '' then*
- retval := retval || ' (';*
- retval := retval || syrow.comment;*
- retval := retval || ')';*
- end if;*
- return retval;*
- end;*
- end if;*
- return rec.slotlink;*
-end;*
+ AS $_$**
+declare**
+ rec record;**
+ sltype char(2);**
+ retval text;**
+begin**
+ select into rec * from WSlot where slotname = $1;**
+ if not found then**
+ return '';**
+ end if;**
+ if rec.slotlink = '' then**
+ return '-';**
+ end if;**
+ sltype := substr(rec.slotlink, 1, 2);**
+ if sltype = 'PH' then**
+ select into rec * from PHone where slotname = rec.slotlink;**
+ retval := 'Phone ' || trim(rec.slotname);**
+ if rec.comment != '' then**
+ retval := retval || ' (';**
+ retval := retval || rec.comment;**
+ retval := retval || ')';**
+ end if;**
+ return retval;**
+ end if;**
+ if sltype = 'IF' then**
+ declare**
+ syrow System%RowType;**
+ ifrow IFace%ROWTYPE;**
+ begin**
+ select into ifrow * from IFace where slotname = rec.slotlink;**
+ select into syrow * from System where name = ifrow.sysname;**
+ retval := syrow.name || ' IF ';**
+ retval := retval || ifrow.ifname;**
+ if syrow.comment != '' then**
+ retval := retval || ' (';**
+ retval := retval || syrow.comment;**
+ retval := retval || ')';**
+ end if;**
+ return retval;**
+ end;**
+ end if;**
+ return rec.slotlink;**
+end;**
$_$;*
*
*
On 09/04/2012 03:09 PM, Andrew Dunstan wrote:
I realized this morning that I might have been a bit cavalier in using
dos2unix to smooth away differences in the dumpfiles produced by
pg_upgrade. Attached is a dump of the diff if this isn't done, with
Carriage Returns printed as '*' to make them visible. As can be seen,
in function bodies dump2 has the Carriage Returns doubled. I have not
had time to delve into how this comes about, and I need to attend to
some income-producing activity for a bit, but I'd like to get it
cleaned up ASAP. We are under the hammer for 9.2, so any help other
people can give on this would be appreciated.
Actually, I have the answer - it's quite simple. We just need to open
the output files in binary mode when we split the dumpall file. The
attached patch fixes it. I think we should backpatch the first part to 9.0.
cheers
andrew
Attachments:
pg_upgrade_binsplit.patchtext/x-patch; name=pg_upgrade_binsplit.patchDownload
diff --git a/contrib/pg_upgrade/dump.c b/contrib/pg_upgrade/dump.c
index b905ab0..0a96dde 100644
--- a/contrib/pg_upgrade/dump.c
+++ b/contrib/pg_upgrade/dump.c
@@ -62,10 +62,10 @@ split_old_dump(void)
if ((all_dump = fopen(filename, "r")) == NULL)
pg_log(PG_FATAL, "Could not open dump file \"%s\": %s\n", filename, getErrorText(errno));
snprintf(filename, sizeof(filename), "%s", GLOBALS_DUMP_FILE);
- if ((globals_dump = fopen_priv(filename, "w")) == NULL)
+ if ((globals_dump = fopen_priv(filename, PG_BINARY_W)) == NULL)
pg_log(PG_FATAL, "Could not write to dump file \"%s\": %s\n", filename, getErrorText(errno));
snprintf(filename, sizeof(filename), "%s", DB_DUMP_FILE);
- if ((db_dump = fopen_priv(filename, "w")) == NULL)
+ if ((db_dump = fopen_priv(filename, PG_BINARY_W)) == NULL)
pg_log(PG_FATAL, "Could not write to dump file \"%s\": %s\n", filename, getErrorText(errno));
current_output = globals_dump;
diff --git a/contrib/pg_upgrade/test.sh b/contrib/pg_upgrade/test.sh
index d411ac6..3899600 100644
--- a/contrib/pg_upgrade/test.sh
+++ b/contrib/pg_upgrade/test.sh
@@ -128,10 +128,6 @@ else
sh ./delete_old_cluster.sh
fi
-if [ $testhost = Msys ] ; then
- dos2unix "$temp_root"/dump1.sql "$temp_root"/dump2.sql
-fi
-
if diff -q "$temp_root"/dump1.sql "$temp_root"/dump2.sql; then
echo PASSED
exit 0
On 09/04/2012 03:44 PM, Andrew Dunstan wrote:
On 09/04/2012 03:09 PM, Andrew Dunstan wrote:
I realized this morning that I might have been a bit cavalier in
using dos2unix to smooth away differences in the dumpfiles produced
by pg_upgrade. Attached is a dump of the diff if this isn't done,
with Carriage Returns printed as '*' to make them visible. As can be
seen, in function bodies dump2 has the Carriage Returns doubled. I
have not had time to delve into how this comes about, and I need to
attend to some income-producing activity for a bit, but I'd like to
get it cleaned up ASAP. We are under the hammer for 9.2, so any help
other people can give on this would be appreciated.Actually, I have the answer - it's quite simple. We just need to open
the output files in binary mode when we split the dumpall file. The
attached patch fixes it. I think we should backpatch the first part to
9.0.
OK, nobody else has reacted. I've spoken to Bruce and he seems happy
with it, although, TBH, whe I talked to him I thought I understood it
and now I'm not so sure. So we have 3 possibilities: leave it as is with
an error-hiding hack in the test script, apply this patch which removes
the hack and applies a fix that apparently works but which confuses us a
bit, or go back to generating errors. The last choice would mean I would
need to turn off pg_ugrade testing on Windows pending a fix. And we have
to decide pretty much now so we can get 9.2 out the door.
Thoughts?
cheers
andrew
On Tue, Sep 4, 2012 at 08:46:53PM -0400, Andrew Dunstan wrote:
On 09/04/2012 03:44 PM, Andrew Dunstan wrote:
On 09/04/2012 03:09 PM, Andrew Dunstan wrote:
I realized this morning that I might have been a bit cavalier in
using dos2unix to smooth away differences in the dumpfiles
produced by pg_upgrade. Attached is a dump of the diff if this
isn't done, with Carriage Returns printed as '*' to make them
visible. As can be seen, in function bodies dump2 has the
Carriage Returns doubled. I have not had time to delve into how
this comes about, and I need to attend to some income-producing
activity for a bit, but I'd like to get it cleaned up ASAP. We
are under the hammer for 9.2, so any help other people can give
on this would be appreciated.Actually, I have the answer - it's quite simple. We just need to
open the output files in binary mode when we split the dumpall
file. The attached patch fixes it. I think we should backpatch the
first part to 9.0.OK, nobody else has reacted. I've spoken to Bruce and he seems happy
with it, although, TBH, whe I talked to him I thought I understood
it and now I'm not so sure. So we have 3 possibilities: leave it as
is with an error-hiding hack in the test script, apply this patch
which removes the hack and applies a fix that apparently works but
which confuses us a bit, or go back to generating errors. The last
choice would mean I would need to turn off pg_ugrade testing on
Windows pending a fix. And we have to decide pretty much now so we
can get 9.2 out the door.
I am very concerned about putting something into pg_upgrade that we
don't fully understand. Adding stuff to pg_upgrade that we think we
understand is risky enough, as we have seen in the pg_upgrade churn of
the past week. Let's work on chat to find the complete details --- same
goes for the log file change we are not sure about either.
pg_upgrade is so complicated that I have learned that if we don't fully
understand something, it can affect things we don't anticipate.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Tue, 2012-09-04 at 20:46 -0400, Andrew Dunstan wrote:
OK, nobody else has reacted. I've spoken to Bruce and he seems happy
with it, although, TBH, whe I talked to him I thought I understood it
and now I'm not so sure. So we have 3 possibilities: leave it as is with
an error-hiding hack in the test script, apply this patch which removes
the hack and applies a fix that apparently works but which confuses us a
bit, or go back to generating errors. The last choice would mean I would
need to turn off pg_ugrade testing on Windows pending a fix. And we have
to decide pretty much now so we can get 9.2 out the door.
I think now is not the time to cram in poorly understood changes into a
release candidate. There is no requirement to have the tests running
now or in time for the release, seeing also that no one has been
particularly bothered about it for the past 11 months.
Peter Eisentraut <peter_e@gmx.net> writes:
On Tue, 2012-09-04 at 20:46 -0400, Andrew Dunstan wrote:
OK, nobody else has reacted. I've spoken to Bruce and he seems happy
with it, although, TBH, whe I talked to him I thought I understood it
and now I'm not so sure. So we have 3 possibilities: leave it as is with
an error-hiding hack in the test script, apply this patch which removes
the hack and applies a fix that apparently works but which confuses us a
bit, or go back to generating errors. The last choice would mean I would
need to turn off pg_ugrade testing on Windows pending a fix. And we have
to decide pretty much now so we can get 9.2 out the door.
I think now is not the time to cram in poorly understood changes into a
release candidate. There is no requirement to have the tests running
now or in time for the release, seeing also that no one has been
particularly bothered about it for the past 11 months.
Also, the tests *are* passing right now. I agree, let's not risk
destabilizing it. pg_upgrade is way overdue for some quiet time so we
can verify a full day's buildfarm cycle on it before the release wrap.
regards, tom lane
On Tue, Sep 4, 2012 at 03:44:35PM -0400, Andrew Dunstan wrote:
On 09/04/2012 03:09 PM, Andrew Dunstan wrote:
I realized this morning that I might have been a bit cavalier in
using dos2unix to smooth away differences in the dumpfiles
produced by pg_upgrade. Attached is a dump of the diff if this
isn't done, with Carriage Returns printed as '*' to make them
visible. As can be seen, in function bodies dump2 has the Carriage
Returns doubled. I have not had time to delve into how this comes
about, and I need to attend to some income-producing activity for
a bit, but I'd like to get it cleaned up ASAP. We are under the
hammer for 9.2, so any help other people can give on this would be
appreciated.Actually, I have the answer - it's quite simple. We just need to
open the output files in binary mode when we split the dumpall file.
The attached patch fixes it. I think we should backpatch the first
part to 9.0.
diff --git a/contrib/pg_upgrade/dump.c b/contrib/pg_upgrade/dump.c index b905ab0..0a96dde 100644 --- a/contrib/pg_upgrade/dump.c +++ b/contrib/pg_upgrade/dump.c @@ -62,10 +62,10 @@ split_old_dump(void) if ((all_dump = fopen(filename, "r")) == NULL) pg_log(PG_FATAL, "Could not open dump file \"%s\": %s\n", filename, getErrorText(errno)); snprintf(filename, sizeof(filename), "%s", GLOBALS_DUMP_FILE); - if ((globals_dump = fopen_priv(filename, "w")) == NULL) + if ((globals_dump = fopen_priv(filename, PG_BINARY_W)) == NULL) pg_log(PG_FATAL, "Could not write to dump file \"%s\": %s\n", filename, getErrorText(errno)); snprintf(filename, sizeof(filename), "%s", DB_DUMP_FILE); - if ((db_dump = fopen_priv(filename, "w")) == NULL) + if ((db_dump = fopen_priv(filename, PG_BINARY_W)) == NULL) pg_log(PG_FATAL, "Could not write to dump file \"%s\": %s\n", filename, getErrorText(errno));current_output = globals_dump; diff --git a/contrib/pg_upgrade/test.sh b/contrib/pg_upgrade/test.sh index d411ac6..3899600 100644 --- a/contrib/pg_upgrade/test.sh +++ b/contrib/pg_upgrade/test.sh @@ -128,10 +128,6 @@ else sh ./delete_old_cluster.sh fi-if [ $testhost = Msys ] ; then
- dos2unix "$temp_root"/dump1.sql "$temp_root"/dump2.sql
-fi
-
if diff -q "$temp_root"/dump1.sql "$temp_root"/dump2.sql; then
echo PASSED
exit 0
I reviewed this idea and supports this patch's inclusion in 9.2. I was
unclear why it was needed, but I see pg_dumpall, which is the file
pg_upgrade splits apart, as also using binary mode to write this file:
OPF = fopen(filename, PG_BINARY_W);
I agree with Tom that pg_upgrade needs some quiet time. ;-) Andrew,
have a sufficient number of buildfarm members verified our recent
patches that this can be added. My patch from last night was mostly C
comments so isn't something that needs testing.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On 09/05/2012 09:11 AM, Bruce Momjian wrote:
I reviewed this idea and supports this patch's inclusion in 9.2. I was
unclear why it was needed, but I see pg_dumpall, which is the file
pg_upgrade splits apart, as also using binary mode to write this file:OPF = fopen(filename, PG_BINARY_W);
I agree with Tom that pg_upgrade needs some quiet time. ;-) Andrew,
have a sufficient number of buildfarm members verified our recent
patches that this can be added. My patch from last night was mostly C
comments so isn't something that needs testing.
I am quite happy not committing anything for now.
There are two buildfarm members doing pg_upgrade tests: crake (Fedora
16) and pitta (Windows/Mingw64). The buildfarm code is experimental and
not in any release yet, and when it is the test will be optional.
The PG_BINARY_W change has only been verified on a non-buildfarm setup
on my laptop (Mingw)
Note that while it does look like there's a bug either in pg_upgrade or
pg_dumpall, it's probably mostly harmless (adding some spurious CRs to
function code bodies on Windows). I'd feel happier if it didn't, and
happier still if I knew for sure the ultimate origin. Your pg_dumpall
discovery above is interesting. I might have time later on today to
delve into all this. I'm out of contact for the next few hours.
cheers
andrew
On 09/05/2012 09:46 AM, Andrew Dunstan wrote:
On 09/05/2012 09:11 AM, Bruce Momjian wrote:
I reviewed this idea and supports this patch's inclusion in 9.2. I was
unclear why it was needed, but I see pg_dumpall, which is the file
pg_upgrade splits apart, as also using binary mode to write this file:OPF = fopen(filename, PG_BINARY_W);
I agree with Tom that pg_upgrade needs some quiet time. ;-) Andrew,
have a sufficient number of buildfarm members verified our recent
patches that this can be added. My patch from last night was mostly C
comments so isn't something that needs testing.I am quite happy not committing anything for now.
There are two buildfarm members doing pg_upgrade tests: crake (Fedora
16) and pitta (Windows/Mingw64). The buildfarm code is experimental
and not in any release yet, and when it is the test will be optional.The PG_BINARY_W change has only been verified on a non-buildfarm setup
on my laptop (Mingw)Note that while it does look like there's a bug either in pg_upgrade
or pg_dumpall, it's probably mostly harmless (adding some spurious CRs
to function code bodies on Windows). I'd feel happier if it didn't,
and happier still if I knew for sure the ultimate origin. Your
pg_dumpall discovery above is interesting. I might have time later on
today to delve into all this. I'm out of contact for the next few hours.
OK, I now have a complete handle on what's going on here, and withdraw
my earlier statement that I am confused on this issue :-)
First, one lot of CRs is produced because the pg_upgrade test script
calls pg_dumpall without -f and redirects that to a file, which Windows
kindly opens on text mode. The solution to that is to change the test
script to use pg_dumpall -f instead.
The second lot of CRs (seen in the second dump file in the diff i
previously sent) is produced by pg_upgrade writing its output in text
mode, which turns LF into CRLF. The solution to that is the patch to
dump.c I posted, which, as Bruce observed, does the same thing that
pg_dumpall does. Arguably, it should also open the input file in binary,
so that if there really is a CRLF in the dump it won't be eaten.
Another question is whether or not pg_dumpall (and pg_dump in text mode
too for that matter) should be trying to suppress newline translation on
its output even to stdout. It already does that for non-text formats
(see call to setmode()) but I don't see why we shouldn't for text as
well. But those are obviously longstanding bugs that we can leave to
another day.
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> writes:
OK, I now have a complete handle on what's going on here, and withdraw
my earlier statement that I am confused on this issue :-)
First, one lot of CRs is produced because the pg_upgrade test script
calls pg_dumpall without -f and redirects that to a file, which Windows
kindly opens on text mode. The solution to that is to change the test
script to use pg_dumpall -f instead.
The second lot of CRs (seen in the second dump file in the diff i
previously sent) is produced by pg_upgrade writing its output in text
mode, which turns LF into CRLF. The solution to that is the patch to
dump.c I posted, which, as Bruce observed, does the same thing that
pg_dumpall does. Arguably, it should also open the input file in binary,
so that if there really is a CRLF in the dump it won't be eaten.
+1 to all the above. Do we want to risk squeezing this into 9.2.0,
or is it better to delay?
Another question is whether or not pg_dumpall (and pg_dump in text mode
too for that matter) should be trying to suppress newline translation on
its output even to stdout.
I'm inclined to think not - we've not heard any complaints from Windows
users about its current behavior, and it's been like that forever.
regards, tom lane
On Wed, Sep 5, 2012 at 03:17:40PM -0400, Andrew Dunstan wrote:
The PG_BINARY_W change has only been verified on a non-buildfarm
setup on my laptop (Mingw)Note that while it does look like there's a bug either in
pg_upgrade or pg_dumpall, it's probably mostly harmless (adding
some spurious CRs to function code bodies on Windows). I'd feel
happier if it didn't, and happier still if I knew for sure the
ultimate origin. Your pg_dumpall discovery above is interesting. I
might have time later on today to delve into all this. I'm out of
contact for the next few hours.OK, I now have a complete handle on what's going on here, and
withdraw my earlier statement that I am confused on this issue :-)First, one lot of CRs is produced because the pg_upgrade test script
calls pg_dumpall without -f and redirects that to a file, which
Windows kindly opens on text mode. The solution to that is to change
the test script to use pg_dumpall -f instead.The second lot of CRs (seen in the second dump file in the diff i
previously sent) is produced by pg_upgrade writing its output in
text mode, which turns LF into CRLF. The solution to that is the
patch to dump.c I posted, which, as Bruce observed, does the same
thing that pg_dumpall does. Arguably, it should also open the input
file in binary, so that if there really is a CRLF in the dump it
won't be eaten.
So, right now we are only add \r for function bodies, which is mostly
harmless, but what if a function body has strings with an embedded
newlines? What about creating a table with newlines in its identifiers:
CREATE TABLE "a
b" ("c
d" int);
If \r is added in there, it would be a data corruption problem. Can you
test that?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On 09/05/2012 03:36 PM, Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
OK, I now have a complete handle on what's going on here, and withdraw
my earlier statement that I am confused on this issue :-)
First, one lot of CRs is produced because the pg_upgrade test script
calls pg_dumpall without -f and redirects that to a file, which Windows
kindly opens on text mode. The solution to that is to change the test
script to use pg_dumpall -f instead.
The second lot of CRs (seen in the second dump file in the diff i
previously sent) is produced by pg_upgrade writing its output in text
mode, which turns LF into CRLF. The solution to that is the patch to
dump.c I posted, which, as Bruce observed, does the same thing that
pg_dumpall does. Arguably, it should also open the input file in binary,
so that if there really is a CRLF in the dump it won't be eaten.+1 to all the above. Do we want to risk squeezing this into 9.2.0,
or is it better to delay?
When we (particularly Bruce and I) didn't fully understand what was
happening there was a good argument for delay, but now I'd rather put it
in so we can remove the error-hiding hack in the test script. I think
the risk is minimal.
cheers
andrew
On 09/05/2012 03:40 PM, Bruce Momjian wrote:
On Wed, Sep 5, 2012 at 03:17:40PM -0400, Andrew Dunstan wrote:
The PG_BINARY_W change has only been verified on a non-buildfarm
setup on my laptop (Mingw)Note that while it does look like there's a bug either in
pg_upgrade or pg_dumpall, it's probably mostly harmless (adding
some spurious CRs to function code bodies on Windows). I'd feel
happier if it didn't, and happier still if I knew for sure the
ultimate origin. Your pg_dumpall discovery above is interesting. I
might have time later on today to delve into all this. I'm out of
contact for the next few hours.OK, I now have a complete handle on what's going on here, and
withdraw my earlier statement that I am confused on this issue :-)First, one lot of CRs is produced because the pg_upgrade test script
calls pg_dumpall without -f and redirects that to a file, which
Windows kindly opens on text mode. The solution to that is to change
the test script to use pg_dumpall -f instead.The second lot of CRs (seen in the second dump file in the diff i
previously sent) is produced by pg_upgrade writing its output in
text mode, which turns LF into CRLF. The solution to that is the
patch to dump.c I posted, which, as Bruce observed, does the same
thing that pg_dumpall does. Arguably, it should also open the input
file in binary, so that if there really is a CRLF in the dump it
won't be eaten.So, right now we are only add \r for function bodies, which is mostly
harmless, but what if a function body has strings with an embedded
newlines? What about creating a table with newlines in its identifiers:CREATE TABLE "a
b" ("c
d" int);If \r is added in there, it would be a data corruption problem. Can you
test that?
These are among the reasons why I am suggesting opening the file in
binary mode. You're right, that would be data corruption.
I can set up a check, but it will take a bit of time.
cheers
andrew
On Wed, Sep 5, 2012 at 03:50:13PM -0400, Andrew Dunstan wrote:
The second lot of CRs (seen in the second dump file in the diff i
previously sent) is produced by pg_upgrade writing its output in
text mode, which turns LF into CRLF. The solution to that is the
patch to dump.c I posted, which, as Bruce observed, does the same
thing that pg_dumpall does. Arguably, it should also open the input
file in binary, so that if there really is a CRLF in the dump it
won't be eaten.So, right now we are only add \r for function bodies, which is mostly
harmless, but what if a function body has strings with an embedded
newlines? What about creating a table with newlines in its identifiers:CREATE TABLE "a
b" ("c
d" int);If \r is added in there, it would be a data corruption problem. Can you
test that?These are among the reasons why I am suggesting opening the file in
binary mode. You're right, that would be data corruption.I can set up a check, but it will take a bit of time.
My only point is that this is no longer a buildfarm failure issue, it is
a potential data corruption issue.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On 09/05/2012 03:50 PM, Andrew Dunstan wrote:
On 09/05/2012 03:40 PM, Bruce Momjian wrote:
On Wed, Sep 5, 2012 at 03:17:40PM -0400, Andrew Dunstan wrote:
The PG_BINARY_W change has only been verified on a non-buildfarm
setup on my laptop (Mingw)Note that while it does look like there's a bug either in
pg_upgrade or pg_dumpall, it's probably mostly harmless (adding
some spurious CRs to function code bodies on Windows). I'd feel
happier if it didn't, and happier still if I knew for sure the
ultimate origin. Your pg_dumpall discovery above is interesting. I
might have time later on today to delve into all this. I'm out of
contact for the next few hours.OK, I now have a complete handle on what's going on here, and
withdraw my earlier statement that I am confused on this issue :-)First, one lot of CRs is produced because the pg_upgrade test script
calls pg_dumpall without -f and redirects that to a file, which
Windows kindly opens on text mode. The solution to that is to change
the test script to use pg_dumpall -f instead.The second lot of CRs (seen in the second dump file in the diff i
previously sent) is produced by pg_upgrade writing its output in
text mode, which turns LF into CRLF. The solution to that is the
patch to dump.c I posted, which, as Bruce observed, does the same
thing that pg_dumpall does. Arguably, it should also open the input
file in binary, so that if there really is a CRLF in the dump it
won't be eaten.So, right now we are only add \r for function bodies, which is mostly
harmless, but what if a function body has strings with an embedded
newlines? What about creating a table with newlines in its identifiers:CREATE TABLE "a
b" ("c
d" int);If \r is added in there, it would be a data corruption problem. Can you
test that?These are among the reasons why I am suggesting opening the file in
binary mode. You're right, that would be data corruption.I can set up a check, but it will take a bit of time.
As expected, we get a difference in field names. Here's the extract from
the dumps diff (* again represents CR):
***************
*** 5220,5228 ****
--
CREATE TABLE hasnewline (
! "x
y" integer,
! "a
b" text
);
--- 5220,5228 ----
-- CREATE TABLE hasnewline (
! "x*
y" integer,
! "a*
b" text
);
If we open the input and output files in binary mode in pg_upgrade's
dump.c this disappears.
Given this, I think we have no choice but to apply the patch, all the
way back to 9.0 in fact.
cheers
andrew
On Wed, Sep 5, 2012 at 04:22:18PM -0400, Andrew Dunstan wrote:
So, right now we are only add \r for function bodies, which is mostly
harmless, but what if a function body has strings with an embedded
newlines? What about creating a table with newlines in its identifiers:CREATE TABLE "a
b" ("c
d" int);If \r is added in there, it would be a data corruption problem. Can you
test that?These are among the reasons why I am suggesting opening the file
in binary mode. You're right, that would be data corruption.I can set up a check, but it will take a bit of time.
As expected, we get a difference in field names. Here's the extract
from the dumps diff (* again represents CR):***************
*** 5220,5228 ****
--CREATE TABLE hasnewline (
! "x
y" integer,
! "a
b" text
);--- 5220,5228 ---- --CREATE TABLE hasnewline (
! "x*
y" integer,
! "a*
b" text
);If we open the input and output files in binary mode in pg_upgrade's
dump.c this disappears.Given this, I think we have no choice but to apply the patch, all
the way back to 9.0 in fact.
I think you are right.
I think I could use some "quite time" right now, as Tom suggested. ;-)
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +