Thread: Error craches pgAdmin

Error craches pgAdmin

From
"Paolo Saudin"
Date:
<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>

Re: Error craches pgAdmin

From
Dave Page
Date:
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
> 
>  
> 
>  
> 
>  
> 
>  
>