Thread: SELECT INTO deprecation

While reading about deprecating and removing various things in other 
threads, I was wondering about how deprecated SELECT INTO is.  There are 
various source code comments about this, but the SELECT INTO reference 
page only contains soft language like "recommended".  I'm proposing the 
attached patch to stick a more explicit deprecation notice right at the top.

I also found some gratuitous uses of SELECT INTO in various tests and 
documentation (not ecpg or plpgsql of course).  Here is a patch to 
adjust those to CREATE TABLE AS.

I don't have a specific plan for removing top-level SELECT INTO 
altogether, but there is a nontrivial amount of code for handling it, so 
there would be some gain if it could be removed eventually.

> On 2 Dec 2020, at 12:54, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:

> While reading about deprecating and removing various things in other threads, I was wondering about how deprecated
SELECTINTO is.  There are various source code comments about this, but the SELECT INTO reference page only contains
softlanguage like "recommended".  I'm proposing the attached patch to stick a more explicit deprecation notice right at
thetop. 

+   This command is deprecated.  Use <link
Should this get similar strong wording to other deprecated things where we add
"..and may/will eventually be removed"?

> I also found some gratuitous uses of SELECT INTO in various tests and documentation (not ecpg or plpgsql of course).
Hereis a patch to adjust those to CREATE TABLE AS. 

I didn't scan for others, but the ones included in the 0001 patch all looks
fine and IMO improves readability.

cheers ./daniel

Greetings,

* Peter Eisentraut (peter.eisentraut@enterprisedb.com) wrote:
> While reading about deprecating and removing various things in other
> threads, I was wondering about how deprecated SELECT INTO is.  There are
> various source code comments about this, but the SELECT INTO reference page
> only contains soft language like "recommended".  I'm proposing the attached
> patch to stick a more explicit deprecation notice right at the top.

I don't see much value in this.  Users already have 5 years to adapt
their code to new major versions of PG and that strikes me as plenty
enough time and is why we support multiple major versions of PG for so
long.  Users who keep pace and update for each major version aren't
likely to have issue making this change since they're already used to
regularly updating their code for new major versions, while others are
going to complain no matter when we remove it and will ignore any
deprecation notices we put out there, so there isn't much point in them.

> I also found some gratuitous uses of SELECT INTO in various tests and
> documentation (not ecpg or plpgsql of course).  Here is a patch to adjust
> those to CREATE TABLE AS.

If we aren't actually removing SELECT INTO then I don't know that it
makes sense to just stop testing it.

> I don't have a specific plan for removing top-level SELECT INTO altogether,
> but there is a nontrivial amount of code for handling it, so there would be
> some gain if it could be removed eventually.

We should either remove it, or remove the comments that it's deprecated,
not try to make it more deprecated or try to somehow increase the
recommendation to not use it.

I'd recommend we remove it and make note that it's been removed in v14
in the back branches at the same time, which will give those who
actually pay attention and care that much more time before v14 comes out
to update their code (not that I'm all that worried, as they'll also see
it in the beta release notes too).

Thanks,

Stephen

On Wed, Dec 02, 2020 at 12:58:36PM -0500, Stephen Frost wrote:
> Greetings,
> 
> * Peter Eisentraut (peter.eisentraut@enterprisedb.com) wrote:
> > While reading about deprecating and removing various things in
> > other threads, I was wondering about how deprecated SELECT INTO
> > is.  There are various source code comments about this, but the
> > SELECT INTO reference page only contains soft language like
> > "recommended".  I'm proposing the attached patch to stick a more
> > explicit deprecation notice right at the top.
> 
> I don't see much value in this.  Users already have 5 years to adapt
> their code to new major versions of PG and that strikes me as plenty
> enough time and is why we support multiple major versions of PG for
> so long.  Users who keep pace and update for each major version
> aren't likely to have issue making this change since they're already
> used to regularly updating their code for new major versions, while
> others are going to complain no matter when we remove it and will
> ignore any deprecation notices we put out there, so there isn't much
> point in them.

+1 for removing it entirely and including this prominently in the
release notes.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Stephen Frost <sfrost@snowman.net> writes:
> * Peter Eisentraut (peter.eisentraut@enterprisedb.com) wrote:
>> While reading about deprecating and removing various things in other
>> threads, I was wondering about how deprecated SELECT INTO is.  There are
>> various source code comments about this, but the SELECT INTO reference page
>> only contains soft language like "recommended".  I'm proposing the attached
>> patch to stick a more explicit deprecation notice right at the top.

> I don't see much value in this.

Yeah, if we want to kill it let's just do so.  The negative language in
the reference page has been there since (at least) 7.1, so people can
hardly say they didn't see it coming.

            regards, tom lane

On Wed, Dec 02, 2020 at 03:35:46PM -0500, Tom Lane wrote:
> Yeah, if we want to kill it let's just do so.  The negative language in
> the reference page has been there since (at least) 7.1, so people can
> hardly say they didn't see it coming.

