Re: [GENERAL] Error During PostGIS Build From Source on Linux

2017-04-12 Thread John R Pierce
On 4/12/2017 10:14 AM, Osahon Oduware wrote: Hi All, I am working on a Linux OS (Centos 6.5). I built GDAL successfully from source as below: ... I sure need the raster support to be enabled in PostGIS. Could anyone help me out with this. if you install those packages from the yum

Re: [GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)

2017-04-12 Thread David G. Johnston
On Wed, Apr 12, 2017 at 3:56 PM, Gavin Wahl wrote: > > Given this limited example I'd probably choose to model notifications as > an > > array on the user table. Then just "UPDATE user SET notifications = > > array['a','b']::text WHERE user_id = 1; > > I'm hesitant to ditch

Re: [GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)

2017-04-12 Thread Gavin Wahl
> Given this limited example I'd probably choose to model notifications as an > array on the user table. Then just "UPDATE user SET notifications = > array['a','b']::text WHERE user_id = 1; I'm hesitant to ditch the first normal form just to get around this. Anyway, there's actually extra data

Re: [GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)

2017-04-12 Thread David G. Johnston
On Wed, Apr 12, 2017 at 3:32 PM, Gavin Wahl wrote: > I have a table that stores user notifications: > > CREATE TABLE notifications ( > user_id INT, > type CHAR(1), > PRIMARY KEY (user_id, type) > ); > ​[...]​ > > > Is there any way to do this correctly without

Re: [GENERAL] Error During PostGIS Build From Source on Linux

2017-04-12 Thread Adrian Klaver
On 04/12/2017 02:37 PM, Osahon Oduware wrote: Hi Adrian, Thanks for your response. From the solution you provided, does it mean I have to build PostgreSQL first from source, before building GDAL from source? Alright did a test run of the build. I installed the dev packages for GEOS and Proj

Re: [GENERAL] SELECT FOR UPDATE violates READ COMMITTED isolation?

2017-04-12 Thread David G. Johnston
On Wed, Apr 12, 2017 at 3:14 PM, Gavin Wahl wrote: > I think this paragraph explains why it happens: > https://www.postgresql.org/docs/9.6/static/transaction- > iso.html#XACT-READ-COMMITTED. > > > If the first updater commits, the second updater will ignore the row if > the

[GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)

2017-04-12 Thread Gavin Wahl
I have a table that stores user notifications: CREATE TABLE notifications ( user_id INT, type CHAR(1), PRIMARY KEY (user_id, type) ); When a user edits their notifications, I need to atomically replace the old set with the new set. My first instinct is to do this: BEGIN; DELETE FROM

[GENERAL] SELECT FOR UPDATE violates READ COMMITTED isolation?

2017-04-12 Thread Gavin Wahl
I have this table: CREATE TABLE test (id INT PRIMARY KEY); INSERT INTO test VALUES (1); Then I run these two transactions simultaneously: one| two ---+--- BEGIN; |

Re: [GENERAL] Error During PostGIS Build From Source on Linux

2017-04-12 Thread Adrian Klaver
On 04/12/2017 02:37 PM, Osahon Oduware wrote: Hi Adrian, Thanks for your response. From the solution you provided, does it mean I have to build PostgreSQL first from source, before building GDAL from source? Well you need something to provide pg_config for the version of Postgres you want to

Re: [GENERAL] Error During PostGIS Build From Source on Linux

2017-04-12 Thread Osahon Oduware
Hi Adrian, Thanks for your response. From the solution you provided, does it mean I have to build PostgreSQL first from source, before building GDAL from source? On Apr 12, 2017 19:24, "Adrian Klaver" wrote: On 04/12/2017 11:11 AM, Rob Sargent wrote: > > > On

Re: [GENERAL] select on view shows different plan than select on table

2017-04-12 Thread David G. Johnston
On Wednesday, April 12, 2017, Bernd Lehmkuhl wrote: > > Why do I get different execution plans when querying the view like this: > SELECT > * > FROM > kkm_ergebnisse.v_protokoll_details_mit_dauer > WHERE > id_rechenlauf = 123 > ORDER BY > schritt ; > > opposed

[GENERAL] select on view shows different plan than select on table

2017-04-12 Thread Bernd Lehmkuhl
Hi list, I have a view defined as: CREATE VIEW kkm_ergebnisse.v_protokoll_details_mit_dauer AS SELECT id, schritt, objekt_typ, objekt_id, zeit, rechenweg_thema, rechenweg_variante, rechenweg_stoffgruppe, formel_inhalt, formel_stoff, formel_variablen, ergebnis_variable,

Re: [GENERAL] Error During PostGIS Build From Source on Linux

