Re: [HACKERS] Summary and Plan for Hot Standby
Simon Riggs wrote: Recovery does *not* take the same locks as the original statements on the master took. For example, the WAL record for an INSERT just makes its changes without acquiring locks. This is OK as long as we only allow read-only users to acquire AccessShareLocks. If we allowed higher locks we might need to do deadlock detection, which would add more complexity. But we *do* allow higher locks than AccessShareLocks, as Tatsuo-sans example shows. Is that a bug? The above restrictions are limited to LOCKTAG_OBJECT so that advisory locks work as advertised. So advisory locks can take both shared and exclusive locks. This never conflicts with recovery because advisory locks are not WAL logged. So we allow any lock on anything *except* LOCKTAG_OBJECT. That includes advisory locks, but also relation locks, tuple locks and page locks. Looking at the lock types in detail: LOCKTAG_RELATION Any lock level is allowed. We have other defenses against actually modifying a relation, but it feels a bit fragile and I got the impression from your comments that it's not intentional. LOCKTAG_RELATION_EXTEND Any lock level is allowed. Again, we have other defenses against modifying relations, but feels fragile. LOCKTAG_PAGE Any lock level is allowed. Page locks are only used when extending a hash index, so it seems irrelevant what we do. I think we should disallow page locks in standby altogether. LOCKTAG_TUPLE, Any lock level is allowed. Only used when locking a tuple for update. We forbid locking tuples by the general is the transaction read-only? check in executor, and if you manage to bypass that, you will fail to get an XID to set to xmax. Nevertheless, seems we shouldn't allow tuple locks. LOCKTAG_TRANSACTION, Any lock level is allowed. Acquired in AssignTransactionId, to allow others to wait for the transaction to finish. We don't allow AssignTransactionId() during recovery, but could someone want to wait for a transaction to finish? All the current callers of XactLockTableWait() seem to be from operations that are not allowed in recovery. Should we take a conservative stance and disallow taking transaction-locks? LOCKTAG_VIRTUALTRANSACTION Any lock level is allowed. Similar to transaction locks, but virtual transaction locks are held by read-only transactions as well. Also during recovery, and we rely on it in the code to wait for a conflicting transaction to finish. But we don't acquire locks to represent transactions in master. LOCKTAG_OBJECT, Anything higher than AccessShareLock is disallowed. Used by dependency walking in pg_depend.c. Also used as interlock between database start and DROP/CREATE DATABASE. At backend start, we normally take RowExclusiveLock on the database in postinit.c, but you had to modify to acquire AccessShareLock instead in standby mode. LOCKTAG_USERLOCK LOCKTAG_ADVISORY Any lock level is allowed. As documented, advisory locks are per-server, so a lock taken in master doesn't conflict with one taken in slave. In any case, all this really needs to be documented in a README or something. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Simon Riggs wrote: Recovery does *not* take the same locks as the original statements on the master took. For example, the WAL record for an INSERT just makes its changes without acquiring locks. This is OK as long as we only allow read-only users to acquire AccessShareLocks. If we allowed higher locks we might need to do deadlock detection, which would add more complexity. But we *do* allow higher locks than AccessShareLocks, as Tatsuo-sans example shows. Is that a bug? Sorry for confusion. My example is under normal PostgreSQL, not under HS enabled. -- Tatsuo Ishii SRA OSS, Inc. Japan The above restrictions are limited to LOCKTAG_OBJECT so that advisory locks work as advertised. So advisory locks can take both shared and exclusive locks. This never conflicts with recovery because advisory locks are not WAL logged. So we allow any lock on anything *except* LOCKTAG_OBJECT. That includes advisory locks, but also relation locks, tuple locks and page locks. Looking at the lock types in detail: LOCKTAG_RELATION Any lock level is allowed. We have other defenses against actually modifying a relation, but it feels a bit fragile and I got the impression from your comments that it's not intentional. LOCKTAG_RELATION_EXTEND Any lock level is allowed. Again, we have other defenses against modifying relations, but feels fragile. LOCKTAG_PAGE Any lock level is allowed. Page locks are only used when extending a hash index, so it seems irrelevant what we do. I think we should disallow page locks in standby altogether. LOCKTAG_TUPLE, Any lock level is allowed. Only used when locking a tuple for update. We forbid locking tuples by the general is the transaction read-only? check in executor, and if you manage to bypass that, you will fail to get an XID to set to xmax. Nevertheless, seems we shouldn't allow tuple locks. LOCKTAG_TRANSACTION, Any lock level is allowed. Acquired in AssignTransactionId, to allow others to wait for the transaction to finish. We don't allow AssignTransactionId() during recovery, but could someone want to wait for a transaction to finish? All the current callers of XactLockTableWait() seem to be from operations that are not allowed in recovery. Should we take a conservative stance and disallow taking transaction-locks? LOCKTAG_VIRTUALTRANSACTION Any lock level is allowed. Similar to transaction locks, but virtual transaction locks are held by read-only transactions as well. Also during recovery, and we rely on it in the code to wait for a conflicting transaction to finish. But we don't acquire locks to represent transactions in master. LOCKTAG_OBJECT, Anything higher than AccessShareLock is disallowed. Used by dependency walking in pg_depend.c. Also used as interlock between database start and DROP/CREATE DATABASE. At backend start, we normally take RowExclusiveLock on the database in postinit.c, but you had to modify to acquire AccessShareLock instead in standby mode. LOCKTAG_USERLOCK LOCKTAG_ADVISORY Any lock level is allowed. As documented, advisory locks are per-server, so a lock taken in master doesn't conflict with one taken in slave. In any case, all this really needs to be documented in a README or something. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] add more frame types in window functions (ROWS)
Here's the rest of the review, as far as I've taken it given the problems with the code. The patch applied cleanly and includes regression tests but not docs. Small nitpicks: there are some comments not updated (e.g. the big one at the start of eval_windowaggregates). A couple of lines are commented-out using C++ comments. The overall approach seems ok, and the parser stuff seems fine to me. These are the issues I've found that make it not committable in its present form (including the ones I mentioned before): - missing _readWindowFrameDef function (all nodes that are output from parse analysis must have both _read and _out functions, otherwise views can't work) - the A_Const nodes should probably be transformed to Const nodes in parse analysis, since A_Const has no _read/_out functions, which means changing the corresponding code in the executor. - ruleutils.c not updated to deparse the newly added window options - leaks memory like it's going out of style The memory leakage is caused by not resetting any memory contexts when throwing away all the aggregate state when advancing the start of the window frame. This looks like it will require a rethink of the memory management being used; it's not enough just to pfree copies of the transition values (which you don't appear to be doing), you have to reset the memory context that was exposed to the transition functions via context-wincontext. So the current setup of a single long-lived context won't work; you'll need a long-lived one, plus an additional one that you can reset any time the aggregates need to be re-initialized. (And if you're not going to break existing aggregate functions, WindowAggState.wincontext needs to be the one that gets reset.) Tests for memory leaks: -- tests for failure to free by-ref transition values select count(*) from (select i,max(repeat(i::text,100)) over (order by i rows between 1 preceding and current row) from generate_series(1,100) i) s; -- tests for failure to reset memory context on window advance select count(*) from (select i,array_agg(i) over (order by i rows between 1 preceding and current row) from generate_series(1,100) i) s; -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Tatsuo Ishii wrote: Sorry for confusion. My example is under normal PostgreSQL, not under HS enabled. You get the same result in standby: postgres=# begin; BEGIN postgres=# prepare a(int) as insert into foo values($1); PREPARE postgres=# SELECT * FROM pg_locks; locktype │ database │ relation │ page │ tuple │ virtualxid │ transactionid │ classid │ objid │ objsubid │ virtualtransaction │ pid │ mode │ gra nted ┼──┼──┼──┼───┼┼───┼─ ┼───┼──┼┼───┼──┼ ─ relation │11564 │10968 │ │ ││ │ │ │ │ 2/4│ 10449 │ AccessShareLock │ t relation │11564 │16384 │ │ ││ │ │ │ │ 2/4│ 10449 │ RowExclusiveLock │ t virtualxid │ │ │ │ │ 1/1│ │ │ │ │ 1/0│ 10419 │ ExclusiveLock │ t virtualxid │ │ │ │ │ 2/4│ │ │ │ │ 2/4│ 10449 │ ExclusiveLock │ t (4 rows) this is from a standby. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Python 3.1 support
On ons, 2009-11-18 at 09:48 -0800, Joshua D. Drake wrote: Although I wonder if longer term (2.x is going to be support a long time) we will end up with frustration within the single source file trying to keep things straight. There are five million Python modules with C code out there with the same problem. Considerable effort has been put in by Python upstream to make the effort manageable. No one in their right mind is going to create two separate source files just because in the future the mythical differences will be too big, when clearly the effort is going into a direction to reduce the differences. If you look into the source file, there is already special code for Python 2.2, 2.3, 2.4, 2.5, 2.6, and now 3.1. The chunk for 3.1 is a bit bigger, but only a bit, and well, that's why it's 3.x and not 2.x. No one has ever suggested, we might need to support Python 2.2 for a long time, let's create a separate source file. I agree, there will probably need to be some configuration/build support on top of this, but that's something we should work out independently of how to manage the source file. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Python 3.1 support
On ons, 2009-11-18 at 08:43 -0800, Nathan Boley wrote: Again, I'm only one user. But so far I haven't seen anyone else speak up here, and clearly accepting this for inclusion will need nontrivial convincing. Well, FWIW, I am excited about better type integration. Let's clarify, as there are two different models being proposed here. The first approach, which is currently implemented (and some patches pending), is to convert a PostgreSQL type to the nearest Python type. For example, text to string, int to int, array to list, timestamp to datetime.datetime, etc. The other approach, which is what James Pye's new implementation proposes (as I understand it), is to convert PostgreSQL types into specially made Python objects, such as Postgres.types.record or Postgres.types.timestamp. Also, I am a little skeptical about this patch. I am sorry if this has already been discussed, but would this mean that I need to choose whether pl/python is built against Python 2.* or Python 3.*? Yeah, see later discussion about how to resolve this. But I think in practice, unless you use lots of print statements in your stored procedures (?!?), this problem is exaggerated. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Python 3.1 support
On ons, 2009-11-18 at 11:32 -0800, Nathan Boley wrote: I took a cursory look at this patch and, while the logic seems sound and roughly in line with the suggested python porting procedure, I'm not quite certain what this implies for potential future patches. For instance, if I wanted to write a type converter for bytea - the python 3 byte type would the expectation be that I ensure that it works in Python 2? Or is an ifdef that ignores it in the case of Python 2 OK, and we can just put a note in the docs. Note that this is already implemented. The main point of the patch is to provide a small compatibility layer so that these kinds of issues are practically nonexistent. The fact that you didn't notice might prove that the patch does its job. ;-) Also, how far back do we want to maintain 2.x compatibility? 2.0? We handle this on an ad hoc basis. We currently support Python 2.2 and later, and this cutoff exists -- this is my interpretation of history -- because 2.2 introduced iterators and no one bothered(?) to put ifdefs around the code in PL/Python that provides iterator support. Over the years, we will probably drop support for other older Python versions, but there is no process or plan for that. Right now, the support for Python 2.2 is about three lines, so it's not a bother, but when someone comes and implements a major feature that, say, requires Python 2.3, we can probably drop 2.2. But when the major feature requires 2.6, we probably don't want to drop 2.5 quite yet at this time. It's a judgment call. If I wanted to submit a patch that makes use of the list sort method, do I need to ensure that it can either use the cmp arguments or a key argument? Any patch one is likely to submit will be a C patch, not a Python patch. But anyway, the key argument was introduced in Python 2.4, and so we'd have to come to a decision in the community about whether Python 2.3 support is worth keeping versus the value of that new feature. See above. But anyway, this problem has nothing to do with my patch; it has already existed in the same form forever. What if I wanted to implement a set returning function that made use of an iterators next() method. Would I just put ifdefs around the code or a preprocessor definition that defines NEXT as next() for Python 2.x and __next__() for 3.x? Again, you would likely submit a C patch, and the iterator API is the same between 2.x and 3.x. I guess that my first impression is that Python broke compatibility for a reason, and that either plpython can't evolve, or it will quickly become impossible to maintain. I think this is an exaggeration of reality. Python 3 removed deprecated features. There is a perfectly good migration path that covers most code: Switch to Python 2.6, switch to the new features, remove the old features, switch to Python 3.x. This applies both on the Python and the C level. They did not break compatibility with the intention of making every module author out there reimplement their thing from scratch. Otherwise Python 2.6 would make very little sense at all. Take a look at an example closer to home: PostgreSQL breaks C API compatibility in almost every major release. We do this to remove cruft and support new features. The intent is not to make Slony and PostGIS and all the other modules reimplement their product from scratch every time. They put in a few ifdefs, sometimes they complain about it ;-), and then the problem is solved. That being said, I mostly buy the maintenance arguments from the previous discussion, but if we want to have plpython and plpython3, a bunch of defines and ifdefs does not seem like the best way to do this. These ifdefs were not my idea. They are in some cases directly and in some cases in spirit from the Python 2.6 header files, so they are the official way to do this. Would a better approach be to maintain compatibility layer? ie plython_compat.h/c plython2.c plython3.c Then patches that apply to a python3 can be applied to plython3.c and any changed function can be ripped out of plython_compat and moved into plpython2. As I tried to explain above, we have always had a rolling feature model of sorts, even across various Python 2.x versions. If you want to try it out, you could take the current source and split it up into plpython22.c, plpython23.c, etc. and see if that becomes useful. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Very bad FTS performance with the Polish config
ts_headline calls ts_lexize equivalent to break the text. Off course there is algorithm to process the tokens and generate the headline. I would be really surprised if the algorithm to generate the headline is somehow dependent on language (as it only processes the tokens). So Oleg is right when he says ts_lexize is something to be checked. ts_lexize performs well for all dictionaries, nothing to see here. It's ts_headline that's causing the problem. But that's just IMHO. I will try to replicate what you are trying to do but in the meantime can you run the same ts_headline under psql multiple times and paste the result. All the results I pasted had the medium run time out of multiple calls. These were certainly not some extreme corner cases. cheers, Wojciech Knapik -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
On Wed, 18 Nov 2009 22:12:18 -0500 Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: (4) drop *old* notifications if the queue is full. Since everyone has made the point that LISTEN is not meant to be a full queueing system, I have no problem dropping notifications LRU-style. NO, NO, NO, a thousand times no! That turns NOTIFY into an unreliable signaling system, and if I haven't made this perfectly clear yet, any such change will be committed over my dead body. If we are unable to insert a new message into the queue, the correct recourse is to fail the transaction that is trying to insert the *new* message. Not to drop messages from already-committed transactions. Failing the current transaction still leaves things in a consistent state, ie, you don't get messages from aborted transactions but that's okay because they didn't change the database state. +1 And in addition i don't like the idea of having the sender sitting around until there's room for more messages in the queue, because some very old backends didn't remove the stuff from the same. So, yes, just failing the current transaction seems reasonable. We are talking about millions of messages in the queue ... Bye -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
On Thu, Nov 19, 2009 at 4:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: There will now be a nonzero chance of transactions failing at commit because of queue full. If the chance is large this will be an issue. (Is it sane to wait for the queue to be drained?) Exactly. The whole idea of putting the notification system to an slru queue was to make this nonzero chance a very-close-to-zero nonzero chance. Currently with pages from 0..0x we can have something between 160,000,000 (no payload) and 2,000,000 (biggest payload) notifications in the queue at the same time. We are free to remove the slru limitation by making slru.c work with 8 character file names. Then you can multiply both limits by 32,000 and then it should be very-close-to-zero, at least in my point of view... The actual queue-full behavior is then (or maybe is already now) just a theoretical aspect that we need to agree on to make the whole concept sound. The current patch would just wait until some space becomes available in the queue and it guarantees that no notification is lost. Furthermore it guarantees that a transaction can listen on an unlimited number of channels and that it can send an unlimited number of notifications, not related to the size of the queue. It can also send that unlimited number of notifications if it is one of the listeners of those notifications. The only real limit is now the backend's memory but as long as nobody proves that he needs unlimited notifications with a limited amount of memory we just keep it like that. I will add a CHECK_FOR_INTERRUPTS() and resubmit so that you can cancel a NOTIFY while the queue is full. Also I've put in an optimization to only signal those backends in a queue full situation that are not yet up-to-date (which will probably turn out to be only one backend - the slowest that is in a long running transaction - after some time...). BTW, did we discuss the issue of 2PC transactions versus notify? The current behavior of 2PC with notify is pretty cheesy and will become more so if we make this change --- you aren't really guaranteed that the notify will happen, even though the prepared transaction did commit. I think it might be better to disallow NOTIFY inside a prepared xact. Yes, I have been thinking about that also. So what should happen when you prepare a transaction that has sent a NOTIFY before? Joachim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
On Thu, Nov 19, 2009 at 1:51 PM, Andreas 'ads' Scherbaum adsm...@wars-nicht.de wrote: And in addition i don't like the idea of having the sender sitting around until there's room for more messages in the queue, because some very old backends didn't remove the stuff from the same. The only valid reason why a backend has not processed the notifications in the queue must be a backend that is still in a transaction since then (and has executed LISTEN some time before). Joachim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Question about ECPGset_noind_null() and ECPGis_noind_null()
Hi, my question is that what platform were these functions developed and tested? We have come across a value that fails a NOT NULL constraint upon INSERT under HP-UX/IA64, but not under x86-64 Linux. The value in question is 1.9998 assigned to a double variable. Under HP-UX/IA64, testing with risnull() from the application indeed returns true, but under Linux/x86-64 returns false. I will test rsetnull() results on real Informix under HP-UX/IA64. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] add more frame types in window functions (ROWS)
2009/11/19 Andrew Gierth and...@tao11.riddles.org.uk: Here's the rest of the review, as far as I've taken it given the problems with the code. The patch applied cleanly and includes regression tests but not docs. Small nitpicks: there are some comments not updated (e.g. the big one at the start of eval_windowaggregates). A couple of lines are commented-out using C++ comments. OK. It's tough for me to rewrite that big part of comment but I'll try it. The overall approach seems ok, and the parser stuff seems fine to me. These are the issues I've found that make it not committable in its present form (including the ones I mentioned before): - missing _readWindowFrameDef function (all nodes that are output from parse analysis must have both _read and _out functions, otherwise views can't work) - the A_Const nodes should probably be transformed to Const nodes in parse analysis, since A_Const has no _read/_out functions, which means changing the corresponding code in the executor. A_Const/Const will be replace by Expr, to cover any expression without local Var. - ruleutils.c not updated to deparse the newly added window options - leaks memory like it's going out of style The memory leakage is caused by not resetting any memory contexts when throwing away all the aggregate state when advancing the start of the window frame. This looks like it will require a rethink of the memory management being used; it's not enough just to pfree copies of the transition values (which you don't appear to be doing), you have to reset the memory context that was exposed to the transition functions via context-wincontext. So the current setup of a single long-lived context won't work; you'll need a long-lived one, plus an additional one that you can reset any time the aggregates need to be re-initialized. (And if you're not going to break existing aggregate functions, WindowAggState.wincontext needs to be the one that gets reset.) Hmm, good point. Though I doubt we need two contexts for this because we have not so far (and we already have tmpcontext for that purpose), memory leakage probably seems to happen. I'll check it out. Thanks for your elaborate review anyway. All I was worried about is now clear. It will be lucky if I can update my patch until next week. So please keep it Waiting on Author. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 (4) drop *old* notifications if the queue is full. Since everyone has made the point that LISTEN is not meant to be a full queueing system, I have no problem dropping notifications LRU-style. NO, NO, NO, a thousand times no! +1. Don't even think about going there. /me gives horrified shudder ... even that was pretty hard. There will now be a nonzero chance of transactions failing at commit because of queue full. If the chance is large this will be an issue. (Is it sane to wait for the queue to be drained?) I think this chance will be pretty small - you need a *lot* of unread notifications before this edge case is reached, so I think we can be pretty severe in our response, and put the responsibility on cleanup on the user, rather than having the backend try to move things around, cleanup the queue selectively, etc. BTW, did we discuss the issue of 2PC transactions versus notify? The current behavior of 2PC with notify is pretty cheesy and will become more so if we make this change --- you aren't really guaranteed that the notify will happen, even though the prepared transaction did commit. I think it might be better to disallow NOTIFY inside a prepared xact. That's a tough one. On the one hand, simply stating that NOTIFY and 2PC don't play together in the docs would be a straightforward solution (and not a bad one, as 2PC is already rare and delicate and should not be used lightly). But what I really don't like the is the idea of a notify that *may* work or may not - so let's keep it boolean: it either works 100% of the time with 2PC, or doesn't at all. Should we throw a warning or error if a client attempts to combine the two? - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200911190857 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAksFTxEACgkQvJuQZxSWSsjkiACfYeevKZ0QngZcZXUoTPP6wXh6 iOMAoLvkPlEV6ywGqyaaloqQrnoryILU =rioB -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timezones (in 8.5?)
On Wed, Nov 18, 2009 at 11:18 PM, Andrew Gierth and...@tao11.riddles.org.uk wrote: Kevin == Kevin Grittner kevin.gritt...@wicourts.gov writes: If he meant (A), then you store the event as: (ts,tz) = (timestamp '2010-07-27 10:30:00', 'Chile/Santiago') If he meant (B), then you store the event as (tsz,tz) = (timestamp '2010-07-27 10:30:00' at time zone 'Chile/Santiago', 'Chile/Santiago') Kevin You seem to be agreeing that these problems can't be solved Kevin without storing a time zone string in addition to the Kevin timestamp. As I read it, Hernán was wishing for types which Kevin include this, rather than having to do the above dance with Kevin multiple values. Right, but including more data in a single type is the wrong approach, since it complicates the semantics and interferes with normalization. For example, if you have a type T which incorporates a timestamp and a timezone, what semantics does the T = T operator have? What semantics apply if the definitions of timezones change? What if you're storing times of events at specific places; in that case you want to associate the timezone with the _place_ not the event (so that if the timezone rules change, moving the place from one timezone to another, you only have to change the place, not all the events that refer to it). Also, if someone DOES want to use these together, isn't that what composite types are for? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] xpath_table equivalent
Scott Bailey wrote: I agree that the syntax of XMLTABLE is odd. But not demonstrably worse than xpathtable. That's not saying much. I dislike both. Why the SQL committee feels the need to invent arcane special case grammar rules is beyond me. I understand why the author of xpathtable designed it the way he did, but it's still ugly in my book. As I said, with LATERAL we could produce a much cleaner functional equivalent. If we are going to exert effort on it, why not do it in a standards compliant way? Otherwise I'd suggest a stop gap of just adding some support functions to make it easier to extract a scalar value from a node. Something like what I did here. http://scottrbailey.wordpress.com/2009/06/19/xml-parsing-postgres/ I think that's an orthogonal issue, really. There's probably a good case for such a function whether or not we do something like xpath_table. The nice thing about XMLTABLE is that it adds xquery support. I think the majority of xquery engines seem to be written in Java. XQuilla is C++. I'm not sure if our licensing is compatible, but it I would love the irony of using Berkeley DB XML (formerly Sleepycat) now that its owned by Oracle. XQuery is a whole other question. Adding another library dependency is something we try to avoid. Zorba http://www.zorba-xquery.com/ might work, but it appears to have its own impressive list of dependencies (why does it require both libxml2 and xerces-c? That looks a bit redundant.) Even if we did implement XMLTABLE, I think I'd probably be inclined to start by limiting it to plain XPath, without the FLWOR stuff. I think that would satisfy the vast majority of needs, although you might feel differently. (Do a Google for XMLTABLE - every example I found uses plain XPath expressions.) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Joachim Wieland wrote: On Thu, Nov 19, 2009 at 4:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, did we discuss the issue of 2PC transactions versus notify? The current behavior of 2PC with notify is pretty cheesy and will become more so if we make this change --- you aren't really guaranteed that the notify will happen, even though the prepared transaction did commit. I think it might be better to disallow NOTIFY inside a prepared xact. That will make anyone currently using 2PC with notify/listen unhappy. Yes, I have been thinking about that also. So what should happen when you prepare a transaction that has sent a NOTIFY before? From the user's point of view, nothing should happen at prepare. At a quick glance, it doesn't seem hard to support 2PC. Messages should be put to the queue at prepare, as just before normal commit, but the backends won't see them until they see that the XID has committed. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Heikki Linnakangas wrote: Joachim Wieland wrote: On Thu, Nov 19, 2009 at 4:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: Yes, I have been thinking about that also. So what should happen when you prepare a transaction that has sent a NOTIFY before? From the user's point of view, nothing should happen at prepare. At a quick glance, it doesn't seem hard to support 2PC. Messages should be put to the queue at prepare, as just before normal commit, but the backends won't see them until they see that the XID has committed. Yeah, but if the server is restarted after the PREPARE but before the COMMIT, the notification will be lost, since all notification queue entries are lost upon restart with the slru design, no? best regards, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] xpath_table equivalent
On Thu, Nov 19, 2009 at 12:03 AM, Scott Bailey arta...@comcast.net wrote: The nice thing about XMLTABLE is that it adds xquery support. I think the majority of xquery engines seem to be written in Java. XQuilla is C++. I'm not sure if our licensing is compatible, but it I would love the irony of using Berkeley DB XML (formerly Sleepycat) now that its owned by Oracle. It's very much not compatible. Berkeley DB is not free for commercial use. I anticipate that this would be a problem both for commericial users of PostgreSQL and also for commercial PostgreSQL forks. Besides, that's a lot of code to suck into Postgres to do, uh, a lot of things that we already do in other ways. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Syntax for partitioning
Hi, Robert Haas wrote: Settling on a syntax, and an internal representation for that syntax, I've been under the impression that this was only about syntax. What are the internal additions? Generally speaking, I'd agree with Simon or even vote for doing the internals first and add the syntactic sugar only later on. seems like it will make subsequent discussions about those projects considerably more straightforward, ..or subsequent implementations more complicated, because you have to support an awkward syntax. and it has some value in and of itself since similar notation is used by other databases. That point is well taken, but it would be more compelling if it were the same or at least a compatible syntax. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] xpath_table equivalent
Robert Haas wrote: On Thu, Nov 19, 2009 at 12:03 AM, Scott Bailey arta...@comcast.net wrote: The nice thing about XMLTABLE is that it adds xquery support. I think the majority of xquery engines seem to be written in Java. XQuilla is C++. I'm not sure if our licensing is compatible, but it I would love the irony of using Berkeley DB XML (formerly Sleepycat) now that its owned by Oracle. It's very much not compatible. Berkeley DB is not free for commercial use. I anticipate that this would be a problem both for commericial users of PostgreSQL and also for commercial PostgreSQL forks. Besides, that's a lot of code to suck into Postgres to do, uh, a lot of things that we already do in other ways. XQuilla, however, is not Berkely DB. And its license is Apache v2. It is built on Xerces-C, although it appears at first glance to have less dependencies that Zorba. I'm not sure how pluggable the XML parser engine is (or could be made). cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rejecting weak passwords
Albe Laurenz laurenz.a...@wien.gv.at writes: I did not know that contrib modules get translated too, else I would have thrown the error messages there. I'm not sure whether contrib is currently covered by the translation teams, but it could someday be. In any case, an inadequate error message is not made less inadequate by translating it ;-) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tatsuo Ishii wrote: Sorry for confusion. My example is under normal PostgreSQL, not under HS enabled. You get the same result in standby: AFAICT Tatsuo's example just shows that we might wish to add a check for read-only transaction mode before parsing an INSERT/UPDATE/DELETE command. But it seems relatively minor in any case --- at the worst you'd get an unexpected error message, no? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tatsuo Ishii wrote: Sorry for confusion. My example is under normal PostgreSQL, not under HS enabled. You get the same result in standby: AFAICT Tatsuo's example just shows that we might wish to add a check for read-only transaction mode before parsing an INSERT/UPDATE/DELETE command. But it seems relatively minor in any case --- at the worst you'd get an unexpected error message, no? Right, it's harmless AFAICS. And it might actually be useful to be able to prepare all queries right after connecting, even though the connection is in not yet read-write. It's the documentation (in source code or README) that's lacking, and perhaps we should add more explicit checks for the can't happen cases, just in case. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch to change a pg_restore message
Hi, pg_restore --help gives this message for the --no-tablespaces parameter: --no-tablespaces do not dump tablespace assignments The message should say restore and not dump. You'll find a patch attached that fixes this issue. Thanks. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com Index: src/bin/pg_dump/pg_restore.c === RCS file: /opt/cvsroot_postgresql/pgsql/src/bin/pg_dump/pg_restore.c,v retrieving revision 1.100 diff -c -p -c -r1.100 pg_restore.c *** src/bin/pg_dump/pg_restore.c 11 Jun 2009 14:49:07 - 1.100 --- src/bin/pg_dump/pg_restore.c 19 Nov 2009 15:41:23 - *** usage(const char *progname) *** 430,436 printf(_( --no-data-for-failed-tables\n do not restore data of tables that could not be\n created\n)); ! printf(_( --no-tablespaces do not dump tablespace assignments\n)); printf(_( --role=ROLENAME do SET ROLE before restore\n)); printf(_( --use-set-session-authorization\n use SET SESSION AUTHORIZATION commands instead of\n --- 430,436 printf(_( --no-data-for-failed-tables\n do not restore data of tables that could not be\n created\n)); ! printf(_( --no-tablespaces do not restore tablespace assignments\n)); printf(_( --role=ROLENAME do SET ROLE before restore\n)); printf(_( --use-set-session-authorization\n use SET SESSION AUTHORIZATION commands instead of\n -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Very bad FTS performance with the Polish config
Wojciech Knapik webmas...@wolniartysci.pl writes: Tom Lane wrote: I tried to duplicate this test, but got no further than here: ERROR: syntax error CONTEXT: line 174 of configuration file /home/tgl/testversion/share/postgresql/tsearch_data/polish.affix: L E C -C,GÅEM #zalec (15a) Here are the files I used (polish.affix, polish.dict already generated): http://wolniartysci.pl/pl.tar.gz Your files were the same as mine. I eventually figured out the problem was I was using C locale, in which some of those letters aren't letters. (I wonder whether the tsearch config file parsers could be made less sensitive to this by avoiding t_isalpha tests.) In pl_PL.ut8 locale I could see that the example is indeed much slower. Oleg is right that the fundamental difference is that this Polish configuration is using an ispell dictionary where the simple English configuration is not. But, just for the record, here's what an oprofile profile looks like: samples %image name symbol name 7480 20.9477 postgres RS_execute 5370 15.0386 postgres pg_utf_mblen 4138 11.5884 postgres pg_mblen 3756 10.5187 postgres mb_strchr 2880 8.0654 postgres FindWord 2754 7.7126 postgres CheckAffix 1576 4.4136 postgres NormalizeSubWord 966 2.7053 postgres FindAffixes 896 2.5092 postgres TParserGet 742 2.0780 postgres AllocSetAlloc 420 1.1762 postgres AllocSetFree 396 1.1090 postgres addHLParsedLex 384 1.0754 postgres LexizeExec So about 55% of the time is going into affix pattern matching. I wonder whether that couldn't be made faster. A lot of the cycles are spent on coping with variable-length characters --- perhaps the ispell code should convert to wchar representation before doing this? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Syntax for partitioning
On Thu, Nov 19, 2009 at 9:58 AM, Markus Wanner mar...@bluegap.ch wrote: Hi, Robert Haas wrote: Settling on a syntax, and an internal representation for that syntax, I've been under the impression that this was only about syntax. What are the internal additions? I haven't looked at it in detail, but it adds a new pg_partition table. Whether that table is suitably structured for use by the optimizer is not clear to me. Generally speaking, I'd agree with Simon or even vote for doing the internals first and add the syntactic sugar only later on. That's not really possible in this case. The internals consist of taking advantage of the fact that we have explicit knowledge of how the partitions are defined vs. just relying on the (slow) constraint exclusion logic. We can't do that unless, in fact, we have that explicit knowledge, and that requires inventing syntax. That point is well taken, but it would be more compelling if it were the same or at least a compatible syntax. There's been an effort to make it close, but I haven't followed it in enough detail to know how close. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Florian G. Pflug wrote: Heikki Linnakangas wrote: At a quick glance, it doesn't seem hard to support 2PC. Messages should be put to the queue at prepare, as just before normal commit, but the backends won't see them until they see that the XID has committed. Yeah, but if the server is restarted after the PREPARE but before the COMMIT, the notification will be lost, since all notification queue entries are lost upon restart with the slru design, no? That's why they're stored in the 2PC state file in pg_twophase. See AtPrepare_Notify(). Hmm, thinking about this a bit more, I don't think the messages should be sent until commit (ie. 2nd phase). Although the information is safe in the state file, if anyone starts to LISTEN between the PREPARE TRANSACTION and COMMIT PREPARED calls, he would miss the notifications. I'm not sure if it's well-defined what happens if someone starts to LISTEN while another transaction has already sent a notification, but it would be rather surprising if such a window existed where it doesn't exist with non-prepared transactions. A better approach is to do something similar to what we do now: at prepare, just store the notifications in the state file like we do already. In notify_twophase_postcommit(), copy the messages to the shared queue. Although it's the same approach we have now, it becomes a lot cleaner with the patch, because we're not piggybacking the messages on the backend-private queue of the current transaction, but sending the messages directly on behalf of the prepared transaction being committed. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: A better approach is to do something similar to what we do now: at prepare, just store the notifications in the state file like we do already. In notify_twophase_postcommit(), copy the messages to the shared queue. Although it's the same approach we have now, it becomes a lot cleaner with the patch, because we're not piggybacking the messages on the backend-private queue of the current transaction, but sending the messages directly on behalf of the prepared transaction being committed. This is still ignoring the complaint: you are creating a clear risk that COMMIT PREPARED will fail. I'm not sure that it's really worth it, but one way this could be made safe would be for PREPARE to reserve the required amount of queue space, such that nobody else could use it during the window from PREPARE to COMMIT PREPARED. On the whole I'd be just as happy to disallow NOTIFY in a 2PC transaction. We have no evidence that anyone out there is using the combination, and if they are, they can do the work to make it safe. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
On Thu, 19 Nov 2009 14:23:57 +0100 Joachim Wieland wrote: On Thu, Nov 19, 2009 at 1:51 PM, Andreas 'ads' Scherbaum adsm...@wars-nicht.de wrote: And in addition i don't like the idea of having the sender sitting around until there's room for more messages in the queue, because some very old backends didn't remove the stuff from the same. The only valid reason why a backend has not processed the notifications in the queue must be a backend that is still in a transaction since then (and has executed LISTEN some time before). Yes, i know. The same backend is probably causing more trouble anyway (blocking vacuum, xid wraparound, ...). -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: A better approach is to do something similar to what we do now: at prepare, just store the notifications in the state file like we do already. In notify_twophase_postcommit(), copy the messages to the shared queue. Although it's the same approach we have now, it becomes a lot cleaner with the patch, because we're not piggybacking the messages on the backend-private queue of the current transaction, but sending the messages directly on behalf of the prepared transaction being committed. This is still ignoring the complaint: you are creating a clear risk that COMMIT PREPARED will fail. I'm not sure that it's really worth it, but one way this could be made safe would be for PREPARE to reserve the required amount of queue space, such that nobody else could use it during the window from PREPARE to COMMIT PREPARED. I'd see no problem with COMMIT PREPARED failing, as long as it was possible to retry the COMMIT PREPARED at a later time. There surely are other failure cases for COMMIT PREPARED too, like an IO error that prevents the clog bit from being set, or a server crash half-way through COMMIT PREPARED. best regards, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Florian G. Pflug f...@phlo.org writes: Tom Lane wrote: This is still ignoring the complaint: you are creating a clear risk that COMMIT PREPARED will fail. I'd see no problem with COMMIT PREPARED failing, as long as it was possible to retry the COMMIT PREPARED at a later time. There surely are other failure cases for COMMIT PREPARED too, like an IO error that prevents the clog bit from being set, or a server crash half-way through COMMIT PREPARED. Yes, there are failure cases that are outside our control. That's no excuse for creating one that's within our control. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timezones (in 8.5?)
Robert Haas robertmh...@gmail.com wrote: On Wed, Nov 18, 2009 at 11:18 PM, Andrew Gierth and...@tao11.riddles.org.uk wrote: Kevin == Kevin Grittner kevin.gritt...@wicourts.gov writes: If he meant (A), then you store the event as: (ts,tz) = (timestamp '2010-07-27 10:30:00', 'Chile/Santiago') If he meant (B), then you store the event as (tsz,tz) = (timestamp '2010-07-27 10:30:00' at time zone 'Chile/Santiago', 'Chile/Santiago') Kevin You seem to be agreeing that these problems can't be solved Kevin without storing a time zone string in addition to the Kevin timestamp. As I read it, Hernán was wishing for types Kevin which include this, rather than having to do the above Kevin dance with multiple values. Right, but including more data in a single type is the wrong approach, since it complicates the semantics and interferes with normalization. Or, one could say, it encapsulates the semantics within the type's operators, avoiding the need to repeat the logic everywhere, or to use more verbose explicit function calls. For example, if you have a type T which incorporates a timestamp and a timezone, what semantics does the T = T operator have? What semantics apply if the definitions of timezones change? I'd rather sort that out once and implement the desired semantics in the operators for a new type than to count on application programmers doing it consistently each time. Wouldn't you? What if you're storing times of events at specific places; in that case you want to associate the timezone with the _place_ not the event (so that if the timezone rules change, moving the place from one timezone to another, you only have to change the place, not all the events that refer to it). I'm not sure I quite followed you there, but Hernán's example specifically called for storing 'Chile/Santiago', not a UTC offset or something as easily changed as the 'CLT' or 'CLST' time zone designations -- so it is tied to a place rather more closely than anything else. I think that was part of his point -- that for civil time you care about what the clock on a typical business's wall at that place will read on that date, regardless of what changes might happen in time zone definitions. Also, if someone DOES want to use these together, isn't that what composite types are for? I'm going to plead both ignorance and laziness here. My use of composite types is limited, so I don't know, offhand, whether you can define a set of operators for a composite type which will provide the consistent behavior with convenient operators which Hernán seems to want. If they allow that, then it certainly seems like the way to go, so that the component parts of the abstraction we've been calling civil time can be easily accessed. If not, they're not suited to what Hernán wants (as I understand it). For the record, this discussion has made me realize that I don't care as much about including such information with tsz as with ts. The tsz enhancement wouldn't change the semantics of the object at all, as far as I can see, beyond it's default presentation when you turn it into a string. That's worth something, but pales in comparison to the value of the civil time concept, which would actually match the common usage in scheduling business meetings and most other every-day activities. I think the popularity of physical time is that it is so concrete. The reality of usage of date and time, though, is that various abstractions which aren't tightly coupled to physical time are common and useful. The civil time issues are one aspect of that. (And as far as I'm concerned, leap seconds can be totally ignored for civil time -- there's a nice round clock up on my wall with a big hand and a little hand and a second hand all spinning around, and there's no place on that clock face for a 61st or 62nd second in any minute, ever.) And those who don't think it's useful be able to add one month to the 31st of January and get a date as a result to which you can add one month and get the 31st of March -- well, come the cultural revolution I plan to see to it that they do nothing but write financial applications for five years :-) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Python 3.1 support
On Nov 19, 2009, at 3:12 AM, Peter Eisentraut wrote: The other approach, which is what James Pye's new implementation proposes (as I understand it), is to convert PostgreSQL types into specially made Python objects, such as Postgres.types.record or Postgres.types.timestamp. Convert is not a good word choice. The Datum of the parameter is stored inside a new Python object(that only holds a Datum). So more like copied into Python memory, and associated with its respective type. Wrapped in a Python object? One cool thing about doing it this way, is that if you just pass parameters forward to a prepared statement, there's no type I/O overhead. Not a huge performance win for common cases, but if someone were passing larger arrays around, it could be quite beneficial. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Tom Lane wrote: Florian G. Pflug f...@phlo.org writes: Tom Lane wrote: This is still ignoring the complaint: you are creating a clear risk that COMMIT PREPARED will fail. I'd see no problem with COMMIT PREPARED failing, as long as it was possible to retry the COMMIT PREPARED at a later time. There surely are other failure cases for COMMIT PREPARED too, like an IO error that prevents the clog bit from being set, or a server crash half-way through COMMIT PREPARED. Yes, there are failure cases that are outside our control. That's no excuse for creating one that's within our control. True. On the other hand, people might prefer having to deal with (very unlikely) COMMIT PREPARED *transient* failures over not being able to use NOTIFY together with 2PC at all. Especially since any credible distributed transaction manager has to deal with COMMIT PREPARED failures anyway. Just my $0.02, though. best regards, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: A better approach is to do something similar to what we do now: at prepare, just store the notifications in the state file like we do already. In notify_twophase_postcommit(), copy the messages to the shared queue. Although it's the same approach we have now, it becomes a lot cleaner with the patch, because we're not piggybacking the messages on the backend-private queue of the current transaction, but sending the messages directly on behalf of the prepared transaction being committed. This is still ignoring the complaint: you are creating a clear risk that COMMIT PREPARED will fail. I'm not sure that it's really worth it, but one way this could be made safe would be for PREPARE to reserve the required amount of queue space, such that nobody else could use it during the window from PREPARE to COMMIT PREPARED. Hmm, ignoring 2PC for a moment, I think the patch suffers from a little race condition: Session 1: BEGIN; Session 1: INSERT INTO foo ..; Session 1: NOTIFY 'foo'; Session 1: COMMIT -- commit begins Session 1: [commit processing runs AtCommit_NotifyBeforeCommit()] Session 2: LISTEN 'foo'; Session 2: SELECT * FROM foo; Session 1: [AtCommit_NotifyAfterCommit() signals listening backends] Session 2: [waits for notifications] Because session 2 began listening after session 1 had already sent its notifications, it missed them. But the SELECT didn't see the INSERT, because the inserting transaction hadn't fully finished yet. The window isn't as small as it might seem at first glance, because the WAL is fsynced between the BeforeCommit and AfterCommit actions. I think we could fix that by arranging things so that a backend refrains from advancing its own 'pos' beyond the first notification it has written itself, until commit is completely finished. I'm not sure but might already be true if we don't receive interrupts between BeforeCommit and AfterCommit. LISTEN can then simply start reading from QUEUE_TAIL instead of QUEUE_HEAD, and in the above example session 2 will see the notifications sent by session 1. That will handle 2PC as well. We can send the notifications in prepare-phase, and any LISTEN that starts after the prepare-phase will see the notifications because they're still in the queue. There is no risk of running out of disk space in COMMIT PREPARED, because the notifications have already been written to disk. However, the notification queue can't be truncated until the prepared transaction finishes; does anyone think that's a show-stopper? On the whole I'd be just as happy to disallow NOTIFY in a 2PC transaction. We have no evidence that anyone out there is using the combination, and if they are, they can do the work to make it safe. Yeah, I doubt we'd hear many complaints in practice. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Python 3.1 support
On ons, 2009-11-18 at 12:28 -0500, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: On Wed, 2009-11-18 at 12:06 -0500, Tom Lane wrote: Yes. That's exactly what I was complaining about upthread. I'm not a Python user, but from what I can gather of the 2-to-3 changes, having to choose one at package build time is going to be a disaster. Agreed. We really need to have a plpython and plpython3. Peter was concerned about duplicative maintenance effort, but what I think this patch shows is that (at least for the near future) both could be built from a single source file. What we need is configure and makefile support to do that. By the way, it occurred to me that having two different versions of libpython loaded into the same process is probably not going to work sanely. So whatever solution we come up with for the Python 3 transition, the possibilities for a jolly back-and-forth are probably going to be quite limited. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Python 3.1 support
On ons, 2009-11-18 at 13:36 -0700, James Pye wrote: On Nov 18, 2009, at 8:37 AM, Peter Eisentraut wrote: The question is whether it helps the user, not the implementer. Sure, but do you have a patch waiting to implement tracebacks? I'd argue the reason it's never been done is due to the way procedures are currently managed in PL/Python. And *without some significant refactoring*, any patch fully implementing tracebacks is going to be a seriously ugly hack. What helped the implementer here would help the user. But you wouldn't, for example, get away with breaking SQL (or even improving it incompatibly) to facilitate a better elog. As far as I can tell, it just creates more typing for no benefit whatsoever. def main(*args): is annoying, but not entirely lamentable... It's explicit, as well(no need to document munging that occurs behind the scenes). Also, compare the cases where you need to cache some initialized data: if 'key' not in SD: ... SD['key'] = my_newly_initialized_data ... With function modules, SD is not needed as you have your module globals to keep your locally cached data in: ... data = my_newly_initialized_data def main(*args): ... I can see that this creates other options for structuring code, but it doesn't actually match my way of thinking. (Obviously, I'm biased, but anyway.) I think of a PL/Python function as a Python script file stored in the database. When you call it, arguments are passed just like a Python script receives arguments from the shell. When Python scripts want to share data, they might use a file (or perhaps a database server in advanced cases) and do if not file exists: create the file fill it with data This is in my mind quite analogous to how the SD business works. The analogy to your approach, as I understand it, would be that multiple instances of the same script file will automatically share their global variables. That could be quite interesting, actually, but it's not how it works, and in most cases it's better that way. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Python 3.1 support
Peter Eisentraut pete...@gmx.net writes: By the way, it occurred to me that having two different versions of libpython loaded into the same process is probably not going to work sanely. Why not? There's no way they'd even know about each other. We tell the loader not to make the symbols globally visible. But in any case, my main concern here is that I don't want to have to predetermine which python version a user of Red Hat/Fedora will have to use. If they can only use one at a time, that's still a good bit better than not having a choice at all. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRIGGER with WHEN clause
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: [ TRIGGER WHEN patch ] I'm starting to work this over now, and I've found one rather serious omission: FreeTriggerDesc doesn't free the expression tree. This means that trigger WHEN clauses will leak memory in CacheMemoryContext any time we do a relcache flush on the relation having the trigger. Over time that could be pretty nasty. There is no mechanism for freeing an expression tree explicitly, and creating one is not feasible because of the possibility of multiple references to subtrees, so this isn't trivial to fix. There are two alternatives that seem reasonable to me: * Keep the expression in nodeToString string form within the TriggerDesc structure; then it's just one more pfree in FreeTriggerDesc. The main disadvantage of this is that we'd have to repeat stringToNode every time the trigger is used. This might not be a big deal considering the other overhead of preparing an expression for execution --- check constraint expressions are handled that way IIRC --- but it's still a bit annoying. * Create a separate memory context for each TriggerDesc. This would simplify FreeTriggerDesc() to a MemoryContextDelete call, which seems attractive from both speed and code maintenance standpoints; but it would probably end up wasting a fair amount of space since the context would likely be mostly empty in most cases. Not sure which way to jump. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question about ECPGset_noind_null() and ECPGis_noind_null()
Boszormenyi Zoltan írta:
Hi,
my question is that what platform were these
functions developed and tested?
We have come across a value that fails a NOT NULL
constraint upon INSERT under HP-UX/IA64, but not
under x86-64 Linux. The value in question is
1.9998 assigned to a double variable.
Under HP-UX/IA64, testing with risnull() from
the application indeed returns true, but under
Linux/x86-64 returns false.
I will test rsetnull() results on real Informix under
HP-UX/IA64.
I have tested it under ESQL/C on HP-UX/ia64 and
this happened:
- rsetnull() on a double value creates
FF FF FF FF FF FF FF FF
- the value causing the error above is
3F FF FF FF FF FF FF FF
It seems that this function in ecpglib/misc.c has
an off-by-one bug as it's interpreted by the HP-UX CC:
static bool
_check(unsigned char *ptr, int length)
{
for (; length 0 ptr[--length] == 0xff;);
if (length = 0)
return true;
return false;
}
I suspect that GCC does the --length after checking
length 0 and before checking the ptr[...] == 0xff,
but HP CC does it before checking length 0.
The attached patch solves the problem.
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
*** pgsql.4/src/interfaces/ecpg/ecpglib/misc.c.old 2009-11-19 19:50:38.0 +0100
--- pgsql.4/src/interfaces/ecpg/ecpglib/misc.c 2009-11-19 19:51:23.0 +0100
***
*** 364,373
static bool
_check(unsigned char *ptr, int length)
{
! for (; length 0 ptr[--length] == 0xff;);
! if (length = 0)
! return true;
! return false;
}
bool
--- 364,375
static bool
_check(unsigned char *ptr, int length)
{
! int i;
! for (i = 0; i length ptr[i] == 0xff; i++)
! ;
! if (i length)
! return false;
! return true;
}
bool
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question about ECPGset_noind_null() and ECPGis_noind_null()
Boszormenyi Zoltan z...@cybertec.at writes: for (; length 0 ptr[--length] == 0xff;); I suspect that GCC does the --length after checking length 0 and before checking the ptr[...] == 0xff, but HP CC does it before checking length 0. If it does, that is *unquestionably* a bug in HP's CC and should be reported to them. However, the code is sufficiently unreadable to be worth rewriting anyhow. Your suggestion is an improvement but personally I'd plump for int i; for (i = 0; i length; i++) if (ptr[i] != 0xff) return false; return true; regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question about ECPGset_noind_null() and ECPGis_noind_null()
Tom Lane wrote: Boszormenyi Zoltan z...@cybertec.at writes: for (; length 0 ptr[--length] == 0xff;); I suspect that GCC does the --length after checking length 0 and before checking the ptr[...] == 0xff, but HP CC does it before checking length 0. If it does, that is *unquestionably* a bug in HP's CC and should be reported to them. Wow, I recall fighting HP over a bad compiler bug (although not as bad as this would be) 15 years ago. Their official response amounted to we don't care and we're not going to fix it. Maybe not much has changed. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Python 3.1 support
On Nov 19, 2009, at 11:32 AM, Peter Eisentraut wrote: But you wouldn't, for example, get away with breaking SQL (or even improving it incompatibly) to facilitate a better elog. This doesn't fit the situation. I'm not breaking PL/Python. I'm trying to add PL/Python3. =) I think of a PL/Python function as a Python script file stored in the database. For Python, I think that's a mistake. Python scripts are independent applications. [tho, I think this does illuminate our perspectives...] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question about ECPGset_noind_null() and ECPGis_noind_null()
Tom Lane írta: Boszormenyi Zoltan z...@cybertec.at writes: for (; length 0 ptr[--length] == 0xff;); I suspect that GCC does the --length after checking length 0 and before checking the ptr[...] == 0xff, but HP CC does it before checking length 0. If it does, that is *unquestionably* a bug in HP's CC and should be reported to them. Is it *really* a bug? I recalled a comment from my C teacher in '92 or '93 about this exact issue, that the prefix/postfix increment/decrement operators are executed in the statement in an implementation-defined order, i.e. they can be freely reordered or placed anywhere in the expression, provided that the postfix operator's evaluation is earlier than the usage of the variable it's used on and evaluation is later than the variable usage in the postfix case. This means that their usage has to be minimized so the result is unambiguous. I.e. in the common usage: str1[pos1++] = str2[pos2++]; these execution orders are possible and all give the same result: 1. evaluate str2[pos2] increment pos2 assign the above value to str1[pos1] increment pos1 or 2. evaluate str2[pos2] assign the above value to str1[pos1] increment pos2 increment pos1 or 3. evaluate str2[pos2] assign the above value to str1[pos1] increment pos1 increment pos2 In the case of for (; length 0 ptr[--length] == 0xff;); the different evaluation orders may give different expression results. But 17 years is a long time, the C language specification has changed a lot. GCC definitely does the most sensible order but I didn't know this behaviour is specified in the C language. However, the code is sufficiently unreadable to be worth rewriting anyhow. Your suggestion is an improvement but personally I'd plump for int i; for (i = 0; i length; i++) if (ptr[i] != 0xff) return false; return true; Yes, it's better than my version. Best regards, Zoltán Böszörményi regards, tom lane -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question about ECPGset_noind_null() and ECPGis_noind_null()
Boszormenyi Zoltan írta: Tom Lane írta: Boszormenyi Zoltan z...@cybertec.at writes: for (; length 0 ptr[--length] == 0xff;); I suspect that GCC does the --length after checking length 0 and before checking the ptr[...] == 0xff, but HP CC does it before checking length 0. If it does, that is *unquestionably* a bug in HP's CC and should be reported to them. Is it *really* a bug? I recalled a comment from my C teacher in '92 or '93 about this exact issue, that the prefix/postfix increment/decrement operators are executed in the statement in an implementation-defined order, i.e. they can be freely reordered or placed anywhere in the expression, provided that the postfix operator's evaluation ^^ Above is the prefix case obviously... is earlier than the usage of the variable it's used on and evaluation is later than the variable usage in the postfix case. This means that their usage has to be minimized so the result is unambiguous. -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question about ECPGset_noind_null() and ECPGis_noind_null()
Boszormenyi Zoltan wrote: Is it *really* a bug? I recalled a comment from my C teacher in '92 or '93 about this exact issue, that the prefix/postfix increment/decrement operators are executed in the statement in an implementation-defined order, Not if they come after a short-circuit operator such as - after all, that's what short-circuit evaluation implies. If the left hand operand of is false the right hand should not be evaluated at all. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question about ECPGset_noind_null() and ECPGis_noind_null()
Andrew Dunstan and...@dunslane.net writes: Boszormenyi Zoltan wrote: Is it *really* a bug? I recalled a comment from my C teacher in '92 or '93 about this exact issue, that the prefix/postfix increment/decrement operators are executed in the statement in an implementation-defined order, Not if they come after a short-circuit operator such as - after all, that's what short-circuit evaluation implies. If the left hand operand of is false the right hand should not be evaluated at all. Yes. is a sequence point and the compiler is not allowed to move side-effects across a sequence point. What your C teacher was warning you against was things like a[i] = i++; '=' is not a sequence point so it's undefined which array index will be stored into. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Architecture of walreceiver (Streaming Replication)
Fujii Masao wrote: On Tue, Nov 3, 2009 at 12:33 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Nov 2, 2009 at 10:14 AM, Euler Taveira de Oliveira eu...@timbira.com wrote: BTW, are you going to submit another WIP patch for next commitfest? Well, Heikki was going to keep working on this and Hot Standby between CommitFests until it gets committed, but things seem to be stalled at the moment, possibly because Heikki is tied up with internal EnterpriseDB projects. I don't think the hold-up is with Fujii Masao. BTW, my replication patch is on git repository: git://git.postgresql.org/git/users/fujii/postgres.git branch: replication Thanks, I started to look at this again now. The consensus seems to be to keep the current architecture where walreceiver is a child of postmaster. I found the global LogstreamResult variable very confusing. It meant different things in different processes. So I replaced it with static globals in walsender.c and walreceiver.c, and renamed the fields to match the purpose better. I removed some variables from shared memory that are not necessary, at least not before we have synchronous mode: Walsender only needs to publish how far it has sent, and walreceiver only needs to tell startup process how far it has fsync'd. I changed walreceiver so that it only lets the startup process to apply WAL that it has fsync'd to disk, per recent discussion on hackers. Maybe we want to support more esoteric modes in the future, but that's the least surprising and most useful one. Plus some other minor simplifications. My changes are in my git repo at git://git.postgresql.org/git/users/heikki/postgres.git, branch replication. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question about ECPGset_noind_null() and ECPGis_noind_null()
Tom Lane írta: Andrew Dunstan and...@dunslane.net writes: Boszormenyi Zoltan wrote: Is it *really* a bug? I recalled a comment from my C teacher in '92 or '93 about this exact issue, that the prefix/postfix increment/decrement operators are executed in the statement in an implementation-defined order, Not if they come after a short-circuit operator such as - after all, that's what short-circuit evaluation implies. If the left hand operand of is false the right hand should not be evaluated at all. Yes. is a sequence point and the compiler is not allowed to move side-effects across a sequence point. What your C teacher was warning you against was things like a[i] = i++; '=' is not a sequence point so it's undefined which array index will be stored into. regards, tom lane Thanks to both of you, this was really informative. Actually my C teacher didn't mention such optimization barriers. It seems I need to look up the raw C language specs... Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Union test case broken in make check?
Hi, Is it just me or the union test case fails in CVS head? manu -- Emmanuel Cecchet Aster Data Web: http://www.asterdata.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
g...@turnstep.com (Greg Sabino Mullane) writes: BTW, did we discuss the issue of 2PC transactions versus notify? The current behavior of 2PC with notify is pretty cheesy and will become more so if we make this change --- you aren't really guaranteed that the notify will happen, even though the prepared transaction did commit. I think it might be better to disallow NOTIFY inside a prepared xact. That's a tough one. On the one hand, simply stating that NOTIFY and 2PC don't play together in the docs would be a straightforward solution (and not a bad one, as 2PC is already rare and delicate and should not be used lightly). But what I really don't like the is the idea of a notify that *may* work or may not - so let's keep it boolean: it either works 100% of the time with 2PC, or doesn't at all. Should we throw a warning or error if a client attempts to combine the two? +1 from me... It should either work, or not work, as opposed to something nondeterministic. While it's certainly a nice thing for features to be orthogonal, and for interactions to just work, I can see making a good case for NOTIFY and 2PC not playing together. -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxfinances.info/info/slony.html Why isn't phonetic spelled the way it sounds? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Very bad FTS performance with the Polish config
Tom Lane wrote: *SNIP* So about 55% of the time is going into affix pattern matching. I wonder whether that couldn't be made faster. A lot of the cycles are spent on coping with variable-length characters --- perhaps the ispell code should convert to wchar representation before doing this? Thanks a lot for looking into this. I hope this will lead to some improvements one day. Unfortunately my C skills were pretty basic years ago and I haven't used the language since, so I can't be of much help.. cheers, Wojciech Knapik -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Union test case broken in make check?
Emmanuel Cecchet wrote: Hi, Is it just me or the union test case fails in CVS head? The buildfarm is pretty much all green: http://www.pgbuildfarm.org/cgi-bin/show_status.pl So it looks like it's you :-) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/Python array support
On fre, 2009-11-13 at 18:46 +0300, Teodor Sigaev wrote:
CREATE OR REPLACE FUNCTION incr(stuff int[]) RETURNS int[] AS $$
for x in stuff:
yield x+1
$$
LANGUAGE 'plpythonu';
# select incr(ARRAY[1,2,3]);
ERROR: invalid memory alloc request size 18446744073709551608
CONTEXT: while creating return value
PL/Python function incr
Fixed with additional error check and regression test. (The problem
could be more simply demonstrated by returning any non-sequence from the
function.) Thanks for catching it.
diff --git a/src/pl/plpython/expected/plpython_types.out b/src/pl/plpython/expected/plpython_types.out
index 2dd498c..cbc93a2 100644
--- a/src/pl/plpython/expected/plpython_types.out
+++ b/src/pl/plpython/expected/plpython_types.out
@@ -477,3 +477,106 @@ CONTEXT: PL/Python function test_type_conversion_bytea10
ERROR: value for domain bytea10 violates check constraint bytea10_check
CONTEXT: while creating return value
PL/Python function test_type_conversion_bytea10
+--
+-- Arrays
+--
+CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$
+plpy.info(x, type(x))
+return x
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_int4(ARRAY[0, 100]);
+INFO: ([0, 100], type 'list')
+CONTEXT: PL/Python function test_type_conversion_array_int4
+ test_type_conversion_array_int4
+-
+ {0,100}
+(1 row)
+
+SELECT * FROM test_type_conversion_array_int4(ARRAY[0,-100,55]);
+INFO: ([0, -100, 55], type 'list')
+CONTEXT: PL/Python function test_type_conversion_array_int4
+ test_type_conversion_array_int4
+-
+ {0,-100,55}
+(1 row)
+
+SELECT * FROM test_type_conversion_array_int4(ARRAY[NULL,1]);
+INFO: ([None, 1], type 'list')
+CONTEXT: PL/Python function test_type_conversion_array_int4
+ test_type_conversion_array_int4
+-
+ {NULL,1}
+(1 row)
+
+SELECT * FROM test_type_conversion_array_int4(ARRAY[]::integer[]);
+INFO: ([], type 'list')
+CONTEXT: PL/Python function test_type_conversion_array_int4
+ test_type_conversion_array_int4
+-
+ {}
+(1 row)
+
+SELECT * FROM test_type_conversion_array_int4(NULL);
+INFO: (None, type 'NoneType')
+CONTEXT: PL/Python function test_type_conversion_array_int4
+ test_type_conversion_array_int4
+-
+
+(1 row)
+
+SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
+ERROR: cannot convert multidimensional array to Python list
+DETAIL: PL/Python only supports one-dimensional arrays.
+CONTEXT: PL/Python function test_type_conversion_array_int4
+CREATE FUNCTION test_type_conversion_array_bytea(x bytea[]) RETURNS bytea[] AS $$
+plpy.info(x, type(x))
+return x
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_bytea(ARRAY[E'\\xdeadbeef'::bytea, NULL]);
+INFO: (['\xde\xad\xbe\xef', None], type 'list')
+CONTEXT: PL/Python function test_type_conversion_array_bytea
+ test_type_conversion_array_bytea
+--
+ {\\xdeadbeef,NULL}
+(1 row)
+
+CREATE FUNCTION test_type_conversion_array_mixed1() RETURNS text[] AS $$
+return [123, 'abc']
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_mixed1();
+ test_type_conversion_array_mixed1
+---
+ {123,abc}
+(1 row)
+
+CREATE FUNCTION test_type_conversion_array_mixed2() RETURNS int[] AS $$
+return [123, 'abc']
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_mixed2();
+ERROR: invalid input syntax for integer: abc
+CONTEXT: while creating return value
+PL/Python function test_type_conversion_array_mixed2
+CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$
+return [None]
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_record();
+ERROR: PL/Python functions cannot return type type_record[]
+DETAIL: PL/Python does not support conversion to arrays of row types.
+CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$
+return 'abc'
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_string();
+ test_type_conversion_array_string
+---
+ {a,b,c}
+(1 row)
+
+CREATE FUNCTION test_type_conversion_array_tuple() RETURNS text[] AS $$
+return ('abc', 'def')
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_tuple();
+ test_type_conversion_array_tuple
+--
+ {abc,def}
+(1 row)
+
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 6fd4aca..6a2a12f 100644
--- a/src/pl/plpython/plpython.c
+++ b/src/pl/plpython/plpython.c
@@ -89,6 +89,9 @@ typedef struct PLyDatumToOb
Oid typoid; /* The OID of the type */
Oid typioparam;
bool typbyval;
+ int16 typlen;
+ char typalign;
+ struct PLyDatumToOb *elm;
} PLyDatumToOb;
typedef struct PLyTupleToOb
@@ -120,6 +123,9 @@ typedef struct PLyObToDatum
Oid typoid; /* The OID of the type */
Oid
Re: [HACKERS] Listen / Notify - what to do when the queue is full
On Thu, Nov 19, 2009 at 6:55 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Hmm, ignoring 2PC for a moment, I think the patch suffers from a little race condition: Session 1: BEGIN; Session 1: INSERT INTO foo ..; Session 1: NOTIFY 'foo'; Session 1: COMMIT -- commit begins Session 1: [commit processing runs AtCommit_NotifyBeforeCommit()] Session 2 must not read uncommited notifications selectively Session 2: LISTEN 'foo'; Session 2: SELECT * FROM foo; Session 1: [AtCommit_NotifyAfterCommit() signals listening backends] Session 2: [waits for notifications] Because session 2 began listening after session 1 had already sent its notifications, it missed them. I think you are right. However note that session 1 does not actively send notifications to anybody, it just puts them into the queue. It's every backend's own job to process the queue and see which messages are interesting and which are not. The example you brought up fails if Session 2 disregards the notifications based on the current set of channels that it is listening to at this point. If I understand you correctly what you are suggesting is to not read uncommitted notifications from the queue in a reading backend or read all notifications (regardless of which channel it has been sent to), such that the backend can apply the check (Am i listening on this channel?) later on. I think we could fix that by arranging things so that a backend refrains from advancing its own 'pos' beyond the first notification it has written itself, until commit is completely finished. In the end this is similar to the idea to not read uncommitted notifications which was what I did at the beginning. However then you run into a full queue a lot faster. Imagine a queue length of 1000 with 3 transactions writing 400 notifications each... All three might fail if they run in parallel, even though space would be sufficient for at least two of them, and if they are executed in a sequence, all of them could deliver their notifications. Given your example, what I am proposing now is to stop reading from the queue once we see a not-yet-committed notification but once the queue is full, read the uncommitted notifications, effectively copying them over into the backend's own memory... Once the transaction commits and sends a signal, we can process, send and discard the previously copied notifications. In the above example, at some point one, two or all three backends would see that the queue is full and everybody would read the uncommitted notifications of the other one, copy them into the own memory and space will be freed in the queue. That will handle 2PC as well. We can send the notifications in prepare-phase, and any LISTEN that starts after the prepare-phase will see the notifications because they're still in the queue. There is no risk of running out of disk space in COMMIT PREPARED, because the notifications have already been written to disk. However, the notification queue can't be truncated until the prepared transaction finishes; does anyone think that's a show-stopper? Note that we don't preserve notifications when the database restarts. But 2PC can cope with restarts. How would that fit together? Also I am not sure how you are going to deliver notifications that happen between the PREPARE TRANSACTION and the COMMIT PREPARED (because you have only one queue pointer which you are not going to advance...) ? Joachim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to change a pg_restore message
On tor, 2009-11-19 at 16:47 +0100, Guillaume Lelarge wrote: pg_restore --help gives this message for the --no-tablespaces parameter: --no-tablespaces do not dump tablespace assignments The message should say restore and not dump. You'll find a patch attached that fixes this issue. Fixed in 8.4 and 8.5. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to change a pg_restore message
Le jeudi 19 novembre 2009 à 23:05:16, Peter Eisentraut a écrit : On tor, 2009-11-19 at 16:47 +0100, Guillaume Lelarge wrote: pg_restore --help gives this message for the --no-tablespaces parameter: --no-tablespaces do not dump tablespace assignments The message should say restore and not dump. You'll find a patch attached that fixes this issue. Fixed in 8.4 and 8.5. Thanks Peter. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timezones (in 8.5?)
Kevin == Kevin Grittner kevin.gritt...@wicourts.gov writes: For example, if you have a type T which incorporates a timestamp and a timezone, what semantics does the T = T operator have? What semantics apply if the definitions of timezones change? Kevin I'd rather sort that out once and implement the desired Kevin semantics in the operators for a new type than to count on Kevin application programmers doing it consistently each time. Kevin Wouldn't you? No, because the desired semantics are not the same for everyone, so even if you take just the two examples I gave above, you're already into combinatorial explosion with four different types needed. By keeping it as a composite value, you allow the app to define the semantics it needs. What if you're storing times of events at specific places; in that case you want to associate the timezone with the _place_ not the event (so that if the timezone rules change, moving the place from one timezone to another, you only have to change the place, not all the events that refer to it). Kevin I'm not sure I quite followed you there, but Hernán's example Kevin specifically called for storing 'Chile/Santiago', not a UTC Kevin offset or something as easily changed as the 'CLT' or 'CLST' Kevin time zone designations -- so it is tied to a place rather more Kevin closely than anything else. But those place definitions do occasionally change. For example, some US states can change timezone at county level; suppose a state that was previously all one timezone decides to change timezone or DST observance for all except a few counties that remain on the previous setting. So places within those counties will have to change timezone name from America/Somestate to America/Somestate/Oddcounty while places in the rest of the state stay with America/Somestate. The fact that geographic names are used for timezones doesn't mean that the timezone name applicable to a given place doesn't change; timezones in the database can split when rule changes happen that don't affect the full extent of the previous zone; this leads to two or more zones which have identical definitions up to some date, and different definitions after it. (Zones can only split, they can't merge, due to the necessity of keeping historical changes.) Kevin I think that was part of his point -- that for civil time you Kevin care about what the clock on a typical business's wall at that Kevin place will read on that date, regardless of what changes might Kevin happen in time zone definitions. Right, but if timezone _boundaries_ change, this can't happen without some manual corrections. (If the timezone _rules_ change without changing the boundaries, then just updating the tzdata is enough if you designed the db correctly.) -- Andrew. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Syntax for partitioning
On ons, 2009-11-18 at 13:52 +0900, Itagaki Takahiro wrote: partinfo = (PartitionInfo *) malloc(ntups * sizeof(PartitionInfo)); Oops, it should be palloc. Thanks. A very low-level comment: 1) Please stop casting the results of palloc and malloc. We are not writing C++ here. 2) I would prefer that you apply sizeof on the variable, not on the type. That way, the expression is independent of any type changes of the variable, and can be reviewed without having to scroll around for the variable definition. So how about, partinfo = palloc(ntups * sizeof(*partinfo)); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Union test case broken in make check?
Andrew Dunstan and...@dunslane.net writes: Emmanuel Cecchet wrote: Is it just me or the union test case fails in CVS head? The buildfarm is pretty much all green: http://www.pgbuildfarm.org/cgi-bin/show_status.pl So it looks like it's you :-) When in doubt, try make distclean and a full rebuild before assuming you've got a problem worth tracking down ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plperl and inline functions -- first draft
On Wed, Nov 18, 2009 at 12:38:00PM +0200, Alexey Klyukin wrote: Yes, current_call_data can't be allocate in the SPI memory context, since it's used to extract the result after SPI_finish is called, although it doesn't lead to problems here since no result is returned. Anyway, I'd move SPI_connect after the current_call_data initialization. I also noticed that no error context is set in the inline handler, not sure whether it really useful except for the sake of consistency, but in case it is - here is the patch: Makes sense on both counts. Thanks for the help. How does the attached look? -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index 49631f2..ebcb608 100644 *** a/doc/src/sgml/plperl.sgml --- b/doc/src/sgml/plperl.sgml *** CREATE FUNCTION replaceablefuncname/r *** 59,69 # PL/Perl function body $$ LANGUAGE plperl; /programlisting The body of the function is ordinary Perl code. In fact, the PL/Perl !glue code wraps it inside a Perl subroutine. A PL/Perl function must !always return a scalar value. You can return more complex structures !(arrays, records, and sets) by returning a reference, as discussed below. !Never return a list. /para note --- 59,81 # PL/Perl function body $$ LANGUAGE plperl; /programlisting + +PL/Perl also supports anonymous code blocks called with the +xref linkend=sql-do endterm=sql-do-title +statement: + + programlisting + DO $$ + # PL/Perl function body + $$ LANGUAGE plperl; + /programlisting + The body of the function is ordinary Perl code. In fact, the PL/Perl !glue code wraps it inside a Perl subroutine. Anonymous code blocks cannot !return a value; PL/Perl functions created with CREATE FUNCTION must always !return a scalar value. You can return more complex structures (arrays, !records, and sets) by returning a reference, as discussed below. Never !return a list. /para note diff --git a/src/include/catalog/pg_pltemplate.h b/src/include/catalog/pg_pltemplate.h index 5ef97df..8cdedb4 100644 *** a/src/include/catalog/pg_pltemplate.h --- b/src/include/catalog/pg_pltemplate.h *** typedef FormData_pg_pltemplate *Form_pg_ *** 70,77 DATA(insert ( plpgsql t t plpgsql_call_handler plpgsql_inline_handler plpgsql_validator $libdir/plpgsql _null_ )); DATA(insert ( pltcl t t pltcl_call_handler _null_ _null_ $libdir/pltcl _null_ )); DATA(insert ( pltclu f f pltclu_call_handler _null_ _null_ $libdir/pltcl _null_ )); ! DATA(insert ( plperl t t plperl_call_handler _null_ plperl_validator $libdir/plperl _null_ )); ! DATA(insert ( plperlu f f plperl_call_handler _null_ plperl_validator $libdir/plperl _null_ )); DATA(insert ( plpythonu f f plpython_call_handler _null_ _null_ $libdir/plpython _null_ )); #endif /* PG_PLTEMPLATE_H */ --- 70,77 DATA(insert ( plpgsql t t plpgsql_call_handler plpgsql_inline_handler plpgsql_validator $libdir/plpgsql _null_ )); DATA(insert ( pltcl t t pltcl_call_handler _null_ _null_ $libdir/pltcl _null_ )); DATA(insert ( pltclu f f pltclu_call_handler _null_ _null_ $libdir/pltcl _null_ )); ! DATA(insert ( plperl t t plperl_call_handler plperl_inline_handler plperl_validator $libdir/plperl _null_ )); ! DATA(insert ( plperlu f f plperl_call_handler plperl_inline_handler plperl_validator $libdir/plperl _null_ )); DATA(insert ( plpythonu f f plpython_call_handler _null_ _null_ $libdir/plpython _null_ )); #endif /* PG_PLTEMPLATE_H */ diff --git a/src/pl/plperl/GNUmakefile b/src/pl/plperl/GNUmakefile index a3c3495..2c32850 100644 *** a/src/pl/plperl/GNUmakefile --- b/src/pl/plperl/GNUmakefile *** OBJS = plperl.o spi_internal.o SPI.o *** 38,45 SHLIB_LINK = $(perl_embed_ldflags) ! REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-language=plperl ! REGRESS = plperl plperl_trigger plperl_shared plperl_elog # where to find psql for running the tests PSQLDIR = $(bindir) --- 38,45 SHLIB_LINK = $(perl_embed_ldflags) ! REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-language=plperl --load-language=plperlu ! REGRESS = plperl plperl_trigger plperl_shared plperl_elog plperl_do # where to find psql for running the tests PSQLDIR = $(bindir) diff --git a/src/pl/plperl/expected/plperl_do.out b/src/pl/plperl/expected/plperl_do.out index ...86337f3 . *** a/src/pl/plperl/expected/plperl_do.out --- b/src/pl/plperl/expected/plperl_do.out *** *** 0 --- 1,9 + DO $$ + $a = 'This is a test'; + elog(NOTICE, $a); + $$ LANGUAGE plperl; + NOTICE: This is a test + CONTEXT: PL/Perl anonymous code block + DO $$ use Config; $$ LANGUAGE plperl; + ERROR: 'require' trapped by operation mask at line 1. + CONTEXT: PL/Perl anonymous code block diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c index 4ed4f59..88b73f3 100644 ***
[HACKERS] plruby code and postgres ?
Does anyone have a link for pl/ruby? I found a link under the postgres documentation and found a web site from there talking about the code. However when I clicked on the link to download it I noticed ftp wouldn't respond on their site. Thanks! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Union test case broken in make check?
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Emmanuel Cecchet wrote: Is it just me or the union test case fails in CVS head? The buildfarm is pretty much all green: http://www.pgbuildfarm.org/cgi-bin/show_status.pl So it looks like it's you :-) When in doubt, try make distclean and a full rebuild before assuming you've got a problem worth tracking down ... Well, I did: 1. make distclean 2. configure with CFLAGS=-O0 --enable-cassert --enable-debug --without-perl --without-python --without-tcl --without-openssl 3. make (everything normal) 4. make check And it still fails for me. I am attaching my regression.diffs if someone thinks it is worth tracking down ... Emmanuel *** /home/manu/workspace/PG-HEAD/src/test/regress/expected/union.out 2009-02-09 16:18:28.0 -0500 --- /home/manu/workspace/PG-HEAD/src/test/regress/results/union.out 2009-11-19 19:37:32.0 -0500 *** *** 198,208 WHERE f1 BETWEEN 0 AND 100; five --- --1004.3 - -34.84 - -1.2345678901234e-200 0 123456 (5 rows) SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL --- 198,208 WHERE f1 BETWEEN 0 AND 100; five --- 0 123456 + -34.84 + -1.2345678901234e-200 +-1004.3 (5 rows) SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL *** *** 263,278 SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl; q2 -- - 4567890123456789 123 (2 rows) SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl; q2 -- 4567890123456789 4567890123456789 - 123 (3 rows) SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; --- 263,278 SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl; q2 -- 123 + 4567890123456789 (2 rows) SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl; q2 -- + 123 4567890123456789 4567890123456789 (3 rows) SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; *** *** 305,320 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl; q1 -- - 4567890123456789 123 (2 rows) SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl; q1 -- 4567890123456789 4567890123456789 - 123 (3 rows) -- --- 305,320 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl; q1 -- 123 + 4567890123456789 (2 rows) SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl; q1 -- + 123 4567890123456789 4567890123456789 (3 rows) -- *** *** 341,348 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl; q1 --- - 4567890123456789 123 456 4567890123456789 123 --- 341,348 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl; q1 --- 123 + 4567890123456789 456 4567890123456789 123 *** *** 353,367 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))); q1 -- - 4567890123456789 123 (2 rows) (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl; q1 --- - 4567890123456789 123 456 4567890123456789 123 --- 353,367 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))); q1 -- 123 + 4567890123456789 (2 rows) (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl; q1 --- 123 + 4567890123456789 456 4567890123456789 123 *** *** 416,423 SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))); q1 -- - 4567890123456789 123 (2 rows) -- --- 416,423 SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)));
Re: [HACKERS] [COMMITTERS] pgsql: /home/peter/commit-msg
2009/11/16 Peter Eisentraut pete...@gmx.net: On mån, 2009-11-16 at 10:05 -0500, Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Magnus Hagander wrote: On Mon, Nov 16, 2009 at 08:29, David Fetter da...@fetter.org wrote: On Mon, Nov 16, 2009 at 06:56:54AM +0200, Peter Eisentraut wrote: Yeah, sorry guys. I fixed the CVS log message now. So it's not only not strange, I'm very happy it didn't pull those changes and broke my repository :-) Yeah, I'm glad it didn't do anything funny with the mirror. I think we should have a policy of NO manual changes to the CVS repository files. At least not without careful discussion beforehand. I used cvs admin. I've cleaned up the git repo, and re-enabled the mirror script. From what I can tell it works fine. In theory you will need to use force mode if you pulled the broken commit that was removed (the one with the wrong message), but it seems this is not necessarily required. As for the future, please avoid doing any cvs admin activity if possible, and if it's done let's specifically coordinate with the git mirror script, to make sure things work smoothly. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] enable-thread-safety defaults?
Is there any actual reason why we are building without thread safety by default on most platforms? Seems I get asked that every time somebody forgets to add a --enable-thread-safety. Wouldn't it be more logical to have that be the default, and provide --disable-thread-safety if there are platforms that still don't support it? AFAIK pretty much all binary packages will do it by default, but it's easy to forget when building from source -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] DEFAULT of domain ignored in plpgsql (8.4.1)
Hi It seems that pl/pgsql ignores the DEFAULT value of domains for local variables. With the following definitions in place create domain myint as int default 0; create or replace function myint() returns myint as $body$ declare v_result myint; begin return v_result; end; $body$ language plpgsql immutable; issuing select myint(); returns NULL, not 0 on postgres 8.4.1 If the line v_result myint; is changes to v_result myint default 0; than 0 is returned as expected. I've tried to create a patch, but didn't see how I'd convert the result from get_typedefault() (A Node*, presumeably the parsetree corresponding to the default expression?) into a plan that I could store in a PLpgSQL_expr. I guess I'd need something like SPI_prepare_plan that takes a parse tree instead of a query string. Or am I on a completely wrong track there? While trying to cook up a patch I've also stumbled over what I perceive as a bug relating to DOMAINS and column DEFAULTs. I'll write that up in a second E-Mail to avoid confusion. best regards, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] Summary and Plan for Hot Standby
On 11/15/09 11:07 PM, Heikki Linnakangas wrote: - When replaying b-tree deletions, we currently wait out/cancel all running (read-only) transactions. We take the ultra-conservative stance because we don't know how recent the tuples being deleted are. If we could store a better estimate for latestRemovedXid in the WAL record, we could make that less conservative. Simon was explaining this issue here at JPUGCon; now that I understand it, this specific issue seems like the worst usability issue in HS now. Bad enough to kill its usefulness for users, or even our ability to get useful testing data; in an OLTP production database with several hundred inserts per second it would result in pretty much never being able to get any query which takes longer than a few seconds to complete on the slave. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Fri, 2009-11-20 at 11:14 +0900, Josh Berkus wrote: On 11/15/09 11:07 PM, Heikki Linnakangas wrote: - When replaying b-tree deletions, we currently wait out/cancel all running (read-only) transactions. We take the ultra-conservative stance because we don't know how recent the tuples being deleted are. If we could store a better estimate for latestRemovedXid in the WAL record, we could make that less conservative. Simon was explaining this issue here at JPUGCon; now that I understand it, this specific issue seems like the worst usability issue in HS now. Bad enough to kill its usefulness for users, or even our ability to get useful testing data; in an OLTP production database with several hundred inserts per second it would result in pretty much never being able to get any query which takes longer than a few seconds to complete on the slave. I am pretty sure that OmniTI, PgExperts, EDB and CMD all have customers that are doing more than that... This sounds pretty significant. Joshua D. Drake --Josh Berkus -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] column DEFAULTs and prepared statements
Hi While trying to come up with a patch to handle domain DEFAULTs in plpgsql I've stumbled across the following behavior regarding domain DEFAULTs and prepared statements. session 1: create domain myint as int default 0 ; session 1: create table mytable (i myint) ; session 2: prepare ins as insert into mytable (i) values (default); session 2: execute ins; session 1: alter domain myint set default 1; session 2: execute ins; select * from mytable returns: i --- 0 0 while I'd have expected: i --- 0 1 After doing the same without using a domain session 1: create table mytable (i myint default 0) ; session 2: prepare ins as insert into mytable (i) values (default); session 2: execute ins; session 1: alter table mytable alter column i default 1; session 2: execute ins; select * from mytable returns: i --- 0 1 As far as I understand the code this happens because the dependency on the domain (for the default value) is not recorded in the plan cache entry. This would imply that the same error also occurs if the INSERT happens from a pl/pgsql function instead of a manually prepared statement, but I haven't tested that. If someone gives me a general idea where to start, I could try to come up with a patch best regards, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] Union test case broken in make check?
Without an order by, the order is not defined. The answers are the same but the test gives a false failure because of the lack of ordering. Regards, Ken On Thu, Nov 19, 2009 at 07:54:30PM -0500, Emmanuel Cecchet wrote: Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Emmanuel Cecchet wrote: Is it just me or the union test case fails in CVS head? The buildfarm is pretty much all green: http://www.pgbuildfarm.org/cgi-bin/show_status.pl So it looks like it's you :-) When in doubt, try make distclean and a full rebuild before assuming you've got a problem worth tracking down ... Well, I did: 1. make distclean 2. configure with CFLAGS=-O0 --enable-cassert --enable-debug --without-perl --without-python --without-tcl --without-openssl 3. make (everything normal) 4. make check And it still fails for me. I am attaching my regression.diffs if someone thinks it is worth tracking down ... Emmanuel *** /home/manu/workspace/PG-HEAD/src/test/regress/expected/union.out 2009-02-09 16:18:28.0 -0500 --- /home/manu/workspace/PG-HEAD/src/test/regress/results/union.out 2009-11-19 19:37:32.0 -0500 *** *** 198,208 WHERE f1 BETWEEN 0 AND 100; five --- --1004.3 - -34.84 - -1.2345678901234e-200 0 123456 (5 rows) SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL --- 198,208 WHERE f1 BETWEEN 0 AND 100; five --- 0 123456 + -34.84 + -1.2345678901234e-200 +-1004.3 (5 rows) SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL *** *** 263,278 SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl; q2 -- - 4567890123456789 123 (2 rows) SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl; q2 -- 4567890123456789 4567890123456789 - 123 (3 rows) SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; --- 263,278 SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl; q2 -- 123 + 4567890123456789 (2 rows) SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl; q2 -- + 123 4567890123456789 4567890123456789 (3 rows) SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; *** *** 305,320 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl; q1 -- - 4567890123456789 123 (2 rows) SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl; q1 -- 4567890123456789 4567890123456789 - 123 (3 rows) -- --- 305,320 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl; q1 -- 123 + 4567890123456789 (2 rows) SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl; q1 -- + 123 4567890123456789 4567890123456789 (3 rows) -- *** *** 341,348 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl; q1 --- - 4567890123456789 123 456 4567890123456789 123 --- 341,348 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl; q1 --- 123 + 4567890123456789 456 4567890123456789 123 *** *** 353,367 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))); q1 -- - 4567890123456789 123 (2 rows) (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl; q1 --- - 4567890123456789 123 456 4567890123456789 123 --- 353,367 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))); q1 -- 123 + 4567890123456789 (2 rows) (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl; q1 --- 123 + 4567890123456789
Re: [HACKERS] Union test case broken in make check?
Then I guess that we need to fix the test. Emmanuel Kenneth Marshall wrote: Without an order by, the order is not defined. The answers are the same but the test gives a false failure because of the lack of ordering. Regards, Ken On Thu, Nov 19, 2009 at 07:54:30PM -0500, Emmanuel Cecchet wrote: Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Emmanuel Cecchet wrote: Is it just me or the union test case fails in CVS head? The buildfarm is pretty much all green: http://www.pgbuildfarm.org/cgi-bin/show_status.pl So it looks like it's you :-) When in doubt, try make distclean and a full rebuild before assuming you've got a problem worth tracking down ... Well, I did: 1. make distclean 2. configure with CFLAGS=-O0 --enable-cassert --enable-debug --without-perl --without-python --without-tcl --without-openssl 3. make (everything normal) 4. make check And it still fails for me. I am attaching my regression.diffs if someone thinks it is worth tracking down ... Emmanuel *** /home/manu/workspace/PG-HEAD/src/test/regress/expected/union.out 2009-02-09 16:18:28.0 -0500 --- /home/manu/workspace/PG-HEAD/src/test/regress/results/union.out 2009-11-19 19:37:32.0 -0500 *** *** 198,208 WHERE f1 BETWEEN 0 AND 100; five --- --1004.3 - -34.84 - -1.2345678901234e-200 0 123456 (5 rows) SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL --- 198,208 WHERE f1 BETWEEN 0 AND 100; five --- 0 123456 + -34.84 + -1.2345678901234e-200 +-1004.3 (5 rows) SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL *** *** 263,278 SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl; q2 -- - 4567890123456789 123 (2 rows) SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl; q2 -- 4567890123456789 4567890123456789 - 123 (3 rows) SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; --- 263,278 SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl; q2 -- 123 + 4567890123456789 (2 rows) SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl; q2 -- + 123 4567890123456789 4567890123456789 (3 rows) SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; *** *** 305,320 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl; q1 -- - 4567890123456789 123 (2 rows) SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl; q1 -- 4567890123456789 4567890123456789 - 123 (3 rows) -- --- 305,320 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl; q1 -- 123 + 4567890123456789 (2 rows) SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl; q1 -- + 123 4567890123456789 4567890123456789 (3 rows) -- *** *** 341,348 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl; q1 --- - 4567890123456789 123 456 4567890123456789 123 --- 341,348 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl; q1 --- 123 + 4567890123456789 456 4567890123456789 123 *** *** 353,367 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))); q1 -- - 4567890123456789 123 (2 rows) (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl; q1 --- - 4567890123456789 123 456 4567890123456789 123 --- 353,367 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))); q1 -- 123 + 4567890123456789 (2 rows) (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl; q1 --- 123 + 4567890123456789
Re: [HACKERS] Summary and Plan for Hot Standby
Joshua D. Drake wrote: On Fri, 2009-11-20 at 11:14 +0900, Josh Berkus wrote: On 11/15/09 11:07 PM, Heikki Linnakangas wrote: - When replaying b-tree deletions, we currently wait out/cancel all running (read-only) transactions. We take the ultra-conservative stance because we don't know how recent the tuples being deleted are. If we could store a better estimate for latestRemovedXid in the WAL record, we could make that less conservative. Simon was explaining this issue here at JPUGCon; now that I understand it, this specific issue seems like the worst usability issue in HS now. Bad enough to kill its usefulness for users, or even our ability to get useful testing data; in an OLTP production database with several hundred inserts per second it would result in pretty much never being able to get any query which takes longer than a few seconds to complete on the slave. I am pretty sure that OmniTI, PgExperts, EDB and CMD all have customers that are doing more than that... This sounds pretty significant. Right. The major use I was hoping for from HS was exactly to be able to run long-running queries. In once case I am thinking of we have moved the business intelligence uses off the OLTP server onto a londiste replica, and I was really wanting to move that to a Hot Standby server. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Wed, Nov 18, 2009 at 9:00 AM, Jeff Davis pg...@j-davis.com wrote: I'm in Tokyo right now, so please excuse my abbreviated reply. On Tue, 2009-11-17 at 23:13 -0500, Robert Haas wrote: Forgive me if this is discussed before, but why does this store the strategy numbers of the relevant operators instead of the operators themselves? At constraint definition time, I need to make sure that the strategy numbers can be identified anyway, so it wouldn't save any work in ATAddOperatorExclusionConstraint. At the time it seemed slightly more direct to use strategy numbers in index_check_constraint, but it's probably about the same. It sets off a red flag for me any time I see code that asks for A from the user and then actually stores B under the hood, for fear that the relationship that A and B might change. However... It seems like this could lead to surprising behavior if the user modifies the definition of the operator class. Right now, operator classes can't be modified in any meaningful way. Am I missing something? ...poking at it, I have to agree that at least as things stand right now, I can't find a way to break it. Not sure if it's future-proof. I'm wondering if we can't use the existing BuildIndexValueDescription() rather than the new function tuple_as_string(). I realize there are two tuples, but maybe it makes sense to just call it twice? Are you suggesting I change the error output, or reorganize the code to try to reuse BuildIndexValueDescription, or both? I was thinking maybe you call BuildIndexValueDescription twice and make the error message say something like output of first call conflicts with output of second call. One other thing I noticed tonight while poking at this. If I install contrib/citext, I can do this: create table test (a citext, exclude using hash (a with =)); But if I install contrib/intarray, I can't do this: create table test (a int4[], exclude using gist (a with =)); ERROR: operator does not exist: integer[] = integer[] Not sure if I'm doing something wrong, or if this is a limitation of the design, or if it's a bug, but it seems strange. I'm guessing it's because intarray uses the anyarray operator rather than a dedicated operator for int[], but it seems like that ought to work. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Wed, Nov 18, 2009 at 9:21 AM, Josh Berkus j...@agliodbs.com wrote: All, FWIW, I'm doing a redesign of a client's production web application right now. I was able, by combining OEC and the Period type from pgfoundry, to make a set of constraints for declaratively asserting in a sports database: That the same player couldn't belong to two different teams at the same time; That the same player couldn't belong to the same team in two different positions with overlapping time periods. This worked as spec'd, and would be extremely useful for this real-world app if it was ready to use in production now. However, I do have an issue with the SQLSTATE returned from the OEC violation. Currently it returns constraint violation, which, from the perspective of an application developer, is not useful. OECs are, in application terms, materially identical to UNIQUE constraints and serve the same purpose. As such, I'd far rather see OECs return unique key violation instead, as any existing application error-trapping code would handle the violation more intelligently if it did. I guess I'm going to have to vote -1 on this proposal. I code see inventing a pgsql-specific SQLSTATE value for exclusion constraints, since they will be a pgsql-specific extension, but reusing the unique key violation value seems misleading. I admit it may help in a limited number of cases, but IMHO it's not worth the confusion. That's just my $0.02, though. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] AFTER triggers RETURN
On 11/5/2009 8:10 PM, Robert Haas wrote: On Thu, Nov 5, 2009 at 4:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: Robert Haas wrote: Since the return value is ignored anyway, why do we have to complain if it's left out altogether? Granted, it's easy to work around, but still. Isn't is a requirement of plpgsql that you not fall off the end of a function unless it is declared to return void? The function doesn't know if it will be called before or after. Yeah, it couldn't be done as a compile-time check. You could probably make it work if you converted the error to a run-time test. Not sure if that's really an improvement though. Well, as it is, you don't get an error when you define the function, only when you do something that causes it to be invoked. An error when you define the function would probably be a small improvement, because at least it would be obvious that you'd broke something (and the transaction that tried to break it would roll back). No error at all seems better still. Perhaps in an ideal world before and after trigger functions would have different signatures - like the before trigger should perhaps take two rows as arguments and return a row, and the after trigger should take two rows as arguments and return void. The idea of overloading the function's return type to provide it with special, magical input parameters is pretty funky and means that you can't invoke that function in any context other than as a trigger, which would occasionally be useful. I think we're stuck with it at this point, but maybe it's possible to at least relax the requirement to explicitly return a useless result. One could of course have triggers return NEW by default for invocations on INSERT or UPDATE, and OLD on DELETE. That would make the default behavior of BEFORE triggers to let the original operation through and fix the annoyance. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why do OLD and NEW have special internal names?
On 11/5/2009 4:33 PM, Tom Lane wrote: So I was testing the next step of plpgsql modification, namely actually letting the parser hooks do something, and it promptly blew up in trigger functions, like so: + ERROR: OLD used in query that is not in a rule + LINE 1: SELECT OLD + ^ + QUERY: SELECT OLD + CONTEXT: SQL statement in PL/PgSQL function trigger_data near line 35 The reason is that because plpgsql is no longer translating references to its names into Params before letting the core parser see them, the kluge in gram.y that changes OLD to *OLD* and NEW to *NEW* kicks in, or actually decides to throw an error instead of kicking in. I am wondering what is the point at all of having that kluge. It certainly doesn't manage to make OLD/NEW not act like reserved words, in fact rather more the opposite, as shown here. If we just made those names be ordinary table alias names in rule queries, wouldn't things work as well or better? Sorry, I don't recall what the exact point back then, when plpgsql was created for 6.WHAT_VERSION, really was. But this brings up another point about the recent discussion of what RENAME is good for. Removing RENAME may conflict with using OLD/NEW in UPDATE ... RETURNING. No? Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
RObert, I guess I'm going to have to vote -1 on this proposal. I code see inventing a pgsql-specific SQLSTATE value for exclusion constraints, since they will be a pgsql-specific extension, but reusing the unique key violation value seems misleading. I admit it may help in a limited number of cases, but IMHO it's not worth the confusion. I'd rather have a new one than just using contstraint violation which is terribly non-specific, and generally makes the application developer think that a value is too large. --Josh BErkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Syntax for partitioning
Hi, partinfo = (PartitionInfo *) malloc(ntups * sizeof(PartitionInfo)); 1) Please stop casting the results of palloc and malloc. We are not writing C++ here. I thought it was/is a good C programming practice to typecast (void *) always to the returning structure type!! Regards, Nikhils 2) I would prefer that you apply sizeof on the variable, not on the type. That way, the expression is independent of any type changes of the variable, and can be reviewed without having to scroll around for the variable definition. So how about, partinfo = palloc(ntups * sizeof(*partinfo)); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Union test case broken in make check?
Kenneth Marshall k...@rice.edu writes: Without an order by, the order is not defined. Yeah, but with the same data and the same software it should generally give the same result; as evidenced by the fact that these same regression tests have worked for most people for years. There's something odd happening on Emmanuel's machine. Maybe he's changed the hashing algorithms or some planner cost parameters? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Syntax for partitioning
On fre, 2009-11-20 at 11:14 +0530, Nikhil Sontakke wrote: Hi, partinfo = (PartitionInfo *) malloc(ntups * sizeof(PartitionInfo)); 1) Please stop casting the results of palloc and malloc. We are not writing C++ here. I thought it was/is a good C programming practice to typecast (void *) always to the returning structure type!! This could be preferable if you use sizeof on the type, so that you have an additional check that the receiving variable actually has that type. But if you use sizeof on the variable itself, it's unnecessary: You just declare the variable to be of some type earlier, and then the expression allocates ntups of it, without having to repeat the type information. Regards, Nikhils 2) I would prefer that you apply sizeof on the variable, not on the type. That way, the expression is independent of any type changes of the variable, and can be reviewed without having to scroll around for the variable definition. So how about, partinfo = palloc(ntups * sizeof(*partinfo)); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Thu, 2009-11-19 at 10:13 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: Recovery does *not* take the same locks as the original statements on the master took. For example, the WAL record for an INSERT just makes its changes without acquiring locks. This is OK as long as we only allow read-only users to acquire AccessShareLocks. If we allowed higher locks we might need to do deadlock detection, which would add more complexity. But we *do* allow higher locks than AccessShareLocks, as Tatsuo-sans example shows. Is that a bug? The above restrictions are limited to LOCKTAG_OBJECT so that advisory locks work as advertised. So advisory locks can take both shared and exclusive locks. This never conflicts with recovery because advisory locks are not WAL logged. So we allow any lock on anything *except* LOCKTAG_OBJECT. That includes advisory locks, but also relation locks, tuple locks and page locks. Looking at the lock types in detail: LOCKTAG_RELATION Any lock level is allowed. We have other defenses against actually modifying a relation, but it feels a bit fragile and I got the impression from your comments that it's not intentional. Possibly fragile, will look further. LOCKTAG_OBJECT was the important one in testing. LOCKTAG_RELATION_EXTEND Any lock level is allowed. Again, we have other defenses against modifying relations, but feels fragile. This only ever happens after xid is assigned, which can never happen. Happy to add protection if you think so. LOCKTAG_PAGE Any lock level is allowed. Page locks are only used when extending a hash index, so it seems irrelevant what we do. I think we should disallow page locks in standby altogether. As above, but OK. LOCKTAG_TUPLE, Any lock level is allowed. Only used when locking a tuple for update. We forbid locking tuples by the general is the transaction read-only? check in executor, and if you manage to bypass that, you will fail to get an XID to set to xmax. Nevertheless, seems we shouldn't allow tuple locks. Specifically disallowed earlier when row marks queries are requested. LOCKTAG_TRANSACTION, Any lock level is allowed. Acquired in AssignTransactionId, to allow others to wait for the transaction to finish. We don't allow AssignTransactionId() during recovery, but could someone want to wait for a transaction to finish? All the current callers of XactLockTableWait() seem to be from operations that are not allowed in recovery. Should we take a conservative stance and disallow taking transaction-locks? Only used after xid assignment, which is disallowed. LOCKTAG_VIRTUALTRANSACTION Any lock level is allowed. Similar to transaction locks, but virtual transaction locks are held by read-only transactions as well. Also during recovery, and we rely on it in the code to wait for a conflicting transaction to finish. But we don't acquire locks to represent transactions in master. Only ever requested as exclusive. LOCKTAG_OBJECT, Anything higher than AccessShareLock is disallowed. Used by dependency walking in pg_depend.c. Also used as interlock between database start and DROP/CREATE DATABASE. At backend start, we normally take RowExclusiveLock on the database in postinit.c, but you had to modify to acquire AccessShareLock instead in standby mode. Yes LOCKTAG_USERLOCK LOCKTAG_ADVISORY Any lock level is allowed. As documented, advisory locks are per-server, so a lock taken in master doesn't conflict with one taken in slave. Yes -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Thu, 2009-11-19 at 17:15 +0900, Tatsuo Ishii wrote: Simon Riggs wrote: Recovery does *not* take the same locks as the original statements on the master took. For example, the WAL record for an INSERT just makes its changes without acquiring locks. This is OK as long as we only allow read-only users to acquire AccessShareLocks. If we allowed higher locks we might need to do deadlock detection, which would add more complexity. But we *do* allow higher locks than AccessShareLocks, as Tatsuo-sans example shows. Is that a bug? Sorry for confusion. My example is under normal PostgreSQL, not under HS enabled. Are you saying you want it to work in HS mode? Why would you want to PREPARE an INSERT, but never execute it? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Python 3.1 support
On tor, 2009-11-19 at 13:43 -0500, Tom Lane wrote: But in any case, my main concern here is that I don't want to have to predetermine which python version a user of Red Hat/Fedora will have to use. If they can only use one at a time, that's still a good bit better than not having a choice at all. By the way, mod_wsgi supports Python 3 already (same patch as here, in principle). From the Fedora wiki page, I gather that no one has really looked into packaging that yet for Python 3, but if someone does, maybe we can cross-inspire ourselves. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why do OLD and NEW have special internal names?
Jan Wieck janwi...@yahoo.com writes: But this brings up another point about the recent discussion of what RENAME is good for. Removing RENAME may conflict with using OLD/NEW in UPDATE ... RETURNING. No? Um ... not sure why. Specific example please? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Simon Riggs wrote: On Thu, 2009-11-19 at 17:15 +0900, Tatsuo Ishii wrote: Simon Riggs wrote: Recovery does *not* take the same locks as the original statements on the master took. For example, the WAL record for an INSERT just makes its changes without acquiring locks. This is OK as long as we only allow read-only users to acquire AccessShareLocks. If we allowed higher locks we might need to do deadlock detection, which would add more complexity. But we *do* allow higher locks than AccessShareLocks, as Tatsuo-sans example shows. Is that a bug? Sorry for confusion. My example is under normal PostgreSQL, not under HS enabled. Are you saying you want it to work in HS mode? Why would you want to PREPARE an INSERT, but never execute it? well I can easily imagine an application that keeps persistent connections and prepares all the queries it might execute after it does the initial connection yet being still aware of the master/slave setup. So the scenario would be prepare but never execute as long as we are in recovery - but once we left recovery we can use them. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Syntax for partitioning
Nikhil Sontakke nikhil.sonta...@enterprisedb.com writes: partinfo = (PartitionInfo *) malloc(ntups * sizeof(PartitionInfo)); 1) Please stop casting the results of palloc and malloc. We are not writing C++ here. I thought it was/is a good C programming practice to typecast (void *) always to the returning structure type!! Yes. The above is good style because it ensures that the variable you're assigning the pointer to is the right type to match the sizeof computation. In C++ you'd use operator new instead and still have that type-check without the cast, but indeed we are not writing C++ here. The *real* bug in the quoted code is that it's using malloc. There are a few places in PG where it's appropriate to use malloc not palloc, but pretty darn few. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Joshua D. Drake wrote: On Fri, 2009-11-20 at 11:14 +0900, Josh Berkus wrote: On 11/15/09 11:07 PM, Heikki Linnakangas wrote: - When replaying b-tree deletions, we currently wait out/cancel all running (read-only) transactions. We take the ultra-conservative stance because we don't know how recent the tuples being deleted are. If we could store a better estimate for latestRemovedXid in the WAL record, we could make that less conservative. Simon was explaining this issue here at JPUGCon; now that I understand it, this specific issue seems like the worst usability issue in HS now. Bad enough to kill its usefulness for users, or even our ability to get useful testing data; in an OLTP production database with several hundred inserts per second it would result in pretty much never being able to get any query which takes longer than a few seconds to complete on the slave. I am pretty sure that OmniTI, PgExperts, EDB and CMD all have customers that are doing more than that... This sounds pretty significant. Agreed, it's the biggest usability issue at the moment. The max_standby_delay option makes it less annoying, but it's still there. I'm fine with it from a code point of view, so I'm not going to hold off committing because of it, but it sure would be nice to address it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Fri, Nov 20, 2009 at 11:14 AM, Josh Berkus j...@agliodbs.com wrote: On 11/15/09 11:07 PM, Heikki Linnakangas wrote: - When replaying b-tree deletions, we currently wait out/cancel all running (read-only) transactions. We take the ultra-conservative stance because we don't know how recent the tuples being deleted are. If we could store a better estimate for latestRemovedXid in the WAL record, we could make that less conservative. Simon was explaining this issue here at JPUGCon; now that I understand it, this specific issue seems like the worst usability issue in HS now. Bad enough to kill its usefulness for users, or even our ability to get useful testing data; in an OLTP production database with several hundred inserts per second it would result in pretty much never being able to get any query which takes longer than a few seconds to complete on the slave. I don't think that's all that was discussed :) Are you saying that it should not be committed if this issue still exists? The point of getting Hot Standby into core is to provide useful functionality. We can make it clear to people what the limitations are, and Simon has said that he will continue to work on solving this problem. -selena -- http://chesnok.com/daily - me http://endpoint.com - work -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why do OLD and NEW have special internal names?
On 11/20/2009 1:12 AM, Tom Lane wrote: Jan Wieck janwi...@yahoo.com writes: But this brings up another point about the recent discussion of what RENAME is good for. Removing RENAME may conflict with using OLD/NEW in UPDATE ... RETURNING. No? Um ... not sure why. Specific example please? regards, tom lane Inside a trigger proc, NEW is supposed to mean the new row for the table that fired the trigger. However, inside an UPDATE RETURNING for example, there is another set of NEW and OLD. Let's call the trigger call's NEW NEW_a and the UPDATE RETURNING NEW NEW_b. How would the developer specify something like INSERT ... RETURNING (NEW_a.value - NEW_b.value)? Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Fri, Nov 20, 2009 at 2:58 AM, Andrew Dunstan and...@dunslane.net wrote: Right. The major use I was hoping for from HS was exactly to be able to run long-running queries. In once case I am thinking of we have moved the business intelligence uses off the OLTP server onto a londiste replica, and I was really wanting to move that to a Hot Standby server. I think Simon's focus on the High Availability use case has obscured the fact that there are two entirely complementary (and conflicting) use cases here. If your primary reason for implementing Hot Standby is to be able to run long-running batch queries then will probably want to set a very high max_standby_delay or even disable it entirely. If you set max_standby_delay to 0 then the recovery will wait indefinitely for your batch queries to finish. You would probably need to schedule quiet periods in order to ensure that the recovery can catch up periodically. If you also need high availability you would need your HA replicas to run with a low max_standby_delay setting as well. This doesn't mean that the index btree split problem isn't a problem though. It's just trading one problem for another. Instead of having all your queries summarily killed regularly you would find recovery pausing extremely frequently for a very long time, rather than just when vacuum runs and for a limited time. I missed the original discussion of this problem, do you happen to remember the subject or url for the details? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Joachim Wieland wrote: The example you brought up fails if Session 2 disregards the notifications based on the current set of channels that it is listening to at this point. Right. Session 2 might not be listening at all yet. If I understand you correctly what you are suggesting is to not read uncommitted notifications from the queue in a reading backend or read all notifications (regardless of which channel it has been sent to), such that the backend can apply the check (Am i listening on this channel?) later on. Right. Note that we don't preserve notifications when the database restarts. But 2PC can cope with restarts. How would that fit together? The notifications are written to the state file at prepare. They can be recovered from there and written to the queue again at server start (see twophase_rmgr.c). Also I am not sure how you are going to deliver notifications that happen between the PREPARE TRANSACTION and the COMMIT PREPARED (because you have only one queue pointer which you are not going to advance...) ? Yeah, that's a problem. One uncommitted notification will block all others too. In theory you have the same problem without 2PC, but it's OK because you don't expect one COMMIT to take much longer to finish than others. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Syntax for partitioning
Peter Eisentraut pete...@gmx.net writes: 2) I would prefer that you apply sizeof on the variable, not on the type. That way, the expression is independent of any type changes of the variable, and can be reviewed without having to scroll around for the variable definition. FWIW, I think the general project style has been the other way. Yes, it means you write the type name three times not once, but the other side of that coin is that it makes it more obvious what is happening (and gives you an extra chance to realize that the type you wrote is wrong ...) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Fri, 2009-11-20 at 11:14 +0900, Josh Berkus wrote: On 11/15/09 11:07 PM, Heikki Linnakangas wrote: - When replaying b-tree deletions, we currently wait out/cancel all running (read-only) transactions. We take the ultra-conservative stance because we don't know how recent the tuples being deleted are. If we could store a better estimate for latestRemovedXid in the WAL record, we could make that less conservative. Simon was explaining this issue here at JPUGCon; now that I understand it, this specific issue seems like the worst usability issue in HS now. Bad enough to kill its usefulness for users, or even our ability to get useful testing data; in an OLTP production database with several hundred inserts per second it would result in pretty much never being able to get any query which takes longer than a few seconds to complete on the slave. sigh This post isn't really very helpful. You aren't providing the second part of the discussion, nor even requesting that this issue be fixed. I can see such comments being taken up by people with a clear interest in dissing HS. The case of several hundred inserts per second would not generate any cleanup records at all. So its not completely accurate, nor is it acceptable to generalise. There is nothing about the HS architecture that will prevent it from being used by high traffic sites, or for long standby queries. The specific action that will cause problems is a work load that generates high volume inserts and deletes. A solution is possible. Heikki and I had mentioned that solving this need not be part of the initial patch, since it wouldn't effect all users. I specifically removed my solution in July/Aug, to allow the patch to be slimmed down. In any case, the problem does have a simple workaround that is documented as part of the current patch. Conflict resolution is explained in detail with the patch. From my side, the purpose of discussing this was to highlight something which is not technically a bug, yet clearly still needs work before close. And it also needs to be on the table, to allow further discussion and generate the impetus to allow work on it in this release. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Fri, 2009-11-20 at 06:47 +, Greg Stark wrote: On Fri, Nov 20, 2009 at 2:58 AM, Andrew Dunstan and...@dunslane.net wrote: Right. The major use I was hoping for from HS was exactly to be able to run long-running queries. In once case I am thinking of we have moved the business intelligence uses off the OLTP server onto a londiste replica, and I was really wanting to move that to a Hot Standby server. I think Simon's focus on the High Availability use case has obscured the fact that there are two entirely complementary (and conflicting) use cases here. If your primary reason for implementing Hot Standby is to be able to run long-running batch queries then will probably want to set a very high max_standby_delay or even disable it entirely. If you set max_standby_delay to 0 then the recovery will wait indefinitely for your batch queries to finish. You would probably need to schedule quiet periods in order to ensure that the recovery can catch up periodically. If you also need high availability you would need your HA replicas to run with a low max_standby_delay setting as well. If I read this correctly then I have provided the facilities you would like. Can you confirm you have everything you want, or can you suggest what extra feature is required? This doesn't mean that the index btree split problem isn't a problem though. It's just trading one problem for another. Instead of having all your queries summarily killed regularly you would find recovery pausing extremely frequently for a very long time, rather than just when vacuum runs and for a limited time. I missed the original discussion of this problem, do you happen to remember the subject or url for the details? December 2008; hackers; you, me and Heikki. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Python 3.1 support
On tor, 2009-11-19 at 13:12 -0700, James Pye wrote: I think of a PL/Python function as a Python script file stored in the database. For Python, I think that's a mistake. Python scripts are independent applications. Is there any precedent for the sort of behavior that you are implementing, that is, automatic sharing of variables between independent executions of the same source container? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] enable-thread-safety defaults?
On fre, 2009-11-20 at 02:41 +0100, Magnus Hagander wrote: Is there any actual reason why we are building without thread safety by default on most platforms? Consistent defaults on all platforms? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Syntax for partitioning
On Thu, 2009-11-19 at 10:53 -0500, Robert Haas wrote: On Thu, Nov 19, 2009 at 9:58 AM, Markus Wanner mar...@bluegap.ch wrote: Hi, Robert Haas wrote: Settling on a syntax, and an internal representation for that syntax, I've been under the impression that this was only about syntax. What are the internal additions? I haven't looked at it in detail, but it adds a new pg_partition table. Whether that table is suitably structured for use by the optimizer is not clear to me. If it does, then my review comments to Kedar still apply: * why do we want another catalog table? what's wrong with pg_inherits? It might need additional columns, and it certainly needs another index. * We need an internal data structure (discussed on this thread also). Leaving stuff in various catalog tables would not be the same thing at all. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Simon Riggs wrote: On Fri, 2009-11-20 at 06:47 +, Greg Stark wrote: I missed the original discussion of this problem, do you happen to remember the subject or url for the details? December 2008; hackers; you, me and Heikki. Yep: http://archives.postgresql.org/message-id/494b5ffe.4090...@enterprisedb.com -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] enable-thread-safety defaults?
2009/11/20 Peter Eisentraut pete...@gmx.net: On fre, 2009-11-20 at 02:41 +0100, Magnus Hagander wrote: Is there any actual reason why we are building without thread safety by default on most platforms? Consistent defaults on all platforms? So why do we have largefile enabled by default? And zlib? And readline? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