+1.  I got to wonder about the impact when migrating applications
though.  SELECT INTO has a different meaning in Oracle, but SQL server
creates a new table like Postgres.
--
Michael

Stephen Frost schrieb am 02.12.2020 um 18:58:
> We should either remove it, or remove the comments that it's deprecated,
> not try to make it more deprecated or try to somehow increase the
> recommendation to not use it.

(I am writing from a "user only" perspective, not a developer)

I don't see any warning about the syntax being "deprecated" in the current manual.

There is only a note that says that CTAS is "recommended" instead of SELET INTO,
but for me that's something entirely different than "deprecating" it.

I personally have nothing against removing it, but I still see it used
a lot in questions on various online forums, and I would think that
a lot of people would be very unpleasantly surprised if a feature
gets removed without any warning (the current "recommendation" does not
constitute a deprecation or even removal warning for most people I guess)

I would vote for a clear deprecation message as suggested by Peter, but I would
add "and will be removed in a future version" to it.

Not sure if maybe even back-patching that warning would make sense as well, so
that also users of older versions get to see that warning.

Then target 15 or 16 as the release for removal, but not 14

Thomas

On 2020-12-02 18:58, Stephen Frost wrote:
>> I also found some gratuitous uses of SELECT INTO in various tests and
>> documentation (not ecpg or plpgsql of course).  Here is a patch to adjust
>> those to CREATE TABLE AS.
> If we aren't actually removing SELECT INTO then I don't know that it
> makes sense to just stop testing it.

The point here was, there is still code that actually tests SELECT INTO 
specifically.  But unrelated test code that just wants to set up a quick 
table with some rows in it ought to use the preferred syntax for doing so.

On 2020-12-03 00:54, Michael Paquier wrote:
> I got to wonder about the impact when migrating applications
> though.  SELECT INTO has a different meaning in Oracle, but SQL server
> creates a new table like Postgres.

Interesting.  This appears to be the case.  SQL Server uses SELECT INTO 
to create a table, and does not appear to have CREATE TABLE AS.

So maybe we should keep it, but adjust the documentation to point out 
this use case.

[some snarky comment about AWS Babelfish here ... ;-) ]

Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> Interesting.  This appears to be the case.  SQL Server uses SELECT INTO 
> to create a table, and does not appear to have CREATE TABLE AS.
> So maybe we should keep it, but adjust the documentation to point out 
> this use case.

That argument makes sense, but only if our version is a drop-in
replacement for SQL Server's version: if people have to adjust their
commands anyway in corner cases, we're not doing them any big favor.
So: are the syntax and semantics really a match?  Do we have feature
parity?

As I recall, a whole lot of the pain we have with INTO has to do
with the semantics we've chosen for INTO in a set-operation nest.
We think you can write something like

   SELECT ... INTO foo FROM ... UNION SELECT ... FROM ...

but we insist on the INTO being in the first component SELECT.
I'd like to know exactly how much of that messiness is shared
by SQL Server.

