Re: [GENERAL] OS X Install
A few comments -- On Fri, 15 Oct 2004 20:09:42 -0400 Nathan Mealey <[EMAIL PROTECTED]> wrote > I am trying to install PostgreSQL on OS X 10.3, using the package from > Entropy.ch. The installation instructions there, as well as anywhere > else I have seen them on the net, say to create a user (using the > System Preferences pane) with a shortname "postgres". The problem is, > this user already exists in my netinfo database/domain, and so I cannot > either create a new user with the same short name, or use this user, > because I do not know the password (I assume it is a system default > user). Thus, I am unable to complete the installation, because I > cannot run the following commands as user postgres. > > /usr/local/bin/initdb -D /usr/local/pgsql/data > > /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l postgres.log start > > Has anyone else encountered this before? I'm so confused by this...why > does this user already exist, and why can't I make use of it for this > installation? Should I create a different user? man sudo man sudoers And then look up netinfo. I'm pretty sure netinfo is available in the man, but you can use Google on Apple's site to get plenty of information. There is no need to give either the postgres user or root a password, much less a shell or a login directory, if you use sudo. Deleting the postgres user/group and adding them again should not be necessary (and will likely change the userid/groupid assigned, if you don't watch, but I don't know whether that might end up an issue). (And I have no idea why Apple would suggest using the system preferences user pane to add the postgres user unless they are intentionally dumbing it down. It works, but then you have OS login enabled for the "postgres" user.) My JPY 2. -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OS X Install
> ... I see a mysql user > also. I know I did not create or install that :). > ... That has in there by default since sometime before Jaguar. It's not used unless you install mysql, but mysql became part of the default install in the server versions somewhere after 10.0 beta, which might have something to do with it. -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] compatibilityissues from 7.1 to 7.4
I seem to remember reading a post on this, but searching marc does not seem to bring it up immediately. Company BBS is on postgresql, but it's still at 7.1. The guy in charge of it wants some ballpark estimates and warnings about upgrading to 7.4 so he doesn't have to worry about the recent vulnerabilities. War stories? Things to watch out for? -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] compatibilityissues from 7.1 to 7.4
On Tue, 26 Oct 2004 13:30:49 +0200 Ian Barwick <[EMAIL PROTECTED]> wrote > On Tue, 26 Oct 2004 18:22:55 +0900, Joel <[EMAIL PROTECTED]> wrote: > > I seem to remember reading a post on this, but searching marc does not > > seem to bring it up immediately. > > > > Company BBS is on postgresql, but it's still at 7.1. The guy in charge > > of it wants some ballpark estimates and warnings about upgrading to 7.4 > > so he doesn't have to worry about the recent vulnerabilities. > > > > War stories? Things to watch out for? > > Off the top of my head: over-length data inserted into varchar fields > will no longer be silently truncated, raising an error instead ( a big > source of problems with web-based apps); also, the LIMIT x,y syntax > will no longer work. > > Your best bet is fro someone who knows your system to go through the > PostgreSQL release notes. Thanks. The guy in charge of this bbs is, of course, looking to avoid work (don't we all), so he was wondering about whether 7.1 was subject to this vulnerability and the possible data loss bug. I did a little research, and it looks like 7.1.3 is the last of the 7.1 line. Security Focus reports a boundary condition vulnerability for 7.1.3 from 2003. So it doesn't look wise to leave it at 7.1 forever, I suppose. I'm looking at the release notes for 7.2 and thinking that, when we make the jump, jumping to 7.4 will probably be the best bet. Any other suggestions? Any thoughts on the urgency of the move? -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] compatibilityissues from 7.1 to 7.4
On Thu, 28 Oct 2004 01:01:20 -0400 Tom Lane <[EMAIL PROTECTED]> wrote > Joel <[EMAIL PROTECTED]> writes: > > Any thoughts on the urgency of the move? > > How large is your pg_log file? 7.1 was the last release that had the > transaction ID wraparound limitation (after 4G transactions your > database fails...). If pg_log is approaching a gig, you had better > do something PDQ. Great. Very low use (to this point) BBS and similar things, so it looks like we'll miss this issue. > More generally: essentially all of the data-loss bugs we've fixed lately > existed also in 7.1. The core committee made a policy decision some > time ago that we wouldn't bother back-patching further than 7.2, however. > The only reason 7.2 is still getting some patching attention is that it > was the last pre-schema release, and so there might be some people out > there with non-schema-aware applications who couldn't conveniently move > up to 7.3 or later. But once 8.0 is out we'll probably lose interest in > supporting 7.2 as well. Thanks for the answers. I think we have good motivation to proceed. -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] {OT?] Auth_PG_grp_group_field directive gives parameter error
I've sent an e-mail to Guiseppe Tanzilli about this, but maybe someone here has seen this. I'm pretty sure it's not PostGreSQL, but it is tangential. We are updating to mod_auth_pgsql2 v2.0.latest and apache 2.0.latest, in the process of updating to PostGreSQL 7.4.latest. We get the following error: > Auth_PG_grp_group_field takes one argument, the name of the group-name field. on the directive Auth_PG_grp_group_field rid These are the directives we are using: Auth_PG_host 127.0.0.1 Auth_PG_port 5432 Auth_PG_database apache_auth Auth_PG_user postgres Auth_PG_pwd postgres Auth_PG_pwd_table user_bbs Auth_PG_uid_field uid Auth_PG_pwd_field pw Auth_PG_grp_table user_bbs #Auth_PG_gid_field rid # name change from 2.0.0 Auth_PG_grp_group_field rid# Auth_PG_gid_field -> Auth_PG_grp_group_field Auth_PG_grp_user_field uid # works from 2.0.0 #Auth_PG_grp_whereclause " and rid = '[EMAIL PROTECTED]' " Auth_PG_encrypted on Auth_PG_hash_type MD5 AuthName "Please Enter Your Password" AuthType Basic require valid-user require group [EMAIL PROTECTED] (And, yes, I'll also try an apache mailing list.) Apologies in advance if the noise is not appreciated. -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] {OT?] Auth_PG_grp_group_field directive gives parameter error
Sorry for the noise. We resolved it: On Fri, 29 Oct 2004 12:36:54 +0900 Joel <[EMAIL PROTECTED]> wrote > ... > > We get the following error: > > > Auth_PG_grp_group_field takes one argument, the name of the group-name field. > > on the directive > > Auth_PG_grp_group_field rid > > These are the directives we are using: > >Auth_PG_host 127.0.0.1 > Auth_PG_port 5432 > Auth_PG_database apache_auth > Auth_PG_user postgres > Auth_PG_pwd postgres > Auth_PG_pwd_table user_bbs > Auth_PG_uid_field uid > Auth_PG_pwd_field pw > Auth_PG_grp_table user_bbs > #Auth_PG_gid_field rid # name change from 2.0.0 > Auth_PG_grp_group_field rid# Auth_PG_gid_field -> Auth_PG_grp_group_field The comment on the above directive seems to have been parsed as an additional parameter, so we just removed the comment and apache runs with no problems. > Auth_PG_grp_user_field uid # works from 2.0.0 > #Auth_PG_grp_whereclause " and rid = '[EMAIL PROTECTED]' " > Auth_PG_encrypted on > Auth_PG_hash_type MD5 > AuthName "Please Enter Your Password" > AuthType Basic > > require valid-user > require group [EMAIL PROTECTED] > > > ... > Apologies in advance if the noise is not appreciated. -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] OS X Install
(A little back-seat driving from me below:) On Thu, 4 Nov 2004 10:11:18 -0500 Jim Crate <[EMAIL PROTECTED]> favored us with > I'm a little late to the party, but figured I could at least offer some info for > the archives. > > If you don't know the user's password, and you have admin access, then it really > doesn't matter. In fact, I set any special users (pgsql, mailman, etc.) up so > that they *cannot* be logged into normally. Instead, do this: > > sudo su - pgsql This gives you a shell under the pgsql user, which can be convenient, but anything you do after getting the shell is not logged. Logging is convenient in a different way. Also, if you tighten up the configuration a bit, su will refuse to log a user with no default shell or home directory. I prefer to sudo each command, for the logging: sudo -u pgsql If you don't like all that extra typing, command line editing is on by default in the Mac OS X shell window, so you can just hit the up arrow, erase the previous command, and type the next one. > and then all you need to know is your own password. > > You can use niutil to create and delete users and groups. And you can even use the netinfo GUI utility. (heh. I sudo each command, but I use the GUI netinfo to save typing. I'm strange, I guess.) > Reading the man page > and exploring a bit using the -list and -read options will be very informative. > > Here is a script I use to create a user. Execute it with sudo. > > > > niutil -create . /groups/pgsql > niutil -createprop . /groups/pgsql gid 5001 > > niutil -create . /users/pgsql > niutil -createprop . /users/pgsql uid 5001 > niutil -createprop . /users/pgsql gid 5001 > niutil -createprop . /users/pgsql passwd \* > niutil -createprop . /users/pgsql home /Users/pgsql > niutil -createprop . /users/pgsql shell /bin/tcsh > > > gid and uid can be set to some non-conflicting number of your choice. I believe > that numbers over 5000 do not show up in System Preferences or the login window. > > -- > Jim Crate > Deep Sky Technologies, Inc. -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.
On Mon, 08 Nov 2004 12:55:06 -0800 Mike Cox <[EMAIL PROTECTED]> wrote > ... > There is also the point of having to post. If I post and I subscribe to the > digest version or if I post with the option of no emails (since my inbox > cannot handle the load), how would I respond to a thread I created? Would > I have to create a new thread for each response nameing the Subject with > the previous one, and prefixing it with "RE:"? > > The usenet experience is more seemless and efficient IMHO. That is why I > rarely subscribe to mailing lists. The KLM (kernel mailing list) destroyed > my inbox after a few hours and I bet the postgresql mailing list would do > the same if I didn't delete my inbox within a few days. > ... postgresql-general averages in the light-to-moderate range, between 30 to 80 posts a day. It's not that bad, although it would not feel so great if you were on dial-up. Incidentally, there are several archives, including the one at http://marc.theaimsgroup.com http://marc.theaimsgroup.com/?l=postgresql-general&r=1&w=2 which picks posts up very quickly. Might be useful until a charter can be worked out that reflects the list policies. http://www.postgresql.org/lists.html http://archives.postgresql.org/pgsql-general/ For instance, as I understand it, you will need to specified that it is moderated at the SPAM-block level. Since they seem to be concerned about whether Marc (all by himself?) would be appropriate, particularly on the usenet side, perhaps it would be good to set up a group of moderators? Ideally, they could be spread around the globe and take shifts, to get good time coverage. Oh, and thanks, Mike, for stirring up the hornets' nest. ;-P (Somebody had to take the brunt of it.) -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] These Lists Are Being Cut To FOUR
> Hello. My name is Mike Cox. I am in charge of the changing of these > postgresql lists. ... (That was a mock diatribe, correct?) Mike, you are not in charge, you are just leading the point on the RFD since Marc has other priorities. (And believe me, he does.) Take a break from the process for a day. There's no rush. And it really isn't all on your back. If you insist on keeping it that way, the vote will fail, and we'll be back where we were. Are you needing a co-proponent? -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] These Lists Are Being Cut To FOUR
> ... > I believe these posts are spoofs. Mike has not posted anything along > these lines before, and the headers are different from his usual posts. Hmm. >>> X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char DD hex) in message >>> header 'X-Mailer' X-Mailer: B\335ANet.org\n ^ Yeah, I think I'm going to agree with you on that. The tone is definitely a little over the top compared to what he's posted before. I should have checked the headers. > Unless he 'fesses up and claims these posts as his own, I'm more than > willing to give him the benefit of the doubt. These mailing list/new > group threads have been tense enough as it is. :) Even if he were to claim it, I'd still give him the benefit of the doubt. Polarhound seems to be enjoying a little pyromania in news.groups. But we probably do need someone from the mail list to go co-proponent with Mike. Unless, of course, the majority of the list would prefer to keep the status quo. -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] These Lists Are Being Cut To FOUR
Richard Huxton wrote > Joel wrote: > > > > But we probably do need someone from the mail list to go co-proponent > > with Mike. > > > > Unless, of course, the majority of the list would prefer to keep the > > status quo. > > I must admit I'm not clear what difference this makes to those of us > subscribed to the mailing lists. Is the following correct? > > 1. We already have a news<=>mail gateway > 2. The namespace is not officially recognised which means many news > servers don't provide a feed. > 3. Mike Cox would like to go through the process to get some of these > names in the official hierarchy, thus spreading access > 4. All lists will continue to be available via news.postgresql.org > > If so, it seems that the only difference I (as a mail-based user) will > see is more people posting via newsgroups rather than email. > > Am I missing something here? No, not really. It raises the profile of the project a bit, makes access to PostGreSQL a little easier for people who find usenet easier or cheaper to use than mailing lists. I don't think it's worth distracting the developers over it, but if some of the users have some spare time to push it through, it might be worth the effort. And it would show support for usenet policy. Good neighbor stuff, you know. Karma, as they say. -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Important Info on comp.databases.postgresql.general
A few more points -- > I'm probably a bit more concerned about this than you are... I don't > want to have to post anonymously just to protect my email address... > That is precisely why I stopped using Usenet about 5 years ago - it just > got overwhelming... Just out of curiousity, does your mail reader do filtering? > I hope the owner of this list considers this issue very carefully.. I for > one will probably find support for Postgres through other mechanisms (I'm > not sure what those would be yet) if what you are suggesting may come to > pass actually does Watch the newsgroups on Google archives? ;-) http://groups.google.com/groups?group=comp.databases.postgresql.general > The quality of this mailing list has always been extremely high and it > would be a real shame to lose that Well, until the kooks (one kook?) gets bored, there's not much to do about it now. Mike stirred up the kooks, but we can't undo that. In the meantime, if you see the f* word in a post, assume it's from the kook and don't give it any further thought. > I know that I surely do not need any more spam... To say nothing of jerks > posting infantile messages... I have a job to do and this list (as it is > now) is an integral part of that > > From what it sounds like the Usenet folks have decided up until now not > to participate on the Postgres mailing list for whatever reason.. I can > only surmise that it is not that important to them --- it is to me though > (and I imagine a lot of other people)... It was not the USENET folks' decision. It was only that Marc had reasons of his own (maybe just not enough time? Heh.) to formally request the namespace allocation. > Why do we need to suffer at > their expense?. I mean if they are going to actually contribute - > great, but that is simply not what appears to be happening here Those were not typical. I'm not even sure they were posted to try to push opinion against formalizing the groups. What I see in news.groups indicates to me they are just trying to see how many people they can get to gag. Ignore them and pretty seen they get bored and go look for other prey. > ... -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] comp.database.postgresql.*
> Mike Cox sent an email to the newsgroup news.groups last night saying he's > giving up the usenet effort because of resistance here. What a shame. Marc, Should we leave this as is, or would you like someone to pick the RFD back up? (Yes, I'm saying I can volunteer. My pace would be a bit slow, but that might actually be an advantage.) (And, BTW, Mike, thanks for trying.) -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Q about NAS mounting things
Long and rambling question, here -- we have a customer with a database (Bthat is outgrowing their original storage, and they want to upgrade (Bstorage as cheaply and smoothly as possible. (B (BPresently, they have one 120G or so RAID 5 unit, and the entire system (Band data are stored on that drive. (B (BWe've talked about cloning the current drive to a larger capacity RAID (Band swapping the larger one in. We've also talked about hanging a second (B(external) RAID 5 unit on the machine and just copying the data over, (Bleaving the OS, the system, and the software components on the original (Bdrive. These solutions are perceived by the customer as too expensive or (Bsomething. (B (BNow we are considering NAS, mounted over NFS. (So far they have only (Bbeen able to find a Terabyte class cheap NAS running MSWindows, but I (Bthink we're going to look harder for Linux NAS.) (B (BI've suggested running postmaster on the NAS, but we don't seem to want (Bto dig into the code. (B (BOne of our group wants to copy both data and postgresql to the NAS, (Brunning postmaster on the server from the binary saved on the NAS. I (Bthink this sounds too fragile. Specifically, if the NAS goes off line (Bwhile postmaster is running, it will tend to go looking for code and die (Bthe horrible, won't it? (B (BSo I have recommended data only on the NAS, modifying the startup and (Bshutdown scripts to check the presence of the NAS before attempting to (Bmount, and adding a watchdog to check every second whether the NAS is (Bstill mounted. I'm also expecting we'll be able to set postgreql to fire (Boff an e-mail if it suddenly can't find the files because the NFS (Bmounted NAS has disappeared for some reason. (B (BOpinions, anyone? (B (B-- (BJoel Rees <[EMAIL PROTECTED]> (Bdigitcom, inc. $B3t<02qhttp://www.ddcom.co.jp> ** (B (B (B---(end of broadcast)--- (BTIP 5: Have you checked our extensive FAQ? (B (B http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] USENET vs Mailing Lists Poll ...
As long as the web page maintainers are going to the trouble of taking a (Bsurvey, might I (at the risk of being tarred and feathered :-p) suggest (Ba more thorough survey? (B (BSuggested questions: (B (B(1) If there were a USENET newsfeed, under comp.databases.postgresql.*, (Bof one or more of the current postgresql mailing lists, I would (B (B(a) use USENET primarily, (B(b) use both USENET and the mailing lists, (B(c) use the mailing lists primarily, (B(d) unsubsribe from the mailing lists and use neither, or (B(e) not sure at this time. (B (B(2) If there were a separate USENET comp.databases.postgresql newsgroup (Bcreated, I would (B (B(a) use the separate USENET newsgroup primarily, (B(b) use both the separate USENET newsgroup and the mailing lists, (B(c) use the mailing lists primarily, (B(d) unsubsribe from the mailing lists and use neither, or (B(e) not sure at this time. (B (B(3) Concerning USENET, I would prefer (B (B(a) that the mailing lists be gated to USENET, (B(b) that the mailing lists and USENET be kept seperate, (B(c) that USENET go take a leap <;-/, or (B(d) not sure at this time. (B (B(4) If the mailing lists are gated to USENET, I would prefer (B (B(a) that the current SPAM moderation policy apply to both, (B(b) that no moderation occur on either USENET or the lists, (B(c) that kooks who post to USENET be tarred and feathered 8-*, or (B(d) not sure at this time. (B (BPlease not that this is not an attempt at a survey, see 3c and 4c. It is (Bonly a suggestion. (B (B-- (BJoel Rees <[EMAIL PROTECTED]> (Bdigitcom, inc. $B3t<02qhttp://www.ddcom.co.jp> ** (B (B (B---(end of broadcast)--- (BTIP 4: Don't 'kill -9' the postmaster
Re: [SOLVED] Re: [GENERAL] pgsql8b5 not launching on OSX system start;
(From someone else who doesn't know what doesn't know, ... :-/) (B (B>sudo -u testuser sh -c "nohup /usr/local/pgsql/bin/postmaster [...] (B... (B (B> >> note that my cmd line refers to the conf file, which has the external (B> >> PID id'd in it: (B> > (B> >> external_pid_file = '/var/run/postgresql.pid' (B> ... (B> just checked -- looks ok. PID is properly 'owned & operated' by the (B> postmaster (B> superuser defined in the launch command (B (BWho owns /var/run? What group? Does testuser have permission to delete (Bfiles there? (May need to add testuser to the wheel or admin group?) (B (BAnother thought, try su -c instead of sudo? (B (B(See warning on first line. It's been a while since I've mucked that (Bdeep in the Mac OS X configurations, and my box is still on 10.2, so I'm (Bprobably just blowing smoke.) (B (B-- (BJoel Rees <[EMAIL PROTECTED]> (Bdigitcom, inc. $B3t<02qhttp://www.ddcom.co.jp> ** (B (B (B---(end of broadcast)--- (BTIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] initdb error: "could not identify current directory" (or,
> % cd /var/data/pgsql (B> still reports the absolute path (B> --> /Volumes/data/pgsql (B (BIt looks to me like you are attempting to mount a few volumes under /var? (B (BI think that's going to wrinkle your handkerchief in Darwin. (B (B-- (BJoel Rees <[EMAIL PROTECTED]> (Bdigitcom, inc. $B3t<02qhttp://www.ddcom.co.jp> ** (B (B (B---(end of broadcast)--- (BTIP 3: if posting/reading through Usenet, please send an appropriate (B subscribe-nomail command to [EMAIL PROTECTED] so that your (B message can get through to the mailing list cleanly
Re: [GENERAL] Do we need more emphasis on backup?
> ... PostgreSQL will run on almost any hardware, > but if you are building a server where you are concerned about reliability > and performance it is wise to research your hardware options thoroughly. ... That's okay, of course. But I'll suggest the following as food for thought PostgreSQL will run on almost any hardware, but, if you are building a server where you are concerned about reliability and performance, it is wise to research your hardware options thoroughly. or PostgreSQL will run on almost any hardware, but, especially where reliability and performance are required, it is wise to research your hardware options thoroughly. (where/when) -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Do we need more emphasis on backup?
> New text: > > Because PC hardware is mostly compatible, people tend to believe that > all PC hardware is of equal quality. It is not. ECC RAM, SCSI, and > quality motherboards are more reliable and have better performance than > less expensive hardware. PostgreSQL will run on almost any hardware, > but if reliability and performance are important it is wise to > research your hardware options thoroughly. Our email lists can be used > to discuss hardware options and tradeoffs. I like that, too. (And now, after a break with real-world grammars, back to the cold, cruel world of mechanical parsers. ;) -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] UTF-8 and LIKE vs =
On Tue, 24 Aug 2004 01:34:46 +0200
(BIan Barwick <[EMAIL PROTECTED]> wrote
(B
(B> ...
(B> wild speculation in need of a Korean speaker, but:
(B>
(B> [EMAIL PROTECTED]:~/tmp> cat j.txt
(B> $Bec,e$;ec(B
(B> $ByyPl%$%9wd!"(B
(B> $Bx"(l%$(Bl$B%i(B
(B> $Bw{%1v.%/wd(B
(B> $Bt7%ev2%%v;(B
(B> $Bv8"oI|w}(B
(B> $Bea%reaRe"*(B
(B> [EMAIL PROTECTED]:~/tmp> uniq j.txt
(B> $Bec,e$;ec(B
(B> $ByyPl%$%9wd!"(B
(B> $Bea%reaRe"*(B
(B>
(B> All but the first and last lines are random Korean (Hangul)
(B> characters. Evidently our respective locales think all Hangul strings
(B> of the same length are identical, which is very probably not the
(B> case...
(B
(BMy browser just nicely botched replying on those, but looking at Ian's
(Bpost, the first and last lines looked like "test" written in Japanese,
(Bthe first line in katakana and the last line in hiragana.
(B
(BThe following should end up posted as shift-JIS, but
(B
$B%F%9%H(B
(Band
$B$F$9$H(B
(B
(Bshould collate the same under some contexts, since it's more-or-less
(Bequivalent to a variation in case.
(B
(B--
(BJoel <[EMAIL PROTECTED]>
(B
(B
(B---(end of broadcast)---
(BTIP 5: Have you checked our extensive FAQ?
(B
(B http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Representating a family tree
On Sun, 29 Aug 2004 Guy Naor wrote > Hi, > > Are there any built in or known structures I can use in Postgres to represent a > family tree? I'll go out on a limb here. Not really, but, ... > The usual tree representations in SQL are for regular hierarchy of > data, but on a family each node had usually 2 parents and a few child > nodes. What will be the best way to represent something like this in an > efficient manner? Well, the way I have usually done this is a table of individuals, a table of marriages, and one or more relationship tables. Some people would reduce the number of relationship tables by including an entry for the first child in the marriage record, and including an entry for the next younger sibling in the individual record. I think I prefer to use the relationship records for all relationships. Further details would depend heavily on what you might mean by "marriage" or "family". For instance, do you only want to deal with family of last official record, or do you want to record the birth parents and maybe even all adoption/foster relationships? Etc. These decisions would determine the number and structure of the relationship records, essentially. Anyway, as far as I know, there is no family tree type structure in PostGreSQL. (I can't think of any database except a custom database that would have such a data structure, even though we use a simplification of the family tree for all sorts of data models.) But PostGreSQL does have tables, and you can use tables to model a family tree. My JPY 2. -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] So, would it make sense to do something like a CRC on each record? (was How to determine a database is intact?)
Pardon me for this, but it's Monday morning here and I'm in shallow thinking mode. I was just looking over Wes's questions about checking database integrity, and the responses given, and I'm wondering if keeping a live CRC on each record would make sense? Or is this already done, or maybe an option? (Not that it would in any way solve Wes's company's current problem, ...) -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Finding recursive dependencies
Hi,
I'm trying to find all recursive dependecies for an object, using the query
in
http://archives.postgresql.org/pgsql-general/2009-05/msg01192.php
I ran into problem with view dependencies.
In the example below, the view "b" depends on view "a".
How is it possible to interpret the result of the pg_depend query below,
to detect it's possible to drop "b", but dropping "a" is not possible, since
it depends on "b"?
Non of the objid or refobjid in pg_depend contain the oids of the views,
192092 nor 192096.
The view oids appears to be stored in pg_rewrite.ev_class though, but I find
it strange I cannot find them in pg_depend?
I'm sure there is an explanation to this and a simple way to solve my
problem.
How can a general query be constructed to find out all dependencies for any
given oid, regardless of its object class, listing all objects it depends
on recursively, or alternatively, listing all objects depending on the given
object recursively?
It would be best if such a query could be constructed only using pg_depend
and pg_class, without involving class specific tables such as pg_rewrite,
pg_constraint etc, as such a join would be quite expensive and
"non-general".
test=# CREATE VIEW a AS SELECT 1;
test=# CREATE VIEW b AS SELECT * FROM a;
test=# SELECT oid, relname FROM pg_class WHERE relname IN ('a','b');
oid | relname
+-
192092 | a
192096 | b
(2 rows)
test=# WITH RECURSIVE tree AS (
test(# SELECT 'a'::regclass::text AS tree,
test(#0 AS level,
test(#'pg_class'::regclass AS classid,
test(#'a'::regclass AS objid
test(# UNION ALL
test(#SELECT tree ||
test(# ' <-- ' ||
test(# pg_depend.classid::regclass || ' ' || pg_depend.objid ||
' ' || pg_depend.deptype,
test(# level+1,
test(# pg_depend.classid,
test(# pg_depend.objid
test(# FROM tree
test(# JOIN pg_depend ON ( tree.classid = pg_depend.refclassid
test(# AND tree.objid = pg_depend.refobjid)
test(# )
test-# SELECT tree.tree
test-# FROM tree
test-# WHERE level < 10
test-# ;
tree
-
a
a <-- pg_rewrite 192095 n
a <-- pg_rewrite 192095 i
a <-- pg_type 192094 i
a <-- pg_rewrite 192099 n
a <-- pg_type 192094 i <-- pg_type 192093 i
(6 rows)
-- Same query for b:
tree
-----
b
b <-- pg_rewrite 192099 n
b <-- pg_rewrite 192099 i
b <-- pg_type 192098 i
b <-- pg_type 192098 i <-- pg_type 192097 i
(5 rows)
--
Best regards,
Joel Jacobson
Glue Finance
Re: [GENERAL] Finding recursive dependencies
2011/1/2 Tom Lane > Greg pointed out to start with that that query was unpolished (and, > in fact, basically untested ...) > > I modified the query like this: > which is at least a little bit clearer to look at than what you had. > Thanks a lot for the help! I managed to partly solve the problem for views now anyway, generated a topologically sorted create/drop sequence of views, but it only joins the dependencies between views<->views and not all objects. I'll continue tomorrow including other dependencies as well, such as functions. Please have a look if you think I'm on the right track: https://github.com/gluefinance/fsnapshot/blob/master/PLAYGROUND.sql > The thing you're missing is that implicit dependencies are really > bidirectional: you can't delete either object without deleting the > other. So you have to scan outwards across reverse implicit > dependencies, as well as forward dependencies of all types, if you > want to find everything that must be deleted when dropping a given > object. I don't immediately see any way to do that with a single > recursive query :-(; you'd probably have to code up something in > plpgsql. > > In the case at hand, b's view rule depends normally on a, and also > implicitly on b. > > So, basically it's not possible to define a recursive query only making use of pg_depend to build an entire dependency tree of all oids? It appears to me it's necessary to join the object type specific tables, such as pg_rewrite, to build a complete tree? If so, that's fine, I just wondered if I had missed something making it possible to avoid it. -- Best regards, Joel Jacobson Glue Finance
Re: [GENERAL] Finding recursive dependencies
2011/1/3 Tom Lane : > select refobjid ... where objid matches and deptype = 'i' > then it'd be easy, but you only get one UNION ALL per recursive query. Ah, I see! Thanks for explaining. Now I get it. -- Best regards, Joel Jacobson Glue Finance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Class dependencies
Hi, Is it safe to assume all objects of a given class can be dropped/created, provided all objects of a list of other classes have already been dropped/created? I'm looking at http://developer.postgresql.org/pgdocs/postgres/catalogs.html For each class, a list of "References" are defined, i.e. other classes the given class depend on. For instance, is it correct to assume constraints always can be dropped, i.e. no other class (nor other constraints) can depend on them? -- Best regards, Joel Jacobson Glue Finance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] string_agg hanging?
Hoping someone will be kind enough to share how to write a query that uses 9.0's string_agg with a subselect, something like... select m.id,m.subject,m.from_address,m.date_sent,m.size,string_agg((select address as to_address from recipient r inner join message_recipient mr on r.id=mr.recipient_id and mr.message_id=m.id and mr.recipient_type='To'), ', ') from message m, recipient r group by m.id, m.subject, m.from_address, m.date_sent, m.size limit 20; Unforunately, that just seems to hang. So I'm using... select m.id,m.subject,m.from_address,m.date_sent,m.size,array_to_string(ARRAY(select r.address from recipient r inner join message_recipient mr on r.id=mr.recipient_id and message_id=m.id and mr.recipient_type='To'), ',') as to_addresses from message m, recipient r limit 20; Which returns in under 4 seconds. Am I doing something wrong with the string_agg query to make it hang? Thanks in advance - I greatly appreciate any help you can offer. jr -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Column storage (EXTERNAL/EXTENDED) settings for bytea/text column
Hi all,
I'm trying to do some comparisons between the EXTERNAL and the EXTENDED storage
methods on a bytea column and from the outside the setting doesn't appear to
affect the value stored on initial insert, but perhaps I'm looking at the wrong
numbers. If I create two new tables with a single bytea column and set one of
them to external storage, then insert an existing bytea value from another
table into each one, they appear to be of exactly the same size. This is using
PG 9.0.3 on Debian Lenny, using the backports-sloppy deb package of PG 9.
(I've verified that the first table has "extended" storage via pg_attribute and
that the second table has external.)
create table obj1 ( object bytea );
create table obj2 ( object bytea );
alter table obj2 alter column object set storage external;
insert into obj1 ( object ) select object from serialized_content where id =
12345;
insert into obj2 ( object ) select object from obj1;
select pg_total_relation_size('obj1') as o1, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj1' ) ) as otoast1,
pg_total_relation_size('obj2') as o2, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj2' ) ) as otoast2;
o1 | otoast1 | o2 | otoast2
---+-+---+-
65536 | 57344 | 65536 | 57344
(1 row)
Now at this point if I perform a vacuum full on one or both, they'll both
shrink down to a bit over half that size:
vacuum full obj1;
vacuum full obj2;
select pg_total_relation_size('obj1') as o1, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj1' ) ) as otoast1,
pg_total_relation_size('obj2') as o2, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj2' ) ) as otoast2;
o1 | otoast1 | o2 | otoast2
---+-+---+-
40960 | 32768 | 40960 | 32768
This shrinkage doesn't occur with a normal vacuum, in fact the reported sizes
increase if I do a normal vacuum at that point, but that's not germane to my
question AFAICT.
Can I use the relation size like this to determine whether or not compression
is happening for these toast columns? If not, is there a way that I can
confirm that it is or isn't active? The results appear to be similar for text
columns.
Thanks,
Joel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Column storage (EXTERNAL/EXTENDED) settings for bytea/text column
On Apr 12, 2011, at 10:33 AM, Bill Moran wrote:
> In response to Joel Stevenson :
>
>> select pg_total_relation_size('obj1') as o1, pg_total_relation_size( (select
>> reltoastrelid from pg_class where relname = 'obj1' ) ) as otoast1,
>> pg_total_relation_size('obj2') as o2, pg_total_relation_size( (select
>> reltoastrelid from pg_class where relname = 'obj2' ) ) as otoast2;
>> o1 | otoast1 | o2 | otoast2
>> ---+-+---+-
>> 40960 | 32768 | 40960 | 32768
>
> I'm not an expert, but it looks like you're not storing enough data to
> actually see the difference, since the actual sizes of the tables will
> always be rounded to an even page size. With only 1 row, it's always
> going to take a minimum amount.
>
> Also, are you sure you're storing compressible data? For example, if
> you're putting PNG or JPEG images in there, they're not going to compress
> any.
>
Thanks for the reply, Bill.
The data is very compressible, the raw data is 33392 bytes long and gzips down
to 6965 bytes. As far as not storing enough, the description of the 'SET
STORAGE' clause and the TOAST strategy it sounds like the TOASTer will try to
compress anything that doesn't fit into the PG page ( default 8Kb ) so I
would've thought that compression would be used for the EXTENDED column and not
used for the EXTERNAL column since my single-row data is larger than that.
To be certain I stored 10 rows of that data and rechecked the reported size
after a vacuum full:
select pg_total_relation_size('obj1') as o1, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj1' ) ) as otoast1,
pg_total_relation_size('obj2') as o2, pg_total_relation_size( (select
reltoastrelid from pg_class where relname = 'obj2' ) ) as otoast2;
o1 | otoast1 | o2 | otoast2
+-++-
147456 | 139264 | 147456 | 139264
So, again from the outside, the column storage settings don't appear to be
behaving as I'd expect them too.
Stumped.
- Joel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] High Disk Utilization on Master with Streaming Replication Running 9.2.4
Hello, What is the disk overhead for streaming replication? I have noticed that PostgreSQL’s disk utilization goes up by 3x on the master when a slave is connected. The setup only uses streaming replication, i.e. archive is off, there is no archive command. I see with iotop that the wal writer process is averaging 80% of IO. If I stop the slave, then IO utilization on the master drops to ~20%. Is there something that I am doing wrong? I have wal_keep_segments at 2048, checkpoint_segments at 96. Thank you, Joel
[GENERAL] Question about Vacuum and Replication failures in 9.3.5
I noticed that tables on my master PostgreSQL server were growing, and running vacuum full analyze on them actually made them even bigger. At the same time, a slave PostgreSQL server had fallen behind in trying to replicate, and was stuck in constantly looping over ‘started streaming WAL from primary at…’ and ‘requested WAL segment …. has already been removed’. Once I stopped running the slave instance, I was able to manually vacuum the tables, and appears that auto vacuum is now able to vacuum as well. One table (for instance) dropped from 10Gb down to 330Mb after this operation. I don’t see anything about auto vacuum not able to acquire locks while the slave wasn’t able to replicate. I am unclear why a slave trying to continue streaming would block the auto vacuum, or is something else at play? I did check, and no base backups were in progress at the time this occurred. Thank you, Joel Avni
Re: [GENERAL] Question about Vacuum and Replication failures in 9.3.5
It 9.3.5 and I did the manual vacuum to try to see where the problem might be. On 9/22/14, 4:04 PM, "Adrian Klaver" wrote: >On 09/22/2014 01:42 PM, Joel Avni wrote: >> I noticed that tables on my master PostgreSQL server were growing, and >> running vacuum full analyze on them actually made them even bigger. > >First what version of Postgres are you using? > >Second VACUUM FULL is usually not recommended for the reason you found >out and which is documented here: > >http://www.postgresql.org/docs/9.3/interactive/sql-vacuum.html > >FULL > > Selects "full" vacuum, which can reclaim more space, but takes much >longer and exclusively locks the table. This method also requires extra >disk space, since it writes a new copy of the table and doesn't release >the old copy until the operation is complete. Usually this should only >be used when a significant amount of space needs to be reclaimed from >within the table. >" > >> >> At the same time, a slave PostgreSQL server had fallen behind in trying >> to replicate, and was stuck in constantly looping over Œstarted >> streaming WAL from primary atŠ¹ and Œrequested WAL segment Š. has >> already been removed¹. Once I stopped running the slave instance, I was >> able to manually vacuum the tables, and appears that auto vacuum is now >> able to vacuum as well. One table (for instance) dropped from 10Gb down >> to 330Mb after this operation. I don¹t see anything about auto vacuum >> not able to acquire locks while the slave wasn¹t able to replicate. I >> am unclear why a slave trying to continue streaming would block the auto >> vacuum, or is something else at play? > >My guess related to the locks your VACUUM FULL was taking, though it >would require more information on what all the various parts where doing >over the time frame. > >> >> I did check, and no base backups were in progress at the time this >>occurred. >> >> Thank you, >> Joel Avni >> > > >-- >Adrian Klaver >adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about Vacuum and Replication failures in 9.3.5
Its version 9.3.5, whats interesting the that the table grew in size after the vacuum full, which I did to try to see why the auto vacuum wasn¹t working. However, after I stopped the PostgreSQL slave instance, then vacuum full did result in a much much smaller size, as expected. So it appears to be that there must be some interaction between a slave that trying to do streaming replication but failing, because the requests WALs have been cycled out and vacuuming on the master. I am not entirely sure that¹s the case, but I think observed it twice. Is it the master can¹t clean up tuples that might be visible at the slave¹s last replayed transaction? I didn¹t think the master was aware of the slave¹s state, and why locks can¹t be coordinated between the master and slave. On 9/22/14, 4:04 PM, "Adrian Klaver" wrote: >On 09/22/2014 01:42 PM, Joel Avni wrote: >> I noticed that tables on my master PostgreSQL server were growing, and >> running vacuum full analyze on them actually made them even bigger. > >First what version of Postgres are you using? > >Second VACUUM FULL is usually not recommended for the reason you found >out and which is documented here: > >http://www.postgresql.org/docs/9.3/interactive/sql-vacuum.html > >FULL > > Selects "full" vacuum, which can reclaim more space, but takes much >longer and exclusively locks the table. This method also requires extra >disk space, since it writes a new copy of the table and doesn't release >the old copy until the operation is complete. Usually this should only >be used when a significant amount of space needs to be reclaimed from >within the table. >" > >> >> At the same time, a slave PostgreSQL server had fallen behind in trying >> to replicate, and was stuck in constantly looping over Œstarted >> streaming WAL from primary atŠ¹ and Œrequested WAL segment Š. has >> already been removed¹. Once I stopped running the slave instance, I was >> able to manually vacuum the tables, and appears that auto vacuum is now >> able to vacuum as well. One table (for instance) dropped from 10Gb down >> to 330Mb after this operation. I don¹t see anything about auto vacuum >> not able to acquire locks while the slave wasn¹t able to replicate. I >> am unclear why a slave trying to continue streaming would block the auto >> vacuum, or is something else at play? > >My guess related to the locks your VACUUM FULL was taking, though it >would require more information on what all the various parts where doing >over the time frame. > >> >> I did check, and no base backups were in progress at the time this >>occurred. >> >> Thank you, >> Joel Avni >> > > >-- >Adrian Klaver >adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Select ranges based on sequential breaks
ORDER BY dt ASC
LIMIT 1)
ORDER BY dt DESC
LIMIT 1) AS last,
bin
FROM foo t1
) t0
GROUP BY last, bin
ORDER BY last;
Finally, what's efficient? With 1,000,000 random rows, we get:
Enumeration: 13s
PL/SQL: 12s
Modified David: minutes.
[I used the following to create test data:
CREATE OR REPLACE FUNCTION make_random(n int) RETURNS SETOF foo AS $$
import random
for i in xrange(n):
m =3D random.randint(1,12)
d =3D random.randint(1,28)
b =3D random.choice(('red', 'blue'))
yield '2009-%d-%d' % (m, d), b
$$ LANGUAGE plpythonu;
DELETE FROM foo; INSERT INTO foo (SELECT * FROM make_random(100));]
I hope that helps you in considering various approaches to the problem.
- Joel
[1] http://developer.postgresql.org/pgdocs/postgres/tutorial-window.html
[2] http://developer.postgresql.org/pgdocs/postgres/functions-window.html
On Tue, 16 Jun 2009 06:06:16 +1000, David Wilson
wrote:
> On Mon, Jun 15, 2009 at 2:23 PM, Mike Toews wrote:
>> Hi,
>>
>> I'm having difficulty constructing a query that will find breaks where
>> data
>> change in a time-series. I've done some searching for this too, but I
>> haven't found anything.
>>
>> Here is my example situation, consider my source table:
>> date =A0 =A0bin
>> 2009-01-01 =A0 =A0 =A0red
>> 2009-01-02 =A0 =A0 =A0red
>> 2009-01-03 =A0 =A0 =A0blue
>> 2009-01-04 =A0 =A0 =A0blue
>> 2009-01-05 =A0 =A0 =A0blue
>> 2009-01-06 =A0 =A0 =A0red
>> 2009-01-07 =A0 =A0 =A0blue
>> 2009-01-08 =A0 =A0 =A0blue
>> 2009-01-09 =A0 =A0 =A0red
>> 2009-01-10 =A0 =A0 =A0red
>>
>>
>> I would like to get the first and last of each consecutive series based
>> on
>> column "bin". My result for the table would look like:
>> first =A0 last =A0 =A0bin
>> 2009-01-01 =A0 =A0 =A02009-01-02 =A0 =A0 =A0red
>> 2009-01-03 =A0 =A0 =A02009-01-05 =A0 =A0 =A0blue
>> 2009-01-06 =A0 =A0 =A02009-01-06 =A0 =A0 =A0red
>> 2009-01-07 =A0 =A0 =A02009-01-08 =A0 =A0 =A0blue
>> 2009-01-09 =A0 =A0 =A02009-01-10 =A0 =A0 =A0red
>>
>>
>> This is easy to compute using a spreadsheet or in R, but how would I do
>> this
>> with SQL? I'm using 8.3. Advice is appreciated.
>
> (Written in email and untested- also, someone will probably provide a
> better way, I hope, but this should at least work)
>
> select date as first,
> (select date from table t3 where t3.date<(select date from table t5
> where t5.date>t1.date and t5.bin<>t1.bin order by date asc limit 1)
> order by date desc limit 1) as last,
> bin
> from table t1 where (select bin from table t2 where t2.date order by date desc limit 1)<>t1.bin;
>
> Ugly, and I'm pretty sure there's a much better way, but my brain is
> failing me right now- hopefully this'll at least get you started,
> though.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Select ranges based on sequential breaks
LIMIT 1)
ORDER BY dt DESC
LIMIT 1) AS last,
bin
FROM foo t1
) t0
GROUP BY last, bin
ORDER BY last;
Finally, what's efficient? With 1,000,000 random rows, we get:
Enumeration: 13s
PL/SQL: 12s
Modified David: minutes.
[I used the following to create test data:
CREATE OR REPLACE FUNCTION make_random(n int) RETURNS SETOF foo AS $$
import random
for i in xrange(n):
m = random.randint(1,12)
d = random.randint(1,28)
b = random.choice(('red', 'blue'))
yield '2009-%d-%d' % (m, d), b
$$ LANGUAGE plpythonu;
DELETE FROM foo; INSERT INTO foo (SELECT * FROM make_random(100));]
I hope that helps you in considering various approaches to the problem.
- Joel
[1] http://developer.postgresql.org/pgdocs/postgres/tutorial-window.html
[2] http://developer.postgresql.org/pgdocs/postgres/functions-window.html
On Tue, 16 Jun 2009 06:06:16 +1000, David Wilson
wrote:
> On Mon, Jun 15, 2009 at 2:23 PM, Mike Toews wrote:
>> Hi,
>>
>> I'm having difficulty constructing a query that will find breaks where
>> data
>> change in a time-series. I've done some searching for this too, but I
>> haven't found anything.
>>
>> Here is my example situation, consider my source table:
>> date bin
>> 2009-01-01 red
>> 2009-01-02 red
>> 2009-01-03 blue
>> 2009-01-04 blue
>> 2009-01-05 blue
>> 2009-01-06 red
>> 2009-01-07 blue
>> 2009-01-08 blue
>> 2009-01-09 red
>> 2009-01-10 red
>>
>>
>> I would like to get the first and last of each consecutive series based
>> on
>> column "bin". My result for the table would look like:
>> first last bin
>> 2009-01-01 2009-01-02 red
>> 2009-01-03 2009-01-05 blue
>> 2009-01-06 2009-01-06 red
>> 2009-01-07 2009-01-08 blue
>> 2009-01-09 2009-01-10 red
>>
>>
>> This is easy to compute using a spreadsheet or in R, but how would I do
>> this
>> with SQL? I'm using 8.3. Advice is appreciated.
>
> (Written in email and untested- also, someone will probably provide a
> better way, I hope, but this should at least work)
>
> select date as first,
> (select date from table t3 where t3.date<(select date from table t5
> where t5.date>t1.date and t5.bin<>t1.bin order by date asc limit 1)
> order by date desc limit 1) as last,
> bin
> from table t1 where (select bin from table t2 where t2.date order by date desc limit 1)<>t1.bin;
>
> Ugly, and I'm pretty sure there's a much better way, but my brain is
> failing me right now- hopefully this'll at least get you started,
> though.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Select ranges based on sequential breaks
On Tue, 23 Jun 2009 04:41:44 +1000, Mike Toews wrote: Window functions appear to be the best solution for this style of problem, and I'm looking forward to their applications. However, I'm sticking with 8.3 for at least a year, so I'm not able to explore this solution yet. For now, I can only post-process the output in a non-SQL environment. I also need to do other fun stuff, like cumulative sums, which is also challenging with SQL, but much easier and intuitive with R. As a largely procedural programmer, the PL/SQL solution is quite appealing to me, and would be similarly simple to calculate cumulative sums. The integration of SELECT statements within PL/SQL also seems much tighter than with other PL languages. Unfortunately, one can't send a cursor or a set of results directly as a PL argument. I'm having a skim through Celko's chapter 24, but it doesn't seem to be close to my needs either. On Tue, 23 Jun 2009 08:05:14 +1000, Mike Toews wrote: ... # Determine where the rows are different; 1=different rows, 0=same rows dat$breaks <- ifelse(dat$bin != c(TRUE, as.character(dat$bin[-nrow(dat)])), 1, 0) # Determine where the continuous parts are: dat$part <- factor(cumsum(dat$breaks)) Yes, as far as I can tell, this is almost identical to my WINDOW-based solution in finding when there is a change, marking it with 0 or 1 and the using cumulative sum to number the partitions. This could be similarly done in PL/SQL but it seemed more sensible to just do the whole thing rather than using GROUP BY after enumeration. - Joel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL?
On Tue, Jan 21, 2014 at 1:34 AM, Maciej Szopinski wrote:
> Hello,
>
> Does PostgreSQL provide any notation/method for putting a constraint on
> each element of a JSON array?
>
> An example to illustrate:
>
>
> [...]
> I know that this can be done by extracting products to a separate table
> with a foreign key to orders. But I want to know if this is possible within
> single JSON column, so I can keep that in mind when designing a database
> schema.
>
>
If you write a short function to help, it's possible. You would need a
subquery to make this assertion, but you can't add one directly as a check
constraint:
create table orders (data JSON);
alter table orders add check (1 <= ALL((select
array_agg((a->>'product_id')::integer) from
json_array_elements(data->'products') as a)));
ERROR: cannot use subquery in check constraint
create function data_product_ids(JSON) returns integer[] immutable as $$
select array_agg((a->>'product_id')::integer) from
json_array_elements($1->'products') as a $$ language sql ;
CREATE FUNCTION
alter table orders add check (1 <= ALL(data_product_ids(data)));
ALTER TABLE
insert into orders (data) values ('{"products": [{ "product_id":1 }, {
"product_id":2 }]}');
INSERT 0 1
insert into orders (data) values ('{"products": [{ "product_id":0 }, {
"product_id":2 }]}');
ERROR: new row for relation "orders" violates check constraint
"orders_data_check"
DETAIL: Failing row contains ({"products": [{ "product_id":0 }, {
"product_id":2 }]}).
[GENERAL] optimizing a cpu-heavy query
Folks, I'm trying to optimize the following query that performs KL Divergence [1]. As you can see the distance function operates on vectors of 150 floats. The query takes 12 minutes to run on an idle (apart from pgsql) EC2 m1 large instance with 2 million documents in the docs table. The CPU is pegged at 100% during this time. I need to be able to both process concurrent distance queries and otherwise use the database. I have the option of moving this distance calculation off of PG but are there other options? Is there anything clearly wrong that I'm doing here? Would it speed things up to make the float array a custom data type backed by C code? Thanks in advance, Joel [1] http://en.wikipedia.org/wiki/Kullback%E2%80%93Leibler_divergence --- CREATE DOMAIN topics AS float[150]; CREATE DOMAIN doc_id AS varchar(64); CREATE TABLE docs ( id serial, doc_id doc_id NOT NULL PRIMARY KEY, topics topics NOT NULL ); CREATE OR REPLACE FUNCTION docs_within_distance(vec topics, threshold float) RETURNS TABLE(id doc_id, distance float) AS $$ BEGIN RETURN QUERY SELECT * FROM (SELECT doc_id, (SELECT sum(vec[i] * ln(vec[i] / topics[i])) FROM generate_subscripts(topics, 1) AS i WHERE topics[i] > 0) AS distance FROM docs) AS tab WHERE tab.distance <= threshold; END; $$ LANGUAGE plpgsql; -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] tuning on ec2
I'm running pgsql on an m1.large EC2 instance with 7.5gb available memory.
The free command shows 7gb of free+cached. My understand from the docs is that
I should dedicate 1.75gb to shared_buffers (25%) and set effective_cache_size
to 7gb.
Is this correct? I'm running 64-bit Ubuntu 10.10, e.g.
Linux ... 2.6.35-28-virtual #50-Ubuntu SMP Fri Mar 18 19:16:26 UTC 2011 x86_64
GNU/Linux
Thanks, Joel
--
- for hire: mac osx device driver ninja, kernel extensions and usb drivers
-++---
http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont
-++---
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tuning on ec2
On Apr 26, 2011, at 4:31 PM, Scott Marlowe wrote: > It's a reasonable start. However, if you consistently using less than > that in aggregate then lowering it is fine. Is there a way to tell if I consistently use less than that in aggregate? > What's your work_mem and max_connections set to? I have the default settings, e.g. work_mem = 1MB and max_connections = 100. I'm looking to process 400 requests per second, though. What should I use for the above? Thanks, Joel -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] optimizing a cpu-heavy query
Tom, On Apr 26, 2011, at 5:00 PM, Tom Lane wrote: > For another couple orders of magnitude, convert the sub-function to C code. > (I don't think you need > a whole data type, just a function that does the scalar product.) That's a 30x speedup, from 12 minutes down to 38s. Thanks Tom! -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Column storage (EXTERNAL/EXTENDED) settings for bytea/text column
Thanks for the help with that, Noah. Indeed the sizes do look like I'd expect them to if I force deflation of the bytea value before inserting it into the EXTENDED column. On Apr 21, 2011, at 2:02 PM, Noah Misch wrote: > On Mon, Apr 11, 2011 at 03:19:23PM -0700, Joel Stevenson wrote: >> create table obj1 ( object bytea ); >> create table obj2 ( object bytea ); >> alter table obj2 alter column object set storage external; >> insert into obj1 ( object ) select object from serialized_content where id = >> 12345; >> insert into obj2 ( object ) select object from obj1; > > If the value that shows up for insertion is already compressed, EXTERNAL > storage > will not decompress it. Change this line to > > insert into obj2 ( object ) select object || '' from obj1; > > to observe the effect you seek. > > Given the purpose of EXTERNAL storage, this might qualify as a bug. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] histogram
I have a column of 2 million float values from 0 to 1. I would like to figure out how many values fit into buckets spaced by 0.10, e.g. from 0 to 0.10, from 0.10 to 0.20, etc. What is the best way to do this? Thanks, Joel -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] histogram
Thank you Thomas! Is there a way for the code below to determine the number of rows in the table and use it? Thanks, Joel On Apr 30, 2011, at 5:48 PM, Thomas Markus wrote: > Hi, > > try something like this: > > select >trunc(random() * 10.)/10. >, count(*) > from >generate_series(1,200) > group by 1 order by 2 -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] histogram
What is the meaning of group by 1 order by 2 e.g. what to the numbers 1 and 2 stand for? What would change if I do the following? group by 1 order by 1 On Apr 30, 2011, at 5:48 PM, Thomas Markus wrote: > Hi, > > try something like this: > > select >trunc(random() * 10.)/10. >, count(*) > from >generate_series(1,200) > group by 1 order by 2 -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] histogram
I think this should do what I want select trunc(distance * 10.)/10., count(*) from doc_ads group by 1 order by 1 Thanks, Joel -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psql \s not working - OS X
I'm trying the new 9.1b2 release and got a weird problem with the \s command to show the latest history, for some reason it's trying to write to /dev/tty instead of printing out to stdout: Joel-Jacobsons-MacBook-Pro:~ joel$ uname -a Darwin Joel-Jacobsons-MacBook-Pro.local 10.7.4 Darwin Kernel Version 10.7.4: Mon Apr 18 21:24:17 PDT 2011; root:xnu-1504.14.12~3/RELEASE_X86_64 x86_64 Joel-Jacobsons-MacBook-Pro:~ joel$ export declare -x Apple_PubSub_Socket_Render="/tmp/launch-pOTElL/Render" declare -x COMMAND_MODE="unix2003" declare -x DISPLAY="/tmp/launch-QNeAJR/org.x:0" declare -x HOME="/Users/joel" declare -x LC_CTYPE="UTF-8" declare -x LOGNAME="joel" declare -x OLDPWD="/Users/joel/Downloads/postgresql-9.1beta2" declare -x PATH="/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/local/git/bin:/opt/local/bin:/usr/X11/bin" declare -x PWD="/Users/joel" declare -x SHELL="/bin/bash" declare -x SHLVL="1" declare -x SSH_AUTH_SOCK="/tmp/launch-ejCA6e/Listeners" declare -x TERM="xterm-color" declare -x TERM_PROGRAM="Apple_Terminal" declare -x TERM_PROGRAM_VERSION="273.1" declare -x TMPDIR="/var/folders/FZ/FZOPIjkcF2GR0xFiNEkxME+++TI/-Tmp-/" declare -x USER="joel" declare -x __CF_USER_TEXT_ENCODING="0x1F5:0:0" Joel-Jacobsons-MacBook-Pro:~ joel$ /Library/PostgreSQL/9.1b2/bin/psql glue psql (9.1beta2) Type "help" for help. glue=# SELECT version(); version - PostgreSQL 9.1beta2 on x86_64-apple-darwin10.7.4, compiled by i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5666) (dot 3), 64-bit (1 row) glue=# \s could not save history to file "/dev/tty": Operation not permitted glue=# -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming replication and temp table operations
Hi all, Does anyone know if temp tables and the operations on them (like inserting or copying to) are replicated from master to standby servers via the new PG WAL shipping replication? Given that temp tables are only accessible per-session it would seem unnecessary but if the shipping is happening at the WAL log level is that sort of thing considered? Specifically I've got a table that I want to get some representative statistics and explain plans on prior to making it live on a production environment and so I was considering creating a temp table to populate with a sizable chunk of representative test data on the master database installation. The Streaming Replication docs ( 25.2.5 ) state: "If you use streaming replication without file-based continuous archiving, you have to set wal_keep_segments in the master to a value high enough to ensure that old WAL segments are not recycled too early, while the standby might still need them to catch up. If the standby falls behind too much, it needs to be reinitialized from a new base backup. If you set up a WAL archive that's accessible from the standby, wal_keep_segments is not required as the standby can always use the archive to catch up." Which, in the streaming replication w/o file-based continuous archiving scenario, seems like I'd want to be absolutely certain that this setting was big enough to handle whatever data was being imported into the temp table via a COPY...FROM even if the actual table wasn't being replicated. Does anyone know if this is a valid concern and whether or not the temp table will be replicated (regardless of the use of file-based continuous archiving)? Thanks in advance, Joel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Finding all 8.4->9.x incompatible PL/pgSQL functions with conflicting parameter/column names causing "column reference ... is ambiguous" errors
I'm upgrading from 8.4 to 9.1, and have a lot of PL/pgSQL functions which works in 8.4, but when called, throws an error in 9.1. Example: CREATE TABLE mytable (id serial not null primary key, value text); INSERT INTO mytable (id, value) VALUES (1, 'foo'); INSERT INTO mytable (id, value) VALUES (2, 'bar'); CREATE OR REPLACE FUNCTION myfunc(id int) RETURNS TEXT AS $$ DECLARE value text; BEGIN SELECT mytable.value INTO value FROM mytable WHERE mytable.id = id; RETURN value; END; $$ LANGUAGE plpgsql; SELECT myfunc(1); SELECT myfunc(2); This returns "foo" and "bar" like expected in 8.4, but in 9.1 I get "column reference "id" is ambiguous", "It could refer to either a PL/pgSQL variable or a table column.". This is of course easy to fix by qualifying id with the name of the function: -SELECT mytable.value INTO value FROM mytable WHERE mytable.id = id; +SELECT mytable.value INTO value FROM mytable WHERE mytable.id = myfunc.id; The problem is, how can I find all functions which have this problem? You don't get this error when creating the functions, only when running them and hitting a statement where there is a conflict. Would it be possible to somehow automatically scan through all functions and getting a list of the functions which have this problem? Thanks! Best regards, Joel Jacobson
Re: [GENERAL] Add a check an a array column
More concisely, you can compare directly against all values of the array: # create table i (i int[] check (0 <= ALL(i) AND 1023 >= ALL(i))); # insert into i values (ARRAY[0,1,2,3,1023]); # insert into i values (ARRAY[0,1,2,3,-1]); ERROR: new row for relation "i" violates check constraint "i_i_check" # insert into i values (ARRAY[0,1,2,3,1024]); ERROR: new row for relation "i" violates check constraint "i_i_check" Joel On Sat, Sep 8, 2012 at 8:31 AM, Andreas Kretschmer < akretsch...@spamfence.net> wrote: > test=# create or replace function check_array(int[]) returns bool as > $declare i int; begin select into i max(unnest) from unnest($1); if i > 10 > then return false; end if; return true; end$ language plpgsql ; > CREATE FUNCTION > Time: 0,579 ms > test=*# create table a (i int[] check (check_array(i))); > CREATE TABLE > Time: 6,768 ms > test=*# insert into a values (array[1,2,3]); > INSERT 0 1 > Time: 0,605 ms > test=*# insert into a values (array[1,2,30]); > ERROR: new row for relation "a" violates check constraint "a_i_check" >
Re: [GENERAL] Rank based on the number of matching OR fields?
If you're easily able to do it, (i.e. you're building rather than receiving
the query), you could rank them by the conjunction of the search terms
first:
ORDER BY ts_rank(vector, to_tsquery('A & B & C')) desc, ts_rank(vector,
to_tsquery('A | B | C')) desc
Or just explicitly order by whether the conjunction matches:
ORDER BY case when to_tsquery('A & B & C') @@ vector then 0 else 1
end, ts_rank(vector, to_tsquery('A | B | C')) desc
I think either of these would have the property you want, but I don't know
how they would otherwise affect the quality of the ranking. You should set
up a test group of documents and make sure your mechanism ranks that group
properly on test queries.
Joel
On Tue, Sep 25, 2012 at 11:16 AM, W. Matthew Wilson wrote:
> I want to run a query like to_tsquery("A | B | C") and then rank the
> results so that if a document contained A, B, and C, then it would
> rank above a document that just had some subset.
>
> How would I do such a thing?
>
> --
> W. Matthew Wilson
> m...@tplus1.com
> http://tplus1.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
[GENERAL] How to access insert/update counts for aborted subtransaction (or current transaction)
Hi, I am working on adding some functionality to pgTAP, in order to detect unintended data modification side-effects. The idea is to, 1. store the insert/update counts for all tables in a temporary table, 2. then run the test unit in a subtransaction, 3. then compare the new insert/update counts for all tables, 4. in addition to all the normal tests in the test unit, I will also keep a lookup table of how many inserts/updates to expect per table for each test unit. I noticed pg_stat_clear_snapshot() doesn't affect pg_stat_get_tuples_inserted() within the same transaction. Is there some other clever way of figuring out how many tuples were updated/inserted per table by the current transaction? Alternatively, some way to force the top level transaction system to report the statistics for the current transaction? I understand the clear benefits of making sure all the stat functions always return the same results in the same transaction, this is of course what you usually want to happen, but in my case I want the ability to override this. Thankful for any ideas. -- Best regards, Joel Jacobson Glue Finance E: j...@gluefinance.com T: +46 70 360 38 01 Postal address: Glue Finance AB Box 549 114 11 Stockholm Sweden Visiting address: Glue Finance AB Birger Jarlsgatan 14 114 34 Stockholm Sweden
[GENERAL] [KB] Information required - Thanks
Hi, I am trying to formulate a checklist for securing or securely configuring the PostgreSQL DB. Wondering if you already have some thing in place or perhaps a hardening document. Could you point me to some place where i could find this. Thanks, Joel
[GENERAL] Trouble connecting to database using PQconnectdb (C/libpq)
Hello -
I am working on moving my WEB pages, which include searches in a PostgreSQL
data base, through "libpq".
Old system: Red Hat Linux 9, PostgreSQL 7.4.6
New system: Fedora 11, PostgreSQL 8.4
When I re-compile the program (using updated gnu C++ compilers/libs),
the new version --will-- connect to the data base if I run the CGI program
from the command line, but will --NOT-- connect if I call the CGI program
from a web page.
Here is the C/C++ code:
--
const char *pcDbArgs = "user=joela dbname=photodb port=5432
host=192.168.111.7";
const char *pcDbName = "photodb";
//
// Connect to PostGresQL data base
//
printf("\nCalling PQconnectdb(%s)\n", pcDbArgs);
gpPhotoDb = PQconnectdb (pcDbArgs);
if (PQstatus(gpPhotoDb) != CONNECTION_OK)
vExit("Can't connect to database \"%s\" - %s\n", pcDbName,
PQerrorMessage(gpPhotoDb));
printf("Successful connection!\n\n");
(vExit is a simple function that formats an error message)
When run from an HTML page:
Error message:
--
Calling PQconnectdb(user=joela dbname=photodb port=5432 host=192.168.111.7)
*** Error - Can't connect to database "photodb" - could not connect to server:
Permission denied
Is the server running on host "192.168.111.7" and accepting
TCP/IP connections on port 5432?
I have verified that the postgresql server --is-- running on the server
machine, and --is-- listening on 5432.
I am sure I am leaving something out, so please write if you need more info.
Any thoughts?
Thanks!
Joel.
--
++
| Joel K. Alpers
| Expressive Black and White Images
| http://www.jkalpers.com
++
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trouble connecting to database using PQconnectdb (C/libpq)
Tom Lane wrote: Joel Alpers writes: New system: Fedora 11, PostgreSQL 8.4 Fedora 11 enables selinux by default ... *** Error - Can't connect to database "photodb" - could not connect to server: Permission denied Is the server running on host "192.168.111.7" and accepting TCP/IP connections on port 5432? Almost certainly, this represents selinux thinking that apache shouldn't be making connections to random ports, and denying the socket open long before it ever has a chance to get to postgres. If you poke around in the selinux configuration options you can probably find a switch that opens this up, but I don't know offhand what it is. Or you could disable selinux, but if you're running a publicly visible webserver I'd strongly recommend against that. selinux will save your bacon someday, but not if it's turned off. regards, tom lane Tom: Thanks for the quick reply - looks like you were spot on with your analysis. I temporarily set selinix to "permissive" and the web page works as it should. Now the trick will be to find a way to make them play well together -- I'll check the postgres site where they have discussions archived... Thanks again!!! Joel. -- ++ | Joel K. Alpers | Expressive Black and White Images | http://www.jkalpers.com ++ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trouble connecting to database using PQconnectdb (C/libpq)
John R Pierce wrote: Now the trick will be to find a way to make them play well together -- I'll check the postgres site where they have discussions archived... you'll probably get more help from a selinux site or list. Yes, after I sent that reply I recalled that I was --on-- a postgres list --- sorry, it's been a long few days fighting software. I meant to say I would check on the Fedora forum! Joel. -- +----+ | Joel K. Alpers | Expressive Black and White Images | http://www.jkalpers.com ++ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Synchronous replication + Fusion-io = waste of money OR significant performance boost? (compared to normal SATA-based SSD-disks)?
My company is in the process of migrating to a new pair of servers, running 9.1. The database performance monetary transactions, we require synchronous_commit on for all transactions. Fusion-io is being considered, but will it give any significant performance gain compared to normal SATA-based SSD-disks, due to the fact we must replicate synchronously? To make it more complicated, what about SLC vs MLC (for synchronous replication)? Assume optimal conditions, both servers have less than a meter between each other, with the best possible network link between them providing the lowest latency possible, maxed out RAM, maxed out CPUs, etc. I've already asked this question to one of the core members, but the answer was basically "you will have to test", I was therefore hoping someone in the community already had some test results to avoid wasting money. Thank you for any advice! Best regards, Joel Jacobson Trustly Group AB (former Glue Finance AB) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Which SQL is the best for servers?
On Feb 15, 10:09 pm, pg wrote: > I am involved with a SQL server project. The server would be used in a > very heavy duty environment, with hundreds of thousands, if not > millions of database enquiries per minutes. > > The server would run Linux or one of the BSD variant, with at least > 32GB of RAM. We are not very certain of the hardware specs yet because > we haven't decided on which SQL to use. > > I know that Oracle, MySQL and PostgreSQL are all designed for heavy > duty uses. > > And I checked all available online resources for a SQL comparison and > all I could find is some articles dated 2005 or so ! > > So, here's my questions: > > 1. Are there any recent SQL comparison article available? > > 2. Since the server may come with only 32GB of RAM, which SQL can run > the "leanest" - that is, not a memory hog? > > 3. The server might also become a web-server, which SQL can tie itself > to the Web-based enquiry they best? > > Please give me your suggestion / opinion. Thank you !! I agree with those who say you are going about this backwards. No-way no-how will a single open source os box handle millions of "enquiries" per minute. Sounds to me like someone has an idea for some web page they think is going to attract a gazillion users. This is what I predict: You will spend $5 million over several years financed via a shoot-the-moon business plan designed to attract venture capital. At the end, it will all fizzle out after numerous rounds of layoffs. It wouldn't be the first time. Good luck in this economic environment. That's as good a prediction as any given this amount of information. Unless the domain you are posting from indicates some religious thing. Some religious databases use Oracle. As to the database engine: There is a reason I'm strongly biased towards Oracle. That reason being, unless there is something special about the processing involved, either exceedingly simple, complex or specialized, it is near impossible to build a bespoke system cheaper than buy and modify off the shelf software. For business systems, ACID is a very important consideration, and Oracle simply handles the concurrency issues better for most business processes. Google, for a big example, doesn't care about concurrency issues, because their business model is simply to approximate eyeballing of ads - and they don't have to account for how they measure it. This happens to be evil. In general, it takes about an order of magnitude more people to write and maintain a custom system than implement an off the shelf system. Given the cost of a large implementation this can make sense for open source - or not. The maintenance costs are often underestimated either way, but moreso in customization. When a startup starts to transition to a business, variable costs are cut. That would be you. Two guys and a box? I've seen that work, where a huge classical development failed - more than once. It depends. Most startups fail. Check out Oracle XE and apex. No cost to you, and you can pay to scale as appropriate. jg -- @home.com is bogus. http://www3.signonsandiego.com/stories/2009/feb/16/1b16twitter19224-whats-twitters-fiscal-fate/?uniontrib -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Which SQL is the best for servers?
On Feb 16, 11:12 am, Paulie wrote: > On Feb 16, 5:51 pm, joel garry wrote: > > > Check out Oracle XE and apex. No cost to you, and you can pay to > > scale as appropriate. > > Before rushing to download Oracle XE, check out > > http://www.oracle.com/technology/pub/articles/cunningham-database-xe > > Limitations. > 1 GB of RAM (OP has 32), > 1 CPU (with 32GB of RAM?) and a > 4GD data limit. > > For millions of queries per hour? For POC of an app, this is fine, > however for > performance testing, it's a non-runner. I guess I wasn't clear enough on the "and you can pay to scale as appropriate." For testing/development purposes, you can download the various editions of Oracle and see what they can do. The XE/Apex (or whatever development environs) is just for getting something working quick. When you see what the other editions can do, then you decide what you need - plus you can decide on the low end, not a big deal to move up if the situation warrants. The patching issue Troels mentioned may or may not make a difference for a production environment exposed to the world, but I'm not advocating XE for this in production, just for developing. Of course, one usual screwup is testing time/volume of rows returned, where some toy db can outperform Oracle. Real performance testing requires realistic load tests, and that can be a lot of work, especially for a small group with one box. > > You are allowed AFAIK, download the full server for testing (but not > deployment). The OP hasn't really given the group enough information > about the system for anyone here to be able to answer any > serious questions about an app that's (supposedly) going > to be almost as busy as Google! I think we may all agree on this! > > No CPU data, no disk array data - they haven't even chosen > an OS and are not sure where to put their web server (and > no mention of an app server tier!). Since they seem uncertain of actual volume, all these things need to be put in terms of a scalability plan. > > Maybe they should run with the mauve db? > With scissors! jg -- @home.com is bogus. http://www3.signonsandiego.com/stories/2009/feb/02/1m2ferry22928-robert-g-ferry-air-force-veteran-was/?uniontrib -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] seg fault with tsearch2
I have Pg installed on i386 NetBSD from the NetBSD package manager. I
then installed tsearch2 and used its default installation.
I then also got the source and built from it, with debugging enabled.
Again I installed tsearch2 but my results were the same.
Created a new database, added a table with:
CREATE TABLE server_file (
server_file_id serial NOT NULL,
server_id integer NOT NULL,
server_file_path text NOT NULL,
server_file_name text NOT NULL,
server_file_ext character varying(20),
server_file_size integer NOT NULL,
server_file_index tsvector,
protocol_id integer NOT NULL
);
ALTER TABLE ONLY server_file
ADD CONSTRAINT server_file_pkey PRIMARY KEY (server_file_id);
CREATE INDEX server_file_indexindex ON server_file USING gist
(server_file_index);
CREATE TRIGGER tsvectorupdate
BEFORE INSERT OR UPDATE ON server_file
FOR EACH ROW
EXECUTE PROCEDURE tsearch2('server_file_index',
'server_file_name', 'server_file_path');
---Query executed:
insert into server_file values (default,'511','/test/20-Shania
Twain-Party For Two with Billy
Curringtonmp3','mp3','323',default,'1');
---Output from gdb
Program received signal SIGSEGV, Segmentation fault.
0x48b22c84 in find_among () from /usr/pkg/lib/tsearch2.so
#0 0x48b22c84 in find_among () from /usr/pkg/lib/tsearch2.so
#1 0x48b21d02 in ts_stat () from /usr/pkg/lib/tsearch2.so
#2 0x48b21e58 in english_stem () from /usr/pkg/lib/tsearch2.so
#3 0x48b14383 in snb_lexize () from /usr/pkg/lib/tsearch2.so
#4 0x081cdf59 in FunctionCall3 (flinfo=, arg1=137034080,
arg2=137063728, arg3=11) at fmgr.c:1164
#5 0x48b17bf5 in parsetext_v2 () from /usr/pkg/lib/tsearch2.so
#6 0x48b1a5e4 in tsearch2 () from /usr/pkg/lib/tsearch2.so
#7 0x080f25c7 in ExecCallTriggerFunc (trigdata=0xbfbff670,
finfo=, per_tuple_context=)
at trigger.c:1149
#8 0x080f2885 in ExecBRInsertTriggers (estate=,
relinfo=, trigtuple=) at trigger.c:1261
#9 0x081032dd in ExecInsert (slot=,
tupleid=, estate=) at execMain.c:1344
#10 0x08102f9e in ExecutePlan (estate=,
planstate=, operation=, numberTuples=0,
direction=, dest=) at execMain.c:1207
#11 0x081023d6 in ExecutorRun (queryDesc=0x8349440,
direction=, count=0) at execMain.c:226
#12 0x0816d629 in ProcessQuery (parsetree=, plan=,
params=, dest=, completionTag=0xbfbff910 "")
at pquery.c:173
#13 0x0816e385 in PortalRunMulti (portal=, dest=,
altdest=, completionTag=0xbfbff910 "") at pquery.c:1016
#14 0x0816dd76 in PortalRun (portal=, count=2147483647,
dest=, altdest=, completionTag=0xbfbff910 "")
at pquery.c:617
#15 0x0816a76a in exec_simple_query (query_string=)
at postgres.c:933
#16 0x0816c979 in PostgresMain (argc=4, argv=,
username=) at postgres.c:3007
#17 0x0811b4ed in main (argc=4, argv=) at main.c:334
#18 0x08072122 in ___start ()
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] seg fault with tsearch2
Ah ok. I must have copied it incorectly. I am using 8.0.1 from the latest binary build from NetBSD. The source I used was postgresql-8.0.1.tar.bz2 stable release. I will try the CVS release to see if it's fixed. --Joel On Apr 7, 2005 12:26 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Joel Leyh <[EMAIL PROTECTED]> writes: > > insert into server_file values (default,'511','/test/20-Shania > > Twain-Party For Two with Billy > > Curringtonmp3','mp3','323',default,'1'); > > I get > ERROR: null value in column "server_file_size" violates not-null constraint > which I think indicates you mistranscribed your test query. I changed > it to > > regression=# insert into server_file values (default,'511','/test/','Shania > regression'# Twain-Party For Two with Billy > regression'# Curringtonmp3','mp3','323',default,'1'); > INSERT 155964 1 > > and as you can see I didn't get a crash. I'm testing with 8.0.2-to-be > (ie, 8.0 branch CVS tip). Teodor applied some tsearch2 fixes just last > week, so it might be that he fixed your bug --- what version are you > using exactly? > > regards, tom lane > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] seg fault with tsearch2
I compiled 8.0.1 on linux with --with-debug only, and it seemed to
work. I had also compiled 8.0.1 on Netbsd with the same configure
option. I removed the tsearch trigger from the table and I had no
problem. I tried using to_tsvector directly and the server crashed.
PostgreSQL stand-alone backend 8.0.1
backend> select to_tsvector('foo blah blah');
Program received signal SIGSEGV, Segmentation fault.
0x48b22c84 in find_among () from /usr/pkg/lib/tsearch2.so
Yet on a linux computer with the source compiled the EXACT same
way(--with-debug)
PostgreSQL stand-alone backend 8.0.1
backend> select to_tsvector('foo blah blah');
1: to_tsvector (typeid = 17359, len = -1, typmod = -1, byval = f)
1: to_tsvector = "'foo':1 'blah':2,3" (typeid = 17359, len =
-1, typmod = -1, byval = f)
backend>
So, the only conclusion I can reach is this problem is some OS
dependant bug, which apparently has been fixed in 8.0.2beta.
--Joel
On Apr 7, 2005 9:04 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Joel Leyh <[EMAIL PROTECTED]> writes:
> > Same problem, same function causing the seg fault. I also tried to
> > narrow down what about the filename is causing the error, but I
> > couldn't figure it out. I also compiled the source on another NetBSD
> > i386 machine, with the same results.
> > Then I compiled 8.0.2beta1 and the above query works just fine. So
> > whatever was causing the problem apparently has been fixed.
>
> Well, that's good news, but it still bothers me that I can't reproduce
> the problem here, and I don't see anything in the CVS logs that looks
> like a fix. I went back to 8.0.1 sources just to be sure, but it worked
> fine. Apparently something platform-specific?
>
> What configure options did you use?
>
> regards, tom lane
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] tsearch2 trigger
I am writing a tsearch2 trigger function in plperl. I would like to
run a query similar to this:
setweight(to_tsvector(col1),'b') || setweight(to_tsvector(col2),'a')
and insert the result into the tsvector column. I know I can call
spi_exec_query('select ...') and insert the result, but is this the
best way of doing it? I need the trigger to be plperl since there is
some additional foo I am doing.
Thanks! --Joel
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [ADMIN] [GENERAL] postgres & server encodings
Not that I am an expert or anything, but my initial data base was SQLASCII and I did have to convert it to Unicode. My reasons were we store French characters in our database and the newer odbc driver was not displaying them correctly coming from SQLASCII, but was from UNICODE. I also think that it can affect functions like length and upper, but Tom knows a ton more then me about this stuff. I did my initial conversion on 7.4 and the odbc driver at that time had no issues with SQLASCII displaying the French, but I think in 8.0.1 I started seeing an issue. The latest version of the driver 8.0.4 seems to be working well (only up a little over 24 hours thus far). I wish I had used a unicode data base from the start (7.4 driver was what I used and it did not like moving from MSSQL to Unicode). I later switched to .net (npgsql objects) for my conversion and used a encoding object to write the data correctly. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Tuesday, August 09, 2005 11:59 AM To: Salem Berhanu Cc: pgsql-admin@postgresql.org; pgsql-general@postgresql.org Subject: Re: [ADMIN] [GENERAL] postgres & server encodings "Salem Berhanu" <[EMAIL PROTECTED]> writes: > What exactly is the SQL_ASCII encoding in postgres? SQL_ASCII isn't so much an encoding as the declaration that you don't care about encodings. That setting simply disables encoding validity checks and encoding conversions. The server will take any byte string clients send it (barring only embedded zero bytes), and store and return it unchanged. Since it disables conversions, the notion of converting to another encoding is pretty much meaningless :-(. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Determining caller of a function (due to a cascaded FK constraint?)
Title: Determining caller of a function (due to a cascaded FK constraint?) Greetings, For reasons a little too complicated to get into off the bat, I am wondering what the most effective way is to determine by whom or how a particular action or function call was initiated. To shed more light, I want to have a trigger that will copy some data from a table in one schema to an analogous table in another schema every time a record is modified UNLESS the modification is the result of a cascaded foreign key constraint. My hunch is that the answer somehow includes using data in pg_class and/or pg_proc, but I haven't quite pieced it all together. Does anyone have any recommendations on how to go about this? Thanks, JL
[GENERAL] Trouble with plpgsql generic trigger function using special variables
I'd like to create a trigger function whose use can extend to multiple tables by employing the special variables available (e.g., TG_RELNAME). Below is a simple version of such a function that ought to prevent insertion of greater than 4 total records in the table that calls it. I'm not sure that I'm using or dereferencing the trigger variables correctly, however, particularly in the query. I have tried many syntax, type casting, and alternate variable assignment variations, but, aside from parsing successfully, this code does not seem to work as intended.Can somebody correct this specific example to have it work properly and/or further explain how to use these variables? Any advice on outputting the values of the variables to the console for inspection during testing would be welcome as well (RAISE EXCEPTION doesn't allow a variable value in the message string, plus it seems a little harsh). Thanks, JL CREATE OR REPLACE FUNCTION trigger_fxn() RETURNS TRIGGER AS $$ BEGIN IF ((TG_OP = 'INSERT') AND (TG_WHEN = 'BEFORE')) THEN IF (SELECT COUNT(*) FROM text(TG_RELNAME)) < 4 THEN RETURN NEW; ELSE RETURN NULL; END IF; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER test_bi BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE trigger_fxn(); ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Help on Update Rule for View
Howdy, I'm trying to create a schema in which there will be simple a view for each table that will have the same columns and can be acted on in the same way as the underlying table An example of one table and its view would be: CREATE TABLE test (id int, text_field varchar(100)); CREATE VIEW _test AS SELECT * FROM test; I'd like to be able to create both the views and the insert, update, delete rules for the views in an automated fashion via a script that uses the information schema to get all of the table names. All is fine and good with the insert and delete rules and no problem to automatically generate this: CREATE RULE _test_oi_rule AS ON INSERT TO _test DO INSTEAD INSERT INTO test VALUES (NEW.*); CREATE RULE _test_od_rule AS ON DELETE TO _test DO INSTEAD DELETE FROM test WHERE id = OLD.id; However, I'm not sure how to create the update rule without having to go through the gory task of specifying each column by name. Yes, I could also use the information schema to automate this as well, but it just seems ugly. Is there any way to create an update rule that's something like this: CREATE RULE _test_ou_rule AS ON UPDATE TO _test SET test.* = NEW.*; -- or even better a command that will only update changed columns (i.e., WHERE NEW.* <> OLD.*) I imagine I could instead delete the old record and insert the new one, but that doesn't seem right either and seems like could be perilous. Maybe I'm overlooking something obvious, but any help to find a nice clean solution would be appreciated. Thanks, JL ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Help with Update Rule on View - 2nd Attempt
I tried to post this the other day, but didn't get any responses and never saw it show up in the digest. Here it is again if anyone can offer any insight: I'm trying to create a schema in which there will be simple a view for each table that will have the same columns and can be acted on in the same way as the underlying table An example of one table and its view would be: CREATE TABLE test (id int, text_field varchar(100)); CREATE VIEW _test AS SELECT * FROM test; I'd like to be able to create both the views and the insert, update, delete rules for the views in an automated fashion via a script that uses the information schema to get all of the table names. All is fine and good with the insert and delete rules and no problem to automatically generate this: CREATE RULE _test_oi_rule AS ON INSERT TO _test DO INSTEAD INSERT INTO test VALUES (NEW.*); CREATE RULE _test_od_rule AS ON DELETE TO _test DO INSTEAD DELETE FROM test WHERE id = OLD.id; However, I'm not sure how to create the update rule without having to go through the gory task of specifying each column by name. Yes, I could also use the information schema to automate this as well, but it just seems ugly. Is there any way to create an update rule that's something like this: CREATE RULE _test_ou_rule AS ON UPDATE TO _test SET test.* = NEW.*; -- or even better a command that will only update changed columns (i.e., WHERE NEW.* <> OLD.*) I imagine I could instead delete the old record and insert the new one, but that doesn't seem right either and seems like could be perilous. Maybe I'm overlooking something obvious, but any help to find a nice clean solution would be appreciated. Thanks, JL ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Metadata from NEW and OLD constructs?
Greetings, I was wondering if it's possible to get any of the metadata from the NEW and OLD constructs in a trigger or view rule? Specifically, I'd like to get the column name or identifier anywhere the new record differs from the old record (i.e. NEW.column_X <> OLD.column_X). Any advice would be greatly appreciated. Thanks, JL
[GENERAL] Pltcl error - could not create "normal" interpreter
Greetings, Could somebody shed any light on the error message below that came from trying to call a simple pltcl test function? I am running Postgres 8.1 on WinXP and just recently added the pltcl language by copying Tcl84.dll into my system directory (C:/Windows/System32) and successfully issuing the command: > createlang pltcl -U db_admin postgres What else do I need to do or configure to enable this full functionality? postgres=# create or replace function test_fxn() returns void as $$ postgres$# spi_exec "SELECT * FROM test" postgres$# $$ language pltcl; CREATE FUNCTION postgres=# select test_fxn(); ERROR: could not create "normal" interpreter postgres=# Any help is greatly appreciated and the little that's in the archives/www is fairly abstruse. Thanks, JL ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] GUI tool that can reverse engineering schemas
I've been using a product called HappyFish, which does reverse engineering on Postgres and has proven to be a great DB development tool. While it's not free, it is very low cost and you can easily get a full-featured evaluation version to try out. I've been running it through its paces with a pretty complex Postgres project and I'm really pleased. The product is maturing and getting more capable all the time and responsiveness on part of the development team is excellent. Check it out here: http://www.polderij.nl/happyfish/ -Original Message- From: nyenyec [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 6:26 PM To: pgsql-general@postgresql.org Subject: GUI tool that can reverse engineering schemas Hi, Can anyone suggest a free GUI tool that can reverse engineer a postgresql schema and show it as a diagram? It doesn't have to be very sophisticated, I just need to get a quick understanding of schemas that I'm not familiar with. Thanks, nyenyec ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Temp Table Within PLPGSQL Function - Something Awry
Greetings, I am trying to work with a TEMP TABLE within a plpgsql function and I was wondering if anyone can explain why the function below, which is fine syntactically, will work as expected the first time it is called, but will err out as shown on subsequent calls. The DROP TABLE line seems to be executing (note \d results on temp_tbl), and repeatedly adding/dropping/querying temp_tbl from the command line also works without a problem. However, when it's all put into the function and cycled through multiple times then something seems to be getting confused. Any light that can be shed on this peculiarity would be great. Once I get past this hurdle the function will, of course, go on to do more and make better use of the temp table, but for now I just need to figure out why it's failing. Is this an improper or ill-advised use of a temp table? Thanks much, Joel CREATE OR REPLACE FUNCTION test_fxn() RETURNS SETOF RECORD AS $$ DECLARE test_rec RECORD; BEGIN CREATE TEMP TABLE temp_tbl (actual_inventory_id BIGINT, cal_due TIMESTAMP); FOR test_rec IN SELECT id FROM item LOOP INSERT INTO temp_tbl (actual_inventory_id) values (6); END LOOP; FOR test_rec IN SELECT actual_inventory_id FROM temp_tbl LOOP RETURN NEXT test_rec; END LOOP; DROP TABLE temp_tbl; RETURN; END; $$ LANGUAGE PLPGSQL; postgres=# select max(id) from test_fxn() AS (id bigint); max - 6 (1 row) postgres=# select max(id) from test_fxn() AS (id bigint); ERROR: relation with OID 24449 does not exist CONTEXT: SQL statement "INSERT INTO temp_tbl (actual_inventory_id) values (6)" PL/pgSQL function "test_fxn" line 6 at SQL statement postgres=# \d temp_tbl; Did not find any relation named "temp_tbl". postgres=#
Re: [GENERAL] trigger question
I ran into a similar problem and the solution I came up with (which admittedly feels like a kludge) was to temporarily disable the triggers on the table being modified while an update was made and then re-enabling them immediately after the update. I am sure there is potential for problems with this approach and I too would like to find a better one, but right now this works as I am still in the development stage and not dealing with any critical data. Anyway, this is essentially the code I use (applied now to table foobar) and maybe sharing it will help inspire a better solution. Please keep the list and me informed if you have oneThanks, Joel Code excerpt from within on delete trigger function for foobar. -- Disable triggers on table foobar UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid = 'foobar'::pg_catalog.regclass'; -- Perform update UPDATE foobar SET is_deleted=TRUE WHERE foobar_id=OLD.foobar; -- Re-enable triggers on table foobar UPDATE pg_catalog.pg_class SET reltriggers = 1 WHERE oid = 'foobar'::pg_catalog.regclass'; -Original Message- From: Furesz Peter [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 16, 2007 10:36 AM To: postgres levlista Subject: trigger question Hello, I have a table named foobar and I don't want to allow from DELETE or UPDATE its rows. I have a table as described below: foobar(foobar_id, value, is_deleted); I don't want to allow directly delete or modify the table's rows. I plan to make an on before update or delete trigger and on delete action I update the actual row is_deleted flag, on UPDATE action I also update the is_deleted flag and I insert a new row with the new values. Everything is ok, but when I capture the delete action I am execute an update what triggering the trigger again and I got an unwanted row. CREATE TRIGGER "tr_foobar" BEFORE UPDATE OR DELETE ON "public"."foobar" FOR EACH ROW EXECUTE PROCEDURE "public"."tr_foobar_func"(); BEGIN IF TG_OP='DELETE' THEN UPDATE foobar SET is_deleted=TRUE WHERE foobar_id=OLD.foobar; RETURN NULL; ELSEIF TG_OP='UPDATE' THEN INSERT INTO foobar(value) VALUES(NEW.value); NEW.is_deleted=TRUE; NEW.value=OLD.value; RETURN NEW; END IF; END; What is the right solution for this situation. Thank you for the help! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] too many trigger records found for relation "item" - what's that about??
Greetings,
I've had a strange error crop up recently on a table 'Item' which
contains about 60 rows and lives in a development database I'm currently
working on. Since the DB was last freshly created from a dump file
several days ago I've added/dropped/altered a few tables (not
necessarily 'Item' though), modified some data, and run many queries
against this and other tables. Now, all of a sudden if I try to run a
query against 'Item' I get the error shown below about too many trigger
records. Any idea what this means, how this came to be, and most of all
how to correct it? Below is the buffer from a recent session with a \d
on Item and the only other thing I can offer is that several tables have
Item.id as a foreign key. Please advise and thanks in advance for the
help.
- Joel
postgres=# select * from item;
ERROR: too many trigger records found for relation "item"
postgres=# \d item
Table "public_test.item"
Column| Type |
Modifiers
-++-
---
id | bigint | not null
default
nextval('item_sequence_id'::regclass)
name| character varying(100) | not null
manufacturer_organization_id| bigint |
model | character varying(100) |
version | character varying(100) |
size| character varying(100) |
quantity_measurement_parameter_enum | bigint | not null
color_enum | bigint |
batch_unit_enum | bigint |
is_consumable | boolean| not null
is_persistent | boolean| not null
Indexes:
"item_pkey_id" PRIMARY KEY, btree (id)
Foreign-key constraints:
"item_fkey_batch_unit_enum" FOREIGN KEY (batch_unit_enum) REFERENCES
enum_va
lue(id) ON UPDATE CASCADE ON DELETE RESTRICT
"item_fkey_color_enum" FOREIGN KEY (color_enum) REFERENCES
enum_value(id) ON
UPDATE CASCADE ON DELETE RESTRICT
"item_fkey_manufacturer_organization_id" FOREIGN KEY
(manufacturer_organizat
ion_id) REFERENCES organization(id) ON UPDATE CASCADE ON DELETE CASCADE
"item_fkey_quantity_measurement_parameter_enum" FOREIGN KEY
(quantity_measur
ement_parameter_enum) REFERENCES enum_value(id) ON UPDATE CASCADE ON
DELETE REST
RICT
postgres=# select * from actual_inventory a join item b on a.item_id =
b.id;
ERROR: too many trigger records found for relation "item"
postgres=#
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] replication choices
I believe I have a similar situation involving multiple database instances acting quasi-independently on a common (at least conceptually) set of data. Since each instance can effectively operate independently, I am uncertain if the term replication is accurate, but here is my strategy to keep the data properly synchronized. It is still unproven so any advice or scrutiny that can be given is welcome. Situation: There are multiple sites at which the same database/front-end application is running. None of the sites are directly connected to one another over a network and the only communication between sites is effectively unidirectional to a central location (i.e., information can independently go both ways during a communications link, but it's not real-time duplex). Each of the sites allows authorized users to perform any type of change to the data. Solution: Each site has 3 different versions of what I call the base schema: Confirmed, Pending, and Update. Each of these versions has some special columns associated with it to capture other information about changes that are made to it (e.g. timestamp, action(insert,update,delete), and status). The central site (which I'm loathe to call 'master') has these same schemas, plus it has an additional Update schema for each other site in the system. During normal use at each non-central site, the Pending schema is the active schema from which data is queried and also added, modified, and deleted. Each time a record is changed in the Pending schema it's status is flagged as 'pending' and the new data is copied to the Update schema. Also copied to the Update schema is the old data from the record that was changed. This effectively makes the Update schema a log of what each record in the database was changed to, what it was changed from, and when that happened (in UTC). The data from the update schema is then dumped regularly to a flat file. When any remote site establishes a communications link with the central site, the flat files of the Update schema from each site are exchanged and the official synchronization time is taken to be that of the flat file that was updated least recently (i.e., the older file). Then, at each site the data from the flat file is uploaded to the local Updates schema. All of the records in the now more populous Update schema are then processed sequentially by timestamp and applied to the Confirmed schema so, in theory, the same changes should be simultaneously getting applied to the Confirmed schemas at both locations in question. Finally, each record in the Pending schema is set to the value contained in the Confirmed schema and the flag set back to 'confirmed', the two sites are considered synchronized, and then the whole process starts anew. There are some details that have been glossed over here to eschew obfuscation, and the actual situation at the central site is more complex than this in practice, but that is the gist of the approach. I do not know of any product, Slony included, that has built in support for a situation such as this, so I suspect all of the details will have to be handled in a custom fashion. Anyhow, Ben, this is my working solution and, from the sounds of it, yours is the only case I have heard of that has the same set of challenges. I am interested in hearing if these ideas will work for you and/or if anyone knows of any flaws in this methodology or a better/easier/more reliable means of accomplishing this task. I should point out that, in our environment of understandably limited connectivity, we are definitely more tolerant of the delayed performance this synchronization strategy will cause than most users/companies would be. The important thing for us is that the data integrity is maintained and that everyone at each site can access and change the data. Regards, Joel -Original Message- From: Ben [mailto:[EMAIL PROTECTED] Sent: Thursday, January 25, 2007 1:18 PM To: pgsql-general@postgresql.org Subject: replication choices Hi guys. I've inherited a system that I'm looking to add replication to. It already has some custom replication code, but it's being nice to say that code less than good. I'm hoping there's an existing project out there that will work much better. Unfortunately, I'm not seeing anything that obviously fits my needs, so maybe somebody here can suggest something. I've got a single cluster in the datacenter and dozens of remote sites. Many of these sites are on unreliable connections to the internet, and while they're online more often then not, when their network will go down isn't known, and even when it's up, the network isn't that fast. A vast majority of activity occurs at these remote sites, with very little at the datacenter cluster. That said, the datacenter cluster needs to keep pretty good copies of most (but not all) of the data at each site. Ob
[GENERAL] How to use OIDs on tables......OR....a better solution?
Greetings, I have seen many a warning against using the Postgres internal OIDs to refer to DB objects, but I've got a situation that may warrant it. In a nutshell, I've got a table called 'Notes', which contains (you guessed it) notes that users can create to attach to various records in the DB. These records may well be in different tables of different sorts of entities (e.g., an inventory item, a calendar event, a facility, etc.). One note may be attached to many records and each record may have multiple notes. The notes are attached to the records via a separate associative table that contains the 'note_id', the 'record_id' (both generated by a sequence), and the 'table_name' in which the record resides. It's managable now, but my gut tells me that the association to the table should be handled by something besides just 'table_name' because if that were to be changed it would break things or potentially cause a lot of maintenance issues. Is the OID a good bet for something to use as a unique and reliable table identifier? If so, is there an elegant way to dereference the OID instead of using the alias (i.e. table name) to run a query against that table? I want to do this: > SELECT * FROM inventory_item; But, the following does not work (where 16675 is the OID of tabled inventory_item): > SELECT * FROM 16675; The one (very scary) pitfall I can see with using the OID is that if the DB were rebuilt, there's probably no guarantee or expectation that a table would have the same OID as before. That's certainly a deal breaker. Maybe the best solution is to continue using the table name, but to create that as a foreign key to the official table name in the information_schema? That way it could cascade if the name was changed, but I'm not sure what kind of ugliness might result if you tried to drop the table and it still had a referencing record. Any opinions on that or any other ways to approach this challenge? Thanks in advance, Joel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Dangers of fsync = off
Hello all. It's clear from the documentation for the fsync configuration option that turning it off may lead to unrecoverable data corruption. I'd like to learn more about why this is possible and how likely it really is. A quick look at xlog.h reveals that each record in the transaction log contains a CRC checksum, a transaction ID, a length, etc.. Assuming the worst thing that can happen due to a crash is that the end of the log is filled with random garbage, there seems to be little danger that the recovery process will misinterpret any of that garbage as a valid transaction record, complete with matching checksum. If my assumption is incorrect (i.e. garbage at the end of the log is not the worst that can happen), what else might happen, and how would this lead to unrecoverable corruption? Also, are there any filesystems available which avoid such cases? Sorry if this has been discussed before - in which case please point me to that discussion. Thanks. - Joel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Dangers of fsync = off
Thanks for the explanation, Tom. I understand the problem now. My next question is this: what are the dangers of turning fsync off in the context of a high-availablilty cluster using asynchronous replication? In particular, we are using Slony-I and linux-ha to provide a two-node, master-slave cluster. As you may know, Slony-I uses triggers to provide asynchronous replication. If the master (X) fails, the slave (Y) becomes active. At this point, the administrator manually performs a recovery by reintroducing X so that Y is the master and X is the slave. This task involves dropping any databases on X and having it sync with the versions on Y. Thus, database corruption on X is irrelevant since our first step is to drop them. It would seem that our only exposure is that both machines fail before the administrator is able to perform the recovery. Even that could be solved by leaving fsync turned on for the slave, so that when failover occurs and the slave becomes active, we only turn fsync off once we've safely reintroduced the other machine (which, in turn will have fsync turned on). There was a discussion about this here: http://gborg.postgresql.org/pipermail/slony1-general/2005-March/001760.html However, that discussion seems to assume that the administrator needs to salvage the databases on the failed machine, which is not necessary in our case. In short, is there any danger (besides losing a few transactions) of turning fsync off on the master of a cluster using asynchronous replication, assuming we don't need to recover the data from the master when it fails? Thanks. - Joel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Dangers of fsync = off
Thanks for your response, Andrew. On Tue, 8 May 2007, Andrew Sullivan wrote: On Fri, May 04, 2007 at 08:54:10AM -0600, Joel Dice wrote: My next question is this: what are the dangers of turning fsync off in the context of a high-availablilty cluster using asynchronous replication? My real question is why you want to turn it off. If you're using a battery-backed cache on your disk controller, then fsync ought to be pretty close to free. Are you sure that turning it off will deliver the benefit you think it will? You may very well be right. I tend to think in terms of software solutions, but a hardware solution may be most appropriate here. In any case, I'm not at all sure this will bring a significant peformance improvement. I just want to understand the implications before I start fiddling; if fsync=off is dangerous, it doesn't matter what the performance benefits may be. on Y. Thus, database corruption on X is irrelevant since our first step is to drop them. Not if the corruption introduces problems for replication, which is indeed possible. That's exactly what I want to understand. How, exactly, is this possible? If the danger of fsync is that it may leave the on-disk state of the database in an inconsistent state after a crash, it would not seem to have any implications for activity occurring prior to the crash. In particular, a trigger-based replication system would seem to be immune. In other words, while there may be ways the master could cause corruption on the slave, I don't see how they could be related to the fsync setting. - Joel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Dangers of fsync = off
Thanks, Bill and Scott, for your responses. To summarize, turning fsync off on the master of a Slony-I cluster is probably safe if you observe the following: 1. When failover occurs, drop all databases on the failed machine and sync it with the new master before re-introducing it into the cluster. Note that the failed machine must not be returned to use until this is done. 2. Be aware that the above implies that you will lose any transactions which did not reach the standby machine prior to failure, violating the Durability component of ACID. This is true of any system which relies on asynchronous replication and automatic failover. - Joel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trying to get postgres to use an index
At 10:11 PM +0100 11/6/04, Pierre-Frédéric Caillaud wrote:
explain select notificationID from NOTIFICATION n, ITEM i where
n.itemID = i.itemID;
QUERY PLAN
--
Hash Join (cost=47162.85..76291.32 rows=223672 width=44)
Hash Cond: ("outer".itemid = "inner".itemid)
-> Seq Scan on notification n (cost=0.00..12023.71 rows=223671
width=48)
-> Hash (cost=42415.28..42415.28 rows=741028 width=4)
-> Seq Scan on item i (cost=0.00..42415.28 rows=741028
width=4)
This query takes about 20 seconds to run.
Well, you're joining the entire two
tables, so yes, the seq scan might be faster.
Try your query with enable_seqscan=0 so
it'll use an index scan and compare the times.
You may be surprised to find that the
planner has indeed made the right choice.
This query selects 223672 rows, are you surprised it's slow ?
I'm not a SQL guru by any stretch but would a
constrained sub-select be appropriate here?
e.g. a simple test setup where each record in
table test1 has a FK referenced to an entry in
test:
joels=# \d test
Table "public.test"
Column | Type | Modifiers
+--+---
id | integer | not null
foo| character(3) |
Indexes:
"test_pkey" primary key, btree (id)
joels=# \d test1
Table "public.test1"
Column | Type | Modifiers
-+-+---
id | integer | not null
test_id | integer |
Indexes:
"test1_pkey" primary key, btree (id)
"test1_test_id_idx" btree (test_id)
Foreign-key constraints:
"$1" FOREIGN KEY (test_id) REFERENCES test(id) ON DELETE CASCADE
joels=# select count(*) from test;
count
---
10001
(1 row)
joels=# select count(*) from test1;
count
---
10001
(1 row)
joels=# explain select test_id from test1 t1, test t where t1.test_id = t.id;
QUERY PLAN
Hash Join (cost=170.01..495.05 rows=10002 width=4)
Hash Cond: ("outer".test_id = "inner".id)
-> Seq Scan on test1 t1 (cost=0.00..150.01 rows=10001 width=4)
-> Hash (cost=145.01..145.01 rows=10001 width=4)
-> Seq Scan on test t (cost=0.00..145.01 rows=10001 width=4)
(5 rows)
joels=# explain select test_id from test1 t1
where test_id in (select id from test where id =
t1.test_id);
QUERY PLAN
--
Seq Scan on test1 t1 (cost=0.00..15269.02 rows=5001 width=4)
Filter: (subplan)
SubPlan
-> Index Scan using test_pkey on test (cost=0.00..3.01 rows=2 width=4)
Index Cond: (id = $0)
(5 rows)
So with the subselect the query planner would use
the primary key index on test when finding
referencing records in the test1 table.
Pierre, I seen the advice to use an additional
where condition in certain cases to induce an
index scan; how is this done?
my 1.2 pennies,
-Joel
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] Socket command type I unknown
Good evening, I hope that I am posting this to the right place. If not, please direct me to the appropriate mailing list and I will send to that one instead. Background Info: Debian Linux (Sarge) Server A -- Apache 2.0.54 + mod_perl + DBD::Pg Server B -- PostgreSQL 7.3.4, Compiled, not debian package (yes, I know we should upgrade) Although I haven't been able to pay much attention to it until recently, we occasionally get the following message in our apache log file. I'm looking for information on what it means: DBD::Pg::db selectrow_array failed: FATAL: Socket command type I unknown server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. DBD::Pg::db selectrow_array failed: no connection to the server After this, we get a series of errors like this: DBD::Pg::db selectrow_array failed: no connection to the server DBD::Pg::db selectrow_array failed: no connection to the server DBD::Pg::db selectrow_array failed: no connection to the server And I -know- that's causing trouble on my web server. :) A restart of the web server 'corrects' the problem by reestablishing the connections to the database. I suspect this might be caused by a mismatch between the client libraries on Server A (7.4.X) whereas Server B is a 7.3.X install. (I'm working on correcting this ASAP, which will also get us on 7.4.) Basically my question is this: What does this error indicate? I can't seem to find much about it on the net and to be honest, I have become rather illiterate in C over the past several years, so reading the source is not really a viable option. Any info would be appreciated. Thank you for your time. --Joel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Socket command type I unknown
Sorry to spam you guys with my noise, but for the sake of posterity, I thought I'd reply with the solution to this problem. I learned a lot about the PGSQL protocol during this time. So, I ended up getting a Ethereal installed on a machine so I could follow the protocol and see what exactly was being sent to it. As it turns out, a separate piece of software on this server, which uses a single database handle to do its work was somehow sending an error message from another perl module as a message to the database. It's a guess that somehow it got its file handles mixed up or something along those lines. So the "73" (PgSQL 7.4) or the "I" (PgSQL 7.3) was truly an I since the message going to the server was something like: "Invalid blah blah blah" instead of: "QSELECT * FROM TABLE" Mystery solved. I've disabled the offensive code until I can more closely investigate in a controlled environment. Give that we didn't really need it, turning it off is an adequate solution for a production server. --Joel On Feb 4, 2006, at 1:16 AM, Tom Lane wrote: Joel Richard <[EMAIL PROTECTED]> writes: ... we occasionally get the following message in our apache log file. I'm looking for information on what it means: DBD::Pg::db selectrow_array failed: FATAL: Socket command type I unknown This looks to me like a protocol-level incompatibility: probably the client code is sending data in a slightly different format than the server is expecting, or one side or the other is off-by-one about message lengths, or something like that. One way or another the server is receiving an 'I' when it wasn't expecting that. I'm not aware of any such bugs on the server side in 7.3.4. What I suspect is a problem on the DBD::Pg side, where you did not specify what version you are using ... but if it's recent, it probably thinks that talking to 7.3.4 is a legacy problem ... regards, tom lane Joel Richard The Richard Group [EMAIL PROTECTED] 703-584-5802 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Socket command type I unknown
Tom (and others who may benefit from this), I'm sorry for taking so long to reply to this. PostgreSQL administration is not my primary task. :) After a client noticed this error twice this morning, I spent several hours investigating our machine and researching on the web. This email led me in this direction http://archives.postgresql.org/pgsql-interfaces/2004-05/msg00011.php and made me look further into what exactly was going on. It turns out that we had two versions of libpq installed on the server. One was from Debian's package manager which was for version 7.4.7 and the other was my compiled version for 7.3.X. This weekend I upgraded us from 7.3 to 7.4 on both the server and the client. I've removed any instances of the 7.3 libraries. So, now we have a different error happening about as often. DBD::Pg::db selectrow_array failed: FATAL: invalid frontend message type 73 I read that 73 is an ASCII value. Surprise 73 is a capital letter I. Same error, different method of reporting. DBD::Pg is at version 1.41 and DBI is 1.46. I'm going to try to bring DBD::Pg up to 1.48 just to get on the latest version of everything I can find. At this point, I want to say that it's my code that's causing the problem. It's as if there's some perl code/query that's creating the error, but a subsequent query to the server is what's manifesting the problem and causing the entry in the error_log. Am I on the right track here? I'm pretty sure the error can be traced back to my code. Therefore, I've started a global DBI->trace() on the postgres client server. I know that's going to be an inordinate amount of data (busy website), but sometimes the brute force method is what will work. I can't have my clients losing faith in my ability to solve their problems, so I get to track this down. :) Thanks, --Joel On Feb 4, 2006, at 1:16 AM, Tom Lane wrote: Joel Richard <[EMAIL PROTECTED]> writes: ... we occasionally get the following message in our apache log file. I'm looking for information on what it means: DBD::Pg::db selectrow_array failed: FATAL: Socket command type I unknown This looks to me like a protocol-level incompatibility: probably the client code is sending data in a slightly different format than the server is expecting, or one side or the other is off-by-one about message lengths, or something like that. One way or another the server is receiving an 'I' when it wasn't expecting that. I'm not aware of any such bugs on the server side in 7.3.4. What I suspect is a problem on the DBD::Pg side, where you did not specify what version you are using ... but if it's recent, it probably thinks that talking to 7.3.4 is a legacy problem ... regards, tom lane Joel Richard The Richard Group [EMAIL PROTECTED] 703-584-5802 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] spelling errors in query terms
Thanks Oleg, I'll take a look at it. Too bad there is no documentation. Also, there's something wrong with 'trgm.tgz'. This is what happens if I try to extract it's contents : gzip: stdin is encrypted -- get newer version of gzip tar: End of archive volume 1 reached tar: Sorry, unable to determine archive format. Cheers ! - Joel On Monday, December 8, 2003, at 05:39 , Oleg Bartunov wrote: On Mon, 8 Dec 2003, Joel Rodrigues wrote: Hi, I seem to recall once coming across & using functionality in PostgreSQL that allowed for some flexibility in the spelling of a query term. For example, if one meant to look for 'Honda', but typed in 'Zonda'. There was even a 'looseness' factor of sorts available. I've spent a lot of time trying to find it in the docs and various articles & tutorials on my hard drive, even an hour on Google in vain. It was not contrib/fuzzystrmatch. You might try http://www.sai.msu.su/~megera/postgres/gist/trgm/ which uses trigram. _ Envie de discuter en "live" avec vos amis ? Télécharger MSN Messenger http://www.ifrance.com/_reloc/m la 1ère messagerie instantanée de France ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] spelling errors in query terms
Thanks Oleg, I'll take a look at it. Too bad there is no documentation. Also, there's something wrong with 'trgm.tgz'. This is what happens if I try to extract it's contents : gzip: stdin is encrypted -- get newer version of gzip tar: End of archive volume 1 reached tar: Sorry, unable to determine archive format. Cheers ! - Joel On Monday, December 8, 2003, at 05:39 , Oleg Bartunov wrote: On Mon, 8 Dec 2003, Joel Rodrigues wrote: Hi, I seem to recall once coming across & using functionality in PostgreSQL that allowed for some flexibility in the spelling of a query term. For example, if one meant to look for 'Honda', but typed in 'Zonda'. There was even a 'looseness' factor of sorts available. I've spent a lot of time trying to find it in the docs and various articles & tutorials on my hard drive, even an hour on Google in vain. It was not contrib/fuzzystrmatch. You might try http://www.sai.msu.su/~megera/postgres/gist/trgm/ which uses trigram. _ Envie de discuter en "live" avec vos amis ? Télécharger MSN Messenger http://www.ifrance.com/_reloc/m la 1ère messagerie instantanée de France ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] make error Mac OS X (ar: illegal option -- s)
Thanks Tom. That got rid of the 'ar' error, but here's the new one that subsequently occurs : - /var/tmp/ccECcaaa.s:2139:Parameter error: r0 not allowed for parameter 2 (code as 0 not r0) make[4]: *** [xlog.o] Error 1 make[3]: *** [transam-recursive] Error 2 make[2]: *** [access-recursive] Error 2 make[1]: *** [all] Error 2 make: *** [all] Error 2 - I found this, which may be relevant: http://www.simdtech.org/apps/group_public/email/altivec/msg00663.html I don't have much of a clue with regard to C programming. Cheers, Joel On Friday, December 12, 2003, at 03:21 , Tom Lane wrote: Joel Rodrigues <[EMAIL PROTECTED]> writes: Hi, I get the following error when I run make on Mac OS X v 10.1.5 Any ideas ? Try setting AROPT = cr (not crs) in Makefile.darwin. regards, tom lane _ Envie de discuter en "live" avec vos amis ? Télécharger MSN Messenger http://www.ifrance.com/_reloc/m la 1ère messagerie instantanée de France ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] make error Mac OS X (ar: illegal option -- s)
Hi, In light of no responses to the make error question - Has anyone succeeded in installing PostgreSQL 7.4 on Mac OS X (10.1/10.2/10.3) ? Until now PostgreSQL was always just a ./configure, make, make install away. - Joel On Friday, December 12, 2003, at 10:02 , Joel Rodrigues wrote: Thanks Tom. That got rid of the 'ar' error, but here's the new one that subsequently occurs : - /var/tmp/ccECcaaa.s:2139:Parameter error: r0 not allowed for parameter 2 (code as 0 not r0) make[4]: *** [xlog.o] Error 1 make[3]: *** [transam-recursive] Error 2 make[2]: *** [access-recursive] Error 2 make[1]: *** [all] Error 2 make: *** [all] Error 2 - I found this, which may be relevant: http://www.simdtech.org/apps/group_public/email/altivec/msg00663.html I don't have much of a clue with regard to C programming. Cheers, Joel On Friday, December 12, 2003, at 03:21 , Tom Lane wrote: Joel Rodrigues <[EMAIL PROTECTED]> writes: Hi, I get the following error when I run make on Mac OS X v 10.1.5 Any ideas ? Try setting AROPT = cr (not crs) in Makefile.darwin. regards, tom lane _ Envie de discuter en "live" avec vos amis ? Télécharger MSN Messenger http://www.ifrance.com/_reloc/m la 1ère messagerie instantanée de France ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Fwd: [NOVICE] contrib/xml make error on Mac OS X 10.3.4
Thought I'd try this on the pgsql-general list. Begin forwarded message: This is what happens : make gcc -no-cpp-precomp -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I../../src/include -c -o pgxml_dom.o pgxml_dom.c pgxml_dom.c:11:26: libxml/xpath.h: No such file or directory pgxml_dom.c:12:25: libxml/tree.h: No such file or directory pgxml_dom.c:13:30: libxml/xmlmemory.h: No such file or directory ... followed by several errors & warnings Any ideas ? I never had any trouble compiling it on OS X 10.1.5 - Joel The files do exist, for example: /usr/include/libxml2/libxml/xpath.h Cheers, - Joel ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Fwd: [NOVICE] contrib/xml make error on Mac OS X 10.3.4
Thank you ! that did it. - Joel On Aug 1, 2004, at 11:15, Peter Eisentraut wrote: Joel Rodrigues wrote: The files do exist, for example: /usr/include/libxml2/libxml/xpath.h Try make all CPPFLAGS=-I/usr/include/libxml2 -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Triggers and SPI, oh my!
Hello all, We are writing a trigger in C that is somewhat like a replication trigger. It needs to know if data is inserted, updated, or deleted from a particular table. When the data changes, it must be able to get these changes and write them to a file. We have been using SPI to get the tuple information when the data changes, but we are having a problem with inserts. I can't seem to figure out how to get inserted data from a C trigger. Here is the scenario. When data is inserted into a table, the trigger must be able to get the inserted data and write it to a file. How can a C trigger get a hold of data from an insert? Thanks in advance for your comments. Joel Dudley Unix Admin DevelopOnline.com ---(end of broadcast)--- TIP 3: 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
[GENERAL] Re: diff's between creations of tables
On Thu, 26 Jul 2001, G.L. Grobe wrote:
> When creating an incremental and unique id, what are the benefits of using:
>
> CREATE TABLE tablename (colname SERIAL);
>
> instead of :
>
> CREATE SEQUENCE tablename_colname_seq;
> CREATE TABLE tablename
> (colname integer DEFAULT nextval('tablename_colname_seq');
> CREATE UNIQUE INDEX tablename_colname_key on tablename (colname);
>
> One is easier do delete as a dropdb dbname would do it, but anything else I
> should know. Or which one is the general practice, any rules of thumb to
> use, etc...
Same thing.
If you
CREATE TABLE foo (id serial);
PostgreSQL handles this by creating the sequence and index for you.
For the above statement, it does the following:
CREATE SEQUENCE "foo_id_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;
CREATE TABLE "foo" (
"id" integer DEFAULT nextval('"foo_id_seq"'::text) NOT NULL
);
CREATE UNIQUE INDEX "foo_id_key" on "foo" using btree
("id" "int4_ops" );
[taken right from pg_dump]
Both are deleted the same way:
DROP table foo;
DROP sequence foo_id_seq;
DROPDB dbname will *always* delete everything in a database, assuming
you have permissions to use it.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Backup Postgre Windows to Linux
On Thu, 18 Oct 2001, [iso-8859-1] Flávio Brito wrote: > Hi ALL > > I imported a database from Access 2000 to Postgre for Windows but now I can't > backup my database to export to Postgre for Linux . > I tried to use pg_dumpall > db.sql, its freeze on Windows2000 for more than a > hour. My database has 6 MB. > > I can backup my database directory on Windows (using ZIP) and put it on Linux? Might want to try pg_dump (rather than dumpall), perhaps even using the -t option to pick just one table. This could let us find out if you can dump *anything* or not. -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] VACUUM, 24/7 availability and 7.2
On Wed, 10 Oct 2001, Ian Barwick wrote: > I'm doing some work for a smallish company which conducts > its business largely online. Currently they have a legacy > mishmash of Oracle and MySQL databases which they wish > to unify one one platform (RDBMS with client access via > browser and custom serverside applications for employees > and customers). > > PostgreSQL would be my primary candidate. However the company's > operating requirments mean that the data needed for interaction > with customers / website users must be available on a 24/7 basis. > This is primarily a) data related to product ordering and > tables for storing order data; and b) website user authentication > and personalisation data (logins, user preferences etc). > > It is therefore not an option to have these databases offline > at regular intervals for any significant length of time for > VACUUMing. Replicating data to say MySQL databases is > technically feasible, at least in the case of b) above, but > not desirable. Are there any existing "native" PostgreSQL solutions > to this problem? > > More importantly, what is the situation on VACUUM for release 7.2? > It seems from the pgsql-hackers list that there are plans for > a none-exclusively locking VACUUM, e.g.: > > >http://groups.google.com/groups?q=vacuum&hl=en&group=comp.databases.postgresql.hackers&rnum=1&selm=12833.990140724%40sss.pgh.pa.us > > (sorry about the long URL); how far advanced are they, and is > there any kind of release schedule for 7.2? > > Any answers (or pointers thereto, haven't found any myself :-() > much appreciated There is a faster, non-exclusive-locking VACUUM in the CVS now; this should become part of 7.2. You can download the nightly snapsot and build it to test it with your application. HTH. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] A creepy story about dates. How to prevent it?
> Is it > desiarable to default postgresql.conf datestyle to match the locale? > > # > # Locale settings > # > # (initialized by initdb -- may be changed) > LC_MESSAGES = 'C' > LC_MONETARY = 'C' > LC_NUMERIC = 'C' > LC_TIME = 'C' Another JPY 2 from the nattou gallery: Defaulting the datestyle to the locale setting makes sense to me. I wouldn't want it hardwired to the locale, of course. I would strongly request having heuristics off in the default settings. Two conditions I really want on the use of heuristics -- I want the date itself to include some "fuzzy" flag, and I want some way to reconstruct the original string. That way, if I want to design an application with tools for getting operator attention, etc., there are hooks in the data. But that feels to me like something for the application layer. -- Joel Rees, programmer, Kansai Systems Group Altech Corporation (Alpsgiken), Osaka, Japan http://www.alpsgiken.co.jp ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] {REPOST, CLARIFIED} How to recognize PG SQL files?
On Wed, Aug 06, 2003 at 12:55:52PM -0400, Joel Burton wrote:
Reposting, with some clarification to my request. Thanks to the several
responses I received originally.
Yes, I know that a perfectly vaild PGSQL SQL file could contain only
ANSI SQL and therefore not be recognized as PG-related. In that case,
though, it would be recognized by Vim's ANSI SQL coloring, and given
that's all this file contains, that's no problem. ;)
However, given that many people edit pg_dump files (which do contain
pgsql-isms in most cases), and many other people do use non-ANSI PG
features, I figure that I should be able to recognize 95% of the files,
and that's a win since it will consistent highlight PG syntax and make
it easier to scan files, catch typos, etc.
Some easy things:
* if a filename ends with ".pgsql", it will use PG syntax coloring
* if a file contains a comment with "pgsql" in it in the first few
lines, it will use PG syntax coloring
* if a file contains the comments that pg_dump puts in a file, it will
use PG syntax coloring.
I'd still like to catch other cases, and still have the following
questions: what features among our extensions are unique to us, and what
features are used by other common DBs? People that have more recent
experience with MySQL, Oracle, SQLServer, etc. can probably answet this
question.
Thanks, everyone!
- j.
> I'm writing a syntax mode for PG for Vim (posted an early version
> earlier today) and would like to have Vim recognize that this is a PG
> SQL file (rather than a MySQL file or an Oracle file or such).
>
> I'm trying to brainstorm what the unique-looking parts of PG's syntax
> are. These need to be present in PG SQL files (& hopefully not too
> obscure) but not present in other DB SQL files.
>
> The PG manual states how PG differs from SQL standards, but not how it
> differs from other popular databases. I've used MySQL and Oracle in the
> past, but not recently, and haven't use DB2 or SQLServer in ages and
> don't have docs for them anymore.
>
> I have a few possible suggestions. Can anyone:
>
> * tell me if these are used in other DB systems (& shouldn't be part of
> my syntax)
>
> or
>
> * provide other ideas for unique PG syntax
>
>
> My ideas:
>
> * \connect
>
> * template1
>
> * "from pg_" (selecting from a PG system table)
>
> * "create rule"
>
> * plpgsql, plperl, plpython, pltcl, pltclu, plruby (& now plphp, too, I
> suppose! ;) )
>
> * "nextval(", "currval("
>
>
> I'd love to find something common, like "SERIAL" or "CREATE SEQUENCE" or
> such, but I suspect that other commonly-used databases use these.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Does CREATE FUNCTION... WITH (ISCACHABLE) work?
I have a function that always returns the same answer given the same input (no database lookups, etc.). The pg Users' Manual documents the attribute 'iscachable' as allowing the database to parse the results of the function and not keep looking it up. Does this actually work yet? A simple test case: CREATE FUNCTION f() RETURNS INT AS ' BEGIN raise notice ''foo''; return 1; end; ' LANGUAGE 'plpgsql' WITH (ISCACHABLE); SELECT o(); NOTICE: foo o ___ 1 (1 row) SELECT o(); NOTICE: foo o (1 row) It might be that the parser is smart enough to copy any output (such as the RAISE NOTICE), my fear is that it is actually running the function a second time. Does anyone know if this caching actually happens yet, or is this a future feature? Thanks. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Does CREATE FUNCTION... WITH (ISCACHABLE) work?
On 19 Jul 2000, at 14:30, Tom Lane wrote: > "Joel Burton" <[EMAIL PROTECTED]> writes: > > I have a function that always returns the same answer given the same > > input (no database lookups, etc.). The pg Users' Manual documents > > the attribute 'iscachable' as allowing the database to parse the > > results of the function and not keep looking it up. > > iscachable does not mean that the system will cache the results of the > function across queries, it just means that the function needn't be > re-evaluated multiple times for the same arguments within a single > query. For example, given > > SELECT * from table1 where col = foo(42); > > If foo() is marked cachable then it's evaluated once during query > planning; if not it's evaluated again for each row scanned in table1. Sounds reasonable. But does it work as advertised? CREATE FUNCTION foo(int) RETURNS int AS ' BEGIN RAISE NOTICE ''hi''; RETURN 1; END;' LANGUAGE 'plpgsql'; CREATE FUNCTION foocache(int) RETURNS int AS ' BEGIN RAISE NOTICE ''hi''; RETURN 1; END;' LANGUAGE 'plpgsql' WITH (iscachable); SELECT foo(1),foo(1),foo(1) gives us 3 NOTICEs, as does SELECT foocache(1), foocache(1), foocache(1) So is it running the cached version a second time? Thanks, -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
(Fwd) RE: [GENERAL] PostgreSQL, ODBC, Access (solution for me)
> > Here's a bothersome issue: I've got the most recent versions of > > Postgres, ODBC client for Win32, and Access 97. My client can enter > > new records fine via a linked table. However, when she goes back to > > add data to a column, she gets the following error: > > > > message box title: "Write Conflict" > > description: "This record has been changed by another user since you > > started editing it. If you save the record, you will overwrite > > the changes the other user made." buttons: "Copy to Clipboard" and > > "Drop > > Changes". It appears that *once* Access finds something unique about a record, it uses that to differentiate records. (Check out the SQL log to see) However, a new field in Access has no key *until* PostgreSQL gets it (if you're using a SERIAL field type), and the default values for other fields don't appear either. So, the trick is to have Access deposit a unique value (in this case, a timestamp) into each field. What works for me (even in datasheet view): 1. Create a table w/a timestamp field. CREATE TABLE Foo (id SERIAL PRIMARY KEY, fullname VARCHAR(30) NOT NULL, dt TIMESTAMP DEFAULT 'now' NOT NULL); Then, in Access: Don't use *table* datasheet view. Create a form w/the fields you want, and use that *form*datasheet view. Set it up so that Access has a DefaultValue property of Now() for the "ts" column. (In addition, while you're there, you might want to lock/hide the ts column, and lock the serial column, as Access will let you renumber a PostgreSQL serial field, which I think is a Bad Thing [YMMV].) Then use the datasheet view. Since the "dt" column should be different for each record *from the moment of inception*, this gives Access something really unique to hang its hat on. Works for me; let me know if it doesn't work for you. -- Has anyone ever collected a FAQ of Access-on-Postgresql? I've got a few tips (nothing heavy, just the usual use-float-instead-of- decimal-for-currency), and suspect others have a few. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
