Dear (PostGIS) data base experts,
trying to connect to an external, non-local, PostGIS layer via `v.external`,
`db.connect -p` shows all is set. Testing via `db.test test="test1"`
works fine, I think:
Using DB driver: pg
CREATE TABLE grass_test1 (i1 INTEGER, d1 DOUBLE PRECISION, c1 VARCHAR(20))
EXECUTE: OK
INSERT INTO grass_test1 VALUES ( 1, 123.456, 'abcd' )
EXECUTE: OK
INSERT INTO grass_test1 VALUES ( 2, null, 'xxx' )
EXECUTE: OK
SELECT * FROM grass_test1
EXECUTE: OK
RESULT: OK
SELECT c1 FROM grass_test1 WHERE d1 < 500 / 2 AND i1 <> 2 AND c1 LIKE '%bc%'
EXECUTE: OK
RESULT: OK
INSERT INTO grass_test1 VALUES ( 3, 0.0, '_\''_' )
EXECUTE: OK
ALTER TABLE grass_test1 ADD COLUMN i2 INTEGER
EXECUTE: OK
UPDATE grass_test1 SET d1 = 18.6, i2 = 987 WHERE i1 = 2
EXECUTE: OK
SELECT * FROM grass_test1
EXECUTE: OK
2d1
< 3|0|_\\'_|
3a3
> 3|0|_\'_|
ERROR: RESULT: ******** ERROR ********
DROP TABLE grass_test1
EXECUTE: OK
What is this "ERROR" about?
Executing
v.external in=PG:dbname=SomeName layer="pa.pa_1000" out=test
reports only warnings:
WARNING: No key column detected.
WARNING: Feature table <pa_1000> has no primary key defined. Unable to define
DB links.
..
WARNING: Random read is not supported for this layer. Unable to build
topology.
v.external complete. Link to vector map <test> created.
Is this an "indexing" issue on the PG side?
What is wanted, actually, is to link to "external" layers, stored in a
PostGIS data base and, subsequently, run `g.region` and `r.mask
vector=LinkedLayer`. I have tried this steps using an external ESRI
Shapefile, and it works. Would a PG "layer" be a problem?
Thank you, Nikos
* Nikos Alexandris <nik@nikosalexandris.net> [2018-05-03 19:57:17 +0200]:
Dear (PostGIS) data base experts,
Apologies for bringing this up again. Any idea what this error message
is about? Where to look further?
trying to connect to an external, non-local, PostGIS layer via `v.external`,
`db.connect -p` shows all is set. Testing via `db.test test="test1"`
works fine, I think:
Using DB driver: pg
CREATE TABLE grass_test1 (i1 INTEGER, d1 DOUBLE PRECISION, c1 VARCHAR(20))
EXECUTE: OK
INSERT INTO grass_test1 VALUES ( 1, 123.456, 'abcd' )
EXECUTE: OK
INSERT INTO grass_test1 VALUES ( 2, null, 'xxx' )
EXECUTE: OK
SELECT * FROM grass_test1
EXECUTE: OK
RESULT: OK
SELECT c1 FROM grass_test1 WHERE d1 < 500 / 2 AND i1 <> 2 AND c1 LIKE '%bc%'
EXECUTE: OK
RESULT: OK
INSERT INTO grass_test1 VALUES ( 3, 0.0, '_\''_' )
EXECUTE: OK
ALTER TABLE grass_test1 ADD COLUMN i2 INTEGER
EXECUTE: OK
UPDATE grass_test1 SET d1 = 18.6, i2 = 987 WHERE i1 = 2
EXECUTE: OK
SELECT * FROM grass_test1
EXECUTE: OK
2d1
< 3|0|_\\'_|
3a3
> 3|0|_\'_|
ERROR: RESULT: ******** ERROR ********
DROP TABLE grass_test1
EXECUTE: OK
What is this "ERROR" about?
Executing
v.external in=PG:dbname=SomeName layer="pa.pa_1000" out=test
reports only warnings:
WARNING: No key column detected.
WARNING: Feature table <pa_1000> has no primary key defined. Unable to define
DB links.
..
WARNING: Random read is not supported for this layer. Unable to build
topology.
v.external complete. Link to vector map <test> created.
Is this an "indexing" issue on the PG side?
On the PostGIS side, there are thousands of views, each one corresponds
to one boundary vector map. This has been developed so, for practical reasons,
is my understanding.
Anyone working with large vector data bases, stored in PostGIS?
Thanks again, Nikos
What is wanted, actually, is to link to "external" layers, stored in a
PostGIS data base and, subsequently, run `g.region` and `r.mask
vector=LinkedLayer`. I have tried this steps using an external ESRI
Shapefile, and it works. Would a PG "layer" be a problem?
Thank you, Nikos
_______________________________________________
grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user
--
Nikos Alexandris | Remote Sensing & Geomatics
GPG Key Fingerprint 6F9D4506F3CA28380974D31A9053534B693C4FB3
Just to document here the way out:
1. First, and not posted here previously, there was a "problem" related to
the number of object locks allocated for each transaction:
db.tables -p
DBMI-PostgreSQL driver error:
Unable to select view names.
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
(see also
https://www.postgresql.org/docs/9.1/static/runtime-config-locks.html)
This was solved by the database admin by increasing the corresponding
parameter.
2. The test "test1" fails for the instruction
INSERT INTO grass_test1 VALUES ( 3, 0.0, '_\''_' )
Guess: related to the "backslash". Maybe the test "test1" needs to be
updated to account for how PostgreSQL treats "escape" string constants.
Have to yet test this.
3. Adding, finally, a primary key, whose name doesn't really matter
(whether it is 'cat' or 'id' or 'wdpaid'), allows GRASS GIS to read the
table in question.
This IS also among the notes of PostgreSQL driver
(https://grass.osgeo.org/grass74/manuals/grass-pg.html).
Nikos