Обсуждение: Connection closed
Hi, I got the following message when executing a plpgsql function: ------ NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. ------ I didn't think it was possible to make a plpgsql function crash this way, but appearantly is was :( Are there any common traps which I should look for? Erik __________________________________________________ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer
I just discovered an interesting thing: the problem comes from this code: EXECUTE ''ALTER TABLE '' || dv || '' ADD CONSTRAINT idfk FOREIGN KEY (_id) REFERENCES dt_'' || a_table; If I put it into a temp variable, it doesn't help: temp := ''ALTER TABLE '' || dv || '' ADD CONSTRAINT idfk FOREIGN KEY (_id) REFERENCES dt_'' || a_table; EXECUTE temp; However, if I raise a notice just before the EXECUTE statement, it suddenly works! temp := ''ALTER TABLE '' || dv || '' ADD CONSTRAINT idfk FOREIGN KEY (_id) REFERENCES dt_'' || a_table; RAISE NOTICE ''%'', temp; EXECUTE temp; Strange, IMO. Erik __________________________________________________ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer
=?iso-8859-1?q?Erik=20Ronstr=F6m?= <kvarken@yahoo.com> writes: > I just discovered an interesting thing: the problem comes from this > code: > EXECUTE ''ALTER TABLE '' || dv || '' ADD CONSTRAINT idfk FOREIGN KEY > (_id) REFERENCES dt_'' || a_table; You're going to need to provide a self-contained example, or else a stack trace. This simple experiment didn't reproduce it: regression=# create function doit(text) returns int as ' regression'# begin regression'# execute $1; regression'# return 1; regression'# end' language plpgsql; CREATE FUNCTION regression=# create table ff1 (f1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'ff1_pkey' for table 'ff1' CREATE TABLE regression=# create table ff2 (f1 int); CREATE TABLE regression=# select doit('alter table ff2 add constraint idfk foreign key(f1) regression'# references ff1'); NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) doit ------ 1 (1 row) regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > You're going to need to provide a self-contained example, or else a > stack trace. [...] I'm quite a beginner of pgsql, and there are some things I don't know: - How do I print a stack trace? - Is there a log file somewhere which is more extensive than the outputted DEBUG and NOTICE messages? BTW, I realized that the errors seem to appear randomly; I tried to run the function several times with the same parameters, and it seems to work about half of the times. I'm using version 7.2.1, and I saw somewhere that it may be buggy. Can it be a bug? Should I upgrade to 7.2.3? Thanks for your answer Erik __________________________________________________ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer
If you've got connections randomly closing, it's also likely you have flakey memory or hardware. www.memtest86.com is a good tester. Postgresql is good, but it can't make up for broken hardware. And broken hardware is way more common than most people think. On Fri, 2 May 2003, Erik Ronström wrote: > --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > > You're going to need to provide a self-contained example, or else a > > stack trace. [...] > > I'm quite a beginner of pgsql, and there are some things I don't know: > - How do I print a stack trace? > - Is there a log file somewhere which is more extensive than the > outputted DEBUG and NOTICE messages? > > BTW, I realized that the errors seem to appear randomly; I tried to run > the function several times with the same parameters, and it seems to > work about half of the times. > > I'm using version 7.2.1, and I saw somewhere that it may be buggy. Can > it be a bug? Should I upgrade to 7.2.3? > > Thanks for your answer > Erik > > __________________________________________________ > Yahoo! Plus > For a better Internet experience > http://www.yahoo.co.uk/btoffer > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >