Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
On Fri, Mar 04, 2022 at 05:03:14PM -0500, Tom Lane wrote: > hubert depesz lubaczewski writes: > > On Fri, Mar 04, 2022 at 02:09:52PM -0500, Tom Lane wrote: > >> I tried and failed to reproduce this on Fedora 35 on aarch64, > >> but that has what I think is a newer LLVM version: > > > I have suspicion that it also kinda depends on number of rows in there. > > When I deleted some schemas, which included some functions, the problem > > disappeared. > > I wasn't able to pinpoint specific thing, though, and when I called the > > pg_proc + pg_roles query for each separate row - it worked flawlessly. > > Mmm ... it might have just been that the planner chose not to use > JIT when it thought there were fewer rows involved. Did you check > with EXPLAIN that these cut-down cases still used JIT? Hi, I tore these boxes down, so can't check immediately, but I think I remember that you're right - single-row queries didn't use JIT. Best regards, depesz
Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
hubert depesz lubaczewski writes: > On Fri, Mar 04, 2022 at 05:03:14PM -0500, Tom Lane wrote: >> Mmm ... it might have just been that the planner chose not to use >> JIT when it thought there were fewer rows involved. Did you check >> with EXPLAIN that these cut-down cases still used JIT? > I tore these boxes down, so can't check immediately, but I think > I remember that you're right - single-row queries didn't use JIT. FWIW, I went to the trouble of installing Ubuntu Focal on my raspberry pi to see if I could duplicate this, and I couldn't. However, what you get from a fresh install now is $ dpkg -l | grep libllvm ii libllvm10:arm641:10.0.0-4ubuntu1 arm64 Modular compiler and toolchain technologies, runtime library not 9.0.1. I also found that Fedora 31/aarch64 is still downloadable, and that does contain LLVM 9 ... and I could not reproduce it there either. So I've run out of things to try. It still seems like a bug in a specific point release of LLVM is a possible explanation, especially given that Debian went so far as to replace that release in an LTS distro. (The gcc-related packages are all exactly the same versions as you show.) I'd believe that data corruption could be a contributing factor too, except that you were able to read all the rows without JIT. regards, tom lane
FDW to postGIS Database
Let say that I have a database named stp. I also have another database named geo that is a PostGIS database for storing geopolitical and geospatial data. I want to set up a foreign data wrapper in stp to connect to geo, so that for each address in stp, I only have to store the primary key value of the lowest level geo unit, such as a city. I can always display the city's county, state, country, whatever in views in stp. I plan to have other databases that need geo data so I want a single source of truth for geopolitical and geospatial data. My questions are: 1. Do I have to install PostGIS in stp?or is it sufficient to install PostGIS only in geo? 2. Do I need the postgis extension in stp? (I'm thinking yes, but I don't want to guess.) 3. I want to geocode addresses in stp. Do I need to install any geocoding software in stp?...or can it be used from geo? Thanks for your help! Sue -- -- Susan E Hurst Email: susan.hu...@brookhurstdata.com Mobile: 314-486-3261
Do you know a json_populate_record alternative method to create a ROW with a named field notation?
Hello,
In this example:
```
CREATE TYPE contact AS (
firstname VARCHAR,
lastname VARCHAR
);
postgres=# SELECT json_populate_record(NULL::contact,
postgres(# '{
postgres'# "firstname": "John",
postgres'# "lastname": "Doe"
postgres'#}'
postgres'# );
json_populate_record
--
(John,Doe)
(1 row)
```
**Question:** do you know a method like json_populate_record (
https://www.postgresql.org/docs/13/functions-json.html), which allows
creating a `ROW` with named field notation without using a json format?
I know the `ROW` syntax expression:
```
postgres=# SELECT ROW('John', 'Doe')::contact;
row
(John,Doe)
(1 row)
```
But I didn't find a `ROW` constructors (
https://www.postgresql.org/docs/13/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS)
syntax allowing a named field notation.
Best regards,
Stéphane
--
Stéphane Klein
Homepage: http://stephane-klein.info
Re: Do you know a json_populate_record alternative method to create a ROW with a named field notation?
=?UTF-8?Q?St=C3=A9phane_Klein?= writes: > **Question:** do you know a method like json_populate_record ( > https://www.postgresql.org/docs/13/functions-json.html), which allows > creating a `ROW` with named field notation without using a json format? You mean you want to give the field names explicitly in the expression? The closest thing I can think of is to make a constructor function and invoke it with named-argument notation: regression=# CREATE TYPE contact AS ( regression(#firstname VARCHAR, regression(#lastname VARCHAR regression(# ); CREATE TYPE regression=# create function make_contact(firstname varchar, lastname varchar) regression-# returns contact as 'select row(firstname, lastname)::contact' regression-# language sql; CREATE FUNCTION regression=# select make_contact(firstname => 'John', lastname => 'Doe'); make_contact -- (John,Doe) (1 row) A bit tedious to set up, but it would have some advantages, eg you could provide default values. regards, tom lane
Re: FDW to postGIS Database
In line below... On Monday, March 7, 2022, 05:36:34 AM GMT+13, Susan Hurst wrote: Let say that I have a database named stp. I also have another database named geo that is a PostGIS database for storing geopolitical and geospatial data. I want to set up a foreign data wrapper in stp to connect to geo, so that for each address in stp, I only have to store the primary key value of the lowest level geo unit, such as a city. I can always display the city's county, state, country, whatever in views in stp. I plan to have other databases that need geo data so I want a single source of truth for geopolitical and geospatial data. My questions are: 1. Do I have to install PostGIS in stp?or is it sufficient to install PostGIS only in geo? If you want to use Postgis functions & datatypes in stp you will need Postgis there 2. Do I need the postgis extension in stp? (I'm thinking yes, but I don't want to guess.) Isn't this the same as (1) above? (Postgis is installed via "create extension postgis;") 3. I want to geocode addresses in stp. Do I need to install any geocoding software in stp?...or can it be used from geo? If you can geocode in geo & access the resulting data via fdw in stp, you should not need geocoding tools in stp.If you need to access spatial data from geo in stp & geocode in stp, you will need the geocoding tools in stp. Thanks for your help! Sue -- -- Susan E Hurst Email: susan.hu...@brookhurstdata.com Mobile: 314-486-3261
