Обсуждение: ALTER TABLE ... IF EXISTS feature?

Hello List,

Is there any reason why Postgres should not support an "ALTER TABLE
tablename [IF EXISTS]" feature? (And similar for other ALTER
OBJECTTYPE)

For example, a hypothetical statement that attempts to drop a
constraint in a *completely* optional manner would look like the
following:
 ALTER TABLE IF EXISTS foo DROP CONSTRAINT bar IF EXISTS;

If you think this is already a reasonable idea, you can stop reading
now and express approval at the general idea. If you are curious as to
why *I* encountered it, continue reading.


It would be very useful if "pg_dump --clean" produced output that
could be run on an empty database without errors so that it is easier
to monitor the process exit status of pg_restore to check for problems
as well as enabling the use with one-transaction restore. The former
is a vital feature for organizations that manage the creation and
monitoring of many backups.

In addition, these semantics are pretty familiar: they are not
dissimilar from "make clean".

As-is, you are very likely to hit errors upon restoring a dump with
--clean in most schemas. The degree of "most" here includes "schemas
that use any form of foreign key" as a subset, so this is a rather
large set. I encountered this problem through first hoping that
meeting this requirement was entirely possible by changing pg_dump
alone. The first hurdle was:
 DROP TABLE foo;

A straightforward pg_dump change can change this to:
 DROP TABLE foo IF EXISTS;

Things then got tricky when dealing with the constraints on domains
and tables. In order to clean tables, for example, pg_dump will
reverse its topological sort on the dependencies and emit drop
statements for the dumpable objects individually. This ensures that
within the scope of objects to be dumped that all dependent objects
(the constraints, in this case) are dropped first, then finally the
table. Thus, one will see the following prior to dropping the table:
 ALTER TABLE foo DROP CONSTRAINT bar;

While one can easily fix part of a problem by emitting the following instead:
 ALTER TABLE foo DROP CONSTRAINT bar IF EXISTS;

One is still stuck on not being able to not-error when there's a
missing "foo" table. And so there is an impasse that requires some
grammar and DDL machinery changes on the server side.

fdr

Daniel Farina <drfarina@acm.org> writes:
> Is there any reason why Postgres should not support an "ALTER TABLE
> tablename [IF EXISTS]" feature?

I think you've phrased the question backwards.  Why *should* we support
that large increment of complexity?  The use-cases seem pretty few and
far between.
        regards, tom lane

On Nov 5, 2010, at 10:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Daniel Farina <drfarina@acm.org> writes:
>> Is there any reason why Postgres should not support an "ALTER TABLE
>> tablename [IF EXISTS]" feature?
> 
> I think you've phrased the question backwards.  Why *should* we support
> that large increment of complexity?  The use-cases seem pretty few and
> far between.

Obviously we have different definitions of "a large increment in complexity".

...Robert

Robert Haas <robertmhaas@gmail.com> writes:
> On Nov 5, 2010, at 10:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I think you've phrased the question backwards.  Why *should* we support
>> that large increment of complexity?  The use-cases seem pretty few and
>> far between.

> Obviously we have different definitions of "a large increment in complexity".

Well,

(1) the proposal affects a large number of commands

(2) in some cases the correct behavior isn't obvious (I note Daniel's
example had *two* IF EXISTS options in one command...)

(3) it raises the bar of expectation for every future ALTER command

That spells "large maintenance burden" to me, even if any one command
would be relatively simple to fix.  And we haven't even reached the
question of whether pg_dump could use these things usefully; I suspect
that the bottom-line issue there might be something else entirely.
        regards, tom lane

On Fri, Nov 5, 2010 at 8:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> That spells "large maintenance burden" to me, even if any one command
> would be relatively simple to fix.  And we haven't even reached the
> question of whether pg_dump could use these things usefully; I suspect
> that the bottom-line issue there might be something else entirely.

Well, why not? I was in the middle of writing it. But I'm open to
other approaches, the real motivating issue could be stated simply as:

"pg_dump --clean should produce output that can be run on empty, full
or partially-full databases in a transaction without a non-zero error
code"

ALTER IF EXISTS just struck me as one such mechanism that is familiar
looking, at least.

Also take note that I suffered from some syntactic dyslexia above, IF
EXISTS doesn't appear to ever occur after the identifier, so the
example would be:
   ALTER TABLE IF EXISTS foo DROP CONSTRAINT IF EXISTS bar;

To me this reads fine, although I agree that it could be annoying to
main this optional-ness on a large series of commands.

One might be able to imagine a special backend function to do this
missing-identifier guarding, that may shorten the whole thing up a
bit...any suggestions, in that line of thinking or others?

fdr

Daniel Farina <drfarina@acm.org> writes:
> Well, why not? I was in the middle of writing it. But I'm open to
> other approaches, the real motivating issue could be stated simply as:

> "pg_dump --clean should produce output that can be run on empty, full
> or partially-full databases in a transaction without a non-zero error
> code"

[ shrug... ]  I'm less than convinced that that's an important goal.
If you don't know what it is you're overwriting, maybe you should
stop and think what you're doing, instead of expecting pg_dump to
silently stomp on portions of whatever you already have.

What you're proposing would maybe be useful for overwriting a database
that contains portions of what is in the source database, but what's
the use of that?  You could just as well dropdb and start fresh.  The
interesting case here is where the target db has *more* objects than
are in the dump, and I really doubt that there is any self-consistent
behavior that pg_dump can automatically provide for such cases.  It
can't drop objects it doesn't know about, and it also has no hope of
ensuring that their relationships to the replaced objects remain
consistent.  Silently wiping out FKs, for instance, seems like a pretty
bad idea.

So, basically, I've never seen any fully credible use case for pg_dump
--clean, and this proposal does nothing to fix that.  I don't see the
argument for putting a large amount of work down that rathole.
Especially not if the only benefit you get out of it is not looking
at error messages.  In most cases you'd *better* study those error
messages, to see what manual cleanup you're going to need to undertake.
        regards, tom lane

On Fri, Nov 5, 2010 at 10:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> What you're proposing would maybe be useful for overwriting a database
> that contains portions of what is in the source database, but what's
> the use of that?  You could just as well dropdb and start fresh.  The
> interesting case here is where the target db has *more* objects than
> are in the dump, and I really doubt that there is any self-consistent
> behavior that pg_dump can automatically provide for such cases.  It
> can't drop objects it doesn't know about, and it also has no hope of
> ensuring that their relationships to the replaced objects remain
> consistent.  Silently wiping out FKs, for instance, seems like a pretty
> bad idea.

I am somewhat sympathetic to this argument, except for one thing:

pg_dump --clean will successfully and silently wipe out a foreign key
right now, should it exist, especially considering the default mode is
not to run in a single transaction; a bunch of errors will be spit out
to the log (except in the case where it will, as you say, silently
wipe out a FK), but it'll probably be too late to do anything.

It only complains (and then fails to act anyway, as there is no object
to act upon) when no such object exists. One way to ensure it doesn't
delete a foreign key that makes less sense is to ensure that all
dependents are to-be-dumped DependentObjects in pg_dump.
Pattern-matching/partial pg_dumps have never been sound in the general
case anyway.

> In most cases you'd *better* study those error messages, to see what manual cleanup you're going to need to
undertake.

Of what possible use to me is a transaction-aborting error message and
state emitted by an ALTER that is then succeeded by a series of DROP
(TABLE|DOMAIN) statements that encompass the dependents anyway?

I think an error message saying "couldn't drop this table because
other objects that were not named by the cleaning-dump depend on it"
falls into the category of interesting error message that should abort
the transaction. An error condition/xact abort complaining about a
missing constraint for tables that are about to have DROP statements
execute for both of them is considerably less useful.

> So, basically, I've never seen any fully credible use case for pg_dump --clean

Then why is it not on the road to deprecation, or at least given a
strong warning to not use it in the --help message (or is it)? As-is I
think it may only exist to misguide people.

