[BUGS] BUG #8434: Why does dead lock occur many times ?
The following bug has been logged on the website: Bug reference: 8434 Logged by: Tomonari Katsumata Email address: katsumata.tomon...@po.ntts.co.jp PostgreSQL version: 9.3rc1 Operating system: RedHatEnterpriseLinux 6.4(x86_64) Description: Hi, I'm testing PostgreSQL 9.3rc1. Many times updates and selects for update become dead lock situation. The reproduce is: 1. initializing data createdb testdb psql testdb -c create table t (col1 int, col2 int, col3 text); psql testdb -c insert into t values (1, 4, 'A'); psql testdb -c insert into t values (2, 5, 'B'); psql testdb -c insert into t values (3, 6, 'C'); 2. executing updates and selects for update (run below script) #!/bin/sh ./tx1 /dev/null ./tx2 /dev/null ./tx3 /dev/null wait tx1 is: #!/bin/sh while : do psql testdb EOF BEGIN; UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6; COMMIT; \q EOF done tx2 is: #!/bin/sh while : do psql testdb EOF BEGIN; SELECT col1, col2, col3 FROM t WHERE col1 = 3 AND col2 = 6 FOR UPDATE; UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6; COMMIT; \q EOF done tx3 is: #!/bin/sh while : do psql testdb EOF BEGIN; UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6; COMMIT; \q EOF done Then, I got below messages. 2013-09-04 15:25:25 JST 29630 5226d254.73be-1 659102 (pgsql, testdb, [local], psql) LOG: 0: process 29630 detected deadlock while waiting for ShareLock on transaction 659103 after 1000.136 ms 2013-09-04 15:25:25 JST 29630 5226d254.73be-2 659102 (pgsql, testdb, [local], psql) LOCATION: ProcSleep, proc.c:1232 2013-09-04 15:25:25 JST 29630 5226d254.73be-3 659102 (pgsql, testdb, [local], psql) STATEMENT: UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6; 2013-09-04 15:25:25 JST 29630 5226d254.73be-4 659102 (pgsql, testdb, [local], psql) ERROR: 40P01: deadlock detected 2013-09-04 15:25:25 JST 29630 5226d254.73be-5 659102 (pgsql, testdb, [local], psql) DETAIL: Process 29630 waits for ShareLock on transaction 659103; blocked by process 29631. Process 29631 waits for ExclusiveLock on tuple (0,153) of relation 16385 of database 16384; blocked by process 29630. Process 29630: UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6; Process 29631: UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6; 2013-09-04 15:25:25 JST 29630 5226d254.73be-6 659102 (pgsql, testdb, [local], psql) HINT: See server log for query details. 2013-09-04 15:25:25 JST 29630 5226d254.73be-7 659102 (pgsql, testdb, [local], psql) LOCATION: DeadLockReport, deadlock.c:956 2013-09-04 15:25:25 JST 29630 5226d254.73be-8 659102 (pgsql, testdb, [local], psql) STATEMENT: UPDATE t SET col3 = 'c' WHERE col1 = 3 AND col2 = 6; I did not get these messages with PostgreSQL 9.2.4. Why did I get the dead lock situation with PostgreSQL9.3rc1? degrading? or I'm missing something? regards, Tomonari Katsumata -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8435: PGAdmin backup: obect list missing objects
The following bug has been logged on the website: Bug reference: 8435 Logged by: mihoff marta Email address: marta.mih...@dal.ca PostgreSQL version: 9.1.9 Operating system: windows 7 64 bit Description: Using the backup option (right click on an object) and then selecting the Objects tab: Not all objects appears in the list. This is true for versions 1.14.3 and the most recent version 1.16.1 downloaded today. I have a database called otn with over 100 schema. I wish to back up several tables from one schema, but that schema called pgs does not appear in the list of schema. When I change the name of the schema to psg then it does appear. Obviously related to some restriction on use of PG. I created several schema starting with pg. None show up in the object list. This is a scientific research project, at Dalhousie University, where the PGS means something and we do not want to change it to get this thing to work. I suspect this bug exists for all versions which use this backup function. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8435: PGAdmin backup: obect list missing objects
marta.mih...@dal.ca writes: Using the backup option (right click on an object) and then selecting the Objects tab: Not all objects appears in the list. This is true for versions 1.14.3 and the most recent version 1.16.1 downloaded today. I have a database called otn with over 100 schema. I wish to back up several tables from one schema, but that schema called pgs does not appear in the list of schema. When I change the name of the schema to psg then it does appear. You should probably report this on the pgadmin mailing list, I'm not sure how many of those folk read the core-server bug list. I would expect PGAdmin to hide schemas beginning with pg_, since those are reserved as system schema names. It sounds like someone got the test wrong and is checking for just pg not pg_. I'm suspicious there's a LIKE test coded as LIKE 'pg_%', which is wrong because _ is a metacharacter in LIKE patterns ... regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8436: Heisenbug: random: relation XXX does not exist on 3 tables/views
The following bug has been logged on the website: Bug reference: 8436 Logged by: Páll Haraldsson Email address: qwe...@hi.is PostgreSQL version: 9.1.9 Operating system: Linux lt;DELgt; 2.6.32-279.1.1.el6.x86_64 Description: Hi, Just a few days ago we started randomly getting: relation th_thjoderni does not exist This has happened a few times now but the other two same errors below only once each subsequently (yet). Those queries are very simple and have been unchanged for a long time in our system while th_thjoderni (nationality), an old static table, had just been added to the query below. Seems there is nothing wrong in our code/system; these queries run on each login (1000s per day) used to and usually now without error. A restart of PostgreSQL is not the solution but my boss wants me to try that just in case and see ef the problem goes away. Is there some way I lose any debug information that way? Is it valuable to know or should I NOT do that and check something before? Any thought on why this is happening now suddenly? SQLSTATE[42P01]Undefined table: 7 ERROR: relation nk_namskeid does not exist LINE 1: select * from nk_namskeid where ke_fagnumer='86295820076' SQLSTATE[42P01]Undefined table: 7 ERROR: relation myndir_notenda does not exist LINE 2: SELECT * FROM myndir_notenda WHERE kennitala = $1; SQLSTATE[42P01]Undefined table: 7 ERROR: relation th_thjoderni does not exist LINE 5: LEFT JOIN th_thjoderni ON (th_thjoderni.th_landakodi = th_na... SELECT th_nafnaskra.th_kennitala, th_nafnaskra.th_nafn, th_nafnaskra.th_radnafn, th_nafnaskra.th_kyn, th_nafnaskra.th_rikisfang, th_nafnaskra.th_faedingardag, acct.username, acct.nafn, acct.kennitala, acct.uid, acct.gid, th_heimilisfong.th_nefnifall, th_heimilisfong.th_thagufall, th_heimilisfong.th_postnumer, th_heimilisfong.th_poststod, th_heimilisfong.th_logheimili_nefnifall, th_heimilisfong.th_logheimili_thagufall, th_heimilisfong.th_logheimili_postnumer, th_heimilisfong.th_logheimili_poststod, th_thjoderni.th_land FROM th_nafnaskra LEFT JOIN acct ON (acct.kennitala = th_nafnaskra.th_kennitala) LEFT JOIN th_heimilisfong ON (th_heimilisfong.th_kennitala = th_nafnaskra.th_kennitala) LEFT JOIN th_thjoderni ON (th_thjoderni.th_landakodi = th_nafnaskra.th_rikisfang) WHERE th_nafnaskra.th_kennitala = ?; Table public.th_thjoderni Table ugla_hi.myndir_notenda View ugla_unak.nk_namskeid We can rule out, view (or table) issues, prepared statements, (specific) schema (they use different usernames that both see public) or public-schema issues, as they are not common. We upgraded to 9.1.9 many months ago. ps -ef |grep postgres postgres 5972 1 0 Apr15 ?17:04:28 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data .. ps -ef |grep postgres |wc -l 62 -bash-4.1$ uptime 15:58:31 up 341 days, 17:26, 1 user, load average: 1.00, 1.33, 1.27 ugla_hi=# \d+ th_thjoderni Table public.th_thjoderni Column| Type | Modifiers | Storage | Description --++---+--+- th_landakodi | character(2) | not null | extended | th_land | character varying(200) | | extended | Indexes: th_thjoderni_pkey PRIMARY KEY, btree (th_landakodi) Has OIDs: no ugla_hi=# select count(*) from th_thjoderni; count --- 231 Table ugla_hi.myndir_notenda Column| Type | Modifiers | Storage | Description -+-+---+--+- kennitala | character(10) | not null | extended | src | character varying(2000) | not null | extended | simaskra_id | character varying(2000) | | extended | Indexes: myndir_notenda_kt btree (kennitala) myndir_notenda_simaskra_id btree (simaskra_id) Has OIDs: no count --- 12640 (and counting) Your system is written in PHP using the usual client libraries (must be the server and not the client libraries?). Any more info you need? I'm pretty sure the few queries (not related to these queries and all autocommit) that came before shouldn't matter. This is my first time reporting (to PostgreSQL that is). Hopefully I'm doing it right. Nobody likes a Heisenbug.. -- Sincerely, Páll Haraldsson DBA University of Iceland -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7730: intarray representation of empty arrays
On Wed, Dec 5, 2012 at 12:44:39AM +, el...@varlena.com wrote: The following bug has been logged on the website: Bug reference: 7730 Logged by: elein Email address: el...@varlena.com PostgreSQL version: 9.2.1 Operating system: Linux Description: select NULLIF('{1,2,3}'::integer[] - '{3,2,1}'::integer[], '{}'::integer[]); This returns an empty array. It should return NULL. Per RhodiumToad: the core code represents '{}' as an array with 0 dimensions, whereas intarray represents it as an array with 1 dimension but 0 elements intarray should use the same standards as the core code if possible. I peered at the code and don't see anything untoward but did not have time to spend on it. I just got time to look at this, and it is certainly easier to see when you use array_dims(): SELECT '{1,2,3}'::integer[] - '{3,2,1}'::integer[]; ?column? -- {} SELECT array_dims('{1,2,3}'::integer[] - '{3,2,1}'::integer[]); array_dims [1:0] SELECT array_dims('{}'::integer[]); array_dims (null) This is part of the larger TODO item of how to handle empty =1-dimensional empty arrays vs. zero-dimensional empty arrays, which is discussed here: https://wiki.postgresql.org/wiki/Todo#Arrays Improve handling of empty arrays In that thread, no one could find a way to create a 1-dimensional empty array at the SQL level, but thanks to intarray, you found a way. It is natural that intarray, being mostly used for one-dimensional arrays, would return a 1-dimensional empty array. However, besides being inconsistent, as you mentioned, there is also no way to dump/restore one-dimensional empty arrays, which is a larger concern. I have developed the attached patch to force empty intarray results to be zero-dimensional empty arrays, rather than 1-dimensional empty arrays. With this patch, a zero-dimensional empty array is returned: SELECT array_dims('{1,2,3}'::integer[] - '{3,2,1}'::integer[]); array_dims (null) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/contrib/intarray/_int_tool.c b/contrib/intarray/_int_tool.c new file mode 100644 index 8635576..fc462b2 *** a/contrib/intarray/_int_tool.c --- b/contrib/intarray/_int_tool.c *** resize_intArrayType(ArrayType *a, int nu *** 246,251 --- 246,258 int nbytes = ARR_DATA_OFFSET(a) + sizeof(int) * num; int i; + /* if no elements, return a zero-dimensional array */ + if (num == 0) + { + ARR_NDIM(a) = 0; + return a; + } + if (num == ARRNELEMS(a)) return a; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8436: Heisenbug: random: relation XXX does not exist on 3 tables/views
qwe...@hi.is writes: Just a few days ago we started randomly getting: relation th_thjoderni does not exist You haven't really provided any information about what changed around the time this started happening. What I'd wonder about is concurrent DDL on these tables --- perhaps you added some kind of background maintenance task that wasn't there before? You might try enabling query logging (log_statement = all) to see exactly what's happening at the time you get one of these errors. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7754: Contrib start scipt comment refers to dead URL
On Sat, Dec 15, 2012 at 03:31:55AM +, pg-...@snkmail.com wrote: The following bug has been logged on the website: Bug reference: 7754 Logged by: Gavan Schneider Email address: pg-...@snkmail.com PostgreSQL version: 9.2.2 Operating system: OSX Description: FILE: postgresql-9.2.2/contrib/start-scripts/osx/PostgreSQL ; and many previous versions PBOBLEM: The comment: # For more information on Darwin/Mac OS X startup bundles, see this article: # # http://www.opensource.apple.com/projects/documentation/howto/html/SystemStarter_HOWTO.html # refers to a dead link. I wrote to Apple who confirmed it was dead, implicitly declined my request for a redirection, and suggested I report here. Suggest this bit of text be snipped as the underlying methodology has been deprecated for years, and is likely to be dropped soon. Specifically there seems little point educating those who don't already know this method. Done. The fix will appear in 9.3.1. OBSERVATION: I am thinking this contribution should be enhanced/replaced with a method suitable for current versions of OSX? So far I have not found a working plist recipe for my situation (and I have tried quite a few) so feel poorly placed to offer anything just now. Happy to trial ideas as suggested. Esp. since I seem to have invented a 'tough' environment :( Could you point me to an active postgresql discussion of this, so I can get up to speed (still pretty new to the pg community). Sorry, I have not seen this discussed anywhere. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7799: Several configuration options in guc.c miss descriptions
Thanks. This will appear in PG 9.4. --- On Tue, Jan 8, 2013 at 07:24:57AM +, t...@cs.ucsd.edu wrote: The following bug has been logged on the website: Bug reference: 7799 Logged by: Tianyin Xu Email address: t...@cs.ucsd.edu PostgreSQL version: 9.2.2 Operating system: any Description: Hi, I just noticed that several developer options do not have descriptions (in src/backend/utils/misc/guc.c) with the text No description available. I wrote the desc. according to the online doc, see below. HTH --- src/backend/utils/misc/guc.c +++ src/backend/utils/misc/guc.c. @@ -988,7 +988,7 @@ static struct config_bool ConfigureNamesBool[] = #ifdef BTREE_BUILD_STATS { {log_btree_build_stats, PGC_SUSET, DEVELOPER_OPTIONS, - gettext_noop(No description available.), + gettext_noop(Logs system resource usage statistics (memory and CPU) on various B-tree operations.), NULL, GUC_NOT_IN_SAMPLE }, @@ -1062,7 +1062,7 @@ static struct config_bool ConfigureNamesBool[] = #ifdef LOCK_DEBUG { {trace_locks, PGC_SUSET, DEVELOPER_OPTIONS, - gettext_noop(No description available.), + gettext_noop(Emits information about lock usage.), NULL, GUC_NOT_IN_SAMPLE }, @@ -1072,7 +1072,7 @@ static struct config_bool ConfigureNamesBool[] = }, { {trace_userlocks, PGC_SUSET, DEVELOPER_OPTIONS, - gettext_noop(No description available.), + gettext_noop(Emits information about user lock usage.), NULL, GUC_NOT_IN_SAMPLE }, @@ -1082,7 +1082,7 @@ static struct config_bool ConfigureNamesBool[] = }, { {trace_lwlocks, PGC_SUSET, DEVELOPER_OPTIONS, - gettext_noop(No description available.), + gettext_noop(Emits information about lightweight lock usage.), NULL, GUC_NOT_IN_SAMPLE }, @@ -1092,7 +1092,7 @@ static struct config_bool ConfigureNamesBool[] = }, { {debug_deadlocks, PGC_SUSET, DEVELOPER_OPTIONS, - gettext_noop(No description available.), + gettext_noop(Dumps information about all current locks when a deadlock timeout occurs.), NULL, GUC_NOT_IN_SAMPLE }, @@ -1828,8 +1828,8 @@ static struct config_int ConfigureNamesInt[] = #ifdef LOCK_DEBUG { {trace_lock_oidmin, PGC_SUSET, DEVELOPER_OPTIONS, - gettext_noop(No description available.), - NULL, + gettext_noop(Sets the minimum OID of tables for tracking locks.), + gettext_noop(Is used to avoid output on system tables.), GUC_NOT_IN_SAMPLE }, Trace_lock_oidmin, @@ -1838,7 +1838,7 @@ static struct config_int ConfigureNamesInt[] = }, { {trace_lock_table, PGC_SUSET, DEVELOPER_OPTIONS, - gettext_noop(No description available.), + gettext_noop(Sets the OID of the table with unconditionally lock tracing.), NULL, GUC_NOT_IN_SAMPLE }, -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs