Re: [HACKERS] stuck spin lock with many concurrent users
Tatsuo Ishii [EMAIL PROTECTED] writes: I have seen problems with extremely many concurrent users. I run pgbench: pgbench -c 1000 -t 1 test And I get stuck spin lock errors. This is 100% reproducable (i.e. I have nerver succeeded in pgbench -c 1000). Is it actually stuck, or just timing out due to huge contention? You could try increasing the timeout intervals in s_lock.c to make sure. I believe it's an actual stuck. From s_lock.c: #define DEFAULT_TIMEOUT (100*100) /* default timeout: 100 sec */ So even if there are 1000 contentions, 100 sec should be enough (100 msec for each backend). If it is stuck, on which lock(s)? How can I check it? In that situation, it's very hard to attacth a debugger to the backend process. 1000 backends consum all CPU time. -- Tatsuo Ishii ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: nocreatetable for 7.1.2
On Thu, Jun 21, 2001 at 01:39:38PM +0200, RISKO Gergely wrote: Hello! I saaw your patch for 7.0.2, but it is hard to port to 7.1.2 for me, because I haven't got any knowlendge in postgresql programming. Can you give me a nocreatetable patch for postgres 7.1.2? I'd like, but I unsure with my time -- may be later (3 weeks?). Will be the new permission system in 7.2? Probably not :-( PS. ...may be someone in hackers list port it to 7.1 (see CC) -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] stuck spin lock with many concurrent users
Tatsuo Ishii [EMAIL PROTECTED] writes: If it is stuck, on which lock(s)? How can I check it? The 'stuck' message should at least give you a code location... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] [PATCH] Re: Setuid functions
Sorry, I have decided not to follow the SQL standard ;-) PRIVILEGE is spelled
correctly in my patch.
This patch will implement the ENABLE PRIVILEGE and DISABLE PRIVILEGE
commands in PL/pgSQL, which, respectively, change the effective uid to that
of the function owner and back. It doesn't break security (I hope). The
commands can be abbreviated as ENABLE and DISABLE for the poor saps that
have trouble with PRIVILEGE :) Easier than adding a setuid bit to the
catalog, no?
Apologies if the patch is not in the correct format. Apply with
patch -p1 enable_disable.patch
in the tippety-top of the 7.1.2 tree.
Regression example:
CREATE USER sample_user;
CREATE TABLE test_log(stamp datetime);
GRANT SELECT ON test_log TO PUBLIC;
DROP FUNCTION test_enable();
CREATE FUNCTION test_enable() RETURNS boolean AS
'
DECLARE
user name;
BEGIN
user:=current_user;
RAISE NOTICE ''Username: %'', user;
ENABLE PRIVILEGE;
user:=current_user;
RAISE NOTICE ''Username: %'', user;
INSERT INTO test_log VALUES(''now''::text);
DISABLE PRIVILEGE; -- Actually unnecessary at the end of the function
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';
\c - sample_user
SELECT test_enable();
SELECT * FROM test_log;
stamp
2001-06-21 11:17:29-04
(Note current time logged into a table where sample_user could not normally
write)
Hope you will find this useful
- Mark
Ross J. Reedstrom wrote:
Come on, Chris, you've never heard about SQL standard LEDGE? That's
the nomenclature they chose to describe a collection of permissions:
a SHELF or LEDGE. PUBLEDGE, USERLEDGE, PRIVLEDGE. So, the last is the
PRIVATE LEDGE, reserved for the owner of the object whose access is
being determined (or was that PRIVITHEDGE? now I'm confused)
... or something. ;-) Actually, not too far from how some of the SQL92
standards docs actually seem to read, especially after falling asleep
face down on the keyboard will trying to understand them, and having
vivid dreams.
Ross (who's in the office much too late, working on budget justifications
for grants that are due tomorrow!)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
diff -ur postgresql-7.1.2/src/pl/plpgsql/src/gram.y
postgresql-7.1.2-patch/src/pl/plpgsql/src/gram.y
--- postgresql-7.1.2/src/pl/plpgsql/src/gram.y Wed Jun 20 20:07:45 2001
+++ postgresql-7.1.2-patch/src/pl/plpgsql/src/gram.yWed Jun 20 19:48:18 2001
@@ -121,7 +121,7 @@
%type stmts proc_sect, proc_stmts, stmt_else, loop_body
%type stmt proc_stmt, pl_block
%type stmt stmt_assign, stmt_if, stmt_loop, stmt_while, stmt_exit
-%type stmt stmt_return, stmt_raise, stmt_execsql, stmt_fori
+%type stmt stmt_return, stmt_raise, stmt_execsql, stmt_fori, stmt_enable,
+stmt_disable
%type stmt stmt_fors, stmt_select, stmt_perform
%type stmt stmt_dynexecute, stmt_dynfors, stmt_getdiag
@@ -164,6 +164,9 @@
%token K_PERFORM
%token K_ROW_COUNT
%token K_RAISE
+%token K_ENABLE
+%token K_DISABLE
+%token K_PRIVILEGE
%token K_RECORD
%token K_RENAME
%token K_RESULT_OID
@@ -569,6 +572,10 @@
{ $$ = $1; }
| stmt_raise
{ $$ = $1; }
+ | stmt_enable
+ { $$ = $1; }
+ | stmt_disable
+ { $$ = $1; }
| stmt_execsql
{ $$ = $1; }
| stmt_dynexecute
@@ -1033,6 +1040,34 @@
$$ = (PLpgSQL_stmt *)new;
}
;
+
+stmt_enable: K_ENABLE opt_privilege lno ';'
+ {
+ PLpgSQL_stmt_privilege *new;
+
+ new=malloc(sizeof(PLpgSQL_stmt_privilege));
+
+ new-cmd_type = PLPGSQL_STMT_ENABLE;
+ new-lineno = $3;
+
+ $$ = (PLpgSQL_stmt *)new;
+ }
+
+stmt_disable : K_DISABLE opt_privilege lno ';'
+ {
+ PLpgSQL_stmt_privilege *new;
+
+ new=malloc(sizeof(PLpgSQL_stmt_privilege));
+
+ new-cmd_type = PLPGSQL_STMT_DISABLE;
+ new-lineno = $3;
+
+ $$ = (PLpgSQL_stmt *)new;
+ }
+
Re: [HACKERS] COPY vs. INSERT
[EMAIL PROTECTED] (D'Arcy J.M. Cain) writes: I followed the instructions on interfacing user defined types as per http://www.ca.postgresql.org/devel-corner/docs/programmer/xindex.html. In fact I helped write that page so I am pretty sure I got it right. This code worked fine before. The only change I did was in the C code to use PG_FUNCTION_INFO_V1() style functions. I put in a lot of debug statements and I am positive that the code is doing the right thing. Obviously it isn't. Care to show us the code? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
RE: [HACKERS] RE: [BUGS] Update is not atomic
Incrementing comand counter is not enough - dirty reads are required to handle concurrent PK updates. What's that with you and dirty reads? Every so often you tell me that something would require them - you really like to read dirty things - no? :-) Dirty things occure - I like to handle them -:) All MVCC stuff is just ability to handle dirties, unlike old, locking, behaviour when transaction closed doors to table while doing its dirty things. Welcome to open world but be ready to handle dirty things -:) So let me get it straight: I execute the entire UPDATE SET A=A+1, then increment the command counter and don't see my own results? So an index scan with heap tuple check will return OLD (+NEW?) rows? Last time I fiddled around with Postgres it didn't, but I could be wrong. How are you going to see concurrent PK updates without dirty reads? If two transactions inserted same PK and perform duplicate check at the same time - how will they see duplicates if no one committed yet? Look - there is very good example of using dirty reads in current system: uniq indices, from where we started this thread. So, how uniq btree handles concurrent (and own!) duplicates? Btree calls heap_fetch with SnapshotDirty to see valid and *going to be valid* tuples with duplicate key. If VALID -- ABORT, if UNCOMMITTED (going to be valid) -- wait for concurrent transaction commit/abort (note that for obvious reasons heap_fetch(SnapshotDirty) doesn't return OLD rows modified by current transaction). I had to add all this SnapshotDirty stuff right to get uniq btree working with MVCC. All what I propose now is to add ability to perform dirty scans to SPI (and so to PL/*), to be able make right decisions in SPI functions and triggers, and make those decisions *at right time*, unlike uniq btree which makes decision too soon. Is it clear now how to use dirty reads for PK *and* FK? You proposed using share *row* locks for FK before. I objected then and object now. It will not work for PK because of PK rows do not exist for concurrent transactions. What would work here is *key* locks (locks placed for some key in a table, no matter does row with this key exist or not). This is what good locking systems, like Informix, use. But PG is not locking system, no reasons to add key lock overhead, because of PG internals are able to handle dirties and we need just add same abilities to externals. Vadim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: [GENERAL] Call for alpha testing: planner statistics revisions
Ed Loehr [EMAIL PROTECTED] writes: Tom Lane wrote: * ANALYZE is now available as a separate command; you can run it without also doing a VACUUM. (Of course, VACUUM ANALYZE still works.) What is the impact of this newly isolated ANALYZE command on the need and/or frequency for VACUUMs? None really. By the time 7.2 is out, I expect we will also have a more lightweight form of VACUUM, and so running VACUUM ANALYZE as a reasonably frequent background operation will still be the norm. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] COPY vs. INSERT
[EMAIL PROTECTED] (D'Arcy J.M. Cain) writes:
Obviously it isn't. Care to show us the code?
Sure. ftp://ftp.vex.net/pub/glaccount.
PG_FUNCTION_INFO_V1(glaccount_cmp);
Datum
glaccount_cmp(PG_FUNCTION_ARGS)
{
glaccount *a1 = (glaccount *) PG_GETARG_POINTER(0);
glaccount *a2 = (glaccount *) PG_GETARG_POINTER(1);
PG_RETURN_BOOL(do_cmp(a1, a2));
}
The btree comparison function needs to return 1/0/-1, not boolean.
Try PG_RETURN_INT32().
PG_FUNCTION_INFO_V1(glaccount_eq);
Datum
glaccount_eq(PG_FUNCTION_ARGS)
{
glaccount *a1 = (glaccount *) PG_GETARG_POINTER(0);
glaccount *a2 = (glaccount *) PG_GETARG_POINTER(1);
PG_RETURN_BOOL (!do_cmp(a1, a2));
}
PG_FUNCTION_INFO_V1(glaccount_ne);
Datum
glaccount_ne(PG_FUNCTION_ARGS)
{
glaccount *a1 = (glaccount *) PG_GETARG_POINTER(0);
glaccount *a2 = (glaccount *) PG_GETARG_POINTER(1);
PG_RETURN_BOOL (!!do_cmp(a1, a2));
}
While these two are not actually wrong, that sort of coding always
makes me itch. Seems like
PG_RETURN_BOOL (do_cmp(a1, a2) == 0);
PG_RETURN_BOOL (do_cmp(a1, a2) != 0);
respectively would be cleaner, more readable, and more like the other
comparison functions. I've always thought that C's lack of distinction
between booleans and integers was a bad design decision; indeed, your
cmp bug kinda proves the point, no?
regards, tom lane
---(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
[HACKERS] Good name for new lock type for VACUUM?
Awhile ago I said that I wanted to create a new flavor of table-level lock for concurrent VACUUM to get on a table. RowExclusiveLock is not the right thing because it is not self-exclusive, whereas we don't want more than one VACUUM mangling a table at a time. But anything higher locks out concurrent writers, which we don't want either. So we need an intermediate lock type that will conflict with itself as well as with ShareLock and above. (It must conflict with ShareLock since we don't want new indexes being created during VACUUM either...) I'm having a hard time coming up with a name, though. I originally called it VacuumLock but naming it after its primary use seems bogus. Some other possibilities that I don't much like either: SchemaLock --- basically we're locking down the table schema WriteShareLock --- sharing access with writers Any better ideas out there? Where did the existing lock type names come from, anyway? (Not SQL92 or SQL99, for sure.) BTW, I'm assuming that I should make the new lock type available at the user level as a LOCK TABLE option. Any objections to that? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [HACKERS] Good name for new lock type for VACUUM?
Any better ideas out there? Names were always hard for me -:) Where did the existing lock type names come from, anyway? (Not SQL92 or SQL99, for sure.) Oracle. Except for Access Exclusive/Share Locks. Vadim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: Good name for new lock type for VACUUM?
Tom Lane wrote: Awhile ago I said that I wanted to create a new flavor of table-level lock for concurrent VACUUM to get on a table. RowExclusiveLock is not the right thing because it is not self-exclusive, whereas we don't want more than one VACUUM mangling a table at a time. But anything higher locks out concurrent writers, which we don't want either. So we need an intermediate lock type that will conflict with itself as well as with ShareLock and above. (It must conflict with ShareLock since we don't want new indexes being created during VACUUM either...) *snip* BTW, I'm assuming that I should make the new lock type available at the user level as a LOCK TABLE option. Any objections to that? I think that type of lock would best be kept to the system level. *thinking out loud* If your goal is to have it used more often, then user level might provide more opportunities for testing. However, I can't really think of any situation where it would be beneficial to a user. The rest of the locks seem to take care of everything else. Is it going to timeout? If a connection is dropped by a user, will the lock release? ---(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] Re: Good name for new lock type for VACUUM?
Thomas Swan [EMAIL PROTECTED] writes: I think that type of lock would best be kept to the system level. Why? I don't have a scenario offhand where it'd be useful, but if we've discovered it's useful for VACUUM then there may be cases where a lock with these properties would be useful to users as well. Besides, we have several lock types that are exposed to users even though we've found no uses for them at the system level. Is it going to timeout? If a connection is dropped by a user, will the lock release? No, and yes, same as any other lock. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] stuck spin lock with many concurrent users
Tatsuo Ishii [EMAIL PROTECTED] writes: If it is stuck, on which lock(s)? How can I check it? The 'stuck' message should at least give you a code location... Here is the actual message: FATAL: s_lock(0x2ac2d016) at spin.c:158, stuck spinlock. Aborting. Last several queries before stuck spinlock are: DEBUG: query: update branches set bbalance = bbalance + 436 where bid = 1 DEBUG: query: update tellers set tbalance = tbalance + 230 where tid = 17 DEBUG: query: update tellers set tbalance = tbalance + 740 where tid = 7 DEBUG: query: update tellers set tbalance = tbalance + 243 where tid = 13 DEBUG: query: select abalance from accounts where aid = 177962 DEBUG: query: update tellers set tbalance = tbalance + 595 where tid = 18 DEBUG: query: update branches set bbalance = bbalance + 595 where bid = 1 DEBUG: query: update tellers set tbalance = tbalance + 252 where tid = 15 I'm trying now is increasing the timeout to 10 times longer. Will report in next email... -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] stuck spin lock with many concurrent users
Tatsuo Ishii [EMAIL PROTECTED] writes: How can I check it? The 'stuck' message should at least give you a code location... FATAL: s_lock(0x2ac2d016) at spin.c:158, stuck spinlock. Aborting. Hmm, that's SpinAcquire, so it's one of the predefined spinlocks (and not, say, a buffer spinlock). You could try adding some debug logging here, although the output would be voluminous. But what would really be useful is a stack trace for the stuck process. Consider changing the s_lock code to abort() when it gets a stuck spinlock --- then you could gdb the coredump. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] stuck spin lock with many concurrent users
Tatsuo Ishii [EMAIL PROTECTED] writes: How can I check it? The 'stuck' message should at least give you a code location... FATAL: s_lock(0x2ac2d016) at spin.c:158, stuck spinlock. Aborting. Hmm, that's SpinAcquire, so it's one of the predefined spinlocks (and not, say, a buffer spinlock). You could try adding some debug logging here, although the output would be voluminous. But what would really be useful is a stack trace for the stuck process. Consider changing the s_lock code to abort() when it gets a stuck spinlock --- then you could gdb the coredump. Nice idea. I will try that. -- Tatsuo Ishii ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