fdr

Daniel Farina <drfarina@acm.org> writes:
> I am somewhat sympathetic to this argument, except for one thing:

> pg_dump --clean will successfully and silently wipe out a foreign key
> right now, should it exist,

No, it will not, because we don't use CASCADE in the drop commands.

The case I'm thinking about is where we are trying to --clean the PK
table, and the referencing table is one we don't know about.  The other
way around, the FK constraint will disappear, but that's arguably less
problematic.
        regards, tom lane

On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Daniel Farina <drfarina@acm.org> writes:
>> I am somewhat sympathetic to this argument, except for one thing:
>
>> pg_dump --clean will successfully and silently wipe out a foreign key
>> right now, should it exist,
>
> No, it will not, because we don't use CASCADE in the drop commands.

I know it does not use CASCADE, but if I understand it correctly,
foreign keys are dropped between tables, and then the tables are
dropped. (effectively a manual cascade) In not-single-transaction
mode, this can cause a foreign key to get dropped, but then the DROP
TABLE could subsequently fail, leaving two tables that were once
connected no longer connected. I could be wrong about this, but I
think this is the case. If that is not the case, please correct me
before continuing to read...

If the referencing table is not scoped for dumping by pg_dump, then
wouldn't the transaction *correctly* (or perhaps I should say
"desirably") abort upon attempting to drop the PK? Right now pg_dump
--clean is falling down in very common cases where constraints are
being dropped prior to all the related objects being dropped.

I will also make an argument that, provided --clean is regarded as a
feature to have at all, that ensuring it can be used productively in a
well-formed case in single-transaction mode is going to prevent a lot
of mistakes. As-is the user is compelled -- should they opt to use
clean -- to not use single-transaction mode for restores because it's
practically guaranteed to fail all the time should they use foreign
key constraints anywhere, unless they are certain that all objects in
the database being restored into exist with the same relationships,
for sure. Right now to get it to do anything useful it *must* run in
multi-transaction mode so constraint drops can fail in their own
transaction, and that means that you will be left with a database with
some subset of constraints remaining (only discernable by the log).

I could be wrong, but I think the change I'm suggesting is very safe,
and quite potentially safer because users will be more inclined to
restore in a transaction.

fdr

Daniel Farina <drfarina@acm.org> writes:
> On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Daniel Farina <drfarina@acm.org> writes:
>>> pg_dump --clean will successfully and silently wipe out a foreign key
>>> right now, should it exist,
>> 
>> No, it will not, because we don't use CASCADE in the drop commands.

> I know it does not use CASCADE, but if I understand it correctly,
> foreign keys are dropped between tables, and then the tables are
> dropped. (effectively a manual cascade)

You're missing the point.  The scenario I'm concerned about is:
source database contained table foo
target database contains table foo, and table bar, andbar has an FK reference to foo

The FK constraint is not known to pg_dump, and therefore there will not
be a DROP CONSTRAINT for it in the dump.  So the DROP on the referenced
table will fail.  The only way we could make it succeed would be to use
CASCADE, which we don't do specifically because this sort of thing seems
quite unsafe.
        regards, tom lane

On Fri, Nov 5, 2010 at 1:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Daniel Farina <drfarina@acm.org> writes:
>> On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Daniel Farina <drfarina@acm.org> writes:
>>>> pg_dump --clean will successfully and silently wipe out a foreign key
>>>> right now, should it exist,
>>>
>>> No, it will not, because we don't use CASCADE in the drop commands.
>
>> I know it does not use CASCADE, but if I understand it correctly,
>> foreign keys are dropped between tables, and then the tables are
>> dropped. (effectively a manual cascade)
>
> You're missing the point.  The scenario I'm concerned about is:
>
>        source database contained table foo
>
>        target database contains table foo, and table bar, and
>        bar has an FK reference to foo
>

I think that's intended and okay to fail, and would continue to fail
post-patch, if I understand what I am doing correctly (always
suspect).

The only condition where this should be emitted is when all the
dependent objects are going to be dropped anyway.

fdr

On Fri, Nov 5, 2010 at 4:48 PM, Daniel Farina <drfarina@acm.org> wrote:
> On Fri, Nov 5, 2010 at 1:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Daniel Farina <drfarina@acm.org> writes:
>>> On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> Daniel Farina <drfarina@acm.org> writes:
>>>>> pg_dump --clean will successfully and silently wipe out a foreign key
>>>>> right now, should it exist,
>>>>
>>>> No, it will not, because we don't use CASCADE in the drop commands.
>>
>>> I know it does not use CASCADE, but if I understand it correctly,
>>> foreign keys are dropped between tables, and then the tables are
>>> dropped. (effectively a manual cascade)
>>
>> You're missing the point.  The scenario I'm concerned about is:
>>
>>        source database contained table foo
>>
>>        target database contains table foo, and table bar, and
>>        bar has an FK reference to foo
>>
>
> I think that's intended and okay to fail, and would continue to fail
> post-patch, if I understand what I am doing correctly (always
> suspect).
>
> The only condition where this should be emitted is when all the
> dependent objects are going to be dropped anyway.

Dan,

Can you give us a self-contained example of the problem you're talking about?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Nov 5, 2010 at 4:20 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> Can you give us a self-contained example of the problem you're talking about?

Sure. Consider the following:

CREATE TABLE t1 (   id integer PRIMARY KEY
);

CREATE TABLE t2 (   id integer PRIMARY KEY,   fk integer
);

ALTER TABLE ONLY t2   ADD CONSTRAINT t2_constr FOREIGN KEY (fk) REFERENCES t1(id);

Try something like this:

createdb foo
psql -1f this_ddl.sql foo
pg_dump --clean foo > cleaning_backup.sql
# db wipe
dropdb foo
createdb foo
psql -1f cleaning_backup.sql foo

The last command will return non-zero and abort the xact early on,
because of the following stanza in pg_dump --clean's output:

ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_constr;
ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_pkey;
ALTER TABLE ONLY public.t1 DROP CONSTRAINT t1_pkey;
DROP TABLE public.t2;
DROP TABLE public.t1;

Since there's no public.t1/t2, it's not possible to ALTER them.

I'm not entirely sure why the DROPs CONSTRAINT on pkeys are being
done, as they only introduce an internal (or is it auto?) style
self-dependency. It is more obvious why foreign keys are dropped,
which is to break up the dependencies so that tables can be dropped
without CASCADE.

fdr

On Fri, Nov 5, 2010 at 7:49 PM, Daniel Farina <drfarina@acm.org> wrote:
> On Fri, Nov 5, 2010 at 4:20 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> Can you give us a self-contained example of the problem you're talking about?
>
> Sure. Consider the following:
>
> CREATE TABLE t1 (
>    id integer PRIMARY KEY
> );
>
> CREATE TABLE t2 (
>    id integer PRIMARY KEY,
>    fk integer
> );
>
> ALTER TABLE ONLY t2
>    ADD CONSTRAINT t2_constr FOREIGN KEY (fk) REFERENCES t1(id);
>
> Try something like this:
>
> createdb foo
> psql -1f this_ddl.sql foo
> pg_dump --clean foo > cleaning_backup.sql
> # db wipe
> dropdb foo
> createdb foo
> psql -1f cleaning_backup.sql foo
>
> The last command will return non-zero and abort the xact early on,
> because of the following stanza in pg_dump --clean's output:
>
> ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_constr;
> ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_pkey;
> ALTER TABLE ONLY public.t1 DROP CONSTRAINT t1_pkey;
> DROP TABLE public.t2;
> DROP TABLE public.t1;
>
> Since there's no public.t1/t2, it's not possible to ALTER them.
>
> I'm not entirely sure why the DROPs CONSTRAINT on pkeys are being
> done, as they only introduce an internal (or is it auto?) style
> self-dependency. It is more obvious why foreign keys are dropped,
> which is to break up the dependencies so that tables can be dropped
> without CASCADE.

