Re: Slow statement using parallelism after 9.6>11 upgrade

[Paul Ramsey]

> On Sep 3, 2019, at 7:06 AM, Arnaud L.  wrote:
> 
> Le 03/09/2019 à 15:43, Tom Lane a écrit :
>> "Arnaud L."  writes:
>>> We have upgraded our database from 9.6 to 11 (and updated PostGIS from 2.3 
>>> to 2.5 as well).
>>> ...
>> Have you re-ANALYZEd the database?  The problem with this query
>> seems to be the spectacularly awful rowcount estimate here:
> 
> You mean after the upgrade process ? Yes I have.
> I've juste re-run "ANALYZE table" to rule this out, estimates are the same.
> 
> Maybe some statistic target problem ? Estimated number of rows is 284.196.352
> Also, this is a GIN index on a bigint[] column.
> 
> I've setup parallel_tuple_cost to 1.0 parallel_setup_cost to 5000.0 for the 
> time being which solves this specific problem. These value don't look very 
> sensible though, they are very high compared to the default ones.

You can also leave that setting unchanged and just change the behaviour on your 
one table:

ALTER TABLE nodes SET ( parallel_workers = 0);

P.

> 
> Cheers
> --
> Arnaud
> 
> 

Re: table name

[Paul Ramsey]

ALTER TABLE "regions-20180101" rename to regions_20180101;


> On Jun 11, 2020, at 11:54 AM, Marc Millas  wrote:
> 
> sorry if my question is tooo simple :-)
> 
> I got a shapefile from the french gov.
> I import it with postgis 3.01 utility.
> fine !
> the table created by this utility is named regions-20180101
> with the dash in the middle.
> I see that table name in pg_class, and, also, in the geometry_columns view.
> 
> 
> obviously if I ask:
> select * from regions-20180101;
> I get a syntax error.
> if I try select * from $$regions_20180101$$;
> I get another syntax error.
> If I try to rename that table, same thing.
> if I try a cte, same thing.
> 
> What should I do ?
> 
> thanks,
>  
> 
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
> 

Re: pg_upgrade of 11 -> 13: free(): invalid pointer

[Paul Ramsey]

> On Nov 17, 2020, at 11:44 AM, Bruce Momjian  wrote:
> 
> On Tue, Nov 17, 2020 at 11:59:10AM -0500, Jeremy Wilson wrote:
>> pg_restore: WARNING:  terminating connection because of crash of another 
>> server process
>> DETAIL:  The postmaster has commanded this server process to roll back the 
>> current transaction and exit, because another server process exited 
>> abnormally and possibly corrupted shared memory.
>> HINT:  In a moment you should be able to reconnect to the database and 
>> repeat your command.
>> pg_restore: creating COMMENT "public.FUNCTION "st_isempty"("rast" 
>> "public"."raster")"
>> pg_restore: while PROCESSING TOC:
>> pg_restore: from TOC entry 5338; 0 0 COMMENT FUNCTION "st_isempty"("rast" 
>> "public"."raster") postgres
>> pg_restore: error: could not execute query: server closed the connection 
>> unexpectedly
>>This probably means the server terminated abnormally
>>before or while processing the request.
>> Command was: COMMENT ON FUNCTION "public"."st_isempty"("rast" 
>> "public"."raster") IS 'args: rast - Returns true if the raster is empty 
>> (width = 0 and height = 0). Otherwise, returns false.’;
> 
> My guess is that this is a crash in the PostGIS shared library.  I would
> ask the PostGIS team if they know of any crash cases, and if not, I
> think you need to do a pg_dump of the database and test-load it into a
> new database to see what query makes it fail, and then load debug
> symbols and do a backtrace of the stack at the point of the crash. 
> Yeah, not fun.

These kinds of problems have been almost always due to multiple versions of 
dependencies installed simultaneously. So packaging fun. You'll get some 
version of postgis compiled against one train of dependencies and another 
against another train, and for upgrade both trains will end up installed 
simultaneously, and things will break. 
P

> 
> -- 
>  Bruce Momjian  https://momjian.us
>  EnterpriseDB https://enterprisedb.com
> 
>  The usefulness of a cup is in its emptiness, Bruce Lee
> 
> 
> 

Re: Cannot terminate backend

[Paul Ramsey]

Afraid so. You can wait longer, I guess. You may have found two bugs… the lack 
of an interrupt in the dbscan loop, which I’m working on now. And maybe an 
infinite looping case? In which case if you want a fix on that, you’ll have to 
share your data and query. 

P.

> On Mar 31, 2023, at 7:41 AM, Arnaud Lesauvage  wrote:
> 
>> On Fri, 2023-03-31 at 13:46 +, Arnaud Lesauvage wrote:
>>> I have a long running query that I seem unable to either cancel or
>> terminate.
>>> What could be the reason for this, and what is the bet way to terminate
>> this kind of query ?
>>> 
>>> The query is a CTE using postgis ST_ClusterDBSCAN function. The CTE
>> returns approximately 150k rows.
>>> The SQL is as follows :
>>> 
>>> EXPLAIN ANALYZE
>>> WITH subq AS (
>>> SELECT id, geom, ST_ClusterDBSCAN(geom, eps := 1000, minpoints
>>> := 1) OVER() AS cluster_id
>>> FROM mytable
>>> )
>>> SELECT cluster_id, count(id), ST_Collect(geom) FROM subq GROUP BY
>>> cluster_id;
>>> 
>>> pg_stat_activity show no wait event.
>>> pg_cancel_backend(mypid) returns true but the state does not change in
>> pg_stat_activity.
>>> pg_terminate_backend(mypid) yields the same result (as superuser)
>>> Pg_stat_activity show no wait_event.
>>> 
>>> SELECT version();
>>> PostgreSQL 14.5 (Ubuntu 14.5-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
>>> compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
>> 
>> The most likely explanation is that one of the PostGIS functions runs for a
>> long time without checking CHECK_FOR_INTERRUPTS().
>> That would be a PostGIS bug.  Try to construct a reproducible test case that
>> you can share!
>> 
>> Perhaps this trick can help:
>> https://www.cybertec-postgresql.com/en/cancel-hanging-postgresql-
>> query/
> 
> 
> Thanks Laurenz
> Unfortunately, I don't have a shell access to the server, so I guess I'll 
> have to ask to sysadmin to kill -9 ?
> 
> Regards
> Arnaud

Re: postgis after pg_upgrade

[Paul Ramsey]

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: Geographic coordinate values format conversion to DD (Decimal Degrees) format

[Paul Ramsey]

> On Dec 4, 2018, at 12:36 PM, Allan Kamau  > wrote:
> 
> Does PostgreSQL (more specifically PostGIS) have functions for these types of 
> conversions.
> 
> Below are examples of the geographic coordinates values I have coupled with 
> the resulting decimal degrees values.
> 39.529053 N 107.772406 W=39.5290530°, -107.7724060°
> 27.485973 S 153.190699 E=-27.4859730°, 153.1906990°
> 30°32’39” N, 91°07’36” E=30.5441667°, 091.127°
> 27.485973 S 153.190699 E=-27.4859730°, 153.1906990°
> 1¡20'1N 103¡45'15E=01.3336111°, 103.7541667°
SELECT st_astext(txt2geometry('S 20 10.8035165 W 176 36.074496'));
> =-20.1800586°, -176.6012416°
> 
> The "°" sign in the results is optional.


Nope, you’ve got a big ugly pattern matching problem there, unfortunately, and 
probably are going to have to regex your way out of the bag. PostGIS will help 
you output forms like that, but it doesn’t have any general handling of 
arbitrary DMS strings.

http://postgis.net/docs/manual-2.5/ST_AsLatLonText.html 


Here’s a PLPGSQL example that does half of your cases.

CREATE OR REPLACE FUNCTION txt2geometry(textcoord text)
RETURNS geometry AS 
$$
DECLARE 
textarr text[];
sep text;
lon float8;
lat float8;
BEGIN
textarr := regexp_matches(textcoord, '(\d+)(\D?)(\d{2})\D?([\d\.]+)\D? 
([NS]),? (\d+)\D?(\d{2})\D?(\d+)\D? ([EW])');
sep := textarr[2];
RAISE NOTICE '%', textarr;
-- DD.DD
IF sep = '.' THEN
lat := int4(textarr[1]) + int4(textarr[3]) / 100.0 + 
float8(textarr[4]) / pow(10, length(textarr[4])) / 100;
lon := int4(textarr[6]) + int4(textarr[7]) / 100.0 + 
float8(textarr[8]) / pow(10, length(textarr[8])) / 100;
-- DD.MM'SS"
ELSE
lat := int4(textarr[1]) + int4(textarr[3]) / 60.0 + 
float8(textarr[4]) / pow(10, length(textarr[4])) / 36;
lon := int4(textarr[6]) + int4(textarr[7]) / 60.0 + 
float8(textarr[8]) / pow(10, length(textarr[8])) / 36;
END IF;
IF textarr[5] = 'S' THEN
lat := -1 * lat;
END IF;
IF textarr[9] = 'W' THEN
lon := -1 * lon;
END IF;
RETURN ST_SetSRID(ST_MakePoint(lon, lat), 4326);
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100;

Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

[Paul Ramsey]

On Thu, Mar 14, 2019 at 8:43 AM Julien Rouhaud  wrote:
>
> On Thu, Mar 14, 2019 at 3:25 PM Adrian Klaver  
> wrote:
> >
> > On 3/14/19 6:14 AM, Julien Rouhaud wrote:
> > > On Thu, Mar 14, 2019 at 1:20 PM fuzk  wrote:
> > >>
> > >> Dear Adrian,
> > >>
> > >> My setting is as following.
> > >>
> > >> max_parallel_workers_per_gather=32
> > >>
> > >> I am looking forward to hearing from you.
> > >
> > > What version of postgres and what version of postgis are you using ?
> > >
> > >> At 2019-03-13 22:31:11, "Adrian Klaver"  
> > >> wrote:
> > >>> On 3/12/19 7:54 PM, fuzk wrote:
> >  postgres=# \set VERBOSITY verbose
> >  postgres=# SELECT
> >  round(cast(coalesce(sum(ST_length(geography(geometry)))/1000,0) as
> >  NUMERIC),4)||'KM' field_value from had_link;
> > 
> >  ERROR:  XX000: cannot update SecondarySnapshot during a parallel 
> >  operation
> >  CONTEXT:  SQL statement "SELECT proj4text FROM public.spatial_ref_sys
> >  WHERE srid = 4326 LIMIT 1"
> >  parallel worker
> >  LOCATION:  GetLatestSnapshot, snapmgr.c:387
> > >
> > > I'm not familiar at all with geography, but if I read the code
> > > correctly, ST_Length / geography_length will call
> > > spheroid_init_from_srid(), which eventuallly calls GetProj4StringSPI()
> > > that run a query using SPI.  I'm not sure why exactly
> > > GetLatestSnapshot() is called here, or if SPI is really allowed in
> > > parallel workers, but that's the problem here apparently.  I'm adding
> > > Paul as he'll probably have a way better answer than me.
> > >
> >
> > https://trac.osgeo.org/postgis/ticket/4129
> > "Cannot update SecondarySnapshot during a parallel operation"
>
> Ah, I didn't look there indeed, thanks!  So postgis people are already
> aware, that's a good news.

Aware but unable to replicate, so nothing is happening on that front.
If you can create a set of data, SQL statements and configuration that
replicates, that would aid immensely.

Re: Debugging leaking memory in Postgresql 13.2/Postgis 3.1

[Paul Ramsey]

> On Mar 31, 2021, at 11:24 AM, Tom Lane  wrote:
> 
> Stephan Knauss  writes:
>> Hello Tom, the output below looks similar to the OOM output you 
>> expected. Can you give a hint how to interpret the results?
> 
> Looks like the answer is that wherever the leak is, it's not accounted
> for by this info; none of those contexts are particularly large.
> 
> Based on nearby threads, it occurs to me to ask whether you have JIT
> enabled, and if so whether turning it off helps.  There seems to be
> a known leak of the code fragments generated by that in some cases.
> 
> If that's not it, then the leak must be accumulating through plain
> old malloc calls.  There's not much of that in the core backend
> (although if you use ispell text search dictionaries, maybe [1] is
> relevant), so my suspicions would next fall on any extensions you
> might be using.

Would be interested in the queries being run. We have a reproduceable leak in 
<-> geography operator that we have been unable to track down. 

P

> 
>   regards, tom lane
> 
> [1] 
> https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=eba939551
> 
> 

Re: index unique

[Paul Ramsey]

Primary key is going to be a BTREE index. I'm surprised you require the 
geometry in order to achieve uniqueness?
You can't put the geometry into a BTREE because it's too large. 
You could add a column and stick the MD5 hash of the geometry there, and use 
that as the last piece of uniqueness?
If the bbox of the geometry is "good enough" for your key, that kind of speaks 
to the idea that maybe your geometry doesn't need to be part of the PK? It's 
possible for different geometries to have the same bbox...
ATB,
P

> On Jun 3, 2021, at 1:51 PM, Marc Millas  wrote:
> 
> Hi,
> postgres 12 with postgis.
> on a table we need a primary key and to get a unique combinaison, we need 3 
> columns of that table:
> 1 of type integer,
> 1 of type text,
> 1 of type geometry
> 
> creating the PK constraint doesn work: (even with our current small data set)
> ERROR:  index row size 6072 exceeds btree version 4 maximum 2704 for index 
> "xxx_spkey"
> DETAIL:  Index row references tuple (32,1) in relation "xxx".
> HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
> Consider a function index of an MD5 hash of the value, or use full text 
> indexing.
> 
> ok. we can do this.
> but if so, we need to create a gist index on the geometry column to do any 
> topology request.
> so 2 indexes containing this single column.
> 
> if we install extension btree_gist, no pb to create an index on all 3 columns.
> but as gist does not support unicity, this index cannot be used for the PK.
> 
> OK, we may try to use a function to get the bounding box around the geometry 
> objects and use the result into a btree index
> 
> Any idea (I mean: another idea !) to tackle this ?
> Or any critic on the "solution" ??
> 
> thanks,
> 
> 
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
> 

Re: proj_create errors in EDB 13.5 installation macOS

[Paul Ramsey]

> On Jan 21, 2022, at 4:45 AM, Moen, Paul T.  wrote:
> 
> I am seeing the following errors in my PostgreSQL log files and wonder if 
> this is a problem with EDB's installation. 

Yes, missing the proj.db would certainly make proj unhappy. Search your system 
first and see if the file is actually there. Maybe it's there but not being 
found, in which case some extra environment variables could make your install 
turn over. But EBD perhaps needs to make sure that it's both (a) included and 
(b) found on startup.

P

SearchSysCache() tutorial?

[Paul Ramsey]

Is there anywhere any documentation on SearchSysCache? I find I end up
on these long spelunking expeditions through the code base for a
particular problem, find the answer after many hours, then forget
everything I learned because I don't exercise my knowledge frequently
enough. A decent reference guide would help a lot. What do the various
SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
etc.

I can accept if there are not, but it would be a shame to keep on
hunting like this if there were a good reference lying around.

Thanks!
P

Re: SearchSysCache() tutorial?

[Paul Ramsey]

On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey  wrote:
> Is there anywhere any documentation on SearchSysCache? I find I end up
> on these long spelunking expeditions through the code base for a
> particular problem, find the answer after many hours, then forget
> everything I learned because I don't exercise my knowledge frequently
> enough. A decent reference guide would help a lot. What do the various
> SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
> etc.
>
> I can accept if there are not, but it would be a shame to keep on
> hunting like this if there were a good reference lying around.

My particular hunt today is "for a given table relation, find any
indexes that use the gist access method and are on a single attribute
of type geometry".

Re: SearchSysCache() tutorial?

[Paul Ramsey]

> On Dec 11, 2017, at 11:37 AM, Melvin Davidson  wrote:
> 
> 
> 
> On Mon, Dec 11, 2017 at 2:26 PM, Paul Ramsey  <mailto:pram...@cleverelephant.ca>> wrote:
> On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey  <mailto:pram...@cleverelephant.ca>> wrote:
> > Is there anywhere any documentation on SearchSysCache? I find I end up
> > on these long spelunking expeditions through the code base for a
> > particular problem, find the answer after many hours, then forget
> > everything I learned because I don't exercise my knowledge frequently
> > enough. A decent reference guide would help a lot. What do the various
> > SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
> > etc.
> >
> > I can accept if there are not, but it would be a shame to keep on
> > hunting like this if there were a good reference lying around.
> 
> My particular hunt today is "for a given table relation, find any
> indexes that use the gist access method and are on a single attribute
> of type geometry".
> 
> 
> >My particular hunt today is "for a given table relation, find any
> >indexes that use the gist access method and are on a single attribute
> >of type geometry".
> 
> For that information, you are better off querying the system catalogs!
> 
> https://www.postgresql.org/docs/9.6/static/catalogs.html 
> <https://www.postgresql.org/docs/9.6/static/catalogs.html>
> 
> adjust the WHERE clause below to include the attribute you are looking for.
> 
> SELECT n.nspname as schema,
>i.relname as table,
>i.indexrelname as index,
>i.idx_scan,
>i.idx_tup_read,
>i.idx_tup_fetch,
>CASE WHEN idx.indisprimary
> THEN 'pkey'
> WHEN idx.indisunique
> THEN 'uidx'
> ELSE 'idx'
> END AS type,
>idx.indisexclusion,
>pg_get_indexdef(idx.indexrelid),
>CASE WHEN idx.indisvalid
> THEN 'valid'
> ELSE 'INVALID'
> END as statusi,
>pg_relation_size(quote_ident(n.nspname)|| '.' || 
> quote_ident(i.relname)) as size_in_bytes,
>pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' || 
> quote_ident(i.relname))) as size
>   FROM pg_stat_all_indexes i
>   JOIN pg_class c ON (c.oid = i.relid)
>   JOIN pg_namespace n ON (n.oid = c.relnamespace)
>   JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
>  WHERE i.relname = 'your_table'
>AND n.nspname NOT LIKE 'pg_%'
>AND  pg_get_indexdef(idx.indexrelid) LIKE '%gist%'
> ORDER BY 1, 2, 3;

