[HACKERS] 'COPY ... FROM' inserts to btree, blocks on buffer writeout
| ExclusiveLock| t 2112116486 | 2112078897 | | 17983 | RowExclusiveLock | t 2112117699 | 2112078897 | | 18084 | AccessShareLock | t 2112116530 | 2112078897 | | 17983 | AccessShareLock | t 2112116535 | 2112078897 | | 17983 | AccessShareLock | t 2112116471 | 2112078897 | | 17980 | AccessShareLock | t 2124233471 | 2112078897 | | 17980 | ExclusiveLock| t 2112116436 | 2112078897 | | 18084 | AccessShareLock | t 2112116535 | 2112078897 | | 17980 | AccessShareLock | t 2112116476 | 2112078897 | | 17980 | AccessShareLock | t ||26990811 | 17977 | ExclusiveLock| t 2112116451 | 2112078897 | | 17980 | AccessShareLock | t 16837 | 2112078897 | | 23060 | AccessShareLock | t 2112116451 | 2112078897 | | 17983 | AccessShareLock | t 2112116441 | 2112078897 | | 17983 | AccessShareLock | t 2112116515 | 2112078897 | | 17983 | AccessShareLock | t 2112116525 | 2112078897 | | 17983 | AccessShareLock | t 2112116515 | 2112078897 | | 17980 | AccessShareLock | t 2112116471 | 2112078897 | | 17983 | AccessShareLock | t ||27016842 | 18084 | ExclusiveLock| t 2112116476 | 2112078897 | | 17983 | AccessShareLock | t 2112116486 | 2112078897 | | 17980 | RowExclusiveLock | t 2112116441 | 2112078897 | | 17980 | AccessShareLock | t 2112116525 | 2112078897 | | 17980 | AccessShareLock | t ||27018036 | 17976 | ExclusiveLock| t 2112116431 | 2112078897 | | 17980 | AccessShareLock | t 2112116456 | 2112078897 | | 17980 | AccessShareLock | t 2112085549 | 2112078897 | | 17983 | RowExclusiveLock | t 2112116436 | 2112078897 | | 17980 | AccessShareLock | t 2112116436 | 2112078897 | | 17983 | AccessShareLock | t 2112117337 | 2112078897 | | 17980 | RowExclusiveLock | t 2112116461 | 2112078897 | | 17980 | AccessShareLock | t 2112117357 | 2112078897 | | 17980 | RowExclusiveLock | t 2112116466 | 2112078897 | | 17980 | AccessShareLock | t 2112116520 | 2112078897 | | 17983 | AccessShareLock | t ||26991357 | 17982 | ExclusiveLock| t ||27018054 | 23060 | ExclusiveLock| t 2124233471 | 2112078897 | | 17983 | ExclusiveLock| f 2112116431 | 2112078897 | | 17983 | AccessShareLock | t 2112116431 | 2112078897 | | 18084 | AccessShareLock | t (54 rows) -- Do not feel safe. The poet remembers. DI Michael Wildpaner You can kill one, but another is born. Ph.D. Student The words are written down, the deed, the date. (Czeslaw Milosz) ---(end of broadcast)--- TIP 3: 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
Re: [HACKERS] 'COPY ... FROM' inserts to btree, blocks on buffer
On Fri, 31 Dec 2004, Tom Lane wrote: Michael Wildpaner [EMAIL PROTECTED] writes: two concurrent clients try to 'COPY ... FROM ...' to the same table, feature_link. The second one (pid 17983) is waiting for an ExclusiveLock on the table's primary key index, key__idpk__flink. You didn't show a stack trace for this one ... Here it is: [fuchs:/people/mike/build/postgresql-8.0.0beta2] gdb src/backend/postgres [...] (gdb) attach 17983 [...] (gdb) where #0 0x002a96181279 in semop () from /lib64/tls/libc.so.6 #1 0x00511707 in PGSemaphoreLock (sema=0x2aa16d8f90, interruptOK=1 '\001') at pg_sema.c:418 #2 0x00533e76 in ProcSleep (lockMethodTable=0x274801d, lockmode=7, lock=0x2aa1773878, proclock=0x2aa18ee398) at proc.c:725 #3 0x00532a7c in WaitOnLock (lockmethodid=32797, locallock=0x894180, owner=0x975130) at lock.c:1037 #4 0x0053246f in LockAcquire (lockmethodid=1, locktag=0x1, xid=2710496152, lockmode=7, dontWait=0 '\0') at lock.c:754 #5 0x00531aa3 in LockPage (relation=0x274801d, blkno=3220402448, lockmode=7) at lmgr.c:267 #6 0x0044a98d in _bt_getbuf (rel=0x2aa2466168, blkno=3220402448, access=2) at nbtpage.c:490 #7 0x00448c87 in _bt_split (rel=0x2aa2466168, buf=16355, firstright=89, newitemoff=119, newitemsz=40, newitem=0x985828, newitemonleft=0 '\0', itup_off=0x7fbff374c6, itup_blkno=0x7fbff374c8) at nbtinsert.c:683 #8 0x004486ba in _bt_insertonpg (rel=0x2aa2466168, buf=16355, stack=0x9a2a70, keysz=1, scankey=0x984a60, btitem=0x985828, afteritem=0, split_only_page=0 '\0') at nbtinsert.c:500 #9 0x004481eb in _bt_doinsert (rel=0x2aa2466168, btitem=0x985828, index_is_unique=1 '\001', heapRel=0x2aa24656e8) at nbtinsert.c:141 #10 0x0044bb71 in btinsert (fcinfo=0x274801d) at nbtree.c:257 #11 0x005a44aa in OidFunctionCall6 (functionId=41189405, arg1=18347880, arg2=548681250960, arg3=548681250928, arg4=10030788, arg5=18345192, arg6=18446744073709551612) at fmgr.c:1487 #12 0x004476a5 in index_insert (indexRelation=0x2aa2466168, datums=0x7fbff37890, nulls=0x7fbff37870 î, heap_t_ctid=0x990ec4, heapRelation=0x2aa24656e8, check_uniqueness=1 '\001') at indexam.c:226 #13 0x004cf3cf in ExecInsertIndexTuples (slot=0x0, tupleid=0x7fbff37110, estate=0x9828a0, is_vacuum=0 '\0') at execUtils.c:859 #14 0x0049c1d1 in CopyFrom (rel=0x2aa24656e8, attnumlist=0x9a25f0, binary=0 '\0', oids=0 '\0', delim=0x63e4cd \t, null_print=0x608614 \\N, csv_mode=0 '\0', quote=0x0, escape=0x0, force_notnull_atts=0x9849d0) at copy.c:1958 #15 0x0049a34f in DoCopy (stmt=0x274801d) at copy.c:1043 #16 0x0053d7b9 in PortalRunUtility (portal=0x906b70, query=0x8fa620, dest=0x8fa940, completionTag=0x7fbff37f60 ) at pquery.c:839 #17 0x0053da41 in PortalRunMulti (portal=0x906b70, dest=0x8fa940, altdest=0x8fa940, completionTag=0x7fbff37f60 ) at pquery.c:902 #18 0x0053d230 in PortalRun (portal=0x906b70, count=9223372036854775807, dest=0x8fa940, altdest=0x8fa940, completionTag=0x7fbff37f60 ) at pquery.c:543 #19 0x00539c59 in exec_simple_query ( query_string=0x8fa730 COPY feature_link from '/anniedev1/impseb/datastore/results/2004/12/29/22/38/bio:query:ncbi-blast:10562060.out_featurelink') at postgres.c:924 #20 0x0053bf6d in PostgresMain (argc=9414448, argv=0x86f028, username=0x86eff0 annieseb) at postgres.c:2970 #21 0x00514db7 in BackendRun (port=0x89c650) at postmaster.c:2848 #22 0x00514850 in BackendStartup (port=0x89c650) at postmaster.c:2470 #23 0x00512fde in ServerLoop () at postmaster.c:1215 #24 0x00512446 in PostmasterMain (argc=1, argv=0x804850) at postmaster.c:898 #25 0x004e3206 in main (argc=1, argv=0x804850) at main.c:265 Happy new year! (nearly 2 AM MET here ;) Best wishes, Mike -- Do not feel safe. The poet remembers. DI Michael Wildpaner You can kill one, but another is born. Ph.D. Student The words are written down, the deed, the date. (Czeslaw Milosz) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] [BUG/PATCH] backend crashes during authentication if data/global/pg_pwd is empty
Hi,
on Solaris 9 with PostgreSQL 7.4:
when you
- 'initdb' a fresh database,
- _don't_ set a password for user 'postgres',
- convert the 'trust' lines in data/pg_hba.conf to 'md5' or 'password'
and then try to connect as user 'postgres', the backend crashes in
backend/libpq/hba.c:372:
368 static int
369 user_group_bsearch_cmp(const void *user, const void *list)
370 {
371 /* first node is line number */
372 char *user2 = lfirst(lnext(*(List **) list));
due to 'list' being NULL, which might mean that 'user_sorted' was never
allocated, due to user_length being zero for an missing or empty
global/pg_pwd:
916 /* create sorted lines for binary searching */
917 user_length = length(user_lines);
918 if (user_length)
919 {
920 int i = 0;
921
922 user_sorted = palloc(user_length * sizeof(List *));
I know this is looks like a case of don't do it, then, but since it's a
backend crash, I would suggest the following fix:
--- postgresql-7.4.orig/src/backend/libpq/hba.c 2003-10-25 05:48:46.01000 +0200
+++ postgresql-7.4/src/backend/libpq/hba.c 2003-12-05 15:21:54.03000 +0100
@@ -62,7 +62,7 @@
static List **user_sorted = NULL; /* sorted user list, for bsearch() */
static List **group_sorted = NULL; /* sorted group list, for
*
bsearch() */
-static int user_length;
+static int user_length = 0;
static int group_length;
static List *tokenize_file(FILE *file);
@@ -395,6 +395,10 @@
List **
get_user_line(const char *user)
{
+ /* fail if there is nothing to search in */
+ if ((user_sorted == NULL) || (user_length == 0))
+ return NULL;
+
return (List **) bsearch((void *) user,
(void *) user_sorted,
user_length,
The initialization of user_length might not be necessary.
Best wishes, Mike
PS: This might be related to
http://archives.postgresql.org/pgsql-admin/2003-03/msg00413.php
--
Life is like a fire.DI Michael Wildpaner
Flames which the passer-by forgets.Ph.D. Student
Ashes which the wind scatters.
A man lived. -- Omar Khayyam
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] [BUG/PATCH] backend crashes during authentication if
Hi,
On Fri, 5 Dec 2003, Tom Lane wrote:
Hm, Solaris' bsearch() fails on empty input? How bizarre.
I was skeptical but apparently this is a known bug ...
googling turned up a couple of references, eg
http://www.opencm.org/pipermail/opencm-dev/2002-July/001077.html
in defense of Solaris' bsearch it should be said that it only breaks if
one passes NULL as the array base, a decidedly undefined (and unfriendly)
case. Passing an array with zero elements works as advertised. Btw, the
same happens on IRIX.
Best wishes, Mike
PS: A little program to demonstrate this: array with elements, empty
array, NULL pointer as base:
#include stdio.h
#include stdlib.h
#include string.h
char strings[][4] = { abc, efg, hij, klm };
typedef int (*cmp_t)(const void*, const void*);
int main(int argc, char**argv) {
char *s, *term = hij;
s = bsearch(term, strings, sizeof(strings)/sizeof(char[4]),
sizeof(char*), (cmp_t) strcmp);
fprintf(stderr, 1: %s\n, (s != NULL) ? s : not found);
s = bsearch(term, strings, 0, sizeof(char*), (cmp_t) strcmp);
fprintf(stderr, 2: %s\n, (s != NULL) ? s : not found);
s = bsearch(term, NULL, 0, sizeof(char*), (cmp_t) strcmp);
fprintf(stderr, 3: %s\n, (s != NULL) ? s : not found);
return 0;
}
Results:
$ ./a.out # Solaris 9
1: hij
2: not found
Segmentation Fault (core dumped)
$ ./a.out # IRIX 6.5
1: hij
2: not found
Segmentation fault (core dumped)
$ ./a.out # Linux with glibc 2.2.5
1: hij
2: not found
3: not found
$ ./a.out # OpenBSD 3.2
1: hij
2: not found
3: not found
--
Life is like a fire.DI Michael Wildpaner
Flames which the passer-by forgets.Ph.D. Student
Ashes which the wind scatters.
A man lived. -- Omar Khayyam
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] type problems during union: NULL+NULL produces TEXT
Hi, I have three tables, two of which are missing a column: CREATE TABLE table1 (t1 TEXT); CREATE TABLE table2 (t2 TEXT); CREATE TABLE table3 (t3 TEXT, i3 INTEGER); I am trying to create a view over these tables that defaults values for non-existant columns to NULL. CREATE VIEW view1 (i, t) AS SELECT t1, NULL FROM table1 UNION ALL SELECT t2, NULL FROM table2 UNION ALL SELECT t3, i3 FROM table3 ; This fails with ERROR: UNION types 'text' and 'integer' not matched suggesting that NULL+NULL produces TEXT as type of the second column in the union. The plain select (without CREATE VIEW) fails in the same way. It works for two tables (NULL+INTEGER = INTEGER): CREATE VIEW view2 (i, t) AS SELECT t1, NULL FROM table1 UNION ALL SELECT t3, i3 FROM table3 ; and of course with explicit casts CREATE VIEW view3 (i, t) AS SELECT t1, NULL::integer FROM table1 UNION ALL SELECT t2, NULL::integer FROM table2 UNION ALL SELECT t3, i3 FROM table3 ; Best wishes, Mike PS: This is version() 'PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4'. -- Life is like a fire. DI Michael Wildpaner Flames which the passer-by forgets. Ph.D. Student Ashes which the wind scatters. A man lived. -- Omar Khayyam ---(end of broadcast)--- TIP 3: 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