If we're going to try to fix this, we probably ought to try to make
sure that we are fixing it fairly completely.  How confident are you
that this is the only problem?

With respect to the syntax itself, I have mixed feelings.  On the one
hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS
precisely because I believe they handle many common cases that people
want in real life without much hullabaloo.  But, there's clearly some
limit to what can reasonably be done this way.  At some point, what
you really want is some kind of meta-language where you can write
things like:

IF EXISTS TABLE t1 THEN  ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr;
END IF;

...or possibly something much more complicated, like checking whether
a table foo has a column called bar and if so doing nothing but if not
but a column called baz exists then renaming it to bar and otherwise
adding a column called bar.

Since we now have PL/pgsql by default, we could possibly fix pg_dump
--clean by emitting a DO block, although the syntax for checking
existence of a table is none too pretty, and it would make pg_dump
--clean rely for correctness on plpgsql being installed, which might
be none too desirable.  It would actually be sort of spiffy to be able
to have some of the PL/pgsql control constructs available in straight
SQL, but I'm not expecting that to happen any time in the forseeable
future.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Sat, Nov 6, 2010 at 6:09 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> If we're going to try to fix this, we probably ought to try to make
> sure that we are fixing it fairly completely.  How confident are you
> that this is the only problem?

I haven't tried to isolate problems on really complicated schemas yet,
but I can tell you what I did do: I went through pg_dump and tried to
find as many parts of the code that added dropStmt to DumpableObject
nodes as possible (in the dumpFoo family of functions). ALTER seemed
like the only interesting bit so far.

