Re: [HACKERS] generalizing the planner knobs
On Sun, 2005-12-04 at 12:49 -0300, Alvaro Herrera wrote: Simon Riggs wrote: ISTM we could do some of that with another GUC, lets call it prepare_once = on. The system default is to have a prepared statement bound to a plan on its first parameter bind. If we set this to off, then the statement will replan each time we bind. This would give us both flexibility and predictability. (As ever, someone suggest a better name?). Why would all statements behave the same? They would be flexible and predictable, but not the same. prepare_once = off would reoptimize each statement, so each could have a potentially different plan. Which, in the case I cited, is the only optimal behaviour: sticking to any one plan, by any method, would be wrong. The plans would be predictable because performance never exceeds the worst case SeqScan; planning would be flexible because it will always take the best plan. I think an important percentage of cases would require a fixed plan (thus planning at first sight is a good idea), while a limited number of cases would require planning every time the sentence is called. Yes, that is exactly what I see. Hence a GUC with a default the same as it is now: they would only be prepared once. You would only set the GUC to another value when you have a statement that looks like it needs hinting i.e. the plan flips from SeqScan to IndexScan and back depending upon the input data. Your idea of qualifying it by table name does not make too much sense to me, because you can have both types of queries for each table, and further any query where this is necessary will involve more than one table anyway, so which one do you choose to make the decision? That was a different idea later down my note, not a variation of the same one: that had nothing to do with the prepare_once concept. Those options were meant to be set on a per statement basis, not at the server level. I was trying to solve Neil's stated problem: How to force one part of a query to avoid a SeqScan, yet without touching the others. So we would provide a protocol/libpq option to allow first-params- planning (the default and current behavior), and another to allow planning-every-time. The latter would tell the server to save only the parsetree of the query and replan each time it is invoked. Or some function similar. I prefer the GUC because it does not imply a protocol change. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] generalizing the planner knobs
On Sun, 2005-12-04 at 13:47 -0500, Pollard, Mike wrote: Simon Riggs wrote The system default is to have a prepared statement bound to a plan on its first parameter bind. We call it deferred optimization. Do you really stop at the first parameter? The first bind of parameters to the query, yes. You can do the same thing with correlated subqueries Not currently done, AFAIK. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] generalizing the planner knobs
On Mon, 2005-12-05 at 01:53 -0500, Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: There is no such thing as a plan that is good for every case --- outlying data values can make a usually-good plan blow out your performance guarantee anyway. But outlying data is something the user has control over. Unfortunately, the DBA cannot choose the data distribution in his database. So the appearance of control is somewhat illusory. The user when approving plans needs to be aware not just that the plan is experimentally good, but that it will perform reliably within the constraints based on his knowledge of the application and the data. Greg's idea to have a plan comparator is a good one, for most situations. What you'll see if you run it though is no matter what you do, there will be a few queries that are resistant to tuning. Their stored plans will flip from SeqScan to IndexScan and back depending upon the parameters used; neither will be suitable all the time and either setting will cause very variable response times. For those queries only, I seek a solution. [Priming the cache by executing IndexScan causing queries does not work for all cases, so again the appearance of control is illusory.] My solution is to replan the queries each time, rather than just once on first parameter bind. By some mechanism; the GUC is just one of those. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] generalizing the planner knobs
On Dec 5, 2005, at 4:17 AM, Tom Lane wrote: Greg Stark [EMAIL PROTECTED] writes: Plan stability is also an important feature, especially for OLTP systems which have hard real-time requirements. OLTP systems typically don't care about getting the best plan for a query, only a plan that is good enough. Good enough means it can keep up with the rate of incoming requests; it doesn't matter whether it keeps up with 10% headroom or 20% headroom. But if one incoming query even one in a thousand takes 1000% of the time available then the entire system risks falling down. Is it worth pointing out that using the same plan all the time is *no* recipe for guaranteeing response time? There is no such thing as a plan that is good for every case --- outlying data values can make a usually-good plan blow out your performance guarantee anyway. Disabling the planner is just a recipe for ensuring that that will happen, IMHO. regards, tom lane I think I know what Greg is trying to say: I think in this plan stability does not mean that the plan has to be completely fixed - usually it is all about indexing. People start with an empty perfectly analyzed database and data is added. However, some day some cron job doing ANALYZE or whatever fails and the system will slow down or even break down because data is added to some table which is still seq-scanned. This is what usually happens and which leads to support cases. Adding hints to some comments or to the statement itself is not a good solution as well. This is why I proposed a table or some flag telling the planner what to favour (= always use a certain index). So the basic idea is not to turn index of in general but to have the chance to do it on a per index basis. I guess this would not be to complex to implement and it solves 90% of all problems without having to hide some information inside comments (which is no good at all). best regards, hans ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Reducing relation locking overhead
On Sun, Dec 04, 2005 at 10:40:55PM -0800, Kevin Brown wrote: One thing I don't quite understand about the discussion is why there's particular attention being paid to deadlocks with respect to REINDEX when it clearly can happen in the general case when lock promotion is involved. Why is REINDEX special here? Although there is a general case, I don't believe it is a common case. INSERT creates the rows invisible to other transactions. UPDATE and DELETE would lock the row for writing. Normally, lock promotion isn't required. If, however, a person locks the row for reading using SELECT, and then attempts to upgrade the lock by SELECT, UPDATE, or DELETE, this would result in a lock promotion. If the problem is that REINDEX has to hold an AccessShareLock to prevent the table or index from being dropped, but does not need to prevent writers in general (because the presumption is that there is some way of efficiently discovering the addtional modifications made during the bulk of REINDEX processing), then doesn't that mean that an AccessShareLock is the wrong kind of lock for REINDEX to be holding, and that the appropriate type of lock should be created if it doesn't already exist? I think the problem is the 'efficiently discovering the additional modifications during' REINDEX processing. A few ideas have been floating around - but none proven. Even the (rather clever in my opinion) solution that would create the index incomplete, allowing new updates to be written to the index automatically, while REINDEX fills in the rest of it in the background before marking it complete, still has problems. If it is a new unique index, then for a time, the unique constraint would not be enforced. I think it has to be tweaked some, such that the new incomplete index would be created along-side the other index, and once the indexes match up, remove the original, and rename the new one into place. Additionally, I was under the impression that normal INSERTs, UPDATEs, and DELETEs didn't generally need to acquire AccessExclusiveLock, because of MVCC. If that's the case, then aren't the operations that could deadlock REINDEX relatively rare? And if those operations *do* need to acquire that lock type, then what exactly does MVCC buy you? REINDEX needs to see visible and invisible rows. This goes back to the previous point. Efficiently and reliably discovering newly created rows. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Concurrent CREATE INDEX, try 2 (was Re: [HACKERS] Reducing relation locking overhead)
Ühel kenal päeval, R, 2005-12-02 kell 02:14, kirjutas Tom Lane: Greg Stark [EMAIL PROTECTED] writes: It was a *major* new feature that many people were waiting for when Oracle finally implemented live CREATE INDEX and REINDEX. The ability to run create an index without blocking any operations on a table, even updates, was absolutely critical for 24x7 operation. Well, we're still not in *that* ballpark and I haven't seen any serious proposals to make us so. How absolutely critical is it really? Is REINDEX-in-parallel-with-reads-but-not-writes, which is what we actually have at the moment, an absolutely critical facility? I don't think REINDEX to be very critical (exept if for some reason you have failed to vacuum a high-traffic table for some time and need to get index sizes down). OTOH, being able to add indexes on live database is sometimes required, and neither of the current ways ( accept a few hours of downtime or use slony relica and do a swithcover) are always acceptable. This capability is reportedly present in MSSQL and available for Oracle if you get the more expensive Enetrprise Edition. So, after more thinking, I have come up with a proposal for fully concurrent (read+write) create index, which should need minimal amount of locking. Concurrent CREATE INDEX Concurrent index NDX1 on table TAB1 is created like this: 1) start transaction. take a snapshot SNAP1 1.1) optionally, remove pages for TAB1 from FSM to force (?) all newer inserts/updates to happen at end of table (won't work for in-page updates without code changes) 2) create the index as we do now, but only for pages which are visible in SNAP1 3) record the index in pg_class, but mark it as do not use for lookups in a new field. Take snapshot SNAP2. commit transaction. -- at this point all new inserts and updates will be recorded in NDX1 4) Run a full scan over TAB1 and add all rows that are visible in SNAP2 but not in SNAP1 to NDX1. (if there is some way (like p1.1) to restrict or record the area in heap that new tuples go to, then this can be done more efficiently than full scan) 5) record the status of index as ready for use. -- now the index is fully created and usable This is in no way a final proposal, but rather starting point for discussion of how things might be doable. For example p.3 is probably tricky to do in a way that all backends pick up at the right time. This will need most places that do table updates to be reviewed to make sure that they check for new indexes. Any comments are appreciated. - Hannu Krosing ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
I wrote:
Bruce Momjian said:
OK, a few things. First, Tom has fixed snprintf.c so it should
properly process positional parameters now. Would you first test the
libintl version of *printf to see if it can process %$ parameters
(probably by hacking up any language file and testing the printing),
and then try your patch below to see if it is properly reorders the
arguments. If libintl does not reorder properly, but our snprintf.c
does, would you please generate an appliable patch we can put into
8.1.X? Thanks.
I know I am asking a lot, but you are the man on this one. :-)
Since the effect of the configure change I am proposing to reverse was to
force use of the *printf in libintl, don't we already know the answer to
your first question from Nicolai's report?
However, a very simple test shows that the libintl printf does indeed do
%m$ processing:
$ cat testpf.c
#include libintl.h
main()
{
printf(%2$s %1$s\n,arg1,arg2);
}
$ gcc -o testpf testpf.c -lintl
$ ./testpf.exe
arg2 arg1
$
So the next question is why isn't it working in the build.
cheers
andrew
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Andrew Dunstan [EMAIL PROTECTED] writes: However, a very simple test shows that the libintl printf does indeed do %m$ processing: ... So the next question is why isn't it working in the build. Is it possible that the build that was being complained of was using our previous, very-broken snprintf.c? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Reducing relation locking overhead
Kevin Brown [EMAIL PROTECTED] writes: And now I see why, since it will introduce deadlocks (sigh). But what of the other rule (always acquiring locks against the table before the index)? You may have stopped reading at the above... We already do that. One thing I don't quite understand about the discussion is why there's particular attention being paid to deadlocks with respect to REINDEX when it clearly can happen in the general case when lock promotion is involved. Why is REINDEX special here? Because what people are asking for is an on-line REINDEX, ie, something that will go through in the presence of concurrent updates. Most other sorts of DDL changes to tables take exclusive locks so they don't have to worry about concurrency. (There is of course some risk of deadlock from the exclusive lock, but at least it happens *before* you've done a lot of work not *after*.) Additionally, I was under the impression that normal INSERTs, UPDATEs, and DELETEs didn't generally need to acquire AccessExclusiveLock, because of MVCC. If that's the case, then aren't the operations that could deadlock REINDEX relatively rare? The concern about deadlock applies to the various proposals that involve upgrading to a write-prevention lock at some late point in the process. That clearly has the potential to deadlock against relatively weak lock requests. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Reducing relation locking overhead
I wonder if it would work to release the AccessShareLock before acquiring the ExclusiveLock. Of course, this would let any ALTER TABLE or DROP TABLE to do anything they wanted, but we could check that the table is still the same after reacquiring the exclusive lock. REINDEX would have to abort if anything unexpected happened to the table while the REINDEX transaction was waiting after releasing the shared lock. What's not at all clear to me is at what time is the lock upgraded? Just after scanning the heap for the first time? In this case, how do we detect all the tuples that need to be inserted after we acquire the exclusive lock? Are they listed somewhere? I imagine each table could have a global flag telling there is an online reindex running for this relation. If this flag is set, each insert/delete to the index needs to save aside somewhere, the CTIDs of tuples it is inserting/deleting. So the protocol for reindex could be: acquire vacuum lock acquire read lock set REINDEX flag build the bulk of the index -- this takes a lot of time ... -- meanwhile other transactions save CTIDs in a spill area release read lock acquire exclusive lock recheck the table, abort if something weird happened read the spill area, insert/delete from the index as appropiate mark the index as complete release exclusive lock release vacuum lock The vacuum lock is designed to leave any VACUUM out of the equation, but let run any select/insert/update/delete run. Maybe this lock could leave ALTER TABLE and other stuff out too. Not sure if we have something like this in our lock table -- if not, can we create it? Note that by this proposal, any DDL gets more expensive -- but if it's the normal case (no REINDEX running), it's only a flag check. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: However, a very simple test shows that the libintl printf does indeed do %m$ processing: ... So the next question is why isn't it working in the build. Is it possible that the build that was being complained of was using our previous, very-broken snprintf.c? There's currently a config setting that is supposed to inhibit its use on Windows. I am quite confused. What is more, when I set the locale of my machine to Turkish and run the installer project's 8.1_RC1 which I happen to have installed there, and set lc_messages to tr_TR.UTF-8, I don't see lines like Nicolai reported: LOG: $s veritaban?n transaction ID warp limiti $u I see this: LOG: 2147484146 veritabanin transaction ID warp limiti postgres So I'm inclined to think there might be something odd about his setup and maybe we aren't quite so broken after all. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Andrew Dunstan [EMAIL PROTECTED] writes: What is more, when I set the locale of my machine to Turkish and run the installer project's 8.1_RC1 which I happen to have installed there, and set lc_messages to tr_TR.UTF-8, I don't see lines like Nicolai reported: LOG: $s veritaban?n transaction ID warp limiti $u I see this: LOG: 2147484146 veritabanin transaction ID warp limiti postgres Well, that's pretty broken too :-(. The tr.po file entry is msgid transaction ID wrap limit is %u, limited by database \%s\ msgstr \%2$s\ veritabanın transaction ID warp limiti %1$u and if I'm not completely confused, correct translated output would be postgres veritabanın transaction ID warp limiti 2147484146 Nicolai's report looks a bit like what you would expect from an sprintf implementation that hadn't heard of %n$ specs at all. Your report looks suspiciously like what our broken version of sprintf was producing last week --- see http://archives.postgresql.org/pgsql-hackers/2005-12/msg00194.php How certain are you that that config setting is inhibiting use of port/snprintf.c? It seems unlikely that any other implementation would have duplicated our bug. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Reducing relation locking overhead
Tom Lane wrote: The concern about deadlock applies to the various proposals that involve upgrading to a write-prevention lock at some late point in the process. That clearly has the potential to deadlock against relatively weak lock requests. After looking at the various lock types, I don't see how this is the case at all (which may mean that I'm more confused than ever. But please read on). It seems to me that only ops that promote to AccessExclusiveLock can deadlock against at least some of the proposed REINDEX implementations. REINDEX would have to initially grab AccessShareLock, of course, but AccessExclusiveLock is the only lock type that blocks against it, so in the case of lock promotion the only operations that would cause REINDEX to really deadlock (as opposed to simply blocking) are operations on the entire table (ALTER TABLE, DROP TABLE, etc.). None of the common operations block against an AccessShareLock, and the order of acquisition against objects (table vs index) is already enforced, so where's the deadlock potential? REINDEX would, I expect, promote its lock to ShareLock when it's time for it to block writers. That would block against quite a number of operations, of course, but that's not a problem in and of itself, because it need only wait until the operations in question are finished. The lock won't be granted until those other operations are finished, and nothing aside from table-level ops will block against the REINDEX until that lock is granted. A true deadlock won't happen against common operations unless REINDEX promotes its lock again to something stronger than ShareLock, and that's easy to avoid: just have REINDEX promote directly from AccessShareLock to the strongest lock it will ever need. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: What is more, when I set the locale of my machine to Turkish and run the installer project's 8.1_RC1 which I happen to have installed there, and set lc_messages to tr_TR.UTF-8, I don't see lines like Nicolai reported: LOG: $s veritaban?n transaction ID warp limiti $u I see this: LOG: 2147484146 veritabanin transaction ID warp limiti postgres Well, that's pretty broken too :-(. The tr.po file entry is msgid transaction ID wrap limit is %u, limited by database \%s\ msgstr \%2$s\ veritabanın transaction ID warp limiti %1$u and if I'm not completely confused, correct translated output would be postgres veritabanın transaction ID warp limiti 2147484146 Nicolai's report looks a bit like what you would expect from an sprintf implementation that hadn't heard of %n$ specs at all. Your report looks suspiciously like what our broken version of sprintf was producing last week --- see http://archives.postgresql.org/pgsql-hackers/2005-12/msg00194.php How certain are you that that config setting is inhibiting use of port/snprintf.c? It seems unlikely that any other implementation would have duplicated our bug. Sorry ... I got into a muddle. I have rerun the tests. With 8.1_RC1 I *do* get the results Nicolai reported. With the changes I made yesterday, I see the result above, i.e. what we expect from our own breakage of sprintf (i haven't yet updated the snapshot I took). I will now try to verify that the changes you made in pg_sprintf do the right thing. We could ask why it appears that one version of libintl works (the one I got the other day from gnuwin32) and one doesn't (the one that is in the installer, apparently). But the simple fix seems to be to use our version of printf and friends. The changes requires are not too invasive. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Andrew Dunstan [EMAIL PROTECTED] writes: With 8.1_RC1 I *do* get the results Nicolai reported. With the changes I made yesterday, I see the result above, i.e. what we expect from our own breakage of sprintf (i haven't yet updated the snapshot I took). Ah. OK, that makes sense. But the simple fix seems to be to use our version of printf and friends. The changes requires are not too invasive. I agree with doing this even if we weren't faced with (apparently) multiple versions of libintl that don't all work alike. My thought is that running our own version of snprintf on a heavily used port like Windows is exactly what is needed to flush out any remaining bugs. It's obviously not gotten enough field usage yet ... Was the last patch you sent in ready for application, or are you still fooling with it? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: With 8.1_RC1 I *do* get the results Nicolai reported. With the changes I made yesterday, I see the result above, i.e. what we expect from our own breakage of sprintf (i haven't yet updated the snapshot I took). Ah. OK, that makes sense. But the simple fix seems to be to use our version of printf and friends. The changes requires are not too invasive. I agree with doing this even if we weren't faced with (apparently) multiple versions of libintl that don't all work alike. My thought is that running our own version of snprintf on a heavily used port like Windows is exactly what is needed to flush out any remaining bugs. It's obviously not gotten enough field usage yet ... Was the last patch you sent in ready for application, or are you still fooling with it? He is still working on it. It did not handle all *printf functions, as he mentioned, and he might have other changes. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] generalizing the planner knobs
Hans-Juergen Schoenig [EMAIL PROTECTED] writes: I think I know what Greg is trying to say: I think in this plan stability does not mean that the plan has to be completely fixed - usually it is all about indexing. Usually problems occur because someone hasn't run analyze at all. That's not what I'm talking about. I'm talking about a large mature system where the DBA has everything tuned and adjusted properly and just wants to get a good night's sleep, confident that the nightly analyze isn't going to suddenly change the performance of existing queries. Adding hints to some comments or to the statement itself is not a good solution as well. This is why I proposed a table or some flag telling the planner what to favour (= always use a certain index). So the basic idea is not to turn index of in general but to have the chance to do it on a per index basis. I guess this would not be to complex to implement and it solves 90% of all problems without having to hide some information inside comments (which is no good at all). I disagree that this is a reasonable solution. I want to be sure my existing queries keep using the plans they've been using until I allow them to change. I don't want to sit down and type select count(*) from users and have it not work correctly (ie, use a sequential scan) because the system is so single mindedly tuned for the OLTP application. -- greg ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] generalizing the planner knobs
[snip] I want to be sure my existing queries keep using the plans they've been using until I allow them to change. I don't want to sit down and type select count(*) from users and have it not work correctly (ie, use a sequential scan) because the system is so single mindedly tuned for the OLTP application. Now this is exactly what I've had in mind... it would be nice to fixate a plan for some of the queries, and let the planner choose the best for all the rest. I think some other data bases have something like an optimizer plan stability feature, providing outlines of query plan bundles. This is maybe too much, but specifying that for a certain query I definitely want to use one index and not the other would be nice... On another note, it might be interesting to have some kind of prepare analyze, where the planner is allowed to go and get some more detailed estimation from the actual table data based on the hard-coded parameter values, and produce some more detailed statistics for the parameterized values so it can then produce hot-shot plans for the actual parameter values on each execution... I wonder if this makes any sense. This way we could have some very detailed statistics directly supporting the queries we actually use. I would call this kind of prepare for the most used/problematic queries from time to time, and the planner should decide what statistics it needs to support it and go and get it... Cheers, Csaba. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SERIAL type feature request
Jan Wieck írta: On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote: I found this in the SQL2003 draft: 4.14.7 Identity columns ... An identity column has a start value, an increment, a maximum value, a minimum value, and a cycle option. ... The exact properties of a sequence. It would be a good idea to be able to provide all these the same way PostgreSQL provides CREATE SEQUENCE. I think nobody would object to implementing support for the SQL2003 syntax. Most of that would be providing all the values that will get forwarded into the internal sequence generation during CREATE TABLE. The other thing needed is an extension to the default value mechanism that overrides any given value to implement GENERATE ALLWAYS. Not too hard either. Where can I find this syntax? (PDF file name, page#) Thanks. I think I modify my feature request for the standard behaviour. Thanks and best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Reduce NUMERIC size by 2 bytes, reduce max length to 508 digits
[ Moved to hackers for patch discussion.] John D. Burger wrote: There are practical applications, eg, 1024-bit keys are fairly common objects in cryptography these days, and that equates to about 10^308. I don't really foresee anyone trying to run crypto algorithms with SQL NUMERIC arithmetic, though ... 2046 bit keys are becoming more common. However, math using these keys is usually done modulo a product of two primes and there are ways of doing the calculations that are going to be much faster than doing them the way Postgres does. So it is unlikely that anyone would be using Postgres' numeric type to do this in any case. Nonetheless, the fact that people can think of practical applications for numbers whose length is easily within a factor of two of the proposed limitation makes me squeamish about it being shrunk. Also, I would say the same arguments about doing math with NUMERICs suggest that saving a few byes in representation is not a big deal. On the few occasions where I have used NUMERICs, I didn't care about stuff like that. For what it's worth. Good point, but I am not 100% sure on the limitation. Look at this: test= CREATE TABLE test(x NUMERIC); CREATE TABLE test= INSERT INTO test SELECT pow(10::numeric, 1) + 1; INSERT 0 1 test= SELECT log(x) FROM test; log 1. (1 row) test= SELECT x % 10 FROM test; ?column? 1. (1 row) And this seems to work too: test= INSERT INTO test SELECT pow(10::numeric, 12) + 1; INSERT 0 1 The limit seems to be around 150k digits: test= INSERT INTO test SELECT pow(10::numeric, 15) + 1; ERROR: value overflows numeric format With current code, you can not define a NUMERIC column with greater than 1000 digits because we just placed an arbitrary limit on the length, but the computational length was obviously much larger than the storage limit. And I suppose you could exceed 1000 if you stored the result as text and converted it to NUMERIC just for computations. In fact we have this TODO, but I wonder if it is still an open issue: * Change NUMERIC to enforce the maximum precision We seem to enforce things just fine. Now, with the new patch, I see a _much_ lower limit: test= SELECT pow(10::NUMERIC, 511) + 1; ... (1 row) test= SELECT pow(10::NUMERIC, 512) + 1; ERROR: value overflows numeric format test= SELECT pow(10::NUMERIC, 512); ERROR: value overflows numeric format I thought maybe I could do the computations at least and then convert into text, but seeing the above it seems higher precision computation is just not possible --- it is more than just storage in a table that is changed. So, with the patch, the storage length is going from 1000 digits to 508, but the computational length is reduced from around 150k digits to 508. Now, because no one has complained about the 1000-digit limit, it is unlikely that anyone is doing calculations over 1000 or the would have had problems with storing the value, but I felt I should point out that we are dramatically changing the computational length. In fact, for the tests we have been running to debug the *printf problem, none of those queries will work with the patch: stest= SELECT factorial(4000); ERROR: value overflows numeric format test= SELECT factorial(400); ERROR: value overflows numeric format Not only does 4000! not work, but 400! doesn't even work. I just lost demo wow factor points! -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Bruce Momjian wrote: Was the last patch you sent in ready for application, or are you still fooling with it? He is still working on it. It did not handle all *printf functions, as he mentioned, and he might have other changes. Yeah. The good news: the new pg_*printf does the right thing for the %m$ parameters in the Turkish locale. The bad news: if we aren't compiling with NLS enabled, having those entries in exports.txt makes the libpq build blow up. So either we need to use pg_*printf unconditionally on Windows, or we need a little Makefile + sed magic to strip those entries out of exports.txt when it is used, if we're not doing NLS, or something of that kind. Question: do the entries in exports.txt have to be numbered consecutively, or just uniquely? With luck I can probably wrap this up today for the 8.1 stable branch - it would be nice if the new release actually did NLS right. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SERIAL type feature request
On 12/5/2005 1:03 PM, Zoltan Boszormenyi wrote: Jan Wieck írta: On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote: I found this in the SQL2003 draft: 4.14.7 Identity columns ... An identity column has a start value, an increment, a maximum value, a minimum value, and a cycle option. ... The exact properties of a sequence. It would be a good idea to be able to provide all these the same way PostgreSQL provides CREATE SEQUENCE. I think nobody would object to implementing support for the SQL2003 syntax. Most of that would be providing all the values that will get forwarded into the internal sequence generation during CREATE TABLE. The other thing needed is an extension to the default value mechanism that overrides any given value to implement GENERATE ALLWAYS. Not too hard either. Where can I find this syntax? (PDF file name, page#) Thanks. I think I modify my feature request for the standard behaviour. It's all in the Foundation paper inside this zip: http://www.wiscorp.com/sql/sql_2003_standard.zip Jan Thanks and best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 6: explain analyze is your friend -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Andrew Dunstan [EMAIL PROTECTED] writes: The bad news: if we aren't compiling with NLS enabled, having those entries in exports.txt makes the libpq build blow up. So either we need to use pg_*printf unconditionally on Windows, or we need a little Makefile + sed magic to strip those entries out of exports.txt when it is used, if we're not doing NLS, or something of that kind. I think it's a bad idea for exports.txt not to be the same in all builds. So yeah, if we export these names at all then it has to be unconditional. What about Plan B? Per Bruce's comment, it should really only be ecpg that needs an extra copy of snprintf.o, and it's not like ecpg doesn't already pull in various port/ files for itself. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Andrew Dunstan [EMAIL PROTECTED] writes: With luck I can probably wrap this up today for the 8.1 stable branch - it would be nice if the new release actually did NLS right. BTW, core has already agreed to postpone the releases a couple days while we make sure we have this problem nailed down. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Reduce NUMERIC size by 2 bytes, reduce max length to 508 digits
Bruce Momjian pgman@candle.pha.pa.us writes: The limit seems to be around 150k digits: It's exactly 10^(128K), as I've mentioned more than once. So, with the patch, the storage length is going from 1000 digits to 508, but the computational length is reduced from around 150k digits to 508. Now, because no one has complained about the 1000-digit limit, it is unlikely that anyone is doing calculations over 1000 or the would have had problems with storing the value, Only if they declared their columns as numeric(N) and not just plain unconstrained numeric. Not to mention the possibility that they're doing the same thing you just did, ie computing values and returning them to the client without ever storing them in a table. So I don't think the above reasoning is defensible. Not only does 4000! not work, but 400! doesn't even work. I just lost demo wow factor points! It looks like the limit would be about factorial(256). The question remains, though, is this computational range good for anything except demos? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Tom Lane wrote:
Andrew Dunstan [EMAIL PROTECTED] writes:
The bad news: if we aren't compiling with NLS enabled, having those
entries in exports.txt makes the libpq build blow up. So either we need
to use pg_*printf unconditionally on Windows, or we need a little
Makefile + sed magic to strip those entries out of exports.txt when it
is used, if we're not doing NLS, or something of that kind.
I think it's a bad idea for exports.txt not to be the same in all
builds. So yeah, if we export these names at all then it has to be
unconditional.
What about Plan B? Per Bruce's comment, it should really only be ecpg
that needs an extra copy of snprintf.o, and it's not like ecpg doesn't
already pull in various port/ files for itself.
The problem is that the alias will be picked up by every libpq client. I
got around the problem with ecpg's libpgtypes by unaliasing sprintf and
snprintf. But we can't do that everywhere.
I'm not sure I see the objection to stripping these out of the *.def files.
I can't spend any more time on this now - I have spent far too much on
it already. My working patch is attached. Maybe I can look at it again
in a few days.
cheers
andrew
Index: configure
===
RCS file: /projects/cvsroot/pgsql/configure,v
retrieving revision 1.461
diff -c -r1.461 configure
*** configure 5 Nov 2005 04:01:38 - 1.461
--- configure 5 Dec 2005 18:56:04 -
***
*** 17111,17123
# Force use of our snprintf if system's doesn't do arg control
# This feature is used by NLS
! if test $enable_nls = yes
!test $pgac_need_repl_snprintf = no
! # On Win32, libintl replaces snprintf() with its own version that
! # understands arg control, so we don't need our own. In fact, it
! # also uses macros that conflict with ours, so we _can't_ use
! # our own.
!test $PORTNAME != win32; then
echo $as_me:$LINENO: checking whether printf supports argument control 5
echo $ECHO_N checking whether printf supports argument control... $ECHO_C 6
if test ${pgac_cv_printf_arg_control+set} = set; then
--- 17111,17117
# Force use of our snprintf if system's doesn't do arg control
# This feature is used by NLS
! if test $enable_nls = yes test $pgac_need_repl_snprintf = no ; then
echo $as_me:$LINENO: checking whether printf supports argument control 5
echo $ECHO_N checking whether printf supports argument control... $ECHO_C 6
if test ${pgac_cv_printf_arg_control+set} = set; then
Index: src/include/c.h
===
RCS file: /projects/cvsroot/pgsql/src/include/c.h,v
retrieving revision 1.190
diff -c -r1.190 c.h
*** src/include/c.h 15 Oct 2005 02:49:41 - 1.190
--- src/include/c.h 5 Dec 2005 18:56:23 -
***
*** 96,101
--- 96,122
#ifdef ENABLE_NLS
#include libintl.h
+ #ifdef WIN32
+ #ifdef USE_SNPRINTF
+
+ #ifdef printf
+ #undef printf
+ #endif
+ #ifdef fprintf
+ #undef fprintf
+ #endif
+ #ifdef sprintf
+ #undef sprintf
+ #endif
+ #ifdef snprintf
+ #undef snprintf
+ #endif
+ #ifdef vsnprintf
+ #undef vsnprintf
+ #endif
+
+ #endif
+ #endif
#else
#define gettext(x) (x)
#endif
Index: src/interfaces/ecpg/pgtypeslib/extern.h
===
RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/pgtypeslib/extern.h,v
retrieving revision 1.7
diff -c -r1.7 extern.h
*** src/interfaces/ecpg/pgtypeslib/extern.h 15 Oct 2005 02:49:47 - 1.7
--- src/interfaces/ecpg/pgtypeslib/extern.h 5 Dec 2005 18:56:24 -
***
*** 1,6
--- 1,14
#ifndef __PGTYPES_COMMON_H__
#define __PGTYPES_COMMON_H__
+
+ #ifdef sprintf
+ #undef sprintf
+ #endif
+ #ifdef snprintf
+ #undef snprintf
+ #endif
+
#include pgtypes_error.h
/* These are the constants that decide which printf() format we'll use in
Index: src/interfaces/libpq/Makefile
===
RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/Makefile,v
retrieving revision 1.138
diff -c -r1.138 Makefile
*** src/interfaces/libpq/Makefile 29 Aug 2005 00:47:35 - 1.138
--- src/interfaces/libpq/Makefile 5 Dec 2005 18:56:24 -
***
*** 25,30
--- 25,34
override CFLAGS += $(PTHREAD_CFLAGS)
endif
+ ifneq ($(enable_nls), yes)
+ NONLS = -e '/^pg_.*printf/d'
+ endif
+
# Need to recomple any libpgport object files
LIBS := $(patsubst -lpgport,, $(LIBS))
***
*** 103,126
echo 'LIBRARY LIBPQ' $@
echo 'DESCRIPTION PostgreSQL Client Library' $@
echo 'EXPORTS' $@
! sed -e '/^#/d' -e 's/^\(.* \)\([0-9][0-9]*\)/\1@ \2/' $ $@
$(srcdir)/libpqddll.def: exports.txt
echo '; DEF file for MS VC++' $@
echo 'LIBRARY LIBPQD' $@
echo 'DESCRIPTION PostgreSQL Client Library' $@
echo 'EXPORTS' $@
! sed -e '/^#/d' -e 's/^\(.* \)\([0-9][0-9]*\)/\1@ \2/' $ $@
Re: [HACKERS] Reduce NUMERIC size by 2 bytes, reduce max length to 508 digits
On 12/5/05, Tom Lane [EMAIL PROTECTED] wrote: Not only does 4000! not work, but 400! doesn't even work. I just lost demo wow factor points! It looks like the limit would be about factorial(256). The question remains, though, is this computational range good for anything except demos? I've hesitated commenting, because I think it might be a silly reason, but perhaps it's one other people share. ... I use PG as a calculator for big numbers because it's the only user friendly thing on my system that can do factorial(300) - factorial(280). I'd rather use something like octave, but I've found its pretty easy to escape its range. If the range for computation is changed, then I'll probably keep an old copy around just for this, though I'm not quite sure how much I'd be affected.. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
-Original Message- From: Andrew Dunstan[EMAIL PROTECTED] Sent: 05/12/05 19:03:17 To: Tom Lane[EMAIL PROTECTED] Cc: Bruce Momjianpgman@candle.pha.pa.us, [EMAIL PROTECTED][EMAIL PROTECTED], [EMAIL PROTECTED][EMAIL PROTECTED], [EMAIL PROTECTED][EMAIL PROTECTED], pgsql-hackers@postgresql.orgpgsql-hackers@postgresql.org Subject: Re: [PATCHES] [HACKERS] snprintf() argument reordering not working I'm not sure I see the objection to stripping these out of the *.def files. It will be a recipe for disaster if different builds of the same dll have different exports - apps that pick up the wrong one from a shared dir for example are likely to crash at startup. We went to some effort to prevent this for 8.0, for example, by not having separate (and different) .def files for each compiler, but by building them all from exports.txt. Regards, Dave -Unmodified Original Message- Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: The bad news: if we aren't compiling with NLS enabled, having those entries in exports.txt makes the libpq build blow up. So either we need to use pg_*printf unconditionally on Windows, or we need a little Makefile + sed magic to strip those entries out of exports.txt when it is used, if we're not doing NLS, or something of that kind. I think it's a bad idea for exports.txt not to be the same in all builds. So yeah, if we export these names at all then it has to be unconditional. What about Plan B? Per Bruce's comment, it should really only be ecpg that needs an extra copy of snprintf.o, and it's not like ecpg doesn't already pull in various port/ files for itself. The problem is that the alias will be picked up by every libpq client. I got around the problem with ecpg's libpgtypes by unaliasing sprintf and snprintf. But we can't do that everywhere. I'm not sure I see the objection to stripping these out of the *.def files. I can't spend any more time on this now - I have spent far too much on it already. My working patch is attached. Maybe I can look at it again in a few days. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SERIAL type feature request
Jan Wieck írta:
On 12/5/2005 1:03 PM, Zoltan Boszormenyi wrote:
Jan Wieck írta:
On 12/4/2005 5:10 PM, Zoltan Boszormenyi wrote:
I found this in the SQL2003 draft:
4.14.7 Identity columns
... An identity column has a start value, an increment, a maximum
value, a minimum value,
and a cycle option. ...
The exact properties of a sequence. It would be a good idea to be
able to provide
all these the same way PostgreSQL provides CREATE SEQUENCE.
I think nobody would object to implementing support for the SQL2003
syntax. Most of that would be providing all the values that will get
forwarded into the internal sequence generation during CREATE TABLE.
The other thing needed is an extension to the default value
mechanism that overrides any given value to implement GENERATE
ALLWAYS. Not too hard either.
Where can I find this syntax? (PDF file name, page#) Thanks.
I think I modify my feature request for the standard behaviour.
It's all in the Foundation paper inside this zip:
http://www.wiscorp.com/sql/sql_2003_standard.zip
Thanks, I found it. It's GENERATED { ALWAYS | BY DEFAULT } AS
IDENTITY, isn't it?
If I interpret it correctly, GENERATED ALWAYS AS IDENTITY means that
no matter
what I give in INSERT INTO MYTABLE (serial_id, ...) VALUES (N, ...),
the sequence
next value will be inserted into the database. I am all for it, it's
much stronger than just watching
for the 0 value and would fit my needs.
The other behaviour is GENERATED BY DEFAULT AS IDENTITY,
which is what PostgreSQL currently provides.
Best regards,
Zoltán Böszörményi
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: What about Plan B? Per Bruce's comment, it should really only be ecpg that needs an extra copy of snprintf.o, and it's not like ecpg doesn't already pull in various port/ files for itself. The problem is that the alias will be picked up by every libpq client. Not at all; libpq clients do not import c.h. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Replication on the backend
What about replication or data distribution inside the backend. This is a valid issue? Thanks, Gustavo.
Re: [HACKERS] Shared locking in slru.c
I wrote:
The way the attached patch attacks this is for the shared-lock access
case to simply set the page's LRU counter to zero, without bumping up
the LRU counters of the other pages as the normal adjustment would do.
...
I'm not totally happy with this heuristic, though, and was
wondering if anyone had a better idea. Anyone seen a lock-free
data structure for LRU or approximately-LRU state tracking?
I've come up with what seems a slightly better way to do this. The idea
is to replace the current structure for tracking which page is the least-
recently-used with this:
typedef struct SlruSharedData
{
...
/*--
* We mark a page most recently used by setting
*page_lru_count[slotno] = ++cur_lru_count;
* The oldest page is therefore the one with the highest value of
*cur_lru_count - page_lru_count[slotno]
*--
*/
intcur_lru_count;
...
intpage_lru_count[NUM_SLRU_BUFFERS];
...
which makes the SlruRecentlyUsed macro look like
#define SlruRecentlyUsed(shared, slotno) \
do { \
intnew_lru_count = (shared)-cur_lru_count; \
if (new_lru_count != (shared)-page_lru_count[slotno]) { \
(shared)-cur_lru_count = ++new_lru_count; \
(shared)-page_lru_count[slotno] = new_lru_count; \
} \
} while (0)
and SlruSelectLRUPage() has to look for the maximum value of
cur_count - shared-page_lru_count[slotno] rather than just
shared-page_lru_count[slotno] as before. This seems like a win
in any case since it takes cycles out of the commonly used path at
a small increase in the cost of SlruSelectLRUPage --- but in that
routine you are about to do I/O anyway, so a few extra subtractions
are negligible.
However, the real reason for doing this is that I think it's OK for
the proposed SimpleLruReadPage_ReadOnly routine to apply this form
of SlruRecentlyUsed even though it holds only a shared lock. Assuming
that int reads and writes are atomic, the only possible failures are
1. If a process running the macro is delayed, it might store a stale
(hence too small) value back into cur_lru_count or a page_lru_count
array element after someone else has incremented them to a larger value.
2. Two processes might read the same cur_lru_count value at the same
time, so that one of their increments is lost. This has the same end
effect as #1, though.
Given reasonable care in SlruSelectLRUPage (see attached excerpt), we
can defend against these scenarios and usually still make a reasonable
choice of which page to evict. In any case, the worst possible scenario
is that we make a nonoptimal choice of page to evict due to confused
lru_count state. This price seems well worth the chance to reduce
contention for shared memory.
Thoughts, objections?
regards, tom lane
/*
* If we find any EMPTY slot, just select that one. Else locate the
* least-recently-used slot to replace.
*
* Normally the page_lru_count values will all be different and so
* there will be a well-defined LRU page. But since we allow
* concurrent execution of SlruRecentlyUsed() within
* SimpleLruReadPage_ReadOnly(), it is possible that multiple pages
* acquire the same lru_count values. In that case we break ties by
* choosing the furthest-back page.
*
* In no case will we select the slot containing latest_page_number
* for replacement, even if it appears least recently used.
*
* Notice that this next line forcibly advances cur_lru_count to a
* value that is certainly beyond any value that will be in the
* page_lru_count array after the loop finishes. This ensures that
* the next execution of SlruRecentlyUsed will give us good data,
* even if it's against a page that has the current counter value.
*/
cur_count = (shared-cur_lru_count)++;
best_delta = -1;
bestslot = 0;/* no-op, just keeps compiler quiet */
best_page_number = 0;/* ditto */
for (slotno = 0; slotno NUM_SLRU_BUFFERS; slotno++)
{
intthis_delta;
intthis_page_number;
if (shared-page_status[slotno] == SLRU_PAGE_EMPTY)
return slotno;
this_delta = cur_count - shared-page_lru_count[slotno];
if (this_delta 0)
{
/*
* Clean up in case shared updates have caused cur_count
* increments to get lost. We back off the page counts,
* rather than trying to increase cur_count, to avoid any
* question of infinite loops or failure in the presence of
* wrapped-around counts.
*/
shared-page_lru_count[slotno] = cur_count;
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: The problem is that the alias will be picked up by every libpq client. Not at all; libpq clients do not import c.h. Well, all the programs that use postgres-fe.h do. Sure, but all of them do (or should) include libpgport and can get at the functions from that. I'm coming around to thinking that the simple solution is just to use it unconditionally on Windows. I agree that that's what we should do, but it should be done the same way we handle other routines from libpgport. None of those are exported to our client-side programs via libpq. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] *printf and zero size
Tom, did you implement this functionality in *printf? The size may be given as zero to find out how many characters are needed; in this case, the str argument is ignored. Sprintf() and vsprintf() effectively assume an infinite size. Looking at the code it doesn't seem supported. Should it be added to the limitations comment section? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] *printf and zero size
Bruce Momjian pgman@candle.pha.pa.us writes: Tom, did you implement this functionality in *printf? The size may be given as zero to find out how many characters are needed; in this case, the str argument is ignored. Sprintf() and vsprintf() effectively assume an infinite size. Where do you read that? The SUS says the opposite: If the value of n is zero on a call to snprintf(), an unspecified value less than 1 is returned. and that's what our code implements. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] *printf and zero size
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom, did you implement this functionality in *printf? The size may be given as zero to find out how many characters are needed; in this case, the str argument is ignored. Sprintf() and vsprintf() effectively assume an infinite size. Where do you read that? The SUS says the opposite: If the value of n is zero on a call to snprintf(), an unspecified value less than 1 is returned. and that's what our code implements. I got it from the BSD/OS manual page, and in the NetBSD manual page I see: If size is zero, nothing is written and str may be a NULL pointer. and: Upon successful completion snprintf() and vsnprintf() return the number of characters that would have been written to a sufficiently sized str, excluding the terminating NUL character. but it seems this is some BSD'ism that we don't need to support if the standard doesn't say so. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] *printf and zero size
Bruce Momjian pgman@candle.pha.pa.us writes: Upon successful completion snprintf() and vsnprintf() return the number of characters that would have been written to a sufficiently sized str, excluding the terminating NUL character. but it seems this is some BSD'ism that we don't need to support if the standard doesn't say so. Yeah, that is a BSD-ism. You have a point though, we ought to document which return convention the code follows. I'll add something. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] About varlena2
To reduce size of varlen2.vl_len to int16. This has been mentioned before, but is there any show-stopper reasoning preventing us from doing that or somebody has been working on it? Sorry, just to repeat myself. Char types will benefit from that. Many applications are from DB2, Oracle or SQL Server: Max Char Length DB2 32672 SQL 8000 Oracle 4000 All of above just need varlena2. To support bigger char types, we could follow the tradition long varchar, etc. Or, we can introduce several new data types like short varchar to keep compatible with previous PostgreSQL applications. Regards, Qingqing ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] *printf and zero size
On Mon, Dec 05, 2005 at 04:35:31PM -0500, Bruce Momjian wrote: but it seems this is some BSD'ism that we don't need to support if the standard doesn't say so. I think the Linux manpage is more informative about this: The functions snprintf and vsnprintf do not write more than size bytes (including the trailing '\0'). If the output was truncated due to this limit then the return value is the number of characters (not including the trailing '\0') which would have been written to the final string if enough space had been available. Thus, a return value of size or more means that the output was truncated. (See also below under NOTES.) If an output error is encountered, a negative value is returned. [...] NOTES The glibc implementation of the functions snprintf() and vsnprintf() conforms to the C99 standard, i.e., behaves as described above, since glibc version 2.1. Until glibc 2.0.6 they would return -1 when the out- put was truncated. [...] Concerning the return value of snprintf(), the SUSv2 and the C99 stan- dard contradict each other: when snprintf() is called with size=0 then SUSv2 stipulates an unspecified return value less than 1, while C99 allows str to be NULL in this case, and gives the return value (as always) as the number of characters that would have been written in case the output string has been large enough. Kurt ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] About varlena2
Qingqing Zhou [EMAIL PROTECTED] writes: To reduce size of varlen2.vl_len to int16. This has been mentioned before, but is there any show-stopper reasoning preventing us from doing that or somebody has been working on it? Sorry, just to repeat myself. Char types will benefit from that. I have considerably less than zero interest in creating variant char types with an int16 header. The proposal that was on the table was to use this for numeric and inet types, where it could be done without introducing any user-visible semantics changes. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Tom Lane wrote:
I'm coming around to thinking that the simple solution is just to use it
unconditionally on Windows.
I agree that that's what we should do, but it should be done the same
way we handle other routines from libpgport. None of those are exported
to our client-side programs via libpq.
OK, eyeball this for the REL8_1_STABLE branch, please. It seems to work
for me. No exports necessary.
cheers
andrew
? autom4te.cache
Index: configure
===
RCS file: /cvsroot/pgsql/configure,v
retrieving revision 1.461
diff -c -r1.461 configure
*** configure 5 Nov 2005 04:01:38 - 1.461
--- configure 5 Dec 2005 22:22:11 -
***
*** 13851,13858
# is missing. Yes, there are machines that have only one. We may
# also decide to use snprintf.c if snprintf() is present but does not
# have all the features we need --- see below.
! pgac_need_repl_snprintf=no
for ac_func in snprintf
do
--- 13851,13861
# is missing. Yes, there are machines that have only one. We may
# also decide to use snprintf.c if snprintf() is present but does not
# have all the features we need --- see below.
+ # Win32 gets this built unconditionally
! if test $PORTNAME = win32; then
! pgac_need_repl_snprintf=yes
! else
for ac_func in snprintf
do
***
*** 14061,14066
--- 14064,14070
fi
done
+ fi
# Check whether stdio.h declares snprintf() and vsnprintf(); if not,
***
*** 17111,17123
# Force use of our snprintf if system's doesn't do arg control
# This feature is used by NLS
! if test $enable_nls = yes
!test $pgac_need_repl_snprintf = no
! # On Win32, libintl replaces snprintf() with its own version that
! # understands arg control, so we don't need our own. In fact, it
! # also uses macros that conflict with ours, so we _can't_ use
! # our own.
!test $PORTNAME != win32; then
echo $as_me:$LINENO: checking whether printf supports argument control 5
echo $ECHO_N checking whether printf supports argument control... $ECHO_C 6
if test ${pgac_cv_printf_arg_control+set} = set; then
--- 17115,17121
# Force use of our snprintf if system's doesn't do arg control
# This feature is used by NLS
! if test $enable_nls = yes test $pgac_need_repl_snprintf = no ; then
echo $as_me:$LINENO: checking whether printf supports argument control 5
echo $ECHO_N checking whether printf supports argument control... $ECHO_C 6
if test ${pgac_cv_printf_arg_control+set} = set; then
Index: configure.in
===
RCS file: /cvsroot/pgsql/configure.in,v
retrieving revision 1.431
diff -c -r1.431 configure.in
*** configure.in 5 Nov 2005 04:01:41 - 1.431
--- configure.in 5 Dec 2005 22:22:12 -
***
*** 849,858
# is missing. Yes, there are machines that have only one. We may
# also decide to use snprintf.c if snprintf() is present but does not
# have all the features we need --- see below.
! pgac_need_repl_snprintf=no
! AC_CHECK_FUNCS(snprintf, [], pgac_need_repl_snprintf=yes)
! AC_CHECK_FUNCS(vsnprintf, [], pgac_need_repl_snprintf=yes)
# Check whether stdio.h declares snprintf() and vsnprintf(); if not,
--- 849,862
# is missing. Yes, there are machines that have only one. We may
# also decide to use snprintf.c if snprintf() is present but does not
# have all the features we need --- see below.
+ # Win32 gets this built unconditionally
! if test $PORTNAME = win32; then
! pgac_need_repl_snprintf=yes
! else
! AC_CHECK_FUNCS(snprintf, [], pgac_need_repl_snprintf=yes)
! AC_CHECK_FUNCS(vsnprintf, [], pgac_need_repl_snprintf=yes)
! fi
# Check whether stdio.h declares snprintf() and vsnprintf(); if not,
***
*** 1046,1058
# Force use of our snprintf if system's doesn't do arg control
# This feature is used by NLS
! if test $enable_nls = yes
!test $pgac_need_repl_snprintf = no
! # On Win32, libintl replaces snprintf() with its own version that
! # understands arg control, so we don't need our own. In fact, it
! # also uses macros that conflict with ours, so we _can't_ use
! # our own.
!test $PORTNAME != win32; then
PGAC_FUNC_PRINTF_ARG_CONTROL
if test $pgac_cv_printf_arg_control != yes ; then
pgac_need_repl_snprintf=yes
--- 1050,1056
# Force use of our snprintf if system's doesn't do arg control
# This feature is used by NLS
! if test $enable_nls = yes test $pgac_need_repl_snprintf = no ; then
PGAC_FUNC_PRINTF_ARG_CONTROL
if test $pgac_cv_printf_arg_control != yes ; then
pgac_need_repl_snprintf=yes
Index: src/include/c.h
===
RCS file: /cvsroot/pgsql/src/include/c.h,v
retrieving revision 1.190
diff -c -r1.190 c.h
*** src/include/c.h 15 Oct 2005 02:49:41 - 1.190
--- src/include/c.h 5
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Andrew Dunstan wrote:
Tom Lane wrote:
I'm coming around to thinking that the simple solution is just to
use it unconditionally on Windows.
I agree that that's what we should do, but it should be done the same
way we handle other routines from libpgport. None of those are exported
to our client-side programs via libpq.
OK, eyeball this for the REL8_1_STABLE branch, please. It seems to
work for me. No exports necessary.
er try this instead. I missed a line from configure.in
cheers
andrew
? autom4te.cache
Index: configure
===
RCS file: /cvsroot/pgsql/configure,v
retrieving revision 1.461
diff -c -r1.461 configure
*** configure 5 Nov 2005 04:01:38 - 1.461
--- configure 5 Dec 2005 22:39:43 -
***
*** 13851,13858
# is missing. Yes, there are machines that have only one. We may
# also decide to use snprintf.c if snprintf() is present but does not
# have all the features we need --- see below.
! pgac_need_repl_snprintf=no
for ac_func in snprintf
do
--- 13851,13862
# is missing. Yes, there are machines that have only one. We may
# also decide to use snprintf.c if snprintf() is present but does not
# have all the features we need --- see below.
+ # Win32 gets this built unconditionally
! if test $PORTNAME = win32; then
! pgac_need_repl_snprintf=yes
! else
! pgac_need_repl_snprintf=no
for ac_func in snprintf
do
***
*** 14061,14066
--- 14065,14071
fi
done
+ fi
# Check whether stdio.h declares snprintf() and vsnprintf(); if not,
***
*** 17111,17123
# Force use of our snprintf if system's doesn't do arg control
# This feature is used by NLS
! if test $enable_nls = yes
!test $pgac_need_repl_snprintf = no
! # On Win32, libintl replaces snprintf() with its own version that
! # understands arg control, so we don't need our own. In fact, it
! # also uses macros that conflict with ours, so we _can't_ use
! # our own.
!test $PORTNAME != win32; then
echo $as_me:$LINENO: checking whether printf supports argument control 5
echo $ECHO_N checking whether printf supports argument control... $ECHO_C 6
if test ${pgac_cv_printf_arg_control+set} = set; then
--- 17116,17122
# Force use of our snprintf if system's doesn't do arg control
# This feature is used by NLS
! if test $enable_nls = yes test $pgac_need_repl_snprintf = no ; then
echo $as_me:$LINENO: checking whether printf supports argument control 5
echo $ECHO_N checking whether printf supports argument control... $ECHO_C 6
if test ${pgac_cv_printf_arg_control+set} = set; then
Index: configure.in
===
RCS file: /cvsroot/pgsql/configure.in,v
retrieving revision 1.431
diff -c -r1.431 configure.in
*** configure.in 5 Nov 2005 04:01:41 - 1.431
--- configure.in 5 Dec 2005 22:39:44 -
***
*** 849,858
# is missing. Yes, there are machines that have only one. We may
# also decide to use snprintf.c if snprintf() is present but does not
# have all the features we need --- see below.
! pgac_need_repl_snprintf=no
! AC_CHECK_FUNCS(snprintf, [], pgac_need_repl_snprintf=yes)
! AC_CHECK_FUNCS(vsnprintf, [], pgac_need_repl_snprintf=yes)
# Check whether stdio.h declares snprintf() and vsnprintf(); if not,
--- 849,863
# is missing. Yes, there are machines that have only one. We may
# also decide to use snprintf.c if snprintf() is present but does not
# have all the features we need --- see below.
+ # Win32 gets this built unconditionally
! if test $PORTNAME = win32; then
! pgac_need_repl_snprintf=yes
! else
! pgac_need_repl_snprintf=no
! AC_CHECK_FUNCS(snprintf, [], pgac_need_repl_snprintf=yes)
! AC_CHECK_FUNCS(vsnprintf, [], pgac_need_repl_snprintf=yes)
! fi
# Check whether stdio.h declares snprintf() and vsnprintf(); if not,
***
*** 1046,1058
# Force use of our snprintf if system's doesn't do arg control
# This feature is used by NLS
! if test $enable_nls = yes
!test $pgac_need_repl_snprintf = no
! # On Win32, libintl replaces snprintf() with its own version that
! # understands arg control, so we don't need our own. In fact, it
! # also uses macros that conflict with ours, so we _can't_ use
! # our own.
!test $PORTNAME != win32; then
PGAC_FUNC_PRINTF_ARG_CONTROL
if test $pgac_cv_printf_arg_control != yes ; then
pgac_need_repl_snprintf=yes
--- 1051,1057
# Force use of our snprintf if system's doesn't do arg control
# This feature is used by NLS
! if test $enable_nls = yes test $pgac_need_repl_snprintf = no ; then
PGAC_FUNC_PRINTF_ARG_CONTROL
if test $pgac_cv_printf_arg_control != yes ; then
pgac_need_repl_snprintf=yes
Index: src/include/c.h
===
RCS file:
Re: [HACKERS] About varlena2
Qingqing Zhou [EMAIL PROTECTED] wrote:
To reduce size of varlen2.vl_len to int16. This has been mentioned before,
but is there any show-stopper reasoning preventing us from doing that or
somebody has been working on it?
Hi, I'm rewriting the patch that I proposed before.
(http://archives.postgresql.org/pgsql-hackers/2005-09/msg00421.php)
This is another way to reduce the size of variable length types,
using variable length headers.
I'm sure that there are pros and cons of this approach.
Pros.
- Optimized for short variables (length = 127),
where the header takes only one byte.
- It can represent long data.
Cons.
- More complexity and operations to extract lengths and buffers.
- Needs more works to support TOAST.
To support TOAST, I think the following representations.
It might be good to use only A and B, if TOAST is not needed.
| Representation | Size | Mode|
--++---+-+
A | 0***+ data | 1 + n | length = 127 |
B | 10** + 1 byte + data | 2 + n | length = 16K -1|
C | 110- + 4 bytes + data | 5 + n | length = 4G -1|
D | 1110 + 6 bytes + data | 7 + n | Compressed |
E | 0--- + 12 bytes| 13| External|
F | 1--- + 16 bytes| 17| External+Compressed |
('*' bits are used for length, '-' are unused.)
Comments welcome,
---
ITAGAKI Takahiro
NTT Cyber Space Laboratories
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Replication on the backend
Gustavo Tonini wrote: replication (master/slave, multi-master, etc) implemented inside postgres...I would like to know what has been make in this area. http://www.commandprompt.com/ - Master/Slave Joshua D. Drake Gustavo. P.S. Sorry for my bad English. 2005/12/5, Chris Browne [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] (Gustavo Tonini) writes: What about replication or data distribution inside the backend. This is a valid issue? I'm not sure what your question is... -- (reverse (concatenate 'string gro.gultn @ enworbbc)) http://www.ntlug.org/~cbbrowne/x.html http://www.ntlug.org/%7Ecbbrowne/x.html Love is like a snowmobile flying over the frozen tundra that suddenly flips, pinning you underneath. At night, the ice weasels come. -- Matt Groening ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Replication on the backend
replication (master/slave, multi-master, etc) implemented inside postgres...I would like to know what has been make in this area. It's not in the backend, check out things like Slony (www.slony.info) and various other commercial solutions. Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Andrew Dunstan [EMAIL PROTECTED] writes: OK, eyeball this for the REL8_1_STABLE branch, please. It seems to work for me. No exports necessary. er try this instead. I missed a line from configure.in I cleaned this up slightly and committed it into HEAD and 8.1 branches. It appears to work in that I can force pgac_need_repl_snprintf to yes on a Linux machine and get a working build. But we need to verify that things are OK on Windows, both with the old libintl that the installer is using and with current libintl. Please build and test ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Bug in pg_dump -c with casts
Hi, Playing around with this MySQL compatibility library, I noticed that pg_dump -c does not emit DROP commands for casts. Seems like a bug...? Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: OK, eyeball this for the REL8_1_STABLE branch, please. It seems to work for me. No exports necessary. er try this instead. I missed a line from configure.in I cleaned this up slightly and committed it into HEAD and 8.1 branches. It appears to work in that I can force pgac_need_repl_snprintf to yes on a Linux machine and get a working build. But we need to verify that things are OK on Windows, both with the old libintl that the installer is using and with current libintl. Please build and test ... the cleanup seems to have omitted the change I had to src/interfaces/ecpg/pgtypeslib/extern.h, which causes a build failure - see http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lorisdt=2005-12-06%2003:30:36 If we don't do this then we need to link snprintf into libpgtypes just like we do for ecpg, but it seems like overkill. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
I did some research and can report what was happening with *printf and libintl. Basically, there are two versions of libintl. Versions before 0.13 (November 2003) use the libc version of *printf to handle printing of translation strings. Version 0.13 and after provide their own *printf functions which understand %$ functionality. The 0.13 change is: - C format strings with positions, as they arise when a translator needs to reorder a sentence, are now supported on all platforms. On those few platforms (NetBSD and Woe32) for which the native printf()/fprintf()/... functions don't support such format strings, replacements are provided through libintl.h. In addition, reports in April 2003 that libintl did not compile with our custom pg *printf functions on Win32 caused us to disable pg *printf functions on Win32. The assumption was that libintl had a special *printf version to handle %$, but in fact only post-0.13 had that feature. If we had built pginstaller with a post-0.13 libintl, pginstaller would have handled %$ translation strings fine. The problem was that pginstaller was built using pre-0.13 libintl, meaning it was using the Win32 *printf, which doesn't understand %$. Added to this was that our *printf functions had a bug that made %$ not work. Aside from fixing our own *printf, we have two ways of fixing this, either use a post-0.13 version of libintl, or use the pre-0.13 libintl. Based on risk analysis, we have chosen to continue using the same pre-0.13 version of libintl, and to rely on our pg *printf functions to handle %$. We hope to put out a new pginstaller in the next few days for testing to make sure this has been resolve before releasing 8.1.1. --- Andrew Dunstan wrote: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: However, a very simple test shows that the libintl printf does indeed do %m$ processing: ... So the next question is why isn't it working in the build. Is it possible that the build that was being complained of was using our previous, very-broken snprintf.c? There's currently a config setting that is supposed to inhibit its use on Windows. I am quite confused. What is more, when I set the locale of my machine to Turkish and run the installer project's 8.1_RC1 which I happen to have installed there, and set lc_messages to tr_TR.UTF-8, I don't see lines like Nicolai reported: LOG: $s veritaban?n transaction ID warp limiti $u I see this: LOG: 2147484146 veritabanin transaction ID warp limiti postgres So I'm inclined to think there might be something odd about his setup and maybe we aren't quite so broken after all. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Reduce NUMERIC size by 2 bytes, reduce max length to 508
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: The limit seems to be around 150k digits: It's exactly 10^(128K), as I've mentioned more than once. So, with the patch, the storage length is going from 1000 digits to 508, but the computational length is reduced from around 150k digits to 508. Now, because no one has complained about the 1000-digit limit, it is unlikely that anyone is doing calculations over 1000 or the would have had problems with storing the value, Only if they declared their columns as numeric(N) and not just plain unconstrained numeric. Not to mention the possibility that they're doing the same thing you just did, ie computing values and returning them to the client without ever storing them in a table. So I don't think the above reasoning is defensible. Not only does 4000! not work, but 400! doesn't even work. I just lost demo wow factor points! It looks like the limit would be about factorial(256). The question remains, though, is this computational range good for anything except demos? I can say that the extended range is good for finding *printf problems. ;-) Let me also add that as far as saving disk space, this is the _big_ improvement on the TODO list: * Merge xmin/xmax/cmin/cmax back into three header fields Before subtransactions, there used to be only three fields needed to store these four values. This was possible because only the current transaction looks at the cmin/cmax values. If the current transaction created and expired the row the fields stored where xmin (same as xmax), cmin, cmax, and if the transaction was expiring a row from a another transaction, the fields stored were xmin (cmin was not needed), xmax, and cmax. Such a system worked because a transaction could only see rows from another completed transaction. However, subtransactions can see rows from outer transactions, and once the subtransaction completes, the outer transaction continues, requiring the storage of all four fields. With subtransactions, an outer transaction can create a row, a subtransaction expire it, and when the subtransaction completes, the outer transaction still has to have proper visibility of the row's cmin, for example, for cursors. One possible solution is to create a phantom cid which represents a cmin/cmax pair and is stored in local memory. Another idea is to store both cmin and cmax only in local memory. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Andrew Dunstan [EMAIL PROTECTED] writes: Also, we need a way to stop this from happening all over the build: In file included from ../../../../../../src/include/c.h:820, from ../../../../../../src/include/postgres.h:48, from utf8_and_sjis.c:14: ../../../../../../src/include/port.h:121: warning: `libintl_printf' is an unrecognized format function type Argh, I ordered things wrong: should undef the old macros before declaring the new functions. Not sure why my build didn't show the problem in pgtypeslib, though. That should have failed with or without libintl macros. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Tom Lane wrote: Not sure why my build didn't show the problem in pgtypeslib, though. That should have failed with or without libintl macros. On *nix it probably just thinks it's an external symbol to be resolved later. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] snprintf() argument reordering not working
Andrew Dunstan [EMAIL PROTECTED] writes: If we don't do this then we need to link snprintf into libpgtypes just like we do for ecpg, but it seems like overkill. It might be overkill today, but what about tomorrow when someone decides to internationalize libpgtypes? I made it link in there too. Please test ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Replication on the backend
On 12/5/2005 8:18 PM, Gustavo Tonini wrote: replication (master/slave, multi-master, etc) implemented inside postgres...I would like to know what has been make in this area. We do not plan to implement replication inside the backend. Replication needs are so diverse that pluggable replication support makes a lot more sense. To me it even makes more sense than keeping transaction support outside of the database itself and add it via pluggable storage add-on. Jan Gustavo. P.S. Sorry for my bad English. 2005/12/5, Chris Browne [EMAIL PROTECTED]: [EMAIL PROTECTED] (Gustavo Tonini) writes: What about replication or data distribution inside the backend. This is a valid issue? I'm not sure what your question is... -- (reverse (concatenate 'string gro.gultn @ enworbbc)) http://www.ntlug.org/~cbbrowne/x.html Love is like a snowmobile flying over the frozen tundra that suddenly flips, pinning you underneath. At night, the ice weasels come. -- Matt Groening ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Reduce NUMERIC size by 2 bytes, reduce max length to 508
On Mon, Dec 05, 2005 at 11:59:10PM -0500, Bruce Momjian wrote: Tom Lane wrote: It looks like the limit would be about factorial(256). The question remains, though, is this computational range good for anything except demos? I can say that the extended range is good for finding *printf problems. ;-) Might anybody be calculating permutations or combinations with the textbook functions that use factorials? Not a show-stopper since those calculations can be optimized (at least the basic formulas I know), but somebody might get bit by the change. Maybe the release notes could mention the new upper limit of factorial(). -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Reduce NUMERIC size by 2 bytes, reduce max length to 508
Michael Fuhr [EMAIL PROTECTED] writes: On Mon, Dec 05, 2005 at 11:59:10PM -0500, Bruce Momjian wrote: Tom Lane wrote: The question remains, though, is this computational range good for anything except demos? I can say that the extended range is good for finding *printf problems. ;-) Might anybody be calculating permutations or combinations with the textbook functions that use factorials? Hm ... between that, the possible crypto connection, and John's personal testimony that he actually uses PG for calculations in this range, I'm starting to lean to the idea that we shouldn't cut the range. We could get the same 2-byte savings (in fact 3 bytes on average, considering alignment issues) by implementing a 2-byte length word format for numeric. I had originally hoped to do both things to save an average 5 bytes per numeric, which is starting to get to the point of actually being interesting ;-). But maybe we should just do the part that we can do without removing any user-visible functionality. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] inet to bigint?
OK, I give up - how do I convert an INET type to a NUMERIC representation of its network address? Is there a quick and easy way? Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] inet to bigint?
On Tue, Dec 06, 2005 at 03:31:59PM +0800, Christopher Kings-Lynne wrote:
OK, I give up - how do I convert an INET type to a NUMERIC
representation of its network address?
How about:
CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
use Socket;
return unpack(N, inet_aton($_[0]));
$$ LANGUAGE plperlu IMMUTABLE STRICT;
SELECT inet2num('127.0.0.1');
inet2num
2130706433
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] inet to bigint?
On 12/6/05, Michael Fuhr [EMAIL PROTECTED] wrote: How about:CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$use Socket;return unpack(N, inet_aton($_[0]));$$ LANGUAGE plperlu IMMUTABLE STRICT; you can use this one: ... AS $$ return unpack(N, pack(C4, split(/\./, $_[0]))); $$ language plperl IMMUTABLE STRICT; to avoid the need to use untrusted languages. it is less readable thought :( depesz
Re: [HACKERS] inet to bigint?
PL/SQL or PL/PGSQL...
Chris
Michael Fuhr wrote:
On Tue, Dec 06, 2005 at 03:31:59PM +0800, Christopher Kings-Lynne wrote:
OK, I give up - how do I convert an INET type to a NUMERIC
representation of its network address?
How about:
CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
use Socket;
return unpack(N, inet_aton($_[0]));
$$ LANGUAGE plperlu IMMUTABLE STRICT;
SELECT inet2num('127.0.0.1');
inet2num
2130706433
(1 row)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