(FWIW, I think the fact that SELECT INTO means something entirely
different in plpgsql is a good reason for killing off one version
or the other.  As things stand, it's mighty confusing.)

            regards, tom lane

On 2020-12-03 16:34, Tom Lane wrote:
> As I recall, a whole lot of the pain we have with INTO has to do
> with the semantics we've chosen for INTO in a set-operation nest.
> We think you can write something like
> 
>     SELECT ... INTO foo FROM ... UNION SELECT ... FROM ...
> 
> but we insist on the INTO being in the first component SELECT.
> I'd like to know exactly how much of that messiness is shared
> by SQL Server.

On sqlfiddle.com, this works:

select a into t3 from t1 union select a from t2;

but this gets an error:

select a from t1 union select a into t4 from t2;

SELECT INTO must be the first query in a statement containing a UNION, 
INTERSECT or EXCEPT operator.

On 2020-12-03 20:26, Peter Eisentraut wrote:
> On 2020-12-03 16:34, Tom Lane wrote:
>> As I recall, a whole lot of the pain we have with INTO has to do
>> with the semantics we've chosen for INTO in a set-operation nest.
>> We think you can write something like
>>
>>      SELECT ... INTO foo FROM ... UNION SELECT ... FROM ...
>>
>> but we insist on the INTO being in the first component SELECT.
>> I'd like to know exactly how much of that messiness is shared
>> by SQL Server.
> 
> On sqlfiddle.com, this works:
> 
> select a into t3 from t1 union select a from t2;
> 
> but this gets an error:
> 
> select a from t1 union select a into t4 from t2;
> 
> SELECT INTO must be the first query in a statement containing a UNION,
> INTERSECT or EXCEPT operator.

So, with that in mind, here is an alternative proposal that points out 
that SELECT INTO has some use for compatibility.

On Wed, Dec  9, 2020 at 09:48:54PM +0100, Peter Eisentraut wrote:
> On 2020-12-03 20:26, Peter Eisentraut wrote:
> > On 2020-12-03 16:34, Tom Lane wrote:
> > > As I recall, a whole lot of the pain we have with INTO has to do
> > > with the semantics we've chosen for INTO in a set-operation nest.
> > > We think you can write something like
> > > 
> > >      SELECT ... INTO foo FROM ... UNION SELECT ... FROM ...
> > > 
> > > but we insist on the INTO being in the first component SELECT.
> > > I'd like to know exactly how much of that messiness is shared
> > > by SQL Server.
> > 
> > On sqlfiddle.com, this works:
> > 
> > select a into t3 from t1 union select a from t2;
> > 
> > but this gets an error:
> > 
> > select a from t1 union select a into t4 from t2;
> > 
> > SELECT INTO must be the first query in a statement containing a UNION,
> > INTERSECT or EXCEPT operator.
> 
> So, with that in mind, here is an alternative proposal that points out that
> SELECT INTO has some use for compatibility.

Do we really want to carry around confusing syntax for compatibility?  I
doubt we would ever add INTO now, even for compatibility.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

On 2020-12-15 23:13, Bruce Momjian wrote:
> Do we really want to carry around confusing syntax for compatibility?  I
> doubt we would ever add INTO now, even for compatibility.

Right, we would very likely not add it now.  But it doesn't seem to 
cause a lot of ongoing maintenance burden, so if there is a use case, 
it's not unreasonable to keep it around.  I have no other motive here, I 
was just curious.

On Wed, Dec 16, 2020 at 06:07:08PM +0100, Peter Eisentraut wrote:
> Right, we would very likely not add it now.  But it doesn't seem to cause a
> lot of ongoing maintenance burden, so if there is a use case, it's not
> unreasonable to keep it around.  I have no other motive here, I was just
> curious.

From the point of view of the code, this would simplify the grammar
rules of SELECT, which is a good thing.  And this would also simplify
some code in the rewriter where we create some CreateTableAsStmt
on-the-fly where an IntoClause is specified, but my take is that this
is not really a burden when it comes to long-term maintenance.  And
the git history tells, at least it seems to me so, that this is not
manipulated much.
--
Michael

On 2020-12-17 02:30, Michael Paquier wrote:
> On Wed, Dec 16, 2020 at 06:07:08PM +0100, Peter Eisentraut wrote:
>> Right, we would very likely not add it now.  But it doesn't seem to cause a
>> lot of ongoing maintenance burden, so if there is a use case, it's not
>> unreasonable to keep it around.  I have no other motive here, I was just
>> curious.
> 
>  From the point of view of the code, this would simplify the grammar
> rules of SELECT, which is a good thing.  And this would also simplify
> some code in the rewriter where we create some CreateTableAsStmt
> on-the-fly where an IntoClause is specified, but my take is that this
> is not really a burden when it comes to long-term maintenance.  And
> the git history tells, at least it seems to me so, that this is not
> manipulated much.

I have committed my small documentation patch that points out 
compatibility with other implementations.

On 12/15/20 5:13 PM, Bruce Momjian wrote:
> On Wed, Dec  9, 2020 at 09:48:54PM +0100, Peter Eisentraut wrote:
>> On 2020-12-03 20:26, Peter Eisentraut wrote:
>> > On 2020-12-03 16:34, Tom Lane wrote:
>> > > As I recall, a whole lot of the pain we have with INTO has to do
>> > > with the semantics we've chosen for INTO in a set-operation nest.
>> > > We think you can write something like
>> > > 
>> > >      SELECT ... INTO foo FROM ... UNION SELECT ... FROM ...
>> > > 
>> > > but we insist on the INTO being in the first component SELECT.
>> > > I'd like to know exactly how much of that messiness is shared
>> > > by SQL Server.
>> > 
>> > On sqlfiddle.com, this works:
>> > 
>> > select a into t3 from t1 union select a from t2;
>> > 
>> > but this gets an error:
>> > 
>> > select a from t1 union select a into t4 from t2;
>> > 
>> > SELECT INTO must be the first query in a statement containing a UNION,
>> > INTERSECT or EXCEPT operator.
>> 
>> So, with that in mind, here is an alternative proposal that points out that
>> SELECT INTO has some use for compatibility.
> 
> Do we really want to carry around confusing syntax for compatibility?  I
> doubt we would ever add INTO now, even for compatibility.
> 

If memory serves the INTO syntax is a result from the first incarnation 
of PL/pgSQL being based on the Oracle PL/SQL syntax. I think it has been 
there from the very beginning, which makes it likely that by now a lot 
of migrants are using it in rather old code.

I don't think it should be our business to throw wrenches into their 
existing applications.


Regards, Jan

-- 
Jan Wieck
Principle Database Engineer
Amazon Web Services