From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org, Thom Brown <thom(at)linux(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> |
Subject: | Re: Command Triggers, patch v11 |
Date: | 2012-03-17 22:26:53 |
Message-ID: | 201203172326.53461.andres@anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2012-03-17 22:45:20 | Re: foreign key locks, 2nd attempt |
Previous Message | Tom Lane | 2012-03-17 22:04:30 | Re: Command Triggers, patch v11 |