Re: [HACKERS] Proposal: Snapshot cloning
Ühel kenal päeval, N, 2007-01-25 kell 22:19, kirjutas Jan Wieck: > Granted this one has a few open ends so far and I'd like to receive some > constructive input on how to actually implement it. > > The idea is to clone an existing serializable transactions snapshot > visibility information from one backend to another. The semantics would > be like this: > > backend1: start transaction; > backend1: set transaction isolation level serializable; > backend1: select pg_backend_pid(); > backend1: select publish_snapshot(); -- will block > > backend2: start transaction; > backend2: set transaction isolation level serializable; > backend2: select clone_snapshot(); -- will unblock backend1 > > backend1: select publish_snapshot(); > > backend3: start transaction; > backend3: set transaction isolation level serializable; > backend3: select clone_snapshot(); > > ... > > This will allow a number of separate backends to assume the same MVCC > visibility, so that they can query independent but the overall result > will be according to one consistent snapshot of the database. I see uses for this in implementing query parallelism in user level code, like querying two child tables in two separate processes. > What I try to accomplish with this is to widen a bottleneck, many > current Slony users are facing. The initial copy of a database is > currently limited to one single reader to copy a snapshot of the data > provider. With the above functionality, several tables could be copied > in parallel by different client threads, feeding separate backends on > the receiving side at the same time. I'm afraid that for most configurations this would make the copy slower, as there will be mode random disk i/o. Maybe better fix slony so that it allows initial copies in different parallel transactions, or just do initial copy in several sets and merge the sets later. > The feature could also be used by a parallel version of pg_dump as well > as data mining tools. > > The cloning process needs to make sure that the clone_snapshot() call is > made from the same DB user in the same database as corresponding > publish_snapshot() call was done. Why ? Snapshot is universal and same for whole db instance, so why limit it to same user/database ? > Since publish_snapshot() only > publishes the information, it gained legally and that is visible in the > PGPROC shared memory (xmin, xmax being the crucial part here), there is > no risk of creating a snapshot for which data might have been removed by > vacuum already. > > What I am not sure about yet is what IPC method would best suit the > transfer of the arbitrarily sized xip vector. Ideas? > > > Jan > -- 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 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] Proposal: Commit timestamp
On 1/25/2007 11:41 PM, Bruce Momjian wrote: Jan Wieck wrote: On 1/25/2007 6:49 PM, Tom Lane wrote: > Jan Wieck <[EMAIL PROTECTED]> writes: >> To provide this data, I would like to add another "log" directory, >> pg_tslog. The files in this directory will be similar to the clog, but >> contain arrays of timestamptz values. > > Why should everybody be made to pay this overhead? It could be made an initdb time option. If you intend to use a product that requires this feature, you will be willing to pay that price. That is going to cut your usage by like 80%. There must be a better way. I'd love to. But it is a datum that needs to be collected at the moment where basically the clog entry is made ... I don't think any external module can do that ever. You know how long I've been in and out and back into replication again. The one thing that pops up again and again in all the scenarios is "what the heck was the commit order?". Now the pure commit order for a single node could certainly be recorded from a sequence, but that doesn't cover the multi-node environment I am after. That's why I want it to be a timestamp with a few fudged bits at the end. If you look at what I've described, you will notice that as long as all node priorities are unique, this timestamp will be a globally unique ID in a somewhat ascending order along a timeline. That is what replication people are looking for. Tom fears that the overhead is significant, which I do understand and frankly, wonder myself about (actually I don't even have a vague estimate). I really think we should make this thing an initdb option and decide later if it's on or off by default. Probably we can implement it even in a way that one can turn it on/off and a postmaster restart plus waiting the desired freeze-delay would do. What I know for certain is that no async replication system can ever do without the commit timestamp information. Using the transaction start time or even the single statements timeofday will only lead to inconsistencies all over the place (I haven't been absent from the mailing lists for the past couple of month hiding in my closet ... I've been experimenting and trying to get around all these issues - in my closet). Slony-I can survive without that information because everything happens on one node and we record snapshot information for later abusal. But look at what cost we are dealing with this rather trivial issue. All we need to know is the serializable commit order. And we have to issue queries that eventually might exceed address space limits? 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Commit timestamp
Jan Wieck wrote: > On 1/25/2007 6:49 PM, Tom Lane wrote: > > Jan Wieck <[EMAIL PROTECTED]> writes: > >> To provide this data, I would like to add another "log" directory, > >> pg_tslog. The files in this directory will be similar to the clog, but > >> contain arrays of timestamptz values. > > > > Why should everybody be made to pay this overhead? > > It could be made an initdb time option. If you intend to use a product > that requires this feature, you will be willing to pay that price. That is going to cut your usage by like 80%. There must be a better way. -- Bruce Momjian [EMAIL PROTECTED] 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] Autovacuum Improvements
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Jim C. Nasby wrote: > >> If we extended relations by more than one page at a time we'd probably > >> have a better shot at the blocks on disk being contiguous and all read > >> at the same time by the OS. > > > Actually, there is evidence that adding only a single page to the end > > causes a lot of contention for that last page, and that adding a few > > might be better. > > Evidence where? The code is designed so that the last page *isn't* > shared --- go read the comments in hio.c sometime. I was talking about the last page of a table, where INSERTs all cluster on that last page and cause lots of page locking. hio.c does look like it avoids that problem. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Autovacuum Improvements
Bruce Momjian <[EMAIL PROTECTED]> writes: > Jim C. Nasby wrote: >> If we extended relations by more than one page at a time we'd probably >> have a better shot at the blocks on disk being contiguous and all read >> at the same time by the OS. > Actually, there is evidence that adding only a single page to the end > causes a lot of contention for that last page, and that adding a few > might be better. Evidence where? The code is designed so that the last page *isn't* shared --- go read the comments in hio.c sometime. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Proposal: Snapshot cloning
Granted this one has a few open ends so far and I'd like to receive some constructive input on how to actually implement it. The idea is to clone an existing serializable transactions snapshot visibility information from one backend to another. The semantics would be like this: backend1: start transaction; backend1: set transaction isolation level serializable; backend1: select pg_backend_pid(); backend1: select publish_snapshot(); -- will block backend2: start transaction; backend2: set transaction isolation level serializable; backend2: select clone_snapshot(); -- will unblock backend1 backend1: select publish_snapshot(); backend3: start transaction; backend3: set transaction isolation level serializable; backend3: select clone_snapshot(); ... This will allow a number of separate backends to assume the same MVCC visibility, so that they can query independent but the overall result will be according to one consistent snapshot of the database. What I try to accomplish with this is to widen a bottleneck, many current Slony users are facing. The initial copy of a database is currently limited to one single reader to copy a snapshot of the data provider. With the above functionality, several tables could be copied in parallel by different client threads, feeding separate backends on the receiving side at the same time. The feature could also be used by a parallel version of pg_dump as well as data mining tools. The cloning process needs to make sure that the clone_snapshot() call is made from the same DB user in the same database as corresponding publish_snapshot() call was done. Since publish_snapshot() only publishes the information, it gained legally and that is visible in the PGPROC shared memory (xmin, xmax being the crucial part here), there is no risk of creating a snapshot for which data might have been removed by vacuum already. What I am not sure about yet is what IPC method would best suit the transfer of the arbitrarily sized xip vector. Ideas? 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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal: Commit timestamp
On 1/25/2007 8:42 PM, Richard Troy wrote: On Thu, 25 Jan 2007, Jan Wieck wrote: For a future multimaster replication system, I will need a couple of features in the PostgreSQL server itself. I will submit separate proposals per feature so that discussions can be kept focused on one feature per thread. Hmm... "will need" ... Have you prototyped this system yet? ISTM you can prototype your proposal using "external" components so you can work out the kinks first. These details are pretty drilled down and are needed with the described functionality. And I will not make the same mistake as with Slony-I again and develop things, that require backend support, as totally external (look at the catalog corruption mess I created there and you know what I'm talking about). 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 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal: Commit timestamp
On Thu, 25 Jan 2007, Jan Wieck wrote: > > For a future multimaster replication system, I will need a couple of > features in the PostgreSQL server itself. I will submit separate > proposals per feature so that discussions can be kept focused on one > feature per thread. Hmm... "will need" ... Have you prototyped this system yet? ISTM you can prototype your proposal using "external" components so you can work out the kinks first. Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal: Change of pg_trigger.tg_enabled and adding
On 1/25/2007 7:33 PM, Tom Lane wrote: 1 fires always 0 fires never N fires in "Normal" mode R fires in "Replica" mode other letters available for other future mode values? If you consistently think of "origin" and "replica" modes then the original proposal is better (using both 0 and O would be Real Bad), but your use of "normal" and "replica" in the followup makes me wonder which terminology is more common. Yeah, I tried for a long time to stay away from terms like master and slave ... but in the end people don't understand you if you talk about origin and subscriber or replica. That's how this inconsistent terminology slipped into my vocabulary. I personally don't care about the particular values. I could live with A, B, C, D. If people find 1, 0, N, R more explanatory, fine. 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: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal: Commit timestamp
On 1/25/2007 7:41 PM, Tom Lane wrote: Jan Wieck <[EMAIL PROTECTED]> writes: On 1/25/2007 6:47 PM, Neil Conway wrote: Would this feature have any use beyond the specific project/algorithm you have in mind? The tablelog project on pgfoundry currently uses the transactions start time but would be very delighted to have the commit time available instead. BTW, it's not clear to me why you need a new log area for this. (We don't log transaction start time anywhere, so certainly tablelog's needs would not include it.) Commit timestamps are available from WAL commit records in a crash-and-restart scenario, so wouldn't that be enough? First, I need the timestamp of the original transaction that caused the data to change, which can be a remote or a local transaction. So the timestamp currently recorded in the WAL commit record is useless and the commit record has to be extended by one more timestamp. Second, I don't think that an API scanning for WAL commit records by xid would be efficient enough to satisfy the needs of a timestamp based conflict resolution system, which would have to retrieve the timestamp for every rows xmin that it is about to update in order to determine if the old or the new values should be used. Third, keeping the timestamp information in the WAL only would require to keep the WAL segments around until they are older than the admin chosen minimum freeze age. I hope you don't want to force that penalty on everyone who intends to use multimaster replication. 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 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] Proposal: Commit timestamp
On 1/25/2007 6:49 PM, Tom Lane wrote: Jan Wieck <[EMAIL PROTECTED]> writes: To provide this data, I would like to add another "log" directory, pg_tslog. The files in this directory will be similar to the clog, but contain arrays of timestamptz values. Why should everybody be made to pay this overhead? It could be made an initdb time option. If you intend to use a product that requires this feature, you will be willing to pay that price. The COMMIT syntax will get extended to COMMIT [TRANSACTION] [WITH TIMESTAMP ]; The extension is limited to superusers and will override the normally generated commit timestamp. This will be used to give the replicating transaction on the replica the exact same timestamp it got on the originating master node. I'm not convinced you've even thought this through. If you do that then you have no guarantee of commit timestamp monotonicity on the slave (if it has either multi masters or any locally generated transactions). Since this is supposedly for a multi-master system, that seems a rather fatal objection --- no node in the system will actually have commit timestamp monotonicity. What are you hoping to accomplish with this? Maybe I wasn't clear enough about this. If the commit timestamps on the local machine are guaranteed to increase at least by one millisecond (okay that limits the system to a sustained 1000 commits per second before it really seems to run ahead of time), then no two commits on the same instance will ever have the same timestamp. If furthermore each instance in a cluster has a distinct priority (the microsecond part added to the millisecond-truncated timestamp), each commit timestamp could even act as a globally unique ID. It does require that all the nodes in the cluster are configured with a distinct priority. What I hope to accomplish with this is a very easy, commit time based "last update wins" conflict resolution for data fields of the overwrite nature. The replication system I have in mind will have another field type of the balance nature, where it will never communicate the current value but only deltas that get applied regardless of the two timestamps. 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: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal: Commit timestamp
Jan Wieck <[EMAIL PROTECTED]> writes: > On 1/25/2007 6:47 PM, Neil Conway wrote: >> Would this feature have any use beyond the specific project/algorithm >> you have in mind? > The tablelog project on pgfoundry currently uses the transactions start > time but would be very delighted to have the commit time available instead. BTW, it's not clear to me why you need a new log area for this. (We don't log transaction start time anywhere, so certainly tablelog's needs would not include it.) Commit timestamps are available from WAL commit records in a crash-and-restart scenario, so wouldn't that be enough? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Change of pg_trigger.tg_enabled and adding pg_rewrite.ev_enabled
Jan Wieck <[EMAIL PROTECTED]> writes: >>> A fires always >>> N fires never >>> O fires on transaction origin only >>> R fires on replica only > Good question. I don't know. I'd rather error on the safe side and make > it multiple states, for now I only have Normal and Replica mode. Hm, "N" up there seems easily confused with "Normal". Perhaps a less mistake-prone coding would be 1 fires always 0 fires never N fires in "Normal" mode R fires in "Replica" mode other letters available for other future mode values? If you consistently think of "origin" and "replica" modes then the original proposal is better (using both 0 and O would be Real Bad), but your use of "normal" and "replica" in the followup makes me wonder which terminology is more common. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal: Commit timestamp
On 1/25/2007 6:47 PM, Neil Conway wrote: On Thu, 2007-01-25 at 18:16 -0500, Jan Wieck wrote: For conflict resolution purposes in an asynchronous multimaster system, the "last update" definition often comes into play. For this to work, the system must provide a monotonically increasing timestamp taken at the commit of a transaction. Do you really need an actual timestamptz derived from the system clock, or would a monotonically increasing 64-bit counter be sufficient? (The assumption that the system clock is monotonically increasing seems pretty fragile, in the presence of manual system clock changes, ntpd, etc.) Yes, I do need it to be a timestamp, and one assumption is that all servers in the multimaster cluster are ntp synchronized. The reason is that this is for asynchronous multimaster (in my case). Two sequences running on separate systems don't tell which was the "last update" on a timeline. This conflict resolution method alone is of course completely inadequate. Comments, changes, additions? Would this feature have any use beyond the specific project/algorithm you have in mind? The tablelog project on pgfoundry currently uses the transactions start time but would be very delighted to have the commit time available instead. 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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] No ~ operator for box, point
Martijn van Oosterhout writes: > On Thu, Jan 25, 2007 at 01:59:33PM -0500, Merlin Moncure wrote: >> On 1/25/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote: >>> decibel=# select box '((0,0),(2,2))' ~ point '(1,1)'; >>> ERROR: operator does not exist: box ~ point >> >> I don't see a reason, although you can do it with polygon and not box. Seems like an old oversight. >> Also, I can't find the ~ operator defined for polygon in the >> documentation, am I missing something? ~ is deprecated, "contains" is preferentially spelled @> now. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal: Change of pg_trigger.tg_enabled and adding
On 1/25/2007 6:55 PM, Tom Lane wrote: Jan Wieck <[EMAIL PROTECTED]> writes: The value definitions of tg_enabled would be A fires always N fires never O fires on transaction origin only R fires on replica only A new per session GUC variable, restricted to superusers, will define if the session is in origin or replica mode. Are you sure two states are enough? Good question. I don't know. I'd rather error on the safe side and make it multiple states, for now I only have Normal and Replica mode. No particular objection, but now would be the time to think if a boolean is sufficient. Likewise the system catalog pg_rewrite is extended with an attribute ev_enabled. It will have the same possible values and a new command, I assume there'd be no intention of supporting on-the-fly changes of this setting (ie, you'd set the GUC variable once at session startup and not change thereafter)? Otherwise you'd have a problem with cached plans. This is indeed the intended use pattern. Since it is restricted to superusers, I don't see a particular reason why to enforce it in the system though. 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 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] Proposal: Change of pg_trigger.tg_enabled and adding pg_rewrite.ev_enabled
Jan Wieck <[EMAIL PROTECTED]> writes: > The value definitions of tg_enabled would be > A fires always > N fires never > O fires on transaction origin only > R fires on replica only > A new per session GUC variable, restricted to superusers, will define if > the session is in origin or replica mode. Are you sure two states are enough? No particular objection, but now would be the time to think if a boolean is sufficient. > Likewise the system catalog pg_rewrite is extended with an attribute > ev_enabled. It will have the same possible values and a new command, I assume there'd be no intention of supporting on-the-fly changes of this setting (ie, you'd set the GUC variable once at session startup and not change thereafter)? Otherwise you'd have a problem with cached plans. 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] Proposal: Commit timestamp
Jan Wieck <[EMAIL PROTECTED]> writes: > To provide this data, I would like to add another "log" directory, > pg_tslog. The files in this directory will be similar to the clog, but > contain arrays of timestamptz values. Why should everybody be made to pay this overhead? > The COMMIT syntax will get extended to > COMMIT [TRANSACTION] [WITH TIMESTAMP ]; > The extension is limited to superusers and will override the normally > generated commit timestamp. This will be used to give the replicating > transaction on the replica the exact same timestamp it got on the > originating master node. I'm not convinced you've even thought this through. If you do that then you have no guarantee of commit timestamp monotonicity on the slave (if it has either multi masters or any locally generated transactions). Since this is supposedly for a multi-master system, that seems a rather fatal objection --- no node in the system will actually have commit timestamp monotonicity. What are you hoping to accomplish with this? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal: Commit timestamp
On Thu, 2007-01-25 at 18:16 -0500, Jan Wieck wrote: > For conflict resolution purposes in an asynchronous multimaster system, > the "last update" definition often comes into play. For this to work, > the system must provide a monotonically increasing timestamp taken at > the commit of a transaction. Do you really need an actual timestamptz derived from the system clock, or would a monotonically increasing 64-bit counter be sufficient? (The assumption that the system clock is monotonically increasing seems pretty fragile, in the presence of manual system clock changes, ntpd, etc.) > Comments, changes, additions? Would this feature have any use beyond the specific project/algorithm you have in mind? -Neil ---(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: [pgsql-patches] [HACKERS] unprivileged pl install
On Wed, 24 Jan 2007, Tom Lane wrote: > * For an untrusted language: must be superuser to either create or use > the language (no change from current rules). Ownership of the > pg_language entry is really irrelevant, as is its ACL. > > * For a trusted language: > > * if pg_pltemplate.something is ON: either a superuser or the current > DB's owner can CREATE the language. In either case the pg_language > entry will be marked as owned by the DB owner (pg_database.datdba), > which means that subsequently he (or a superuser) can grant or deny > USAGE within his DB. What happens on ALTER DATABASE ALTER OWNER? Does the ownership of the language change to the new datdba or stay the old one? If the CREATE LANGUAGE results in creating the handler and validation funcs, who should own them? At the moment it is the user doing the CREATE LANGUAGE, but what does that mean? Can they then do odd things to the permissions of the procs, such as denying execute on them, to break other user's usage of the language, or does the perms on a language pre-empt the perms on the func? What happens if pg_pltemplate.something changes after the language is created? The datdba would continue to own the language, and can change permissions and drop it, but could not recreate it. I assume if the superuser wanted to revoke the ability for database owners to create that language they would remove it from people's databases who already have it. > > * if pg_pltemplate.something is OFF: must be superuser to CREATE the > language; subsequently it will be owned by you, so only you or another > superuser can grant or deny USAGE (same behavior as currently). What if pg_pltemplate.something is OFF, the language is CREATEd by a superuser, and then pg_pltemplate is set ON? The language is now owned by a superuser, so the db owner could not manipulate it. The patch I put together adds an owner to pg_language. Should there be an ALTER LANGUAGE OWNER TO command added as well. Thinking about these conditions I have described here, it seems to me there should be. Or there could not be an owner for a language and who the owner is depends on the conditions listed. But then permissions checks for languages would depend on pg_pltemplate, which seems less than clear or ideal to me. Besides which, when the acl is initalized from NULL to a value, it depends on who the owner is. It would need to be changed as well when the owner changing conditions change. I think that an ALTER LANGUAGE OWNER TO is the proper response to these things, and unless I hear otherwise I will attempt to add this to my patch. -- Checkuary, n.: The thirteenth month of the year. Begins New Year's Day and ends when a person stops absentmindedly writing the old year on his checks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Proposal: Change of pg_trigger.tg_enabled and adding pg_rewrite.ev_enabled
The experience with Slony-I has shown that a) different behavior of triggers and rules on a transactions origin and a replica is essential; b) mucking around with the system catalog to achieve this is futile. This would be even more catastrophic in a multimaster environment, where regular transaction origin and replica behavior are required on a per session level concurrently. To achieve the required flexibility, we need to change the definition of the pg_trigger attribute tg_enabled. It currently is a boolean. I would like to change it into a char along with the syntax of ALTER TRIGGER. The value definitions of tg_enabled would be A fires always N fires never O fires on transaction origin only R fires on replica only Anyone preferences how to map that to ALTER TRIGGER? A new per session GUC variable, restricted to superusers, will define if the session is in origin or replica mode. Likewise the system catalog pg_rewrite is extended with an attribute ev_enabled. It will have the same possible values and a new command, ALTER RULE, will match the functionality of ALTER TRIGGER. 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 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] Proposal: Commit timestamp
For a future multimaster replication system, I will need a couple of features in the PostgreSQL server itself. I will submit separate proposals per feature so that discussions can be kept focused on one feature per thread. For conflict resolution purposes in an asynchronous multimaster system, the "last update" definition often comes into play. For this to work, the system must provide a monotonically increasing timestamp taken at the commit of a transaction. During replication, the replication process must be able to provide the remote nodes timestamp so that the replicated data will be "as of the time it was written on the remote node", and not the current local time of the replica, which is by definition of "asynchronous" later. To provide this data, I would like to add another "log" directory, pg_tslog. The files in this directory will be similar to the clog, but contain arrays of timestamptz values. On commit, the current system time will be taken. As long as this time is lower or equal to the last taken time in this PostgreSQL instance, the value will be increased by one microsecond. The resulting time will be added to the commit WAL record and written into the pg_tslog file. If a per database configurable tslog_priority is given, the timestamp will be truncated to milliseconds and the increment logic is done on milliseconds. The priority is added to the timestamp. This guarantees that no two timestamps for commits will ever be exactly identical, even across different servers. The COMMIT syntax will get extended to COMMIT [TRANSACTION] [WITH TIMESTAMP ]; The extension is limited to superusers and will override the normally generated commit timestamp. This will be used to give the replicating transaction on the replica the exact same timestamp it got on the originating master node. The pg_tslog segments will be purged like the clog segments, after all transactions belonging to them have been stamped frozen. A frozen xid by definition has a timestamp of epoch. To ensure a system using this timestamp feature has enough time to perform its work, a new GUC variable defining an interval will prevent vacuum from freezing xid's that are younger than that. A function get_commit_timestamp(xid) returning timpstamptz will return the commit time of a transaction as recorded by this feature. Comments, changes, additions? 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: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] DROP FUNCTION failure: cache lookup failed for
Is this a TODO? --- Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > I've found a situation that causes DROP FUNCTION to fail (tested > > in 8.1.6, 8.2.1, and 8.3devel): > > Ugh ... I haven't traced this through in detail, but I'm pretty sure > the problem arises from the fact that dependency.c traces through > auto/internal dependencies before actually starting to do the deletions > (and thus before acquiring locks). Can we fix this without multiple > scans of the dependency tree (probably costing O(N^lots))? > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] 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] Piggybacking vacuum I/O
Is there a TODO here? --- Heikki Linnakangas wrote: > Pavan Deolasee wrote: > > Another simpler solution for VACUUM would be to read the entire CLOG file > > in local memory. Most of the transaction status queries can be satisfied > > from > > this local copy and the normal CLOG is consulted only when the status is > > unknown (TRANSACTION_STATUS_IN_PROGRESS) > > The clog is only for finished (committed/aborted/crashed) transactions. > If a transaction is in progress, the clog is never consulted. Anyway, > that'd only be reasonable for vacuums, and I'm actually more worried if > we had normal backends thrashing the clog buffers. > > -- >Heikki Linnakangas >EnterpriseDB http://www.enterprisedb.com > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED] 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
Re: [HACKERS] No ~ operator for box, point
On Thu, Jan 25, 2007 at 01:59:33PM -0500, Merlin Moncure wrote: > On 1/25/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > >decibel=# select version(); > > PostgreSQL 8.3devel on i386-apple-darwin8.8.2, compiled by GCC > > i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5363) > > > >decibel=# select box '((0,0),(2,2))' ~ point '(1,1)'; > >ERROR: operator does not exist: box ~ point > >LINE 1: select box '((0,0),(2,2))' ~ point '(1,1)'; > > I don't see a reason, although you can do it with polygon and not box. > Also, I can't find the ~ operator defined for polygon in the > documentation, am I missing something? I ran into this the other day too. But box ~ box does exist, which worked for my purposes, but it did surprise me. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] [GENERAL] Autovacuum Improvements
Jim C. Nasby wrote: > On Mon, Jan 22, 2007 at 12:17:39PM -0800, Ron Mayer wrote: > > Gregory Stark wrote: > > > > > > Actually no. A while back I did experiments to see how fast reading a file > > > sequentially was compared to reading the same file sequentially but > > > skipping > > > x% of the blocks randomly. The results were surprising (to me) and > > > depressing. > > > The breakeven point was about 7%. [...] > > > > > > The theory online was that as long as you're reading one page from each > > > disk > > > track you're going to pay the same seek overhead as reading the entire > > > track. > > > > Could one take advantage of this observation in designing the DSM? > > > > Instead of a separate bit representing every page, having each bit > > represent 20 or so pages might be a more useful unit. It sounds > > like the time spent reading would be similar; while the bitmap > > would be significantly smaller. > > If we extended relations by more than one page at a time we'd probably > have a better shot at the blocks on disk being contiguous and all read > at the same time by the OS. Actually, there is evidence that adding only a single page to the end causes a lot of contention for that last page, and that adding a few might be better. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] BUG #2917: spi_prepare doesn't accept typename aliases
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: I dug into it a bit and found that pltcl and plpython appear to use almost identical code, but only pltcl has this limitation documented. I'm inclined to say we should document this for plperl and plpython for stable releases and remove the limitation for all three for 8.3. I see that SQL level prepare calls regprocin() to resolve type names, so maybe we should that for the PLs when calling SPI_prepare as well. I think parseTypeString() may be the thing to use. It's what plpgsql uses... OK, I'll see what I can do. cheers andrew. ---(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] autovacuum process handling
Markus Schiltknecht wrote: Hi Markus, > > Alvaro Herrera wrote: > >1. There will be two kinds of processes, "autovacuum launcher" and > >"autovacuum worker". > > Sounds similar to what I do in Postgres-R: one replication manager and > several "replication workers". Those are called "remote backends" (which > is somewhat of an unfortunate name, IMO.) Oh, yeah, I knew about those and forgot to check them. > >6. Launcher will start a worker using the following protocol: > > - Set up information on what to run on shared memory > > - invoke SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_WORKER) > > - Postmaster will react by starting a worker, and registering it very > > similarly to a regular backend, so it can be shut down easily when > > appropriate. > > (Thus launcher will not be informed right away when worker dies) > > - Worker will examine shared memory to know what to do, clear the > > request, and send a signal to Launcher > > - Launcher wakes up and can start another one if appropriate > > It looks like you need much less communication between the launcher and > the workers, probably also less between the postmaster and the launcher. Yeah. For what I need, the launcher just needs to know when a worker has finished and how many workers there are. > For Postgres-R, I'm currently questioning if I shouldn't merge the > replication manager process with the postmaster. Of course, that would > violate the "postmaster does not touch shared memory" constraint. I suggest you don't. Reliability from Postmaster is very important. > But it would make some things a lot easier: > > * What if the launcher/manager dies (but you potentially still have >active workers)? > >Maybe, for autovacuum you can simply restart the launcher and that >one detects workers from shmem. > >With replication, I certainly have to take down the postmaster as >well, as we are certainly out of sync and can't simply restart the >replication manager. So in that case, no postmaster can run without a >replication manager and vice versa. Why not make it one single >process, then? Well, the point of the postmaster is that it can notice when one process dies and take appropriate action. When a backend dies, the postmaster closes all others. But if the postmaster crashes due to a bug in the manager (due to both being integrated in a single process), how do you close the backends? There's no one to do it. When the logger process dies, postmaster just starts a new one. But when the bgwriter dies, it must cause an restart cycle as well. The postmaster knows what process dies, so it knows how to act. If the manager dies, the postmaster is certainly able to stop all other processes and restart the whole thing. In my case, the launcher is not critical. It can die and the postmaster should just start a new one without much noise. A worker is critical because it's connected to tables; it's as critical as a regular backend. So if a worker dies, the postmaster must take everyone down and cause a restart. This is pretty easy to do. > * Startup races: depending on how you start workers, the launcher/ >manager may get a "database is starting up" error when requesting >the postmaster to fork backends. >That probably also applies to autovacuum, as those workers shouldn't >work concurrently to a startup process. But maybe there are other >means of ensuring that no autovacuum gets triggered during startup? Oh, this is very easy as well. In my case the launcher just sets a database OID to be processed in shared memory, and then calls SendPostmasterSignal with a particular value. The postmaster must only check this signal within ServerLoop, which means it won't act on it (i.e., won't start a worker) until the startup process has finished. The worker is very much like a regular backend. It starts up, and then checks this shared memory. If there's a database OID in there, it removes the OID from shared memory, then connects to the database and does a vacuum cycle. > * Simpler debugging: one process less which could fail, and a whole lot >of concurrency issues (like deadlocks or invalid IPC messages) are >gone. I guess your problem is that the manager's task is quite a lot more involved than my launcher's. But in that case, it's even more important to have them separate. I don't understand why the manager talks to postmaster. If it doesn't, well, then there's no concurrency issue gone, because the remote backends will be talking to *somebody* anyway; be it postmaster, or manager. (Maybe your problem is that the manager is not correctly designed. We can talk about checking that code. I happen to know the Postmaster process handling code because of my previous work with Autovacuum and because of Mammoth Replicator.) > So, why do you want to add a special launcher process? Why can't the > postmaster take care of launching autovacuum worker
Re: [HACKERS] BUG #2917: spi_prepare doesn't accept typename aliases
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: I see that SQL level prepare calls regprocin() to resolve type names, so maybe we should that for the PLs when calling SPI_prepare as well. Of course, that should be regtypein() [ squint... ] build_regtype_array seems a rather stupid bit of code. How many hundreds of cycles is it expending to convert an OID to an OID? Yeah, you're right. I should have squinted too ;-) Well, I am open to suggestions on what to do about this. I really think we should support use of standard type aliases in the PLs. cheers andrew ---(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] BUG #2917: spi_prepare doesn't accept typename aliases such as 'integer'
Andrew Dunstan <[EMAIL PROTECTED]> writes: > I dug into it a bit and found that pltcl and plpython appear to use > almost identical code, but only pltcl has this limitation documented. > I'm inclined to say we should document this for plperl and plpython for > stable releases and remove the limitation for all three for 8.3. I see > that SQL level prepare calls regprocin() to resolve type names, so maybe > we should that for the PLs when calling SPI_prepare as well. I think parseTypeString() may be the thing to use. It's what plpgsql uses... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Implied Functional index use (redux)
"Simon Riggs" <[EMAIL PROTECTED]> writes: > A simpler, alternate proposal is to allow the user to specify whether a > functional index is transformable or not using CREATE or ALTER INDEX, > with a default of not transformable. That then leaves the responsibility > for specifying this with the user, who as we have seen is the really > only person really capable of judging the whole case on its merits. > e.g. CREATE INDEX fooidx ON foo (foofunc(foocol1)) > [TABLESPACE ...] [ENABLE|DISABLE TRANSFORM] [WHERE ...]; This is a foot-gun and nothing else. I hardly think the average DBA will realize such subtleties as "numeric equality doesn't guarantee that such-and-such works". If it's not specified by the datatype author it's not going to be safe. In fact, this doesn't work anyway, since it still doesn't address the question of which "equality" operators we think permit us to apply the transform. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] BUG #2917: spi_prepare doesn't accept typename aliases
Andrew Dunstan <[EMAIL PROTECTED]> writes: >> I see that SQL level prepare calls regprocin() to resolve type names, >> so maybe we should that for the PLs when calling SPI_prepare as well. > Of course, that should be regtypein() [ squint... ] build_regtype_array seems a rather stupid bit of code. How many hundreds of cycles is it expending to convert an OID to an OID? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] BUG #2917: spi_prepare doesn't accept typename aliases
I wrote: I see that SQL level prepare calls regprocin() to resolve type names, so maybe we should that for the PLs when calling SPI_prepare as well. Of course, that should be regtypein() cheers andrew ---(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] Scanner/Parser question - what does _P imply?
> > Some years ago there was discussion of consistently P-ifying *all* those > > macros, but it didn't get done; I think Thomas or somebody objected that > > it would make gram.y needlessly harder to read. > > Are there many people who read gram.y on a regular base? I can't seem to put it down :-) >From the back cover: A rollercoaster ride of passion, heart-stopping adventures, and gut-wrenching laughs ... every bit as thrilling as copyfuncs.c, more of a tearjerker than bufmgr.c, and as deliciously naughty as MySQL's item.cc. Get gram.y, in stores now (or order at Amazon.com, delivered in a plain brown wrapper). -- Korry
Re: [HACKERS] Scanner/Parser question - what does _P imply?
On 1/18/2007 10:35 AM, Tom Lane wrote: <[EMAIL PROTECTED]> writes: Many of the keywords listed in keywords.c are defined with symbolic names that end in '_P' (underscore P). What differentiates those keywords from the other keywords? What does the 'P' stand for? P = Parser. The reason for the _P is just to avoid conflicts with other definitions of the macro name, either in our own code or various platforms' header files. We haven't been totally consistent about it, but roughly speaking we've stuck _P on when it was either known or seemed likely that there might be a conflict. Some years ago there was discussion of consistently P-ifying *all* those macros, but it didn't get done; I think Thomas or somebody objected that it would make gram.y needlessly harder to read. Are there many people who read gram.y on a regular base? 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 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] BUG #2917: spi_prepare doesn't accept typename aliases such as 'integer'
The author of this bug was good enough to send me a copy, since I don't normally read the -bugs list. It can now be found at http://archives.postgresql.org/pgsql-bugs/2007-01/msg00111.php . I dug into it a bit and found that pltcl and plpython appear to use almost identical code, but only pltcl has this limitation documented. I'm inclined to say we should document this for plperl and plpython for stable releases and remove the limitation for all three for 8.3. I see that SQL level prepare calls regprocin() to resolve type names, so maybe we should that for the PLs when calling SPI_prepare as well. Alternatively, should we adjust things lower down (e.g. in typenameType() or LookupTypeName() ) ? Comments? cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] No ~ operator for box, point
On 1/25/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote: decibel=# select version(); PostgreSQL 8.3devel on i386-apple-darwin8.8.2, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5363) decibel=# select box '((0,0),(2,2))' ~ point '(1,1)'; ERROR: operator does not exist: box ~ point LINE 1: select box '((0,0),(2,2))' ~ point '(1,1)'; I don't see a reason, although you can do it with polygon and not box. Also, I can't find the ~ operator defined for polygon in the documentation, am I missing something? merlin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Implied Functional index use (redux)
In a thread in July last year, I raised the possibility of transforming a query to allow functional indexes to be utilised automatically. http://archives.postgresql.org/pgsql-hackers/2006-07/msg00323.php This idea can work and has many benefits, but there are some complexities. I want to summarise those issues first, then make a more practical and hopefully more acceptable proposal. Taken together the complexities would have lead us to have additional TRANSFORMABLE clauses on TYPEs, FUNCTIONs and potentially encoding schemes. All of which, I agree, just too much complexity to allow this to be specified. One example of this was FLOAT, where -0 and +0 are equal but not the same in a binary form. That would normally mean we couldn't use FLOAT for TRANSFORMABLE indexes, but of course what happens if we specify a partial functional index, where we only index values > 0. In that case, we *can* use the transform technique again. Worse still we may have a full (non-partial) index where there is a constraint on the column(s) such as CHECK (value > 0). So we'd need another heavy dose of catalog-complexity to catch all the special cases. Yuck and double Yuck. Even if we did that, it isn't easy for a data type author to tell whether their type is transformable, or not **in all cases**. That would probably lead to people saying DISABLE TRANSFORM for their data type, just in case. Which means no benefit in practice with this feature. - - - A simpler, alternate proposal is to allow the user to specify whether a functional index is transformable or not using CREATE or ALTER INDEX, with a default of not transformable. That then leaves the responsibility for specifying this with the user, who as we have seen is the really only person really capable of judging the whole case on its merits. e.g. CREATE INDEX fooidx ON foo (foofunc(foocol1)) [TABLESPACE ...] [ENABLE|DISABLE TRANSFORM] [WHERE ...]; ENABLE TRANSFORM is only possible for functional indexes. Suggestions for better syntax/naming welcome. Placing the TRANSFORM clause on the index as a simple boolean makes utilising the feature more streamlined at planning time too. This would be an extra initial check in create_index_paths() to see if the query might benefit from transform. Most indexable WHERE clauses would be able to be transformed, if the index allows. The feature would be enabled by default with a GUC, but as stated above, the default for each index would be to *not* transform unless specifically requested by the user. enable_index_transform = on (default)| off EXPLAIN would not need alteration, since the modified query would show up clearly in the output. (I can add explicit visibility if people want that). Overall, a fairly isolated patch, with little user interface changes. All of the complexities would be very clearly documented as part of this feature. That is essential to avoid user error, of which I am mindful. But the technique has much promise, so I would like to make this option available to designers and DBAs. If we can agree this smoothly, then it seems possible for 8.3. Comments? -- 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] Proposal: allow installation of any contrib module
Joshua D. Drake wrote: Teodor Sigaev wrote: This might be a good idea, but it's hardly transparent; it can be counted on to break the applications of just about everyone using those modules today. Hmm, can we make separate schema for all contib modules and include it in default search_path? It will not touchs most users. Oh now that is interesting... something like a contrib schema that everything is loaded into? That would would work nicely and removes a lot of problems. People can use the modules if they want, they don't have to enable the functions but they aren't in the way of their app or db either. I think I'd rather have one schema per module than one for all of contrib, but maybe the addition to the search path makes a single schema more sensible. Another approach to backwards compatibility issue, perhaps, would have 2 install scripts per module, one that installs stuff in the public schema for legacy purposes, and one that uses a dedicated schema. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal: allow installation of any contrib module
Teodor Sigaev wrote: >> This might be a good idea, but it's hardly transparent; it can be >> counted on to break the applications of just about everyone using those >> modules today. > > Hmm, can we make separate schema for all contib modules and include it > in default search_path? It will not touchs most users. Oh now that is interesting... something like a contrib schema that everything is loaded into? That would would work nicely and removes a lot of problems. People can use the modules if they want, they don't have to enable the functions but they aren't in the way of their app or db either. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] crash on 8.2 and cvshead - failed to add item to the
Tom Lane wrote: Heikki Linnakangas <[EMAIL PROTECTED]> writes: I'm afraid the bug has been there for ages, but the 90%-fillfactor on rightmost page patch made it much more likely to get triggered. But that patch has been there for ages too; the only new thing in 8.2 is that the fillfactor is configurable, but its default is the same. So I'm still wondering why the bug isn't seen in 8.1. (Joe, did you try anything older than 8.1?) I just tried on Postgres 8.0.8 -- no crash. Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: allow installation of any contrib module
This might be a good idea, but it's hardly transparent; it can be counted on to break the applications of just about everyone using those modules today. Hmm, can we make separate schema for all contib modules and include it in default search_path? It will not touchs most users. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal: allow installation of any contrib module
Teodor Sigaev wrote: >> Why is ltree still in contrib? What prevents it from being in core? > Nothing. But I don't see any advantage of placing it in core - it > changes nothing in SQL, API or feature. Moving tsearch2 into core allows > to manage configuration with nice SQL API, using SysCache, automatical > rereading dictionaries files etc. The advantage is that *users* don't know ltree exists and when they find out they term it, "User contributed","Non official","Unsupported" because it is in contrib. I recognize that this does play into Tom's extremely valid arguments on modules/plugins and correctly documentation and advertising. Sincerely, Joshua D. Drake > > IMHO, common problem for hstore/ltree/tsearch2 is a problem with public > namespace is dump/restore. Users usually dump the whole db and restore > it in new version. Even we make new version of contrib module with the > same function, new function/feature will not be accessible. > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal: allow installation of any contrib module
> I don't think two releases from API change to API change is enough - > postgresql is running larger and larger databases by now and I expect > people to upgrade less often in the future (and iirc you already said > something along the lines of recommending such things on occasion to > your customers too). > So if you have things in core you really have to provide a stable API > over a number of releases or in practise - years. Two releases is approximately 3 years. With the exception of 8.3. Joshua D. Drake > And depending on how the API changed it might not even be possible to > stay backwards compatible so: > >> Maybe there isn't a good answer. > > Indeed there might not be one ... > > > Stefan > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] Proposal: allow installation of any contrib module
Why is ltree still in contrib? What prevents it from being in core? Nothing. But I don't see any advantage of placing it in core - it changes nothing in SQL, API or feature. Moving tsearch2 into core allows to manage configuration with nice SQL API, using SysCache, automatical rereading dictionaries files etc. IMHO, common problem for hstore/ltree/tsearch2 is a problem with public namespace is dump/restore. Users usually dump the whole db and restore it in new version. Even we make new version of contrib module with the same function, new function/feature will not be accessible. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: allow installation of any contrib module
Joshua D. Drake wrote: > Tom Lane wrote: >> "Joshua D. Drake" <[EMAIL PROTECTED]> writes: >>> Oleg Bartunov wrote: we have several requests to improve ltree, particularly, people want to expand class of allowed symbols and configurable separator, which is hard-coded right now. Also, we discussed GiN support for ltree. >>> O.k. but how does that prevent it from being in core? >> Once it's in core the expectations for API stability get a lot higher ... > > True but any API can be backward compatible for say a release while > offering the new functionality as well. I fully understand stability in > core. I certainly don't want any level of instability, even if just > syntactic. I don't think two releases from API change to API change is enough - postgresql is running larger and larger databases by now and I expect people to upgrade less often in the future (and iirc you already said something along the lines of recommending such things on occasion to your customers too). So if you have things in core you really have to provide a stable API over a number of releases or in practise - years. And depending on how the API changed it might not even be possible to stay backwards compatible so: > > Maybe there isn't a good answer. Indeed there might not be one ... Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal: allow installation of any contrib module
Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: >> Oleg Bartunov wrote: >>> we have several requests to improve ltree, particularly, people want >>> to expand class of allowed symbols and configurable separator, which is >>> hard-coded right now. Also, we discussed GiN support for ltree. > >> O.k. but how does that prevent it from being in core? > > Once it's in core the expectations for API stability get a lot higher ... True but any API can be backward compatible for say a release while offering the new functionality as well. I fully understand stability in core. I certainly don't want any level of instability, even if just syntactic. Maybe there isn't a good answer. Joshua D. Drake > > 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 > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] Proposal: allow installation of any contrib module
FWIW: > * Better packaging support, eg make it easier to add/remove an extension > module and control how pg_dump deals with it. We talked about that > awhile back but nobody did anything with the ideas. +1 > * Better documentation for the contrib modules; some of them are > reasonably well doc'd now, but many are not, and in almost all cases > it's only plain text not SGML. +1 > * Better advertising, for instance make the contrib documentation > available on the website (which probably requires SGML conversion > to happen first...) +1 RT -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal: allow installation of any contrib module
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Oleg Bartunov wrote: >> we have several requests to improve ltree, particularly, people want >> to expand class of allowed symbols and configurable separator, which is >> hard-coded right now. Also, we discussed GiN support for ltree. > O.k. but how does that prevent it from being in core? Once it's in core the expectations for API stability get a lot higher ... 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] Proposal: allow installation of any contrib module
Joshua D. Drake wrote: > Tom Lane wrote: > > Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > >> I would like to see pgcrypto (or at least some of it's > >> functionality) in core. > > > > I believe the reason we keep it separate is so that people can > > easily make crypto-free versions of PG for use in countries where > > encryption capability is considered subject to arms regulations. > > Not sure how important that case really is today, but it was a big > > consideration last time this was discussed. > > That is a good question but in reality people who need to do so, can > get the source from a mirror that is outside the country that has the > rules. The point is that vendors (consultants, distributors, nice people) can produce safe versions to distribute in or to places with crypto regulations. But since PostgreSQL contains code that links with SSL and Kerberos functionality, you need to patch the code anyway in order to do that legally. But it's nice to keep these things constrained to well-known places in the source anyway. (This opinion was sponsored by real lawyers.) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal: allow installation of any contrib module
Oleg Bartunov wrote: > On Thu, 25 Jan 2007, Joshua D. Drake wrote: > >> >>> The problem with this proposal is that the ISPs aren't the ones running >>> configure --- these days, most people are running prebuilt packages >>> (RPMs or DEBs or what have you). So what you are hoping is that the >>> packagers will choose to do this and thereby force these modules into >>> the "standard" configuration for everybody using their packages. I'm >>> not sure that the packagers will change ... well maybe Gentoo will, >>> but not anyone with more conservative policies ... and I'm pretty sure >>> any who do will get push-back from people who still won't trust contrib. >> >> Well perhaps it is time to trim Contrib even further. E.g; >> >> Why is ltree still in contrib? What prevents it from being in core? > > we have several requests to improve ltree, particularly, people want > to expand class of allowed symbols and configurable separator, which is > hard-coded right now. Also, we discussed GiN support for ltree. O.k. but how does that prevent it from being in core? Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: allow installation of any contrib module
On Thu, 25 Jan 2007, Joshua D. Drake wrote: The problem with this proposal is that the ISPs aren't the ones running configure --- these days, most people are running prebuilt packages (RPMs or DEBs or what have you). So what you are hoping is that the packagers will choose to do this and thereby force these modules into the "standard" configuration for everybody using their packages. I'm not sure that the packagers will change ... well maybe Gentoo will, but not anyone with more conservative policies ... and I'm pretty sure any who do will get push-back from people who still won't trust contrib. Well perhaps it is time to trim Contrib even further. E.g; Why is ltree still in contrib? What prevents it from being in core? we have several requests to improve ltree, particularly, people want to expand class of allowed symbols and configurable separator, which is hard-coded right now. Also, we discussed GiN support for ltree. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] No ~ operator for box, point
decibel=# select version(); PostgreSQL 8.3devel on i386-apple-darwin8.8.2, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5363) decibel=# select box '((0,0),(2,2))' ~ point '(1,1)'; ERROR: operator does not exist: box ~ point LINE 1: select box '((0,0),(2,2))' ~ point '(1,1)'; Any reason this doesn't exist? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] crash on 8.2 and cvshead - failed to add item to the
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > I'm afraid the bug has been there for ages, but the 90%-fillfactor on > rightmost page patch made it much more likely to get triggered. But that patch has been there for ages too; the only new thing in 8.2 is that the fillfactor is configurable, but its default is the same. So I'm still wondering why the bug isn't seen in 8.1. (Joe, did you try anything older than 8.1?) > To see what's going on, I added some logs to the split code to print out > the free space on both halves as calculated by findsplitloc, and the > actual free space on the pages after split. I'm seeing a discrepancy of > 4 bytes on the right half; actual space free on right page after split > is 4 bytes less than anticipated. Hm, mis-counting the positions of itempointers maybe? > That's not a big deal, but I'll take a closer look > tomorrow to see what's missing from the calculations. OK, I've got some other things to worry about, will leave it to you. 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] tsearch in core patch, for inclusion
On Thu, 25 Jan 2007, Nikolay Samokhvalov wrote: On 1/25/07, Teodor Sigaev <[EMAIL PROTECTED]> wrote: It's should clear enough for now - dump data from old db and load into new one. But dump should be without any contrib/tsearch2 related functions. Upgrading from 8.1.x to 8.2.x was not tivial because of very trivial change in API (actually not really API but the content of "pg_ts_*" tables): russian snowball stemming function was forked to 2 different ones, for koi8 and utf8 encodings. So, as I dumped my pg_ts_* tables data (to keep my tsearch2 settings), I saw errors during restoration (btw, why didn't you keep old russian stemmer function name as a synonym to koi8 variant?) -- so, I had to change my dump file manually, because I didn't manage to follow "tsearch2 best practices" sed and grep did the trick. (to use some kind of "bootstrap" script that creates tsearch2 configuration you need from default one -- using several INSERTs and UPDATEs). And there were no upgrade notes for tsearch2. This is unfair, you promised to write upgrade notes and we discussed the problem with name change before release and I rely on you. It was my fault, of course. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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] Proposal: allow installation of any contrib module
> You miss the point. Everybody knows that those laws are not too hard > to circumvent if you are willing to break the law. The question is > how hard is it for someone to distribute Postgres into one of those > countries *without* breaking any local law. We won't be making things > better if we make it difficult or impossible to put together a > crypto-free PG distribution; we'll just be cutting off some potential > users. O.k. that is certainly true. > > As Peter keeps pointing out, this urge to push everything into core is > misguided anyway. I do not believe we need to push everything into core. I do believe there is a lot of stuff that is not in core, that could be and possibly should be. > The fact that you can build useful functionality as > extension modules is a strength of PG, not a weakness, and we should > build on that strength instead of running away from it. If you ask me > we should be working on these things: I am not suggesting it is a weakness in any way. I think its great. I am just saying there appears to be several modules that really should be in core that aren't. > > * Better packaging support, eg make it easier to add/remove an extension > module and control how pg_dump deals with it. We talked about that > awhile back but nobody did anything with the ideas. > Yes that would be great. > * Better documentation for the contrib modules; some of them are > reasonably well doc'd now, but many are not, and in almost all cases > it's only plain text not SGML. > Agreed... more to the point, if it is in contrib it needs to be documented in Docbok and it needs to have a section in the main docs. > * Better advertising, for instance make the contrib documentation > available on the website (which probably requires SGML conversion > to happen first...) Agreed. Sincerely, Joshua D. Drake > > regards, tom lane > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal: allow installation of any contrib module
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I believe the reason we keep it separate is so that people can easily >> make crypto-free versions of PG for use in countries where encryption >> capability is considered subject to arms regulations. Not sure how >> important that case really is today, but it was a big consideration >> last time this was discussed. > That is a good question but in reality people who need to do so, can get > the source from a mirror that is outside the country that has the rules. You miss the point. Everybody knows that those laws are not too hard to circumvent if you are willing to break the law. The question is how hard is it for someone to distribute Postgres into one of those countries *without* breaking any local law. We won't be making things better if we make it difficult or impossible to put together a crypto-free PG distribution; we'll just be cutting off some potential users. As Peter keeps pointing out, this urge to push everything into core is misguided anyway. The fact that you can build useful functionality as extension modules is a strength of PG, not a weakness, and we should build on that strength instead of running away from it. If you ask me we should be working on these things: * Better packaging support, eg make it easier to add/remove an extension module and control how pg_dump deals with it. We talked about that awhile back but nobody did anything with the ideas. * Better documentation for the contrib modules; some of them are reasonably well doc'd now, but many are not, and in almost all cases it's only plain text not SGML. * Better advertising, for instance make the contrib documentation available on the website (which probably requires SGML conversion to happen first...) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] crash on 8.2 and cvshead - failed to add item to the
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > I think I found it. The page splitting code didn't take into account > that when the new item is the first one on the right page, it also > becomes the high key of the left page. Good catch! This is something that would not make a difference with equal-sized keys (a very common case) and also would never matter unless the "best available" split was pretty awful, which would require very large index entries. So that explains why it'd not been seen before. AFAICS the bug must go back a long way though; I'm not sure why Joe failed to reproduce on 8.1. Did we change the size of the page overhead in btree indexes recently? 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] Proposal: allow installation of any contrib module
Tom Lane wrote: > Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: >> I would like to see pgcrypto (or at least some of it's functionality) in >> core. > > I believe the reason we keep it separate is so that people can easily > make crypto-free versions of PG for use in countries where encryption > capability is considered subject to arms regulations. Not sure how > important that case really is today, but it was a big consideration > last time this was discussed. That is a good question but in reality people who need to do so, can get the source from a mirror that is outside the country that has the rules. Sincerely, Joshua D. Drake > > regards, tom lane > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: allow installation of any contrib module
>> Well perhaps it is time to trim Contrib even further. E.g; >> >> Why is ltree still in contrib? What prevents it from being in core? > > not sure - ltree is quite useful but I'm not convinced it is really core > material Why? > >> >> Why is pgcrypto,pgstattuple and pg_freespacemap in contrib? > > I would like to see pgcrypto (or at least some of it's functionality) in > core. > pgstattuple is still being quite activily developed (just look at the > large changes/feature enhancements for 8.2 which seems like a sign of it > not being ready yet. Is PostgreSQL not under active development? Is it not receiving large changes or feature enhancements? > pg_freespacemap is iirc new as of 8.2 so it imho never was a candidate > for core. I think you are missing my point. I only picked contribs that seemed logical to be in core. The point of the post was the discussion, not the particular module. Joshua D. Drake > > > Stefan > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] crash on 8.2 and cvshead - failed to add item to the
Tom Lane wrote: Heikki Linnakangas <[EMAIL PROTECTED]> writes: Hmm. There seems to be something wrong in the free space calculation in the algorithm for choosing the right split location. I'll dig deeper, unless someone beats me to it.. I seem to recall that that part of the code was changed recently, so you might try looking at the CVS history for hints. This is probably recently introduced, else we'd have seen it reported before :-( I'm afraid the bug has been there for ages, but the 90%-fillfactor on rightmost page patch made it much more likely to get triggered. With a 50% or 67% target for splitting, there is a lot more wiggle room. To see what's going on, I added some logs to the split code to print out the free space on both halves as calculated by findsplitloc, and the actual free space on the pages after split. I'm seeing a discrepancy of 4 bytes on the right half; actual space free on right page after split is 4 bytes less than anticipated. That's on every split, not just in some corner cases. That's not a big deal, but I'll take a closer look tomorrow to see what's missing from the calculations. -- Heikki Linnakangas 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] Recursive Queries
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > That's basically how the existing patch approached the problem. It invents a > > new type of join and a new type of tuplestore that behaves this way. This > > has > > the advantage of working the way Oracle users expect and being relatively > > simple conceptually. It has the disadvantage of locking us into what's > > basically a nested loop join and not reusing existing join code so it's > > quite > > a large patch. > > I believe our Syntax should be whatever the standard dictates, > regardless of Oracle. Well the issue here isn't one of syntax. The syntax is really an orthogonal issue. The basic question is whether to treat this as a new type of plan node with its behaviour hard coded or whether to try to reuse existing join types executing them recursively on their output. I can see advantages either way. As far as the syntax goes, now that I've actually read up on both, I have to say: I'm not entirely sure I'm happy IBM won this battle. The Oracle syntax is simple easy to use. The IBM/ANSI syntax is, well, baroque. There's a certain logical beauty to it but I can't see users being happy trying to figure out how to use it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: allow installation of any contrib module
Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > I would like to see pgcrypto (or at least some of it's functionality) in > core. I believe the reason we keep it separate is so that people can easily make crypto-free versions of PG for use in countries where encryption capability is considered subject to arms regulations. Not sure how important that case really is today, but it was a big consideration last time this was discussed. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal: allow installation of any contrib module
Joshua D. Drake wrote: The problem with this proposal is that the ISPs aren't the ones running configure --- these days, most people are running prebuilt packages (RPMs or DEBs or what have you). So what you are hoping is that the packagers will choose to do this and thereby force these modules into the "standard" configuration for everybody using their packages. I'm not sure that the packagers will change ... well maybe Gentoo will, but not anyone with more conservative policies ... and I'm pretty sure any who do will get push-back from people who still won't trust contrib. Well perhaps it is time to trim Contrib even further. E.g; Why is ltree still in contrib? What prevents it from being in core? not sure - ltree is quite useful but I'm not convinced it is really core material Why is pgcrypto,pgstattuple and pg_freespacemap in contrib? I would like to see pgcrypto (or at least some of it's functionality) in core. pgstattuple is still being quite activily developed (just look at the large changes/feature enhancements for 8.2 which seems like a sign of it not being ready yet. pg_freespacemap is iirc new as of 8.2 so it imho never was a candidate for core. Stefan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal: allow installation of any contrib module
> The problem with this proposal is that the ISPs aren't the ones running > configure --- these days, most people are running prebuilt packages > (RPMs or DEBs or what have you). So what you are hoping is that the > packagers will choose to do this and thereby force these modules into > the "standard" configuration for everybody using their packages. I'm > not sure that the packagers will change ... well maybe Gentoo will, > but not anyone with more conservative policies ... and I'm pretty sure > any who do will get push-back from people who still won't trust contrib. Well perhaps it is time to trim Contrib even further. E.g; Why is ltree still in contrib? What prevents it from being in core? Why is pgcrypto,pgstattuple and pg_freespacemap in contrib? I would almost think it makes sense to have: /modules - This is for things that are indeed modules they will be installed by default but are not *internal* like sum() or generate_series() /contrib - This is for things like adminpack, dblink, pgbench Sincerely, Joshua D. Drake > > 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 > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch in core patch, for inclusion
though that we still have the more odd grammar of actually using Tsearch to query. Although I don't really have a better suggestion without adding some ungodly obscure operator. IMHO, best possible solution is 'WHERE table.text_field @ text'. Operator @ internally makes equivalent of 'to_tsvector(table.text_field) @@ plainto_tsquery(text)', it's also possible to add GIN/GIST opclasses to speedup search queries. Performance of making headline in this case will be decreased insignificant, but ranking time will be disastrous. Because of reparsing of whole found texts. GIST performance may be decreased too - GIST indexing of tsvector is lossy. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] WAL Record Header Size Reduction
On Thu, 2007-01-25 at 11:03 -0500, Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: > > ISTM that we would get the effect your looking for by just moving the > > xl_tot_len field to the end, and only storing it for records with backup > > blocks: > > I like that formulation better --- seems like less > change-for-the-sake-of-change. Will do. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ECPG buglet?
On Thu, Jan 25, 2007 at 01:23:57PM +0100, Magnus Hagander wrote: > Looking at the VC warnings, I find that in ECPGDynamicType() > (typename.c in ecpglib), the default branch of the case statement > returns "-type". But the function returns "unsigned int", so returning a > negative value seems very strange to me. And I see no reason at all why this function is unsigned. After all there are about 20 different return values. Will change it. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch in core patch, for inclusion
On 1/25/07, Teodor Sigaev <[EMAIL PROTECTED]> wrote: It's should clear enough for now - dump data from old db and load into new one. But dump should be without any contrib/tsearch2 related functions. Upgrading from 8.1.x to 8.2.x was not tivial because of very trivial change in API (actually not really API but the content of "pg_ts_*" tables): russian snowball stemming function was forked to 2 different ones, for koi8 and utf8 encodings. So, as I dumped my pg_ts_* tables data (to keep my tsearch2 settings), I saw errors during restoration (btw, why didn't you keep old russian stemmer function name as a synonym to koi8 variant?) -- so, I had to change my dump file manually, because I didn't manage to follow "tsearch2 best practices" (to use some kind of "bootstrap" script that creates tsearch2 configuration you need from default one -- using several INSERTs and UPDATEs). And there were no upgrade notes for tsearch2. So, I consider upgrading process for tsearch2 to be a little bit tricky till present. I assume it will be improved with 8.3... -- Best regards, Nikolay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PERFORM] how to plan for vacuum?
On Thu, Jan 25, 2007 at 08:04:49AM -0800, Joshua D. Drake wrote: > > It really depends on the system. Most of our systems run anywhere from > 10-25ms. I find that any more than that, Vacuum takes too long. How do you measure the impact of setting it to 12 as opposed to 15? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch in core patch, for inclusion
Teodor Sigaev wrote: >> the patch. I'm personally not sold on the need for modifications to the >> SQL grammar, for example, as opposed to just using a set of SQL-callable >> functions and some new system catalogs. > > SQL grammar isn't changed significantly - just add variants of > CREATE/DROP/ALTER /COMMENTS commands. Next, functions haven't > autocomplete feature or built-in quick help - if you don't remember > exactly kind/type of argument(s) of function then you should read a docs. I didn't read the patch but I did skim the docs for this and if the docs are current I see things like this: CREATE FULLTEXT DICTIONARY en_ispell ( OPT = 'DictFile="ispell/english.dict", AffFile="ispell/english.aff", StopFile="english.stop"' ) LIKE ispell_template; ALTER FULLTEXT DICTIONARY en_stem SET OPT='english.stop'; Which to me is perfectly reasonable and intuitive. It is unfortunate though that we still have the more odd grammar of actually using Tsearch to query. Although I don't really have a better suggestion without adding some ungodly obscure operator. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] how to plan for vacuum?
On Thu, Jan 25, 2007 at 07:52:50PM +0900, Galy Lee wrote: > It is very hard for any normal user to set this correctly. I think the > experience / trial-and-error approach is awful for the user, every DBA > need to be an expert of vacuum to keep the system stable. For vacuum is > still a big threat to the performance, a more intelligent way is needed. Agreed. > So I have proposed the "vacuum in time" feature in previous; just let > vacuum know how long can it runs, and then it will minimize the impact > in the time span for you. Some argue that it should not have the > maintenance window assumption, but the most safely way is to run in the > maintenance window. Most systems I work on don't have a maintenance window. For those that do, the window is at best once a day, and that's nowhere near often enough to be vacuuming any database I've run across. I'm not saying they don't exist, but effort put into restricting vacuums to a maintenance window would serve very few people. It'd be much better to put effort into things like piggyback vacuum. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal: allow installation of any contrib module simultaneously with Postgres itself
"Nikolay Samokhvalov" <[EMAIL PROTECTED]> writes: > 1. Change default behaviour of .sql file so it will be > installed in schema instead of "public" (e.g., "hstore" > schema will contain all hstore relations and functions). This might be a good idea, but it's hardly transparent; it can be counted on to break the applications of just about everyone using those modules today. > 2. Allow running configure with "--with-" (or > "--enable-") to include compilation of module's libraries > simultaneously with Postgres itself and including running of module's > registration SQLs (from that .sql files) simultaneously with cluster > creation (in other words, with inidb invocation -- this will add > "" schema to template0). > This will simplify the procedure of starting to use contrib modules > and will help to promote the modules themselves (and, as a result, > some PostgreSQL's advanced features). I think many projects have > similar behaviour with regard to their extensions. And ISPs will > install PostgreSQL with a bundle of useful and "trusted" extensions, > simply running "./configure --with-tsearch2 --with-hstore > --with-dblink" (actually, I hope that tsearch2 will be in core, but > this is really good example at the moment ;-) ) - like they do with > PHP, Apache and other software. The problem with this proposal is that the ISPs aren't the ones running configure --- these days, most people are running prebuilt packages (RPMs or DEBs or what have you). So what you are hoping is that the packagers will choose to do this and thereby force these modules into the "standard" configuration for everybody using their packages. I'm not sure that the packagers will change ... well maybe Gentoo will, but not anyone with more conservative policies ... and I'm pretty sure any who do will get push-back from people who still won't trust contrib. 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] unused_oids?
Gevik Babakhani <[EMAIL PROTECTED]> writes: > At this moment the following is the list of the unused OIDs. For the > uuid datatype I use a script for generating catalog entries. I can close > some gaps there if the "masters" are okay with this. Are any OIDs > reserved for later or any range can be used in this case? My advice is not to try to fill in the gaps --- better to leave them there so that if any other objects are created that're related to the nearby ones, they can be assigned nearby OIDs. If you've got a patch that is going to use a bunch of OIDs for related purposes, it's better to use a contiguous chunk of OIDs for it. I believe that the bitmap-indexing patch is using OIDs starting at 3000 (at least that's the advice I gave them awhile back). If you need less than 50 you might start at 2950, else check that patch and start above whatever they used. 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] tsearch in core patch, for inclusion
the patch. I'm personally not sold on the need for modifications to the SQL grammar, for example, as opposed to just using a set of SQL-callable functions and some new system catalogs. SQL grammar isn't changed significantly - just add variants of CREATE/DROP/ALTER /COMMENTS commands. Next, functions haven't autocomplete feature or built-in quick help - if you don't remember exactly kind/type of argument(s) of function then you should read a docs. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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] crash on 8.2 and cvshead - failed to add item to the
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > Hmm. There seems to be something wrong in the free space calculation in > the algorithm for choosing the right split location. I'll dig deeper, > unless someone beats me to it.. I seem to recall that that part of the code was changed recently, so you might try looking at the CVS history for hints. This is probably recently introduced, else we'd have seen it reported before :-( regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] crash on 8.2 and cvshead - failed to add item to the
Heikki Linnakangas wrote: Heikki Linnakangas wrote: Joe Conway wrote: We just came upon a crash bug in Postgres >= 8.2. The attached standalone script (just needs a database with plpgsql installed) reproduces the crash for me on 32-bit machines (i686) but NOT on 64 bit machines (x86_64), for Postgres 8.2 and cvs-head, but not on 8.1. We've verified this on about four 32 bit machines, and four 64 bit machines (including one each under vmware on the same host). All machines were some flavor of Red Hat, Fedora, or Gentoo. Hmm. There seems to be something wrong in the free space calculation in the algorithm for choosing the right split location. I'll dig deeper, unless someone beats me to it.. I think I found it. The page splitting code didn't take into account that when the new item is the first one on the right page, it also becomes the high key of the left page. The fact that this test case triggered it in 32 bit machines and not on 64 bit machines was a coincidence. Patch attached. Thanks! That seems to have fixed it. Both the original test case and a somewhat simplified one that we created a few minutes ago work fine now, on 8.2 and cvs-head. The simplified case is still about 22K gzipped -- let me know if you want a copy and I'll send it off list. Joe ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] tsearch in core patch, for inclusion
This is a fairly large patch and I would like the chance to review it
before it goes in --- "we'll commit tomorrow" is not exactly a decent
review window.
Not a problem.
One possible argument for this over the contrib version is a saner
approach to dumping and restoring configurations. However, as against
that:
1) what's the upgrade path for getting an existing tsearch2
configuration into this implementation?
It's should clear enough for now - dump data from old db and load into new one.
But dump should be without any contrib/tsearch2 related functions.
2) once we put this in core we are going to be stuck with supporting its
SQL API forever. Are we convinced that this API is the one we want?
I don't recall even having seen any proposal or discussion. It was OK
for tsearch2's API to change every release while it was in contrib, but
the expectation of stability is a whole lot higher for core features.
Basic tsearch2 SQL API doesn't changed since its first release, just extended.
As I can see, there isn't any standard of fulltext search in SQL. DB/2, MS SQL,
Oracle and MySQL use different SQL API. I don't know which better. I remember
only one suggestion: 'CREATE FULLTEXT INDEX ...'. So, I believe, existing SQL
API satisfies users. But it possible to emulate on grammar level subset of MySQL
syntax:
SQL commands
CREATE FULLTEXT INDEX idxname ON tbl [ USING {GIN|GIST} ] ( field1[, [...]] );
SELECT .. FROM table WHERE MATCH( field1[, [...]] ) AGAINST ( txt );
will be translated to
CREATE INDEX idxname ON tbl [ USING {GIN|GIST} ] ( to_tsquery(field1)[ ||
[...]] );
SELECT .. FROM table WHERE ( to_tsquery(field1)[ || [...]] ) @@ plainto_tsquery(
txt );
Notes
1 that is full equivalent MySQL's MATCH() AGAINST (txt IN BOOLEAN MODE)
2 it requires to keyword MATCH & AGAINST which cannot be a function's name
without quoting.
Internal API changed sometimes (not every release), but I don't see a problem
here: all other internal API's in postgres are often changed.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] WAL Record Header Size Reduction
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > ISTM that we would get the effect your looking for by just moving the > xl_tot_len field to the end, and only storing it for records with backup > blocks: I like that formulation better --- seems like less change-for-the-sake-of-change. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Access last inserted tuple info...
On Thu, Jan 25, 2007 at 10:45:43AM -0400, Luis D. García wrote: > Well, maybe I didn't make myself clear, this is what I need to do: > > I need to know the values of some fields (columns) from the last > inserted tuple. The problem is that I need to do this in the moment > I'm inserting a new one (all from the backend code) and compare > the values between the new one (N.O) and the all ready inserted (A.R.I), > and if the N.O fits some properties (according to the A.R.I) it must > be decided if it has to be finally inserted or not. Sounds like exactly what a trigger is for. The trigger can do what it likes and depending on that, update the tuple or not. However, you say you can't use triggers (which are just functions), which begs the question: in what context is this? In what context is this going to happen? "Last" relative to what? > I think there's not to much difficulty on that, but until now I haven't > found how to access that information. I can reach the HeapTuple > structure according the A.R.I tuple, but I don't know how to access > the information (values for its attributes) stored on it. If you have a HeapTuple, there are functions to extract values out of it and examine it. Is that what you're looking for? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] [PERFORM] how to plan for vacuum?
Alvaro Herrera wrote: > Jim C. Nasby wrote: > >> I'll generally start with a cost delay of 20ms and adjust based on IO >> utilization. > > I've been considering set a default autovacuum cost delay to 10ms; does > this sound reasonable? It really depends on the system. Most of our systems run anywhere from 10-25ms. I find that any more than that, Vacuum takes too long. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] crash on 8.2 and cvshead - failed to add item to the
Heikki Linnakangas wrote: Joe Conway wrote: We just came upon a crash bug in Postgres >= 8.2. The attached standalone script (just needs a database with plpgsql installed) reproduces the crash for me on 32-bit machines (i686) but NOT on 64 bit machines (x86_64), for Postgres 8.2 and cvs-head, but not on 8.1. We've verified this on about four 32 bit machines, and four 64 bit machines (including one each under vmware on the same host). All machines were some flavor of Red Hat, Fedora, or Gentoo. Hmm. There seems to be something wrong in the free space calculation in the algorithm for choosing the right split location. I'll dig deeper, unless someone beats me to it.. I think I found it. The page splitting code didn't take into account that when the new item is the first one on the right page, it also becomes the high key of the left page. The fact that this test case triggered it in 32 bit machines and not on 64 bit machines was a coincidence. Patch attached. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com Index: src/backend/access/nbtree/nbtinsert.c === RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/access/nbtree/nbtinsert.c,v retrieving revision 1.147 diff -c -r1.147 nbtinsert.c *** src/backend/access/nbtree/nbtinsert.c 5 Jan 2007 22:19:23 - 1.147 --- src/backend/access/nbtree/nbtinsert.c 25 Jan 2007 15:50:36 - *** *** 1153,1159 /* need to try it both ways! */ _bt_checksplitloc(&state, offnum, leftfree, rightfree, true, itemsz); ! /* here we are contemplating newitem as first on right */ _bt_checksplitloc(&state, offnum, leftfree, rightfree, false, newitemsz); } --- 1153,1166 /* need to try it both ways! */ _bt_checksplitloc(&state, offnum, leftfree, rightfree, true, itemsz); ! ! /* here we are contemplating newitem as first on right. ! * ! * The new item is going to be the high key of the left page ! * instead of the current item that we subtracted from leftfree ! * above. ! */ ! leftfree = leftfree + ((int) itemsz) - ((int) newitemsz); _bt_checksplitloc(&state, offnum, leftfree, rightfree, false, newitemsz); } ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Recursive Queries
Gregory Stark wrote: > "Martijn van Oosterhout" writes: > >> On Thu, Jan 25, 2007 at 11:08:14AM +, Gregory Stark wrote: >>> b) I do want to be able to support depth-first searching too. I'm not sure >>> how >>> to reconcile that with the repeated-join conceptual model. We could always >>> resort the entire result set after generating it but that seems like an >>> unsatisfactory solution. >> If you have a tuplestore storing the intermediate tuples for looping, >> then surely the only difference between depth and breadth searching is >> that for the former new tuples goes to the front of the tuplestore, and >> the latter to the end. > > That's basically how the existing patch approached the problem. It invents a > new type of join and a new type of tuplestore that behaves this way. This has > the advantage of working the way Oracle users expect and being relatively > simple conceptually. It has the disadvantage of locking us into what's > basically a nested loop join and not reusing existing join code so it's quite > a large patch. I believe our Syntax should be whatever the standard dictates, regardless of Oracle. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] unused_oids?
> uuid datatype I use a script for generating catalog entries. I can close > some gaps there if the "masters" are okay with this. Are any OIDs > reserved for later or any range can be used in this case? IMHO, better way is to use some high oids ( for example, starting from 8000 ) and before committing change they to lowest possible. Ehan HEAD is under hard development, oids change quickly, so you will need to rearrange your oids for each snapshot. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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] Proposal: allow installation of any contrib module simultaneously with Postgres itself
Nikolay Samokhvalov wrote: > 1. Change default behaviour of .sql file so it will be > installed in schema instead of "public" (e.g., "hstore" > schema will contain all hstore relations and functions). That might be a good idea in any case. > 2. Allow running configure with "--with-" (or > "--enable-") to include compilation of module's > libraries simultaneously with Postgres itself and including running > of module's registration SQLs (from that .sql files) simultaneously > with cluster creation (in other words, with inidb invocation -- this > will add "" schema to template0). Build-time options will generally suffer from the problem that package builders will turn them all on and then users are stuck with all modules and then they're reallly not modular anymore. But I think your general idea of making them "more default" is sound. But it needs to be a run-time choice. Maybe we really just need to call them "modules" instead of "contrib", since users of Apache, PHP, or Linux will be familiar with that term. (I don't know how this overlaps with SQL modules though.) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] unused_oids?
Folks, At this moment the following is the list of the unused OIDs. For the uuid datatype I use a script for generating catalog entries. I can close some gaps there if the "masters" are okay with this. Are any OIDs reserved for later or any range can be used in this case? 2 - 9 32 86 - 88 90 100 193 - 199 276 321 - 328 376 432 - 433 820 - 828 1004 1972 - 1973 1980 1998 2003 - 2004 2039 2096 2230 2746 2758 - 2780 2858 - 2859 2922 - Regards, Gevik ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] crash on 8.2 and cvshead - failed to add item to the
Joe Conway wrote: We just came upon a crash bug in Postgres >= 8.2. The attached standalone script (just needs a database with plpgsql installed) reproduces the crash for me on 32-bit machines (i686) but NOT on 64 bit machines (x86_64), for Postgres 8.2 and cvs-head, but not on 8.1. We've verified this on about four 32 bit machines, and four 64 bit machines (including one each under vmware on the same host). All machines were some flavor of Red Hat, Fedora, or Gentoo. Hmm. There seems to be something wrong in the free space calculation in the algorithm for choosing the right split location. I'll dig deeper, unless someone beats me to it.. -- Heikki Linnakangas 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] crash on 8.2 and cvshead - failed to add item to the
Stefan Kaltenbrunner wrote: Gregory Stark wrote: "Joe Conway" <[EMAIL PROTECTED]> writes: psql:/home/jconway/pgsql/das_data_load_failure2.sql:419: PANIC: failed to add item to the left sibling for "pk_status_log_2007_01_4_10" Was this preceded by a WARNING? Was the server running with a log_min_messages low enough to log WARNINGs? I probably can't help find the bug but I can see that would be helpful as there are three branches of the code that can result in this and two of them log warnings before returning the invalid offset which causes the panic. FWIW I can reproduce the crash on 8.2 and I don't get a WARNING either ... I don't get the WARNING, and I'm using stock default postgresql.conf. Joe ---(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] crash on 8.2 and cvshead - failed to add item to the
Gregory Stark wrote: "Joe Conway" <[EMAIL PROTECTED]> writes: psql:/home/jconway/pgsql/das_data_load_failure2.sql:419: PANIC: failed to add item to the left sibling for "pk_status_log_2007_01_4_10" Was this preceded by a WARNING? Was the server running with a log_min_messages low enough to log WARNINGs? I probably can't help find the bug but I can see that would be helpful as there are three branches of the code that can result in this and two of them log warnings before returning the invalid offset which causes the panic. FWIW I can reproduce the crash on 8.2 and I don't get a WARNING either ... Stefan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] crash on 8.2 and cvshead - failed to add item to the left sibling
"Joe Conway" <[EMAIL PROTECTED]> writes: > psql:/home/jconway/pgsql/das_data_load_failure2.sql:419: PANIC: failed > to add item to the left sibling for "pk_status_log_2007_01_4_10" Was this preceded by a WARNING? Was the server running with a log_min_messages low enough to log WARNINGs? I probably can't help find the bug but I can see that would be helpful as there are three branches of the code that can result in this and two of them log warnings before returning the invalid offset which causes the panic. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql: Fix for plpython functions; return
Tom Lane wrote: > [EMAIL PROTECTED] (Bruce Momjian) writes: > > Fix for plpython functions; return true/false for boolean, > > This patch has broken a majority of the buildfarm. Yea, reverted with comment added. -- Bruce Momjian [EMAIL PROTECTED] 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
Re: [HACKERS] WAL Record Header Size Reduction
On Thu, 2007-01-25 at 14:04 +, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > Current WAL Header uses 32 bytes on a 64-bit CPU. It seems possible to
> > reduce this to 24 bytes, without reducing resilience, when
> > full_page_writes = off. This will reduce overall WAL volumes by around
> > 5-15%, depending upon the application with performance gains in various
> > ways.
>
> Actually, it would help even when full_page_writes=on, because even then
> most xlog records don't have backup blocks attached to them.
Sure it would be active, but I meant it probably would not reduce the
overall WAL volume by very much. We'll see, I guess.
> > xlog.h shows this definition currently:
> >
> > typedef struct XLogRecord
> > {
> > pg_crc32 xl_crc; /* CRC for this record */
> > XLogRecPtr xl_prev; /* ptr to previous record in log */
> > TransactionId xl_xid; /* xact id */
> > uint32 xl_tot_len; /* total len of entire record */
> > uint32 xl_len; /* total len of rmgr data */
> > uint8 xl_info; /* flag bits, see below */
> > RmgrId xl_rmid; /* resource manager for this record */
> >
> > /* Depending on MAXALIGN, there are either 2 or 6 wasted bytes here */
> >
> > I propose to rearrange the XLogRecord structure to this:
>
> I think you got your alignment wrong:
>
> > pg_crc32 xl_crc; /* CRC for this record */
> > uint8 xl_info; /* flag bits, see below */
> > RmgrId xl_rmid; /* resource manager for this record */
>
> Because of xl_prev below which is two uint32 fields, there will be 2
> bytes of wasted space in here.
No escaping that though.
> ISTM that we would get the effect your looking for by just moving the
> xl_tot_len field to the end, and only storing it for records with backup
> blocks:
>
> > pg_crc32 xl_crc; /* CRC for this record */
> > XLogRecPtr xl_prev; /* ptr to previous record in log */
> > TransactionId xl_xid; /* xact id */
> > uint32 xl_len; /* total len of rmgr data */
> > uint8 xl_info; /* flag bits, see below */
> > RmgrId xl_rmid; /* resource manager for this record */
> >> uint32 xl_tot_len; /* total len of entire record, if backup blocks
> indicated in xl_info*/
OK. I was assuming XLogRecPtr was 8-byte aligned, but its a struct with
2 4-byte aligned elements, so I thought I had to move it to get the
benefit. Seems not...
--
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] Fix for bug in plpython bool type conversion
I have had to reverse out this patch because Py_RETURN_TRUE is only
supported in Python versions >= 2.3, and we support older versions. I
did add a comment:
* We would like to use Py_RETURN_TRUE and Py_RETURN_FALSE here for
* generating SQL from trigger functions, but those are only
* supported in Python >= 2.3, and we support older
* versions. http://docs.python.org/api/boolObjects.html
---
bruce wrote:
>
> Your patch has been added to the PostgreSQL unapplied patches list at:
>
> http://momjian.postgresql.org/cgi-bin/pgpatches
>
> It will be applied as soon as one of the PostgreSQL committers reviews
> and approves it.
>
> ---
>
>
> Guido Goldstein wrote:
> > Hi!
> >
> > The attached patch fixes a bug in plpython.
> >
> > This bug was found while creating sql from trigger functions
> > written in plpython and later running the generated sql.
> > The problem was that boolean was was silently converted to
> > integer, which is ok for python but fails when the created
> > sql is used.
> >
> > The patch uses the Py_RETURN_xxx macros shown at
> > http://docs.python.org/api/boolObjects.html .
> >
> > It would be nice if someone could test and comment
> > on the patch.
> >
> > Cheers
> > Guido
>
> > --- postgresql-8.2.1.orig/src/pl/plpython/plpython.c2006-11-21
> > 22:51:05.0 +0100
> > +++ postgresql-8.2.1/src/pl/plpython/plpython.c 2007-01-17
> > 18:06:58.185497734 +0100
> > @@ -1580,8 +1580,8 @@
> > PLyBool_FromString(const char *src)
> > {
> > if (src[0] == 't')
> > - return PyInt_FromLong(1);
> > - return PyInt_FromLong(0);
> > + Py_RETURN_TRUE;
> > + Py_RETURN_FALSE;
> > }
> >
> > static PyObject *
> >
> >
> > ---(end of broadcast)---
> > TIP 5: don't forget to increase your free space map settings
>
> --
> Bruce Momjian [EMAIL PROTECTED]
> EnterpriseDBhttp://www.enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(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] Proposal: allow installation of any contrib module simultaneously with Postgres itself
Discussion "tsearch in core patch, for inclusion" shows (http://archives.postgresql.org/pgsql-hackers/2007-01/msg01165.php and following http://archives.postgresql.org/pgsql-hackers/2007-01/msg01186.php) that there are some problems with contrib promotion and expansion. I've encountered with bad awareness and some kind of fears (due to "possible security holes" and similar reasons) regarding contrib modules. For example, hstore is very good thing that helps to resolve many issues (e.g. logging tasks), but not many people know are aware of it, and there are very few hosting providers which include hstore to Postgres installation. So, it would be really good if documentation and the main website itself include more information describing the modules (maybe to review README files and include them all in the docs?). But I want to propose something more. It's clear that some ISPs are afraid of contrib modules installation, many of which are very useful and have reliable code. But, those ISPs are not afraid to install, say, PHP with a dozen modules (extensions). Why? Besides the fact that PHP modules are very good described in the main PHP manual, I see very simple reason: to install a contrib module you must go to contrib dir and run _another_ "make install" (wth following "psql .. < module.sql" surely), while to install PHP extension you should only add "--with-modulename" to the configuration command. Well, my proposal is simple: 1. Change default behaviour of .sql file so it will be installed in schema instead of "public" (e.g., "hstore" schema will contain all hstore relations and functions). 2. Allow running configure with "--with-" (or "--enable-") to include compilation of module's libraries simultaneously with Postgres itself and including running of module's registration SQLs (from that .sql files) simultaneously with cluster creation (in other words, with inidb invocation -- this will add "" schema to template0). This will simplify the procedure of starting to use contrib modules and will help to promote the modules themselves (and, as a result, some PostgreSQL's advanced features). I think many projects have similar behaviour with regard to their extensions. And ISPs will install PostgreSQL with a bundle of useful and "trusted" extensions, simply running "./configure --with-tsearch2 --with-hstore --with-dblink" (actually, I hope that tsearch2 will be in core, but this is really good example at the moment ;-) ) - like they do with PHP, Apache and other software. Let's make the usage of contrib modules more user-friendly. -- Best regards, Nikolay ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Access last inserted tuple info...
Thanks, I've heard that, but in this case won't exactly fit my needs... ..First, I'm working on a PostgreSQL 8.1.4 modification, and it would be really difficult to add all the changes I've made to the new version. ...Second, I need to do all this form the backend (Postgres Source Code). I can't use any User or Administrator defined functions or sometime like that. Well, maybe I didn't make myself clear, this is what I need to do: I need to know the values of some fields (columns) from the last inserted tuple. The problem is that I need to do this in the moment I'm inserting a new one (all from the backend code) and compare the values between the new one (N.O) and the all ready inserted (A.R.I), and if the N.O fits some properties (according to the A.R.I) it must be decided if it has to be finally inserted or not. I think there's not to much difficulty on that, but until now I haven't found how to access that information. I can reach the HeapTuple structure according the A.R.I tuple, but I don't know how to access the information (values for its attributes) stored on it. Thanks again... 2007/1/24, David Fetter <[EMAIL PROTECTED]>: On Wed, Jan 24, 2007 at 12:56:14PM -0400, Luis D. Garc?a wrote: > >Hi, I'm working on a modification of PostgreSQL 8.1.4 and I need to access >the >information stored in the last tuple inserted in a table (All this from the >backend >code). >Could anyone please help me on this? >Greetings and Thanks... 8.2 has (INSERT/UPDATE/DELETE) ... RETURNING. Perhaps you could use the 8.2 series instead :) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! -- Luis D. García M. Telf: (+58) 2418662663 Cel.: (+58) 4143482018 - FACYT - UC - - Computación -
[HACKERS] crash on 8.2 and cvshead - failed to add item to the left sibling
[ Sorry if this is a duplicate -- resending since it hasn't made it to the list after 1 1/2 hour, possibly due to large attachment (?); here's a URL instead: http://www.joeconway.com/das_data_load_failure2.sql.gz ] We just came upon a crash bug in Postgres >= 8.2. The attached standalone script (just needs a database with plpgsql installed) reproduces the crash for me on 32-bit machines (i686) but NOT on 64 bit machines (x86_64), for Postgres 8.2 and cvs-head, but not on 8.1. We've verified this on about four 32 bit machines, and four 64 bit machines (including one each under vmware on the same host). All machines were some flavor of Red Hat, Fedora, or Gentoo. Here is the error: 8<-- das=# \i /home/jconway/pgsql/das_data_load_failure2.sql [...snip...] das_tbl_das_status_log (1 row) psql:/home/jconway/pgsql/das_data_load_failure2.sql:419: PANIC: failed to add item to the left sibling for "pk_status_log_2007_01_4_10" CONTEXT: COPY das_status_log_2007_01_4_10, line 27: "10 2007-Jan-25 03:06:59.372266 -0800 r_x_data 406,386,482,342,338,338,330,310,322,330,314,326,334,31..." psql:/home/jconway/pgsql/das_data_load_failure2.sql:420: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:/home/jconway/pgsql/das_data_load_failure2.sql:420: connection to server was lost 8<-- Here is a backtrace: 8<-- #0 0x00d2f402 in __kernel_vsyscall () #1 0x42901d40 in raise () from /lib/libc.so.6 #2 0x42903591 in abort () from /lib/libc.so.6 #3 0x082ed82e in errfinish (dummy=0) at elog.c:449 #4 0x082ee729 in elog_finish (elevel=22, fmt=0x832c0c4 "failed to add item to the %s for \"%s\"") at elog.c:937 #5 0x080b0483 in _bt_pgaddtup (rel=0xb5b4302c, page=0x86c6bfc "", itemsize=48, itup=0xb5e49d14, itup_off=36, where=0x832b826 "left sibling") at nbtinsert.c:1654 #6 0x080ae849 in _bt_split (rel=0xb5b4302c, buf=252, firstright=40, newitemoff=40, newitemsz=1312, newitem=0x8680714, newitemonleft=0 '\0') at nbtinsert.c:843 #7 0x080ad965 in _bt_insertonpg (rel=0xb5b4302c, buf=252, stack=0x869c3b4, keysz=4, scankey=0x8681538, itup=0x8680714, afteritem=0, split_only_page=0 '\0') at nbtinsert.c:538 #8 0x080acd08 in _bt_doinsert (rel=0xb5b4302c, itup=0x8680714, index_is_unique=1 '\001', heapRel=0xb5b42c7c) at nbtinsert.c:141 #9 0x080b484a in btinsert (fcinfo=0xbfc80380) at nbtree.c:224 #10 0x082f393c in FunctionCall6 (flinfo=0x866ef9c, arg1=3048484908, arg2=3217557044, arg3=3217557012, arg4=141339656, arg5=3048483964, arg6=1) at fmgr.c:1267 #11 0x080ab427 in index_insert (indexRelation=0xb5b4302c, values=0xbfc80634, isnull=0xbfc80614 "", heap_t_ctid=0x86cac08, heapRelation=0xb5b42c7c, check_uniqueness=1 '\001') at indexam.c:196 #12 0x081a08e5 in ExecInsertIndexTuples (slot=0x86899e4, tupleid=0x86cac08, estate=0x862cc14, is_vacuum=0 '\0') at execUtils.c:1088 #13 0x0814fcd9 in CopyFrom (cstate=0x863a590) at copy.c:2082 #14 0x0814d90b in DoCopy (stmt=0x860bd14) at copy.c:1141 #15 0x082555a7 in ProcessUtility (parsetree=0x860bd14, params=0x0, dest=0x860bbe4, completionTag=0xbfc80d1e "") at utility.c:635 #16 0x08253bff in PortalRunUtility (portal=0x862ec1c, query=0x860bd70, dest=0x860bbe4, completionTag=0xbfc80d1e "") at pquery.c:1063 #17 0x08253d72 in PortalRunMulti (portal=0x862ec1c, dest=0x860bbe4, altdest=0x860bbe4, completionTag=0xbfc80d1e "") at pquery.c:1131 #18 0x08253482 in PortalRun (portal=0x862ec1c, count=2147483647, dest=0x860bbe4, altdest=0x860bbe4, completionTag=0xbfc80d1e "") at pquery.c:700 #19 0x0824deb2 in exec_simple_query (query_string=0x860b9cc "copy das_status_log_2007_01_4_10 from stdin;") at postgres.c:939 #20 0x08251b5e in PostgresMain (argc=4, argv=0x85b1c80, username=0x85b1c50 "postgres") at postgres.c:3424 8<-- Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Questions about warnings
Gavin Sherry wrote: On Thu, 25 Jan 2007, Magnus Hagander wrote: I'm looking over the VC build trying to eliminate what warnings are left. One thing that appears in a couple of places is stuff like: .\src\bin\psql\print.c(2014): warning C4090: 'function' : different 'const' qualifiers Seems like other projects have encountered this problem. Looks like a simple type difference. Casting to (void *) should fix it. http://mirror.ethereal.com/lists/ethereal-dev/200502/msg00170.html But note that Tom recently (correctly) chided me thus: Oh, and casting away const gets no points for style. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL Record Header Size Reduction
Simon Riggs wrote:
Current WAL Header uses 32 bytes on a 64-bit CPU. It seems possible to
reduce this to 24 bytes, without reducing resilience, when
full_page_writes = off. This will reduce overall WAL volumes by around
5-15%, depending upon the application with performance gains in various
ways.
Actually, it would help even when full_page_writes=on, because even then
most xlog records don't have backup blocks attached to them.
xlog.h shows this definition currently:
typedef struct XLogRecord
{
pg_crc32 xl_crc; /* CRC for this record */
XLogRecPtr xl_prev; /* ptr to previous record in log */
TransactionId xl_xid; /* xact id */
uint32 xl_tot_len; /* total len of entire record */
uint32 xl_len; /* total len of rmgr data */
uint8 xl_info; /* flag bits, see below */
RmgrId xl_rmid; /* resource manager for this record */
/* Depending on MAXALIGN, there are either 2 or 6 wasted bytes here */
I propose to rearrange the XLogRecord structure to this:
I think you got your alignment wrong:
pg_crc32 xl_crc; /* CRC for this record */
uint8 xl_info; /* flag bits, see below */
RmgrId xl_rmid; /* resource manager for this record */
Because of xl_prev below which is two uint32 fields, there will be 2
bytes of wasted space in here.
XLogRecPtr xl_prev; /* ptr to previous record in log */
TransactionId xl_xid; /* xact id */
uint32 xl_tot_len; /* total len of entire record */
ISTM that we would get the effect your looking for by just moving the
xl_tot_len field to the end, and only storing it for records with backup
blocks:
> pg_crc32 xl_crc; /* CRC for this record */
> XLogRecPtr xl_prev; /* ptr to previous record in log */
> TransactionId xl_xid; /* xact id */
> uint32 xl_len; /* total len of rmgr data */
> uint8 xl_info; /* flag bits, see below */
> RmgrId xl_rmid; /* resource manager for this record */
>> uint32 xl_tot_len; /* total len of entire record, if backup blocks
indicated in xl_info*/
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.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] WAL Record Header Size Reduction
Current WAL Header uses 32 bytes on a 64-bit CPU. It seems possible to
reduce this to 24 bytes, without reducing resilience, when
full_page_writes = off. This will reduce overall WAL volumes by around
5-15%, depending upon the application with performance gains in various
ways.
If full_page_writes = off then it this is true
xl_tot_len == xl_len + SizeOfXLogRecord
since there are no backup blocks. As a result, there is no loss in
resilience by removing this field.
xlog.h shows this definition currently:
typedef struct XLogRecord
{
pg_crc32 xl_crc; /* CRC for this record */
XLogRecPtr xl_prev; /* ptr to previous record in log */
TransactionId xl_xid; /* xact id */
uint32 xl_tot_len; /* total len of entire record */
uint32 xl_len; /* total len of rmgr data */
uint8 xl_info; /* flag bits, see below */
RmgrId xl_rmid; /* resource manager for this record */
/* Depending on MAXALIGN, there are either 2 or 6 wasted bytes here */
I propose to rearrange the XLogRecord structure to this:
pg_crc32 xl_crc; /* CRC for this record */
uint8 xl_info; /* flag bits, see below */
RmgrId xl_rmid; /* resource manager for this record */
XLogRecPtr xl_prev; /* ptr to previous record in log */
TransactionId xl_xid; /* xact id */
uint32 xl_tot_len; /* total len of entire record */
which will occupy 24 bytes, saving 4 bytes on 32-bit and 8 bytes on
64-bit architectures, once alignment is considered.
The xl_len field would be included only if backup blocks are included
with the record. This is already marked by flags in the xl_info field,
so no new flags are required.
These changes can be mostly isolated to xlog.c, since only XLogInsert(),
ReadRecord() and pg_resetxlog need to know about the changes. The xlog
record would dynamically adjust according to whether backup blocks are
present, so it can still work as full_page_writes is switched on/off by
user or during the period between start/stop backup.
The saving is only really relevant when full_page_writes = off, so I'm
not worried about changing the xlrec header in that case anyway.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Questions about warnings
On Thu, Jan 25, 2007 at 01:20:10PM +, Gregory Stark wrote: > "const char **" means the character at the end of the pointer chain is > constant. Which means my previous message is misguided, ignore it, sorry. In > short, yes, this is a limitation of the const syntax in C and you have to cast > it away in this case. Well, you can say things like: char * const *ptr Which means that *ptr is const, but ptr and **ptr are not. Each of those can be made const/not const as desired... What is intended here is quite a different question, the use of pointers in that part of psql is a bit haphazard at times. FWIW, Coverity complains about stuff here too, but I just marked it all WONTFIX :). Have anice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
