From: Andres Freund <andres@anarazel.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org, Thom Brown <thom@linux.com>
Cc: Dimitri Fontaine <dimitri@2ndquadrant.fr>
Subject: Re: Command Triggers, patch v11
Date: Sat, 17 Mar 2012 23:26:53 +0100
Message-ID: <201203172326.53461.andres@anarazel.de> (raw)
In-Reply-To: <24814.1332021870@sss.pgh.pa.us>
References: <m24nufq466.fsf@2ndQuadrant.fr>
<9503.1332006327@sss.pgh.pa.us>
<24814.1332021870@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andres@anarazel.de, tgl@sss.pgh.pa.us, thom@linux.com, dimitri@2ndquadrant.fr
Subject: Re: Command Triggers, patch v11
In-Reply-To: <201203172326.53461.andres@anarazel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox