Truncate if exists

On 9 October 2012 09:33, Sébastien Lardière <slardiere@hi-media.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@hi-media.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@2ndquadrant.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@gmail.com> wrote:

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

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

Yes, DEALLOCATE.

Patch attached.

Simon Riggs <simon@2ndQuadrant.com> writes:

On 9 October 2012 09:33, S�bastien Lardi�re <slardiere@hi-media.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?

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@hi-media.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@2ndQuadrant.com> writes:

On 9 October 2012 09:33, Sébastien Lardière <slardiere@hi-media.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@hi-media.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@sss.pgh.pa.us> wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

On 9 October 2012 09:33, Sébastien Lardière <slardiere@hi-media.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@gmx.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@2ndquadrant.com> wrote:

On 9 October 2012 21:35, Peter Eisentraut <peter_e@gmx.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@agliodbs.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]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
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.
implementation work, if that's helpful. Or else somebody else can do
it. That's good, too.

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus <josh@agliodbs.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@agliodbs.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@gmail.com> wrote:

On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus <josh@agliodbs.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