Обсуждение: 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
>