But we can give the mechanic a try with, say, a PLPGSQL hack for a
little while to see if it basically gets the job done in sort-of real
life for a while. I think that's not a bad idea....I'll see what I can
do about that. (Of course, continue to share your suggestions and
revelations, I'd appreciate it)

fdr

On Sat, Nov 6, 2010 at 6:09 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> Since we now have PL/pgsql by default, we could possibly fix pg_dump
> --clean by emitting a DO block, although the syntax for checking
> existence of a table is none too pretty, and it would make pg_dump
> --clean rely for correctness on plpgsql being installed, which might
> be none too desirable.  It would actually be sort of spiffy to be able
> to have some of the PL/pgsql control constructs available in straight
> SQL, but I'm not expecting that to happen any time in the forseeable
> future.

I have hacked up a little set of work-in-progress pg_dump patches as a
proof of concept. It uses the PGSQL and DO mechanics, thanks for
making me aware of the latter...

https://github.com/fdr/postgres/tree/pg_dump-if-exists

I will be giving it a try on some more schemas in the near future. On
my small, plain but not-entirely-a-toy schema it works great.

For some reason I cannot seem to get it to exercise the ALTER DOMAIN
check, even though I created a domain with a check constraint...hmm,
I'll have to stare at what can cause it to exercise that later.

fdr

Robert Haas wrote:
> With respect to the syntax itself, I have mixed feelings.  On the one
> hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS
> precisely because I believe they handle many common cases that people
> want in real life without much hullabaloo.  But, there's clearly some
> limit to what can reasonably be done this way.  At some point, what
> you really want is some kind of meta-language where you can write
> things like:
> 
> IF EXISTS TABLE t1 THEN
>    ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr;
> END IF;

FYI, I have felt this way for a while.  IF EXISTS seemed like something
that should never have been added as an inline SQL command option; it
just crept in, and kept growing.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +

On Wed, Nov 24, 2010 at 4:30 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Robert Haas wrote:
>> With respect to the syntax itself, I have mixed feelings.  On the one
>> hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS
>> precisely because I believe they handle many common cases that people
>> want in real life without much hullabaloo.  But, there's clearly some
>> limit to what can reasonably be done this way.  At some point, what
>> you really want is some kind of meta-language where you can write
>> things like:
>>
>> IF EXISTS TABLE t1 THEN
>>    ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr;
>> END IF;
>
> FYI, I have felt this way for a while.  IF EXISTS seemed like something
> that should never have been added as an inline SQL command option; it
> just crept in, and kept growing.

Okay, that being the case: would it make sense to have pg_dump emit DO
blocks? I have a feeling this might draw fire, but I don't see any
reason why the mechanism would not work to more or less equivalent
effect. Certainly making dumps harder to use for those who insist on
disabling PL/PGSQL is probably a negative side effect, if one can
identify this hypothetical class of person.

fdr

Daniel Farina wrote:
> On Wed, Nov 24, 2010 at 4:30 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Robert Haas wrote:
> >> With respect to the syntax itself, I have mixed feelings. ?On the one
> >> hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS
> >> precisely because I believe they handle many common cases that people
> >> want in real life without much hullabaloo. ?But, there's clearly some
> >> limit to what can reasonably be done this way. ?At some point, what
> >> you really want is some kind of meta-language where you can write
> >> things like:
> >>
> >> IF EXISTS TABLE t1 THEN
> >> ? ?ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr;
> >> END IF;
> >
> > FYI, I have felt this way for a while. ?IF EXISTS seemed like something
> > that should never have been added as an inline SQL command option; it
> > just crept in, and kept growing.
> 
> Okay, that being the case: would it make sense to have pg_dump emit DO
> blocks? I have a feeling this might draw fire, but I don't see any
> reason why the mechanism would not work to more or less equivalent
> effect. Certainly making dumps harder to use for those who insist on
> disabling PL/PGSQL is probably a negative side effect, if one can
> identify this hypothetical class of person.

Not being able to recover a dump is serious problem for a user.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +

On Wed, Nov 24, 2010 at 7:03 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Daniel Farina wrote:
>> On Wed, Nov 24, 2010 at 4:30 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> > Robert Haas wrote:
>> >> With respect to the syntax itself, I have mixed feelings. ?On the one
>> >> hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS
>> >> precisely because I believe they handle many common cases that people
>> >> want in real life without much hullabaloo. ?But, there's clearly some
>> >> limit to what can reasonably be done this way. ?At some point, what
>> >> you really want is some kind of meta-language where you can write
>> >> things like:
>> >>
>> >> IF EXISTS TABLE t1 THEN
>> >> ? ?ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr;
>> >> END IF;
>> >
>> > FYI, I have felt this way for a while. ?IF EXISTS seemed like something
>> > that should never have been added as an inline SQL command option; it
>> > just crept in, and kept growing.
>>
>> Okay, that being the case: would it make sense to have pg_dump emit DO
>> blocks? I have a feeling this might draw fire, but I don't see any
>> reason why the mechanism would not work to more or less equivalent
>> effect. Certainly making dumps harder to use for those who insist on
>> disabling PL/PGSQL is probably a negative side effect, if one can
>> identify this hypothetical class of person.
>
> Not being able to recover a dump is serious problem for a user.

Even if it only involves enabling PLPGSQL to do the restore? Also take
into consideration that plpgsql is enabled by default. A user would
have to change the template database (which, in general, can cause
restores to fail in at least a few other ways) or drop the procedural
language explicitly to make that mechanism not work with a fresh and
normal-looking createdb.

--
fdr

Daniel Farina wrote:
> On Wed, Nov 24, 2010 at 7:03 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Daniel Farina wrote:
> >> On Wed, Nov 24, 2010 at 4:30 PM, Bruce Momjian <bruce@momjian.us> wrote:
> >> > Robert Haas wrote:
> >> >> With respect to the syntax itself, I have mixed feelings. ?On the one
> >> >> hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS
> >> >> precisely because I believe they handle many common cases that people
> >> >> want in real life without much hullabaloo. ?But, there's clearly some
> >> >> limit to what can reasonably be done this way. ?At some point, what
> >> >> you really want is some kind of meta-language where you can write
> >> >> things like:
> >> >>
> >> >> IF EXISTS TABLE t1 THEN
> >> >> ? ?ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr;
> >> >> END IF;
> >> >
> >> > FYI, I have felt this way for a while. ?IF EXISTS seemed like something
> >> > that should never have been added as an inline SQL command option; it
> >> > just crept in, and kept growing.
> >>
> >> Okay, that being the case: would it make sense to have pg_dump emit DO
> >> blocks? I have a feeling this might draw fire, but I don't see any
> >> reason why the mechanism would not work to more or less equivalent
> >> effect. Certainly making dumps harder to use for those who insist on
> >> disabling PL/PGSQL is probably a negative side effect, if one can
> >> identify this hypothetical class of person.
> >
> > Not being able to recover a dump is serious problem for a user.
> 
> Even if it only involves enabling PLPGSQL to do the restore? Also take
> into consideration that plpgsql is enabled by default. A user would
> have to change the template database (which, in general, can cause
> restores to fail in at least a few other ways) or drop the procedural
> language explicitly to make that mechanism not work with a fresh and
> normal-looking createdb.

What are we adding a pl/pgsql dependency for?  What is the benefit that
will warrant requiring people who disable plpgsql to enable it for
restores?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +

On Wed, Nov 24, 2010 at 7:21 PM, Bruce Momjian <bruce@momjian.us> wrote:
> What are we adding a pl/pgsql dependency for?  What is the benefit that
> will warrant requiring people who disable plpgsql to enable it for
> restores?

There are two use cases I want to cover:

1) It should be possible to restore a dump made with --clean on an
empty database without error, so it can be run in a transaction and
the error code can be usefully monitored.

