On Tue, Oct 16, 2012 at 2:12 PM, Stafford, David x78061
wrote:
> On Mon, 15 Oct 2012 10:21:18 -0700, Robert Haas wrote:
>> 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 the
On Mon, 15 Oct 2012 10:21:18 -0700, Robert Haas wrote:
> 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
On Mon, Oct 15, 2012 at 3:14 PM, Dimitri Fontaine
wrote:
> Robert Haas 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 m
Robert Haas 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
On Mon, Oct 15, 2012 at 3:14 PM, Dimitri Fontaine
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_ta
Robert Haas 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 synta
On Mon, Oct 15, 2012 at 12:53 PM, Christopher Browne 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
On Mon, Oct 15, 2012 at 11:57 AM, Robert Haas wrote:
> On Mon, Oct 15, 2012 at 10:34 AM, Greg Stark 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/pgsq
On Mon, Oct 15, 2012 at 10:34 AM, Greg Stark 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 se
On 10/15/2012 04:34 PM, Greg Stark wrote:
On Mon, Oct 15, 2012 at 3:26 PM, Robert Haas 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 eve
On Mon, Oct 15, 2012 at 3:26 PM, Robert Haas 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
On Fri, Oct 12, 2012 at 5:52 PM, Dimitri Fontaine
wrote:
> Christopher Browne 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. M
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 i
On Tue, Oct 9, 2012 at 9:04 PM, Robert Haas 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 EX
On Fri, Oct 12, 2012 at 5:52 PM, Dimitri Fontaine
wrote:
> Christopher Browne 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. M
>> 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
Christopher Browne 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 cha
On 10/12/2012 11:05 PM, Christopher Browne wrote:
On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas wrote:
On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs 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
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
--
Se
On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas wrote:
> On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs 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')
On Fri, Oct 12, 2012 at 3:23 PM, Alvaro Herrera
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
Alvaro Herrera 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
di
* Josh Berkus (j...@agliodbs.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
>
Robert Haas escribió:
> On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs 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
* Tom Lane (t...@sss.pgh.pa.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
* Robert Haas (robertmh...@gmail.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 O
On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs 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
Andrew Dunstan 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
Hello
2012/10/12 Sébastien Lardière :
> 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 o
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 m
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 tha
On 11 October 2012 19:59, Robert Haas wrote:
> On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs 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 eve
On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs 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
> 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?
The
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
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
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 INSE
On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs wrote:
> On 10 October 2012 02:10, Robert Haas wrote:
>> On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus wrote:
>>> The second is for making deployment scripts idempotent. For example,
>>> say you have script A which creates table "josh", and script B wh
On 10 October 2012 02:10, Robert Haas wrote:
> On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus 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
On Tue, Oct 09, 2012 at 09:10:13PM -0400, Robert Haas wrote:
> On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus 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, i
Robert Haas writes:
> On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus 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
>> ti
On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus 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 fe
On Tue, Oct 9, 2012 at 2:04 PM, Simon Riggs wrote:
> On 9 October 2012 21: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,
On 9 October 2012 21: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
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 EXI
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?
--
Sent via pgsql-hackers mail
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
On 9 October 2012 15:06, Tom Lane wrote:
> Simon Riggs writes:
>> On 9 October 2012 09:33, Sébastien Lardière 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 in
On Tue, Oct 9, 2012 at 12:28 PM, Sébastien Lardière
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
On 10/09/2012 04:06 PM, Tom Lane wrote:
> Simon Riggs writes:
>> On 9 October 2012 09:33, Sébastien Lardière 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 invi
=?ISO-8859-1?Q?S=E9bastien_Lardi=E8re?= 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 th
Simon Riggs writes:
> On 9 October 2012 09:33, Sébastien Lardière 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
On Tue, Oct 9, 2012 at 11:51 AM, Vik Reykja wrote:
> On Tue, Oct 9, 2012 at 11:09 AM, Simon Riggs wrote:
>
>> Anyone want to check for any other missing IF EXISTS capability in other
>> DDL?
>>
>
> Yes, DEALLOCATE.
>
Patch attached.
deallocate_if_exists.patch
Description: Binary data
--
Sent
On Tue, Oct 9, 2012 at 11:09 AM, Simon Riggs wrote:
> Anyone want to check for any other missing IF EXISTS capability in other
> DDL?
>
Yes, DEALLOCATE.
On 10/09/2012 11:09 AM, Simon Riggs wrote:
> On 9 October 2012 09:33, Sébastien Lardière 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.
>>
>> Thi
On 9 October 2012 09:33, Sébastien Lardière 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
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 visibil
57 matches
Mail list logo
