Re: [PATCH] Return command tag 'REPLACE X' for CREATE OR REPLACE statements.

On Mon, Jan 17, 2011 at 2:25 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Jan 17, 2011 at 9:41 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>>> Maybe instead of the proposed patch, a notice could be added:
>>> NOTICE: existing object was replaced
>
>> Well, that would eliminate the backward-compatibility hazard, pretty
>> much, but it seems noisy.  I already find some of these notices to be
>> unduly informative.
>
> ROTFL ...
>
> There has been some previous banter about reorganizing or reclassifying
> the various NOTICE messages to make them more useful and/or less noisy;
> but I don't think we've ever had a really concrete proposal for better
> behavior.  Maybe it's time to reopen that discussion.
>
> I do agree with Peter's underlying point: it would be pretty
> inconsistent for CREATE OR REPLACE to report this bit of info via
> command tag when CREATE IF NOT EXISTS is reporting an absolutely
> equivalent bit of info via elog(NOTICE).

There's a fine line between serious discussion, humor, and outright
mockery here, and I'm not too sure which one Peter's currently engaged
in. I guess the point here for me is that commands tags for SELECT,
INSERT, UPDATE, and DELETE all return some useful information about
what actually happened - especially, a row count. If it's reasonable
for those commands to return a row count in the command tag, then
there's no reason why utility commands shouldn't also be allowed to
return high-level status information as part of the command tag. On
the flip side we could just rip out command tags altogether and have
psql print out the first two words of the input string.

The asymmetry between DROP-IF-EXISTS and CREATE-IF-NOT-EXISTS and the
proposed CREATE-OR-REPLACE behavior doesn't bother me very much,
because it's already asymmetric: the first two currently report what
happened, and the third one currently doesn't. If you want to propose
to make all of them consistent, how? I don't particularly like the
idea of adding a NOTICE here; we've got too many of those already[1].
Making DIE report that it didn't do anything via a command tag clearly
won't work, because you can say "DROP IF EXISTS foo, bar, baz" and the
answer might not be the same in all three cases, but COR has no such
issue.

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

[1] rhaas=# create table foo (a serial primary key);
NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for
serial column "foo.a"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE

Well, yeah, why did I say primary key if I didn't want a primary key
index to be created, and why did I say serial if I didn't want a
sequence?