Thread: [GENERAL] DROP [TEMP] TABLE syntax, as reason why not?

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I'm wondering if there is anything technical preventing someone from making:

> DROP TEMP TABLE tablename;

There is no great need for that because you can get the semantics you're
asking for with "DROP TABLE pg_temp.tablename".

            regards, tom lane

2017-08-24 3:08 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> I'm wondering if there is anything technical preventing someone from making:
>
>> DROP TEMP TABLE tablename;
>
> There is no great need for that because you can get the semantics you're
> asking for with "DROP TABLE pg_temp.tablename".
>
>                         regards, tom lane

This sounds like another syntax inconsistency/asymmetry.

ALTER TABLE pg_temp.tablename ... is OK.
ALTER TEMP TABLE tablename ... is NOT OK.

CREATE TEMP TABLE tablename ... is OK.
CREATE TABLE pg_temp.tablename ... is OK.

DROP TABLE pg_temp.tablename ... is OK.
DROP TEMP TABLE tablename ... is NOT OK.

Unless the standard explicitly forbids it, why not supporting both
syntaxes in all commands using the TABLE predicate?
Those are semantically equivalent. Aren't they?

--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

2017-08-24 11:04 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:
>
>
> 2017-08-24 9:11 GMT+02:00 Vincenzo Romano <vincenzo.romano@notorand.it>:
>>
>> 2017-08-24 3:08 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
>> > "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> >> I'm wondering if there is anything technical preventing someone from
>> >> making:
>> >
>> >> DROP TEMP TABLE tablename;
>> >
>> > There is no great need for that because you can get the semantics you're
>> > asking for with "DROP TABLE pg_temp.tablename".
>> >
>> >                         regards, tom lane
>>
>> This sounds like another syntax inconsistency/asymmetry.
>>
>> ALTER TABLE pg_temp.tablename ... is OK.
>> ALTER TEMP TABLE tablename ... is NOT OK.
>>
>> CREATE TEMP TABLE tablename ... is OK.
>> CREATE TABLE pg_temp.tablename ... is OK.
>>
>> DROP TABLE pg_temp.tablename ... is OK.
>> DROP TEMP TABLE tablename ... is NOT OK.
>>
>> Unless the standard explicitly forbids it, why not supporting both
>> syntaxes in all commands using the TABLE predicate?
>> Those are semantically equivalent. Aren't they?
>
>
> It can be issue when somebody will do port from PostgreSQL to any other
> databases.

Postgres is already creating issues to people porting DBs away from it
as it sports a number of extensions.
So this does not sounds like a good argument.

> There should be stronger reason for introduction possible NON
> ANSI SQL feature than syntactic sugar.

Once you accept that Postgres is already extending the standard, I
would focus on syntax consistency and symmetry as a yet-another-extra
value from Postgres.

Moreover, "DROP TEMP TABLE..." would make it clear and explicit that
the table is temporary.
And it would thus "protect the programmer from typos and errors"
(intentional tongue-in-cheek).

2017-08-24 12:06 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:
>
>
> 2017-08-24 11:46 GMT+02:00 Vincenzo Romano <vincenzo.romano@notorand.it>:
>>
>> 2017-08-24 11:04 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:
>> >
>> >
>> > 2017-08-24 9:11 GMT+02:00 Vincenzo Romano <vincenzo.romano@notorand.it>:
>> >>
>> >> 2017-08-24 3:08 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
>> >> > "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> >> >> I'm wondering if there is anything technical preventing someone from
>> >> >> making:
>> >> >
>> >> >> DROP TEMP TABLE tablename;
>> >> >
>> >> > There is no great need for that because you can get the semantics
>> >> > you're
>> >> > asking for with "DROP TABLE pg_temp.tablename".
>> >> >
>> >> >                         regards, tom lane
>> >>
>> >> This sounds like another syntax inconsistency/asymmetry.
>> >>
>> >> ALTER TABLE pg_temp.tablename ... is OK.
>> >> ALTER TEMP TABLE tablename ... is NOT OK.
>> >>
>> >> CREATE TEMP TABLE tablename ... is OK.
>> >> CREATE TABLE pg_temp.tablename ... is OK.
>> >>
>> >> DROP TABLE pg_temp.tablename ... is OK.
>> >> DROP TEMP TABLE tablename ... is NOT OK.
>> >>
>> >> Unless the standard explicitly forbids it, why not supporting both
>> >> syntaxes in all commands using the TABLE predicate?
>> >> Those are semantically equivalent. Aren't they?
>> >
>> >
>> > It can be issue when somebody will do port from PostgreSQL to any other
>> > databases.
>>
>> Postgres is already creating issues to people porting DBs away from it
>> as it sports a number of extensions.
>> So this does not sounds like a good argument.
>
>
> I am thinking, and it is my opinion, nothing more, so Postgres should to
> introduce proprietary syntax only when there are not any possibility and
> there are not some similar in ANSI or Oracle, DB2 or MSSQL.

Mine are also opinions.

>> > There should be stronger reason for introduction possible NON
>> > ANSI SQL feature than syntactic sugar.
>> Once you accept that Postgres is already extending the standard, I
>> would focus on syntax consistency and symmetry as a yet-another-extra
>> value from Postgres.
>>
>> Moreover, "DROP TEMP TABLE..." would make it clear and explicit that
>> the table is temporary.
>> And it would thus "protect the programmer from typos and errors"
>> (intentional tongue-in-cheek).
>
>
> If we disable DROP TABLE on temporary tables, then your arguments are 100%
> valid, but if we don't do it, then DROP TEMP TABLE is just syntactic sugar

Isn't a CHOICE for better syntax enough?
Aren't symmetry and consistency valuable arguments?
Syntactic sugar is not evil on its own.
It can help people writing code that can be better understood.
And it would still be up to the programmer to choose.

--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

On Thu, Aug 24, 2017 at 11:46 AM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:
> Once you accept that Postgres is already extending the standard, I
> would focus on syntax consistency and symmetry as a yet-another-extra
> value from Postgres.
>
> Moreover, "DROP TEMP TABLE..." would make it clear and explicit that
> the table is temporary.
> And it would thus "protect the programmer from typos and errors"
> (intentional tongue-in-cheek).


Given drop temp table x is just syntactic sugar to drop table
PG_TEMP.x I think the (slight) increase on the bug-surface is enough
to avoid it, as the pg_temp. makes it equally clear and explicit you
are dropping a temporary table.

And if the programmer forgets the pg_temp. it can equally forget the TEMP.

Francisco Olarte.

On Thu, Aug 24, 2017 at 12:48 PM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:

> Isn't a CHOICE for better syntax enough?
> Aren't symmetry and consistency valuable arguments?
> Syntactic sugar is not evil on its own.
> It can help people writing code that can be better understood.

Valid arguments, but those extensions are NOT free to develop, test
and maintain. And every syntax extensions, specially one like this,
introduces the possibility of collisions with future standards ( de
facto or de iure, although Pg already deviates from ansi on the temp
stuff ).

Francisco Olarte.

Francisco Olarte <folarte@peoplecall.com> writes:
> On Thu, Aug 24, 2017 at 12:48 PM, Vincenzo Romano
> <vincenzo.romano@notorand.it> wrote:
>> Isn't a CHOICE for better syntax enough?
>> Aren't symmetry and consistency valuable arguments?
>> Syntactic sugar is not evil on its own.
>> It can help people writing code that can be better understood.

> Valid arguments, but those extensions are NOT free to develop, test
> and maintain.

I think the more important point is the one already alluded to upthread:
it doesn't make any sense to invent DROP TEMP TABLE unless we're willing
to follow through on all the logical consequences of treating temp tables
as a distinct kind of object, the way that e.g. views and materialized
views are considered distinct.  So we'd need ALTER TEMP TABLE,
TRUNCATE TEMP TABLE, yadda yadda, and we'd need to forbid applying the
wrong type of command to regular vs. temp tables.  The first of those
makes this a considerably less trivial patch, and the second one would
be unlikely to get accepted because of the compatibility break for
existing applications.  But if you don't do the second one, then what
you've mostly accomplished is to introduce more irregularity rather
than remove some.

Another issue is that regular and temp tables aren't very different
kinds of objects, the way that views and matviews are.  I think it's
really a rather unfortunate choice on the part of the SQL committee to
have denoted temp tables by putting the modifier word right there, rather
than attaching it as an option somewhere later in the command.  We should
not let that syntax accident drive what we consider reasonable semantics
to be.

(Also, once you've done all that, do you want to also do it for UNLOGGED
tables?)

            regards, tom lane