[GENERAL] Please say it isn't so
Hi all, Please tell me this is a mistake: https://wiki.postgresql.org/wiki/Systemd Why a database system should care about how processes get started is beyond me. Systemd is an entangled mess that every year subsumes more and more of the operating system, in a very non-cooperative way. There are almost ten init systems. In every one of those init systems, one can run a process supervisor, such as runit or s6 or daemontools-encore, completely capable of starting the postgres server. Every year, systemd further hinders interoperability, further erodes interchangeability of parts, and continues to address problems with WONTFIX. In the long run, you do your users no favor by including init-system specific code in Postgres or its makefiles. If systemd can't correctly start Postgres, I guarantee you that s6 or runit, running on top of systemd, can. Postgres doesn't care which language makes a query to it. Why should Postgres care which init system started it? I hope you can free Postgres of init-specific code, and if for some reason you can't do that, at least don't recommend init-specific code. Thanks, SteveT Steve Litt July 2017 featured book: Quit Joblessness: Start Your Own Business http://www.troubleshooters.com/startbiz -- 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] browser interface to forums please?
On Wed, 5 Apr 2017 09:31:59 -0700 Adrian Klaver wrote: > On 04/05/2017 09:17 AM, Magnus Hagander wrote: > > > > > This has been tried a number of times. I'ts been a couple of years > > since I last saw one, but multiple people have set up forums, > > either mirrored or not. They have all died because of either lack > > of usage or because the person who did it disappeared. > > Mostly, because they did not work well and the folks on this end of > the process had to do more work to get the information necessary to > answer the question. I know I eventually stopped responding to the > questions from those sources because it was difficult to follow the > information flow. Namely you had to crawl back up to the forum to get > information and then the email thread had mix of information that > made it through on its own and some subset of information that > dedicated people pulled in from the forum. That mix depended on > dedication level and time available. In addition, once you subscribe to a mailing list, all info comes to you. No password necessary. Read, reply, lightning quick. Contrast this with forums, where you have to remember to go out to each and every forum you're interested in, put in the password, and then operate within the work-flow of the forum. I'm subscribed to mailing lists of 20 LUGs. Can you imagine the inconvenience if I had to go out to each one and put in a password just to see if there's anything new? With mailing lists, the information comes to you, instead of making you go out to it. SteveT Steve Litt April 2017 featured book: Troubleshooting Techniques of the Successful Technologist http://www.troubleshooters.com/techniques -- 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] browser interface to forums please?
On Mon, 27 Mar 2017 11:31:02 +0900 Michael Paquier wrote: > If you have subscribed to more mailing lists than -general, having one > subfolder per list can also help a lot, grouping as well some of those > having a low activity, for example: > - one folder for -hackers and -hackers-cluster. > - one folder for -general. > - one folder for -jdbc and -odbc. > - one for -bugs and -docs. > - one for -jobs and -announce, etc. > Something like that will make your hacking activity way easier to > handle. I would bet that a lot of people around here do that. I sure do. I have a heck of a lot of email in a heck of a lot of folders, all stored in a nice, easy to drill down hierarchy. That hierarchy is maintained by the Dovecot IMAP server that runs on my desktop computer. On every successful mailing list, somebody inevitably suggests replacing it with "a forum" or "a facebook page" or some proprietary website that acts as a middleman (Google, Meetup and Linkedin are three of the usual suspects). Such suggestions usually go nowhere, and when they're followed, communication usually ceases and the the community becomes a ghost town. When it comes to having a lively group discussion that focuses all minds into a supermind greater than the sum of the parts, a mailing list is the best tool. Especially if those who use it trim properly and make sure they're not being ambiguous. Another mailing list benefit: Most of these other types of "community communicators" sooner or later disappear from the Internet, just like mailing lists. But with mailing lists, individuals can keep their own archives. I have archives from my first LUG, even though that LUG's mailing list went defunct in 2002. Because my email folder hierarchy was designed by me, I can usually find emails of any age very quickly. Responding is as easy as replying to an email. One assertion in the original post was that email communication is "so 1990's". That's neither a compliment nor an insult, and has prompted me to write an essay, for which I'll provide the URL when it's finished. Bottom line though, don't mess with success. SteveT Steve Litt March 2017 featured book: Troubleshooting: Why Bother? http://www.troubleshooters.com/twb -- 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] Looking for an online mentor
On Thu, 8 Dec 2016 19:19:27 -0500 Metare Solve wrote: > Sorry, I got on so many lists yesterday. I'm really not that dense. > > I have absolutely no language programming skills and it is very very > frustrating. I can HTML and that's it. I desperately want to develop > the skills but whenever I try on my own, I hit the same wall as I do > with SQL. I'm just bad at the deep dives into code and really need > some honest help. I've written two books on Rapid Learning. You can learn this stuff, and learn it a lot faster than most people think you can (depending on your beliefs), but please understand you're not going to be proficient at several programming languages and SQL and Postgres in two weeks. What you're trying to do requires you to make a real committment over a much longer time than a couple weeks, and you should not delay further employment until you've learned programming, SQL, and Postgres. [snip] > The reason I think I can learn SQL with just a bit of > guidance is I know the concepts behind "where" and "group buys" and > "unique," etc, but I miss a ; and get an error and then get > frustrated. Remember above I mentioned your beliefs as an energizer or retardant to your learning? If errors get you frustrated, you must either change your beliefs to believe that errors, even those caused by a silly syntax mistake like a missing ';', are just part of the process: Fix them and move on. If you cannot adopt that belief, you'll never succeed in programming of any kind. Fortunately, most beliefs are fairly easy to change. > > Purpose of the project: > > Eh, a bunch of things. > > - The project is to build my Tableau [snip] > - The project will also give me a product [snip] portfolio. [snip] > > - I have two projects, one of them is to analyze crime rates around > the moon phases. Just a question my dad once posed as a science > project that I blew off. Now seems kind of interesting to me to > pursue. Will give me date experience, mapping if I want it, can go > down to the precinct level, etc. The other is some data I've been > collecting for about 15 months on a pixel dragon game I play. I want > to build a dashboard to manage my lair/income/value, etc. That is > definitely where the SQL database comes in. I think the moon one is > just data blending. I doubt that crime rate analysis and a dragon game will give you sufficient motivation for the large amount of work that will be required to learn all of this. > > - To give me intellectual stimulation because I am a nerd. Yeah, that'll do it. > > Just a note, I'm a female gen x with a master's degree in library and > information science. I took a database design class in grad school > and it was the biggest waste of money and time. A whole heck of a lot of college courses are a waste of time. I'm writing a series of books related to this right now. I think you need to decide how serious you are about this. If you're serious enough to make the committment it requires, please keep me in the loop. Also, as somebody else already mentioned in this thread, if you're serious about it, revise your email communications to answer specific questions right below the questions, and delete any context material not closely related to what you're writing back. Top posting is great for corporate communication, where the priority is CYA. But it's a lousy way of conducting question/answer/question/answer type communication: You need interleave posting with scrupulous snipping of unrelated material for that. SteveT December 2016 featured book: Rapid Learning for the 21st Century http://www.troubleshooters.com/rl21 -- 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] Looking for an online mentor
On Thu, 8 Dec 2016 17:08:41 -0800 (PST) Rich Shepard wrote: > On Thu, 8 Dec 2016, Adrian Klaver wrote: > > > GUI's only get you so far. At some point you will need to dive > > deeper to get what you. I am mostly a self taught > > programmer(biologist by training) so I understand the hill you are > > facing. The language I use is Python, mainly because to me it made > > sense. For you it might be a good choice as it is quite prevalent > > in the data analysis world. There are a lot of places to turn to to > > learn how to use it. My suggestion for seeing if it is something > > you can use would be to start here: > > +1 for Python > > > Go through at least the Introduction to Python part. The rest has > > to do with Django, Web framework built using Python. > >Mike Driscoll has a blog (I don't recall the URL) and his Python > 101 is a very good introduction. There are also a lot of online > tutorials. > >I would suggest starting by learning a general programming language > (specifically Python). That puts you in a learnable mindset. I absolutely concur that Python's a great starting point. It's easy, it puts you in a learnable mindset, there are many directions you can follow once you know Python, and Python knowledge might even get you a job. > SQL is a > set-oriented language and is quite different from procedural, object > oriented, and functional languages. I think she could learn SQL concurrently with Python, as long as she completely understands that they don't do anything the same way as each other, and they're not even for the same purpose. SteveT Steve Litt November 2016 featured book: Quit Joblessness: Start Your Own Business http://www.troubleshooters.com/startbiz -- 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] Let's Do the CoC Right
On Sat, 23 Jan 2016 15:43:11 -0800 "Joshua D. Drake" wrote: > I have been accused of being a fat hater. My crime? I suggested that > generally speaking, obesity is a matter of diet and exercise. Worse? > The individual started the conversation and I am also classified as > obese (barely, I won't be in a month). Perfect! I know a person who is fat because his/her (I'll call the person male from now on) thyroid was removed, and weight control is extremely difficult under those circumstances. Do you think he'd feel welcome on a list where somebody said "generally speaking, obesity is a matter of diet and exercise?"[1] And then perhaps someone else says he thinks fat people are lazy. Is my overweight friend going to set you straight? Probably not. He knows how much antifat prejudice exists in the employment marketplace, and doesn't want to do anything do the slightest thing to "out" himself to potential networking associates who haven't seen him in person. And for what? What does a person's weight have to do with a great and powerful Open Source relational database? Not a dam thing. Hey, if the conflict is about technology, by all means have at it. It's an argument that needs to happen in order to produce the best result. But when it comes to gender, gender preference, gender-assignment, race, nationality, religion, body shape, or political party (unless the party takes a stand on technology), the CoC should ban negative statements about that crap. [1] I'm not faulting your example. Your example is relevant to the discussion. I'm faulting a hypothetical person who comes on the list and says that, apropos to nothing. SteveT Steve Litt January 2016 featured book: Twenty Eight Tales of Troubleshooting http://www.troubleshooters.com/28 -- 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] Let's Do the CoC Right
On Sat, 23 Jan 2016 17:09:32 -0500 Melvin Davidson wrote: > I been pretty quiet about this whole discussion, but now I have to > ask the following questions. > > This is an INTERNET SUPPORT FORUM. > Just how in the hell is it possible for anyone to have their actual > sex detected unless they voluntarily provide it? Given that my name is "Steve", I doubt anyone thinks I'm a woman. If I were a woman named "Stephanie", should I be expected to assume a different name in preference to making rules against saying bad things about people and groups, unrelated to the topic of the mailing list? > Further to the point, how is it possible to harass sexually (or > physically) molest anyone in this forum unless they provide > information and agree to meet in person. I could tell crude jokes about rape. I could a woman's worth is proportional to her looks. I could arbitrarily attribute the lower female participation in tech to lack of intelligence. If it were just me, it would be just one asshole mouthing off. But add a couple more like me, with minimal repudiation by others, and perhaps the same old cast of characters shouting down any repudiation with that tired old "free speech" argument that some always seem to apply to completely offtopic negative spew, and some women who might have made big contributions have left the project. > Please, drop the argument about protecting against physical or verbal > abuse, Yeah, if everyone else does. But a code of conduct is actually a good idea, because there are a lot of vicious, worthless clowns out there who like to issue gratuitous insults. > because it does not apply to this forum. False. SteveT Steve Litt January 2016 featured book: Twenty Eight Tales of Troubleshooting http://www.troubleshooters.com/28 -- 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] Let's Do the CoC Right
On Sun, 24 Jan 2016 00:00:27 + Geoff Winkless wrote: > On 23 January 2016 at 21:59, Steve Litt > wrote: > > I'm reminded of a person on a computer on a no-Internet-connection > > LAN saying that everyone needs equal protection from firewalls. > > Ummm, no. The Internet connected firewall has many, many more > > attempts made against it than the guy on the island LAN. > > Did I say we all need equal protection? No. I said we're all entitled > to the same level of protection. The preceding two sentences form a distinction that will need some elaboration. > I'm also making the point that your > Island guy might have a mobile phone that's linked to his computer > that you don't know about and you're assuming that cos he's on an > island he has no right to have an opinion on firewalls. Come on, the preceding is contrived to the point of being silly. You know exactly what I mean. SteveT Steve Litt January 2016 featured book: Twenty Eight Tales of Troubleshooting http://www.troubleshooters.com/28 -- 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] Let's Do the CoC Right
On Sat, 23 Jan 2016 20:12:15 + Geoff Winkless wrote: > On 23 January 2016 at 18:07, David E. Wheeler > wrote: > > On Jan 22, 2016, at 6:14 PM, Joshua D. Drake > > wrote: > >> A Code of Conduct should protect all, equally and without bias. > > > > Says someone who requires no protection at all. > > I must object to the repeated assertions that certain people in this > community require no protection, or have no reason to, as a way of > discounting their arguments. > > In addition you might appreciate the irony if you took the time to > consider the (reasonably recent) history of people with names like his > before stating that Josh requires no protection. Everyone is entitled > to the same level of protection, whatever their race, gender > alignment, sexuality or whatever, and that includes us white > middle-class men, however guilty you appear to feel the need to be > about being one. I'm reminded of a person on a computer on a no-Internet-connection LAN saying that everyone needs equal protection from firewalls. Ummm, no. The Internet connected firewall has many, many more attempts made against it than the guy on the island LAN. We all need protection --- this is true. But the transsexual has much more bad verbiage aimed at "his (her) kind" than a run of the mill, average person, whatever that may be. When you go to computer conferences, how often does someone put their hands all over you? Read this: http://blog.valerieaurora.org/2010/11/08/its-not-just-noirin/ If you think the author of the preceding article is lying, google the combination of "groped" and "Linux conference". Women are the minority at these conferences, yet many more hands reach out and grab them. We all need the necessary protection, which is not necessarily equal protection, because some of us are subjected to much more harassment. And I think we all need to walk a mile in other peoples shoes before assuming others need only the meager amount of protection we need. SteveT Steve Litt January 2016 featured book: Twenty Eight Tales of Troubleshooting http://www.troubleshooters.com/28 -- 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] Let's Do the CoC Right
On Fri, 22 Jan 2016 09:25:58 -0800 Adrian Klaver wrote: > When and whom? This is the time for those that had issues to speak up > either directly or through someone else. In doing so though I would > expect verifiable information. Maybe they can't. Imagine for a second that I'm a homosexual, and that a guy cracked a crude joke about homosexuals, and three or four people post that it was a funny joke. Imagine further that I work for one of those troglodyte employers who would fire me the instant they found out I was a homosexual, and I come from a family that would disown me if they found out. I wouldn't speak up. I wouldn't even say "I'm not a homosexual, but I think your words are hurtful!" Because I would be so afraid of being found out that I would not give one hint. I'd just leave. Now imagine I was from one of those countries where homosexuality is punishable by death. Speaking up is a privilege often reserved for the in crowd and the revolutionary. SteveT Steve Litt January 2016 featured book: Twenty Eight Tales of Troubleshooting http://www.troubleshooters.com/28 -- 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] CoC [Final]
On Thu, 21 Jan 2016 10:43:26 + Geoff Winkless wrote: > On 21 January 2016 at 10:37, Chris Travers > wrote: > > At the end of the day this will require human judgment rather than > > formulation. > > Then make it explicit. > > * Disruption of the collaborative space, or patterns of behaviour > which the majority of the core team consider to be harassment, will > not be tolerated. "Disruption of the collaborative space" is almost meaningless, and almost guarantees selective enforcement. On the other hand, "patterns of behaviour which the majority of the core team consider to be harassment" is crystal clear. What would happen if you just dropped "Disruption of the collaborative space"? If not, I'd suggest a much more definitive substitute for that phrase. SteveT Steve Litt January 2016 featured book: Twenty Eight Tales of Troubleshooting http://www.troubleshooters.com/28 -- 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] CoC [Final]
On Mon, 18 Jan 2016 10:02:33 -0800 "Joshua D. Drake" wrote: > * Participants who disrupt the collaborative space, or participate in > a pattern of behaviour which could be considered harassment will not > be tolerated. This one might come back to bite you. I, along with probably twenty others, "disrupted the collaborative space" on the Debian-User mailing list during the Systemd Civil War, and I'd do it over again because of the technological and practical importance of preserving an alternative to systemd. I'd even like to believe that in a small way I helped recruit more people to the Devuan (Debian Fork) project. My posting rights were removed, basically, for "disrupting the collaborative space", and that's fine: Guilty as charged. But here's the thing: The list was moderated by a systemd advocate, who let the pro-systemd fanatics disrupt the collaborative space to their hearts' content with only the mildest wrist slaps, while removing posting rights of several anti-systemd people. "Disrupting the collaborative space" is very hard to define even when nobody has an agenda. When there are agendas, it almost certainly will lead to selective enforcement. Be careful what you wish for :-) SteveT Steve Litt January 2016 featured book: Twenty Eight Tales of Troubleshooting http://www.troubleshooters.com/28 -- 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] WIP: CoC V5
On Wed, 13 Jan 2016 09:15:45 -0500 "Regina Obe" wrote: > For example if Tom makes some snide remark like "Do all Bostonians > program this way?" Why not simply discuss the code, with no value judgment about the coder? "The strcpy() in the foo() function can cause intermittent problems and open an attack route. Why not use strncpy() instead?" Of course we all know there are some people who prefer to say something like the following: "Really? I mean really? People still use strcpy() in 2016? All but the dullest know that opens an attack route." For some reason, a significant percentage of people just LOVE to get judgmental about the other guy's work product, rather than simply showing a better way. To me it's simple... Disallow "You " Disallow "Your code " Encourage "It would be better if your code because ." Steve Steve Litt January 2016 featured book: Twenty Eight Tales of Troubleshooting http://www.troubleshooters.com/28 -- 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] Code of Conduct: Is it time?
On Mon, 11 Jan 2016 11:00:23 -0800 "Joshua D. Drake" wrote: > A CoC is not about being offended. The act of being offended is > purely a recipients response and usually because the recipient is > more interested in being a victim than moving forward. I've seen text like the preceding in over 10 messages in this thread. I could be interpreting them wrong, but they seem to be saying the offended recipient is more interested in being a victim than moving forward, or with some of the responses, the recipient is being a cry baby. In my opinion, this is a much easier position to take when it's the other person regularly spoken of as "misinformed", "ignorant", "neckbeard", or whatever. Much harder position when your technical posts are regularly greeted by such personal insults, and few folks rise to your defense. Well, whatever, survival of the fittest. 90% of the time, the party being personally insulted silently leaves, and is not missed. But sometimes the community has something to lose. Let me tell you a story. = = 12 years ago, one guy in my LUG continually replied to me in what I think most reasonable people would call an insulting manner. Some of his posts called me "ignorant", "unprofessional", lack of "checking my work", "committing libel", "lies and hypocracy", and "reinventing history". I called for the LUG's Executive Committee to reign in his rhetoric, explaining that it had gotten to such a point that I could no longer bring friends, or possible business associates into the LUG because they would be hearing a constant barrage of anti-Litt rhetoric, and some of it might stick. The Exec Committee told me I was being too sensitive and I should just let it slide. So I got a new domain name, started a new LUG, drew membership both from the old LUG and from the greater area. Immediately those same people who said I was being too sensitive begged me to cancel the new LUG and they'd institute anti-personal-insult rules. But it was too late: I'd already done it. Over the next several years, the new LUG grew and still meets every month, maintaining an active mailing list and IRC channel. Meanwhile, the old LUG lost membership, lost their nonprofit corporate status, lost their mailing list, lost their domain name, and their remnants hold an "installfest" once a month in a venue with no Internet (it's BYOI). = = Most of the time, chalking things up to "recipient is more interested in being a victim" does the community no harm. But every once in a while, the costs are considerable. All because somebody just *had* to personally insult someone else, repeatedly, and nobody thought that was a bad thing, and when the recipient finally objected, the objection was chalked up to him or her valuing his/her victimhood. About a CoC, here's what I want to know: What *possible* value to a free software community could come of a sentence structured like the following: "You " What possible harm would it do to ban such sentences? What features do such sentences introduce into the software? Why is it difficult to discuss features instead of people on the mailing list? How many potential contributors have silently left after seeing personal insults to themselves or others? My opinion: Whether you call it CoC or mailing list rules or anything else, some degree of it is needed, because the community allowing a wild west of personal insults fails to achieve its potential at best, and disintegrates at worst. SteveT Steve Litt January 2016 featured book: Twenty Eight Tales of Troubleshooting http://www.troubleshooters.com/28 -- 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] Linux vs FreeBSD
On Fri, 11 Apr 2014 14:05:43 +0200 Alban Hertroys wrote: > My advice to the OP: > > Install FreeBSD on a system to play around with, get a feel for how > it works and whether you like it or not. See how it performs with > Postgres on different file-systems; UFS2 or ZFS - UFS is the faster > of the two, but ZFS makes up for that big time in maintainability if > you provide it with enough memory. If you require locale-specific > collations (native language specific sort ordering), check that it > does what you expect. Curious: Why not consider OpenBSD also? SteveT Steve Litt* http://www.troubleshooters.com/ Troubleshooting Training * Human Performance -- 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] Need sql to pull data from terribly architected table
On Thu, 25 Oct 2012 03:56:39 +1100, Chris Angelico said: > On Thu, Oct 25, 2012 at 2:42 AM, Steve Litt > wrote: > > Also, with the organization they're using, one can make new > > "columns" on the fly. ... Anyway, the keypuncher is punching > > data, comes across a brand new type of data (let's say "artist"), so > > for this row the keypuncher puts in a key-value pair of "artist=Lady > > Gaga". From a practical point of view, data structure could be > > change at key entry time, and needn't have been anticipated by the > > programmer nor recompiled or reorganized when a new type of data > > element entered the requirements. > > That's wonderfully flexible, but it forfeits the protection that a > well-designed schema gives. A system like that is likely to end up > with different records storing the same data under slightly different > names, and you'll have a massive proliferation of "columns" that have > only a single row's value in them. That's fine if that's what you > want, but from a data entry standpoint, I think it's _too_ flexible > for most purposes. True enough. In my particular case, my program was used by litigation support professionals (I probably shouldn't have called them "keypunchers") who knew what they were doing. Secondly, IIRC, I had a separate table for field names, so that before creating a new field name, they'd be presented with the current ones. One possible way of limiting the possible damage you pointed out would be to have only a select few be permitted to add new field names. I think my program might have done that -- I seem to remember those entering data having to call the supervisor to put in a new category of information. But yeah, the data organization I mentioned requires use by halfway intelligent and competent users, and certainly isn't universally appropriate. Thanks SteveT Steve Litt* http://www.troubleshooters.com/ * http://twitter.com/stevelitt Troubleshooting Training * Human Performance -- 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] Need sql to pull data from terribly architected table
On Tue, 23 Oct 2012 18:52:52 +, Gauthier, Dave said:
> Here's the deal...
>
> Instead of architecting and loading a table like...
> create teble foo (col1 text, col2 text, col3 text, col4 text, col5
> text); insert into foo (col1,col2,col3,col4,col5) values
> ('c1',null,'c3','c4',null);
>
> They did this instead...
>
> create table foo (property text, value text);
> insert into foo (property, value) values ('col1','c1'),
> ('col3','c3'), ('col4','c4');
>
> Notice how "col2" and "col5" were simply left out of the table in the
> 2nd model to indicate null.
>
> The question is how to do this model 1 query for model 2...
>
> select col1,col2 from foo where col4='c4' and col5 <> 'xxx';
>
> I know I have to use outer joins to deal with the potential of
> nulls. But I don't know how to construct this. I won't list my
> failed attempts (so as not to embarass myself :-))
>
> Thanks in Advance !
Hi Deve,
If it were me, I'd use a computer language like Perl, Python, Ruby, Lua
or Java to retrieve the data, rather than trying to do the whole thing
in SQL. Looking at the way they constructed their table, I'd guess the
intent was to use a language to do the logic, rather than pure SQL.
The way they constructed the table looks to me like they were trying to
comply with the first normal group -- no repeating columns (no arrays).
This would make sense if col1 through col5 were all the same type of
entity -- for instance, each is the name of a child of the couple. By
doing it as key-value pairs, each couple can have as many or as few
children as necessary, rather than reserving five columns for children
and then running into trouble when a six child family comes along.
Also, with the organization they're using, one can make new "columns"
on the fly. Years ago I created a litigation support database
structured partially as key-value pairs (along with a "row number" -- I
don't know how your database got along without a key to show which row
each key-value pair belonged to). Anyway, the keypuncher is punching
data, comes across a brand new type of data (let's say "artist"), so
for this row the keypuncher puts in a key-value pair of "artist=Lady
Gaga". From a practical point of view, data structure could be change
at key entry time, and needn't have been anticipated by the programmer
nor recompiled or reorganized when a new type of data element entered
the requirements.
I'll bet you dollars to donuts if you could speak to the original
programmer, he'd show you a good reason for his data organization, and
he'd also tell you he in no way anticipated that the data would ever be
handled purely by SQL.
Anyway, bottom line, a simple, procedural language with an interface to
Postgres would be a quick and easy way to convert this data to the type
you prefer.
HTH
SteveT
Steve Litt* http://www.troubleshooters.com/
* http://twitter.com/stevelitt
Troubleshooting Training * Human Performance
--
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] Looking for Suggestion on Learning
On Saturday 05 February 2011 18:42:09 John R Pierce wrote: > On 02/05/11 9:30 AM, ray wrote: > > I have built a few databases with MS Access and I would like to learn > > how to use pgsql. I have found some .. > > Access really isn't a database, its an application development system > that happens to use databases, by default the Jet engine. Postgres > would replace Jet, but not Access itself, you'd need some other sort of > software for creating forms & reports and such. > > Do note, you can use Postgres databases with Access, via Postgres ODBC > or ADODB connectors. Is there an open source product that runs on Linux that does what Access does? Thanks SteveT Steve Litt Recession Relief Package http://www.recession-relief.US Twitter: http://www.twitter.com/stevelitt -- 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] Crosstab query on huge amount of values
On Monday 17 January 2011 07:21:11 you wrote: > Am 17.01.2011 00:20, schrieb Steve Litt: > > On Sunday 16 January 2011 17:40:34 Julia Jacobson wrote: > >> Hello everybody out there using PostgreSQL, > >> > >> A table with the results of students in different exams > >> > >> student | date_of_exam | grade > >> -- > >> Peter | 2010-09-09 | 2 > >> Tom | 2010-09-09 | 1 > >> Andy| 2010-09-21 | 3 > >> Tom | 2010-09-21 | 4 > >> Peter | 2010-09-21 | 1 > >> Peter | 2010-10-11 | 2 > >> > >> shall be transformed to a denormalized view like: > >> > >> student | grade_2010_09_09 | grade_2010_09_21 | grade_2010_10_11 > >> > >> Peter | 2| 1| 2 > >> Tom | 1| 4| NULL > >> Andy| NULL | 3| NULL > >> > >> I've already done extensive Web-search and posted in Usenet for help > >> concerning this problem and was pointed to the tablefunc module which > >> seems to be a solution. > >> Since I only have a database but no administrative rights for the > >> PostgreSQL installation, I can't use the tablefunc module. > >> Is there any way to denormalize my table using a simple SQL script? > >> > >> Thanks in advance, > >> Julia > > > > Hi Julia, > > > > If you're denormalizing it just for a report, you could do it in your > > application, and just ringtoss rows onto the test periods. > > > > If you want to have a permanent table containing the denormalized > > material (and one would have to ask why), then one possible method would > > be the same as for the report -- let your application ring toss rows onto > > the newly created table containing an array. Since you have no > > administrative rights, the DBA would need to create the denormalized > > table, and add another column every time there's a new exam. > > > > Let the darn thing run overnight, or perhaps do one exam at a time or a > > small range of students at a time. Do you happen to know why they want a > > denormalized table as opposed to just making an index sorted by student > > and then by grade period? Do you have any idea how long it would take to > > create an index sorted first by student and then by exam? > > > > I'm sure there are easier ways of doing it, but what I suggested is one > > way that it could work. > > > > HTH > > > > SteveT > > > > Steve Litt > > Recession Relief Package > > http://www.recession-relief.US > > Twitter: http://www.twitter.com/stevelitt > > Hello Steve, > > Thanks a lot for your answer. > Indeed, I actually want to denormalize my table for a report, but I need > to join the denormalized table with another table of the database for > this report. > So when I ring toss rows and columns in my application, it won't be > possible to do the join anymore. > Although I think PostgreSQL does good in not offering pivot tables like > Oracle or MS-SQL, I'm really desperately looking for a workaround here. > > Regards, > Julia Hi Julia, I liked Igor Neyman's suggestion and hope it fills the bill, but if that doesn't work for you, instead of setting up a join you could just have your app do lookups on the second table for each student/exam combination, or for each student. I don't know what your report needs to do, but if part of the problem is you need a student total in the student's page header instead of footer, you could do a 2 pass thing where pass 1 is for each student look up corresponding row in other table for each exam look up corresponding row in other table write exam temp table entry update student totals end write student header temp table entry write student footer temp table entry end Now here's the thing. The temp table is sorted by student, then line_type_flag, then exam. That flag is set by the various writes such that the flag for a student header sorts above the flag for the exam entries, which sorts above the student footer. Then you just iterate right down the temp table, in student/flag/exam order, and it maps right into your report. Like I say, I don't know if that was your problem domain, but I've used it quite a bit when my header needed to "read the tealeaves" to know what would only be known after the whole entity had been read and calculated. SteveT Steve Litt Recession Relief Package http://www.recession-relief.US Twitter: http://www.twitter.com/stevelitt -- 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] Crosstab query on huge amount of values
On Sunday 16 January 2011 17:40:34 Julia Jacobson wrote: > Hello everybody out there using PostgreSQL, > > A table with the results of students in different exams > > student | date_of_exam | grade > -- > Peter | 2010-09-09 | 2 > Tom | 2010-09-09 | 1 > Andy| 2010-09-21 | 3 > Tom | 2010-09-21 | 4 > Peter | 2010-09-21 | 1 > Peter | 2010-10-11 | 2 > > shall be transformed to a denormalized view like: > > student | grade_2010_09_09 | grade_2010_09_21 | grade_2010_10_11 > > Peter | 2| 1| 2 > Tom | 1| 4| NULL > Andy| NULL | 3| NULL > > I've already done extensive Web-search and posted in Usenet for help > concerning this problem and was pointed to the tablefunc module which > seems to be a solution. > Since I only have a database but no administrative rights for the > PostgreSQL installation, I can't use the tablefunc module. > Is there any way to denormalize my table using a simple SQL script? > > Thanks in advance, > Julia Hi Julia, If you're denormalizing it just for a report, you could do it in your application, and just ringtoss rows onto the test periods. If you want to have a permanent table containing the denormalized material (and one would have to ask why), then one possible method would be the same as for the report -- let your application ring toss rows onto the newly created table containing an array. Since you have no administrative rights, the DBA would need to create the denormalized table, and add another column every time there's a new exam. Let the darn thing run overnight, or perhaps do one exam at a time or a small range of students at a time. Do you happen to know why they want a denormalized table as opposed to just making an index sorted by student and then by grade period? Do you have any idea how long it would take to create an index sorted first by student and then by exam? I'm sure there are easier ways of doing it, but what I suggested is one way that it could work. HTH SteveT Steve Litt Recession Relief Package http://www.recession-relief.US Twitter: http://www.twitter.com/stevelitt -- 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] Why can't I change a password
On Sunday 16 January 2011 16:02:12 Raymond O'Donnell wrote: > If you have configured PG to listen on a TCP/IP port (5432 by default), > you can also do: > >psql -U postgres -h localhost super > > Ray. Thanks Ray, You were so close! The command that works is this: psql -U super -h localhost super That's because the super database is owned by the super user, in this particular case. Interestingly enough, even though I've set my port to be 5433 instead of 5432, it wasn't necessary for me to add -p 5433. Your method has the big benefit of being able to supervise from a Postgres Superuser who doesn't have a Linux account (super, in this case). Thanks for the info! SteveT Steve Litt Recession Relief Package http://www.recession-relief.US Twitter: http://www.twitter.com/stevelitt -- 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] Why can't I change a password
On Sunday 16 January 2011 16:02:12 Raymond O'Donnell wrote: > If you have configured PG to listen on a TCP/IP port (5432 by default), > you can also do: > >psql -U postgres -h localhost super > > Ray. Thanks Ray, My psql seems a lot different from others. Loook what happened: slitt@mydesk:~$ psql -U postgres -h localhost super Password for user postgres: psql: FATAL: password authentication failed for user "postgres" FATAL: password authentication failed for user "postgres" slitt@mydesk:~$ psql -U postgres -h 127.0.0.1 super Password for user postgres: psql: FATAL: password authentication failed for user "postgres" FATAL: password authentication failed for user "postgres" slitt@mydesk:~$ My postgresql.conf configures the port at 5433 instead of 5432, so I also tried this: slitt@mydesk:~$ psql -U postgres -h localhost -p 5433 super Password for user postgres: psql: FATAL: password authentication failed for user "postgres" FATAL: password authentication failed for user "postgres" slitt@mydesk:~$ Thanks Steve Steve Litt Recession Relief Package http://www.recession-relief.US Twitter: http://www.twitter.com/stevelitt -- 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] Why can't I change a password
Thanks Dmitriy, It turns out the solution I used was to su to postgres in Linux, and then run the command psql without arguments, at which time I could have my way with any object. More in my responses to you... On Sunday 16 January 2011 06:21:28 Dmitriy Igrishin wrote: > Hey Steve, > > 2011/1/16 Steve Litt > > > Hi all, > > > > I've somehow messed up something. > > > > psql super > > psql's synopsis is >psql [option...] [dbname [username]] > Thus, the call "psql super" connects psql to a database > "super" but since username unspecified it is connected > with current Unix user (which is returned by whois(1)). > > So, you should call psql like that > psql super super slitt@mydesk:~$ psql super super psql: FATAL: Ident authentication failed for user "super" slitt@mydesk:~$ psql postgres postgres psql: FATAL: Ident authentication failed for user "postgres" slitt@mydesk:~$ > or like that > psql -U super slitt@mydesk:~$ psql -U super psql: FATAL: Ident authentication failed for user "super" slitt@mydesk:~$ psql -U postgres psql: FATAL: Ident authentication failed for user "postgres" slitt@mydesk:~$ Now watch this: slitt@mydesk:~$ su - postgres Password: postgres@mydesk:~$ psql -U postgres psql (8.4.5) Type "help" for help. postgres=# Luckily for me, needing to be Linux user postgres isn't at all an inconvenience, so the problem is pretty much solved, although some curiosity remains. Thanks SteveT Steve Litt Recession Relief Package http://www.recession-relief.US Twitter: http://www.twitter.com/stevelitt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why can't I change a password
Hi all,
I've somehow messed up something.
psql super
psql (8.4.5)
Type "help" for help.
super=> \du+
List of roles
Role name | Attributes | Member of | Description
---+-+---+-
bobo | | {}|
junk | | {}|
myuid | | {}|
postgres | Superuser | {}|
: Create role
: Create DB
slitt | Create DB | {}|
super | Superuser | {}|
: Create role
: Create DB
super=>
super=> alter user myuid password 'mypass';
ERROR: permission denied
super=>
I don't get it. User super is listed as attributes Superuser, create role and
createdb, so why the lack of permission? This also happened when I was in as
"postgres".
Interestingly, earlier tonight I was changing passwords just fine. I don't
know what happened. Obviously there's a chunk of information I'm missing.
What diagnostic tests can I do to narrow this down?
Thanks
SteveT
Steve Litt
Recession Relief Package
http://www.recession-relief.US
Twitter: http://www.twitter.com/stevelitt
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
