[HACKERS] time format
how can i change default time format because now i have for example 2004-01-01 16:51:46.995927 but i want only 2004-01-01 16:51:46, with out millisec. a tryed with Data-Style but there arent custom style :/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] cache in plpgsql
may be postgres can use same way like triggers working, and when relation is droping ( what is equal to delete from pg_class) there could be something like trigger after .. which can waiting for CREATE or DROP command, and then clean-up cache, (for each backend). This could be for example same message, not just trigger (i said trigger only to show scheme of acction) ehheh this idea is also wrong ? On Wed, 31 Dec 2003, Jan Wieck wrote: ivan wrote: why all backend can not using one cache, which would be always Variable sized shared memory with garbage collection for SPI plans? in real state ... or i can just clear only my cache, at first (if i know that this relation could has another oid) and then normal using relations ? As said, that is not sufficient. The user who does the DDL statement can as well reconnect to the database to recompile all saved plans. It is the 200 persistent PHP DB connections that suffer from not finding the index any more. or ... just turn off cache, because its strange to has possible using drop, create etc in function, but using only EXECUTE .. Do you have any numbers about how that would affect performance? Jan there must be same solution .. no ? On Wed, 31 Dec 2003, Jan Wieck wrote: ivan wrote: as new know plpgsql has special cache which remember too long (event non-existing tables (i mean old oid)) so i suggest to create same function only in plpgsql which would clear this cache, or sth like this ? for ie, where i drop table i would do plpgsql_clear_cache (); and when i will create one more time table with this same name plpgsql will not remeber wrong oid possible ? You obviously did not bother to search the archives on this. This will not solve the problem since the cache you're talking about is per backend local memory. So if one backend modifies the schema, how does it cause all other to forgt? Since the same problem exists in general for everything that uses SPI, the solution lies in there. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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 -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PL/Java issues
Barry, This appears in principal to be very close to a servlet container. I'm wondering if we could just use something like jetty as the basis for the work? It has already defined class loading per the servlet spec. It is already setup to handle multiple requests, and load balancing, etc. Dave On Wed, 2003-12-31 at 19:34, Barry Lind wrote: Jan, In Oracle a call from sql into java (be it trigger, stored procedure or function), is required to be a call to a static method. Thus in Oracle all the work is left for the programmer to manage object instances and operate on the correct ones. While I don't like this limitation in Oracle, I can't see a better way of implementing things. Therefore if you want to operate on object instances you (in Oracle) need to code up object caches that can hold the instances across function calls so that two or more functions can operate on the same instance as necessary. What this implies to the implementation is that in order to be possible the multiple function calls need to run inside the same jvm (so you can access the static caches across the different calls). If every call created a new jvm instance in Oracle you couldn't do very much. The Oracle jvm essentially gives you one jvm per connection (although technically it is somewhere between one jvm for the whole server and one per connection - i.e. it has the memory and process footprint of a single jvm for the entire server, but appears to the user as a jvm per connection). Having one jvm per connection is important to limit multiple connections ability to stomp on each others data. Something similar could probably a done for postgres by having one jvm running, by having each postgres connection having a unique thread in that jvm and having each connection thread run with its own class loader instance so that separate classes (and thus static members) are loaded for each connection. thanks, --Barry Jan Wieck wrote: I have included the JDBC mailing list since I guess most Java developers are around here, but not necessarily on Hackers. Dave Cramer and I where discussing a few issues about the PL/Java implementation last night and would like to get more input and suggestions on the matter. The basic question is the definition of the lifetime of an object and it's identificaition when doing nested calls in this context. In the OO world, ideally a real world object is translated into one instance of a class. And complex structures are trees of instances, possibly of different classes. As an example, a sales order consists of the order header and a variable number of order lines. Therefore, per order we have one OH instance and several OL's. So far so good. Naturally, one Java object instance would correspond to one row in a database. If we now implement a stored procedure in PL/Java, that means that a pg_proc entry corresponds to a specific method of a specific class (its signature). But there is no obvious relationship between functions and tables or other objects. Because of that it is not implicitly clear if an incoming call to a method is meant for an existing instance or if a new one should be created. As an example, if a PL/Java trigger on the order header executes an SPI query on the order lines, a trigger on the order line (also in PL/Java) might now want to call a method on it's parent object (the order header that is waiting for the SPI result set). This should NOT result in another OH instance being created for the same logical OH. Probably it is not possible to map these things automatically while keeping the system flexible enough to be usefull. But is it feasable to require the programmer to provide glue code for every procedure that does all these things? How does Oracle attack this problem? Jan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Dave Cramer 519 939 0336 ICQ # 1467551 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Spinlock support for linux-hppa?
Oliver Elphick [EMAIL PROTECTED] writes: On Thu, 2004-01-01 at 03:22, Oliver Elphick wrote: What is needed to provide spinlock support for linux on hppa? Possibly nothing --- can you try CVS tip? Bruce has already committed his patch to decouple CPU and OS assumptions there, and I changed the TAS code to be inline for gcc. AFAICS it should just work. I should add that 7.3.4 built OK. For sufficiently small values of OK, perhaps so. We didn't complain about not having spinlocks then. 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] Spinlock support for linux-hppa?
On Thu, 2004-01-01 at 17:58, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: On Thu, 2004-01-01 at 03:22, Oliver Elphick wrote: What is needed to provide spinlock support for linux on hppa? Possibly nothing --- can you try CVS tip? Bruce has already committed his patch to decouple CPU and OS assumptions there, and I changed the TAS code to be inline for gcc. AFAICS it should just work. I can't very easily get cvs tip built on linus-hppa, because I couldn't make a package of that except for experimental, but experimental doesn't get processed by the autobuilders. I saw the changes to s_lock.h, but I couldn't at first sight see how to alter 7.4.1 to incorporate them, because they coincided with a file reorganisation. My object is to get 7.4.1 working on all the Debian architectures. However, I only have i386 machines; for the rest I have to rely on the autobuilders, which makes the task of debugging rather long-winded and is likely to involve a series of faulty packages till I get it right. So is there any possibility of a backpatch for 7.4.1? or tell me which files in 7.4.1 should receive the tas assembler for gcc. Can I take it that your words above imply that the assembler code is the same for gcc on all architectures? Is there any way of finding out from cvs all the files affected by a patch? Using the web interface to view changes to one file, I don't see how to find out what else changed at the same time. Thanks. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The LORD shall preserve thy going out and thy coming in from this time forth, and even for evermore. Psalms 121:8 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Spinlock support for linux-hppa?
Oliver Elphick [EMAIL PROTECTED] writes: I saw the changes to s_lock.h, but I couldn't at first sight see how to alter 7.4.1 to incorporate them, because they coincided with a file reorganisation. That's right. The change depends on Bruce's reorganization of the spinlock configuration mechanism, which we agreed not to apply to 7.4 because of the risk of breaking things (we were already well into the 7.4 beta test cycle at that time). We are not going to revisit that decision at this point --- it's not reasonable to risk breaking other platforms to add one more supported platform in a stable branch. The reorganization has to go through a port testing cycle before it can be considered suitable for a production release. My object is to get 7.4.1 working on all the Debian architectures. I'd have been more willing to buy into that goal if you'd been working on it during the 7.4 beta test cycle. I gather from what you are saying that you couldn't, because Debian provides essentially no infrastructure for testing package portability in advance of official releases. That seems like a rather serious misjudgement on their part ... maybe you could lobby to get it corrected? I think it might be possible to develop a localized patch for 7.4 that only affects linux-hppa and doesn't risk breaking anything else: copy the slock_t definition for hppa from hpux.h into linux.h and add the inline'd version of hppa tas() from CVS tip into s_lock.h. But I'm not particularly interested in working on it at two removes from where any testing could get done. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] using stp for dbt2 + postgresql
Bruce Momjian wrote: [EMAIL PROTECTED] wrote: Hi Manfred, Just wanted to let you know I tried your patch-spinlock-i386 patch on our STP (our automated test platform) 8-way systems and saw a 5.5% improvement with Pentium III Xeons. If you want to see those results: PostgreSQL 7.4.1: http://khack.osdl.org/stp/285062/ PostgreSQL 7.4.1 w/ your patch: http://khack.osdl.org/stp/285087/ Impressive. Thanks. The best thing is that we can try our own postgres patches with SDT now: this gives us a chance to run tests on up to 8-way systems, with 4 gb memory, 40 spindles. From my experience, the typical turnaround time is half a day - submit patch [web interface], start benchmark run, and after a few ours you get a mail that contains the output. With oprofile, it's very detailed - % cpu time for each function, down to individual asm instructions, plus the ability for custom logging into the postmaster log. I think we should try to use that to find a cache replacement policy that is SMP scalable, i.e. doesn't need a global lock - I searched a few minutes on citeseer, but couldn't find anything that doesn't rely on global lists. -- Manfred ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] cache in plpgsql
ivan wrote: may be postgres can use same way like triggers working, and when relation is droping ( what is equal to delete from pg_class) there could be something like trigger after .. which can waiting for CREATE or DROP command, and then clean-up cache, (for each backend). This could be for example same message, not just trigger (i said trigger only to show scheme of acction) ehheh this idea is also wrong ? Ivan, I really appreciate that you are thinking about this problem. But you keep talking about it as if this would be some sort of shared buffer cache. Do you know what a saved SPI execution plan is? Your idea is neither bad nor new, there actually is a so called system cache invalidation event propagated to every backend in the case of catalog changes, so that it can purge it's syscache. But can you tell me how to actually check if a saved SPI plan references that particular catalog object or not? Jan On Wed, 31 Dec 2003, Jan Wieck wrote: ivan wrote: why all backend can not using one cache, which would be always Variable sized shared memory with garbage collection for SPI plans? in real state ... or i can just clear only my cache, at first (if i know that this relation could has another oid) and then normal using relations ? As said, that is not sufficient. The user who does the DDL statement can as well reconnect to the database to recompile all saved plans. It is the 200 persistent PHP DB connections that suffer from not finding the index any more. or ... just turn off cache, because its strange to has possible using drop, create etc in function, but using only EXECUTE .. Do you have any numbers about how that would affect performance? Jan there must be same solution .. no ? On Wed, 31 Dec 2003, Jan Wieck wrote: ivan wrote: as new know plpgsql has special cache which remember too long (event non-existing tables (i mean old oid)) so i suggest to create same function only in plpgsql which would clear this cache, or sth like this ? for ie, where i drop table i would do plpgsql_clear_cache (); and when i will create one more time table with this same name plpgsql will not remeber wrong oid possible ? You obviously did not bother to search the archives on this. This will not solve the problem since the cache you're talking about is per backend local memory. So if one backend modifies the schema, how does it cause all other to forgt? Since the same problem exists in general for everything that uses SPI, the solution lies in there. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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 -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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] cache in plpgsql
Jan Wieck [EMAIL PROTECTED] writes: But can you tell me how to actually check if a saved SPI plan references that particular catalog object or not? This is certainly doable in principle. recordDependencyOnExpr() contains much of the logic that would be needed -- it would need to be modified so that the dependency info can just be put in memory and not stored into pg_depend, but that's surely not hard. (Note that to use it directly, we'd want to examine the parsed querytree not the plan tree, but that isn't a problem AFAICS.) Whether it's *practical* is another question. Cache inval events happen often enough that speed of response to 'em is an issue. Maybe we could use a hashtable of dependencies to avoid expensive searches for cached plans that must be invalidated. Another little problem is that plpgsql doesn't really have any mechanism for invalidating cached stuff at all; it will leak memory like there's no tomorrow if we start dropping cached subplans. plpgsql needs to be rewritten so that everything it allocates lives in per-function memory contexts that can be dropped when an invalidation happens. As far as I can see a dependency-based solution is possible. It's just a Small Matter Of Programming. http://www.catb.org/~esr/jargon/html/S/SMOP.html regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Spinlock support for linux-hppa?
Tom Lane wrote: I think it might be possible to develop a localized patch for 7.4 that only affects linux-hppa and doesn't risk breaking anything else: copy the slock_t definition for hppa from hpux.h into linux.h and add the inline'd version of hppa tas() from CVS tip into s_lock.h. But I'm not particularly interested in working on it at two removes from where any testing could get done. I think he also has to modify src/include/port/linux.h to support the HP processor. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spinlock support for linux-hppa?
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: ... copy the slock_t definition for hppa from hpux.h into linux.h ... I think he also has to modify src/include/port/linux.h to support the HP processor. That's what I said, I thought. 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] Spinlock support for linux-hppa?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: ... copy the slock_t definition for hppa from hpux.h into linux.h ... I think he also has to modify src/include/port/linux.h to support the HP processor. That's what I said, I thought. Oh, I saw slock_t definition and thought s_lock.h. -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] PL/Java issues
Jan Wieck wrote: The basic question is the definition of the lifetime of an object and it's identificaition when doing nested calls in this context. In the OO world, ideally a real world object is translated into one instance of a class. And complex structures are trees of instances, possibly of different classes. As an example, a sales order consists of the order header and a variable number of order lines. Therefore, per order we have one OH instance and several OL's. So far so good. Naturally, one Java object instance would correspond to one row in a database. It's not clear to me that this object -- row mapping is workable. It looks like Oracle, by allowing only static methods, has basically abandoned any possibility of it. ISTM that if you want to live in the object world, you have to take care of marshalling and unmarshalling the data yourself - either by manual methods or using some of the increasingly sophisticated automated tools that are available. OTOH, if you want to live in the table world, you have to live without the hard ties between data in different tables that the object world wants. PL/Java must surely live in the table world. IOW, the Java interface would probably need to function in a fairly similar way to the way the current C interface does. Or have I missed something? Also, what does the Standard say about all this? Has anyone actually seen it? cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
