The 8.1.4 shows POSTGIS="1.1.3" GEOS="2.2.1-CAPI-1.0.1" PROJ="Rel.
4.4.7, 31 March 2003" USE_STATS and the 8.1.3 shows POSTGIS="1.1.2"
GEOS="2.2.1-CAPI-1.0.1" PROJ="Rel. 4.4.7, 31 March 2003" USE_STATS
I've been playing with this a bit more by switching to 8.1.3 with
postgis 1.1.3 and still get the locking problem. I've attached a file
that shows the output of 'select pg_class.relname,pg_locks.* from
pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation' and the
process list when it locks. I've also attached the table descriptions.
I'd really like to nail this as I have a production system going live
very soon (hopefully).
--
Ross Elliott
Senior Software Engineer
Infoterra Ltd
T +44 (0)1252 362095
www.infoterra.co.uk
-----Original Message-----
From: Michael Fuhr [mailto:[EMAIL PROTECTED]
Sent: 17 September 2006 14:41
To: Tom Lane
Cc: Ross Elliott; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #2631: database locking problem
On Sun, Sep 17, 2006 at 01:29:33AM -0400, Tom Lane wrote:
> "Ross Elliott" <[EMAIL PROTECTED]> writes:
> > So, what happened between 8.1.3 and 8.1.4 that may have affected
> > locking?
>
> Nothing that I know of. Please provide a self-contained test case
> (but are you sure this is not a PostGIS bug?) ...
What does "SELECT postgis_full_version()" show? If you still have the
8.1.3 system then please post the output from both 8.1.3 and 8.1.4.
--
Michael Fuhr
Table "mastermap.topographicarea"
Column | Type | Modifiers
---------------------+------------------------+-----------
toid | character varying(22) |
version | numeric |
versiondate | date |
calculatedareavalue | numeric(16,6) |
changehistory | text |
descriptivegroup | character varying(250) |
descriptiveterm | character varying(250) |
featurecode | numeric(6,0) |
gml_version | numeric(6,0) |
loaddate | date |
make | character varying(12) |
mi_prinx | numeric(8,2) |
mi_style | character varying(250) |
osmmstyle_name | character varying(70) |
osmmstyle_number | numeric(6,0) |
physicallevel | numeric(6,0) |
physicalpresence | character varying(12) |
poly_broken | character varying(5) |
theme | character varying(250) |
tile | character varying(4) |
tilenumber | integer |
geometry | geometry |
Indexes:
"topoarea_idx1" gist (geometry), tablespace "mastermap"
"topoarea_idx2" btree (toid, version), tablespace "mastermap"
Check constraints:
"enforce_dims_geometry" CHECK (ndims(geometry) = 2)
"enforce_geotype_geometry" CHECK (geometrytype(geometry) = 'POLYGON'::text
OR geometry IS NULL)
"enforce_srid_geometry" CHECK (srid(geometry) = 27700)
Tablespace: "mastermap"
Table "mastermap.topographicline"
Column | Type | Modifiers
----------------------+------------------------+-----------
toid | character varying(22) |
version | numeric |
versiondate | date |
accuracyofposition | character varying(7) |
changehistory | text |
descriptivegroup | character varying(250) |
descriptiveterm | character varying(250) |
featurecode | numeric(6,0) |
gml_version | numeric(6,0) |
heightabovedatum | numeric(8,2) |
heightabovedatumacc | character varying(7) |
heightaboveground | numeric(8,2) |
heightabovegroundacc | character varying(7) |
loaddate | date |
make | character varying(12) |
mi_prinx | numeric(8,2) |
mi_style | character varying(250) |
osmmstyle_name | character varying(70) |
osmmstyle_number | numeric(6,0) |
physicallevel | numeric(6,0) |
physicalpresence | character varying(12) |
polyline_broken | character varying(5) |
referencetofeature | character varying(250) |
theme | character varying(250) |
tile | character varying(4) |
tilenumber | integer |
geometry | geometry |
Indexes:
"topoline_idx1" gist (geometry), tablespace "mastermap"
"topoline_idx2" btree (toid, version), tablespace "mastermap"
Check constraints:
"enforce_dims_geometry" CHECK (ndims(geometry) = 2)
"enforce_geotype_geometry" CHECK (geometrytype(geometry) =
'LINESTRING'::text OR geometry IS NULL)
"enforce_srid_geometry" CHECK (srid(geometry) = 27700)
Tablespace: "mastermap"
Table "mastermap.topographicpoint"
Column | Type | Modifiers
----------------------+------------------------+-----------
toid | character varying(22) |
version | numeric |
versiondate | date |
accuracyofposition | character varying(7) |
changehistory | text |
descriptivegroup | character varying(250) |
descriptiveterm | character varying(250) |
featurecode | numeric(6,0) |
gml_version | numeric(6,0) |
heightabovedatum | numeric(8,2) |
heightabovedatumacc | character varying(7) |
heightaboveground | numeric(8,2) |
heightabovegroundacc | character varying(7) |
loaddate | date |
make | character varying(12) |
mi_prinx | numeric(8,2) |
mi_style | character varying(250) |
osmmstyle_name | character varying(70) |
osmmstyle_number | numeric(6,0) |
physicallevel | numeric(6,0) |
physicalpresence | character varying(12) |
referencetofeature | character varying(250) |
theme | character varying(250) |
tile | character varying(4) |
tilenumber | integer |
geometry | geometry |
Indexes:
"topopoint_idx1" gist (geometry), tablespace "mastermap"
"topopoint_idx2" btree (toid, version), tablespace "mastermap"
Check constraints:
"enforce_dims_geometry" CHECK (ndims(geometry) = 2)
"enforce_geotype_geometry" CHECK (geometrytype(geometry) = 'POINT'::text OR
geometry IS NULL)
"enforce_srid_geometry" CHECK (srid(geometry) = 27700)
Tablespace: "mastermap"
Table "mastermap.cartographicsymbol"
Column | Type | Modifiers
------------------+------------------------+-----------
toid | character varying(22) |
version | numeric |
versiondate | date |
changehistory | text |
descriptivegroup | character varying(250) |
descriptiveterm | character varying(250) |
featurecode | numeric(6,0) |
gml_version | numeric(6,0) |
loaddate | date |
make | character varying(12) |
mi_style | character varying(250) |
osmmstyle_name | character varying(70) |
osmmstyle_number | numeric(6,0) |
physicallevel | numeric(6,0) |
physicalpresence | character varying(12) |
orientation | numeric(6,0) |
primary_key | numeric(6,0) |
theme | character varying(250) |
tile | character varying(4) |
tilenumber | integer |
geometry | geometry |
Indexes:
"cartosym_idx1" gist (geometry), tablespace "mastermap"
"cartosym_idx2" btree (toid, version), tablespace "mastermap"
Check constraints:
"enforce_dims_geometry" CHECK (ndims(geometry) = 2)
"enforce_geotype_geometry" CHECK (geometrytype(geometry) = 'POINT'::text OR
geometry IS NULL)
"enforce_srid_geometry" CHECK (srid(geometry) = 27700)
Tablespace: "mastermap"
Table "mastermap.cartographictext"
Column | Type | Modifiers
------------------------------+------------------------+-----------
toid | character varying(22) |
version | numeric |
versiondate | date |
changehistory | text |
descriptivegroup | character varying(250) |
descriptiveterm | character varying(250) |
featurecode | numeric(6,0) |
gml_version | numeric(6,0) |
loaddate | date |
make | character varying(12) |
mi_prinx | numeric(8,2) |
mi_style | character varying(250) |
osmmstyle_name | character varying(70) |
osmmstyle_number | numeric(6,0) |
physicallevel | numeric(6,0) |
physicalpresence | character varying(12) |
textstring | character varying(250) |
textrendering_anchorposition | numeric(6,0) |
textrendering_font | numeric(6,0) |
textrendering_height | numeric(6,3) |
textrendering_orientation | numeric(6,0) |
theme | character varying(250) |
tile | character varying(4) |
tilenumber | integer |
geometry | geometry |
Indexes:
"cartotext_idx1" gist (geometry), tablespace "mastermap"
"cartotext_idx2" btree (toid, version), tablespace "mastermap"
Check constraints:
"enforce_dims_geometry" CHECK (ndims(geometry) = 2)
"enforce_geotype_geometry" CHECK (geometrytype(geometry) = 'POINT'::text OR
geometry IS NULL)
"enforce_srid_geometry" CHECK (srid(geometry) = 27700)
Tablespace: "mastermap"
Table "mastermap.boundaryline"
Column | Type | Modifiers
--------------------+------------------------+-----------
toid | character varying(22) |
version | numeric |
versiondate | date |
accuracyofposition | character varying(7) |
changehistory | text |
descriptivegroup | character varying(250) |
descriptiveterm | character varying(250) |
featurecode | numeric(6,0) |
gml_version | numeric(6,0) |
loaddate | date |
make | character varying(12) |
mi_prinx | numeric(8,2) |
mi_style | character varying(250) |
osmmstyle_name | character varying(70) |
osmmstyle_number | numeric(6,0) |
physicallevel | numeric(6,0) |
physicalpresence | character varying(12) |
polyline_broken | character varying(5) |
theme | character varying(250) |
tile | character varying(4) |
tilenumber | integer |
geometry | geometry |
Indexes:
"bndline_idx1" gist (geometry), tablespace "mastermap"
"bndline_idx2" btree (toid, version), tablespace "mastermap"
Check constraints:
"enforce_dims_geometry" CHECK (ndims(geometry) = 2)
"enforce_geotype_geometry" CHECK (geometrytype(geometry) =
'LINESTRING'::text OR geometry IS NULL)
"enforce_srid_geometry" CHECK (srid(geometry) = 27700)
Tablespace: "mastermap"
relname | locktype | database | relation | page | tuple |
transactionid | classid | objid | objsubid | transaction | pid | mode
| granted
-----------------+----------+----------+-----------+------+-------+---------------+---------+-------+----------+-------------+------+------------------+---------
topoarea_idx2 | relation | 17403 | 444162126 | | |
| | | | 2237875 | 9331 | RowExclusiveLock | t
pg_locks | relation | 17403 | 10342 | | |
| | | | 2237900 | 9418 | AccessShareLock | t
topoarea_idx2 | extend | 17403 | 444162126 | | |
| | | | 2237816 | 9403 | ExclusiveLock | t
topographicarea | relation | 17403 | 444162093 | | |
| | | | 2237875 | 9331 | RowExclusiveLock | t
topographicarea | relation | 17403 | 444162093 | | |
| | | | 2237816 | 9403 | RowExclusiveLock | t
pg_class | relation | 17403 | 1259 | | |
| | | | 2237900 | 9418 | AccessShareLock | t
topoarea_idx2 | extend | 17403 | 444162126 | | |
| | | | 2237875 | 9331 | ExclusiveLock | f
topoarea_idx1 | relation | 17403 | 444162120 | | |
| | | | 2237875 | 9331 | RowExclusiveLock | t
topoarea_idx2 | relation | 17403 | 444162126 | | |
| | | | 2237816 | 9403 | RowExclusiveLock | t
topoarea_idx1 | relation | 17403 | 444162120 | | |
| | | | 2237816 | 9403 | RowExclusiveLock | t
(10 rows)
postgres 20180 1 0 08:32 pts/1 00:00:05 /usr/local/pgsql/bin/postmaster
-i
postgres 9185 20180 0 15:08 pts/1 00:00:00 postgres: writer process
postgres 9186 20180 0 15:08 pts/1 00:00:00 postgres: stats buffer process
postgres 9187 9186 0 15:08 pts/1 00:00:00 postgres: stats collector
process
postgres 9212 20180 0 15:09 pts/1 00:00:00 postgres: mastermap mastermap
192.168.89.2(44790) idle
postgres 9225 20180 0 15:09 pts/1 00:00:00 postgres: mastermap mastermap
192.168.89.3(52559) idle
postgres 9331 20180 1 15:10 pts/1 00:00:08 postgres: mastermap mastermap
192.168.89.3(52568) INSERT waiting
postgres 9332 20180 0 15:10 pts/1 00:00:01 postgres: mastermap mastermap
192.168.89.3(52569) idle
postgres 9333 20180 0 15:10 pts/1 00:00:00 postgres: mastermap mastermap
192.168.89.3(52570) idle
postgres 9334 20180 0 15:10 pts/1 00:00:00 postgres: mastermap mastermap
192.168.89.3(52571) idle
postgres 9403 20180 0 15:11 pts/1 00:00:00 postgres: mastermap mastermap
192.168.89.2(44962) INSERT
postgres 9404 20180 0 15:11 pts/1 00:00:00 postgres: mastermap mastermap
192.168.89.2(44963) idle
postgres 9405 20180 0 15:11 pts/1 00:00:00 postgres: mastermap mastermap
192.168.89.2(44964) idle
postgres 9406 20180 0 15:11 pts/1 00:00:00 postgres: mastermap mastermap
192.168.89.2(44965) idle
boris 9422 17187 0 15:22 pts/1 00:00:00 ps -Aef
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