2017-04-12 Thread Adrian Klaver
On 04/12/2017 11:11 AM, Rob Sargent wrote: On 04/12/2017 12:01 PM, Adrian Klaver wrote: On 04/12/2017 10:14 AM, Osahon Oduware wrote: Hi All, I am working on a Linux OS (Centos 6.5). I built GDAL successfully from source as below: ./configure --prefix=/path/to/gdal make make install Next I

Re: [GENERAL] Error During PostGIS Build From Source on Linux

2017-04-12 Thread Rob Sargent
On 04/12/2017 12:01 PM, Adrian Klaver wrote: On 04/12/2017 10:14 AM, Osahon Oduware wrote: Hi All, I am working on a Linux OS (Centos 6.5). I built GDAL successfully from source as below: ./configure --prefix=/path/to/gdal make make install Next I built PostgreSQL successfully from source

Re: [GENERAL] Error During PostGIS Build From Source on Linux

2017-04-12 Thread Adrian Klaver
On 04/12/2017 10:14 AM, Osahon Oduware wrote: Hi All, I am working on a Linux OS (Centos 6.5). I built GDAL successfully from source as below: ./configure --prefix=/path/to/gdal make make install Next I built PostgreSQL successfully from source as below: ./configure --prefix=/path/to/pgsql

[GENERAL] Error During PostGIS Build From Source on Linux

2017-04-12 Thread Osahon Oduware
Hi All, I am working on a Linux OS (Centos 6.5). I built GDAL successfully from source as below: ./configure --prefix=/path/to/gdal make make install Next I built PostgreSQL successfully from source as below: ./configure --prefix=/path/to/pgsql make make install I attempted to configure PostGIS

Re: [GENERAL] advice for efresh of materialized view

2017-04-12 Thread Igor Neyman
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ivan Fabris, Gruppo ColliniConsulting.it Sent: Wednesday, April 12, 2017 11:17 AM To: 'pgsql-general@postgresql.org' Subject: [GENERAL] R: advice for efresh of

[GENERAL] R: advice for efresh of materialized view

2017-04-12 Thread Ivan Fabris, Gruppo ColliniConsulting.it
Thank you very much, I followed both of your advice, plus adding some extra ram, and the excution time dropped from 10 to 2 minutes. Since I did everything with one shot, I can't tell the weight of every single action. Regards IF Da: Igor Neyman

Re: [GENERAL] dynamic schema modeling and performance

2017-04-12 Thread Merlin Moncure
On Tue, Apr 11, 2017 at 12:46 PM, Rj Ewing wrote: > I'm looking for thoughts on the best way to handle dynamic schemas. > > The application I am developing revolves around user defined entities. Each > entity is a tabular dataset with user defined columns and data types. >

Re: [GENERAL] advice for efresh of materialized view

2017-04-12 Thread Igor Neyman
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ivan Fabris, Gruppo ColliniConsulting.it Sent: Wednesday, April 12, 2017 6:15 AM To: 'pgsql-general@postgresql.org' Subject: [GENERAL] advice for efresh of

Re: [GENERAL] advice for efresh of materialized view

2017-04-12 Thread Igor Neyman
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ivan Fabris, Gruppo ColliniConsulting.it Sent: Wednesday, April 12, 2017 6:15 AM To: 'pgsql-general@postgresql.org' Subject: [GENERAL] advice for efresh of

Re: [GENERAL] dynamic schema modeling and performance

2017-04-12 Thread Dorian Hoxha
I've done the dynamic-table-per-project previously and it worked great. Even dynamic indexes on it. If low thousands it should work ok. If more than that, use as many static-columns as possible, everything dynamic in jsonb, and check stuff with per-project-constraints. On Wed, Apr 12, 2017 at

[GENERAL] advice for efresh of materialized view

2017-04-12 Thread Ivan Fabris, Gruppo ColliniConsulting.it
Hi all, I have a table defined this way ( a couple of indexes are actually obsolete, the table needs a little clean ) : # \d categories_stat Table "public.categories_stat" Column|Type |

Re: [GENERAL] [OT] Help: stories of database security and privacy

2017-04-12 Thread vinny
On 2017-04-12 09:09, Lifepillar wrote: Hi folks, in a few weeks I will start a short course on the basics of database security for a group of high-school students with a background in elementary relational theory and SQL. I plan to discuss the usage of grant/revoke, RBAC, DAC, and inference in

[GENERAL] [OT] Help: stories of database security and privacy

2017-04-12 Thread Lifepillar
Hi folks, in a few weeks I will start a short course on the basics of database security for a group of high-school students with a background in elementary relational theory and SQL. I plan to discuss the usage of grant/revoke, RBAC, DAC, and inference in statistical databases. I'd like to take