Thread: Error craches pgAdmin
<div class="Section1"><p class="MsoNormal"><span lang="EN-US">Hi,</span><p class="MsoNormal"><span lang="EN-US">I found adifferent pgAdmin behavior if I use it against Postgres 8.1.15 or 8.2.0. Here to try it out</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">I have a table filled with dates :</span><pclass="MsoNormal"><i><span lang="EN-US">CREATE TABLE _master_h24 (</span></i><p class="MsoNormal"><i><span lang="EN-US"> fulldate timestamp without time zone NOT NULL,</span></i><p class="MsoNormal"><i><span lang="EN-US"> CONSTRAINT_master_h24_pkey PRIMARY KEY (fulldate)</span></i><p class="MsoNormal"><i><span lang="EN-US">) WITHOUT OIDS;</span></i><pclass="MsoNormal"><i><span lang="EN-US"> </span></i><p class="MsoNormal"><span lang="EN-US">I have a functionto fulfill it with dates :</span><p class="MsoNormal"><span lang="EN-US">CREATE OR REPLACE FUNCTION fillmastertable_h24()</span><pclass="MsoNormal"><span lang="EN-US"> RETURNS timestamp without time zone AS</span><p class="MsoNormal"><spanlang="EN-US">$BODY$</span><p class="MsoNormal"><span lang="EN-US">declare</span><p class="MsoNormal"><spanlang="EN-US"> dt_now timestamp;</span><p class="MsoNormal"><span lang="EN-US"> dt_last timestamp;</span><pclass="MsoNormal"><span lang="EN-US"> max_loops INTEGER;</span><p class="MsoNormal"><span lang="EN-US"> v INTEGER;</span><p class="MsoNormal"><span lang="EN-US">BEGIN</span><p class="MsoNormal"><span lang="EN-US"> -- gets the last update</span><p class="MsoNormal"><span lang="EN-US"> SELECT fulldate INTO dt_last FROM_master_h24 ORDER BY fulldate DESC LIMIT 1;</span><p class="MsoNormal"><span lang="EN-US"> --RAISE NOTICE 'last :% ', dt_last;</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> -- getsthe gmt - 1 hour date time</span><p class="MsoNormal"><span lang="EN-US"> dt_now := to_timestamp(TIMEZONE('WAT',CURRENT_TIMESTAMP),'YYYY-MM-DD');</span><p class="MsoNormal"><span lang="EN-US"> --RAISE NOTICE'dt_now : % ', dt_now;</span><p class="MsoNormal"><span lang="EN-US"> max_loops := 100;</span><p class="MsoNormal"><spanlang="EN-US"> v := 0;</span><p class="MsoNormal"><span lang="EN-US"> WHILE dt_last < dt_nowAND v < max_loops loop</span><p class="MsoNormal"><span lang="EN-US"> v := v + 1;</span><p class="MsoNormal"><spanlang="EN-US"> dt_last := dt_last + '24 HOUR'::INTERVAL;</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US"> /* execute query */</span><p class="MsoNormal"><span lang="EN-US"> BEGIN</span><p class="MsoNormal"><span lang="EN-US"> RAISE NOTICE 'Dt : % ', dt_last;</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> insert into _master_24 (fulldate)VALUES (dt_last); /* </span><span lang="EN-US" style="font-family:Wingdings">ß</span><spanlang="EN-US"> <- HERE IS THE TABLE MISSPELLING (_master_24 ) */</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> /* errors check */</span><p class="MsoNormal"><spanlang="EN-US"> EXCEPTION</span><p class="MsoNormal"><span lang="EN-US"> /* in case of anyerror */</span><p class="MsoNormal"><span lang="EN-US"> WHEN OTHERS THEN RAISE NOTICE 'ERROR in fillmastertable_h24';</span><pclass="MsoNormal"><span lang="EN-US"> END;</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US"> END loop;</span><p class="MsoNormal"><span lang="EN-US"> return dt_last;</span><p class="MsoNormal"><span lang="EN-US">END;</span><p class="MsoNormal"><span lang="EN-US">$BODY$</span><pclass="MsoNormal"><span lang="EN-US"> LANGUAGE 'plpgsql' VOLATILE;</span><p class="MsoNormal"><spanlang="EN-US">ALTER FUNCTION fillmastertable_h24() OWNER TO postgres;</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">I then insert the first date to beginwith :</span><p class="MsoNormal"><span lang="EN-US">insert into _master_h24 (fulldate) VALUES ('2006-12-01'); ->OK</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">I run the script :</span><pclass="MsoNormal"><span lang="EN-US">SELECT fillmastertable_h24();</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">And on Postgres 8.1.15 I get back ‘ERROR: “relation _master_24”does not exist -> OK</span><p class="MsoNormal"><span lang="EN-US">While on Postgres 8.2.0 I get back only“:” and pgAdmin craches loosing the connection to the server. If I click on the server node I get the following messagebox : </span><p class="MsoNormal"><span lang="EN-US">An error has occurred:</span><p class="MsoNormal"><span lang="EN-US">Serverclosed the connection unexpectedly</span><p class="MsoNormal"><span lang="EN-US">This probably means theserver terminated abnormally before or while processing the request</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">I don’t know if depends on pgAdmin or the server itself.</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">Thanks,</span><p class="MsoNormal"><spanlang="EN-US">Paolo Saudin</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span></div>
Hi, This is a PostgreSQL bug, not pgAdmin so I've forwarded it to pgsql-bugs@postgresql.org. Thanks, Dave. Paolo Saudin wrote: > Hi, > > I found a different pgAdmin behavior if I use it against Postgres > 8.1.15 or 8.2.0. Here to try it out > > > > I have a table filled with dates : > > /CREATE TABLE _master_h24 (/ > > / fulldate timestamp without time zone NOT NULL,/ > > / CONSTRAINT _master_h24_pkey PRIMARY KEY (fulldate)/ > > /) WITHOUT OIDS;/ > > / / > > I have a function to fulfill it with dates : > > CREATE OR REPLACE FUNCTION fillmastertable_h24() > > RETURNS timestamp without time zone AS > > $BODY$ > > declare > > dt_now timestamp; > > dt_last timestamp; > > max_loops INTEGER; > > v INTEGER; > > BEGIN > > -- gets the last update > > SELECT fulldate INTO dt_last FROM _master_h24 ORDER BY fulldate DESC > LIMIT 1; > > --RAISE NOTICE 'last : % ', dt_last; > > > > -- gets the gmt - 1 hour date time > > dt_now := to_timestamp(TIMEZONE('WAT',CURRENT_TIMESTAMP), 'YYYY-MM-DD'); > > --RAISE NOTICE 'dt_now : % ', dt_now; > > max_loops := 100; > > v := 0; > > WHILE dt_last < dt_now AND v < max_loops loop > > v := v + 1; > > dt_last := dt_last + '24 HOUR'::INTERVAL; > > > > /* execute query */ > > BEGIN > > RAISE NOTICE 'Dt : % ', dt_last; > > > > insert into _master_24 (fulldate) VALUES > (dt_last); /* ß <- HERE IS THE TABLE MISSPELLING > (_master_24 ) */ > > > > /* errors check */ > > EXCEPTION > > /* in case of any error */ > > WHEN OTHERS THEN RAISE NOTICE 'ERROR in fillmastertable_h24'; > > END; > > > > END loop; > > return dt_last; > > END; > > $BODY$ > > LANGUAGE 'plpgsql' VOLATILE; > > ALTER FUNCTION fillmastertable_h24() OWNER TO postgres; > > > > I then insert the first date to begin with : > > insert into _master_h24 (fulldate) VALUES ('2006-12-01'); -> OK > > > > I run the script : > > SELECT fillmastertable_h24(); > > > > And on Postgres 8.1.15 I get back ‘ERROR: “relation _master_24” does not > exist -> OK > > While on Postgres 8.2.0 I get back only “:” and pgAdmin craches loosing > the connection to the server. If I click on the server node I get the > following message box : > > An error has occurred: > > Server closed the connection unexpectedly > > This probably means the server terminated abnormally before or while > processing the request > > > > I don’t know if depends on pgAdmin or the server itself. > > > > Thanks, > > Paolo Saudin > > > > > > > > >