Thanks. I’m working on doing this at the C level however, so using syscache 
seems like the right way to go about it. I’d like to avoid doing an SPI thing, 
if I can, tho I suppose I could always suck it up and just do that.

P

Re: SearchSysCache() tutorial?

[Paul Ramsey]

> On Dec 11, 2017, at 12:40 PM, Andres Freund  wrote:
> 
> On 2017-12-11 11:26:51 -0800, Paul Ramsey wrote:
>> On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey  
>> wrote:
>>> Is there anywhere any documentation on SearchSysCache? I find I end up
>>> on these long spelunking expeditions through the code base for a
>>> particular problem, find the answer after many hours, then forget
>>> everything I learned because I don't exercise my knowledge frequently
>>> enough. A decent reference guide would help a lot. What do the various
>>> SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
>>> etc.
>>> 
>>> I can accept if there are not, but it would be a shame to keep on
>>> hunting like this if there were a good reference lying around.
>> 
>> My particular hunt today is "for a given table relation, find any
>> indexes that use the gist access method and are on a single attribute
>> of type geometry".
> 
> I don't think there's a way to do this with a single syscache, there
> won't be an index than can cover all of these. I'd suggest using
> RelationGetIndexList(), and then filtering for gist and attribute type
> on the returned value.

Thanks, A, I seem to be on the right path then. Loop through all indexes in 
RelationGetIndexList(), for those that have a relam == GIST_AM_OID, loop 
through the associated columns (looks like I actually have to use a SysScanDesc 
on AttributeRelationId) and look for atts that have the type I’m interested in, 
and if I find one, yay, we have a winner.
Thanks,
P