Re: postgis after pg_upgrade
Hi, This will not resolve the issue I have because extension is already to the latest version, but it is using postgres 9.4 and it should use 9.6. Regards, Slavcho On Fri, Nov 30, 2018 at 6:01 PM Paul Ramsey wrote: > If you install the new version, and then use ‘ALTER EXTENSION UPDATE’ to > update the SQL-side bindings, everything should improve. > > On Nov 30, 2018, at 5:11 AM, Slavcho Trnkovski > wrote: > > Hi, > > I have postgresql 9.4 with postgis extension installed (latest version, > 2.4.5). I upgraded postgresql from 9.4 to 9.6. > After upgrading to 9.6 I get the following result > from PostGIS_full_version() > select PostGIS_full_version(); > > postgis_full_version > > --- > POSTGIS="2.4.5 r16765" *PGSQL="94" (procs need upgrade for use with > "96") *GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August > 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" > RASTER > (1 row) > > Is there any way to resolve this besides recreating the extension? > > Regards, > Slavcho > > >
Re: psql is hanging
I like the "add an analyze" idea; the two most likely causes of the phenomenon (to my mind) are either: a) Something's getting locked and Tom Lane's idea of checking pg_locks when you notice it's stopped can help track down the problem. Further to that, the thing I'd be expecting to see if the problem is locking is that the connection that's blocked up will be waiting on a lock held by another connection. It's near certain that you'll find that the connection doing the work will have LOTS of locks outstanding; that's not a problem at all; that's perfectly normal. You need to look keenly for locks that have not yet been granted. b) I find it common in my environments to need to do manual ANALYZE requests all the time because I'll set up temporary tables (that the autovacuum daemon can't do anything about) which, as the stats are lacking, will lead to awful query plans that make queries run badly. If you use temporary tables, that's like a heavy "thumb on the scale" that can lead to awful performance, unless those tables get an ANALYZE after getting populated. Unpredictable slowness can certainly result from tables having changed size leading to pathological query plans. ANALYZE will help.
Re: How to watch for schema changes
Hi, Adrian, Sorry for the delay to come back to this. I was busy doing other things. On Tue, Jul 3, 2018 at 12:32 PM Adrian Klaver wrote: > > On 07/03/2018 10:21 AM, Igor Korot wrote: > > Hi, ALL, > > Is there any trigger or some other means I can do on the server > > which will watch for CREATE/ALTER/DROP TABLE command and after successful > > execution of those will issue a NOTIFY statement? > > https://www.postgresql.org/docs/10/static/event-triggers.html So if I understand correctly, I should write the trigger for the event I am interested in. And in this trigger I write a little SQL that will write the DDL command in some temporary table. I'm just looking for a way to execute this trigger and a function from my C++ code on the connection (either ODBC or thru the libpq). And then in my C++ code I will continuously query this temporary table. Or there is a better alternative on the algorithm? Thank you. >> > > > Thank you. > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com
using a function in where
All, I am using the function below to convert a continuous variable to a binned value. Sometimes a value other than zero is passed through the query. For example -.5 result value is passed to the query result. The basic of the query is below. select incentivebin(wac, rate, .25) from my_table where incentivebin(was, rate, .25) = 0 I have checked the function works correctly and the raw values match those values expected from simple subtraction and are in the correct bin. I am not sure why some values would be allowed through the query. Any ideas would be appreciated. Best, Glenn CREATE or REPLACE FUNCTION embs_owner.IncentiveBin(IN "Gwac" double precision, IN "MtgRate" double precision, IN "BinSize" double precision) RETURNS double precision LANGUAGE 'sql' PARALLEL SAFE AS 'select ceiling(($1 - $2)/$3) *$3'; ALTER FUNCTION embs_owner.IncentiveBin(double precision, double precision, double precision) OWNER TO embs_owner;
Re: using a function in where
> "Glenn" == Glenn Schultz writes: Glenn> All, Glenn> I am using the function below to convert a continuous variable Glenn> to a binned value. Sometimes a value other than zero is passed Glenn> through the query. For example -.5 result value is passed to the Glenn> query result. The basic of the query is below. Glenn> select Glenn> incentivebin(wac, rate, .25) Glenn> from Glenn> my_table Glenn> where incentivebin(was, rate, .25) = 0 Those two calls to incentivebin() have different parameters - is that an error in the original query, or a typo in transcribing it to email? -- Andrew (irc:RhodiumToad)
