On Tue, Oct 16, 2012 at 2:12 PM, Stafford, David x78061
david.staff...@broadridge.com wrote:
On Mon, 15 Oct 2012 10:21:18 -0700, Robert Haas robertmh...@gmail.com 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
On Mon, 15 Oct 2012 10:21:18 -0700, Robert Haas robertmh...@gmail.com 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.
On Fri, Oct 12, 2012 at 5:52 PM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
Christopher Browne cbbro...@gmail.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;
On Mon, Oct 15, 2012 at 3:26 PM, Robert Haas robertmh...@gmail.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
On 10/15/2012 04:34 PM, Greg Stark wrote:
On Mon, Oct 15, 2012 at 3:26 PM, Robert Haas robertmh...@gmail.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
On Mon, Oct 15, 2012 at 10:34 AM, Greg Stark st...@mit.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
On Mon, Oct 15, 2012 at 11:57 AM, Robert Haas robertmh...@gmail.com wrote:
On Mon, Oct 15, 2012 at 10:34 AM, Greg Stark st...@mit.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
On Mon, Oct 15, 2012 at 12:53 PM, Christopher Browne cbbro...@gmail.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
Robert Haas robertmh...@gmail.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
On Mon, Oct 15, 2012 at 3:14 PM, Dimitri Fontaine
dimi...@2ndquadrant.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
Robert Haas robertmh...@gmail.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
On Mon, Oct 15, 2012 at 3:14 PM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
Robert Haas robertmh...@gmail.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
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:
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
Hello
2012/10/12 Sébastien Lardière slardi...@hi-media.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
Andrew Dunstan and...@dunslane.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
On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs si...@2ndquadrant.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
* 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 OR
* 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 escribió:
On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs si...@2ndquadrant.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
* 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
stopping
Alvaro Herrera alvhe...@2ndquadrant.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,
On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas robertmh...@gmail.com wrote:
On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs si...@2ndquadrant.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
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
--
Sent
On 10/12/2012 11:05 PM, Christopher Browne wrote:
On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas robertmh...@gmail.com wrote:
On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs si...@2ndquadrant.com wrote:
So we just need a function called pg_if_table_exists(table, SQL) which
wraps a test in a
Christopher Browne cbbro...@gmail.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
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
On Fri, Oct 12, 2012 at 5:52 PM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
Christopher Browne cbbro...@gmail.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;
On Tue, Oct 9, 2012 at 9:04 PM, Robert Haas robertmh...@gmail.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
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
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/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
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
On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs si...@2ndquadrant.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
On 11 October 2012 19:59, Robert Haas robertmh...@gmail.com wrote:
On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs si...@2ndquadrant.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
On 10 October 2012 02:10, Robert Haas robertmh...@gmail.com wrote:
On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus j...@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
On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs si...@2ndquadrant.com wrote:
On 10 October 2012 02:10, Robert Haas robertmh...@gmail.com wrote:
On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus j...@agliodbs.com wrote:
The second is for making deployment scripts idempotent. For example,
say you have
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
On 9 October 2012 09:33, Sébastien Lardière slardi...@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
On 10/09/2012 11:09 AM, Simon Riggs wrote:
On 9 October 2012 09:33, Sébastien Lardière slardi...@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
On Tue, Oct 9, 2012 at 11:09 AM, Simon Riggs si...@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 vikrey...@gmail.com wrote:
On Tue, Oct 9, 2012 at 11:09 AM, Simon Riggs si...@2ndquadrant.comwrote:
Anyone want to check for any other missing IF EXISTS capability in other
DDL?
Yes, DEALLOCATE.
Patch attached.
deallocate_if_exists.patch
Simon Riggs si...@2ndquadrant.com writes:
On 9 October 2012 09:33, Sébastien Lardière slardi...@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
=?ISO-8859-1?Q?S=E9bastien_Lardi=E8re?= slardi...@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
On 10/09/2012 04:06 PM, Tom Lane wrote:
Simon Riggs si...@2ndquadrant.com writes:
On 9 October 2012 09:33, Sébastien Lardière slardi...@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
On Tue, Oct 9, 2012 at 12:28 PM, Sébastien Lardière
slardi...@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
On 9 October 2012 15:06, Tom Lane t...@sss.pgh.pa.us wrote:
Simon Riggs si...@2ndquadrant.com writes:
On 9 October 2012 09:33, Sébastien Lardière slardi...@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
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 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
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
On 9 October 2012 21:35, Peter Eisentraut pete...@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
On Tue, Oct 9, 2012 at 2:04 PM, Simon Riggs si...@2ndquadrant.com wrote:
On 9 October 2012 21:35, Peter Eisentraut pete...@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
On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus j...@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
53 matches
Mail list logo
