[HACKERS] DROP TABLE and concurrent modifications
I can reproduce the following behavior with CVS HEAD.
1. Have a process do INSERTs into a table in a tight loop (I've
attached a trivial libpq app that does this)
2. In another session, repeatedly drop and re-create the table
that is being modified
You should see a stream of error messages from the INSERT client like:
query failed: ERROR: relation 29118 deleted while still in use
query failed: ERROR: relation test_tbl does not exist
query failed: ERROR: relation test_tbl does not exist
query failed: ERROR: relation test_tbl does not exist
query failed: ERROR: relation test_tbl does not exist
query failed: ERROR: relation test_tbl does not exist
query failed: ERROR: relation test_tbl does not exist
query failed: ERROR: relation 32430 deleted while still in use
query failed: ERROR: relation test_tbl does not exist
query failed: ERROR: relation test_tbl does not exist
query failed: ERROR: relation 34206 deleted while still in use
The problem is the variant of the error message. When the error
message variant occurs, the INSERT backend is in the following state:
[ ... ]
#2 0x0824ff48 in RelationClearRelation (relation=0x40c92538, rebuild=1 '\001') at
relcache.c:1711
#3 0x0825006e in RelationFlushRelation (relation=0x40c92538) at relcache.c:1775
#4 0x082501b5 in RelationCacheInvalidateEntry (relationId=17145, rnode=0x0) at
relcache.c:1842
#5 0x0824d153 in LocalExecuteInvalidationMessage (msg=0xbfffeed0) at inval.c:452
#6 0x081c6af5 in ReceiveSharedInvalidMessages (invalFunction=0x824d043
LocalExecuteInvalidationMessage,
resetFunction=0x824d213 InvalidateSystemCaches) at sinval.c:125
#7 0x0824d3c6 in AcceptInvalidationMessages () at inval.c:611
#8 0x081c8f99 in LockRelation (relation=0x40c92538, lockmode=3) at lmgr.c:143
#9 0x08089232 in relation_open (relationId=17145, lockmode=3) at heapam.c:462
#10 0x080892c9 in relation_openrv (relation=0x83956e0, lockmode=3) at heapam.c:506
#11 0x08089576 in heap_openrv (relation=0x83956e0, lockmode=3) at heapam.c:610
#12 0x080ee857 in setTargetTable (pstate=0x83955ec, relation=0x83956e0, inh=0 '\0',
alsoSource=0 '\0', requiredPerms=1) at parse_clause.c:142
#13 0x080d4390 in transformInsertStmt (pstate=0x83955ec, stmt=0x8395808,
extras_before=0xb0a0, extras_after=0xb09c) at analyze.c:543
[ ... ]
i.e. it is waiting to acquire a lock on the relation it wants to
INSERT into, but before returning from LockRelation() it receives a
shared-cache invalidation message for the relation the other backend
has just dropped. This causes it to error out in the bowels of
RelationClearRelation():
if (RelationBuildDesc(buildinfo, relation) != relation)
{
/* Should only get here if relation was deleted */
FreeTupleDesc(old_att);
if (old_rulescxt)
MemoryContextDelete(old_rulescxt);
pfree(relation);
elog(ERROR, relation %u deleted while still in use,
buildinfo.i.info_id);
}
Assuming my analysis is correct, is this a bug?
AFAICS it should be totally harmless, but at the least it would be
nice to display a more friendly/informative error message. Can anyone
see a way to do this without too much pain?
-Neil
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] DROP TABLE and concurrent modifications
Neil Conway [EMAIL PROTECTED] writes:
1. Have a process do INSERTs into a table in a tight loop (I've
attached a trivial libpq app that does this)
Sorry, I was evidently a little too quick off the draw. A simple test
app is /really/ attached this time.
-Neil
#include stdio.h
#include unistd.h
#include libpq-fe.h
int
main(void)
{
PGconn *conn;
conn = PQconnectdb();
if (PQstatus(conn) == CONNECTION_BAD)
return 1;
for (;;)
{
PGresult *res;
res = PQexec(conn, INSERT INTO test_tbl VALUES (5, 5, 5););
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
/* query failed */
printf(query failed: %s, PQresultErrorMessage(res));
fflush(stdout);
sleep(1);
}
PQclear(res);
}
PQfinish(conn);
return 0;
}
---(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] DROP TABLE and concurrent modifications
Neil Conway [EMAIL PROTECTED] writes: Assuming my analysis is correct, is this a bug? Yes, though a low-priority one in my mind. There is a TODO item about it: * Acquire lock on a relation before building a relcache entry for it (The TODO item is a bit unspecific though, since the issue here probably has to do with reusing an existing relcache entry rather than starting from scratch.) The difficulty with acquiring lock earlier is that to acquire lock, you need to know the relation's shared/unshared status as well as its OID. We'd need to do something with all the code that assumes that an OID is sufficient information for opening relations. For the case of DROP TABLE, we don't really need to solve this problem; it would be sufficient to make the error message a bit more friendly (we could possibly save aside the relation name before trying to rebuild the cache entry). I think the real reason for the TODO was concerns about ALTER TABLE RENAME --- if someone else is doing that, you could end up accessing a table that, by the time you've locked it, has a different name than you were looking up. It's not entirely clear to me what *should* happen in that case, but silently continuing is arguably not the best idea. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Renaming tables to other schemas
Rod Taylor wrote: -- Start of PGP signed section. On Sun, 2004-02-15 at 01:34, Neil Conway wrote: [EMAIL PROTECTED] writes: The capability to move objects to other schemas would be quite useful. I agree. It's not utterly-trivial to implement (for one thing, you need to move any dependant objects like indexes to the new schema), but some form of this functionality would be a useful thing to add, IMHO. It's not that hard to do either (I've done about 100 tables by hand at this point). Anyway, this should be supported by all RENAME commands, not just ALTER TABLE. Added TODO: o Allow the schema of objects to be changed -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ISAM driver for PostgreSQL
Merlin Moncure wrote: Does anybody think there might be some interest in an ISAM driver for PostgreSQL? I've written a functional alpha that allows PostgreSQL to be a drop in (or as easy as reasonably possible) replacement for an ISAM file system driving a COBOL application. It is a STL based thin wrapper around libpq that generates queries on the fly from traditional ISAM (read, write, start, etc.). It was made to be generic, allowing porting to various COBOL vendors' compilers with minimal effort. Error conditions are returned as traditional COBOL error codes. The main drawback to the system is that performance is highly sensitive to network latency (as are most COBOL applications). Unlike traditional hybrid COBOL which use ESQL, this system could run native COBOL code with reasonable performance in any COBOL application which allows linking to an external ISAM file system. This is very much a work in progress, built strictly for my own needs; but could be made to be useful in a more general sense. I'm thinking about cleaning up the code and setting up a project on sourceforge. PostgreSQL is uniquely suited for this purpose because of its portability and rich syntax. A good example is postgres's array type which maps very elegantly to COBOL's OCCURS syntax. Don't know --- no one has asked for this before. You could create a project on gborg.postgresql.org and see if folks find it useful. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] MS SQL features for new version
On Tue, 10 Feb 2004, Rodrigo wrote: Shridhar Daithankar wrote: Just stumbled upon this. just an FYI, http://www.microsoft.com/sql/yukon/productinfo/top30features.asp Shridhar From the page: A new Snapshot Isolation (SI) level will be provided at the database level. With SI, users will be able to access the last committed row using a transitionally consistent view of the database. This capability will provide greater scalability for very large database (VLDB) implementations. Is Snapshot Isolation == MVCC ? I think it goes that MVCC is a kind of snap shot, but snap shotting could be provided by more ways than just MVCC. But I'm not 100% certain on that. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] log_line_info
Rod Taylor wrote: and I'm willing to entertain other suggestions. Very nice, but you missed the most important. Command Tag. I've had a brief look at this proposal (to allow reporting of the command tag along with username, database, session cookie etc on each log line). I'm wondering where the best place to collect it might be. I thought that having it set in set_ps_display() might work. Thoughts? cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Win32 development question
Title: Message I am wanting to fiddle with the latest Win32 stuff, using the Mingw tools. Is it included in the current (2-17-04) snapshot? I did a download of the tarball and expanded it. I changed directory to the /postgresql-snapshot directory and performed a ./configure configure: creating ./config.statusconfig.status: creating GNUmakefileconfig.status: creating src/Makefile.globalconfig.status: creating src/include/pg_config.hconfig.status: linking ./src/backend/port/tas/dummy.s to src/backend/port/tas.sconfig.status: linking ./src/backend/port/dynloader/win32.c to src/backend/port/dynloader.cconfig.status: linking ./src/backend/port/sysv_sema.c to src/backend/port/pg_sema.cconfig.status: linking ./src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.cconfig.status: linking ./src/backend/port/dynloader/win32.h to src/include/dynloader.hconfig.status: linking ./src/include/port/win32.h to src/include/pg_config_os.hconfig.status: linking ./src/makefiles/Makefile.win32 to src/Makefile.port Make fails: $ makesrc/Makefile.global:275: src/Makefile.port: No such file or directorymake: *** No rule to make target `src/Makefile.port'. Stop. What is necessary in order to fool around with the Win32 stuff? From the above symbolic link formation, it seems like it should have found the file.
[HACKERS] log_line_info plan
I am about to redo the patch that would allow tagging of log lines with info via a printf-style string. Current plans are to call the config parameter log_line_info and implement the following escapes: %U = user %D = database %T = timestamp %P = pid %L = session log line number %C = sessionid cookie (hex encoded session start time + pid) %S = session start timestamp %I = Command Tag (e.g. CREATE TABLE) Any comments or suggestions before I start? cheers andrew ---(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] log_line_info plan
Andrew Dunstan wrote: I am about to redo the patch that would allow tagging of log lines with info via a printf-style string. Current plans are to call the config parameter log_line_info and implement the following escapes: %U = user %D = database %T = timestamp %P = pid %L = session log line number %C = sessionid cookie (hex encoded session start time + pid) %S = session start timestamp %I = Command Tag (e.g. CREATE TABLE) Any comments or suggestions before I start? My be kind of cool if we a a duration variable in there, especially if combined with %I J cheers andrew ---(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 -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0034 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(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] log_line_info plan
Joshua D. Drake wrote: Andrew Dunstan wrote: I am about to redo the patch that would allow tagging of log lines with info via a printf-style string. Current plans are to call the config parameter log_line_info and implement the following escapes: %U = user %D = database %T = timestamp %P = pid %L = session log line number %C = sessionid cookie (hex encoded session start time + pid) %S = session start timestamp %I = Command Tag (e.g. CREATE TABLE) Any comments or suggestions before I start? My be kind of cool if we a a duration variable in there, especially if combined with %I Duration of what? We could show the duration within the session, but we don't know the query duration because we print this before the query is executed, so folks can look in the logs to see what is currently running. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] log_line_info plan
Joshua D. Drake wrote: Andrew Dunstan wrote: I am about to redo the patch that would allow tagging of log lines with info via a printf-style string. Current plans are to call the config parameter log_line_info and implement the following escapes: %U = user %D = database %T = timestamp %P = pid %L = session log line number %C = sessionid cookie (hex encoded session start time + pid) %S = session start timestamp %I = Command Tag (e.g. CREATE TABLE) Any comments or suggestions before I start? My be kind of cool if we a a duration variable in there, especially if combined with %I Duration of what? This patch will not generate a single extra log line. It is intended to tag existing log lines, particularly to make creating log analysis tools and loading logs to tables easier. cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] log_line_info plan
Andrew Dunstan [EMAIL PROTECTED] writes: I am about to redo the patch that would allow tagging of log lines with info via a printf-style string. Any comments or suggestions before I start? I think Bruce already applied the previous version of your patch. No problem with yanking it out for a better version --- but please supply the update as a diff from CVS tip. Fewer chances for error that way. 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
Re: [HACKERS] [GENERAL] Check for prepared statement
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Fabrizio Mazzoni asked: How can i find out if a prepared statement already exists..? Is there a function or a query i can execute ..?? I have not seen an answer to this, and I am curious as well. Anyone? (Cross-posting to hackers due to the lack of response on general) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200402172039 -BEGIN PGP SIGNATURE- iD8DBQFAMsKDvJuQZxSWSsgRAtbeAJ9mmm3TKSU/hLc+oN3RREzCnM7kkQCfS4TS 6UoHDhGLc8kNyG7F/pT/6IM= =Lg+O -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
