Truncate if exists

Hi,

With the help of Cédric, here's a patch changing the TRUNCATE TABLE
command, adding the IF EXISTS option to allow the presence in the list
of tables of a missing or invisible table.

This meets the needs of scripts that should be run in different stages,
and do not always have the same visibility on the tables, as well as
DROP TABLE. Rather than rollback the entire TRUNCATE or transaction, we
prefer to ignore the absence of the table.

It is a small patch which changes very little code, but that could be
quite useful.

Regards,

--
Sébastien Lardière
PostgreSQL DBA Team Manager
Hi-Media

On 9 October 2012 09:33, Sébastien Lardière <slardiere(at)hi-media(dot)com> wrote:

> With the help of Cédric, here's a patch changing the TRUNCATE TABLE
> command, adding the IF EXISTS option to allow the presence in the list
> of tables of a missing or invisible table.
>
> This meets the needs of scripts that should be run in different stages,
> and do not always have the same visibility on the tables, as well as
> DROP TABLE. Rather than rollback the entire TRUNCATE or transaction, we
> prefer to ignore the absence of the table.
>
> It is a small patch which changes very little code, but that could be
> quite useful.

Agreed.

Patch looks fine, but please observe the coding standards wrt nested brackets.

Will apply in 48 hours barring objections.

Anyone want to check for any other missing IF EXISTS capability in other DDL?

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 10/09/2012 11:09 AM, Simon Riggs wrote:
> On 9 October 2012 09:33, Sébastien Lardière <slardiere(at)hi-media(dot)com> wrote:
>
>> With the help of Cédric, here's a patch changing the TRUNCATE TABLE
>> command, adding the IF EXISTS option to allow the presence in the list
>> of tables of a missing or invisible table.
>>
>> This meets the needs of scripts that should be run in different stages,
>> and do not always have the same visibility on the tables, as well as
>> DROP TABLE. Rather than rollback the entire TRUNCATE or transaction, we
>> prefer to ignore the absence of the table.
>>
>> It is a small patch which changes very little code, but that could be
>> quite useful.
> Agreed.
>
> Patch looks fine, but please observe the coding standards wrt nested brackets.
>
> Will apply in 48 hours barring objections.
>
> Anyone want to check for any other missing IF EXISTS capability in other DDL?
>

Indeed, brackets was not correct, it's better now (I think), and correct
some comments.

Thanks,

--
Sébastien Lardière
PostgreSQL DBA Team Manager
Hi-Media

On Tue, Oct 9, 2012 at 11:09 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> Anyone want to check for any other missing IF EXISTS capability in other
> DDL?
>

Yes, DEALLOCATE.

On Tue, Oct 9, 2012 at 11:51 AM, Vik Reykja <vikreykja(at)gmail(dot)com> wrote:

> On Tue, Oct 9, 2012 at 11:09 AM, Simon Riggs <simon(at)2ndquadrant(dot)com>wrote:
>
>> Anyone want to check for any other missing IF EXISTS capability in other
>> DDL?
>>
>
> Yes, DEALLOCATE.
>

Patch attached.

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On 9 October 2012 09:33, Sbastien Lardire <slardiere(at)hi-media(dot)com> wrote:
>> With the help of Cdric, here's a patch changing the TRUNCATE TABLE
>> command, adding the IF EXISTS option to allow the presence in the list
>> of tables of a missing or invisible table.

> Will apply in 48 hours barring objections.

I object: this doesn't deserve to be fast-tracked like that with no
thought about whether the semantics are actually useful or sensible.

For starters, the use-case hasn't been explained to my satisfaction.
In what situation is it actually helpful to TRUNCATE a table that's
not there yet? Aren't you going to have to do a CREATE IF NOT EXISTS
to keep from failing later in the script? If so, why not just do that
first?

Second, to my mind the point of a multi-table TRUNCATE is to ensure that
all the referenced tables get reset to empty *together*. With something
like this, you'd have no such guarantee. Consider a timeline like this:

Session 1 Session 2

TRUNCATE IF EXISTS a, b, c;
... finds c doesn't exist ...
... working on a and b ...
CREATE TABLE c ( ... );
INSERT INTO c ...;
... commits ...

Now we have a, b, and c, but c isn't empty, violating the expectations
of session 1. So even if there's a use-case for IF EXISTS on a single
table, I think it's very very dubious to allow it in multi-table
commands.

regards, tom lane

=?ISO-8859-1?Q?S=E9bastien_Lardi=E8re?= <slardiere(at)hi-media(dot)com> writes:
> Indeed, brackets was not correct, it's better now (I think), and correct
> some comments.

Still wrong ... at the very least you missed copyfuncs/equalfuncs.
In general, when adding a field to a struct, it's good practice to
grep for all uses of that struct.

regards, tom lane

> For starters, the use-case hasn't been explained to my satisfaction.
> In what situation is it actually helpful to TRUNCATE a table that's
> not there yet? Aren't you going to have to do a CREATE IF NOT EXISTS
> to keep from failing later in the script? If so, why not just do that
> first?

There is a use case for the truncate 'mutliple' tables, maybe less clear for a single table.
Sébastien will speak here I suppose.

> Second, to my mind the point of a multi-table TRUNCATE is to ensure that
> all the referenced tables get reset to empty *together*. With something
> like this, you'd have no such guarantee. Consider a timeline like this:
>
> Session 1 Session 2
>
> TRUNCATE IF EXISTS a, b, c;
> ... finds c doesn't exist ...
> ... working on a and b ...
> CREATE TABLE c ( ... );
> INSERT INTO c ...;
> ... commits ...
>
> Now we have a, b, and c, but c isn't empty, violating the expectations
> of session 1. So even if there's a use-case for IF EXISTS on a single
> table, I think it's very very dubious to allow it in multi-table
> commands.

well, in such case you probably don't want to use IF EXISTS.

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

On 10/09/2012 04:06 PM, Tom Lane wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On 9 October 2012 09:33, Sébastien Lardière <slardiere(at)hi-media(dot)com> wrote:
>>> With the help of Cédric, here's a patch changing the TRUNCATE TABLE
>>> command, adding the IF EXISTS option to allow the presence in the list
>>> of tables of a missing or invisible table.
>> Will apply in 48 hours barring objections.
> I object: this doesn't deserve to be fast-tracked like that with no
> thought about whether the semantics are actually useful or sensible.
>
> For starters, the use-case hasn't been explained to my satisfaction.
> In what situation is it actually helpful to TRUNCATE a table that's
> not there yet? Aren't you going to have to do a CREATE IF NOT EXISTS
> to keep from failing later in the script? If so, why not just do that
> first?

it could be useful to not rollback transactions :

- if a table is not yet or no more visible, because of search_path
modification
- if a table was dropped, for any reason

> Second, to my mind the point of a multi-table TRUNCATE is to ensure that
> all the referenced tables get reset to empty *together*. With something
> like this, you'd have no such guarantee. Consider a timeline like this:
>
> Session 1 Session 2
>
> TRUNCATE IF EXISTS a, b, c;
> ... finds c doesn't exist ...
> ... working on a and b ...
> CREATE TABLE c ( ... );
> INSERT INTO c ...;
> ... commits ...
>
> Now we have a, b, and c, but c isn't empty, violating the expectations
> of session 1. So even if there's a use-case for IF EXISTS on a single
> table, I think it's very very dubious to allow it in multi-table
> commands.

Well, I have to say that if I'm the guy who create the table c, I don't
want to see the table empty after my insert, don't you think ?

I understand your point about the multi-table TRUNCATE, but my point is
to commit transaction, whatever the visibility or presence of a given
table.
In a perfect world, we could review all our processes, and change them
to guarantee commit, then we don't need IF EXISTS ; But i'm not in this
case, and maybe some others neither, are you ?

--
Sébastien Lardière
PostgreSQL DBA Team Manager
Hi-Media

On Tue, Oct 9, 2012 at 12:28 PM, Sébastien Lardière
<slardiere(at)hi-media(dot)com> wrote:
>> For starters, the use-case hasn't been explained to my satisfaction.
>> In what situation is it actually helpful to TRUNCATE a table that's
>> not there yet? Aren't you going to have to do a CREATE IF NOT EXISTS
>> to keep from failing later in the script? If so, why not just do that
>> first?
>
> it could be useful to not rollback transactions :
>
> - if a table is not yet or no more visible, because of search_path
> modification

I don't think I understand the case you are describing here.

> - if a table was dropped, for any reason

But in this case surely you could use DROP IF EXISTS.

I've been a big proponent of adding "IF EXISTS" support to CREATE
TABLE and ALTER TABLE but I'm having a hard time getting excited about
this one. I can't imagine that many people would use it, and those
who do can implement it in about 10 lines of PL/pgsql. The existence
of DO blocks and the fact that PL/pgsql is now installed by default
have made it much more convenient to solve these kinds of problems
using those tools rather than needing dedicated syntax. That does not
mean that the most frequently used cases shouldn't have dedicated
syntax anyway, for convenience, but I'm doubtful that this falls into
that category.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 9 October 2012 15:06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On 9 October 2012 09:33, Sébastien Lardière <slardiere(at)hi-media(dot)com> wrote:
>>> With the help of Cédric, here's a patch changing the TRUNCATE TABLE
>>> command, adding the IF EXISTS option to allow the presence in the list
>>> of tables of a missing or invisible table.
>
>> Will apply in 48 hours barring objections.
>
> I object: this doesn't deserve to be fast-tracked like that with no
> thought about whether the semantics are actually useful or sensible.

I wasn't fast tracking it, just looking to apply small uncontentious
patches quickly.

Your objection is enough to stall until next commitfest for further discussion.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Robert,

> I've been a big proponent of adding "IF EXISTS" support to CREATE
> TABLE and ALTER TABLE but I'm having a hard time getting excited about
> this one. I can't imagine that many people would use it, and those
> who do can implement it in about 10 lines of PL/pgsql. The existence
> of DO blocks and the fact that PL/pgsql is now installed by default
> have made it much more convenient to solve these kinds of problems
> using those tools rather than needing dedicated syntax. That does not
> mean that the most frequently used cases shouldn't have dedicated
> syntax anyway, for convenience, but I'm doubtful that this falls into
> that category.

On the other hand, it's useful to consistently have "IF EXISTS" syntax
for the majority of utility commands. It's confusing to users that they
can do "DROP TABLE IF EXISTS" but not "TRUNCATE IF EXISTS", even if the
latter is less useful than the former. So that's one reason to support
this.

The second is for making deployment scripts idempotent. For example,
say you have script A which creates table "josh", and script B which
needs table "josh" to be empty, if present. Since the two scripts are
tied to different database features, and you don't know which one will
be deployed first, it's useful to have TRUNCATE IF EXISTS. Yes, you can
solve that problem with DO, but why make users go to the extra effort?

Is it *as* useful as other IF EXISTS? No. Is it replaceable with a DO
$$ statement? Yes. Is that a reason to block a fairly trivial patch
which makes things 0.1% easier for users? No.

Not if the patch itself is broken, that's another story.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 10/9/12 5:09 AM, Simon Riggs wrote:
> Anyone want to check for any other missing IF EXISTS capability in other DDL?

TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is
stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next?

On 10/10/12 09:35, Peter Eisentraut wrote:
> On 10/9/12 5:09 AM, Simon Riggs wrote:
>> Anyone want to check for any other missing IF EXISTS capability in other DDL?
> TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is
> stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next?
>
>
>
INSERT IF NOT EXISTS ?

On 9 October 2012 21:35, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On 10/9/12 5:09 AM, Simon Riggs wrote:
>> Anyone want to check for any other missing IF EXISTS capability in other DDL?
>
> TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is
> stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next?

I'm not involved in the planning or justification for this patch, and
have no opinion.

I discussed applying it because it was an uncontentious patch. It
clearly is not....

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Tue, Oct 9, 2012 at 2:04 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 9 October 2012 21:35, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> On 10/9/12 5:09 AM, Simon Riggs wrote:
>>> Anyone want to check for any other missing IF EXISTS capability in other DDL?
>>
>> TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is
>> stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next?
>
> I'm not involved in the planning or justification for this patch, and
> have no opinion.
>
> I discussed applying it because it was an uncontentious patch. It
> clearly is not....

I also read Simon's approach as not a push for inclusion, but
defaulting to commit for smaller patches that basically look
mechanically legitimate with no objections to streamline
communication. Since pgsql-hackers has a good record objecting to
patches that require objection in a timely manner, I think that's
reasonable. The cost of revert would not be that high, either.

Clearly those conditions were not met, but I don't think it's
justified to jump on Simon for this approach on a patch like this.

--
fdr

On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> The second is for making deployment scripts idempotent. For example,
> say you have script A which creates table "josh", and script B which
> needs table "josh" to be empty, if present. Since the two scripts are
> tied to different database features, and you don't know which one will
> be deployed first, it's useful to have TRUNCATE IF EXISTS. Yes, you can
> solve that problem with DO, but why make users go to the extra effort?

Hmm. That's an interesting point. I think we're currently in
somewhat of a limbo zone about where we ought to have IF EXISTS and IF
NOT EXISTS options, and where we should not. Really, I'd like to
figure out what policy we want to have, and then go make everything
work that way. I don't exactly know what the policy should be, but if
we don't have one then we're going to have to argue about every patch
individually, which is already getting to be more than tedious. At
the one extreme, you have Tom, who probably would not have added any
of these given his druthers; at the other extreme, there are probably
some people who would say we ought to have this for every command in
the book, right down to INSERT IF EXISTS (and, hey, why not INSERT OR
CREATE for good measure?). I'm not sure what the right thing to do
is... but we should probably come up with some consensus position we
can all live with, and then go make this uniform[1].

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[1] And yes, I will volunteer to do some or all of the required
implementation work, if that's helpful. Or else somebody else can do
it. That's good, too.

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> The second is for making deployment scripts idempotent. For example,
>> say you have script A which creates table "josh", and script B which
>> needs table "josh" to be empty, if present. Since the two scripts are
>> tied to different database features, and you don't know which one will
>> be deployed first, it's useful to have TRUNCATE IF EXISTS. Yes, you can
>> solve that problem with DO, but why make users go to the extra effort?

> Hmm. That's an interesting point.

I'm still not buying this as a realistic use-case. The only way
TRUNCATE IF EXISTS helps script B is if B isn't going to do *anything*
with table "josh" except truncate it. I will grant that there might be
a case or two out there where that's just the ticket, but I think
they're probably few and far between; not enough to justify bespoke
syntax. As Robert already pointed out, a quick DO handles the problem
well enough if you only need it once in a blue moon.

I also note the lack of response to my point about IF EXISTS being
squishy to the point of outright dangerous in the multi-table case.
I might hold still and not complain if we didn't have the multi-table
syntax. But with it, this looks a lot less like a well-considered
feature and a lot more like something that was implemented because
it could be done in two lines, as long as you aren't too picky about
what the semantics are. TBH, I think most all of our ventures in
IF(NOT)EXISTS have suffered from that disease, but that doesn't mean
I'm not going to complain when we adopt the same cowboy approach to
command semantics for ever thinner justifications.

regards, tom lane

On Tue, Oct 09, 2012 at 09:10:13PM -0400, Robert Haas wrote:
> On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> > The second is for making deployment scripts idempotent. For example,
> > say you have script A which creates table "josh", and script B which
> > needs table "josh" to be empty, if present. Since the two scripts are
> > tied to different database features, and you don't know which one will
> > be deployed first, it's useful to have TRUNCATE IF EXISTS. Yes, you can
> > solve that problem with DO, but why make users go to the extra effort?
>
> Hmm. That's an interesting point. I think we're currently in
> somewhat of a limbo zone about where we ought to have IF EXISTS and IF
> NOT EXISTS options, and where we should not. Really, I'd like to
> figure out what policy we want to have, and then go make everything
> work that way. I don't exactly know what the policy should be, but if
> we don't have one then we're going to have to argue about every patch
> individually, which is already getting to be more than tedious.

Agreed. I, too, struggle to envision the concrete use case for TRUNCATE IF
EXISTS, but adding IF [NOT] EXISTS to some marginal candidate commands would
not hurt as part of a broad plan.

> At
> the one extreme, you have Tom, who probably would not have added any
> of these given his druthers; at the other extreme, there are probably
> some people who would say we ought to have this for every command in
> the book, right down to INSERT IF EXISTS (and, hey, why not INSERT OR
> CREATE for good measure?). I'm not sure what the right thing to do
> is... but we should probably come up with some consensus position we
> can all live with, and then go make this uniform[1].

For what it's worth, I'm in that camp of disfavoring all IF [NOT] EXISTS
syntax. I worked on a project that fed idempotent SQL scripts through psql to
migrate schema changes; I used such syntax then and appreciated the keystrokes
saved. But the syntax is a bandage for raw psql input remaining a hostile
environment for implementing the full range of schema changes. Switch to
submitting your SQL from a richer programming environment, and these additions
to core syntax cease to add much.

nm

On 10 October 2012 02:10, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> The second is for making deployment scripts idempotent. For example,
>> say you have script A which creates table "josh", and script B which
>> needs table "josh" to be empty, if present. Since the two scripts are
>> tied to different database features, and you don't know which one will
>> be deployed first, it's useful to have TRUNCATE IF EXISTS. Yes, you can
>> solve that problem with DO, but why make users go to the extra effort?
>
> Hmm. That's an interesting point. I think we're currently in
> somewhat of a limbo zone about where we ought to have IF EXISTS and IF
> NOT EXISTS options, and where we should not. Really, I'd like to
> figure out what policy we want to have, and then go make everything
> work that way. I don't exactly know what the policy should be, but if
> we don't have one then we're going to have to argue about every patch
> individually, which is already getting to be more than tedious. At
> the one extreme, you have Tom, who probably would not have added any
> of these given his druthers; at the other extreme, there are probably
> some people who would say we ought to have this for every command in
> the book, right down to INSERT IF EXISTS (and, hey, why not INSERT OR
> CREATE for good measure?). I'm not sure what the right thing to do
> is... but we should probably come up with some consensus position we
> can all live with, and then go make this uniform[1].

Damn it, now I have an opinion.

I would say two things:

1) Consistency for DDL syntax is important. Sometimes humans still
write SQL and often, ORMs generate SQL. Asking poeple to guess what
our syntax is from release to release is a good way to have people not
bother to support us properly. As Peter says, Truncate is not DDL (and
argument I have used), but it is often used alongside DDL and does
have many of the same characteristics. INSERT IF EXISTS is simply an
argument ad absurdum, not a requirement that needs to be addressed.

2) Clearly, rollout scripts benefit from not throwing errors.
Personally I would prefer setting SET ddl_abort_on_missing_object =
false; at the top of a script than having to go through every SQL
statement and add extra syntax. That might even help people more than
littering SQL with extra clauses.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 10 October 2012 02:10, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>> The second is for making deployment scripts idempotent. For example,
>>> say you have script A which creates table "josh", and script B which
>>> needs table "josh" to be empty, if present. Since the two scripts are
>>> tied to different database features, and you don't know which one will
>>> be deployed first, it's useful to have TRUNCATE IF EXISTS. Yes, you can
>>> solve that problem with DO, but why make users go to the extra effort?
>>
>> Hmm. That's an interesting point. I think we're currently in
>> somewhat of a limbo zone about where we ought to have IF EXISTS and IF
>> NOT EXISTS options, and where we should not. Really, I'd like to
>> figure out what policy we want to have, and then go make everything
>> work that way. I don't exactly know what the policy should be, but if
>> we don't have one then we're going to have to argue about every patch
>> individually, which is already getting to be more than tedious. At
>> the one extreme, you have Tom, who probably would not have added any
>> of these given his druthers; at the other extreme, there are probably
>> some people who would say we ought to have this for every command in
>> the book, right down to INSERT IF EXISTS (and, hey, why not INSERT OR
>> CREATE for good measure?). I'm not sure what the right thing to do
>> is... but we should probably come up with some consensus position we
>> can all live with, and then go make this uniform[1].
>
> Damn it, now I have an opinion.
>
> I would say two things:
>
> 1) Consistency for DDL syntax is important. Sometimes humans still
> write SQL and often, ORMs generate SQL. Asking poeple to guess what
> our syntax is from release to release is a good way to have people not
> bother to support us properly. As Peter says, Truncate is not DDL (and
> argument I have used), but it is often used alongside DDL and does
> have many of the same characteristics. INSERT IF EXISTS is simply an
> argument ad absurdum, not a requirement that needs to be addressed.

I think I agree. We should not go down the "well, we haven't got
UPSERT yet, and that's why we shouldn't do this one" road.

> Clearly, rollout scripts benefit from not throwing errors.
> Personally I would prefer setting SET ddl_abort_on_missing_object =
> false; at the top of a script than having to go through every SQL
> statement and add extra syntax. That might even help people more than
> littering SQL with extra clauses.

Here, I'm rather less comfortable.

I could easily take the opposite tack, that rollout scripts benefit
from yes, indeed, throwing errors, so that inconsistencies get
rectified. I don't want to take that argument *too* far, mind you.

Doing things that "avoid throwing errors" isn't purely a good thing.
If a DDL script is doing genuinely different things when running in
different environments, it's difficult to be confident that the result
is correct in all cases. Hiding errors might lead to ignoring
important differences.

Given two further bits of "processing model," I might be made more
comfortable...

1. A direction we're trying to go is to have good comparison tools to
see where schemas differ between environments. (I need to poke at
getting a tool I call "pgcmp" released publicly.) If you have the
capability to compare the starting schema against what you imagined it
was supposed to be, as well as to compare the post-rollout schema
against what it was supposed to become, then that keeps things
relatively under control. If you can quickly determine divergence
from expected schema, then you can more easily keep on track.

2. [More on the SQL syntax/clauses front] In order to NOT litter the
SQL with extra clauses, I expect that there needs to be something of a
model of How You SHOULD Update Your Schema, in effect, some idiomatic
'best practice' that tends to cut risk and diminish the need for IF
EXISTS/IF NOT EXISTS. I'd be interested to see an outline of that
model.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On 10/9/12 1:35 PM, Peter Eisentraut wrote:
> On 10/9/12 5:09 AM, Simon Riggs wrote:
>> Anyone want to check for any other missing IF EXISTS capability in other DDL?
>
> TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is
> stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next?

That's a very good point. I tend to think of all utility commands as
DDL, which of course they're not.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 10/09/2012 10:04 PM, Robert Haas wrote:

>> - if a table is not yet or no more visible, because of search_path
>> modification
>
> I don't think I understand the case you are describing here.

Here's a sample :

begin;
set search_path = foo, public;
create table c ( … ) ;
commit;

begin;
set search_path = bar, public;
create table d ( … );
truncate if exists c;
commit;

>
>> - if a table was dropped, for any reason
>
> But in this case surely you could use DROP IF EXISTS.

Well, TRUNCATE and DROP TABLE are not the same, I don't see your point ?

>
> I've been a big proponent of adding "IF EXISTS" support to CREATE
> TABLE and ALTER TABLE but I'm having a hard time getting excited about
> this one. I can't imagine that many people would use it, and those
> who do can implement it in about 10 lines of PL/pgsql. The existence
> of DO blocks and the fact that PL/pgsql is now installed by default
> have made it much more convenient to solve these kinds of problems
> using those tools rather than needing dedicated syntax. That does not
> mean that the most frequently used cases shouldn't have dedicated
> syntax anyway, for convenience, but I'm doubtful that this falls into
> that category.
>

I don't think we can ask people to do DO blocks for TRUNCATE, when they
simply use IF EXISTS with DROP TABLE.

Even if TRUNCATE is not a DDL, it's often use as is

--
Sébastien Lardière
PostgreSQL DBA Team Manager
Hi-Media

On 10/09/2012 04:06 PM, Tom Lane wrote:
> Second, to my mind the point of a multi-table TRUNCATE is to ensure that
> all the referenced tables get reset to empty *together*. With something
> like this, you'd have no such guarantee. Consider a timeline like this:
>
> Session 1 Session 2
>
> TRUNCATE IF EXISTS a, b, c;
> ... finds c doesn't exist ...
> ... working on a and b ...
> CREATE TABLE c ( ... );
> INSERT INTO c ...;
> ... commits ...
>
> Now we have a, b, and c, but c isn't empty, violating the expectations
> of session 1. So even if there's a use-case for IF EXISTS on a single
> table, I think it's very very dubious to allow it in multi-table
> commands.

Hi,

I've to say that I don't understand your timeline :

- If c exist in Session 1, CREATE TABLE in Session 2 can't be done,
neither INSERT
- If c doesn't exists in Session 1, it will be ignored, then, Session 2
work fine.

In any case, TRUNCATE is sent before INSERT, but it can't lock an object
which still not exists.

I understand that people don't want TRUNCATE IF EXISTS, but, in my point
of view, even if TRUNCATE is not a DDL, it's the same use-case as DROP
TABLE IF EXISTS.

Regards,

--
Sébastien Lardière
PostgreSQL DBA Team Manager
Hi-Media

On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> 2) Clearly, rollout scripts benefit from not throwing errors.
> Personally I would prefer setting SET ddl_abort_on_missing_object =
> false; at the top of a script than having to go through every SQL
> statement and add extra syntax. That might even help people more than
> littering SQL with extra clauses.

I've been thinking about this a bit more. It seems to me that the
awkwardness here has a lot to do with the fact that the IF EXISTS is
attached to the command rather than sitting outside it. We're
basically trying to put the control logic inside the command itself,
whereas probably what we really want is for the control logic to be
able to exist around the command, like this:

IF TABLE foo EXISTS THEN
TRUNCATE TABLE foo;
END IF

But of course that doesn't work. I think you have to write something like this:

do $$
begin
if (select 1 from pg_class where relname = 'foo' and
pg_table_is_visible(oid)) then
truncate table foo;
end if;
end
$$;

That is a lot more typing and it's not exactly intuitive. One obvious
thing that would help is a function pg_table_exists(text) that would
return true or false. But even with that there's a lot of syntactic
sugar in there that is less than ideal: begin/end, dollar-quoting, do.
Whatever becomes of this particular patch, I think we'd make a lot of
people really happy if we could find a way to dispense with some of
that stuff in simple cases.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 11 October 2012 19:59, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> 2) Clearly, rollout scripts benefit from not throwing errors.
>> Personally I would prefer setting SET ddl_abort_on_missing_object =
>> false; at the top of a script than having to go through every SQL
>> statement and add extra syntax. That might even help people more than
>> littering SQL with extra clauses.
>
> I've been thinking about this a bit more. It seems to me that the
> awkwardness here has a lot to do with the fact that the IF EXISTS is
> attached to the command rather than sitting outside it. We're
> basically trying to put the control logic inside the command itself,
> whereas probably what we really want is for the control logic to be
> able to exist around the command, like this:
>
> IF TABLE foo EXISTS THEN
> TRUNCATE TABLE foo;
> END IF
>
> But of course that doesn't work. I think you have to write something like this:
>
> do $$
> begin
> if (select 1 from pg_class where relname = 'foo' and
> pg_table_is_visible(oid)) then
> truncate table foo;
> end if;
> end
> $$;
>
> That is a lot more typing and it's not exactly intuitive. One obvious
> thing that would help is a function pg_table_exists(text) that would
> return true or false. But even with that there's a lot of syntactic
> sugar in there that is less than ideal: begin/end, dollar-quoting, do.
> Whatever becomes of this particular patch, I think we'd make a lot of
> people really happy if we could find a way to dispense with some of
> that stuff in simple cases.

Yeh, definitely.

So we just need a function called pg_if_table_exists(table, SQL) which
wraps a test in a subtransaction.

And you write

SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');

and we can even get rid of all that other DDL crud that's been added....

and we can have pg_if_table_not_exists() also.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 10/11/2012 09:22 PM, Simon Riggs wrote:

>>
>> That is a lot more typing and it's not exactly intuitive. One obvious
>> thing that would help is a function pg_table_exists(text) that would
>> return true or false. But even with that there's a lot of syntactic
>> sugar in there that is less than ideal: begin/end, dollar-quoting, do.
>> Whatever becomes of this particular patch, I think we'd make a lot of
>> people really happy if we could find a way to dispense with some of
>> that stuff in simple cases.
>
> Yeh, definitely.
>
> So we just need a function called pg_if_table_exists(table, SQL) which
> wraps a test in a subtransaction.
>
> And you write
>
> SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
>
> and we can even get rid of all that other DDL crud that's been added....
>
> and we can have pg_if_table_not_exists() also.
>

If we can do something like :

SELECT pg_if_table_exists('bar' , pg_if_table_exists('foo', 'TRUNCATE
TABLE foo, bar, foobar')) ;

or

SELECT pg_if_tables_exists('TRUNCATE TABLE foo, bar, foobar', 'foo',
'bar') ;

I say yes !

--
Sébastien Lardière
PostgreSQL DBA Team Manager
Hi-Media

On 10/12/2012 12:03 PM, Sébastien Lardière wrote:
>
> If we can do something like :
>
> SELECT pg_if_table_exists('bar' , pg_if_table_exists('foo', 'TRUNCATE
> TABLE foo, bar, foobar')) ;
>
> or
>
> SELECT pg_if_tables_exists('TRUNCATE TABLE foo, bar, foobar', 'foo',
> 'bar') ;
>
> I say yes !
>
>

This strikes me as just highly un-SQL-like.

Someone could fairly easily write it for themselves in Plpgsql or C, but
it doesn't seem to me like something we should be doing.

I tend to agree with Noah's comment upthread:

> But the syntax is a bandage for raw psql input remaining a hostile
> environment for implementing the full range of schema changes. Switch to
> submitting your SQL from a richer programming environment, and these additions
> to core syntax cease to add much.

I think this goes a fortiori for Heath Robinson-like devices such as this.

cheers

andrew

Hello

2012/10/12 Sébastien Lardière <slardiere(at)hi-media(dot)com>:
> On 10/11/2012 09:22 PM, Simon Riggs wrote:
>
>>>
>>> That is a lot more typing and it's not exactly intuitive. One obvious
>>> thing that would help is a function pg_table_exists(text) that would
>>> return true or false. But even with that there's a lot of syntactic
>>> sugar in there that is less than ideal: begin/end, dollar-quoting, do.
>>> Whatever becomes of this particular patch, I think we'd make a lot of
>>> people really happy if we could find a way to dispense with some of
>>> that stuff in simple cases.
>>
>> Yeh, definitely.
>>
>> So we just need a function called pg_if_table_exists(table, SQL) which
>> wraps a test in a subtransaction.
>>
>> And you write
>>
>> SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
>>
>> and we can even get rid of all that other DDL crud that's been added....
>>
>> and we can have pg_if_table_not_exists() also.
>>
>
> If we can do something like :
>
> SELECT pg_if_table_exists('bar' , pg_if_table_exists('foo', 'TRUNCATE
> TABLE foo, bar, foobar')) ;
>
> or
>
> SELECT pg_if_tables_exists('TRUNCATE TABLE foo, bar, foobar', 'foo',
> 'bar') ;
>
> I say yes !

I don't like it in core - it can be used for SQL injection - it is dynamic SQL.

Regards

Pavel

>
>
> --
> Sébastien Lardière
> PostgreSQL DBA Team Manager
> Hi-Media
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> This strikes me as just highly un-SQL-like.

+1

> I tend to agree with Noah's comment upthread:
>
>> But the syntax is a bandage for raw psql input remaining a hostile
>> environment for implementing the full range of schema changes. Switch to
>> submitting your SQL from a richer programming environment, and these additions
>> to core syntax cease to add much.

I think the comment is generally true, but fails in the face of the
simplicity of this particular grammar addition. Also, I have the same
problem as Sébastien to understand Tom's usage example.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> INSERT IF EXISTS (and, hey, why not INSERT OR
> CREATE for good measure?). I'm not sure what the right thing to do
> is... but we should probably come up with some consensus position we
> can all live with, and then go make this uniform[1].

'INSERT OR CREATE' was specifically mentioned as something which would
be very useful for certain development-type activities at PGOpen. I'm
on the fence about it myself, but it is kind of a neat idea.

Thanks,

Stephen

On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> So we just need a function called pg_if_table_exists(table, SQL) which
> wraps a test in a subtransaction.
>
> And you write
>
> SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
>
> and we can even get rid of all that other DDL crud that's been added....
>
> and we can have pg_if_table_not_exists() also.

You could make this more composable by having pg_table_exists() and
pg_execute_sql_from_string(). Then you can write: SELECT CASE WHEN
pg_table_exists(...) THEN pg_execute_sql_from_string(...) END. And if
you want the if-not-exists case then just stick a NOT in there. And
if you want a more complicated condition, you can easily write that as
well.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Second, to my mind the point of a multi-table TRUNCATE is to ensure that
> all the referenced tables get reset to empty *together*. With something
> like this, you'd have no such guarantee. Consider a timeline like this:

Don't we have the exact same issue with DROP TABLE and multi-table
support for it today..?

Session 1 Session 2

DROP IF EXISTS a, b, c;
... finds c doesn't exist ...
... working on a and b ...
CREATE TABLE c ( ... );
... commits ...

But now we have a table 'c' where we didn't expect to because we DROP'd
it? If you COMMIT then you can't expect things to not have changed under
you after your transaction is over, you're going to have to be ready to
deal with the consequences either way..

Heck, even if your scenario, don't you have to be concerned in Session 1
that someone insert'd data into 'c' after your commit but before you
open your next transaction?

The TRUNCATE in a multi-table case, imv, is typically to address FK
relationships. Provided we don't allow a situation where data could be
stored which violates a FK due to a TRUNCATE IF EXISTS happening in some
other session concurrently (which I don't think could happen, but it'd
be something to verify, I suppose), the precedent of proceeding with
multi-table IF EXISTS commands even in the face of a given table not
existing should hold.

If we don't feel that is appropriate for TRUNCATE, then I would question
if we should have it for DROP TABLE- but if we don't have that semantic,
what are we going to have? All tables have to either exist or not
exist? Disallow IF EXISTS when a multi-table command is given? Neither
strikes me as better.

Thanks,

Stephen

Robert Haas escribió:
> On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > So we just need a function called pg_if_table_exists(table, SQL) which
> > wraps a test in a subtransaction.
> >
> > And you write
> >
> > SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
> >
> > and we can even get rid of all that other DDL crud that's been added....
> >
> > and we can have pg_if_table_not_exists() also.
>
> You could make this more composable by having pg_table_exists() and
> pg_execute_sql_from_string(). Then you can write: SELECT CASE WHEN
> pg_table_exists(...) THEN pg_execute_sql_from_string(...) END. And if
> you want the if-not-exists case then just stick a NOT in there. And
> if you want a more complicated condition, you can easily write that as
> well.

Uh, we had an execute() function of sorts in the extensions patch; that
seems to have been ripped out. Do we want it back?

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

* Josh Berkus (josh(at)agliodbs(dot)com) wrote:
> On 10/9/12 1:35 PM, Peter Eisentraut wrote:
> > On 10/9/12 5:09 AM, Simon Riggs wrote:
> >> Anyone want to check for any other missing IF EXISTS capability in other DDL?
> >
> > TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is
> > stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next?
>
> That's a very good point. I tend to think of all utility commands as
> DDL, which of course they're not.

I don't actually see why that's, inherently, a bad idea. Nor do I see
why IF EXISTS should only apply to DDL and not to all commands.

Obviously, if you write 'IF EXISTS', you've got a plan to deal with the
fact that it doesn't exist. In a lossy system that's using partitions,
I could actually see a pretty good use-case for wanting INSERT IF
EXISTS (rather than having to constantly poll, waiting for the partition
table that's supposted to be created by some other process to show up).

Thanks,

Stephen

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> Uh, we had an execute() function of sorts in the extensions patch; that
> seems to have been ripped out. Do we want it back?

It was pretty different from what's being proposed here, as it was the
server-side version of psql \i feature, that is, executing commands read
directly from a SQL file on the server file's system.

I'd much prefer that we spend time making such an hypothetical feature
that irrelevant in all cases. There's still some work here, because the
feature only is hypothetical to end users, that's exactly what we rely
on today in the backend internal code…

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Fri, Oct 12, 2012 at 3:23 PM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> Uh, we had an execute() function of sorts in the extensions patch; that
> seems to have been ripped out. Do we want it back?

Well, it wasn't necessary for that patch, which is why it got ripped
out. But I don't remember anybody saying it was a bad idea in
general. Which also doesn't mean that it's a good idea in general.
I'm open to whatever other people think is best.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> So we just need a function called pg_if_table_exists(table, SQL) which
>> wraps a test in a subtransaction.
>>
>> And you write
>>
>> SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
>>
>> and we can even get rid of all that other DDL crud that's been added....
>>
>> and we can have pg_if_table_not_exists() also.
>
> You could make this more composable by having pg_table_exists() and
> pg_execute_sql_from_string(). Then you can write: SELECT CASE WHEN
> pg_table_exists(...) THEN pg_execute_sql_from_string(...) END. And if
> you want the if-not-exists case then just stick a NOT in there. And
> if you want a more complicated condition, you can easily write that as
> well.

While that certainly has the merit of being compact, it mixes kinds of
evaluation (e.g. - parts of it are parsed at different times) and
requires quoting that isn't true for the other sorts of "IF EXISTS"
queries.

To be sure, you can do anything you like inside a DO $$ $$ language
plpgsql; block, but it's not nice to have to do a lot of work
involving embedding code between languages. Makes it harder to
manipulate, analyze, and verify.

Let me observe that Perl has, as one of its conditional concepts, the
notion of a "statement modifier"
<http://perldoc.perl.org/perlsyn.html#Statement-Modifiers>, which
corresponds pretty much to the IF EXISTS/IF NOT EXISTS modifiers that
have gotten added to Postgres over the last few versions. (I *think*
statement modifiers are attributable to SNOBOL, not 100% sure. I'm
pretty sure it predates Perl.)

I suggest the though of embracing statement modifiers in DDL, with
some options possible:
a) { DDL STATEMENT } IF CONDITION;
b) { DDL STATEMENT } UNLESS CONDITION;

where CONDITION has several possible forms:
i) {IF|UNLESS} ( SQL expression returning T/F )
ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
{TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name

That feels like a cleaner extension than what we have had, with the IF
EXISTS/IF NOT EXISTS clauses that have been added to various
CREATE/DROP/ALTER commands.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On 10/12/12 2:05 PM, Christopher Browne wrote:
> That feels like a cleaner extension than what we have had, with the IF
> EXISTS/IF NOT EXISTS clauses that have been added to various
> CREATE/DROP/ALTER commands.

+1

Josh like!

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 10/12/2012 11:05 PM, Christopher Browne wrote:
> On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> So we just need a function called pg_if_table_exists(table, SQL) which
>>> wraps a test in a subtransaction.
>>>
>>> And you write
>>>
>>> SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
>>>
>>> and we can even get rid of all that other DDL crud that's been added....
>>>
>>> and we can have pg_if_table_not_exists() also.
>> You could make this more composable by having pg_table_exists() and
>> pg_execute_sql_from_string(). Then you can write: SELECT CASE WHEN
>> pg_table_exists(...) THEN pg_execute_sql_from_string(...) END. And if
>> you want the if-not-exists case then just stick a NOT in there. And
>> if you want a more complicated condition, you can easily write that as
>> well.
> While that certainly has the merit of being compact, it mixes kinds of
> evaluation (e.g. - parts of it are parsed at different times) and
> requires quoting that isn't true for the other sorts of "IF EXISTS"
> queries.
>
> To be sure, you can do anything you like inside a DO $$ $$ language
> plpgsql; block, but it's not nice to have to do a lot of work
> involving embedding code between languages. Makes it harder to
> manipulate, analyze, and verify.
>
> Let me observe that Perl has, as one of its conditional concepts, the
> notion of a "statement modifier"
> <http://perldoc.perl.org/perlsyn.html#Statement-Modifiers>, which
> corresponds pretty much to the IF EXISTS/IF NOT EXISTS modifiers that
> have gotten added to Postgres over the last few versions. (I *think*
> statement modifiers are attributable to SNOBOL, not 100% sure. I'm
> pretty sure it predates Perl.)
>
> I suggest the though of embracing statement modifiers in DDL, with
> some options possible:
> a) { DDL STATEMENT } IF CONDITION;
> b) { DDL STATEMENT } UNLESS CONDITION;
We could even go as far as

{ DDL STATEMENT } IF CONDITION ELSE {ANOTHER DDL STATEMENT };

For example

CREATE TABLE mytable(...)
IF NOT EXISTS TABLE mytable
ELSE TRUNCATE mytable;

>
> where CONDITION has several possible forms:
> i) {IF|UNLESS} ( SQL expression returning T/F )
> ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
> {TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name
>
> That feels like a cleaner extension than what we have had, with the IF
> EXISTS/IF NOT EXISTS clauses that have been added to various
> CREATE/DROP/ALTER commands.

Christopher Browne <cbbrowne(at)gmail(dot)com> writes:
> I suggest the though of embracing statement modifiers in DDL, with
> some options possible:
> a) { DDL STATEMENT } IF CONDITION;
> b) { DDL STATEMENT } UNLESS CONDITION;

Just saying. I hate that. Makes it harder to read, that last bit at the
end of the command changes it all. It's cool for a linguist, I guess,
but we're not typing sentences at the psql prompt…

> where CONDITION has several possible forms:
> i) {IF|UNLESS} ( SQL expression returning T/F )
> ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
> {TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name

Now we're talking about T-SQL? Sorry, I suppose you didn't mean to lead
us that way, but I couldn't resist comparing. Soon enough you want a
full programming language there.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

>> where CONDITION has several possible forms:
>> i) {IF|UNLESS} ( SQL expression returning T/F )
>> ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
>> {TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name
>
> Now we're talking about T-SQL? Sorry, I suppose you didn't mean to lead
> us that way, but I couldn't resist comparing. Soon enough you want a
> full programming language there.

Well, embedding such a thing into plpgsql wouldn't be a bad thing. It's
a lot less hard on the DevOps person to request that they write a DO
statement if the DO statement is one line:

DO $$
BEGIN
TRUNCATE TABLE foo IF EXISTS foo;
END;$$;

Come to think of it, I've *often* wished for the perl-ish "do x if y"
syntax for plpgsql, and not just for DDL.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Fri, Oct 12, 2012 at 5:52 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Christopher Browne <cbbrowne(at)gmail(dot)com> writes:
>> I suggest the though of embracing statement modifiers in DDL, with
>> some options possible:
>> a) { DDL STATEMENT } IF CONDITION;
>> b) { DDL STATEMENT } UNLESS CONDITION;
>
> Just saying. I hate that. Makes it harder to read, that last bit at the
> end of the command changes it all. It's cool for a linguist, I guess,
> but we're not typing sentences at the psql prompt…

I could see it being
WHEN CONDITION { STATEMENT } OTHERWISE { STATEMENT };

It's all a strawman proposal, where I'm perfectly happy if there's
something people like better. I like to think this is cleaner than
the present proliferation of {IF EXISTS|IF NOT EXISTS}, but if others
don't concur, there's little point to taking it further.

>> where CONDITION has several possible forms:
>> i) {IF|UNLESS} ( SQL expression returning T/F )
>> ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
>> {TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name
>
> Now we're talking about T-SQL? Sorry, I suppose you didn't mean to lead
> us that way, but I couldn't resist comparing. Soon enough you want a
> full programming language there.

Heh. Next, I'll be proposing LETREC*, or of adopting the EVERY
operator from Icon, and coroutines from BCPL :-). Keen on LOOP? :-)

The fact that we now have WITH RECURSIVE does extend what's reasonable
to hope for :-).
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Tue, Oct 9, 2012 at 9:04 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I've been a big proponent of adding "IF EXISTS" support to CREATE
> TABLE and ALTER TABLE but I'm having a hard time getting excited about
> this one. I can't imagine that many people would use it

The reason CREATE IF NOT EXISTS and DROP IF EXISTS are so useful is
because they're shortcuts for ensuring some specific state is always
true. Regardless of whether the table existed before, now it does or
doesn't as desired. (The concern about create was in fact specifically
that it wouldn't guarantee that the same table definition would exist
afterwards)

The same is not true of TRUNCATE IF EXISTS. In that case after the
command has run either the table exists and is empty or it doesn't
exist and still needs to be created.

I take it the intended use is something like
TRUNCATE IF EXISTS foo;
CREATE IF NOT EXISTS foo...

So perhaps what we really need is a CREATE OR TRUNCATE foo(...), but
just plain TRUNCATE IF EXISTS doesn't seem to make sense.

--
greg

On 10/12/12 3:49 PM, Greg Stark wrote:
> TRUNCATE IF EXISTS foo;
> CREATE IF NOT EXISTS foo...

Thing is, this can be written:

CREATE IF NOT EXISTS foo ...
TRUNCATE foo;

For the exact same result.

So, based on all of the objections and discussion on this feature, I
personally no longer support it.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Fri, Oct 12, 2012 at 5:52 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Christopher Browne <cbbrowne(at)gmail(dot)com> writes:
>> I suggest the though of embracing statement modifiers in DDL, with
>> some options possible:
>> a) { DDL STATEMENT } IF CONDITION;
>> b) { DDL STATEMENT } UNLESS CONDITION;
>
> Just saying. I hate that. Makes it harder to read, that last bit at the
> end of the command changes it all. It's cool for a linguist, I guess,
> but we're not typing sentences at the psql prompt…
>
>> where CONDITION has several possible forms:
>> i) {IF|UNLESS} ( SQL expression returning T/F )
>> ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
>> {TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name
>
> Now we're talking about T-SQL? Sorry, I suppose you didn't mean to lead
> us that way, but I couldn't resist comparing. Soon enough you want a
> full programming language there.

To be perfectly frank, I think that's exactly where we ought to be
going. Oracle and Microsoft both did it, so why are we convinced it's
a bad idea? One of the huge problems with PL/pgsql is that every SQL
expression in there has to be passed to the executor separately, which
is painfully slow. It frequently doesn't matter because writing loops
in a procedural language is often the wrong approach anyway, but it is
not always the wrong approach and people sometimes do it even when it
is, and then they end up unhappy.

In the short term this is not a practical outcome for us; what we can
reasonably do is add a few convenience functions to what we already
have to make it easy to test for things like the presence of a table,
the presence of a column, the presence of a schema, etc. But in the
longer term, this is definitely something that people want. Being
able to wrap control-flow statements around SQL is fundamentally
useful, which is why every major database supports it. Being able to
do it without a lot of superfluous syntactic sugar and with good
performance is even more useful.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Oct 15, 2012 at 3:26 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> To be perfectly frank, I think that's exactly where we ought to be
> going. Oracle and Microsoft both did it, so why are we convinced it's
> a bad idea? One of the huge problems with PL/pgsql is that every SQL
> expression in there has to be passed to the executor separately, which
> is painfully slow.

I'm a bit lost. I would think pl/pgsql is precisely the same as
Oracle's pl/sql and MS's T-SQL. I see the complaint you have as a
purely implementation detail. I don't think pl/pgsql is the best
implemented part of Postgres but I don't see how integrating it into
the core is going to automatically make it all wonderful either.

Fwiw my experience has consistently been that life got better whenever
I moved anything I had implemented as PL/SQL or PL/pgsql into client
code in Perl or Python.

--
greg

On 10/15/2012 04:34 PM, Greg Stark wrote:
> On Mon, Oct 15, 2012 at 3:26 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> To be perfectly frank, I think that's exactly where we ought to be
>> going. Oracle and Microsoft both did it, so why are we convinced it's
>> a bad idea? One of the huge problems with PL/pgsql is that every SQL
>> expression in there has to be passed to the executor separately, which
>> is painfully slow.
> I'm a bit lost. I would think pl/pgsql is precisely the same as
> Oracle's pl/sql and MS's T-SQL. I see the complaint you have as a
> purely implementation detail. I don't think pl/pgsql is the best
> implemented part of Postgres but I don't see how integrating it into
> the core is going to automatically make it all wonderful either.
>
> Fwiw my experience has consistently been that life got better whenever
> I moved anything I had implemented as PL/SQL or PL/pgsql into client
> code in Perl or Python.
Just curious - why did you move it into _client_ code ?

Why not pl/perl or pl/python ?

Was performance not a concern and it was easier (administratively?) to
manage it on the client side ?

---------
Hannu

>

On Mon, Oct 15, 2012 at 10:34 AM, Greg Stark <stark(at)mit(dot)edu> wrote:
> I'm a bit lost. I would think pl/pgsql is precisely the same as
> Oracle's pl/sql and MS's T-SQL. I see the complaint you have as a
> purely implementation detail. I don't think pl/pgsql is the best
> implemented part of Postgres but I don't see how integrating it into
> the core is going to automatically make it all wonderful either.

It isn't. But (1) there would be a significant usability benefit in
not having to surround procedural logic with DO $$ BEGIN ... END $$
and (2) PL/pgsql's performance issues seem to revolve around the fact
that you don't get one big ol' plan thingy that can be passed to the
executor and run; instead, you interpret each statement separately and
pass them off to the executor one piece at a time.

It wouldn't technically be necessary to integrate the code fully into
core into realize these benefits; you could maintain some abstraction
layer in between and provide an API to push information back and
forth. But to take a trivial example, consider a FOR loop that
executes an enclosed SQL statement a large number of times. Right
now, we build a plan tree for the SQL statement and then start up and
shut down the executor N times. If we could instead push an "iterate"
not on top of the plan tree to handle the iteration, and then start up
the executor, run the plan, and shut down the executor, my guess is
that it would be way faster than our current implementation.
Everything I've seen leads me to believe that the executor is quite
zippy when it gets going, but bouncing in and out of it repeatedly
seems to be a source of real pain.

> Fwiw my experience has consistently been that life got better whenever
> I moved anything I had implemented as PL/SQL or PL/pgsql into client
> code in Perl or Python.

Hmm... I've had the opposite experience, which I guess is why I've got
strong feelings about this. I've found that checking for uniqueness
violations without relying on the database doesn't really work due to
concurrency issues, and once I've got to catch that error from the
database side and expose it to the user as a nicely-formatted
complaint (the name you have chosen is already in use; please choose
another) I have found that it seems to make sense to push everything
other than the initial, relatively trivial syntax checking into
PostgreSQL. Anyway, I think there's probably more than one sensible
design decision there and may come down to personal preference and
toolchain selection more than anything.

Whatever either of us think, though, the complaint at the top of this
thread indicates that people are NOT happy doing this on the client
side and DO isn't convenient enough either. What do we do about that?
I'm not extraordinarily attached to any specific proposal but I think
we should be looking for ways to make this better.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Oct 15, 2012 at 11:57 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Oct 15, 2012 at 10:34 AM, Greg Stark <stark(at)mit(dot)edu> wrote:
>> I'm a bit lost. I would think pl/pgsql is precisely the same as
>> Oracle's pl/sql and MS's T-SQL. I see the complaint you have as a
>> purely implementation detail. I don't think pl/pgsql is the best
>> implemented part of Postgres but I don't see how integrating it into
>> the core is going to automatically make it all wonderful either.
>
> It isn't. But (1) there would be a significant usability benefit in
> not having to surround procedural logic with DO $$ BEGIN ... END $$
> and (2) PL/pgsql's performance issues seem to revolve around the fact
> that you don't get one big ol' plan thingy that can be passed to the
> executor and run; instead, you interpret each statement separately and
> pass them off to the executor one piece at a time.

The places where *I* care about this are places where performance is
almost entirely irrelevant to the question.

When I'm writing 'scripts' that are doing this kind of thing, I'm
doing schema 'surgery', and, within reason, it's not particularly
performance sensitive. I'm much more worried about DDL scripts being
repeatable and manageable than I am about them being fast.

So I'm going to elide the performance bits.

Robert, when you first tossed out the notion of:

do $$
begin
if (select 1 from pg_class where relname = 'foo' and
pg_table_is_visible(oid)) then
truncate table foo;
end if;
end
$$;

my first reaction was "Ick! Why am I switching languages (e.g. -
from plain SQL to pl/pgsql), and running functions to do this?!?"

In retrospect, your later comments make it pretty clear that you're
not proposing that as the end state, just that that's the
functionality that needs to be run.

That would would be equivalent to my would-be-strawman syntax of:

TRUNCATE TABLE public.foo IF EXISTS TABLE public.foo;

I'm comfortable that Dimitri didn't particularly love the idea of
stowing the conditional at the end; it was just a strawman proposal,
and what was particularly important to me was to make sure that it was
recognizable that other systems (e.g. - Perl, Ruby, probably SNOBOL)
have done the very same thing. I'd be perfectly happy if someone came
up with something better. The number of "+1"'s thus far is pretty
gratifying, mind you.

>> Fwiw my experience has consistently been that life got better whenever
>> I moved anything I had implemented as PL/SQL or PL/pgsql into client
>> code in Perl or Python.
>
> Hmm... I've had the opposite experience, which I guess is why I've got
> strong feelings about this.

When I'm "managing schema", I have exactly *zero* interest in
switching over to Perl or Python. Those aren't languages for managing
database schemas, and, if I wind up using them, my code is going to be
rife with context switches as I'm switching between
"oh, am I writing Perl code?"
and
"Am I attached to the right Perl database connection object, with
the proper transaction context?"
and
"Oh, here is the SQL DDL for managing the schema."

Two of these three varieties of contexts are distracting sidelines to
me. Guess which are the two? :-)
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Mon, Oct 15, 2012 at 12:53 PM, Christopher Browne <cbbrowne(at)gmail(dot)com> wrote:
> The places where *I* care about this are places where performance is
> almost entirely irrelevant to the question.
>
> When I'm writing 'scripts' that are doing this kind of thing, I'm
> doing schema 'surgery', and, within reason, it's not particularly
> performance sensitive. I'm much more worried about DDL scripts being
> repeatable and manageable than I am about them being fast.
>
> So I'm going to elide the performance bits.
>
> Robert, when you first tossed out the notion of:
>
> do $$
> begin
> if (select 1 from pg_class where relname = 'foo' and
> pg_table_is_visible(oid)) then
> truncate table foo;
> end if;
> end
> $$;
>
> my first reaction was "Ick! Why am I switching languages (e.g. -
> from plain SQL to pl/pgsql), and running functions to do this?!?"
>
> In retrospect, your later comments make it pretty clear that you're
> not proposing that as the end state, just that that's the
> functionality that needs to be run.

Yeah, I think the functionality that we need is pretty much there
already today. What we need to do is to get the syntax to a point
where people can write the code they want to write without getting
tangled up by it.

I think the invention of DO was a big step in the right direction,
because before that if you wanted procedural logic in your script, you
had to create a function, call it, and then drop the function. That
is exceedingly awkward and introduces a number of unpleasant and
unnecessary failure modes. With DO, you can write the logic you want
as an SQL statement, it's just a clunky and awkward SQL statement. In
my view the goal ought to be to refine that mechanism to remove the
clunkiness and awkwardness, rather than to invent something completely
new.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> if (select 1 from pg_class where relname = 'foo' and
>> pg_table_is_visible(oid)) then
>> truncate table foo;
>> end if;
>
> Yeah, I think the functionality that we need is pretty much there
> already today. What we need to do is to get the syntax to a point
> where people can write the code they want to write without getting
> tangled up by it.

What about continuing to extend on that incredibly useful WITH syntax we
already have:

WITH target AS (
SELECT oid::regclass AS t
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE pg_table_is_visible(oid)
AND nspname = 'public' AND NOT relname ~ 'exclude-pattern'
)
TRUNCATE TABLE t FROM target;

Maybe somewhat involved as far as code support is concerned. That said,
full integration of a PL into the main parser doesn't strike me as that
easier. Maybe a simpler way to reach the feature would be:

WITH target AS (
SELECT oid::regclass AS t
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE pg_table_is_visible(oid)
AND nspname = 'public' AND NOT relname ~ 'exclude-pattern'
)
EXECUTE 'TRUNCATE TABLE $1' USING target(t);

But I'm not sure it gives anything else than a hint about how to
implement the first idea.

> I think the invention of DO was a big step in the right direction,
> because before that if you wanted procedural logic in your script, you
> had to create a function, call it, and then drop the function. That

Yes, that's the sentence that got me to think about the above proposal,
because we are already talking about implementing WITH FUNCTION in
another thread, to answer some of Pavel's needs.

> my view the goal ought to be to refine that mechanism to remove the
> clunkiness and awkwardness, rather than to invent something completely
> new.

So, what do you think? Smells like empowered SQL this time, right?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Mon, Oct 15, 2012 at 3:14 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> What about continuing to extend on that incredibly useful WITH syntax we
> already have:
>
> WITH target AS (
> SELECT oid::regclass AS t
> FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
> WHERE pg_table_is_visible(oid)
> AND nspname = 'public' AND NOT relname ~ 'exclude-pattern'
> )
> TRUNCATE TABLE t FROM target;

I'm not exactly sure what that is supposed to do, but it doesn't seem
like an easy-to-use substitute for truncate-if-exists...

>> my view the goal ought to be to refine that mechanism to remove the
>> clunkiness and awkwardness, rather than to invent something completely
>> new.
>
> So, what do you think? Smells like empowered SQL this time, right?

I like the idea of making our SQL dialect capable of working with DDL
in more powerful ways; I'm not sold on the concrete proposal.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> WITH target AS (
>> TRUNCATE TABLE t FROM target;
>
> I'm not exactly sure what that is supposed to do, but it doesn't seem
> like an easy-to-use substitute for truncate-if-exists...

Indeed. I'm still a supporter of truncate-if-exists. Still, we're also
talking about a more flexible and powerful design, it seems to me.

> I like the idea of making our SQL dialect capable of working with DDL
> in more powerful ways; I'm not sold on the concrete proposal.

Cool,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Mon, Oct 15, 2012 at 3:14 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> if (select 1 from pg_class where relname = 'foo' and
>>> pg_table_is_visible(oid)) then
>>> truncate table foo;
>>> end if;
>>
>> Yeah, I think the functionality that we need is pretty much there
>> already today. What we need to do is to get the syntax to a point
>> where people can write the code they want to write without getting
>> tangled up by it.
>
> What about continuing to extend on that incredibly useful WITH syntax we
> already have:
>
> WITH target AS (
> SELECT oid::regclass AS t
> FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
> WHERE pg_table_is_visible(oid)
> AND nspname = 'public' AND NOT relname ~ 'exclude-pattern'
> )
> TRUNCATE TABLE t FROM target;

This still seems to be trying rather too hard.

The original suggestion was that, given the original query:

truncate table public.foo;

that we add syntax to make the request optional:

truncate table if exists public.foo;

Throwing in $$, oid, pg_class, joins, and such all seem like way more
syntax than we started with.

There are only so many 'clean' ways to modify the truncate request:

a) We could augment TRUNCATE with an "IF EXISTS" modifier, as
described in the initial patch.

b) Perhaps the IF EXIST might fit well afterwards, or be reversed somehow.

truncate table unless not exists public.foo;
truncate table public.foo if exists;
truncate table where exists public.foo;

c) My proposal was to add in a more generic modifier that wouldn't be
specific to TRUNCATE.

Thus:

truncate table public.foo if exists table public.foo;

That's a *little* longer than what's in b), but this would allow
extending the conditional to any kind of statement, which seems like a
more powerful idea to me. It would also support doing other actions
on the same conditional basis:

insert into bar (select id, name from public.foo)
if exists table public.foo;

If you want a more "prefix-y" version, well, here's how it might look
using a leading WITH clause:

with exists table public.foo
truncate public.foo;

with exists table public.foo
insert into bar (select id, name from public.foo);

I don't terribly much like that. I think I'd rather use WHEN than WITH.

when exists table public.foo
truncate public.foo;

when exists table public.foo
insert into bar (select id, name from public.foo);

That does seem a bit nicer than the { STATEMENT } if (conditional)
idea. And nary a $$, oid, or pg_class to be seen.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"