[postgis-tickets] [PostGIS] #4405: Index over ST_Transform(constant, SRID) breaks pg_upgrade

[PostGIS]

#4405: Index over ST_Transform(constant, SRID) breaks pg_upgrade
------------------------------------+----------------------------
  Reporter:  Algunenano             |      Owner:  Algunenano
      Type:  defect                 |     Status:  assigned
  Priority:  medium                 |  Milestone:  PostGIS 2.3.10
 Component:  build/upgrade/install  |    Version:  2.3.x
Resolution:                         |   Keywords:
------------------------------------+----------------------------

Comment (by Algunenano):

 Speculation over IRC:
 {{{
 [19:07] <robe2> I guess maybe the index tries to evaluate the constant
 even though the table is empty
 [19:08] <Algunenano> That's also what I think
 [19:08] <robe2> though still I thought pg_upgrade would load all the data
 first before applying the index, so still doesn't make sense to me
 [19:08] <Algunenano> As far as I can see, it first loads the schemas and
 then the data
 [19:08] <Algunenano> I need to investigate further
 [19:08] <robe2> and the index after?
 [19:09] <Algunenano> Schema + bare extension + Index; then data
 [19:10] <Algunenano> It loads postgis doing something like: `SELECT
 pg_catalog.binary_upgrade_create_empty_extension('postgis', 'public',
 false, '2.4.7', '{19728}', '{"WHERE NOT (`
 [19:10] <Algunenano> It doesn't do a CREATE EXTENSION
 [19:15] <robe2> Algunenano: yah it adds each bit directly to the extension
 [19:15] <Algunenano> And it doesn't add the data to the srid table as far
 as I can tell
 [19:16] <Algunenano> I haven't figured out yet when it's added
 [19:16] <robe2> I asked Tom about that once and he said to preserve the
 existing extension stuff
 [19:16] <robe2> so it does a naked extension and then adds the bits to it
 [19:17] <robe2> but hmm in a regular extension install the data would be
 loaded as part of the create extension step
 [19:17] <robe2> it might treat the actual data in the table like any other
 I suppose which would cause an order dependency
 [19:18] <robe2> but still I think indexes get created at the end so should
 not be an issue
 [19:18] <robe2> unless if that assumption is wrong
 [19:20] <Algunenano> They seem to be created at the end, but before adding
 data
 [19:20] <robe2> the indexes?
 [19:20] <Algunenano> At least I don't see any data insertion in the dump
 created by pg_upgrade
 [19:20] <Algunenano> Yes
 [19:20] <robe2> hmm
 [19:20] <Algunenano> The dump done by pg_upgrade is `--schema-only`
 [19:21] <robe2> okay guess that explains it
 [19:21] <robe2> Algunenano: well technically we are breaking the contract
 of immutability
 [19:22] <robe2> because the index depends on a function that is not
 immutable that we lied and said was
 [19:22] <Algunenano> "I didn't sign no contract", but yes we are
 [19:23] <robe2> Algunenano: but you are saying your spatial_ref_sys table
 then never loads and pg_upgrade gives no error?
 [19:23] <-- jmarsac (~jmarsac at lfbn-nic-1-72-223.w2-15.abo.wanadoo.fr) ha
 dejado este servidor (Ping timeout: 272 seconds).
 [19:23] <-- kapilp (uid36151 at gateway/web/irccloud.com/x-boohfisijcwgqwib)
 ha dejado este servidor (Quit: Connection closed for inactivity).
 [19:24] <Algunenano> No, pg_upgrade does error and stops loading. But
 since it doesn't clean up after that I can get in and see that there isn't
 any data in spatial_sys_ref
 [19:24] <robe2> ah okay that all makes sense now
 [19:24] <robe2> but ST_Transform works
 [19:24] <robe2> I wonder if it does load the user data first
 [19:25] <Algunenano> I'd say so
 [19:25] <robe2> but since you have a constant calls the function
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4405#comment:1>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.