Re: Command Triggers, patch v11

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

In response to

Responses

Browse pgsql-hackers by date

  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