[HACKERS] API changes in patch release
The world is not perfect and I know that you are normally very restrictive in what is back-patched from head into bug-fix branches. The 8.1.4 release however, did introduce a problem. You changed the API function inv_open() with the comment Revise large-object access routines to avoid running with CurrentMemoryContext. This change will force me to a) introduce patch level sensitive conditionals in the code, and b) have two PostgreSQL 8.1.n compatible releases of PL/Java. One where n 4 and another where n = 4. I would like to avoid this in the future if possible. API's should remain stable during patch releases. Having said that, I've been in the game long enough to know that Utopia doesn't exist. You probably had a very good reason to break the compatibility. Kind Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)
Tom Lane wrote: I think the hard part of this task is designing the API for access to the rowsets from triggers. My preference would be something similar to two Portal instances (the NEW and OLD). I could then map it in the same way that I map the result of a query. If the API actually used two real Portal instances, the PL/Java implementation would take minutes. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] error-free disabling of individual child partition
table of another table. I propose a TODO item to allow this: ALTER TABLE childN INHERITS ( parent1, ... ); We don't need a disinherit do we? I propose: ALTER TABLE childN INHERITS (); Thus I also think, that the list should be complete, and is not an addition to existing inheritance. O, yes, I think we do. I can imagine that the ability to swap a table Agreed. Simon, were you testing how many ppl read to the end :-) Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] error-free disabling of individual child partition
On Tue, 2006-05-23 at 09:49 +0200, Zeugswetter Andreas DCP SD wrote: table of another table. I propose a TODO item to allow this: ALTER TABLE childN INHERITS ( parent1, ... ); We don't need a disinherit do we? I propose: ALTER TABLE childN INHERITS (); Thus I also think, that the list should be complete, and is not an addition to existing inheritance. Sounds good; an absolute rather than a relative approach. Avoids new keywords. Implementation is simpler too: - check that we have all required merged attributes (if any) - remove any inheritance that isn't on the list If the table is already INHERITS (x) and we specify INHERITS (x) then its a no-op that returns success. O, yes, I think we do. I can imagine that the ability to swap a table Agreed. Simon, were you testing how many ppl read to the end :-) Just seeing who cared. DROP TABLE removes inheritance too, in a Keyser Soze manner. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] error-free disabling of individual child partition
Ühel kenal päeval, T, 2006-05-23 kell 10:51, kirjutas Simon Riggs: On Tue, 2006-05-23 at 09:49 +0200, Zeugswetter Andreas DCP SD wrote: table of another table. I propose a TODO item to allow this: ALTER TABLE childN INHERITS ( parent1, ... ); We don't need a disinherit do we? I propose: ALTER TABLE childN INHERITS (); Thus I also think, that the list should be complete, and is not an addition to existing inheritance. Sounds good; an absolute rather than a relative approach. Avoids new keywords. And also allows you move a partition from live to archive table in one command. Brilliant :) Implementation is simpler too: - check that we have all required merged attributes (if any) - remove any inheritance that isn't on the list If the table is already INHERITS (x) and we specify INHERITS (x) then its a no-op that returns success. O, yes, I think we do. I can imagine that the ability to swap a table Agreed. Simon, were you testing how many ppl read to the end :-) Just seeing who cared. DROP TABLE removes inheritance too, in a Keyser Soze manner. Just fyi - I care too . -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Performance Issues
I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seconds to execute the next time also. I'm wondering if there's any kind of caching that can be enabled -- so, the next time it takes 10 seconds to return the results. Thanks Dhanaraj ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Performance Issues
Dhanaraj M [EMAIL PROTECTED] writes: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. To enforce the primary key constraint, PG creates a unique index when the table is created (I think it even tells you this after CREATE TABLE). 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seconds to execute the next time also. I'm wondering if there's any kind of caching that can be enabled -- so, the next time it takes 10 seconds to return the results. All kinds of data is cached in shared memory. Did you tune the shared_buffers setting in postgresql.conf? It's set quite low by default to make sure the server can start on systems with low shared memory limits. The online documentation has this info and lots more--I suggest you read it. -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] error-free disabling of individual child partition
Zeugswetter Andreas DCP SD [EMAIL PROTECTED] writes: We don't need a disinherit do we? I propose: ALTER TABLE childN INHERITS (); Thus I also think, that the list should be complete, and is not an addition to existing inheritance. Don't like that at all: it seems far too error-prone. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] error-free disabling of individual child partition
Ühel kenal päeval, T, 2006-05-23 kell 09:37, kirjutas Tom Lane: Zeugswetter Andreas DCP SD [EMAIL PROTECTED] writes: We don't need a disinherit do we? I propose: ALTER TABLE childN INHERITS (); Thus I also think, that the list should be complete, and is not an addition to existing inheritance. Don't like that at all: it seems far too error-prone. In what way ? Do you mean that it will be easy for the user to make errors, od do yuo think that it will be hard to implement in a robust way ? In the first case, I'd propose following syntax ALTER TABLE childN ALTER INHERITS DROP (parent); ALTER TABLE childN ALTER INHERITS ADD (parent); With this syntax reparenting would need an explicit transaction and two ALTER TABLE ... ALTER INHERITS ... commands, but it is (arguably) harder to make mistakes. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Performance Issues
Dhanaraj M wrote: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. Yes, a unique index is used to enforce the primary-key. 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seconds to execute the next time also. I'm wondering if there's any kind of caching that can be enabled -- so, the next time it takes 10 seconds to return the results. Not of query results. Obviously data itself might be cached. You might want to look at memcached for this sort of thing. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] error-free disabling of individual child partition
On Tue, 2006-05-23 at 09:37 -0400, Tom Lane wrote: Zeugswetter Andreas DCP SD [EMAIL PROTECTED] writes: We don't need a disinherit do we? I propose: ALTER TABLE childN INHERITS (); Thus I also think, that the list should be complete, and is not an addition to existing inheritance. Don't like that at all: it seems far too error-prone. What types of error do you think its prone to? Can you say what you would prefer? As ever, not that worried about syntax, but I would like to get agreement on a specific way forward now we're discussing this. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] error-free disabling of individual child partition
ALTER TABLE childN ALTER INHERITS DROP (parent); ALTER TABLE childN ALTER INHERITS ADD (parent); Wouldn't it be possible to allow the ADD/DROP to happen in the same statement, like: ALTER TABLE childN ALTER INHERITS DROP crt_parent ADD new_parent; or: ALTER TABLE childN ALTER INHERITS DROP crt_parent, ALTER INHERITS ADD new_parent; That would still make it one statement, but more explicit. And it would eliminate the need for parenthesis (I assume they were needed for supplying more than 1 table to inherit/disinherit). Cheers, Csaba. ---(end of broadcast)--- TIP 1: 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] Performance Issues
On 23-May-06, at 10:24 AM, Richard Huxton wrote: Dhanaraj M wrote: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. Yes, a unique index is used to enforce the primary-key. 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seconds to execute the next time also. I'm wondering if there's any kind of caching that can be enabled -- so, the next time it takes 10 seconds to return the results. Not of query results. Obviously data itself might be cached. You might want to look at memcached for this sort of thing. Postgresql relies on the kernel buffers, and shared buffers for caching. As someone else said postgresql is quite conservative when shipped. Tuning helps considerably Dave -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [ADMIN] does wal archiving block the current client
On Fri, 2006-05-19 at 17:27 +0100, Simon Riggs wrote: On Fri, 2006-05-19 at 12:03 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: OK, I'm on it. What solution have you got in mind? I was thinking about an fcntl lock to ensure only one archiver is active in a given data directory. That would fix the problem without affecting anything outside the archiver. Not sure what's the most portable way to do it though. I was trying to think of a better way than using an archiver.pid file in pg_xlog/archive_status... Yesterday I posted to -patches with a new archiver.pid interlock mechanism. This will prevent server startup when the archiver is first activated, but once running will clean up and restart again. This doesn't quite get to the nub of the problem: archiver is designed to keep archiving files, even in the event that the postmaster explodes. It will keep archiving until they're all gone. My recent patch will prevent server startup, so if you do a fast restart to bounce the server and change parameters you'll have to keep the server down while the archiver completes (or you kill it). The archiver's Spartan diligence is great if postmaster does fail, but archiver can't tell the difference between a normal shutdown and a postmaster crash. If the postmaster sent a SIGUSR2 on normal shutdown, we would be able to interrupt the outer loop and shutdown much faster. A starting postmaster might then reasonably wait a little while for the old archiver to quit before starting the new one. What do you think? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ADMIN] does wal archiving block the current client connection?
Simon Riggs [EMAIL PROTECTED] writes: This doesn't quite get to the nub of the problem: archiver is designed to keep archiving files, even in the event that the postmaster explodes. It will keep archiving until they're all gone. I think we just need a PostmasterIsAlive check in the per-file loop. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] error-free disabling of individual child partition
On Tue, 2006-05-23 at 16:29 +0200, Csaba Nagy wrote: ALTER TABLE childN ALTER INHERITS DROP (parent); ALTER TABLE childN ALTER INHERITS ADD (parent); Wouldn't it be possible to allow the ADD/DROP to happen in the same statement, like: ALTER TABLE childN ALTER INHERITS DROP crt_parent ADD new_parent; or: ALTER TABLE childN ALTER INHERITS DROP crt_parent, ALTER INHERITS ADD new_parent; That would still make it one statement, but more explicit. And it would eliminate the need for parenthesis (I assume they were needed for supplying more than 1 table to inherit/disinherit). Sounds good. Do we need the ALTER keyword? That isn't used anywhere apart from manipulating columns. i.e. ALTER TABLE childN INHERITS DROP old_parent; ALTER TABLE childN INHERITS ADD new_parent; -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ADMIN] does wal archiving block the current client
On Tue, 2006-05-23 at 10:53 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: This doesn't quite get to the nub of the problem: archiver is designed to keep archiving files, even in the event that the postmaster explodes. It will keep archiving until they're all gone. I think we just need a PostmasterIsAlive check in the per-file loop. ...which would mean the archiver would not outlive postmaster in the event it crashes...which is exactly the time you want it to keep going. Granted, that's an easy change. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [ADMIN] does wal archiving block the current client connection?
Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2006-05-23 at 10:53 -0400, Tom Lane wrote: I think we just need a PostmasterIsAlive check in the per-file loop. ...which would mean the archiver would not outlive postmaster in the event it crashes...which is exactly the time you want it to keep going. Postmaster crashes are not a problem in practice; we've been careful to keep the postmaster doing so little that there's no material risk of it failing. If the postmaster dies it's almost certainly because someone killed it, and you really want the child processes to close up shop too. (If we did want the archiver to keep running, it shouldn't have any PostmasterIsAlive check at all; I can't see a reason why completing one iteration of the outer loop is a better time to stop than any other time.) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ADMIN] does wal archiving block the current client
On Tue, 2006-05-23 at 11:09 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2006-05-23 at 10:53 -0400, Tom Lane wrote: I think we just need a PostmasterIsAlive check in the per-file loop. ...which would mean the archiver would not outlive postmaster in the event it crashes...which is exactly the time you want it to keep going. Postmaster crashes are not a problem in practice; we've been careful to keep the postmaster doing so little that there's no material risk of it failing. If the postmaster dies it's almost certainly because someone killed it, and you really want the child processes to close up shop too. (If we did want the archiver to keep running, it shouldn't have any PostmasterIsAlive check at all; I can't see a reason why completing one iteration of the outer loop is a better time to stop than any other time.) This does at least solve the fast restart problem, so look on -patches in a few minutes. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Why is CVS server so slow?
The last few days the CVS server seems to be much slower than it used to be. No network changes here. Anything changed server side, or should I ask elsewhere? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] error-free disabling of individual child partition
Ühel kenal päeval, T, 2006-05-23 kell 15:59, kirjutas Simon Riggs: On Tue, 2006-05-23 at 16:29 +0200, Csaba Nagy wrote: ALTER TABLE childN ALTER INHERITS DROP (parent); ALTER TABLE childN ALTER INHERITS ADD (parent); Wouldn't it be possible to allow the ADD/DROP to happen in the same statement, like: ALTER TABLE childN ALTER INHERITS DROP crt_parent ADD new_parent; or: ALTER TABLE childN ALTER INHERITS DROP crt_parent, ALTER INHERITS ADD new_parent; That would still make it one statement, but more explicit. And it would eliminate the need for parenthesis (I assume they were needed for supplying more than 1 table to inherit/disinherit). Sounds good. Do we need the ALTER keyword? Probably not. That isn't used anywhere apart from manipulating columns. i.e. ALTER TABLE childN INHERITS DROP old_parent; ALTER TABLE childN INHERITS ADD new_parent; For me DROP INHERITS oldtable sounds better than INHERITS DROP oldtable , but it may be just me :) -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com NOTICE: This communication contains privileged or other confidential information. If you have received it in error, please advise the sender by reply email and immediately delete the message and any attachments without copying or disclosing the contents. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [ADMIN] does wal archiving block the current client
Simon Riggs [EMAIL PROTECTED] writes: My recent patch will prevent server startup, so if you do a fast restart to bounce the server and change parameters you'll have to keep the server down while the archiver completes (or you kill it). BTW, I was not planning on having it do that. The archiver subprocess should fail to start (and the PM keep trying to start it). Not take down the entire database. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] error-free disabling of individual child partition
On Tue, 2006-05-23 at 18:19 +0300, Hannu Krosing wrote: For me DROP INHERITS oldtable sounds better than INHERITS DROP oldtable , but it may be just me :) Agreed, so proposal is now ALTER TABLE childN DROP INHERITS old_parent; ALTER TABLE childN ADD INHERITS new_parent; Going once; going twice... -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: 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] error-free disabling of individual child partition
Simon Riggs [EMAIL PROTECTED] writes: Do we need the ALTER keyword? That isn't used anywhere apart from manipulating columns. i.e. ALTER TABLE childN INHERITS DROP old_parent; ALTER TABLE childN INHERITS ADD new_parent; At that point it seems like it'd read more naturally the other way round: ALTER TABLE childN DROP INHERITS old_parent; ALTER TABLE childN ADD INHERITS new_parent; although I'm not sure if this would create a parser conflict against ADD/DROP COLUMN. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Further reduction of bufmgr lock contention
Tom Lane wrote: I've been looking into Gavin Hamill's recent report of poor performance with PG 8.1 on an 8-way IBM PPC64 box. [...] Hullo again :) I'm unfamiliar with postgres development practices, so this is more a request for information than anything else. It's been about a month since the last activity on bufmgr as documented on the hackers list and I was just concerned that this issue had been filed as an interesting toy at the time, but now left for the circular filing cabinet :) Tom + Simon were able to see a fairly easy 25% performance boost against our dataset and I'd obv. be very keen to see this work make it into 8.1.4 or 8.2.0 :) Cheers, Gavin. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] file-locking and postmaster.pid
Andreas Joseph Krogh [EMAIL PROTECTED] writes: I've experienced several times that PG has died somehow and the postmaster.pid file still exists 'cause PG hasn't had the ability to delete it upon proper shutdown. Upon start-up, after such an incidence, PG tells me another PG is running and that I either have to shut down the other instance, or delete the postmaster.pid file if there really isn't an instance running. This seems totally unnecessary to me. The postmaster does check to see whether the PID mentioned in the file is still alive, so it's not that easy for the above to happen. If you can provide details of a scenario where a failure is likely, we'd like to know about it. Also, what PG version are you talking about? Why doesn't PG use file-locking to tell if another PG is running or not? Portability. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] New feature proposal
On Fri, 2006-05-19 at 12:35 -0700, Marc Munro wrote: On Fri, 2006-05-19 at 14:44 -0400, Tom Lane wrote: This could all be solved in a cleaner, more bulletproof way if you simply require such add-ins to be preloaded into the postmaster process using the existing preload_libraries hook. Then, such an add-in would allocate its own shmem segment independent of the main Postgres one. This totally eliminates worries about one chunk of code eating the other one's memory, which otherwise we'd have to have additional mechanism to deal with. This is an interesting idea that I had not previously considered. I will give it some thought. I have give this idea some further thought and I agree; Tom's solution is more bulletproof and is the right way to go. My original proposal is withdrawn. I am going to look into the best way to implement this but my gut feeling is that I would like the support infrastructure for this to be in Postgres rather than in Veil. By support infrastructure, I mean APIs to create and access new shared memory segments, and allocate chunks of memory from those shared segments. I think this code is better placed in Postgres rather than in specific add-ins because: it is functionality that could benefit many add-ins; it can make use of existing postgres code; and it can be easily tested in the regression suite using the buildfarm. I don't want to start working on this without knowing there is a chance of the patch being acceptable, so feedback is invited. Thanks. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] file-locking and postmaster.pid
On Tuesday 23 May 2006 17:54, Tom Lane wrote: Andreas Joseph Krogh [EMAIL PROTECTED] writes: I've experienced several times that PG has died somehow and the postmaster.pid file still exists 'cause PG hasn't had the ability to delete it upon proper shutdown. Upon start-up, after such an incidence, PG tells me another PG is running and that I either have to shut down the other instance, or delete the postmaster.pid file if there really isn't an instance running. This seems totally unnecessary to me. The postmaster does check to see whether the PID mentioned in the file is still alive, so it's not that easy for the above to happen. If you can provide details of a scenario where a failure is likely, we'd like to know about it. Also, what PG version are you talking about? I have experienced this with PG-8.1.3 and will provide details if I can make it happen. Basically it has happened when I have had to hard-reset my laptop due to some strange bugs in Linux which have made it hang. Why doesn't PG use file-locking to tell if another PG is running or not? Portability. Ok. -- Andreas Joseph Krogh [EMAIL PROTECTED] Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] file-locking and postmaster.pid
Andreas Joseph Krogh [EMAIL PROTECTED] writes: On Tuesday 23 May 2006 17:54, Tom Lane wrote: The postmaster does check to see whether the PID mentioned in the file is still alive, so it's not that easy for the above to happen. If you can provide details of a scenario where a failure is likely, we'd like to know about it. Also, what PG version are you talking about? I have experienced this with PG-8.1.3 and will provide details if I can make it happen. Basically it has happened when I have had to hard-reset my laptop due to some strange bugs in Linux which have made it hang. If you're talking about a postmaster that's auto-started during the boot sequence, then there is a risk depending on what start script you use. The problem is that depending on what else runs during the system startup, the PID assigned to the postmaster might be the same as in the last boot cycle, or it might be different by one or two counts. The postmaster disregards a pidfile containing its own PID, or its parent process' PID, or a PID not belonging to a postgres-owned process. That covers most cases but if your start script does something like su -l postgres -c pg_ctl start ... then you have a situation where not only the parent process (pg_ctl) but also the grandparent (a shell) is postgres-owned, and if the pidfile PID happens to match the grandparent then you lose. Solution is to either not use pg_ctl here, or write exec pg_ctl start ..., so that there's only one postgres-owned process besides the postmaster itself. Initscripts published by PGDG itself and by Red Hat have gotten this right for awhile, but I suspect the word has not propagated to all distros. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] error-free disabling of individual child partition
On Tue, 2006-05-23 at 11:31 -0400, Tom Lane wrote: At that point it seems like it'd read more naturally the other way round: ALTER TABLE childN DROP INHERITS old_parent; ALTER TABLE childN ADD INHERITS new_parent; although I'm not sure if this would create a parser conflict against ADD/DROP COLUMN. Behaviour would be: - If you DROP INHERITS this simply removes the link to the parent. All existing columns, constraints etc are retained. You can DROP inheritance on a table that is itself a parent; its children are unaffected. - If you ADD INHERITS this will fail if it would do the equivalent of possibly multiple ADD COLUMNs. You can ADD inheritance onto a table that is itself a parent; its children are unaffected. - The table data is not scanned at all for either ADD or DROP INHERITS - You cannot ADD INHERITS if the table being added as parent is already one of the inheritance set of the target table (i.e. no loops) - ADD/DROP are opposites; you can use the other one to undo an action taken in haste, error etc - Once DROP INHERITS has committed no changes are propagated down from parent to former child. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Performance Issues
Dhanaraj M wrote: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. Yes, a unique index is used to enforce the primary-key. Well, here is an interesting question that I have suddenly become very curious of, if you have a primary key, obviously a unique index, is it, in fact, use this index regardless of analyzing the table? 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seconds to execute the next time also. I'm wondering if there's any kind of caching that can be enabled -- so, the next time it takes 10 seconds to return the results. Not of query results. Obviously data itself might be cached. You might want to look at memcached for this sort of thing. I am looking at this string of posts and it occurs to me that he should run analyze. Maybe I'm jumping at the wrong point. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] file-locking and postmaster.pid
On Tue, May 23, 2006 at 05:23:16PM +0200, Andreas Joseph Krogh wrote: Hi all. I've experienced several times that PG has died somehow and the postmaster.pid file still exists 'cause PG hasn't had the ability to delete it upon proper shutdown. Upon start-up, after such an incidence, PG tells me another PG is running and that I either have to shut down the other instance, or delete the postmaster.pid file if there really isn't an instance running. This seems totally unnecessary to me. Why doesn't PG use file-locking to tell if another PG is running or not? If PG holds an exclusive-lock on the pid-file and the process crashes, or shuts down, then the lock(which is process-based and controlled by the kernel) will be removed and another PG which tries to start up can detect that. Using the existence of the pid-file as the only evidence gives too many false positives IMO. Well, maybe you could tweak postgres startup script, add check for post master (either 'pgrep postmaster' or 'ps -axu | grep [p]ostmaster'), and delete pid file on negative results. i.e. #!/bin/bash PID=`pgrep -f /usr/bin/postmaster`; if [[ $PID ]]; then echo '$PID'; # postgres is already running else echo Postmaster is not running; # delete stale PID file fi pgpBL3yb1NFGM.pgp Description: PGP signature
Re: [HACKERS] error-free disabling of individual child partition
Simon Riggs [EMAIL PROTECTED] writes: - ADD/DROP are opposites; you can use the other one to undo an action taken in haste, error etc It's not going to be that easy. What exactly will happen to the child table's attislocal/attinhcount settings, and why, during ADD or DROP? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] file-locking and postmaster.pid
Adis Nezirovic [EMAIL PROTECTED] writes: Well, maybe you could tweak postgres startup script, add check for post master (either 'pgrep postmaster' or 'ps -axu | grep [p]ostmaster'), and delete pid file on negative results. This is exactly what you should NOT do. A start script that thinks it is smarter than the postmaster is almost certainly wrong. It is certainly dangerous, too, because auto-deleting that pidfile destroys the interlock against having two postmasters running in the same data directory (which WILL corrupt your data, quickly and irretrievably). All it takes to cause a problem is to use the start script to start a postmaster, forgetting that you already have one running ... regards, tom lane ---(end of broadcast)--- TIP 1: 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] error-free disabling of individual child partition
On Tue, 2006-05-23 at 13:17 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: - ADD/DROP are opposites; you can use the other one to undo an action taken in haste, error etc It's not going to be that easy. What exactly will happen to the child table's attislocal/attinhcount settings, and why, during ADD or DROP? Never is round here ;-) attislocal: If you set this to False, you wouldn't be able to set it back again. If you leave it as it is, you'd never be able to recursively drop a column. If you change it, you'll never be able to stop someone from dropping a previously defined local column. Proposal: 1. attislocal is not touched. That means if you want to create a new partition you do this: CREATE TABLE newChild () INHERITS (template); ... do some processing ... ALTER TABLE newChild ADD INHERITS parent; or this: CREATE TABLE newChild () INHERITS (parent); ALTER TABLE newChild DROP INHERITS parent; ... do some processing ... ALTER TABLE newChild ADD INHERITS parent; Neither of which I like. 2. attislocal is always set False when an appropriate ADD INHERITS is actioned. Not ever set back again. attinhcount changes as appropriate - up for ADDs and down for DROPs. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: 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] file-locking and postmaster.pid
On Tue, May 23, 2006 at 01:36:41PM -0400, Tom Lane wrote: This is exactly what you should NOT do. A start script that thinks it is smarter than the postmaster is almost certainly wrong. It is certainly dangerous, too, because auto-deleting that pidfile destroys the interlock against having two postmasters running in the same data directory (which WILL corrupt your data, quickly and irretrievably). All it takes to cause a problem is to use the start script to start a postmaster, forgetting that you already have one running ... I do agree with you that we should not play games with postmaster. Better to be safe than sorry. (So, manually deleting pid file is the only safe option). I was just suggestion (possibly dangerous) workaround. Btw, I do check for running postmaster, using full path (I don't wan to kill every postmaster on the system), is this safe? Or there could be race condition? pgpVhuJZOzXtM.pgp Description: PGP signature
Re: [HACKERS] [GENERAL] autovacuum connections are hidden
On Mon, May 22, 2006 at 02:45:30PM -0700, Casey Duncan wrote: On May 22, 2006, at 2:37 PM, Alvaro Herrera wrote: Jim C. Nasby wrote: Moving to -hackers You forgot to actually do it apparently? Yup, I are SMRT. Sorry about posting the patch to -general, BTW. Anyway it was committed to the 8.1 branch, so it is included in the new release (8.1.4?) Does this still obey stats_command_string? Yes. I considered having the ps display show the info, but it's not as useful because you can only get the info if you have access to the process list (i.e. not a remote client). Well, there's now been 2 calls for seperate options for the autovacuum process; this one and the ability to give it a different log level. Perhaps what would be best is having autovac read a second set of config options that would over-ride settings in the main postgresql.conf. This could be a seperate GUC, or perhaps a seperate config file (which would allow for removing all the autovac controls from postgresql.conf). In any event thanks a lot for the current fix, as is it's a big improvement! 8^) Ditto. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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] Update on sort-compression stuff
Martijn van Oosterhout kleptog@svana.org writes: - Test a way of storing tuples with less overhead than a HeapTuple header. If you could do it for in-memory sorts, that'd mean you could fit more tuples in memory before spilling to disk. Given the compression in that case is extremely cheap, it'd be much more likely to be beneficial. I looked into this and decided that trimming the headers for the in-memory copies is not as attractive as all that. The killer problem is that comparetup_heap() needs to be able to apply heap_getattr() to the stored tuples to extract sort keys. Unless we want to support a variant copy of the heap_getattr() infrastructure just for sort tuples, it ain't gonna work. Another issue is that we'd be increasing the palloc traffic for in-memory sorts, because tuplesort_gettuple() would have to cons up a freshly palloc'd complete tuple to hand back to the caller. However, we can definitely trim a lot of overhead from what gets written to tape, so I'll have a go at doing that. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Weird ..... (a=1 or a=2) (a=2 or a=1)
On 5/19/06, Tom Lane [EMAIL PROTECTED] wrote: This only affects the 7.4 and 8.0 branches, because earlier and later versions of Postgres don't use this technique for detecting duplicates. But it's surprising we didn't find it before. hm. about a year ago I reported a case where the database allowed multiple records with the same p-key which were causing problems with dump/reload from 8.0-8.1. It was pretty rare, but it looked like under certain circumstances unique constraint was not getting applied. I was unable to reproduce it, though. Is it possible that this bug was the cause of that particular problem? Merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Update on sort-compression stuff
On Tue, 2006-05-23 at 14:27 -0400, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: - Test a way of storing tuples with less overhead than a HeapTuple header. If you could do it for in-memory sorts, that'd mean you could fit more tuples in memory before spilling to disk. Given the compression in that case is extremely cheap, it'd be much more likely to be beneficial. I looked into this and decided that trimming the headers for the in-memory copies is not as attractive as all that. The killer problem is that comparetup_heap() needs to be able to apply heap_getattr() to the stored tuples to extract sort keys. Unless we want to support a variant copy of the heap_getattr() infrastructure just for sort tuples, it ain't gonna work. Another issue is that we'd be increasing the palloc traffic for in-memory sorts, because tuplesort_gettuple() would have to cons up a freshly palloc'd complete tuple to hand back to the caller. However, we can definitely trim a lot of overhead from what gets written to tape, so I'll have a go at doing that. If we write the tuples in compressed form and read them back in that same form, there wouldn't be any more palloc overhead at all. The freelists would be full of too large blocks, but that might not be such a problem. heap_getattr() is called by so few other places it makes sense to have a sort specific version. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] API changes in patch release
Thomas Hallgren wrote: The world is not perfect and I know that you are normally very restrictive in what is back-patched from head into bug-fix branches. The 8.1.4 release however, did introduce a problem. You changed the API function inv_open() with the comment Revise large-object access routines to avoid running with CurrentMemoryContext. This change will force me to a) introduce patch level sensitive conditionals in the code, and b) have two PostgreSQL 8.1.n compatible releases of PL/Java. One where n 4 and another where n = 4. I would like to avoid this in the future if possible. API's should remain stable during patch releases. Having said that, I've been in the game long enough to know that Utopia doesn't exist. You probably had a very good reason to break the compatibility. I don't think any of us realized the change would affect third-party projects. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Weird ..... (a=1 or a=2) (a=2 or a=1)
Merlin Moncure [EMAIL PROTECTED] writes: On 5/19/06, Tom Lane [EMAIL PROTECTED] wrote: This only affects the 7.4 and 8.0 branches, because earlier and later versions of Postgres don't use this technique for detecting duplicates. But it's surprising we didn't find it before. hm. about a year ago I reported a case where the database allowed multiple records with the same p-key which were causing problems with dump/reload from 8.0-8.1. It was pretty rare, but it looked like under certain circumstances unique constraint was not getting applied. I was unable to reproduce it, though. Yeah, I remember. Is it possible that this bug was the cause of that particular problem? No, this is unrelated. It only occurs in a query that's fetching rows using OR'd indexscans, eg SELECT ... WHERE indexedcol = 42 OR indexedcol = 47 OR ...; (you can spell it indexedcol IN (42,47,...) with same results) and the problem is basically incorrect detection of fetching the same row more than once, ie, a bug in the code that's in charge of not returning rows multiple times if query is like SELECT ... WHERE indexedcol = 42 OR indexedcol = 42 OR ...; This is nowhere near the unique-constraint enforcement mechanism. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] API changes in patch release
Magnus Hagander wrote: I don't think any of us realized the change would affect third-party projects. To help specifically PL/Java next time, is there any chance to get it included in the buildfarm builds? If it had been there, it would've been caught right away... Currently buildfarm builds the core, and nothing else. Testing of non core code (pgfoundry projects, uncommitted patches, unknown other stuff) is on a possible TODO list. It's something I want discussed at least informally at the Toronto conference - I hope the organizers have managed to find me a decent slot for a small discussion session to cover buildfarm issues. Right now I do not have enough time available to do much development work on it, though. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] error-free disabling of individual child partition
Added to TODO: o Add ALTER TABLE tab ADD/DROP INHERITS parent pg_attribute.attislocal has to be set to 'false' for ADD, and pg_attribute.attinhcount adjusted appropriately --- Simon Riggs wrote: On Tue, 2006-05-23 at 13:17 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: - ADD/DROP are opposites; you can use the other one to undo an action taken in haste, error etc It's not going to be that easy. What exactly will happen to the child table's attislocal/attinhcount settings, and why, during ADD or DROP? Never is round here ;-) attislocal: If you set this to False, you wouldn't be able to set it back again. If you leave it as it is, you'd never be able to recursively drop a column. If you change it, you'll never be able to stop someone from dropping a previously defined local column. Proposal: 1. attislocal is not touched. That means if you want to create a new partition you do this: CREATE TABLE newChild () INHERITS (template); ... do some processing ... ALTER TABLE newChild ADD INHERITS parent; or this: CREATE TABLE newChild () INHERITS (parent); ALTER TABLE newChild DROP INHERITS parent; ... do some processing ... ALTER TABLE newChild ADD INHERITS parent; Neither of which I like. 2. attislocal is always set False when an appropriate ADD INHERITS is actioned. Not ever set back again. attinhcount changes as appropriate - up for ADDs and down for DROPs. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: 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 -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] SQL compliant interval implementation
Hi all, I've been looking at the postgres interval implementation lately, and I'm interested in putting together an improved implementation that accords more closely with the SQL specification, in particular with: --- 4.6.2 Intervals There are two classes of intervals. One class, called year-month intervals, has an express or implied datetime precision that includes no fields other than YEAR and MONTH, though not both are required. The other class, called day-time intervals, has an express or implied interval precision that can include any fields other than YEAR or MONTH. --- The reason for intervals being divided into two classes is that interval days and months (as distinct from datetime days and months) have no well-defined relationship. The current postgres implementation uses a conversion rate of 30 days to the month, but this appears to be a band-aid solution to a deeper problem; that postgres is trying to do with a single type something which really should be done with two. Imagine that I tried to implement a unified length type that contained two fields, metres and hobbits, where the number of hobbits per metre differs from one hobbit to the next, but nonetheless you're allowed to perform comparison and conversion between hobbits and metres. People would haul me out into the proverbial town square and throw rotten tomatoes at me. And rightly so. I think the SQL standard has the right idea. Keep the year-months and the day-times separate. Don't try to perform arithmetic or comparisons between the two. I note that this has been discussed on the mailing lists a few times before, but I didn't see any conclusion or consensus reached. So, the question is, are there any compelling reasons we shouldn't try to implement interval as two types? Does the existing unified approach offer any significant advantages? The only such advantage I can see is that it's easy for the user to specify month values and day values together when performing date arithmetic, for example if I wanted to add 1 month and 12 days to a date, under the current approach I would simply issue: SELECT dateval + interval '1 month 12 days'; That's nice and convenient, however, there's no reason we can't keep this simple under a separated approach: SELECT dateval + interval month '1' + interval second '12 days'; With ISO INTERVAL syntax (already listed as a TODO item) this becomes a bit more human-readable: SELECT dateval + interval '1' month + interval '12' day; By defining some convenient numeric input functions it becomes very readable (not to mention incredibly easy to parse into the internal format, versus textual inputs) SELECT dateval + months(1) + days(12); It could be done without breaking existing applications; just implement the two new interval types, and leave the old unified interval in place as a deprecated type, then drop it after a few major releases. The day-time interval type would be pretty much the same as the existing interval, with the month field removed. The year-month field would simply be a signed integer. Thanks in advance for your comments. Regards, BJ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SQL compliant interval implementation
I don't see how this makese our system any better than it does not. It just seems to eliminate the 30-day problem by not allowing it. That doesn't seem to be a step forward. --- Brendan Jurd wrote: Hi all, I've been looking at the postgres interval implementation lately, and I'm interested in putting together an improved implementation that accords more closely with the SQL specification, in particular with: --- 4.6.2 Intervals There are two classes of intervals. One class, called year-month intervals, has an express or implied datetime precision that includes no fields other than YEAR and MONTH, though not both are required. The other class, called day-time intervals, has an express or implied interval precision that can include any fields other than YEAR or MONTH. --- The reason for intervals being divided into two classes is that interval days and months (as distinct from datetime days and months) have no well-defined relationship. The current postgres implementation uses a conversion rate of 30 days to the month, but this appears to be a band-aid solution to a deeper problem; that postgres is trying to do with a single type something which really should be done with two. Imagine that I tried to implement a unified length type that contained two fields, metres and hobbits, where the number of hobbits per metre differs from one hobbit to the next, but nonetheless you're allowed to perform comparison and conversion between hobbits and metres. People would haul me out into the proverbial town square and throw rotten tomatoes at me. And rightly so. I think the SQL standard has the right idea. Keep the year-months and the day-times separate. Don't try to perform arithmetic or comparisons between the two. I note that this has been discussed on the mailing lists a few times before, but I didn't see any conclusion or consensus reached. So, the question is, are there any compelling reasons we shouldn't try to implement interval as two types? Does the existing unified approach offer any significant advantages? The only such advantage I can see is that it's easy for the user to specify month values and day values together when performing date arithmetic, for example if I wanted to add 1 month and 12 days to a date, under the current approach I would simply issue: SELECT dateval + interval '1 month 12 days'; That's nice and convenient, however, there's no reason we can't keep this simple under a separated approach: SELECT dateval + interval month '1' + interval second '12 days'; With ISO INTERVAL syntax (already listed as a TODO item) this becomes a bit more human-readable: SELECT dateval + interval '1' month + interval '12' day; By defining some convenient numeric input functions it becomes very readable (not to mention incredibly easy to parse into the internal format, versus textual inputs) SELECT dateval + months(1) + days(12); It could be done without breaking existing applications; just implement the two new interval types, and leave the old unified interval in place as a deprecated type, then drop it after a few major releases. The day-time interval type would be pretty much the same as the existing interval, with the month field removed. The year-month field would simply be a signed integer. Thanks in advance for your comments. Regards, BJ ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SQL compliant interval implementation
Brendan Jurd [EMAIL PROTECTED] writes: I've been looking at the postgres interval implementation lately, and I'm interested in putting together an improved implementation that accords more closely with the SQL specification, in particular with: Appealing to the SQL spec isn't going to take you very far in this argument, because the SQL spec itself is pretty broken in this area. In particular, the only reason that year-month vs day-second makes any sense is that they're pretending daylight savings doesn't exist. If you allow for DST transitions then the day/second multiplier isn't constant any more than the month/day multiplier is. (And then there are leap seconds...) I don't especially want to get into leap-second-aware timekeeping, because that'd practically destroy the ability to do calendar arithmetic at all. But DST awareness seems like a requirement to me. We just recently fixed interval to do something fairly plausible in that area, and I don't much want to backtrack. It could be done without breaking existing applications; just implement the two new interval types, and leave the old unified interval in place as a deprecated type, then drop it after a few major releases. Uh, not if all three types want to be called interval, and I don't entirely see how you maintain spec compliance without that. In any case, actually *dropping* support for the existing interval behavior is a way harder sell than just adding something more spec-like; you haven't been nearly persuasive enough to sell that one. I don't even see an argument here for deprecating it, because if you ask me it works better than the SQL-spec behavior. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] SQL compliant interval implementation
Brendan, There are two classes of intervals. One class, called year-month intervals, has an express or implied datetime precision that includes no fields other than YEAR and MONTH, though not both are required. The other class, called day-time intervals, has an express or implied interval precision that can include any fields other than YEAR or MONTH. Yeah, we used to do that. It sucked. In fact, most developers of applications which were calendar-heavy ended up using custom data types to work around the SQL-spec INTERVAL limitations. And that benefits nobody. We modified our INTERVAL type to function in ways that calendar application developers (of which there are several on this list) can actually use. See discussion leading up to the 8.0 release. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL compliant interval implementation
On 5/24/06, Josh Berkus josh@agliodbs.com wrote: Brendan, There are two classes of intervals. One class, called year-month intervals, has an express or implied datetime precision that includes no fields other than YEAR and MONTH, though not both are required. The other class, called day-time intervals, has an express or implied interval precision that can include any fields other than YEAR or MONTH. Yeah, we used to do that. It sucked. In fact, most developers of applications which were calendar-heavy ended up using custom data types to work around the SQL-spec INTERVAL limitations. And that benefits nobody. Could you elaborate on how it sucked? Apart from the issue of daylight savings which Tom has mentioned, what are these limitations that needed to be worked around? I've been searching through the archives for discussions relating to intervals, but haven't come across the one you're describing. Most probably because there have been a LOT of discussions relating to intervals. Regards, BJ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] LIKE, leading percent, bind parameters and indexes
PG-Hackers,I got the following picture:detran=# \d sa_dut.tb_usuario Table sa_dut.tb_usuario Column | Type | Modifiers-+-+---
numprocesso | bigint | not nullnome | character varying(44) |nomemae | character varying(44) |datanascimento | date |
Indexes: tb_usuario_pkey PRIMARY KEY, btree (numprocesso) ix_usuario_11 btree (nome varchar_pattern_ops, nomemae varchar_pattern_ops) ix_usuario_13 btree (datanascimento, nome varchar_pattern_ops)
As I do not use C locale, I created indexes based on varchar_pattern_ops.The issue I'm having is based on the following queries:select * from TB_USUARIO where nome like 'TATIANA CRISTINA G%';
select * from TB_USUARIO where nome like '%TATIANA CRISTINA G%';For some reasons, I'm not using text-search engines, like TSearch2, but only the LIKE operator.Here are the query plans involved:
detran=# explain analyze select count(*) as x0_0_ from sa_dut.TB_PROCESSO processo0_, sa_dut.TB_USUARIO usuario1_ where (usuario1_.NOME like 'TATIANA CRISTINA G%' and processo0_.NUMPROCESSO=usuario1_.NUMPROCESSO);
QUERY PLANAggregate (cost=11.94..11.95 rows=1 width=0) (actual time=
143.970..143.972 rows=1 loops=1) - Nested Loop (cost=0.00..11.94 rows=1 width=0) (actual time=143.935..143.949 rows=1 loops=1) - Index Scan using ix_usuario_11 on tb_usuario usuario1_ (cost=0.00..6.01
rows=1 width=8) (actual time=93.884..93.889 rows=1 loops=1) Index Cond: (((nome)::text ~=~ 'TATIANA CRISTINA G'::character varying) AND ((nome)::text ~~ 'TATIANA CRISTINA H'::character varying))
Filter: ((nome)::text ~~ 'TATIANA CRISTINA G%'::text) - Index Scan using tb_processo_pkey on tb_processo processo0_ (cost=0.00..5.91 rows=1 width=8) (actual time=50.041..50.044 rows=1 loops=1)
Index Cond: (processo0_.numprocesso = outer.numprocesso)Total runtime: 144.176 msdetran=# explain analyze select count(*) as x0_0_ from sa_dut.TB_PROCESSO processo0_, sa_dut.TB_USUARIO usuario1_ where
(usuario1_.NOME like '%TATIANA CRISTINA G%' and processo0_.NUMPROCESSO=usuario1_.NUMPROCESSO);QUERY PLAN-
Aggregate (cost=67534.55..67534.56 rows=1 width=0) (actual time=8101.957..8101.959 rows=1 loops=1) - Nested Loop (cost=0.00..67534.55 rows=1 width=0) (actual time=5404.106..8101.923 rows=1 loops=1) - Seq Scan on tb_usuario usuario1_ (cost=
0.00..67528.62 rows=1 width=8) (actual time=5404.056..8101.862 rows=1 loops=1) Filter: ((nome)::text ~~ '%TATIANA CRISTINA G%'::text) - Index Scan using tb_processo_pkey on tb_processo
processo0_ (cost=0.00..5.91 rows=1 width=8) (actual time=0.034..0.037 rows=1 loops=1) Index Cond: (processo0_.numprocesso = outer.numprocesso)Total runtime: 8102.105 msWe use Java, and recently we made an effort in order to avoid the leading '%' on LIKE expressions.
The problem is that it wasn't solved, and then I made the following Java code to verify it.What happens is that only the 004 block uses the index! The 002 code, which also has no leading percent, does a sequential scan. The difference between them is that 002 uses bind parameters.
Is it concerned to the JDBC Driver or PostgreSQL itself? What could be done in order to fix it?I could use static parameters, but then the queries would have to be reparsed each time on the backend, missing cache advantages.
package db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement
;import java.sql.ResultSet;import java.sql.SQLException;public class SelectLike { public SelectLike() { long qtd = 0L, inicio = 0L, tempo[] = {0,0,0,0}; try {
Class.forName(org.postgresql.Driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection con = null; String dbURL = jdbc:postgresql://10.15.61.6/database;
try { con = DriverManager.getConnection(dbURL, user, password); String sql = select count(*) as x0_0_ fromsa_dut.TB_PROCESSO processo0_, sa_dut.TB_USUARIO usuario1_ where
(usuario1_.NOME like ? and processo0_.NUMPROCESSO=usuario1_.NUMPROCESSO); String nome = TATIANA CRISTINA G; PreparedStatement ps = null; ResultSet rs = null;
//001 - '%NAME%' binded if (ps != null) ps.close(); ps = con.prepareStatement(sql); ps.setString(1, % + nome + %); inicio =
System.currentTimeMillis(); rs = ps.executeQuery(); rs.next(); qtd = rs.getLong(1); rs.close(); tempo[0] = System.currentTimeMillis() - inicio;
//002 - 'NAME%' binded if (ps != null) ps.close(); ps = con.prepareStatement(sql); ps.setString(1, nome + %); inicio = System.currentTimeMillis
(); rs = ps.executeQuery(); rs.next(); qtd = rs.getLong(1); rs.close(); tempo[1] = System.currentTimeMillis() - inicio; //003 - '%NAME%' static
if
Re: [HACKERS] SQL compliant interval implementation
Brendan, Could you elaborate on how it sucked? Apart from the issue of daylight savings which Tom has mentioned, what are these limitations that needed to be worked around? Well, actually, the DST thing was pretty severe -- it made timestamptz unusable. That's why we partitioned interval into month/year | day/week | hour/minute/second/etc. I personally don't see the benefit of evaluating 1 month = 30 days, but I don't see the harm either. days *don't* get rolled up to months, which is proper partitioned behavior: postgres=# select interval '180 days'; interval -- 180 days (1 row) postgres=# select interval '1800 days'; interval --- 1800 days The only issue comes when you multiply units by a decimal: postgres=# select interval '11 months' * 0.3; ?column? 3 mons 8 days 24:00:00 ... which leads to some broken calculations: select ( interval '11 months' * 0.3 ) / 0.3; ?column? -- 10 mons 26 days 96:00:00 but anyone who deals in fractional months should know that they're approximating. Previously, we couldn't get decimal calculations to work at all. I've been searching through the archives for discussions relating to intervals, but haven't come across the one you're describing. Most probably because there have been a LOT of discussions relating to intervals. If I had a link, I'd send it. But I'd have to do the same searching you're doing. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Why is CVS server so slow?
On Tue, 23 May 2006, Simon Riggs wrote: The last few days the CVS server seems to be much slower than it used to be. No network changes here. Anything changed server side, or should I ask elsewhere? I'm logged on daily interactively, and haven't noticed any issues ... Bruce has, in the past, noticed an issue that if he wipes out and re-checks out the source, it seems to clear up ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Porting MSSQL to PGSQL -- triggers
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: How expensive is this going to be, especially for huge numbers of rows? Certainly cheaper than firing a per-row trigger. I'm curious: I've never written a MSSQL trigger that did NOT use the INSERTED/DELETED pseudotables (aka NEW/OLD). I know STATEMENT-level triggers have existed in PG for a while ... but what do people use them for??? -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] SQL compliant interval implementation
On May 24, 2006, at 7:37 , Brendan Jurd wrote: I've been searching through the archives for discussions relating to intervals, but haven't come across the one you're describing. Most probably because there have been a LOT of discussions relating to intervals. I don't have links to the threads, but here are some subject titles and approximate times that may help you find where things related to this have been discussed before. November 2004 [GENERAL] Comment on timezone and interval types October 2005 [BUGS] BUG #1993: Adding/subtracting negative time intervals March 2006 Re: [HACKERS] [SQL] Interval subtracting Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Why is CVS server so slow?
On Tue, May 23, 2006 at 08:30:36PM -0300, Marc G. Fournier wrote: On Tue, 23 May 2006, Simon Riggs wrote: The last few days the CVS server seems to be much slower than it used to be. No network changes here. Anything changed server side, or should I ask elsewhere? I'm logged on daily interactively, and haven't noticed any issues ... Bruce has, in the past, noticed an issue that if he wipes out and re-checks out the source, it seems to clear up ... Are you both referring to the same server? I've noticed that anoncvs.postgresql.org (66.98.251.159) has been slow for a couple of days -- it just took over five minutes to do a cvs update of HEAD where it usually takes thirty seconds or less. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Improving ALTER TYPE support
I've been working on a function which returns a setof a composite type. Everytime I've changed the structure of the returning setof, I've had to change the type accordingly, which current means doing a drop type ... cascade down to the function. We should allow one of the following: 1) Add a REPLACE on CREATE TYPE (create or replace type)2) Allow ALTER TYPE to alter the definition of the type (currently it only allows changes to schema and ownership)It may get tricky with the first option because there'd be orphaned dependencies on objects which reference that particular type while it's being re-created. I'll work on a patch according to feedback.
Re: [HACKERS] Improving ALTER TYPE support
John, I've been working on a function which returns a setof a composite type. Everytime I've changed the structure of the returning setof, I've had to change the type accordingly, which current means doing a drop type ... cascade down to the function. We should allow one of the following: Why not go all the way and work out a way to define an SRF return type as a part of the function? e.g. CREATE OR REPLACE FUNCTION some_srf ( param1 int, param2 text ) RETURNS some_srf ( col1 int, col2 numeric ) AS Then the replace function would automatically rebuild the type. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Why is CVS server so slow?
Michael, Are you both referring to the same server? I've noticed that anoncvs.postgresql.org (66.98.251.159) has been slow for a couple of days -- it just took over five minutes to do a cvs update of HEAD where it usually takes thirty seconds or less. Marc's been building the 8.1.4 et. al. releases. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] LIKE, leading percent, bind parameters and indexes
Rodrigo Hjort [EMAIL PROTECTED] writes: What happens is that only the 004 block uses the index! The 002 code, which also has no leading percent, does a sequential scan. The difference between them is that 002 uses bind parameters. Yeah. The LIKE index optimization depends on seeing a constant LIKE pattern at plan time --- otherwise the planner doesn't know what indexscan parameters to generate. So a bound-parameter query loses. Ideas for improving this situation are welcome ... it's not an easy problem ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Why is CVS server so slow?
Michael Fuhr [EMAIL PROTECTED] writes: On Tue, May 23, 2006 at 08:30:36PM -0300, Marc G. Fournier wrote: I'm logged on daily interactively, and haven't noticed any issues ... Are you both referring to the same server? I've noticed that anoncvs.postgresql.org (66.98.251.159) has been slow for a couple of days -- it just took over five minutes to do a cvs update of HEAD where it usually takes thirty seconds or less. Yeah, I was about to say the same. The master CVS server has been OK (not great, but OK) for the last week or so; but I believe the folks complaining are using anoncvs. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Improving ALTER TYPE support
Makes more sense to do that, and I think it'll be cleaner to implement as well.On 5/23/06, Josh Berkus josh@agliodbs.com wrote:John, I've been working on a function which returns a setof a composite type. Everytime I've changed the structure of the returning setof, I've had to change the type accordingly, which current means doing a drop type ... cascade down to the function. We should allow one of the following: Why not go all the way and work out a way to define an SRF return type as apart of the function?e.g.CREATE OR REPLACE FUNCTION some_srf ( param1 int, param2 text )RETURNS some_srf ( col1 int, col2 numeric ) AS Then the replace function would automatically rebuild the type.--Josh BerkusPostgreSQL @ SunSan Francisco---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Performance Issues
Thank you for your help. I found that an implicit index is created for the primary key in the current version. However, it is not done in 7.x version. Mark Woodward wrote: Dhanaraj M wrote: I have the following doubts. 1. Does postgres create an index on every primary key? Usually, queries are performed against a table on the primary key, so, an index on it will be very useful. Yes, a unique index is used to enforce the primary-key. Well, here is an interesting question that I have suddenly become very curious of, if you have a primary key, obviously a unique index, is it, in fact, use this index regardless of analyzing the table? 2. If 'm executing a complex query and it takes 10 seconds to return the results -- it takes 10 seconds to execute the next time also. I'm wondering if there's any kind of caching that can be enabled -- so, the next time it takes 10 seconds to return the results. Not of query results. Obviously data itself might be cached. You might want to look at memcached for this sort of thing. I am looking at this string of posts and it occurs to me that he should run analyze. Maybe I'm jumping at the wrong point. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Performance Issues
Thank you for your help. I found that an implicit index is created for the primary key in the current version. However, it is not done in 7.x version. It absolutely is created in all 7.x versions of PostgreSQL. ---(end of broadcast)--- TIP 1: 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] Performance Issues
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Thank you for your help. I found that an implicit index is created for the primary key in the current version. However, it is not done in 7.x version. It absolutely is created in all 7.x versions of PostgreSQL. And every other version too. PRIMARY KEY/UNIQUE syntax was not supported before this patch: 1997-12-04 18:07 thomas * src/backend/parser/: analyze.c, gram.y: Add SQL92-compliant syntax for constraints. Implement PRIMARY KEY and UNIQUE clauses using indices. and in that patch and every subsequent version, unique constraints are associated with indexes. In fact, we do not even *have* any implementation method for unique constraints other than the duplicate- entry-detection code in the btree index AM. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
