Thread: 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
Attachment
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
Attachment
<div class="gmail_quote">On Tue, Oct 9, 2012 at 11:09 AM, Simon Riggs <span dir="ltr"><<a href="mailto:simon@2ndquadrant.com"target="_blank">simon@2ndquadrant.com</a>></span> wrote:<br /><blockquote class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> Anyone want to check for any othermissing IF EXISTS capability in other DDL? <br /></blockquote></div><br />Yes, DEALLOCATE.<br />
On Tue, Oct 9, 2012 at 11:51 AM, Vik Reykja <vikreykja@gmail.com> wrote:
Patch attached.
Yes, DEALLOCATE.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?
Patch attached.
Attachment
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
Sébastien Lardière <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
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]. -- 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@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
On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > 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. 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
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>For starters, the use-case hasn't been explained to my satisfaction.<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> In whatsituation is it actually helpful to TRUNCATE a table that's<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> not there yet? Aren't yougoing to have to do a CREATE IF NOT EXISTS<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> to keep from failing later in the script? Ifso, why not just do that<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> first?<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">There is a usecase for the truncate 'mutliple' tables, maybe less clear for a single table.<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Sébastien willspeak here I suppose.<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;-qt-block-indent:0; text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> Second, to my mind the point of a multi-tableTRUNCATE is to ensure that<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> all the referenced tables get reset to empty *together*. Withsomething<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;-qt-user-state:0;">> like this, you'd have no such guarantee. Consider a timeline like this:<p style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">><p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> Session 1 Session 2<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>TRUNCATE IF EXISTS a, b, c;<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> ... finds c doesn't exist ...<p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>... working on a and b ...<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> CREATE TABLE c ( ... );<p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>INSERT INTO c ...;<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> ... commits ...<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>Now we have a, b, and c, but c isn't empty, violating the expectations<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> of session1. So even if there's a use-case for IF EXISTS on a single<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> table, I think it's very verydubious to allow it in multi-table<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> commands.<p style="-qt-paragraph-type:empty; margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">well,in such case you probably don't want to use IF EXISTS.<p style="-qt-paragraph-type:empty; margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">--<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;">Cédric Villemain +33 (0)6 20 30 22 52<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">http://2ndQuadrant.fr/<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">PostgreSQL: Support 24x7 - Développement, Expertiseet Formation
On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs <simon@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 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 thinkwe'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@gmail.com> wrote:
> On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs <simon@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 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@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 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
Andrew Dunstan <andrew@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 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@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 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@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 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@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 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@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 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@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 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@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, 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@2ndquadrant.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@gmail.com> wrote:
> On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs <simon@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 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@gmail.com> wrote:
>> On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs <simon@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 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@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 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@2ndquadrant.fr> wrote:
> Christopher Browne <cbbrowne@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 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 beingWHEN 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@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 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@2ndquadrant.fr> wrote:
> Christopher Browne <cbbrowne@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 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@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 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@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 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@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 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 proposalbut 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@gmail.com> wrote: > On Mon, Oct 15, 2012 at 10:34 AM, Greg Stark <stark@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 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 Perldatabase 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@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 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@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 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 FROMtarget; 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@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 > 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@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 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@2ndquadrant.fr> wrote:
> Robert Haas <robertmhaas@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 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?"
On Mon, 15 Oct 2012 10:21:18 -0700, Robert Haas <robertmhaas@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.
>
> I think the invention of DO was a big step in the right direction
> ...
> 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.
As someone who has worked with a number of databases now, none of them really get this DDL integration completely
right. What I'd like to see is 1) a predicate to easily test things about the schema (does this table, column, index,
schema,etc. exist? does it have the right type?) and 2) a way to conditionally execute DDL (and DML, which should fall
rightout, but it isn't really what this discussion is covering). I would propose extending the current EXISTS / NOT
EXISTSpredicate as follows:
[NOT] EXISTS TABLE tab [ ( col [type] [ , col [type]]... ) ]
[NOT] EXISTS COLUMN tab.col [type]
[NOT] EXISTS INDEX tab.idx [ ( col [ , col]... ) ]
[NOT] EXISTS CONSTRAINT tab.cstrt -- not sure what else might be useful here
[NOT] EXISTS ( select ) -- this is the current EXISTS predicate, of course
[NOT] EXISTS ANY ROWS FROM tab [join] [WHERE predicate] [GROUP BY col [ , col]...] -- exactly the same as
-- (select 1 FROM etc.)
-- only because I like
-- it better
(the latter [which by no means am I nuts over; it's just that when extending EXISTS I can't stop neatening it up to my
personalpreferences] could be extended with [NOT] EXISTS MORE THAN n ROWS FROM and [NOT] EXISTS EXACTLY n ROWS FROM.)
There is a new SQL statement: IF predicate true-statement [ELSE false-statement].
To actually execute this new IF statement, the executor would need an IF node that evaluates the predicate (with ANDs
andORs, just like all SQL predicates) and then executes the rest only if the predicate came out TRUE (or NOT FALSE; I
forgetwhich is usually used, and the difference with NULL could be useful, as long as it matches other predicates).
Thismoves one more bit of procedural logic into the executor.
Another wrinkle is that the dependent statement might not compile, due to missing tables or whatnot. Actually
executingit while it doesn't compile is an error, but we want to defer that error until we actually decide we need to
executeit. Also, it's probably good to try compiling it again at that point. So my thought would be to try planning
thedependent statement(s); if they compile, hook them to the IF node; if not, hook a DEFERRED node to the IF node. The
DEFERREDnode has the parse tree (or raw string, whatever makes sense) of the statement; on execution it tries again to
planthat statement; if it succeeds, run it; if not, error out.
I'd also add a SEQUENCE node to the executor. It just runs its children in order (could be n-ary, or if fixed arity
nodesare what is in the planner/executor today, could be binary, first left, then right, and right could be another
SEQUENCE). The DEFERRED node means that a CREATE statement could precede use of what is created in the same sequence
andall could get planned (with some deferral to execution time) in advance and run in one lump. This implements DO at
theexecutor level.
The biggest concepts left from plpgsql are looping and variables. Most variables could be modeled as a single row
value;SQL already can update a row, so the planning of assignments and calculations of scalars (and arrays, I think)
alreadyfits into things the planner knows about. Table variables (which I don't know that plpgsql supports, but
somedayit should) are less defined. Adding plpgsql's loops to the executor would let whole functions run under one
tripthrough the executor. This is beyond just improving the DDL support for scripts.
I have written a number of database upgrade scripts. Over time we've made them less fragile, by checking for the
existenceof tables, indexes, and most recently, columns. The usual sequence is:
1) check the existence of an index; check that the first few columns are correct; if not, drop the index
2) repeat for other indexes that have changed definition over time
3) check the existence of the table; create with current layout if it is missing
4) check for the presence of a column; if missing, alter the table to add it (of course, we can only add new columns at
theend, and occasionally delete a column)
5) repeat for more columns
6) check the existence of an index; if missing, create it
7) repeat for all the indexes
This is doable in most databases, but pretty messy. You need to join with infoschema tables, or system tables, or use
clunkyfunctions to check for existence; checking types is usually pretty horrid. Consequently, we only check a few
thingsand trust that the schema is only in a few different states. A true schema comparator and upgrade solver would
begreat, but I don't know anyone who has written such a thing. The extended EXISTS predicate that could check tables
andindexes would declutter a lot of our upgrade scripts. That's the use case for me.
Much as I'd like to, I'm not volunteering to write this. And I'm not volunteering anyone else either, or demanding
anything,or requesting or pleading. I did want to write it down and send it along just to clarify my thoughts. And if
somedayI get time, maybe I can learn enough internals to write a patch. But that day isn't today....
This message and any attachments are intended only for the use of the addressee and may contain information that is
privilegedand confidential. If the reader of the message is not the intended recipient or an authorized representative
ofthe intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited.
Ifyou have received this communication in error, please notify us immediately by e-mail and delete the message and any
attachmentsfrom your system.
On Tue, Oct 16, 2012 at 2:12 PM, Stafford, David x78061 <David.Stafford@broadridge.com> wrote: > On Mon, 15 Oct 2012 10:21:18 -0700, Robert Haas <robertmhaas@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. >> >> I think the invention of DO was a big step in the right direction >> ... >> 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. > > As someone who has worked with a number of databases now, none of them really get this DDL integration completely right. What I'd like to see is 1) a predicate to easily test things about the schema (does this table, column, index, schema,etc. exist? does it have the right type?) and 2) a way to conditionally execute DDL (and DML, which should fall rightout, but it isn't really what this discussion is covering). I would propose extending the current EXISTS / NOT EXISTSpredicate as follows: > > [NOT] EXISTS TABLE tab [ ( col [type] [ , col [type]]... ) ] > [NOT] EXISTS COLUMN tab.col [type] > [NOT] EXISTS INDEX tab.idx [ ( col [ , col]... ) ] > [NOT] EXISTS CONSTRAINT tab.cstrt -- not sure what else might be useful here > [NOT] EXISTS ( select ) -- this is the current EXISTS predicate, of course > [NOT] EXISTS ANY ROWS FROM tab [join] [WHERE predicate] [GROUP BY col [ , col]...] -- exactly the same as > -- (select 1 FROM etc.) > -- only because I like > -- it better > (the latter [which by no means am I nuts over; it's just that when extending EXISTS I can't stop neatening it up to mypersonal preferences] could be extended with [NOT] EXISTS MORE THAN n ROWS FROM and [NOT] EXISTS EXACTLY n ROWS FROM.) > > There is a new SQL statement: IF predicate true-statement [ELSE false-statement]. > > To actually execute this new IF statement, the executor would need an IF node that evaluates the predicate (with ANDs andORs, just like all SQL predicates) and then executes the rest only if the predicate came out TRUE (or NOT FALSE; I forgetwhich is usually used, and the difference with NULL could be useful, as long as it matches other predicates). Thismoves one more bit of procedural logic into the executor. > > Another wrinkle is that the dependent statement might not compile, due to missing tables or whatnot. Actually executingit while it doesn't compile is an error, but we want to defer that error until we actually decide we need to executeit. Also, it's probably good to try compiling it again at that point. So my thought would be to try planning thedependent statement(s); if they compile, hook them to the IF node; if not, hook a DEFERRED node to the IF node. The DEFERREDnode has the parse tree (or raw string, whatever makes sense) of the statement; on execution it tries again to planthat statement; if it succeeds, run it; if not, error out. > > I'd also add a SEQUENCE node to the executor. It just runs its children in order (could be n-ary, or if fixed arity nodesare what is in the planner/executor today, could be binary, first left, then right, and right could be another SEQUENCE). The DEFERRED node means that a CREATE statement could precede use of what is created in the same sequence andall could get planned (with some deferral to execution time) in advance and run in one lump. This implements DO at theexecutor level. > > The biggest concepts left from plpgsql are looping and variables. Most variables could be modeled as a single row value;SQL already can update a row, so the planning of assignments and calculations of scalars (and arrays, I think) alreadyfits into things the planner knows about. Table variables (which I don't know that plpgsql supports, but somedayit should) are less defined. Adding plpgsql's loops to the executor would let whole functions run under one tripthrough the executor. This is beyond just improving the DDL support for scripts. > > I have written a number of database upgrade scripts. Over time we've made them less fragile, by checking for the existenceof tables, indexes, and most recently, columns. The usual sequence is: > 1) check the existence of an index; check that the first few columns are correct; if not, drop the index > 2) repeat for other indexes that have changed definition over time > 3) check the existence of the table; create with current layout if it is missing > 4) check for the presence of a column; if missing, alter the table to add it (of course, we can only add new columns atthe end, and occasionally delete a column) > 5) repeat for more columns > 6) check the existence of an index; if missing, create it > 7) repeat for all the indexes > This is doable in most databases, but pretty messy. You need to join with infoschema tables, or system tables, or useclunky functions to check for existence; checking types is usually pretty horrid. Consequently, we only check a few thingsand trust that the schema is only in a few different states. A true schema comparator and upgrade solver would begreat, but I don't know anyone who has written such a thing. The extended EXISTS predicate that could check tables andindexes would declutter a lot of our upgrade scripts. That's the use case for me. I agree. I think something like this would be great. But figuring out how to make it happen is, of course, the trick. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company