Tom Lane wrote:
 > We can go with the auto-COMMIT idea for statements that are invoked at
 > the outer interactive level, but that doesn't work for stuff invoked
 > inside a function.  I think we need to forbid these statements inside
 > functions, too.  We already have that for VACUUM, because of its
 > internal commits causing problems for functions, but we'll need to
 > extend it to all of them.
 >
 > Just FYI, the statements involved are
 > VACUUM
 > TRUNCATE TABLE
 > CREATE/DROP DATABASE

I just noticed that this afternoon's changes cause dblink's regression test to 
fail due to:

CREATE OR REPLACE FUNCTION conditional_drop()
[...]
     IF FOUND THEN
         DROP DATABASE regression_slave;
     END IF;
[...]
' LANGUAGE 'plpgsql';
SELECT conditional_drop();


I'm wondering what is the best alternative? Should we simply do a "DROP 
DATABASE regression_slave;" and have the expected results include the 
'database "regression_slave" does not exist'? In this case there would be an 
expected failure whenever the regression test was run more than once.

Joe


Mon, 21 Oct 2002 22:25:42 -0700
Joe Conway <mail@joeconway.com>
Re: autocommit vs TRUNCATE et al
Joe Conway <mail@joeconway.com> writes:
> I just noticed that this afternoon's changes cause dblink's regression
> test to fail due to:

> CREATE OR REPLACE FUNCTION conditional_drop()
> [...]
>      IF FOUND THEN
>          DROP DATABASE regression_slave;
>      END IF;
> [...]
> ' LANGUAGE 'plpgsql';
> SELECT conditional_drop();

> I'm wondering what is the best alternative?

Well, the *best* alternative would be to make CREATE/DROP DATABASE
transaction-safe ;-).  I was speculating to myself earlier today about
how we might do that.  It seems like it's not that far out of reach:
we could make smgr's list of files-to-remove-at-xact-commit-or-abort
include whole database subdirectories.  But I'm not sure how that would
interact with upcoming features like tablespaces, so I don't want to
go off and implement it right now.

In the meantime, to tell you the truth, the cleanest way to handle the
dblink regression test would be to make it circularly connect to
database "regression".  I know this seems cheesy, but as long as the
software under test doesn't know that it's a connection-to-self, seems
like the test is perfectly good.  And it's surely easier to manage that
way.

			regards, tom lane

Tue, 22 Oct 2002 01:38:01 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: autocommit vs TRUNCATE et al 
Tom Lane wrote:
> In the meantime, to tell you the truth, the cleanest way to handle the
> dblink regression test would be to make it circularly connect to
> database "regression".  I know this seems cheesy, but as long as the
> software under test doesn't know that it's a connection-to-self, seems
> like the test is perfectly good.  And it's surely easier to manage that
> way.

OK, easy enough. Patch attached.

I also added "SET autocommit TO ''on'';" to the beginning of each dblink_exec 
input statement because the "SET autocommit TO 'on';" at the top of the script 
won't help for the connected database.

Joe
Mon, 21 Oct 2002 23:02:48 -0700
Joe Conway <mail@joeconway.com>
Re: autocommit vs TRUNCATE et al
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> > I just noticed that this afternoon's changes cause dblink's regression
> > test to fail due to:
> 
> > CREATE OR REPLACE FUNCTION conditional_drop()
> > [...]
> >      IF FOUND THEN
> >          DROP DATABASE regression_slave;
> >      END IF;
> > [...]
> > ' LANGUAGE 'plpgsql';
> > SELECT conditional_drop();
> 
> > I'm wondering what is the best alternative?
> 
> Well, the *best* alternative would be to make CREATE/DROP DATABASE
> transaction-safe ;-).  I was speculating to myself earlier today about
> how we might do that.  It seems like it's not that far out of reach:
> we could make smgr's list of files-to-remove-at-xact-commit-or-abort
> include whole database subdirectories.  But I'm not sure how that would
> interact with upcoming features like tablespaces, so I don't want to
> go off and implement it right now.

FYI, the MSWin port in 7.4 will have C versions of 'cp' and 'rm -r', so
those can be used to hook into the smgr layer for all platforms.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 22 Oct 2002 12:50:56 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: autocommit vs TRUNCATE et al