2) It should be possible a database be dumped and restored by a
non-superuser, again, cleanly, as per 1.

It was easy enough to change all the "DROP ..." statements to "DROP
... IF EXISTS", but the ALTER statements have no equivalent, and thus
the only way for a dump created with --clean to run without error is
to ensure that all table and domain constraints exist prior to
restore.

The obvious mechanisms that have come to mind in this thread are:

* An IF EXISTS variant of ALTER, at least for TABLE and DOMAIN
(although it may be strange to only support it on a couple of types)

* Use of anonymous-DO code blocks (the prototype uses this, and this
depends on plpgsql)

* Bizarre things I can imagine doing that involve creative queries
that, as a side effect, might drop objects that I have not mentioned
because I thought they were too gross to be given serious
consideration. But it might be plpgsql-less, which would be nice.

Note that in the case where one wants to dump/restore as a
non-superuser that one may not be in a position to conveniently do a
(DROP|CREATE) DATABASE statement to work around the problem.

--
fdr

Yes, thanks, those are reasonable goals.

---------------------------------------------------------------------------

Daniel Farina wrote:
> On Wed, Nov 24, 2010 at 7:21 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > What are we adding a pl/pgsql dependency for? ?What is the benefit that
> > will warrant requiring people who disable plpgsql to enable it for
> > restores?
> 
> There are two use cases I want to cover:
> 
> 1) It should be possible to restore a dump made with --clean on an
> empty database without error, so it can be run in a transaction and
> the error code can be usefully monitored.
> 
> 2) It should be possible a database be dumped and restored by a
> non-superuser, again, cleanly, as per 1.
> 
> It was easy enough to change all the "DROP ..." statements to "DROP
> ... IF EXISTS", but the ALTER statements have no equivalent, and thus
> the only way for a dump created with --clean to run without error is
> to ensure that all table and domain constraints exist prior to
> restore.
> 
> The obvious mechanisms that have come to mind in this thread are:
> 
> * An IF EXISTS variant of ALTER, at least for TABLE and DOMAIN
> (although it may be strange to only support it on a couple of types)
> 
> * Use of anonymous-DO code blocks (the prototype uses this, and this
> depends on plpgsql)
> 
> * Bizarre things I can imagine doing that involve creative queries
> that, as a side effect, might drop objects that I have not mentioned
> because I thought they were too gross to be given serious
> consideration. But it might be plpgsql-less, which would be nice.
> 
> Note that in the case where one wants to dump/restore as a
> non-superuser that one may not be in a position to conveniently do a
> (DROP|CREATE) DATABASE statement to work around the problem.
> 
> --
> fdr

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +