Re: [GENERAL] shared data for different applications
On 22 Nov 2010, at 4:43, Kent Tong wrote: > Hi Alban, > > Thanks a lot for your useful info! > >> I think most companies have ended up at that point just by the progress >> of time. They have several different databases (often from different >> vendors even) that they need to aggregate their information from. > > So, is this the result of lack of central coordination or a carefully > thought-out decision? It's usually a mix of both. While a company grows, requirements change, for example. Also, there tend to be 3rd-party applications that don't work with the company's database of choice, necessitating to install a second database, etc. Being in a situation where you get to decide this without the historic requirements is pretty cool, but, no matter how careful you plan now, history tends to catch up with you. That doesn't mean you shouldn't try your best to prevent it to, though ;) >> Usually the different databases contain different kinds of contents, so >> the need for distributed transactions and such is quite minimal. > > Let's say, if a customer would like to change his address through > a certain app (eg, the web interface for customers), and assuming > that customer info is shared across the whole company, then a > distributed transaction will be required, right? Or there is a better > approach? I don't really see why that would require a distributed transaction. They can just directly change a record in the master database, can't they? If I were you, I certainly wouldn't let them change their _live_ data directly in your production database though! You'll want somebody (in your company) to approve what they entered - people who're not familiar with a system (it's yours, not theirs, after all) are bound to make mistakes, no matter how obvious you make the interface. There are all kinds of approaches to that though, it doesn't mean you need a separate database for their data. You could for example keep an approval flag if the data is from the application that the customers use to update their data. You could also keep a separate database around, but then you're quickly moving in the direction of master-master replication, which is really quite complicated due to the conflicting data it tends to generate between masters. Now, before you storm off to implement what I'm telling you; I'm not an expert on this issue. I know my databases and all, but I haven't been in your position and I've never had an opportunity (or a reason) to put replication to practice. I've read a lot about it, mostly from this mailing list, but my knowledge in that respect is mostly theoretical. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4cea1ff710421896774915! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What is Dialect
Dear all, I am reading about Dialects of different databases. Yet I can't understand what is the need of dialect in Postgres or any other like Hibernate uses Dialect of all Databases for ORM. What is it & How can we create our own Dialect ? Thanks in Advance Adarsh Sharma -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Removing duplicates from multiple self left joins
I am dynamically generating a query like below that creates different combinations of rules by left joining (any number of times) on itself and avoiding rules with some of the same attributes as part of the joins conditions e.g. SELECT count(*) FROM rules AS t1 LEFT JOIN rules AS t2 ON t1.id != t2.id AND ... LEFT JOIN rules AS t3 ON t1.id != t2.id AND t1.id != t3.id AND t2.id != t3.id AND ... I am currently removing duplicates by creating an array of ids from the joined rows then sorting and grouping by them: SELECT sort(array[t1.id, t2.id, t3.id]) ... GROUP BY ids I would like to know if there is a better way of removing duplicate rows e.g. t1.ID | t2.ID | t3.ID - A | B | C C | B | A Should be t1.ID | t2.ID | t3.ID - A | B | C Or t1.ID | t2.ID | t3.ID - C | B | A But not both. I would like to go from a permutation of rows to a combination rows.
Re: Fwd: [GENERAL] [pgsql-www] Forums at postgresql.com.au
On 22/11/10 07:40, Elliot Chance wrote: > It does surprise me a bit that when I (or someone else) signs up to a mailing > list (not postgres specifically) that there is no fine print or agreement > that says something along the lines of "Your email address will be plastered > all over the internet, guaranteed to be picked up by spiders, make sure you > have a good anti-spam." > > This doesn't so much bother me because the address I use on the mailing list > is public and already on googles index but I bet some people don't like it, > and once they realise its too late you can't remove emails from a mailing > list. Forums are designed to act as the barrier that stops anyone from > getting your address if you so choose. Spammers routinely subscribe to mailing lists and scrape addresses out of incoming mail, so filtering archives doesn't do much good these days. As far as I'm concerned it's way past the day when hiding your email address was useful. Some PhpBB or Wordpress forum you sign up to will get cracked and a spammer will scrape the email addresses from the database. Someone you know will have a crappy webmail account cracked, or their password recovery question(s) guessed, and a spammer will scrape your address from their address book before using their account to flood out spam. Someone else will have a trojan or worm hit their machine, doing much the same thing to the addressbook and recent-recipients address lists in their rich mail client. Another spammer will get your email address along with your credit card details when they crack the poorly secured database of somewebstore.com . Someone else gets it when you sign up to the account required to actually download the software you just bought from mudbricksoftware.com when you accept the "really, we promise not to pass your address on, honest" checkbox. And so on. The only real answer is decent anti-spam software. Per-list addresses can help a little, but personally I prefer to have it all come to one mailbox. -- System & Network Administrator POST Newspapers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] [pgsql-www] Forums at postgresql.com.au
On Mon, Nov 22, 2010 at 10:40:34AM +1100, Elliot Chance wrote: > It does surprise me a bit that when I (or someone else) signs up to > a mailing list (not postgres specifically) that there is no fine > print or agreement that says something along the lines of "Your > email address will be plastered all over the internet, guaranteed to > be picked up by spiders, make sure you have a good anti-spam." If you imagine that not signing up for a mailing list in any way alleviates this need, I have a bridge to sell you. It connects Manhattan with Brooklyn. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] shared data for different applications
Hi Alban, Thanks a lot for your useful info! > I think most companies have ended up at that point just by the progress > of time. They have several different databases (often from different > vendors even) that they need to aggregate their information from. So, is this the result of lack of central coordination or a carefully thought-out decision? > Usually the different databases contain different kinds of contents, so > the need for distributed transactions and such is quite minimal. Let's say, if a customer would like to change his address through a certain app (eg, the web interface for customers), and assuming that customer info is shared across the whole company, then a distributed transaction will be required, right? Or there is a better approach? > That said, separating things out would be an improvement. For example, > for generating reports (big aggregations of many data sources at once), > you tend to generate heavy-weight queries that are likely to impact > other queries (unless your database server is capable of limiting I/O > and such per process, that helps some). I see. For reporting, I agree that using a replicated copy of the database is a good way to do it. > You limit access to who can touch what and you replicate. Fully agree. I am just concerned that maintenance work for a schema may have foreseen or un foreseen impacts on the other schemas as they're in the same DB on the same server. > I wouldn't go so far as to create a separate database for every business > unit though, the distinction is more a role-based one than an > organisational one - there will be overlap between who has access to > what. Fully agree. Therefore I've seen people suggesting doing it on a process and data flow basis. > That said, unless you're in a very large company, a central database will > probably do for almost anything. The exceptions are more likely to be > among the lines of reporting and frequent short but specialised queries > for, for example, machine performance statistics. Thanks for the clear conclusion. Wondering how common is it in practice (I am not really familiar with the data management status quo)? > At the company where I work we have a central DB2/400 database for > production, shipping and sales. That database gets replicated for > reporting. We also have a few separate MS SQL databases where for > example machines on our production facility send their performance > statistics, which they get back in an aggregated form every 5 minutes > or so. > > It isn't ideal, but that 20-year old DB2/400 database (although the > hardware's been upgraded to i5-series or so) can handle its load just > fine. Thanks a lot for sharing the practical info! It is very useful! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] [pgsql-www] Forums at postgresql.com.au
On Mon, 2010-11-22 at 10:40 +1100, Elliot Chance wrote: > > I would argue that if the person wants to use a forum, aren't they > > saying they don't want to be contacted via email. I think we just throw > > it only to the forum (that is the user) and leave it that. Forum users > > don't get the _rich_ email experience. ;-) > > It does surprise me a bit that when I (or someone else) signs up to a mailing > list (not postgres specifically) that there is no fine print or agreement > that says something along the lines of "Your email address will be plastered > all over the internet, guaranteed to be picked up by spiders, make sure you > have a good anti-spam." > > This doesn't so much bother me because the address I use on the mailing list > is public and already on googles index but I bet some people don't like it, > and once they realise its too late you can't remove emails from a mailing > list. Forums are designed to act as the barrier that stops anyone from > getting your address if you so choose. Isn't this kind of the disclaimer for the Internet as a whole? JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] SYSCONFDIR, initdb and postgresql.conf
On 2010-11-21, Tom Lane wrote: > SYSCONFDIR is only used for global configuration files, like the default > psqlrc or pg_service.conf. OK, so it doesn't regard postgresql.conf and friends as conf files in that sense. > It would be pretty inappropriate to put postgresql.conf there > because postgresql.conf is a per-cluster configuration file. Debian does it with a hierarchy under /etc/postgres that reflects the versions and clusters installed. E.g. /etc/postgres/8.4/main holds the cluster-conf files for the 'main' cluster running 8.4. > Having said that, you don't have to put postgresql.conf in the data > directory if you don't want to. Just move it to where you do want it > (along with the other cluster config files) and add an entry to it to > point to the actual data directory. Beware that this arrangement isn't > supported as fully as the default --- in particular, I think pg_ctl > will have some trouble with it. It wants a '-o' to tell postgres where its config is. Debian uses a system of ingenious wrapper scripts that automatically set it. My poor man's version seems to be working in my /etc/rc.local, su -l _postgresql -c "nohup /usr/local/bin/pg_ctl start \ -D /var/postgresql/9.0/main -l /var/postgresql/logfile \ -o '-D /var/postgresql/9.0/main' \ -o '-c config_file=/etc/postgresql/9.0/main/postgresql.conf' \ >/dev/null" > regards, tom lane Thanks -- KM -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] Postgres forums ... take 2
On Sat, Nov 20, 2010 at 16:54, Bruce Momjian wrote: > Elliot Chance wrote: >> > Also, if someone registers on the forum, do they get a major domo >> > registration email? And if so, would this be set to receive no emails >> > upon registration? I'm not clear as to how this step would work because, >> > at the moment, mailing list subscribers have to subscribe on a >> > list-by-list basis. So registration to the forum site wouldn't >> > necessarily mean they'd want to join any particular mailing list. >> > Similarly, could they unregister easily? And anyone who attempts to post >> > to a mailing list they aren't subscribed to requires moderation, so we >> > don't wish to exacerbate this. >> >> No they are not registered on the mailing list, but they actually don't need >> to be, let me explain: >> 1. John Smith has a postgres related question and finds the forums, he signs >> up and posts his question. >> 2. His post is then emailed to the mailing list under a generic registered >> address like "mailingl...@postgresql.com.au" >> 3. Bob House reads Johns question on the mailing list and simply sends an >> email reply. >> 4. The email reply is piped into the forum and matches the topic based on >> the email subject (thats how it currently does it.) >> 5. John gets an email from phpBB along the lines of "Bob House has replied >> to your post, click here" (all forums do this) he reads the response and is >> happy. >> >> This is the best balance of no-fuss and expert response, keeping in mind >> that: >> * John can still sign up to the mailing list like anyone else if he wants to. >> * All of John's forums communications are in the postgres mailing list >> archive now. > > Yes, I think this is fine. We would need to be more careful that a > non-group _reply_ would now be going to a public place. I doubt people will really remember that. However, the forums could be given a big disclaimer on posts saying that private replies may show up public, or it could even add it to the footer of the message (sure, nobody reads that, but at least we tried..) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] [pgsql-www] Forums at postgresql.com.au
On 21/11/2010, at 2:59 AM, Bruce Momjian wrote: > Magnus Hagander wrote: >> On Sat, Nov 20, 2010 at 14:46, Elliot Chance wrote: > for...@postgresql.com.au is pointed to a black hole so that email > disappears but the mailing list gets another copy. When the mailing list > gets its copy it sends a copy to the forum (because the forum is just > like a subscribed user), the parser then dissects the headers to find out > where the post belongs. We already know this part works. So how does one respond to the user? >>> >>> I can't explain it any clearer, your email response goes to the mailing >>> list and that mailing list sends a copy to the original person thats how a >>> mailing list works. It also sends a copy to the forum which is parses you >>> and that person and anyone else can see the reply on the forum. >> >> Clearly you're not understanding my point. I don't *want* it to go to >> the list. I want to write a private email to the user who made a post >> from the forum, without having to set up and use a forum account. Just >> a simple response, just the way I can do now. >> >> But I'll leave it to somebody else to attempt to explain that, since I >> clearly am unable to get it across. > > I would argue that if the person wants to use a forum, aren't they > saying they don't want to be contacted via email. I think we just throw > it only to the forum (that is the user) and leave it that. Forum users > don't get the _rich_ email experience. ;-) It does surprise me a bit that when I (or someone else) signs up to a mailing list (not postgres specifically) that there is no fine print or agreement that says something along the lines of "Your email address will be plastered all over the internet, guaranteed to be picked up by spiders, make sure you have a good anti-spam." This doesn't so much bother me because the address I use on the mailing list is public and already on googles index but I bet some people don't like it, and once they realise its too late you can't remove emails from a mailing list. Forums are designed to act as the barrier that stops anyone from getting your address if you so choose. - Now to elaborate on private messages. 1. I'm not going to use the persons real email without their permission regardless of if the safe-guards of a mailing list can be subverted. 2. It's no problem to put a line at the top of the email saying something like (I understand what you mean now Tom): "Replying to this email will be PUBLIC. If you would like to send a private response to "chancey" send your response to df62a...@postgresql.com.au." The "df62a...@postgresql.com.au" is a cached salted hash that stops anyone from guessing everyones username, and perhaps expires after 30 days or something. After which they can goto the persons profile page and send the message from their if they are desperate for a private reply. 3. That private message line will be stripped out before replies are shown on the forum since its not relevant information to the forum. 4. The "df62a...@postgresql.com.au" also does not go directly to the person but passes the message through the private message system in the forum (which of course still notifies them.) This is what forum users would expect. In particular but in my time as a developer one thing i've learnt is you not only have to make systems ultra idiot proof but also move all the important bits our of reach of that user. If we incorporated mailing list subscriptions with posting it will absolutely cause more problems that the benefits it brings, even if it sounds fine in *theory*. > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + -- 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] [pgsql-www] Forums at postgresql.com.au
On 21 Nov 2010, at 16:16, Trevor Talbot wrote: >> I do see a difficulty here; if the forum software is only subscribed with >> one e-mail address, how is it going to distinguish between a reply-all and a >> private reply? >> Maybe it would help to subscribe it using two or three addresses, so that >> you can see if both (or at least two out of three) addresses got the reply, >> or only one? > > Once the forum software determines an incoming email is meant to be a > private message, how would it determine which user it is meant for? I don't expect the forum software to post the message anonymously to the ML. It wouldn't be difficult to at least include the username (as used on the forum) in the message body, and perhaps work that into the message headers, in such a way that that information can be obtained from a reply to the message. Of course, the way e-mail works, there's not much guarantee that anything but the original senders' e-mail address is preserved, but such cases can be detected and be acted upon - although probably not entirely transparently For example, receiving a message that a recipient could not be reached from the recipient itself - the global forum user account - would seem a bit odd. It's not that different from receiving a mail from a mail-server that a recipient could not be found though, except that the error isn't with the address, but with related headers or the message body. You can reach the user, but it probably can not be guaranteed 100%. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4ce93e6810421257911754! -- 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] Fwd: [pgsql-www] Forums at postgresql.com.au
Elliot Chance writes: > On 21/11/2010, at 2:41 AM, Tom Lane wrote: >> Elliot Chance writes: >>> Then I can create a catch-all so that when an email is sent to >>> forums-chan...@postgresql.com.au it finds the user "chancey" gets the real >>> address and sends it on. If there were a way we could register a range for >>> mj2 like accept all emails from forum...@posgresql.com.au then I think that >>> solution would work well. >> So any spammer that knows that can blast the lists at will? > No, because their spam email would have to go through the mailing list before > it reaches the forum and the mailing list would simply reject it if they were > not subscribed. Uh, how you figure that, if the mailing list has been hacked so that it will accept mail from a large range of names that aren't in fact subscribed? regards, tom lane -- 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] [pgsql-www] Forums at postgresql.com.au
Alban Hertroys writes: > I'm not one of the people who've been communicating off-list about this with > him, so I may be wrong, but to my understanding what Magnus wants (the > requirement, not a solution to it) is this: > - Person A is on the forums and sends a message that ends up on the ML (and > on the forums, naturally). > - Someone on the ML, Person B, sends him a _private_ reply, not intended to > end up in either the ML or the forums. > - The message goes to the forum software and is passed on to Person A, and > does _not_ end up on the forums or the ML. I tend to agree with Bruce that that's not necessarily a hard requirement: a person who's using the forums to post has more or less disclaimed interest in getting private email, no? But what I think we *do* need to worry about is that someone using the mailing lists might try to send what they *think* is a private reply. We need to be sure that a reply-to-sender-only operation does not end up getting splattered across the forums and/or lists. Maybe bouncing it is sufficient, in which case ML posts originating from forum users don't have to carry a working From: address. What I'm more concerned about myself is that forum users be identifiable. We don't allow anonymous trolls on the mailing lists, and I'm not pleased at the idea that a forum might provide an end-run around that. I don't necessarily think that every posting has to carry a working From: address to meet that requirement, though. A forum handle of some sort would probably be sufficient. regards, tom lane -- 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] [pgsql-www] Forums at postgresql.com.au
On Sun, Nov 21, 2010 at 18:12, Tom Lane wrote: > Alban Hertroys writes: >> I'm not one of the people who've been communicating off-list about this with >> him, so I may be wrong, but to my understanding what Magnus wants (the >> requirement, not a solution to it) is this: > >> - Person A is on the forums and sends a message that ends up on the ML (and >> on the forums, naturally). >> - Someone on the ML, Person B, sends him a _private_ reply, not intended to >> end up in either the ML or the forums. >> - The message goes to the forum software and is passed on to Person A, and >> does _not_ end up on the forums or the ML. > > I tend to agree with Bruce that that's not necessarily a hard > requirement: a person who's using the forums to post has more or less > disclaimed interest in getting private email, no? > > But what I think we *do* need to worry about is that someone using the > mailing lists might try to send what they *think* is a private reply. > We need to be sure that a reply-to-sender-only operation does not end up > getting splattered across the forums and/or lists. Maybe bouncing it is > sufficient, in which case ML posts originating from forum users don't > have to carry a working From: address. Depends on your definitoin of working From. I think they need to carry a working From, from the perspective of SMTP, because otherwise they run a higher risk of getting eaten by anti-spam. That From doesn't necessarily need to go to the user - if it goes to something that does a "controlled bounce" informing the original poster, that should still work. > What I'm more concerned about myself is that forum users be > identifiable. We don't allow anonymous trolls on the mailing lists, > and I'm not pleased at the idea that a forum might provide an end-run > around that. I don't necessarily think that every posting has to > carry a working From: address to meet that requirement, though. > A forum handle of some sort would probably be sufficient. The forum is, AIUI, requiring a validated registration, so we know that it's not just a completely unidentified person. I assume the forum will be putting the registered *name* in the name part of the From field still, even if it uses a shared address. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] [pgsql-www] Forums at postgresql.com.au
On 11/21/2010 06:04 AM, Alban Hertroys wrote: > On 21 Nov 2010, at 24:17, Trevor Talbot wrote: > >>> I can't explain it any clearer, your email response goes to the mailing >>> list and that mailing list sends a copy to the original person thats how a >>> mailing list works. It also sends a copy to the forum which is parses you >>> and that person and anyone else can see the reply on the forum. >> >> Elliot, Magnus wants forum->list email to come from a per-user address >> so that when he replies directly to that address (without sending it >> to the list), the response is mapped to a PM. > > Actually, I think that's only your approach to a possible solution to achieve > what he wants? > > I'm not one of the people who've been communicating off-list about this with > him, so I may be wrong, but to my understanding what Magnus wants (the > requirement, not a solution to it) is this: > > - Person A is on the forums and sends a message that ends up on the ML (and > on the forums, naturally). > - Someone on the ML, Person B, sends him a _private_ reply, not intended to > end up in either the ML or the forums. > - The message goes to the forum software and is passed on to Person A, and > does _not_ end up on the forums or the ML. I rarely use forums (I access most MLs via Gmane, NNTP and a newsreader which is *the* right way to do it :-) but ISTM insisting that a ML user *must* be able to send a private message to a forum user, should not only not be a show-stopper, but could be construed as a misfeature. Forums and MLs have different features which is why some people prefer one over the other. A feature forums have is that the user community is somewhat restricted compared to a ML -- people who can send me PMs are limited to other forums users that have been validated (to a forum-dependent extent) by the forum operators. I may not want anyone who can get a ML reply address off the internet to send me a PM. If you need to send me a PM but can't be bothered to register on the PG forum to do so (just as you would have to on any other forum) then one has to question the use of the word, "need". I also note that even on the ML, you have no *right* to reply in private to me. Not on the PG list but on many others I subscribe with a temporary or unmonitored mailbox and read messages via NNTP or Google or whatever. I have a personal policy (which is a convention on many lists) that replies to public messages should be public -- if you really need to contact me privately, say so in your public reply and I will figure out a way to make it happen. It is an extremely rare need in my experience. And if it is important to you that people be able to respond privately to you, then your choice is clear -- use the ML. So demanding that the forum *must* provide a way for a mailing user to send private replies to forum users strikes me as more obstructionist than helpful. Do some of the people insisting on private email replies have some official role in the PG mailing lists? Perhaps some of this is just a plain old- fashioned turf battle? -- 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] SYSCONFDIR, initdb and postgresql.conf
KM writes: > On an OpenBSD machine I just compiled and installed 9.0.1. The > ./configure arguments included '--sysconfdir=/etc'. Running > 'pg_config --sysconfdir' returns '/etc/postgresql'. The cluster is > running and I can create a database and connect to it. > However, initdb put the config files in the directory named by the > --pgdata option. There is no directory /etc/postgresql. > Should I expect this? How do I persuade postgres to put its config > under /etc? SYSCONFDIR is only used for global configuration files, like the default psqlrc or pg_service.conf. It would be pretty inappropriate to put postgresql.conf there because postgresql.conf is a per-cluster configuration file. Having said that, you don't have to put postgresql.conf in the data directory if you don't want to. Just move it to where you do want it (along with the other cluster config files) and add an entry to it to point to the actual data directory. Beware that this arrangement isn't supported as fully as the default --- in particular, I think pg_ctl will have some trouble with it. regards, tom lane -- 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] finding rows with invalid characters
Hey Sim, Maybe this helps: http://blog.tapoueh.org/articles/blog/_Getting_out_of_SQL_ASCII,_part_2.html 2010/11/21 Sim Zacks > I am using PG 8.2.17 with UTF8 encoding. > "PostgreSQL 8.2.17 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 > (Gentoo 4.1.1)" > > One of my tables somehow has invalid characters in it: > >> ERROR: invalid byte sequence for encoding "UTF8": 0xa9 >> HINT: This error can also happen if the byte sequence does not match the >> encoding expected by the server, which is controlled by "client_encoding". >> > I have already manually found a number of the bad rows by running queries > with text functions (upper) between groups of IDs until I found the specific > bad row. > > 1) Is there a quicker way to get a list of all rows with invalid characters > 2) Shouldn't the database prevent these rows from being entered in the > first place? > 3) I have backups of this database (using -Fc) and I noticed that on > restore, this table is not restored because of this error. Is there a way to > fix the existing backups, or tell the restore to ignore bad rows instead of > erroring out the whole table? > > Thanks > Sim > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- // Dmitriy.
Re: [GENERAL] [pgsql-www] Forums at postgresql.com.au
On Sun, Nov 21, 2010 at 05:04, Alban Hertroys wrote: > On 21 Nov 2010, at 24:17, Trevor Talbot wrote: >> Elliot, Magnus wants forum->list email to come from a per-user address >> so that when he replies directly to that address (without sending it >> to the list), the response is mapped to a PM. > Actually, I think that's only your approach to a possible solution to achieve > what he wants? I was simply trying to "translate" the issues between the two environments, as it were. A per-user email address is what Magnus requested: >>> Based on that, I'm back to saying that the email has to be generated >>> from a valid email address, that can be used for return traffic. >>> Whether it's the users original address or a forum-specific one is a >>> different question, but a blackhole catch-all one just won't do. > I do see a difficulty here; if the forum software is only subscribed with one > e-mail address, how is it going to distinguish between a reply-all and a > private reply? > Maybe it would help to subscribe it using two or three addresses, so that you > can see if both (or at least two out of three) addresses got the reply, or > only one? Once the forum software determines an incoming email is meant to be a private message, how would it determine which user it is meant for? -- 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] shared data for different applications
On 21 Nov 2010, at 5:08, Kent Tong wrote: > Hi, > > Let's say that there is some data that should be logically shared by > many applications in the company, such as some core information about > its customers (name, address, contact info). In principle, such data > should be stored in a DB for sharing. > > But then if a certain application needs to access such shared data, it > will need to access its own DB plus this shared DB, which is > troublesome and requires distributed transactions. I think most companies have ended up at that point just by the progress of time. They have several different databases (often from different vendors even) that they need to aggregate their information from. Usually the different databases contain different kinds of contents, so the need for distributed transactions and such is quite minimal. Where there is a need to keep relational integrity, the related data is usually in one big central database. You'd be amazed how much a database like that can handle! That said, separating things out would be an improvement. For example, for generating reports (big aggregations of many data sources at once), you tend to generate heavy-weight queries that are likely to impact other queries (unless your database server is capable of limiting I/O and such per process, that helps some). > An alternative is to store everything into a single DB and use, say, > schemas to separate the data. The shared data can be put into one > schema and that application can have its own schema. This eliminates > the need for distributed transactions but then you'll have a single > DB for the whole company! This creates problems in: > 1) availability: if the DB is down, everything is down. > 2) maintenance: if we need to deploy a new application, we'll need to > create a new schema in that single DB, potentially impacting other > apps. > 3) performance: all apps are access that single DB server. > > I was wondering in practice, how people address this problem? You limit access to who can touch what and you replicate. For example, the people in your sales department will need to be able to add and modify customer information, but the guys in the IT department don't need to. So the first group gets access to a database server where the customer database is a master, while the others get access to a read-only slave. I wouldn't go so far as to create a separate database for every business unit though, the distinction is more a role-based one than an organisational one - there will be overlap between who has access to what. That said, unless you're in a very large company, a central database will probably do for almost anything. The exceptions are more likely to be among the lines of reporting and frequent short but specialised queries for, for example, machine performance statistics. At the company where I work we have a central DB2/400 database for production, shipping and sales. That database gets replicated for reporting. We also have a few separate MS SQL databases where for example machines on our production facility send their performance statistics, which they get back in an aggregated form every 5 minutes or so. It isn't ideal, but that 20-year old DB2/400 database (although the hardware's been upgraded to i5-series or so) can handle its load just fine. I suspect that Postgres would actually perform better, but you can't just switch a big 24/7 company from one database to another (provided I had anything to say about it at all, which I don't). That's an expensive, time-consuming and risky process. Just saying, I don't know from experience how well Postgres would fare there, as it's not what we're using. I have no reason to suspect it to perform less well though. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4ce923f910421136214443! -- 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] [pgsql-www] Forums at postgresql.com.au
On 21 Nov 2010, at 24:17, Trevor Talbot wrote: >> I can't explain it any clearer, your email response goes to the mailing list >> and that mailing list sends a copy to the original person thats how a >> mailing list works. It also sends a copy to the forum which is parses you >> and that person and anyone else can see the reply on the forum. > > Elliot, Magnus wants forum->list email to come from a per-user address > so that when he replies directly to that address (without sending it > to the list), the response is mapped to a PM. Actually, I think that's only your approach to a possible solution to achieve what he wants? I'm not one of the people who've been communicating off-list about this with him, so I may be wrong, but to my understanding what Magnus wants (the requirement, not a solution to it) is this: - Person A is on the forums and sends a message that ends up on the ML (and on the forums, naturally). - Someone on the ML, Person B, sends him a _private_ reply, not intended to end up in either the ML or the forums. - The message goes to the forum software and is passed on to Person A, and does _not_ end up on the forums or the ML. I do see a difficulty here; if the forum software is only subscribed with one e-mail address, how is it going to distinguish between a reply-all and a private reply? Maybe it would help to subscribe it using two or three addresses, so that you can see if both (or at least two out of three) addresses got the reply, or only one? I'm pretty sure the end result that Magnus (and me, and probably many more on this ML) intends is a forum layer that is _transparent_ to the list. We're not going to change our habits because there's suddenly a forum connected to our ML, you need to be able to and willing to deal with that or you're not up to this project. Personally I'm not particularly pleased with your choice of PhpBB, it's got a few dark areas in its history - they seem more concerned with skins than with security (Plus, it's written in PHP, which is a mess), so forgive me for being a bit skeptic. Solving the issue is a different matter. Giving every forum poster their own e-mail address is a possible solution. I think that subscribing the forum with a few e-mail addresses is another, and that will also give you redundancy in case one of those addresses encounters a problem. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4ce9194510421646015157! -- 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 rows with invalid characters
I am using PG 8.2.17 with UTF8 encoding. "PostgreSQL 8.2.17 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 (Gentoo 4.1.1)" One of my tables somehow has invalid characters in it: ERROR: invalid byte sequence for encoding "UTF8": 0xa9 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". I have already manually found a number of the bad rows by running queries with text functions (upper) between groups of IDs until I found the specific bad row. 1) Is there a quicker way to get a list of all rows with invalid characters 2) Shouldn't the database prevent these rows from being entered in the first place? 3) I have backups of this database (using -Fc) and I noticed that on restore, this table is not restored because of this error. Is there a way to fix the existing backups, or tell the restore to ignore bad rows instead of erroring out the whole table? Thanks Sim -- 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] PostgreSQL 9.0 RPMs for RHEL 6 and Fedora 14 released
2010/11/14 Devrim GÜNDÜZ : > > I just released PostgreSQL 9.0 RPM for Red Hat Enterprise Linux 6 and > Fedora 14, on both x86 and x86_64. > > Please note that 9.0 packages have a different layout as compared to > previous ones. You may want to read this blog post about this first: > > http://people.planetpostgresql.org/devrim/index.php?/archives/48-What-is-new-in-PostgreSQL-9.0-RPMs.html > > Installing PostgreSQL 9.0 on these platforms are quite easy. First, > install repository RPM from here: > > http://yum.pgrpms.org/reporpms/repoview/letter_p.group.html > > Then, > > yum groupinstall "PostgreSQL Database Server PGDG" > > will install minimum package sets for you. > > Here are all packages that have been released so far: > > RHEL 6: > > http://yum.pgrpms.org/9.0/redhat/rhel-6-i386/repoview/ > http://yum.pgrpms.org/9.0/redhat/rhel-6-x86_64/repoview/ > > Fedora 14: > > http://yum.pgrpms.org/9.0/fedora/fedora-14-i386/repoview/ > http://yum.pgrpms.org/9.0/fedora/fedora-14-x86_64/repoview/ > > If you find any issues with the repository or packaging, please send an > e-mail to me. > > Regards, > -- > Devrim GÜNDÜZ > PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer > PostgreSQL RPM Repository: http://yum.pgrpms.org > Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr > http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz > I am unable to obtain (using yum) a version of pgAdmin3 that can connect fruitfully to postgreSQL 9.x. My installation reports that the version I do have 1.10.5 is the latest. I am running FC12 64bit and I have installed the latest repository as advised here "http://yum.pgrpms.org/reporpms/repoview/letter_p.group.html"; but it seems it only provides me with pgadmin3 1.10.5 as shown below. Package pgadmin3-1.10.5-1.fc12.x86_64 already installed and latest version Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
