[HACKERS] Build problem with VS2008 Express Edition
Hi, I managed to get most of 8.3 built with VS2008EE last night. Ii had to change some references to msbuild to vsbuild, which I guess is expected but one compile issue surprised me. I had to change #ifdef IPV6_V6ONLY at backend/libpq/pqcomm.c:386 to: #if defined(IPV6_V6ONLY) && (!defined(WIN32) || (_WIN32_WINNT >= 0x0501)) because IPPROTO_IPV6 is defined in ws2ipdef.h but the IIPROTO_V6 enum isn't defined in ws2def.h unless you set the version up appropriately. James -- 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] bug in numeric_power() function
"Richard Wang" <[EMAIL PROTECTED]> writes: > I expected 0 ^ 123.3 to be 0, but it reported error as follows > postgres=# select 0 ^ 123.3; > ERROR: cannot take logarithm of zero Hmm, seems like the numeric and float8 power operators don't agree about 0^0 either... 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] bug in numeric_power() function
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED] On Behalf Of Richard Wang > Sent: Tuesday, March 11, 2008 7:38 PM > To: pgsql-hackers@postgresql.org > Subject: [HACKERS] bug in numeric_power() function > > I expected 0 ^ 123.3 to be 0, but it reported error as follows > > postgres=# select 0 ^ 123.3; > ERROR: cannot take logarithm of zero > > I find that there is a bug in numeric_power() function > the function caculates a ^ b based on the algorithm e ^ (lna * b) > as you see, ln0 is not valid It seems an obvious work-around that: if (b == 0) return 1; if (a == 0) return 0; could be inserted at the top. Aside: Having the ^ operator overloaded for exponentiation surprises me. Does it really have the right precedence for performing exponentiation? (E.g. you cannot do this in C++ because ^ will have the precedence of xor, which is wrong). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] bug in numeric_power() function
I expected 0 ^ 123.3 to be 0, but it reported error as follows postgres=# select 0 ^ 123.3; ERROR: cannot take logarithm of zero I find that there is a bug in numeric_power() function the function caculates a ^ b based on the algorithm e ^ (lna * b) as you see, ln0 is not valid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Fwd: [HACKERS]COPY issue(gsoc project)
-- Forwarded message -- From: longlong <[EMAIL PROTECTED]> Date: 2008-3-12 9:42 Subject: Re: [HACKERS]COPY issue(gsoc project) To: Neil Conway <[EMAIL PROTECTED]> the first feature has been implementet. i used to ignore the pid column with the command "copy tablename (columns) from " long time ago and i forgeted. that's my fault. Allow COPY FROM to create index entries in bulk http://archives.postgresql.org/pgsql-hackers/2008-02/msg00811.php i know pg_bulkload from the link above. i don't know what is the different between pg_bulkload and pgloader. right now i try to figure out how copy works and get more infomation form archives for prior discussions on this subject. meanwhile i'll focus on pgloader.
Re: [HACKERS] Reducing Transaction Start/End Contention
Is this still a TODO? --- Simon Riggs wrote: > On Mon, 2007-07-30 at 20:20 +0100, Simon Riggs wrote: > > > Jignesh Shah's scalability testing on Solaris has revealed further > > tuning opportunities surrounding the start and end of a transaction. > > Tuning that should be especially important since async commit is likely > > to allow much higher transaction rates than were previously possible. > > > > There is strong contention on the ProcArrayLock in Exclusive mode, with > > the top path being CommitTransaction(). This becomes clear as the number > > of connections increases, but it seems likely that the contention can be > > caused in a range of other circumstances. My thoughts on the causes of > > this contention are that the following 3 tasks contend with each other > > in the following way: > > > > CommitTransaction(): takes ProcArrayLock Exclusive > > but only needs access to one ProcArray element > > > > waits for > > > > GetSnapshotData():ProcArrayLock Shared > > ReadNewTransactionId():XidGenLock Shared > > > > which waits for > > > > GetNextTransactionId() > > takes XidGenLock Exclusive > > ExtendCLOG(): takes ClogControlLock Exclusive, WALInsertLock Exclusive > > two possible place where I/O is required > > ExtendSubtrans(): takes SubtransControlLock() > > one possible place where I/O is required > > Avoids lock on ProcArrayLock: atomically updates one ProcArray element > > > > > > or more simply: > > > > CommitTransaction() -- i.e. once per transaction > > waits for > > GetSnapshotData() -- i.e. once per SQL statement > > which waits for > > GetNextTransactionId() -- i.e. once per transaction > > > > This gives some goals for scalability improvements and some proposals. > > (1) and (2) are proposals for 8.3 tuning, the others are directions for > > further research. > > > > > > Goal: Reduce total time that GetSnapshotData() waits for > > GetNextTransactionId() > > The latest patch for lazy xid allocation reduces the number of times > GetNextTransactionId() is called by eliminating the call entirely for > read only transactions. That will reduce the number of waits and so will > for most real world cases increase the scalability of Postgres. > Right-mostly workloads will be slightly less scalable, so we should > expect our TPC-C numbers to be slightly worse than our TPC-E numbers. > > We should retest to see whether the bottleneck has been moved > sufficiently to allow us to avoid doing techniques (1), (2), (3), (5) or > (6) at all. > > > 1. Increase size of Clog-specific BLCKSZ > > Clog currently uses BLCKSZ to define the size of clog buffers. This can > > be changed to use CLOG_BLCKSZ, which would then be set to 32768. > > This will naturally increase the amount of memory allocated to the clog, > > so we need not alter CLOG_BUFFERS above 8 if we do this (as previously > > suggested, with successful results). This will also reduce the number of > > ExtendClog() calls, which will probably reduce the overall contention > > also. > > > > 2. Perform ExtendClog() as a background activity > > Background process can look at the next transactionid once each cycle > > without holding any lock. If the xid is almost at the point where a new > > clog page would be allocated, then it will allocate one prior to the new > > page being absolutely required. Doing this as a background task would > > mean that we do not need to hold the XidGenLock in exclusive mode while > > we do this, which means that GetSnapshotData() and CommitTransaction() > > would also be less likely to block. Also, if any clog writes need to be > > performed when the page is moved forwards this would also be performed > > in the background. > > > 3. Consider whether ProcArrayLock should use a new queued-shared lock > > mode that puts a maximum wait time on ExclusiveLock requests. It would > > be fairly hard to implement this well as a timer, but it might be > > possible to place a limit on queue length. i.e. allow Share locks to be > > granted immediately if a Shared holder already exists, but only if there > > is a queue of no more than N exclusive mode requests queued. This might > > prevent the worst cases of exclusive lock starvation. > > (4) is a general concern that remains valid. > > > 4. Since shared locks are currently queued behind exclusive requests > > when they cannot be immediately satisfied, it might be worth > > reconsidering the way LWLockRelease works also. When we wake up the > > queue we only wake the Shared requests that are adjacent to the head of > > the queue. Instead we could wake *all* waiting Shared requestors. > > > > e.g. with a lock queue like this: > > (HEAD) S<-S<-X<-S<-X<-S<-X<-S > > Currently we would wake the 1st and 2nd waiters only. > > > > If we were to wake the 3rd, 5th and 7th waiters also, then the queue > > would reduce in length very quickly, if we assume generally uniform > > service times. (I
Re: [HACKERS] Autovacuum vs statement_timeout
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Bruce asks: >>> Particularly consider using psql to restore a pg_dump >>> dump --- are we going to add "SET statement_timeout=0" >>> to the pg_dump file? > >> I hope not. That should be the user's choice. > Would anyone want to limit the load time for pg_dump? I > can hardly see why. Not for pg_dump, but for psql, as you stated above. I don't have a problem adding it to pg_dump or pg_restore. They are single, atomic actions out of the control of the user. Restoring a pg_dump'ed file through psql, on the other hand, should not assume that the user might not want to keep or set their own timeout, perhaps because they want to limit the load on the server, or because of vacuuming concerns. Recall that pg_dump is not just used to restore entire systems: we can dump schemas, tables, and in the near future may even have the ability to dump different classes (schema, data, constraints). Hard-coding a forced option to the top of a potentially ginormous and hard-to-edit file that really has nothing to do with the data itself seems the wrong way to do things. It's not as if we've been inundated on the lists with tales of people getting caught on custom statement_timeouts when importing dumps. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200803111959 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfXHqMACgkQvJuQZxSWSsgifQCgthvDCTiKhw/3A4S1na1mvlOB +MQAn2baL34c8k3FV+f2CUAn7GwDewrN =x24Q -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]COPY issue(gsoc project)
On Tue, 2008-03-11 at 15:18 -0700, Neil Conway wrote: > Note also that pg_bulkload currently does something analogous to this > outside of the DBMS proper: > > http://pgbulkload.projects.postgresql.org/ Sorry, wrong project. I mean pgloader: http://pgfoundry.org/projects/pgloader/ -Neil -- 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] [PATCHES] allow CSV quote in NULL
Added to TODO for COPY: o Allow COPY in CSV mode to control whether "" is treated as NULL http://archives.postgresql.org/pgsql-hackers/2007-07/msg00905.php --- Andrew Dunstan wrote: > > [redirecting to -hackers] > > Stephen Frost wrote: > > * Gregory Stark ([EMAIL PROTECTED]) wrote: > > > >> "Tom Lane" <[EMAIL PROTECTED]> writes: > >> > >> > >>> Stephen Frost <[EMAIL PROTECTED]> writes: > >>> > Please find attached a minor patch to remove the constraints that a > user can't include the delimiter or quote characters in a 'NULL AS' > string when importing CSV files. > > >>> This can't really be sane can it? > >>> > > > > Not very, no :-) > > > >> The alternative would be interpreting NULL strings after dequoting but that > >> would leave no way to include the NULL string literally. This solution > >> means > >> there's no way to include it (if it needs quoting) but only when you > >> specify > >> it this way. > >> > > > > Yeah, interpreting NULLs after dequoting means you've lost the > > information about if it's quoted or not, or you have to add some funky > > syntax to say "if it's quoted, do it differently...", which is no good, > > imv. > > > > What the patch does basically is say "give us the exact string that > > shows up between the unquoted delimiters that you want to be treated > > as a NULL." This removes the complexity of the question about quoting, > > unquoting, whatever, and makes it a very clear-cut, straight-forward > > solution with no impact on existing users, imv. > > > > > > > > This looks too clever by half, to me. Someone facing the problem you are > facing would have to dig quite deep to find the solution you're promoting. > > A much better way IMNSHO would be to add an extra FORCE switch. On > input, FORCE NOT NULL says to treat an unquoted null as the literal > value rather than as a null field for the columns named. The reverse > would be to tell it to treat a quoted null as null rather than as the > literal value, for the named columns. Perhaps that should just be "FORCE > NULL columnlist". It would be more explicit and at the same time would > only apply to the named columns, rather than discarding totally the > ability to distinguish between null and not null values. > > This should probably be discussed on -hackers, anyway. > > > > cheers > > andrew > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] plpgsql and qualified variable names
Added to pl/pgsql TODO:
o Improve logic of determining if an identifier is a a
variable or column name
http://archives.postgresql.org/pgsql-hackers/2007-07/msg00436.php
---
Tom Lane wrote:
> I have just absorbed the significance of some code that has been in
> plpgsql since day one, but has never been documented anyplace.
> It seems that if you attach a "label" to a statement block in a
> plpgsql function, you can do more with the label than just use it in
> an EXIT statement (as I'd always supposed it was for). You can also use
> the label to qualify the names of variables declared in that block.
> For example, I've extended the example in section 37.3 like this:
>
> CREATE FUNCTION somefunc() RETURNS integer AS $$
> << outerblock >>
> DECLARE
> quantity integer := 30;
> BEGIN
> RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30
> quantity := 50;
> --
> -- Create a subblock
> --
> DECLARE
> quantity integer := 80;
> BEGIN
> RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80
> RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; --
> Prints 50
> END;
>
> RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50
>
> RETURN quantity;
> END;
> $$ LANGUAGE plpgsql;
>
> Now the reason I'm interested in this is that it provides another
> technique you can use to deal with conflicts between plpgsql variable
> names and SQL table/column/function names: you can qualify the variable
> name with the block label when you use it in a SQL command. This is
> not in itself a solution to the conflict problem, because unqualified
> names are still at risk of being resolved the "wrong" way, but it still
> seems worth documenting in the new section I'm writing about variable
> substitution rules.
>
> Anyway, I'm not writing just to point out that we have a previously
> undocumented feature. I notice that the section on porting from Oracle
> PL/SQL mentions
>
> You cannot use parameter names that are the same as columns that are
> referenced in the function. Oracle allows you to do this if you qualify
> the parameter name using function_name.parameter_name.
>
> While i haven't tested yet, I believe that we could match this Oracle
> behavior with about a one-line code change: the outermost namespace
> level ("block") that the function parameter aliases are put into just
> needs to be given a label equal to the function name, instead of being
> label-less as it currently is.
>
> Comments? Also, can anyone verify whether this labeling behavior
> matches Oracle?
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
--
Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
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]COPY issue(gsoc project)
On Tue, 2008-03-11 at 20:56 +0800, longlong wrote: > This would be a nice feature. Right now there are often applications > where there is a data loading or staging table that ends up being > merged with a larger table after some cleanup. Moving that data from > the preperation area into the final table right now is most easily > done with INSERT INTO X (SELECT A,B FROM C) type actions. This is > slow because INSERT takes much longer than COPY. Why would INSERT INTO ... SELECT be any slower than COPY ... FROM SELECT? > 2.this come from TODO list: COPY always behaviors like a unit of work > thar consists of some insert commands, if any error, it rollback. but > sometimes we only care the data should be inserted. in that situation, > i used to use "trycatch" insert row by row to skip the error, > because it will take much time to examine every row. so: > Allow COPY to report error lines and continue. > this is a good idea. Search the archives for prior discussions of this idea; the implementation will require some careful thought. This is a relevant thread: http://markmail.org/message/y3atxu56s2afgidg Note also that pg_bulkload currently does something analogous to this outside of the DBMS proper: http://pgbulkload.projects.postgresql.org/ > which one should i choose to proposal or both? FWIW, error handling for COPY sounds like a more useful project to me. -Neil -- 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] Restartable signals 'n all that
Added to TODO: * Research use of signals and sleep wake ups http://archives.postgresql.org/pgsql-hackers/2007-07/msg3.php --- Tom Lane wrote: > While poking at the vacuum-launcher issue currently under discussion, > I got annoyed again at the behavior we've known for a long while that > on some platforms pg_usleep() won't be interrupted by signals. (In > particular I see this on HPUX, though not on Linux or Darwin. Anyone > know if it happens on any BSDen?) I noticed that with the launcher set > to sleep at most one second between checks for signals, it seemed to > *always* take the full second before shutting down, which seemed awfully > unlucky. > > Some more testing and man-page-reading revealed the full truth of what's > going on. The Single Unix Spec's select(2) page says under ERRORS > > [EINTR] > The select() function was interrupted before any of the selected events > occurred and before the timeout interval expired. If SA_RESTART has been > set for the interrupting signal, it is implementation-dependent whether > select() restarts or returns with [EINTR]. > > Since pqsignal() sets SA_RESTART for all trapped signals except SIGALRM, > that means we are exposing ourselves to the implementation dependency. > What I furthermore realized while tracing is that "restart" means > "start counting down the full timeout interval over again". Thus, if > we have told select() to time out after 1 second, and SIGINT arrives > after 0.9 second, we will wait a full second more before responding. > > Bad as that is, it gets worse rapidly: each new signal arrival restarts > the cycle. So a continuous flow of signals at a spacing of less than > 1 second would prevent the delay from *ever* terminating. > > This may be why some kernels reduce the timeout value before returning, > so that a "restart" behavior in userland behaves sanely. But that's > not what's happening for me :-(. > > To me, this calls into question whether we should try to avoid using > SA_RESTART at all. The reason for doing it of course is to avoid > unexpected syscall EINTR failures as well as short read/short write > behaviors during disk I/O. However, if that's the plan then what the > heck is pqsignal() doing giving an exception for SIGALRM? As soon as > you have even one non-restartable trapped signal, it seems you need > to handle EINTR everywhere. > > I looked into the CVS history and found that we inherited the SIGALRM > exception from Berkeley (in fact it's in the v4r2 sources from 1994). > Back then the system's usage of SIGALRM was pretty darn narrow --- it > was used only to trigger the deadlock checker, which means it applied > only while waiting for a lock, and the range of code in which the > interrupt could occur was just a few lines. Now that we use SIGALRM for > statement_timeout, the interrupt can potentially happen almost anywhere > in the backend code. > > So we've got two problems: SA_RESTART is preventing some EINTRs from > happening when we'd like, and yet it seems we are at risk of unwanted > EINTRs anyway. > > The only really clean solution I can see is to stop using SA_RESTART > and try to make all our syscalls EINTR-proof. But the probability > of bugs-of-omission seems just about 100%, especially in third party > backend add-ons that we don't get to review the code for. > > If we do nothing, anyone using statement_timeout is at risk. The > risk is somewhat ameliorated by the fact that occurrence of the > interrupt means transaction cancellation anyway, so an unexpected > error of some other type isn't really a fatal problem. But it's > still a bit nervous-making. I don't currently see a way to get > corrupted data from an EINTR (bufmgr is fairly robust about write > failures, for instance) but ... > > If we decide to live with that, and fix any reported problems, then > one thing we could do to ameliorate the sleep problem is to turn > off SA_RESTART for all activity-cancelling interrupts, in particular > SIGINT/SIGTERM/SIGQUIT. This wouldn't make it safe for bgwriter > and friends to go back to long sleep intervals, because they are > watching for other interrupts too that don't represent reasons to > cancel transactions. But it would at least solve the problem of > slow response to shutdown requests. > > Comments? I sure hope someone has a better idea. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-ha
Re: [HACKERS] Bgwriter LRU cleaning: we've been going at this all wrong
Added to TODO: * Consider wither increasing BM_MAX_USAGE_COUNT improves performance http://archives.postgresql.org/pgsql-hackers/2007-06/msg01007.php --- Gregory Stark wrote: > > "Tom Lane" <[EMAIL PROTECTED]> writes: > > > I don't really see why it's "overkill". > > Well I think it may be overkill in that we'll be writing out buffers that > still have a decent chance of being hit again. Effectively what we'll be doing > in the approximated LRU queue is writing out any buffer that reaches the 80% > point down the list. Even if it later gets hit and pulled up to the head > again. > > I suppose that's not wrong though, the whole idea of the clock sweep is that > that's precisely the level of precision to which it makes sense to approximate > the LRU. Ie, that any point in the top 20% is equivalent to any other and when > we use a buffer we want to promote it to somewhere "near" the head but any > point in the top 20% is good enough. Then any point in the last 20% should be > effectively "good enough" too be considered a target buffer to clean as well. > > If we find it's overkill then what we should consider doing is raising > BM_MAX_USAGE_COUNT. That's effectively tuning the percentage of the lru chain > that we decide we try to keep clean. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Autovacuum vs statement_timeout
Bruce Momjian <[EMAIL PROTECTED]> writes: > Greg Sabino Mullane wrote: >>> Particularly consider using psql to restore a pg_dump >>> dump --- are we going to add "SET statement_timeout=0" >>> to the pg_dump file? >> >> I hope not. That should be the user's choice. > Would anyone want to limit the load time for pg_dump? I can hardly see > why. I think we need to be careful to distinguish three situations: * statement_timeout during pg_dump * statement_timeout during pg_restore * statement_timeout during psql reading a pg_dump script file Only in the third case is it really trivial for the user to set an appropriate timeout value before starting the script. (You can manage it via PGOPTIONS in the other two cases, but it's not exactly easy nor well-documented.) To me, the killer reason for statement_timeout = 0 during pg_dump is that without it, routine cron-driven dumps could fail, and the user might not notice until he really really needed that dump. Ooops. In the second and third cases, you at least have an option to do it over if a timeout zaps you. But it'd still likely be unpleasant, especially if the restore already did a lot of work before getting killed. Offhand I don't see an argument in any of these scenarios why a timeout kill is ever a good idea, but I'm willing to listen. 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] Estimating geometric distributions
I wrote: > > I have a field whose distribution of frequencies of values is > > roughly geometric, rather than flat. > My problem is frequent > > over-estimation of rows when restricting by this field with > > values not known at plan time. > Is there any facility already in PostgreSQL to help me here? > > Hopefully an index type that I don't know about yet? > (Geometric distributions are similar to those found in word > count distributions). > > If not... is there any merit in this idea: > > During the analyze process, the geometric mean of sampled > rows was calculated, and if determined to be significantly > different from the arithmetic mean, stored in a new stats > column. When estimating the number of rows that will be > returned by queries of the form shown above, if there is a > geometric mean stored, use it instead of the arithmetic mean. I came up with another (much easier) means of adjusting the planners estimation of how many rows will be returned: Increase the number of distinct values in the statistics. For example: update pg_statistic set stadistinct=2691 where starelid=29323 and staattnum=2; I can then pick a number of distinct values such that the effective arithmetic mean is equal to what I calculated the geometric mean to be. Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/services/bqem.htm for details. __ -- 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] Autovacuum vs statement_timeout
Joshua D. Drake wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Tue, 11 Mar 2008 16:17:53 -0400 (EDT) > Bruce Momjian <[EMAIL PROTECTED]> wrote: > > > Greg Sabino Mullane wrote: > > > > Particularly consider using psql to restore a pg_dump > > > > dump --- are we going to add "SET statement_timeout=0" > > > > to the pg_dump file? > > > > > > I hope not. That should be the user's choice. > > > > Would anyone want to limit the load time for pg_dump? I can hardly > > see why. > > I have to agree with Bruce here. You restore a backup because your > hosed something or you are building a dev environment. These are both > boolean results that should end in TRUE :) But maybe this brings up that people just shouldn't put statement_timeout in postgresql.conf, and if they do, they deserve what they get. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Autovacuum vs statement_timeout
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 11 Mar 2008 16:17:53 -0400 (EDT) Bruce Momjian <[EMAIL PROTECTED]> wrote: > Greg Sabino Mullane wrote: > > > Particularly consider using psql to restore a pg_dump > > > dump --- are we going to add "SET statement_timeout=0" > > > to the pg_dump file? > > > > I hope not. That should be the user's choice. > > Would anyone want to limit the load time for pg_dump? I can hardly > see why. I have to agree with Bruce here. You restore a backup because your hosed something or you are building a dev environment. These are both boolean results that should end in TRUE :) Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH1uxyATb/zqfZUUQRAvyWAKCATrKgRQygjveXSrY0KIptvKYZJQCgg87z XrXncZ8d2Qyf61Wtc+OtHXg= =rBaK -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] Autovacuum vs statement_timeout
Greg Sabino Mullane wrote: > > Particularly consider using psql to restore a pg_dump > > dump --- are we going to add "SET statement_timeout=0" > > to the pg_dump file? > > I hope not. That should be the user's choice. Would anyone want to limit the load time for pg_dump? I can hardly see why. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Fractions in GUC variables
Added to TODO: o Consider normalizing fractions in postgresql.conf, perhaps using '%' http://archives.postgresql.org/pgsql-hackers/2007-06/msg00550.php --- Heikki Linnakangas wrote: > We have these GUC variables that define a fraction of something: > > #autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before > # vacuum > #autovacuum_analyze_scale_factor = 0.1# fraction of rel size before > # analyze > > #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round > #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round > > Autovacuum settings use fractions, and bgwriter settings use a > percentage. Fortunately these settings are not related so there's not > too much potential for confusion, but it seems we should have a common > way to define settings like that. > > A nice way would be that the base unit would be a fraction, like in the > autovacuum settings, but you could add a %-sign to give it as a percent, > just like you can use KB/MB etc. I'm not sure if we can do anything for > those without breaking backwards-compatibility, though. > > Any ideas? The load distributed checkpoints patch adds one more GUC > variable like. I'm inclined to follow the example of the bgwriter > settings because it's more closely related to them, though I like the > autovacuum style more. > > -- >Heikki Linnakangas >EnterpriseDB http://www.enterprisedb.com > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Command tags in create/drop scripts
Added to TODO: * Prefix command-line utilities like createuser with 'pg_' http://archives.postgresql.org/pgsql-hackers/2007-06/msg00025.php --- Alvaro Herrera wrote: > Zdenek Kotala wrote: > > Tom Lane wrote: > > >Bruce Momjian <[EMAIL PROTECTED]> writes: > > >>Is this a TODO? > > > > > >I don't think so; there is no demand from anybody but Zdenek to remove > > >those programs. Has it ever even come up before? > > Personally I found really strange to have "createuser" and "createdb" > shipped by Postgres when I started using it. I just didn't complain. > > > "pg_ctl -D init" instead of initdb command? > > Seems a reasonable thing to do too. Harder to type for developers, but > how much of a problem this is for real users? And developers can script > the thing anyway so I don't think this is a real problem. > > One idea is to move the offending binaries into libexec, and have pg_ctl > or other helpers (pg_cmd) call them; and where compatibility with the > old versions is requested, create symlinks in bindir. > > -- > Alvaro Herrerahttp://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] What is the maximum encoding-conversion growth rate, anyway?
Added to TODO: * Change memory allocation for multi-byte functions so memory is allocated inside conversion functions Currently we preallocate memory based on worst-case usage. --- Tom Lane wrote: > Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > Thinking more, it striked me that users can define arbitarily growing > > rate by using CFREATE CONVERSION. So it seems we need functionality to > > define the growing rate anyway. > > Seems to me that would be an argument for moving the palloc inside the > conversion functions, as I suggested before. > > In practice though, I find it hard to imagine a pair of encodings for > which the growth rate is more than 3x. You'd need something that > translates a single-byte character into 4 or more bytes (pretty > unlikely, especially considering we require all these encodings to be > ASCII supersets); or something that translates a 2-byte character into > more than 6 bytes. > > 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 -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Autovacuum vs statement_timeout
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Particularly consider using psql to restore a pg_dump > dump --- are we going to add "SET statement_timeout=0" > to the pg_dump file? I hope not. That should be the user's choice. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200803111607 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfW5sMACgkQvJuQZxSWSsjceACgq72VOH210agu3GNn5p4d3L0R 0KUAniYE/TfuBEZXnWEvH7dLGnHMUU04 =TfbX -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] Sorted writes in checkpoint
Added to TODO: * Consider sorting writes during checkpoint http://archives.postgresql.org/pgsql-hackers/2007-06/msg00541.php --- ITAGAKI Takahiro wrote: > Greg Smith <[EMAIL PROTECTED]> wrote: > > > On Mon, 11 Jun 2007, ITAGAKI Takahiro wrote: > > > If the kernel can treat sequential writes better than random writes, is > > > it worth sorting dirty buffers in block order per file at the start of > > > checkpoints? > > I wrote and tested the attached sorted-writes patch base on Heikki's > ldc-justwrites-1.patch. There was obvious performance win on OLTP workload. > > tests| pgbench | DBT-2 response time (avg/90%/max) > ---+-+--- > LDC only | 181 tps | 1.12 / 4.38 / 12.13 s > + BM_CHECKPOINT_NEEDED(*) | 187 tps | 0.83 / 2.68 / 9.26 s > + Sorted writes | 224 tps | 0.36 / 0.80 / 8.11 s > > (*) Don't write buffers that were dirtied after starting the checkpoint. > > machine : 2GB-ram, SCSI*4 RAID-5 > pgbench : -s400 -t4 -c10 (about 5GB of database) > DBT-2 : 60WH (about 6GB of database) > > > > I think it has the potential to improve things. There are three obvious > > and one subtle argument against it I can think of: > > > > 1) Extra complexity for something that may not help. This would need some > > good, robust benchmarking improvements to justify its use. > > Exactly. I think we need a discussion board for I/O performance issues. > Can I use Developers Wiki for this purpose? Since performance graphs and > result tables are important for the discussion, so it might be better > than mailing lists, that are text-based. > > > > 2) Block number ordering may not reflect actual order on disk. While > > true, it's got to be better correlated with it than writing at random. > > 3) The OS disk elevator should be dealing with this issue, particularly > > because it may really know the actual disk ordering. > > Yes, both are true. However, I think there is pretty high correlation > in those orderings. In addition, we should use filesystem to assure > those orderings correspond to each other. For example, pre-allocation > of files might help us, as has often been discussed. > > > > Here's the subtle thing: by writing in the same order the LRU scan occurs > > in, you are writing dirty buffers in the optimal fashion to eliminate > > client backend writes during BuferAlloc. This makes the checkpoint a > > really effective LRU clearing mechanism. Writing in block order will > > change that. > > The issue will probably go away after we have LDC, because it writes LRU > buffers during checkpoints. > > Regards, > --- > ITAGAKI Takahiro > NTT Open Source Software Center > [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Planning large IN lists
Added to TODO:
* Consider using a hash for joining to a large IN (VALUES ...) list
http://archives.postgresql.org/pgsql-hackers/2007-05/msg00450.php
---
Atul Deopujari wrote:
> Hi,
>
> Tom Lane wrote:
> > Neil Conway <[EMAIL PROTECTED]> writes:
> >> When planning queries with a large IN expression in the WHERE clause,
> >> the planner transforms the IN list into a scalar array expression. In
> >> clause_selectivity(), we estimate the selectivity of the ScalarArrayExpr
> >> by calling scalararraysel(), which in turn estimates the selectivity of
> >> *each* array element in order to determine the selectivity of the array
> >> expression as a whole.
> >
> >> This is quite inefficient when the IN list is large.
> >
> > That's the least of the problems. We really ought to convert such cases
> > into an IN (VALUES(...)) type of query, since often repeated indexscans
> > aren't the best implementation.
> >
> I thought of giving this a shot and while I was working on it, it
> occurred to me that we need to decide on a threshold value of the IN
> list size above which such transformation should take place. For small
> sizes of the IN list, scalararraysel() of IN list wins over the hash
> join involved in IN (VALUES(...)). But for larger sizes of the IN list,
> IN (VALUES(...)) comes out to be a clear winner.
> I would like to know what does the community think should be a heuristic
> value of the IN list size beyond which this transformation should take
> place.
> I was thinking of a GUC variable (or a hard coded value) which defaults
> to say 30. This is based on numbers from the following test:
>
> postgres=# create table w (w text);
> CREATE TABLE
>
> postgres=# \copy w from '/usr/share/dict/words'
>
> And run the following query with different IN list sizes
> explain analyze select * from w where w in ('one', 'two', ...);
>
> I got the following runtimes:
>
> IN list IN (VALUES(...)) IN
> size
>
> 150 ~2000 ms ~5500 ms
> 100 ~1500 ms ~4000 ms
> 80 ~1400 ms ~3000 ms
> 50 ~1400 ms ~2500 ms
> 30 ~1500 ms ~1500 ms
> 20 ~1400 ms ~1200 ms
> 10 ~1400 ms ~1200 ms
>
>
> The IN (VALUES(...)) gives an almost steady state behavior, while the IN
> runtimes deteriorate with growing list size.
>
> There would obviously be different conditions on which to base this
> value. I seek community opinion on this.
>
> --
> Atul
>
> EnterpriseDB
> www.enterprisedb.com
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
--
Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
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 for Prevent pg_dump/pg_restore from being affected by statement_timeout
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 11 Mar 2008 10:46:23 -0700 "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: And the -c version :) (thanks bruce) Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH1spfATb/zqfZUUQRAv2EAJ92/8EBxBbqLDlOX5wUYdN3ElG5OQCghZ2Z tUIrN/MYVgP6rc4QXONDrFg= =2oJ5 -END PGP SIGNATURE- ? pg_dump_restore.diff Index: pg_backup_archiver.c === RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v retrieving revision 1.152 diff -c -r1.152 pg_backup_archiver.c *** pg_backup_archiver.c 14 Jan 2008 19:27:41 - 1.152 --- pg_backup_archiver.c 11 Mar 2008 18:05:55 - *** *** 218,224 else ahprintf(AH, "BEGIN;\n\n"); } ! /* * Establish important parameter values right away. */ --- 218,229 else ahprintf(AH, "BEGIN;\n\n"); } ! ! /* ! * Disable statement_timeout in archive for pg_restore/psql ! */ ! ahprintf(AH, "SET statement_timeout = 0;\n"); ! /* * Establish important parameter values right away. */ Index: pg_dump.c === RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v retrieving revision 1.482 diff -c -r1.482 pg_dump.c *** pg_dump.c 30 Jan 2008 18:35:55 - 1.482 --- pg_dump.c 11 Mar 2008 18:05:58 - *** *** 566,578 if (g_fout->remoteVersion >= 80300) do_sql_command(g_conn, "SET synchronize_seqscans TO off"); /* * Start serializable transaction to dump consistent data. */ do_sql_command(g_conn, "BEGIN"); do_sql_command(g_conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"); ! /* Select the appropriate subquery to convert user IDs to names */ if (g_fout->remoteVersion >= 80100) username_subquery = "SELECT rolname FROM pg_catalog.pg_roles WHERE oid ="; --- 566,583 if (g_fout->remoteVersion >= 80300) do_sql_command(g_conn, "SET synchronize_seqscans TO off"); + /* + * Set statement timeout to zero. + */ + do_sql_command(g_conn, "SET statement_timeout = 0"); + /* * Start serializable transaction to dump consistent data. */ do_sql_command(g_conn, "BEGIN"); do_sql_command(g_conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"); ! /* Select the appropriate subquery to convert user IDs to names */ if (g_fout->remoteVersion >= 80100) username_subquery = "SELECT rolname FROM pg_catalog.pg_roles WHERE oid ="; -- 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] [PATCHES] Fix for large file support (nonsegment mode support)
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Zdenek Kotala wrote: >> Maybe we can put additional test into configure and collect appropriate >> data from buildfarm. > It might be good to just check first if it returns realistic values for the > example cases that have been mentioned. Yeah, please just make up a ten-line C program that prints the numbers you want, and post it on -hackers for people to try. If manual testing says that it's printing useful numbers, then it would be time enough to think about how to get it into the buildfarm. 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] simple plpgsql / sql function question
Thanks for the info. I will make sure to direct these types of questions to the 'general' list in the future. Thanks again for your help!!! Merlin Moncure wrote: On Tue, Mar 11, 2008 at 1:26 PM, Joshua <[EMAIL PROTECTED]> wrote: Hello, I have a series of SQL Update statements. I would like to write a function which runs all of the SQL Update statements one at a time, in order from top to bottom. Can somebody share the basic syntax that I would need to write the aforementioned function? please ask future questions of this nature on the general list. the hackers list is reserved for things pertaining to postgresql development. anywyays, the answer is easy: sql (imo preferred for this case): create function do_stuff returns void as $$ update foo set bar = 1 where foo_id = 2; [repeat as necessary] $$ language sql; plpgsql: create function do_stuff returns void as $$ begin update foo set bar = 1 where foo_id = 2; [repeat as necessary] end; $$ language plpgsql; merlin -- 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] simple plpgsql / sql function question
On Tue, Mar 11, 2008 at 1:53 PM, Merlin Moncure <[EMAIL PROTECTED]> wrote: > > I have a series of SQL Update statements. I would like to write a > > function which runs all of the SQL Update statements one at a time, in > > order from top to bottom. Can somebody share the basic syntax that I > > would need to write the aforementioned function? > > please ask future questions of this nature on the general list. the > hackers list is reserved for things pertaining to postgresql > development. > > anywyays [sic], the answer is easy: > sql (imo preferred for this case): left off parens > create function do_stuff returns void as create function do_stuff() returns void as merlin -- 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] simple plpgsql / sql function question
On Tue, Mar 11, 2008 at 1:26 PM, Joshua <[EMAIL PROTECTED]> wrote: > Hello, > > I have a series of SQL Update statements. I would like to write a > function which runs all of the SQL Update statements one at a time, in > order from top to bottom. Can somebody share the basic syntax that I > would need to write the aforementioned function? please ask future questions of this nature on the general list. the hackers list is reserved for things pertaining to postgresql development. anywyays, the answer is easy: sql (imo preferred for this case): create function do_stuff returns void as $$ update foo set bar = 1 where foo_id = 2; [repeat as necessary] $$ language sql; plpgsql: create function do_stuff returns void as $$ begin update foo set bar = 1 where foo_id = 2; [repeat as necessary] end; $$ language plpgsql; merlin -- 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 for Prevent pg_dump/pg_restore from being affected by statement_timeout
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, O.k. this appeared easy enough for even me to do it. So I did. It seems to work but I wasn't 100% positive on "where" I put the code changes. Please take a look. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH1sVyATb/zqfZUUQRAkO/AJ4jncdM3NbbwXCVngitkadxxTAGawCeMBeZ Lnr2zCdV1WLijQl+dE5yUgU= =Qu8m -END PGP SIGNATURE- ? pg_dump_restore.diff Index: pg_backup_archiver.c === RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v retrieving revision 1.152 diff -r1.152 pg_backup_archiver.c 221c221,226 < --- > > /* > * Disable statement_timeout in archive for pg_restore/psql > */ > ahprintf(AH, "SET statement_timeout = 0;\n"); > Index: pg_dump.c === RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v retrieving revision 1.482 diff -r1.482 pg_dump.c 568a569,573 > /* > * Set statement timeout to zero. > */ > do_sql_command(g_conn, "SET statement_timeout = 0"); > 575c580 < --- > -- 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] temporal variants of generate_series()
Added to TODO:
* Add temporal versions of generate_series()
http://archives.postgresql.org/pgsql-hackers/2007-04/msg01180.php
---
Jim Nasby wrote:
> On May 6, 2007, at 8:07 PM, Tom Lane wrote:
> > Jim Nasby <[EMAIL PROTECTED]> writes:
> >> Also, what would be the appropriate way to put this into initdb?
> > You seem to have missed a step here, which is to convince people that
> > these belong in core at all. So far I've not even seen an argument
> > that
> > would justify putting them in contrib.
>
> These are all examples of using generate series plus additional math
> to generate a series of dates/timestamps:
> http://archives.postgresql.org/pgsql-general/2007-01/msg01292.php
> http://archives.postgresql.org/pgsql-sql/2006-02/msg00249.php
> http://archives.postgresql.org/pgsql-general/2005-06/msg01254.php
> http://archives.postgresql.org/pgsql-sql/2007-03/msg00093.php
> http://archives.postgresql.org/pgsql-novice/2007-01/msg2.php
> http://archives.postgresql.org/pgsql-sql/2006-03/msg00391.php
> http://archives.postgresql.org/pgsql-hackers/2006-09/msg00330.php
>
> That's from the first page of search results for 'generate_series
> timestamp'.
>
> FWIW, I could also make use of this in some of my code.
>
> > If they *were* of sufficiently
> > wide use to justify putting them into core, a more efficient
> > implementation would probably be expected.
>
> Ok, I'll look into a C version, but why do SQL functions have such a
> high overhead? I'm seeing an SQL function taking ~2.6x longer than
> the equivalent code run directly in a query. With 100 days, the
> difference drops a bit to ~2.4x. (this is on HEAD from a few months ago)
>
> This is on my MacBook Pro with the Jean-Pierre's version of
> generate_series:
>
> decibel=# select count(*) from generate_series(now(),now()+'10
> days'::interval,'1'::interval);
> Time: 1851.407 ms
> decibel=# select count(*) from generate_series(1,86400*10);
> Time: 657.894 ms
> decibel=# select count(*) from (select now() + (generate_series
> (1,86400*10) * '1 second'::interval)) a;
> Time: 733.592 ms
> decibel=# select count(*) from (select 'epoch'::timestamptz + s.i *
> '1 second'::interval AS "generate_series" from generate_series(extract
> ('epoch' from now())::bigint, extract('epoch' from now()+'10
> days'::interval)::bigint, extract('epoch' from
> '1'::interval)::bigint) s(i)) a;
> Time: 699.606 ms
> --
> Jim Nasby[EMAIL PROTECTED]
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
--
Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
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] [PATCHES] Fix for large file support (nonsegment mode support)
Zdenek Kotala wrote: > Yes, agree. It seems only ZFS is OK at this moment and if somebody sets > 32TB he gets nonsegment mode anyway. Surely if you set the segment size to INT64_MAX, you will get nonsegmented behavior anyway, so two code paths might not be necessary at all. > I looked into posix standard and > there is useful function which can be used. See > > http://www.opengroup.org/onlinepubs/009695399/functions/pathconf.html > > Maybe we can put additional test into configure and collect appropriate > data from buildfarm. It might be good to just check first if it returns realistic values for the example cases that have been mentioned. -- 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] Re: [COMMITTERS] pgsql: Add: > o Add SQLSTATE severity to PGconn return status > >
On Tue, 11 Mar 2008, Magnus Hagander wrote: How about we move it to the wiki. AFAIK we can still lock it down to who can edit it if we want to You should confirm you can get the editing granularity you want before making too many plans here if this is important. The features for locking down things in Mediawiki are very limited. The Wiki philosophy here is that you could revert quite a few bad changes in the time it would take you to lock it down so those bad changes could never happen in the first place. Last time I checked vandalism and bad edits were not a problem on the developer's wiki. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] simple plpgsql / sql function question
Hello, I have a series of SQL Update statements. I would like to write a function which runs all of the SQL Update statements one at a time, in order from top to bottom. Can somebody share the basic syntax that I would need to write the aforementioned function? Please let me know. 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] [PATCHES] Fix for large file support (nonsegment mode support)
Zdenek Kotala <[EMAIL PROTECTED]> writes: > I think current patch could stay in CVS and I will rip out non segment > code path in a new patch. Sure, I feel no need to revert what's applied. Have at 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] Autovacuum vs statement_timeout
Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > Alvaro Herrera wrote:
> >> Hmm, AFAIR subsequent investigation led to the discovery that autovacuum
> >> is not affected by statement_timeout.
>
> > Right -- see
> > http://thread.gmane.org/gmane.comp.db.postgresql.devel.general/80044/focus=93847
>
> Or even more to the point, look into autovacuum.c:
>
> /*
>* Force statement_timeout to zero to avoid a timeout setting from
>* preventing regular maintenance from being executed.
>*/
> SetConfigOption("statement_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
>
> > So your documentation changes are incorrect.
>
> Indeed. But wasn't the start of this thread a mention that pg_dump
> ought to have a similar defense? AFAIR it does not, so there's still
> a small TODO.
OK, I have added a documentation mention but removed the mention of
autovacuum. I have also added this TODO:
o Prevent pg_dump/pg_restore from being affected by
statement_timeout
Using psql to restore a pg_dump dump is also affected.
Particularly consider using psql to restore a pg_dump dump --- are we
going to add "SET statement_timeout=0" to the pg_dump file?
--
Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/config.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.170
diff -c -c -r1.170 config.sgml
*** doc/src/sgml/config.sgml 10 Mar 2008 12:55:13 - 1.170
--- doc/src/sgml/config.sgml 11 Mar 2008 15:38:41 -
***
*** 3877,3882
--- 3877,3888
logged. A value of zero (the default) turns off the
limitation.
+
+
+ Setting statement_timeout in
+ postgresql.conf is not recommended because it
+ affects all sessions.
+
--
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] [PATCHES] Fix for large file support (nonsegment mode support)
Tom Lane napsal(a): Zdenek Kotala <[EMAIL PROTECTED]> writes: Tom Lane napsal(a): These examples suggest that maybe what we want is not so much a "no segments ever" mode as a segment size larger than 1GB. PS: ZFS is happy with 2^64bit size and UFS has 1TB file size limit (depends on solaris version) So even on Solaris, "no segments ever" is actually a pretty awful idea. As it stands, the code would fail on tables > 1TB. I'm thinking we need to reconsider this patch. Rather than disabling segmentation altogether, we should see it as allowing use of segments larger than 1GB. I suggest that we ought to just flat rip out the "non segmenting" code paths in md.c, and instead look into what segment sizes are appropriate on different platforms. Yes, agree. It seems only ZFS is OK at this moment and if somebody sets 32TB he gets nonsegment mode anyway. I looked into posix standard and there is useful function which can be used. See http://www.opengroup.org/onlinepubs/009695399/functions/pathconf.html Maybe we can put additional test into configure and collect appropriate data from buildfarm. I think current patch could stay in CVS and I will rip out non segment code path in a new patch. Zdenek -- 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] [NOVICE] encoding problems
Cliff Nieuwenhuis <[EMAIL PROTECTED]> writes: > I'm not sure how to ask this question. I have written a function, and with > PostgreSQL 8.0.13 I can do a "\df+" and see something like this under Source > Code: > DECLARE > result text; > ... > If I create the same function on my computer running PostgreSQL 8.3.0 and try > the \df+ then the Source Code shows: > \x09DECLARE > \x09\x09result text; > ... That's not an encoding problem, that's an intentional behavioral change in the way that psql formats strings for display. I guess it's a bit annoying if you were hoping that tabs would be useful for pretty-printing purposes. Should we reconsider what's done with a tab in mbprint.c? 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] [PATCHES] Fix for large file support (nonsegment mode support)
Tom Lane wrote: > Zdenek Kotala <[EMAIL PROTECTED]> writes: > > Tom Lane napsal(a): > >> These examples suggest that maybe what we want is not so much a "no > >> segments ever" mode as a segment size larger than 1GB. > > > PS: ZFS is happy with 2^64bit size and UFS has 1TB file size limit > > (depends on solaris version) > > So even on Solaris, "no segments ever" is actually a pretty awful idea. > As it stands, the code would fail on tables > 1TB. > > I'm thinking we need to reconsider this patch. Rather than disabling > segmentation altogether, we should see it as allowing use of segments > larger than 1GB. I suggest that we ought to just flat rip out the "non > segmenting" code paths in md.c, and instead look into what segment sizes > are appropriate on different platforms. Agreed. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] [PATCHES] Fix for large file support (nonsegment mode support)
Zdenek Kotala <[EMAIL PROTECTED]> writes: > Tom Lane napsal(a): >> These examples suggest that maybe what we want is not so much a "no >> segments ever" mode as a segment size larger than 1GB. > PS: ZFS is happy with 2^64bit size and UFS has 1TB file size limit > (depends on solaris version) So even on Solaris, "no segments ever" is actually a pretty awful idea. As it stands, the code would fail on tables > 1TB. I'm thinking we need to reconsider this patch. Rather than disabling segmentation altogether, we should see it as allowing use of segments larger than 1GB. I suggest that we ought to just flat rip out the "non segmenting" code paths in md.c, and instead look into what segment sizes are appropriate on different platforms. 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] Autovacuum vs statement_timeout
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Alvaro Herrera wrote:
>> Hmm, AFAIR subsequent investigation led to the discovery that autovacuum
>> is not affected by statement_timeout.
> Right -- see
> http://thread.gmane.org/gmane.comp.db.postgresql.devel.general/80044/focus=93847
Or even more to the point, look into autovacuum.c:
/*
* Force statement_timeout to zero to avoid a timeout setting from
* preventing regular maintenance from being executed.
*/
SetConfigOption("statement_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
> So your documentation changes are incorrect.
Indeed. But wasn't the start of this thread a mention that pg_dump
ought to have a similar defense? AFAIR it does not, so there's still
a small TODO.
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] [PATCHES] Fix for large file support (nonsegment mode support)
Tom Lane napsal(a): "Zeugswetter Andreas OSB SD" <[EMAIL PROTECTED]> writes: Exactly, e.g. AIX is one of those. jfs (not the newer jfs2) has an option to enable large files, which is not the default and cannot be changed post crfs. And even if it is enabled, jfs has a 64 Gb filesize limit ! Anybody know others that support large but not huge files ? Yeah, HPUX 10 is similar --- 128GB hard maximum. It does say you can convert an existing filesystem to largefile support, but it has to be unmounted. These examples suggest that maybe what we want is not so much a "no segments ever" mode as a segment size larger than 1GB. Patch allows to use bigger than 2/4GB segment files and it is possible changed it in source file. But how it was mentioned in this thread maybe somethink like this "CREATE TABLESPACE name LOCATION '/my/location' SEGMENTS 10GB" should good solution. If segments is not mentioned then default value is used. Zdenek PS: ZFS is happy with 2^64bit size and UFS has 1TB file size limit (depends on solaris version) -- 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] Autovacuum vs statement_timeout
Alvaro Herrera wrote: > Bruce Momjian wrote: > > > > I have added a TODO: > > > > > o Set up autovacuum to ignore statement_timeout set in > > > postgresql.conf > > > > > > http://archives.postgresql.org/pgsql-hackers/2007-03/msg01753.php > > > > and documented this behavior with the attached patch; backpatched to 8.3.X. > > Hmm, AFAIR subsequent investigation led to the discovery that autovacuum > is not affected by statement_timeout. Oh, very good. I will remove the TODO and doc item then. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Autovacuum vs statement_timeout
Alvaro Herrera wrote: > Hmm, AFAIR subsequent investigation led to the discovery that autovacuum > is not affected by statement_timeout. Right -- see http://thread.gmane.org/gmane.comp.db.postgresql.devel.general/80044/focus=93847 So your documentation changes are incorrect. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 48' 55.3", W 73º 15' 24.7" "You knock on that door or the sun will be shining on places inside you that the sun doesn't usually shine" (en Death: "The High Cost of Living") -- 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] Autovacuum vs statement_timeout
Bruce Momjian wrote: > > I have added a TODO: > > > o Set up autovacuum to ignore statement_timeout set in > > postgresql.conf > > > > http://archives.postgresql.org/pgsql-hackers/2007-03/msg01753.php > > and documented this behavior with the attached patch; backpatched to 8.3.X. Hmm, AFAIR subsequent investigation led to the discovery that autovacuum is not affected by statement_timeout. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Background LRU Writer/free list
Added to TODO: * Consider adding buffers the BGW finds reusable to the free list http://archives.postgresql.org/pgsql-hackers/2007-04/msg00781.php * Automatically tune bgwriter_delay based on activity rather then using a fixed interval http://archives.postgresql.org/pgsql-hackers/2007-04/msg00781.php --- Greg Smith wrote: > I'm mostly done with my review of the "Automatic adjustment of > bgwriter_lru_maxpages" patch. In addition to issues already brought up > with that code, there are some small things that need to be done to merge > it with the recent pg_stat_bgwriter patch, and I have some concerns about > its unbounded scanning of the buffer pool; I'll write that up in more > detail or just submit an improved patch as I get time this week. > > But there's a fundamental question that has been bugging me, and I think > it impacts the direction that code should take. Unless I'm missing > something in my reading, buffers written out by the LRU writer aren't ever > put onto the free list. I assume this is to stop from prematurely > removing buffers that contain useful data. In cases where a substantial > percentage of the buffer cache is dirty, the LRU writer has to scan a > significant portion of the pool looking for one of the rare clean buffers, > then write it out. When a client goes to grab a free buffer afterward, it > has to scan the same section of the pool to find the now clean buffer, > which seems redundant. > > With the new patch, the LRU writer is fairly well bounded in that it > doesn't write out more than it thinks it will need; you shouldn't get into > a situation where many more pages are written than will be used in the > near future. Given that mindset, shouldn't pages the LRU scan writes just > get moved onto the free list? > > -- > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Autovacuum vs statement_timeout
I have added a TODO: > o Set up autovacuum to ignore statement_timeout set in > postgresql.conf > > http://archives.postgresql.org/pgsql-hackers/2007-03/msg01753.php and documented this behavior with the attached patch; backpatched to 8.3.X. --- Magnus Hagander wrote: > On Tue, Apr 17, 2007 at 10:33:21PM -0400, Bruce Momjian wrote: > > Alvaro Herrera wrote: > > > I think that is too strong an assumption, which is why I'm planning to > > > back-patch the change to reset statement_timeout to 0 on autovacuum till > > > 8.0, as discussed. I think I should also backpatch the change to set > > > zero_damaged_pages as well (which is not on 8.0 AFAIR). > > > > > > It's very very easy to change things in postgresql.conf. Actually > > > knowing what you are doing (i.e. thinking on the consequences on VACUUM > > > and such) is a whole another matter. > > > > Frankly, setting statement_timeout in postgresql.conf seems so risky in > > so many ways, perhaps we just need to document that the parameter > > probably should not be set in postgresql.conf, and why. > > I'd suggest doing both. Tell people that it's dangerous (probably to the > point of a comment in the sample config file), but *also* force it in > pg_dump since you can't really expect people to read the documentation. > > //MAgnus -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/config.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.170 diff -c -c -r1.170 config.sgml *** doc/src/sgml/config.sgml 10 Mar 2008 12:55:13 - 1.170 --- doc/src/sgml/config.sgml 11 Mar 2008 15:38:41 - *** *** 3877,3882 --- 3877,3888 logged. A value of zero (the default) turns off the limitation. + + + Setting statement_timeout in + postgresql.conf is not recommended because it + affects all sessions, including autovacuum. + -- 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 vs. two-phase commit
Tom Lane wrote: "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: There's a small window between backend A committing and sending a NOTIFY, and the time client B receives the notification from backend B through the connection and reacts to it. Sorry, I was unclear: the case that's of interest is telling self-notifies apart from others. For this purpose, your own backend's PID *is* sufficiently stable, because you're still connected to it when the notify is sent to you. Oh, I see. Yes, that's true. This is all very hand-wavy of course, as we don't know of any real application that uses LISTEN/NOTIFY with 2PC... Yeah. I'm inclined to leave that alone (but document it) until/unless someone complains. Without a real use-case to look at, it's a bit hard to be sure what's a useful behavior. Yep. -- 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 vs. two-phase commit
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: >>> To be honest, I didn't realize the receiver gets to know the PID of the >>> sending process, but clearly it does. It seems mostly indifferent to me; >>> it's not guaranteed that the PID is valid by the time the client >>> application sees it anyway. >> >> Well, with the current definition it is; but that seems like a point >> against trying to send the original PID. > There's a small window between backend A committing and sending a > NOTIFY, and the time client B receives the notification from backend B > through the connection and reacts to it. Sorry, I was unclear: the case that's of interest is telling self-notifies apart from others. For this purpose, your own backend's PID *is* sufficiently stable, because you're still connected to it when the notify is sent to you. > This is all very hand-wavy of course, as we don't know of any real > application that uses LISTEN/NOTIFY with 2PC... Yeah. I'm inclined to leave that alone (but document it) until/unless someone complains. Without a real use-case to look at, it's a bit hard to be sure what's a useful behavior. 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] Re: [COMMITTERS] pgsql: Add: > o Add SQLSTATE severity to PGconn return status > >
On Fri, Mar 07, 2008 at 04:55:17PM -0500, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Alvaro Herrera wrote: > >> Why do we keep the TODO file with the source code? Wouldn't it make > >> more sense to store it separately? > > > No idea --- it has always been there because it relates directly to the > > source. > > I kinda like the fact that the diffs get posted to pgsql-committers; > it provides an easy chance to complain if the TODO description is off > base, which isn't too unusual. I personally find that quite annoying :-) For each commit we get both the diff posted to committers, and the reply from Bruce saying "Added to TODO". Both of which also contani the actual text... Ok, you're going to be s surprised to hear this from me... But... How about we move it to the wiki. AFAIK we can still lock it down to who can edit it if we want to (which may for the TODO certainly be a good idea). You can still get diffs. I think you can get them in the mail if you want it (using watches), and I think you can also get it as an RSS feed. > What I'd like to have taken out of CVS is all the FAQs ... I find the > diff traffic on those to be noise. But others probably see that > differently. +1 (at least, I'd + more than 1 if I was allowed to) Once again, to the big surprise of a lot of people I'm sure, how about the wiki? //Magnus -- 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 vs. two-phase commit
Tom Lane wrote: "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: To be honest, I didn't realize the receiver gets to know the PID of the sending process, but clearly it does. It seems mostly indifferent to me; it's not guaranteed that the PID is valid by the time the client application sees it anyway. Well, with the current definition it is; but that seems like a point against trying to send the original PID. There's a small window between backend A committing and sending a NOTIFY, and the time client B receives the notification from backend B through the connection and reacts to it. There is one slightly interesting use case though: if the client application ignores self-notifies, it would ignore the NOTIFYs of the prepared transactions it commits, even though they originally ran in another backend. It's worth mentioning in the docs, but I would leave it as it is for now. Yeah, the original reason for sending the PID was exactly so that the client could tell self-notifies apart from remote ones. The question is, what the heck is a "self-notify" in the 2PC context? I don't know. Perhaps we should just always report -1 as the PID with 2PC? Seems like the safest option. Often you do use the same connection to send both PREPARE TRANSACTION and COMMIT PREPARED, and do nothing in-between. If you use it like that, then the 2PC is not any different from a normal commit from LISTEN/NOTIFY point of view, and we could interpret self-notify as one that came from your own backend. This is all very hand-wavy of course, as we don't know of any real application that uses LISTEN/NOTIFY with 2PC... -- 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] [PATCHES] Fix for large file support (nonsegment mode support)
"Zeugswetter Andreas OSB SD" <[EMAIL PROTECTED]> writes: > Exactly, e.g. AIX is one of those. jfs (not the newer jfs2) has an > option > to enable large files, which is not the default and cannot be changed > post crfs. > And even if it is enabled, jfs has a 64 Gb filesize limit ! > Anybody know others that support large but not huge files ? Yeah, HPUX 10 is similar --- 128GB hard maximum. It does say you can convert an existing filesystem to largefile support, but it has to be unmounted. These examples suggest that maybe what we want is not so much a "no segments ever" mode as a segment size larger than 1GB. 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] [PATCHES] Fix for large file support (nonsegment mode support)
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I think this needs to be treated as experimental until it's got a few >> more than zero miles under its belt. > OK, then maybe we should document that. Agreed, but at this point we don't even know what hazards we need to document. 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 vs. two-phase commit
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> So I'm thinking that PREPARE TRANSACTION should throw an error if any >> LISTEN or UNLISTEN is pending in the current transaction. > For back-branches, I'm a bit hesitant to do that, as there might be > applications that do LISTEN in a prepared transaction unknowingly. I think that's a bit far-fetched... >> BTW, another little issue I just noticed is that while 2PC can cope >> with NOTIFY actions, the eventual notify is sent with the PID of the >> backend that executes COMMIT PREPARED, not the one that originally >> created the prepared transaction. > To be honest, I didn't realize the receiver gets to know the PID of the > sending process, but clearly it does. It seems mostly indifferent to me; > it's not guaranteed that the PID is valid by the time the client > application sees it anyway. Well, with the current definition it is; but that seems like a point against trying to send the original PID. > There is one slightly interesting use case > though: if the client application ignores self-notifies, it would ignore > the NOTIFYs of the prepared transactions it commits, even though they > originally ran in another backend. It's worth mentioning in the docs, > but I would leave it as it is for now. Yeah, the original reason for sending the PID was exactly so that the client could tell self-notifies apart from remote ones. The question is, what the heck is a "self-notify" in the 2PC context? 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] [PATCHES] Fix for large file support (nonsegment mode support)
On Mon, 10 Mar 2008, Tom Lane wrote: Peter Eisentraut <[EMAIL PROTECTED]> writes: Tom Lane wrote: Applied with minor corrections. Why is this not the default when supported? Fear. Maybe eventually, but right now I think it's too risky. One point that I already found out the hard way is that sizeof(off_t) = 8 does not guarantee the availability of largefile support; there can also be filesystem-level constraints, and perhaps other things we know not of at this point. Just to note an additional filesystem that will need special action... The VxFS filesystem has a largefiles option, per filesystem. At least that was the case on SCO UnixWare (No, I no longer run it). LER regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: [EMAIL PROTECTED] US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 -- 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] strange pg_ctl's behavior
> > Line 52: > > #define WHITESPACE "\f\n\r\t\v" /* as defined by isspace() */ > > > > We've defined whitespace as not including a space character :-/ > > Yes, we fixed this on February 20 and the fix will appear in 8.3.1 and > all back brances once a release is made. Thanks. I should have checked the CVS tips. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [ANN] Now v8.3 based SE-PostgreSQL is available
It is an announcement of new version of SE-PostgreSQL. Now v8.3 based SE-PostgreSQL is available - SE-PostgreSQL development team published v8.3 based SE-PostgreSQL at: http://download.fedora.redhat.com/pub/fedora/linux/development You can obtain the v8.3 based sepostgresql package which contains its security policy module from here. NOTE: This version is not available at Fedora 8 or prior. Please wait for Fedora 9, or update your system to rawhide. The following official documentation will help your understanding: http://sepgsql.googlecode.com/files/sepgsql_security_guide.20080214.en.pdf Updates from v8.2 based SE-PostgreSQL - The base version was upgraded to PostgreSQL 8.3.0 - It enabled to share external libraries (like -contrib package) with original PostgreSQL. - Cumulative bugfixes. The features of SE-PostgreSQL - Security-Enhanced PostgreSQL (SE-PostgreSQL) is a security extension built in PostgreSQL, to provide system-wide consistency in access controls. It enables to apply a single unigied security policy of SELinux for both operating system and database management system. In addition, it also provides fine-grained mandatory access which includes column-/row- level non-bypassable access control even if privileged database users. These features enables to deploy SE-PostgreSQL into data flow control scheme integrated with operating system, to protect our information asset from threats like leaking, manupulation and so on. http://code.google.com/p/sepgsql/wiki/WhatIsSEPostgreSQL P.S. Now we are preparing to submit our PGACE/SE-PostgreSQL patches to join v8.4.x development cycle. Please wait for a while! Thanks, -- KaiGai Kohei <[EMAIL PROTECTED]> -- 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] strange pg_ctl's behavior
Richard Huxton wrote: > Richard Huxton wrote: > > > > Hmm - that does seem to be the case. I added a line to print "connstr" > > along with the "." > > waiting for server to startdbname=postgres port=5483 -i > > connect_timeout=5. > > > > The code looks fine to my uneducated eye though (bin/pg_ctl/pg_ctl.c > > test_postmaster_connection starting at line 412. I think the issue must > > be at lines 425..443) > > Line 52: > #define WHITESPACE "\f\n\r\t\v" /* as defined by isspace() */ > > We've defined whitespace as not including a space character :-/ Yes, we fixed this on February 20 and the fix will appear in 8.3.1 and all back brances once a release is made. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS]COPY issue(gsoc project)
i want to paticipate in gsoc 2008. here is my plan: the red part is came from Greg Smith(thanks for Greg Smith's advice). 1.release8.2 make COPY TO can copy the output of an arbitrary SELECT statement. so i think maybe COPY FROM can get data from output and 'insert into' some column that designated. the format of the command will be discussed. This would be a nice feature. Right now there are often applications where there is a data loading or staging table that ends up being merged with a larger table after some cleanup. Moving that data from the preperation area into the final table right now is most easily done with INSERT INTO X (SELECT A,B FROM C) type actions. This is slow because INSERT takes much longer than COPY. Adding support for COPY X FROM (SELECT A,B FROM C) would make this problem go away. It is possible to do this right now with some clever use of STDIN/OUT like the below, but having a pure SQL solution would be more widely applicable. The overhead of having to pass everything through the client (as STDIN/OUT do) is certainly not zero. 2.this come from TODO list: COPY always behaviors like a unit of work thar consists of some insert commands, if any error, it rollback. but sometimes we only care the data should be inserted. in that situation, i used to use "trycatch" insert row by row to skip the error, because it will take much time to examine every row. so: Allow COPY to report error lines and continue. this is a good idea. This is a long standing request and many people would be happy to see it implemented. You do want to make sure the implementation easily allows pushing all the lines that didn't commit into what's commonly called a "reject file". is these feasible? which one should i choose to proposal or both?
[HACKERS] Idea about sql command create table like
Hello, I've seen that postgreSQL tries to follow sql standards as far as possible, and that sometimes (often) brings it's own extentions. I've thought of one concerning the sql command create table like. As far as I've understood the documentation, one can create a table using another table as a template. Could it be possible then to add the possibility just to rename columns using this command. Here is an example to make my words a better meaning : create table some_lib ( some_code serial primary key, some_text text ); Now, I want a second table that has the same structure, I can do it two ways : create table any_lib ( any_code serial primary key, any_text text ); or create table any_lib like (some_lib including constraints); If I use the second option, the fields of any_lib will be named some_code and some_text, which is not what I want, so I will have to use alter table any_lib rename column some_code to any_code; alter table any_lib rename column some_text to any_text; Could it be possible to have a syntax that would allow to use "create table like" to rename fields as well, for example : create table any_lib like (some_lib including constraints) with names (any_code, any_text); Of course, this is just an example, and the tables schemas used are not big enough to make it needed in this case, it is just here to illustrate my meaning. If one or more fields have to keep the name of the original table, then one could use one keyword (maybe "default") like this : create table any_lib like (some_lib including constraints) with names (default, any_text); This way, the first field would be named some_code... What do you think of this ? signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] [ANNOUNCE] == Postgres Weekly News - March 09 2008 ==
On Mon, Mar 10, 2008 at 12:16:52AM -0700, David Fetter wrote: > - Add to TODO: "Add SQLSTATE severity to PGconn return status." Yes, please. I have recently been discussing this with Andreas, Bernd and Peter at Linuxtage Chemnitz. In GNUmed we want to be able to distinguish connection errors due to faulty credentials (not *which* part of the credentials only *whether* credentials are the cause) from other errors in order to react meaningfully in the program. Currently, PGconn only returns CONNECTION_BAD or _OK and an error message. The error message already says things like FATAL: password authentication failed for user ... so the level of information I want is already there. However, our code would need to parse the message to detect this state. This is a problem because the above message is translated according to the local users LC_MESSAGE setting. IOW, it'd be really helpful if the error state communicated by the message in a way independant of the wording of the message itself. Now, a technically proper way might be to change the API to return a status along with the message. A hacky but easy-to-implement solution might be to simply append or prepend a fixed, non-translated string to the message. Thanks, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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 vs. two-phase commit
Tom Lane wrote: Does it make any sense to allow LISTEN or UNLISTEN in a prepared transaction? It's certainly not sensical for these actions to affect the backend that actually executes the COMMIT PREPARED, in the sense of creating or destroying pg_listener entries for it. But how can we say that they should affect the originating backend either? It might not even be around anymore. Hmm. Yeah, it's much like the problem with temporary tables. In the current implementation, LISTEN/UNLISTEN create or delete pg_listener rows that are then held as uncommitted until COMMIT PREPARED. This is bad enough for the LISTEN case, as a pg_listener row becomes active for a backend PID that might not exist any longer, or might now refer to a different session. Yeah, that's bad :-(. In practice, prepared transactions should be pretty short-lived, so reusing the PID for another backend seems unlikely. Entries for non-existent PIDs will be cleared on the next NOTIFY, but of course, if the PID is reused before any NOTIFYs, we're in trouble. In the UNLISTEN case it'd result in blocking any other backend that is unlucky enough to try to send a notify to the pending-dead tuple. (Well, actually, there's some ugly coding in async.c that avoids that, but surely that's a crock.) And weird as that behavior would be, there would be no way at all to duplicate it after the long-planned rewrite to get rid of pg_listener and handle LISTEN/NOTIFY all in memory. So I'm thinking that PREPARE TRANSACTION should throw an error if any LISTEN or UNLISTEN is pending in the current transaction. This is relatively difficult to enforce correctly in the existing code, but it will be easy in the rewrite that I'm working on in response to Laurent Birtz's bug report. Seems reasonable, for 8.4 at least. I can't think of a use case for using 2PC with LISTEN/UNLISTEN. For back-branches, I'm a bit hesitant to do that, as there might be applications that do LISTEN in a prepared transaction unknowingly. Such an application wouldn't actually care about the atomicity of the LISTEN, but for example just issues a LISTEN at the beginning of each transaction, "just in case", together with a connection pool or something where it doesn't know which connection it's using. BTW, another little issue I just noticed is that while 2PC can cope with NOTIFY actions, the eventual notify is sent with the PID of the backend that executes COMMIT PREPARED, not the one that originally created the prepared transaction. It's not clear if this is good, bad, or indifferent; but at the least it probably deserves a sentence someplace in the documentation. To be honest, I didn't realize the receiver gets to know the PID of the sending process, but clearly it does. It seems mostly indifferent to me; it's not guaranteed that the PID is valid by the time the client application sees it anyway. There is one slightly interesting use case though: if the client application ignores self-notifies, it would ignore the NOTIFYs of the prepared transactions it commits, even though they originally ran in another backend. It's worth mentioning in the docs, but I would leave it as it is for now. -- 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] strange pg_ctl's behavior
Richard Huxton wrote: Hmm - that does seem to be the case. I added a line to print "connstr" along with the "." waiting for server to startdbname=postgres port=5483 -i connect_timeout=5. The code looks fine to my uneducated eye though (bin/pg_ctl/pg_ctl.c test_postmaster_connection starting at line 412. I think the issue must be at lines 425..443) Line 52: #define WHITESPACE "\f\n\r\t\v" /* as defined by isspace() */ We've defined whitespace as not including a space character :-/ -- Richard Huxton Archonet Ltd -- 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] strange pg_ctl's behavior
> Tatsuo Ishii wrote: > > Hi, > > > > I have encountered a strange pg_ctl's behavior in 8.3. > > > > pg_ctl -w -o "-p 5432" start<-- works > > pg_ctl -w -o "-i" start <-- works > > pg_ctl -w -o "-p 5432 -i" start <-- doesn't work > > > > In the last case, even postmaster starts successfully, pg_ctl keeps > > trying to make sure that postmaster actually started and continues to > > print "...". > > It's not getting confused and thinking the port is "5432 -i" is it? I > tried "-i -p 5432" and that seemed to work. Thanks for the hint. I confirmed that: pg_ctl -w -o "-p 5432 '-i'" also worked. If my memory is correct, pg_ctl -w -o "-p 5432 -i" worked fine on 8.2 or 8.1 or before. -- Tatsuo Ishii SRA OSS, Inc. Japan -- 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] strange pg_ctl's behavior
Richard Huxton wrote: Tatsuo Ishii wrote: Hi, I have encountered a strange pg_ctl's behavior in 8.3. pg_ctl -w -o "-p 5432" start<-- works pg_ctl -w -o "-i" start<-- works pg_ctl -w -o "-p 5432 -i" start<-- doesn't work In the last case, even postmaster starts successfully, pg_ctl keeps trying to make sure that postmaster actually started and continues to print "...". It's not getting confused and thinking the port is "5432 -i" is it? I tried "-i -p 5432" and that seemed to work. Hmm - that does seem to be the case. I added a line to print "connstr" along with the "." waiting for server to startdbname=postgres port=5483 -i connect_timeout=5. The code looks fine to my uneducated eye though (bin/pg_ctl/pg_ctl.c test_postmaster_connection starting at line 412. I think the issue must be at lines 425..443) -- Richard Huxton Archonet Ltd -- 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] strange pg_ctl's behavior
Tatsuo Ishii wrote: Hi, I have encountered a strange pg_ctl's behavior in 8.3. pg_ctl -w -o "-p 5432" start <-- works pg_ctl -w -o "-i" start <-- works pg_ctl -w -o "-p 5432 -i" start <-- doesn't work In the last case, even postmaster starts successfully, pg_ctl keeps trying to make sure that postmaster actually started and continues to print "...". It's not getting confused and thinking the port is "5432 -i" is it? I tried "-i -p 5432" and that seemed to work. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] strange pg_ctl's behavior
Hi, I have encountered a strange pg_ctl's behavior in 8.3. pg_ctl -w -o "-p 5432" start<-- works pg_ctl -w -o "-i" start <-- works pg_ctl -w -o "-p 5432 -i" start <-- doesn't work In the last case, even postmaster starts successfully, pg_ctl keeps trying to make sure that postmaster actually started and continues to print "...". I now that "-i" is obsoleted, but I think it does not explain well why pg_ctl behaves like this. -- Tatsuo Ishii SRA OSS, Inc. Japan -- 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] [PATCHES] Fix for large file support (nonsegment mode support)
> > Why is this not the default when supported? > > Fear. > > Maybe eventually, but right now I think it's too risky. > > One point that I already found out the hard way is that sizeof(off_t) = 8 > does not guarantee the availability of largefile support; there can also > be filesystem-level constraints, and perhaps other things we know not of > at this point. Exactly, e.g. AIX is one of those. jfs (not the newer jfs2) has an option to enable large files, which is not the default and cannot be changed post crfs. And even if it is enabled, jfs has a 64 Gb filesize limit ! Anybody know others that support large but not huge files ? Andreas -- 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] [PATCHES] Fix for large file support (nonsegment mode support)
> > Why is this not the default when supported? I am wondering both from the > > point of view of the user, and in terms of development direction. > > Also it would get more buildfarm coverage if it were default. If it > breaks something we'll notice earlier. No we don't, because the buildfarm does not test huge files. Andreas -- 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] [PATCHES] Fix for large file support (nonsegment mode support)
Tom Lane wrote: > I think this needs to be treated as experimental until it's got a few > more than zero miles under its belt. OK, then maybe we should document that. > I wouldn't be too surprised to > find that we have to implement it as a run-time switch instead of > compile-time, in order to not fail miserably when somebody sticks a > tablespace on an archaic filesystem. Yes, that sounds quite useful. Let's wait and see what happens. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
