Connection closed

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: