Skip site navigation (1) Skip section navigation (2)

Re: Truncate if exists

From: Sébastien Lardière <slardiere(at)hi-media(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Simon Riggs <simon(at)2ndQuadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org, cedric(at)2ndquadrant(dot)fr
Subject: Re: Truncate if exists
Date: 2012-10-09 16:28:08
Message-ID: 50745098.4090500@hi-media.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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



In response to

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2012-10-09 17:01:56
Subject: Re: Switching timeline over streaming replication
Previous:From: Greg StarkDate: 2012-10-09 15:44:44
Subject: Re: TODO item: teach pg_dump about sparsely-stored large objects

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group