From: Andres Freund <andres@anarazel.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Command Triggers, patch v11
Date: Sat, 17 Mar 2012 23:26:53 +0100
User-Agent: KMail/1.13.7 (Linux/3.3.0-rc3-andres-00278-g4903062; KDE/4.6.5;
	x86_64; ; )
Cc: pgsql-hackers@postgresql.org, Thom Brown <thom@linux.com>,
	Dimitri Fontaine <dimitri@2ndquadrant.fr>
References: <m24nufq466.fsf@2ndQuadrant.fr> <9503.1332006327@sss.pgh.pa.us>
	<24814.1332021870@sss.pgh.pa.us>
In-Reply-To: <24814.1332021870@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Message-Id: <201203172326.53461.andres@anarazel.de>

On Saturday, March 17, 2012 11:04:30 PM Tom Lane wrote:
> I've found a couple more issues in the CTAS patch:
> 
> 1. Previous versions delivered a "SELECT n" command tag for either
> spelling of the command:
> 
> regression=# select * into t1 from int8_tbl;
> SELECT 6
> regression=# create table t2 as select * from int8_tbl;
> SELECT 6
> 
> With the patch I get
> 
> regression=# select * into t1 from int8_tbl;
> SELECT 0 0
hm. Stupid me.

> regression=# create table t2 as select * from int8_tbl;
> CREATE TABLE AS
> 
> The first of these is particularly unfortunate since it's outright lying
> as to the number of rows processed.
> I'm not sure what we should do instead.  We have gotten push-back before
> anytime we changed the command tag for an existing command (and in fact
> it seems that we intentionally added the rowcount display in 9.0, which
> means there are people out there who care about that functionality).
> On the other hand, the traditional output for CREATE TABLE AS doesn't
> seem to satisfy the principle of least astonishment.  A third
> consideration is that if we are pushing CREATE TABLE AS as the preferred
> spelling, people will probably complain if it omits functionality that
> SELECT INTO provides; so I'm not sure that "SELECT n" in one case and
> "CREATE TABLE AS" in the other would be a good idea either.  Any
> opinions what to do here?
I would prefer both returning CREATE TABLE AS since thats what actually 
happens. We already document that SELECT INTO is kinda deprecated: "The 
PostgreSQL usage of SELECT INTO to represent table creation is historical. It 
is best to use CREATE TABLE AS for this purpose in new code."
I have seen code that uses the command code for selecting the, app level, 
logging. Its kinda hard to do that if a CREATE TABLE AS/SELECT INTO returns 
SELECT.

Does CTAS ommit any functionality currently? I don't see any reason not to 
support stuff there thats supported in SELECT INTO.

> 2. Historically, CREATE RULE has allowed a rule action to be SELECT INTO
> (though not CREATE TABLE AS).  Currently the patch is throwing an error
> for that.  This seems like something that might not be worth fixing,
> though.  It's fairly hard to conceive of a use-case for such a rule,
> since it would work only once before starting to throw "table already
> exists" errors.  How much do we care about preserving backward
> compatibility here?
I vote for not supporting that anymore. That being possible looks more like an 
implementation detail to me.


Thanks for looking at this!

Andres