perl upgrade scripts make DO check more robust to handle named $something$

[robe]

So in trying to fix up #3683, I'm forced to move the postgis brin stuff into a separate file because

1) Since there is not create if not exists for operator and operator family, the only way to future version proof this is to check if those operator / families are in the catalog and only run those parts if they are not in the users catalog.

2) Our perl scripts, if I include the DO right in there, mangles with my DO so I still end up with those operator stuff being filtered out if they have an availability tag on top.

At anyrate I think it's dangerous for it to be touching DO constructs at all. Not quite sure what logic to put in there to prevent that.

So far it hasn't been a non-issue since all our DO's happen in before or after.sql files that are tacked on later and never pass thru the perl scripts.

[strk]

It looks like there IS code in postgis_proc_upgrade.pl to not mess with DO, here it is:

  if ( /^do *language .*\$\$/i )
  {
    print;
    while(<INPUT>)
    {
      print;
      last if /\$\$/;
    }
  }

Are you saying it's not working as expected ?

[robe]

I'll double check, but I tried putting the whole script in do, and it stripped out all the operators in my do wrapper which is what it did when it wasn't wrapped in a do.

[strk]

You can see the regexps are very simple, so care is needed to make the code match them (or they need be improved)

[robe]

yap that was it, if keep DO $$ but do $func$ internal it skips over it as a it. Now I'm having a different problem with the uninstall piece, presumably because it maybe is skipping DO's as well or something and missing things being created there.

I may need to just fudge that part. What's so annoying is if we were just using extensions, we wouldn't need to f.. with an uninstall script, since using drop postgis extension works fine.

[strk]

But then we'd be unable to test-before-install. Is any new PostgreSQL version supporting that yet ? Our least-required-version of PostgreSQL must support that before we can even think of dropping the script-based install method. (although there might be other limits, like need to have access on the server, which script-based is still good with)

[strk]

Regexp for similar thing is really not easy. Rather, can we think of a proper way to document how to write our SQL in a way to be nice with scripts ?

Things like: -- Availability / Changed comments, recommendation no to change signatures, how to deal with upgrades etc..

[robe]

Yah I was thinking that. I got more questions asked about that at last code sprint than anything else.

[robe]

We've changed the upgrade stuff so much I'm not sure my complaint is even valid anymore.