[GENERAL] SYSCONFDIR, initdb and postgresql.conf
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? 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: [GENERAL] newbie question - delete before insert
How do people implement insert or upate ( otherwise known as upsert ) behaviour in postgres i.e. insert a row if it's key does not exist in the database else update the existing row? I tried using an insert rule to delete any existing rows first then insert however this leads to infinitely recursive rules ( which postgres properly rejects. ) I'm guesisng that the most sensible approach would be a stored proc/function? I've done it with an on insert trigger. The where clause contains the values that make this row unique. If it finds another row with the same fields, it doe san update instead of the insert: CREATE OR REPLACE FUNCTION stock.trg_beforeinsertstock() RETURNS "trigger" AS $BODY$ declare v_stockid int; begin select stockid into v_stockid from stock where pnid=new.pnid and ownerid=new.ownerid and coalesce(stocklocationid,-1)=coalesce(new.stocklocationid,-1) and coalesce(batchid,-1)=coalesce(new.batchid,-1); if v_stockid is not null then Update stock set stock=stock+new.stock where stockid=v_stockid; return null; else return new; end if; end; $BODY$ LANGUAGE plpgsql VOLATILE; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] shared data for different applications
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. 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? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] shared data for different applications
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. 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? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cancel a query.
Hi I have some code using psycopg in python. Connecting in async mode. I am trying to catch time outs etc, basically after a set amount of time I am assuming something has failed. I then want to use "select pg_cancel_backend(15209);" to cancel the query. But I can't unless I am connected as the postgres super user. How can I get my app to cancel one of it's own queries while logged on as a normal user? Thanks. -- Rob -- 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] The first dedicated PostgreSQL forum
Apple Mail has not been copying the mailing list, so excuse the reposts if you got one. Begin forwarded message: > From: Elliot Chance > Date: 14 November 2010 12:30:19 PM AEDT > To: Tom Lane > Subject: Re: [GENERAL] The first dedicated PostgreSQL forum > > > On 14/11/2010, at 6:24 AM, Tom Lane wrote: > >> Thom Brown writes: >>> It's a shame that a forum can't act as a front-end for a mailing list, so >>> signing up to the forum actually signs you up to a mailing list (if you're >>> not already signed up), but without receiving any emails. Messages posted >>> to the forum would get sent to the list, and vice versa. >> >> That would be cool. Wonder if there's something like that out there already. > > I have no problem with this idea. Rather than trying to write a MOD to do > this you (or someone) could create a new account with the mailing list > address, then subscribe that account to all or some of the forums. Any posts > made will be send to the subscriber (which is the mailing list) with nice > URLs to reply. This could be implemented immediately. > > "Content is king." There is a huge amount of archives, and before creating > the forum I did think of ways to cleanly get all the archives into the forums > but its fraught with problems, for example to map the email users to fresh > accounts would require creating forums accounts with generic passwords or > having to contact the original mailing list posters to give them their forum > password ... messy. More importantly that information belongs to the greater > postgres community and I would have no right to simply copy it all without > permission. > > I use other mailing lists (musicbrainz.org, for example) and they work as > intended but often you want to ask a question and not get dozens of emails > coming to you about other issues. This is where forums are useful (yes, > musicbrainz has a forum of which I am a moderator) - but some people like > them and some don't, theres no reason why that has to change. > > To those who say, it won't work and therefore don't bother. To not even try > when forums are clearly one ideal alternative for the postgres community > seems silly. Even the largest forums had to start with 0 posts, I don't > expect it to take off over night but it has to start somewhere. > > The potential is only as strong as the community is willing. I understand > nobody wants to sign up to an empty forum but the forum can only grow with > support from the majority of people on this list (if they so choose.) > > Thanks to the first 4 people who did sign up, feel free to make some posts :) > >> >>> And such a system >>> would need to support cross-posting too somehow. >> >> Dunno, seems like we've been discouraging cross-posting lately anyhow. >> I wouldn't see anything wrong with forum posts only being able to go to >> one mailing list, as long as there were a forum corresponding to each >> active list. >> >> 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 Nov 20, 2010, at 8:22 , Elliot Chance wrote: > One very annoying thing about Apple Mail with these lists is that when I hit > reply if I don't change the To address to the mailing list or manually add > the Cc then it doesn't even get sent to the mailing list. Use Reply to All: Cmd-Shift-R. Michael Glaesemann grzm seespotcode net -- 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
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. -- 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] Fwd: [pgsql-www] Forums at postgresql.com.au
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? > > regards, tom lane 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. They can't send emails to the forum directly because there is are secret email addresses that are never given out or shown. -- 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 Sat, Nov 20, 2010 at 05:46, Elliot Chance wrote: > On 21/11/2010, at 12:32 AM, Magnus Hagander wrote: >> On Sat, Nov 20, 2010 at 14:22, Elliot Chance wrote: >> Use "Reply To All" when you want to send to the list. It's what >> everybody else has been doing for ages :-) If you want to read up on >> the bike-shedding that goes behind that preference, it is something >> that comes up regularly - just search the archives. >>> 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. 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. Magnus, I see a couple issues with that: 1 - Conventionally, private messages are not used quite that often in forums. They are truly separate from public discussion, not a natural part of it by virtue of being the same ultimate message destination. 2 - Since Reply to All is a convention on this list, the forum needs to deal with that somehow. Both a private and public response containing the same content is unacceptable for forums. The public response is always preferred and can't be integrated later, such as in the common "solution" of having the list software not send a copy when it detects a person's address already in the address list. -- 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] Comparing first 3 numbers of a IPv4 address?
Hey Tom,
Thanks for you solution!
2010/11/20 Tom Lane
> Alexander Farber writes:
> > 1) if I'd like to compare just the first 3 numbers of
> > the IP address instead of the 4, how can I do it?
>
> regression=# select network(set_masklen('123.45.67.42'::inet, 24)) =
> regression-# network(set_masklen('123.45.67.56'::inet, 24));
> ?column?
> --
> t
> (1 row)
>
>
>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
>
--
// Dmitriy.
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
AFAIK we don't truncate the log file created by the log_filename GUC on every unclean crash and every clean shutdown. That's not a remotely relevant analogy. A log file is not a database table. If we allow a database table to become corrupted due to being unsynched at the time of shutdown, it's not a matter of "missing a few rows". The table is *unreadable*, and may cause the backend or even the whole server to crash when you try to read it. Anyway, per discussion on hackers, unlogged tables (or "volatile tables" as they're now being called) include two modes in the spec; one which checkpoints (and thus can survive a planned restart) and one which doesn't (and will truncate on every restart, but doesn't cause physical I/O). We may or may not have both modes for 9.1. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.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] Comparing first 3 numbers of a IPv4 address?
Thank you. Or maybe also?
(author_ip & '255.255.255.0'::inet) = (_author_ip & '255.255.255.0'::inet)
On Sat, Nov 20, 2010 at 4:47 PM, Tom Lane wrote:
> Alexander Farber writes:
>> 1) if I'd like to compare just the first 3 numbers of
>> the IP address instead of the 4, how can I do it?
>
> regression=# select network(set_masklen('123.45.67.42'::inet, 24)) =
> regression-# network(set_masklen('123.45.67.56'::inet, 24));
> ?column?
> --
> t
> (1 row)
>|
--
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
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. ;-) -- 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] Postgres forums ... take 2
Stephen Cook wrote: > On 11/16/2010 10:51 AM, Magnus Hagander wrote: > > What I'm more interested in is still a word from the people who would > > actually *use* a forum on how this would be better than sites like > > Nabble and Gmane. > > I'm one of those. I'm subscribed to these mailing lists simply because > it is the only way I know of to get the messages in a timely fashion, > but I would greatly prefer a forum-style interface. > > I had never heard of Nabble or Gmane until now, but I just checked them > out and from my quick look it *looks* like a web interface for people > who prefer mailing lists. > > I like having a category breakdown (at the moment I have my email client > splitting the various lists into folders), and I like having little > icons telling me which ones I already read and which are new (my email > client has that also of course). OK, here is why our Nabble/Gmane web sites are inferior to forums for some people Nabble/Gmane have no per-user state indicating what has been read, what threads you are interested in getting email notification about, etc. -- 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] Comparing first 3 numbers of a IPv4 address?
Alexander Farber writes:
> 1) if I'd like to compare just the first 3 numbers of
> the IP address instead of the 4, how can I do it?
regression=# select network(set_masklen('123.45.67.42'::inet, 24)) =
regression-# network(set_masklen('123.45.67.56'::inet, 24));
?column?
--
t
(1 row)
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] Fwd: [pgsql-www] Forums at postgresql.com.au
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? 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 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. Uh, no. Not when you're accessing the interface with the proper password (one that has permissions to do admin actions on the list). The code in that example does not require confirmation for the subscriptions. It does, I think, send out the "welcome to the xyz list" mail, but that should also be easily scriptable away. >>> >>> Theres no way I'm relying on the fact that every person that signs up to >>> the forums will be informed enough to realise that the forum is >>> more-or-less just a front for the mailing list. If I signed up to a forum >>> and got and email saying "welcome to the mailing list" I would think "Um, I >>> didn't sign up to this" and unsubscribe. Now all my posts will be rejected >>> by the mailing list and my posts will goto thin air without me ever knowing. >> >> Like I said, "that should also be easily scriptable away". Yes, it >> will take more than zero seconds of work to look into how to do it. > > As theres no way or checking the subscription status of an address I would > have to subscribe them every post. And i'm sure they would get really annoyed > when every they unsubscribe from the mailing list and make a post on the > forum they keep getting "welcome to the mailing list" emails. Sure there is. If you looked at the script I sent, you would notice it does just that - checks if a user is already subscribed, and subscribes him/her if not on there only. And you're still stuck on the "keep getting the welcome mail", even though I've told you several times that I'm certain you can make it work without having it send that out. Again, I give up. It can be done, but clearly I can't explain it in a way that you can understand. Hopefully somebody else can understand what I'm saying and explain it further. You are still not understanding the problem. Since I *don't have the users email address*, I can't send it the normal way. I have nowhere to send it. >>> >>> Explained above, your not sending it to the person your sending it back to >>> the mailing list. I know this works because I've been testing it with my >>> own address like a dummy mailing list. >> >> At the risk of sounding like a broken record.. I don't *WANT* to send >> it to the list, in this scenario. I want to send it to the *person*. > > Theres only two possible scenarios; > 1. The person is signed up to the mailing list, in which case they will get > your reply when its passed through the mailing list like they should. Seriously? Please read what I wrote. Or at least try. I am NOT SENDING IT TO THE LIST. I am sending it DIRECTLY TO THE PERSON. Or rather, I'm trying, but it goes to forum@, which is a blackhole that throws it away. That shall, again, be my last attempt. But solving the problem is a *requirement*, so hopefully someone can explain it better. >>> If you want to personally reach John Doe you can use either the PM or email >>> system in the forum - and you know how to reach him by his name. And >>> perhaps a URL at the bottom of the email. If you just want to reply to him >>> then i've explain that above. >> >> But I'm not *on* the forum, I'm using the mailinglist. >> >> The URL at the bottom is an acceptable solution, if you can make it >> work transparently. I just don't understand how you can do that - >> since I haven't signed up, I don't have a password.And you can't >> encode it in the URL, because it goes into public archives... So how >> would that URL *work*? >> > > This is your forum account > http://forums.postgresql.com.au/memberlist.php?mode=viewprofile&u=101 > > Anyone that posts to the mailing list becomes a member of the forum so that > their emails can be used as posts otherwise there would be one giant > MailingList user that has 99%+ of the forum posts which is a terrible idea. I can't use it, because I don't have my pa
Re: [GENERAL] Comparing first 3 numbers of a IPv4 address?
I will be glad to see the best solution, so if you find it please share. :-)
But you want to compare IP addresses by 3 first parts. I don't see any
function or operator at SQL level which allow to do it without converting
to text.
Probably, you can do it by manipulating a binary form (from libpq).
But is it worth it ? :-)
2010/11/20 Alexander Farber
> I think inet is a number internally, there is probably a more effective
> way...
>
> On 11/20/10, Dmitriy Igrishin wrote:
> > You can. host() just extract IP address from a value of inet
> > type as text, string_to_array() converts this text to text[], and
> > it makes it possible to compare with another text[]... I see no
> > problem here. It works just fine.
> > But probably there is another (better) solution...
> >
> > 2010/11/20 Alexander Farber
> >
> >> I'm actually hoping to use inet (or cidr?) instead of strings...
> >>
> >>
> >>
> >> On 11/20/10, Dmitriy Igrishin wrote:
> >> > Hey Alexander,
> >> >
> >> > 2010/11/20 Alexander Farber
> >> >
> >> >> Hello,
> >> >>
> >> >> I'm trying to program a PHP-script, where users
> >> >> can rate the "goodness" of the other players:
> >> >>
> >> >>create table pref_rep (
> >> >>id varchar(32) references pref_users(id) check (id <>
> >> >> author),
> >> >>author varchar(32) references pref_users(id),
> >> >>author_ip inet,
> >> >>good boolean,
> >> >>last_rated timestamp default current_timestamp
> >> >>);
> >> >>
> >> >> To (try to) prevent tampering I'd like to delete
> >> >> entries for the same id coming
> >> >> from the same IP in the course of last hour:
> >> >>
> >> >>create or replace function pref_update_rep(_id varchar,
> >> >>_author varchar, _author_ip inet,
> >> >>_good boolean) returns void as $BODY$
> >> >>begin
> >> >>
> >> >>delete from pref_rep
> >> >>where id = _id and
> >> >>author_ip = _author_ip and
> >> >>age(to_timestamp(last_rated)) < interval '1 hour';
> >> >>
> >> >>update pref_rep set
> >> >>author= _author,
> >> >>author_ip = _author_ip,
> >> >>good = _good,
> >> >>last_rated = current_timestamp
> >> >>where id = _id and author = _author;
> >> >>
> >> >>if not found then
> >> >>insert into pref_rep(id, author, author_ip,
> >> >> good)
> >> >>values (_id, _author, _author_ip, _good);
> >> >>end if;
> >> >>end;
> >> >>$BODY$ language plpgsql;
> >> >>
> >> >> I have 2 questions please:
> >> >>
> >> >> 1) if I'd like to compare just the first 3 numbers of
> >> >> the IP address instead of the 4, how can I do it?
> >> >> (yes, I know about the A,B,C type of IPv4 networks...)
> >> >>
> >> > You may try something like this (this solution can be better):
> >> > SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
> >> > to get first 3 bytes of IP in array (ready to compare with another
> >> > array).
> >> >
> >> >
> >> >> 2) Do I need to add an index to my table
> >> >> or are id and author indexed already?
> >> >>
> >> > Foreign keys columns does not indexed. You should create them
> >> > manually (if you need).
> >> >
> >> >
> >> >>
> >> >> Thank you!
> >> >> Alex
> >> >>
> >> >> --
> >> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> >> To make changes to your subscription:
> >> >> http://www.postgresql.org/mailpref/pgsql-general
> >> >>
> >> >
> >> >
> >> >
> >> > --
> >> > // Dmitriy.
> >> >
> >>
> >> --
> >> Sent from my mobile device
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >>
> >
> >
> >
> > --
> > // Dmitriy.
> >
>
> --
> Sent from my mobile device
>
> --
> 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] where is pg_stat_activity (and others) in the documentation?
On Mon, Nov 15, 2010 at 10:03 AM, Vick Khera wrote: > On Mon, Nov 15, 2010 at 5:15 AM, Willy-Bas Loos > wrote: > > I was looking for what exactly "waiting" means in pg_stat_activity. > You can find out exactly what you're waiting for by correlating this to the pg_locks table. Grab the 'procpid' of your waiting query and run: select * from pg_locks where pid= and granted='f'; Notice, in the pg_locks table, the logic works slightly different... if you're 'waiting' (pg_stat_activity), then you haven't been 'granted' (pg_locks). As far as documentation: http://www.postgresql.org/docs/current/static/monitoring-stats.html --Scott > > waiting for a lock. > > -- > 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] Comparing first 3 numbers of a IPv4 address?
I think inet is a number internally, there is probably a more effective way...
On 11/20/10, Dmitriy Igrishin wrote:
> You can. host() just extract IP address from a value of inet
> type as text, string_to_array() converts this text to text[], and
> it makes it possible to compare with another text[]... I see no
> problem here. It works just fine.
> But probably there is another (better) solution...
>
> 2010/11/20 Alexander Farber
>
>> I'm actually hoping to use inet (or cidr?) instead of strings...
>>
>>
>>
>> On 11/20/10, Dmitriy Igrishin wrote:
>> > Hey Alexander,
>> >
>> > 2010/11/20 Alexander Farber
>> >
>> >> Hello,
>> >>
>> >> I'm trying to program a PHP-script, where users
>> >> can rate the "goodness" of the other players:
>> >>
>> >>create table pref_rep (
>> >>id varchar(32) references pref_users(id) check (id <>
>> >> author),
>> >>author varchar(32) references pref_users(id),
>> >>author_ip inet,
>> >>good boolean,
>> >>last_rated timestamp default current_timestamp
>> >>);
>> >>
>> >> To (try to) prevent tampering I'd like to delete
>> >> entries for the same id coming
>> >> from the same IP in the course of last hour:
>> >>
>> >>create or replace function pref_update_rep(_id varchar,
>> >>_author varchar, _author_ip inet,
>> >>_good boolean) returns void as $BODY$
>> >>begin
>> >>
>> >>delete from pref_rep
>> >>where id = _id and
>> >>author_ip = _author_ip and
>> >>age(to_timestamp(last_rated)) < interval '1 hour';
>> >>
>> >>update pref_rep set
>> >>author= _author,
>> >>author_ip = _author_ip,
>> >>good = _good,
>> >>last_rated = current_timestamp
>> >>where id = _id and author = _author;
>> >>
>> >>if not found then
>> >>insert into pref_rep(id, author, author_ip,
>> >> good)
>> >>values (_id, _author, _author_ip, _good);
>> >>end if;
>> >>end;
>> >>$BODY$ language plpgsql;
>> >>
>> >> I have 2 questions please:
>> >>
>> >> 1) if I'd like to compare just the first 3 numbers of
>> >> the IP address instead of the 4, how can I do it?
>> >> (yes, I know about the A,B,C type of IPv4 networks...)
>> >>
>> > You may try something like this (this solution can be better):
>> > SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
>> > to get first 3 bytes of IP in array (ready to compare with another
>> > array).
>> >
>> >
>> >> 2) Do I need to add an index to my table
>> >> or are id and author indexed already?
>> >>
>> > Foreign keys columns does not indexed. You should create them
>> > manually (if you need).
>> >
>> >
>> >>
>> >> Thank you!
>> >> Alex
>> >>
>> >> --
>> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-general
>> >>
>> >
>> >
>> >
>> > --
>> > // Dmitriy.
>> >
>>
>> --
>> Sent from my mobile device
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> // Dmitriy.
>
--
Sent from my mobile device
--
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] Comparing first 3 numbers of a IPv4 address?
You can. host() just extract IP address from a value of inet
type as text, string_to_array() converts this text to text[], and
it makes it possible to compare with another text[]... I see no
problem here. It works just fine.
But probably there is another (better) solution...
2010/11/20 Alexander Farber
> I'm actually hoping to use inet (or cidr?) instead of strings...
>
>
>
> On 11/20/10, Dmitriy Igrishin wrote:
> > Hey Alexander,
> >
> > 2010/11/20 Alexander Farber
> >
> >> Hello,
> >>
> >> I'm trying to program a PHP-script, where users
> >> can rate the "goodness" of the other players:
> >>
> >>create table pref_rep (
> >>id varchar(32) references pref_users(id) check (id <>
> >> author),
> >>author varchar(32) references pref_users(id),
> >>author_ip inet,
> >>good boolean,
> >>last_rated timestamp default current_timestamp
> >>);
> >>
> >> To (try to) prevent tampering I'd like to delete
> >> entries for the same id coming
> >> from the same IP in the course of last hour:
> >>
> >>create or replace function pref_update_rep(_id varchar,
> >>_author varchar, _author_ip inet,
> >>_good boolean) returns void as $BODY$
> >>begin
> >>
> >>delete from pref_rep
> >>where id = _id and
> >>author_ip = _author_ip and
> >>age(to_timestamp(last_rated)) < interval '1 hour';
> >>
> >>update pref_rep set
> >>author= _author,
> >>author_ip = _author_ip,
> >>good = _good,
> >>last_rated = current_timestamp
> >>where id = _id and author = _author;
> >>
> >>if not found then
> >>insert into pref_rep(id, author, author_ip, good)
> >>values (_id, _author, _author_ip, _good);
> >>end if;
> >>end;
> >>$BODY$ language plpgsql;
> >>
> >> I have 2 questions please:
> >>
> >> 1) if I'd like to compare just the first 3 numbers of
> >> the IP address instead of the 4, how can I do it?
> >> (yes, I know about the A,B,C type of IPv4 networks...)
> >>
> > You may try something like this (this solution can be better):
> > SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
> > to get first 3 bytes of IP in array (ready to compare with another
> > array).
> >
> >
> >> 2) Do I need to add an index to my table
> >> or are id and author indexed already?
> >>
> > Foreign keys columns does not indexed. You should create them
> > manually (if you need).
> >
> >
> >>
> >> Thank you!
> >> Alex
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >>
> >
> >
> >
> > --
> > // Dmitriy.
> >
>
> --
> Sent from my mobile device
>
> --
> 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] Comparing first 3 numbers of a IPv4 address?
I'm actually hoping to use inet (or cidr?) instead of strings...
On 11/20/10, Dmitriy Igrishin wrote:
> Hey Alexander,
>
> 2010/11/20 Alexander Farber
>
>> Hello,
>>
>> I'm trying to program a PHP-script, where users
>> can rate the "goodness" of the other players:
>>
>>create table pref_rep (
>>id varchar(32) references pref_users(id) check (id <>
>> author),
>>author varchar(32) references pref_users(id),
>>author_ip inet,
>>good boolean,
>>last_rated timestamp default current_timestamp
>>);
>>
>> To (try to) prevent tampering I'd like to delete
>> entries for the same id coming
>> from the same IP in the course of last hour:
>>
>>create or replace function pref_update_rep(_id varchar,
>>_author varchar, _author_ip inet,
>>_good boolean) returns void as $BODY$
>>begin
>>
>>delete from pref_rep
>>where id = _id and
>>author_ip = _author_ip and
>>age(to_timestamp(last_rated)) < interval '1 hour';
>>
>>update pref_rep set
>>author= _author,
>>author_ip = _author_ip,
>>good = _good,
>>last_rated = current_timestamp
>>where id = _id and author = _author;
>>
>>if not found then
>>insert into pref_rep(id, author, author_ip, good)
>>values (_id, _author, _author_ip, _good);
>>end if;
>>end;
>>$BODY$ language plpgsql;
>>
>> I have 2 questions please:
>>
>> 1) if I'd like to compare just the first 3 numbers of
>> the IP address instead of the 4, how can I do it?
>> (yes, I know about the A,B,C type of IPv4 networks...)
>>
> You may try something like this (this solution can be better):
> SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
> to get first 3 bytes of IP in array (ready to compare with another
> array).
>
>
>> 2) Do I need to add an index to my table
>> or are id and author indexed already?
>>
> Foreign keys columns does not indexed. You should create them
> manually (if you need).
>
>
>>
>> Thank you!
>> Alex
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> // Dmitriy.
>
--
Sent from my mobile device
--
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] Comparing first 3 numbers of a IPv4 address?
Hey Alexander,
2010/11/20 Alexander Farber
> Hello,
>
> I'm trying to program a PHP-script, where users
> can rate the "goodness" of the other players:
>
>create table pref_rep (
>id varchar(32) references pref_users(id) check (id <>
> author),
>author varchar(32) references pref_users(id),
>author_ip inet,
>good boolean,
>last_rated timestamp default current_timestamp
>);
>
> To (try to) prevent tampering I'd like to delete
> entries for the same id coming
> from the same IP in the course of last hour:
>
>create or replace function pref_update_rep(_id varchar,
>_author varchar, _author_ip inet,
>_good boolean) returns void as $BODY$
>begin
>
>delete from pref_rep
>where id = _id and
>author_ip = _author_ip and
>age(to_timestamp(last_rated)) < interval '1 hour';
>
>update pref_rep set
>author= _author,
>author_ip = _author_ip,
>good = _good,
>last_rated = current_timestamp
>where id = _id and author = _author;
>
>if not found then
>insert into pref_rep(id, author, author_ip, good)
>values (_id, _author, _author_ip, _good);
>end if;
>end;
>$BODY$ language plpgsql;
>
> I have 2 questions please:
>
> 1) if I'd like to compare just the first 3 numbers of
> the IP address instead of the 4, how can I do it?
> (yes, I know about the A,B,C type of IPv4 networks...)
>
You may try something like this (this solution can be better):
SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
to get first 3 bytes of IP in array (ready to compare with another
array).
> 2) Do I need to add an index to my table
> or are id and author indexed already?
>
Foreign keys columns does not indexed. You should create them
manually (if you need).
>
> Thank you!
> Alex
>
> --
> 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
Using the reply to all, thanks. On 21/11/2010, at 12:32 AM, Magnus Hagander wrote: > On Sat, Nov 20, 2010 at 14:22, Elliot Chance wrote: >> >> On 20/11/2010, at 11:52 PM, Magnus Hagander wrote: >> >>> On Sat, Nov 20, 2010 at 12:26, Elliot Chance wrote: On 20/11/2010, at 9:52 PM, Magnus Hagander wrote: > On Sat, Nov 20, 2010 at 02:57, Elliot Chance > wrote: >> >> On 20/11/2010, at 3:58 AM, Magnus Hagander wrote: >> >> Isn't that a secondary use case, though? It would be easy to solve this >> >> by providing a URL to the post in the forum that you can click; assuming >> >> the forum interface gives you the option to reply privately. >> >> That would pretty much make it impossible to use offline. >> >> That would be annoying, but I guess survivable. But how would that >> work for a user that hasn't signed up for the forum? How does it >> verify the sender? >> >> The forum uses the same confirmation as the mailing list where an email >> is >> sent to the address and they have to click on a link to activate their >> account - this very standard practice on forum software. > > Oh, I assumed that - you're missing my point. > > The point is this: > Assume John Doe posts something to the list. I am reading this, and > want to use "alvaros suggestion" for doing a direct response. So I > click the link that was in the email. *I* am not registrered in the > forums. How do I respond to his post in a safe way? You are registered in the forum already (it does this automatically), you simply reply on the mailing list as you have always done. If you feel the sudden urge to only reply via the forum then simply use the recover password to login and reply from there. >>> >>> I can't do that, since all email is sent from the same address. How >>> will the forum software know which person I was trying to respond to? >> >> One very annoying thing about Apple Mail with these lists is that when I hit >> reply if I don't change the To address to the mailing list or manually add >> the Cc then it doesn't even get sent to the mailing list. I wouldn't be >> surprised if a lot of my posts have disappeared like that. > > Use "Reply To All" when you want to send to the list. It's what > everybody else has been doing for ages :-) If you want to read up on > the bike-shedding that goes behind that preference, it is something > that comes up regularly - just search the archives. > > >> 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. > > Using nomail still requires you to confirm your email address (I know because i've tried it.) If there were a magic value you could pass then it would defeat the purpose of having email confirmations and people would just write scripts to cheat it - like I want to do. >>> >>> Uh, no. Not when you're accessing the interface with the proper >>> password (one that has permissions to do admin actions on the list). >>> The code in that example does not require confirmation for the >>> subscriptions. It does, I think, send out the "welcome to the xyz >>> list" mail, but that should also be easily scriptable away. >> >> Theres no way I'm relying on the fact that every person that signs up to the >> forums will be informed enough to realise that the forum is more-or-less >> just a front for the mailing list. If I signed up to a forum and got and >> email saying "welcome to the mailing list" I would think "Um, I didn't sign >> up to this" and unsubscribe. Now all my posts will be rejected by the >> mailing list and my posts will goto thin air without me ever knowing. > > Like I said, "that should also be easily scriptable away". Yes, it > will take more than zero seconds of work to look into how to do it. As theres no way or checking the subscription status of an address I would have to subscribe them every post. And i'm sure they would get really annoyed when every they unsubscribe from the mailing list and make a post on the forum they keep getting "welcome to the mailing list" emails. > > >>> You are still not understanding the problem. Since I *don't have the >>> users email address*, I can't send it the normal way. I have nowhere >>> to send it. >> >> Explain
Re: [GENERAL] [pgsql-www] Forums at postgresql.com.au
On Sat, Nov 20, 2010 at 14:22, Elliot Chance wrote: > > On 20/11/2010, at 11:52 PM, Magnus Hagander wrote: > >> On Sat, Nov 20, 2010 at 12:26, Elliot Chance wrote: >>> >>> On 20/11/2010, at 9:52 PM, Magnus Hagander wrote: >>> On Sat, Nov 20, 2010 at 02:57, Elliot Chance wrote: > > On 20/11/2010, at 3:58 AM, Magnus Hagander wrote: > > Isn't that a secondary use case, though? It would be easy to solve this > > by providing a URL to the post in the forum that you can click; assuming > > the forum interface gives you the option to reply privately. > > That would pretty much make it impossible to use offline. > > That would be annoying, but I guess survivable. But how would that > work for a user that hasn't signed up for the forum? How does it > verify the sender? > > The forum uses the same confirmation as the mailing list where an email is > sent to the address and they have to click on a link to activate their > account - this very standard practice on forum software. Oh, I assumed that - you're missing my point. The point is this: Assume John Doe posts something to the list. I am reading this, and want to use "alvaros suggestion" for doing a direct response. So I click the link that was in the email. *I* am not registrered in the forums. How do I respond to his post in a safe way? >>> >>> You are registered in the forum already (it does this automatically), you >>> simply reply on the mailing list as you have always done. If you feel the >>> sudden urge to only reply via the forum then simply use the recover >>> password to login and reply from there. >> >> I can't do that, since all email is sent from the same address. How >> will the forum software know which person I was trying to respond to? > > One very annoying thing about Apple Mail with these lists is that when I hit > reply if I don't change the To address to the mailing list or manually add > the Cc then it doesn't even get sent to the mailing list. I wouldn't be > surprised if a lot of my posts have disappeared like that. Use "Reply To All" when you want to send to the list. It's what everybody else has been doing for ages :-) If you want to read up on the bike-shedding that goes behind that preference, it is something that comes up regularly - just search the archives. > 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? >>> Using nomail still requires you to confirm your email address (I know >>> because i've tried it.) If there were a magic value you could pass then it >>> would defeat the purpose of having email confirmations and people would >>> just write scripts to cheat it - like I want to do. >> >> Uh, no. Not when you're accessing the interface with the proper >> password (one that has permissions to do admin actions on the list). >> The code in that example does not require confirmation for the >> subscriptions. It does, I think, send out the "welcome to the xyz >> list" mail, but that should also be easily scriptable away. > > Theres no way I'm relying on the fact that every person that signs up to the > forums will be informed enough to realise that the forum is more-or-less just > a front for the mailing list. If I signed up to a forum and got and email > saying "welcome to the mailing list" I would think "Um, I didn't sign up to > this" and unsubscribe. Now all my posts will be rejected by the mailing list > and my posts will goto thin air without me ever knowing. Like I said, "that should also be easily scriptable away". Yes, it will take more than zero seconds of work to look into how to do it. >> You are still not understanding the problem. Since I *don't have the >> users email address*, I can't send it the normal way. I have nowhere >> to send it. > > Explained above, your not sending it to the person your sending it back to > the mailing list. I know this works because I've been testing it with my own > address like a dummy mailing list. At the risk of sounding like a broken record.. I don't *WANT* to send it to the list, in this scenario. I want to send it to the *person*. >>> Forums and mailing lists have the same functionality they just do the same >>> things different ways. If you want to use the mailing list you have to use >>> it like a mailing list, if you want to use the forum then you have to use >>> it like a forum. >>> >>> If John Doe signs up to the forum he is expecting the forum to work like a >>> forum. When his answer is posted to the forum thread he will be notified. >>> If in rare cases someone needs to send him a priv
[GENERAL] Comparing first 3 numbers of a IPv4 address?
Hello, I'm trying to program a PHP-script, where users can rate the "goodness" of the other players: create table pref_rep ( id varchar(32) references pref_users(id) check (id <> author), author varchar(32) references pref_users(id), author_ip inet, good boolean, last_rated timestamp default current_timestamp ); To (try to) prevent tampering I'd like to delete entries for the same id coming from the same IP in the course of last hour: create or replace function pref_update_rep(_id varchar, _author varchar, _author_ip inet, _good boolean) returns void as $BODY$ begin delete from pref_rep where id = _id and author_ip = _author_ip and age(to_timestamp(last_rated)) < interval '1 hour'; update pref_rep set author= _author, author_ip = _author_ip, good = _good, last_rated = current_timestamp where id = _id and author = _author; if not found then insert into pref_rep(id, author, author_ip, good) values (_id, _author, _author_ip, _good); end if; end; $BODY$ language plpgsql; I have 2 questions please: 1) if I'd like to compare just the first 3 numbers of the IP address instead of the 4, how can I do it? (yes, I know about the A,B,C type of IPv4 networks...) 2) Do I need to add an index to my table or are id and author indexed already? Thank you! Alex -- 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] Postgres forums ... take 2
Apple Mail has not been copying the mailing list, so excuse the reposts if you got one. Begin forwarded message: > From: Elliot Chance > Date: 16 November 2010 4:57:27 PM AEDT > To: Craig Ringer > Subject: Re: [GENERAL] Postgres forums ... take 2 > > On 16/11/2010, at 2:01 PM, Craig Ringer wrote: > >> On 15/11/10 17:37, Thom Brown wrote: >> >>> That's actually some good work you've done there! I didn't know phpBB >>> supported bidirectional mailing list support. >> >> Yikes. Neither did I. I've always seen phpBB as the barren wasteland of >> web forums - forums full of half-page animated GIF signatures separating >> single lines of text, some kind of content-free zone of minimum >> information density. Maybe it can be configured to be better than that >> after all. >> >> How does it handle threading? Will forum threads be properly threaded? >> And will replies have the correct In-Reply-To: header so they >> get threaded correctly? > > It uses the message-id and in-reply-to header information, so a change in > email subject will not effect the flow of the thread. However not all emails > stick by those rules, a few emails do not carry the header information in > which case the script strips the subject (to remove "Re:", "[GENERAL]") and > then matches the topic by name as a fall back. > > Email header information can be sketchy at the best of times, but this does a > pretty good job at making sure almost all of the messages are handled > correctly. In some rare cases when the in-reply-to is missing and the subject > has changed it will create a new thread, but a forum moderator can click a > button to merge the threads and all is fixed. > >> >> Have you been in touch with the Pg list admins to make sure they're cool >> with this? > > At this point its a good idea, who is the best person(s) to contact? I want > to make sure anything I do does not in any way reflect badly on the community > or seem like i'm doing anything dishonest. > >> >>> A few points though. I think we'd need to disable smileys, bbcode, any >>> form of rich text formatting, flash or embedded images. >> >> Mostly agreed. Limiting signatures to 4/5 lines would be nice too. > > All forum software limits the size of a signature to stop people abusing it. > As the signature is a separate entity the mail that comes from the forum can > contain or ignore the signature. > >> >> Limited HTML is really useful on web forums, though, as it allows you to >> delineate code from other text. Unless the whole forum is set to >> monospaced text with preserved whitespace, that's necessary to ensure >> that code samples are readable. > > That's one thing that can't be fixed when incoming emails are converted to > forums posts the code blocks appear as normal text. > > There is no use importing huge archives seeing as most of it is either too > old to be relevant, bloat the forum and most people don't bother using a > search anyway before posting. But it may be alright to say import the last > few months of data (that include product release announcements, active bugs > etc.) I have written another script which imports the mbox files just as a > proof of concept: > http://forums.postgresql.com.au/viewforum.php?f=34 > Those 7 threads were imported from a single months mbox file. > >> >> -- >> Craig Ringer >> >> Tech-related writing: http://soapyfrogs.blogspot.com/ >
Fwd: [GENERAL] Postgres forums ... take 2
Apple Mail has not been copying the mailing list, so excuse the reposts if you got one. Begin forwarded message: > From: Elliot Chance > Date: 15 November 2010 9:02:43 PM AEDT > To: Thom Brown > Subject: Re: [GENERAL] Postgres forums ... take 2 > > On 15/11/2010, at 8:37 PM, Thom Brown wrote: > >> On 15 November 2010 08:34, Elliot Chance wrote: >> Hi again, >> >> I've taken in all the feedback about http://forums.postgresql.com.au and the >> general consensus is that nobody wants a separate entity - a few people >> mentioned that if it was interoperable with the mailing list that it would >> be better. So I did. >> >> The concept goes like this; >> 1. Any posts to the general mailing list will be picked up by the forum, the >> email data is converted and posted on the forum, for example; >> http://forums.postgresql.com.au/viewtopic.php?f=30&t=39 >> 2. Any reply to the forum will do the reverse and send the post back to the >> mailing list as a reply. >> >> This means the forum can be fully controlled through the mailing list >> without the need to visit the forums directly. However those people who >> prefer to use a forum interface can, and those messages are relayed back >> through the mailing list to get answered. >> >> Step 1 is complete (might need a little tweaking, i've only tried it with a >> couple of topics.) Step 2 I haven't begun - wanted to get some more feedback. >> >> All the forum topics and posts are back-dated to match the emails, which >> means it would be *theoretically* possible to load in the entire postgres >> mailing list archive but I wouldn't do that on a server that couldn't handle >> that much data. >> >> Disclaimer about user names: >> User names are registered automatically based on the unique email address of >> the person emailing the response. Each user is given a random 8 character >> password. You can use the recover password page to login to your account and >> change your user name to anything you want, the only important thing is that >> your email address matches. >> >> I know this is a sensitive issue with some people, i've made sure no >> information is posted thats not already currently being indexed by google. >> >> The only maintenance I can see is that all new topics are pushed into the >> General > Other category as the script can't differentiate what category it >> should in fact belong to, once the topic is moved it will stay there. This >> shouldn't be a real problem as theres not many new topics being created on >> any given day. >> >> Elliot, >> >> That's actually some good work you've done there! I didn't know phpBB >> supported bidirectional mailing list support. > > It doesn't. I have a subscription address that is piped into a PHP script > that uses the phpBB3 APIs to do all you see. > >> >> I think, however, that having such a forum at a .com.au address isn't >> particularly desirable, as it implies it's regional. If others are happy >> for you to work on this, it might be an idea to speak to the existing web >> team to see if they are able to provide you with pointers and possibly >> resources to get such a thing up and running. It would be nice, for >> example, to have forums.postgresql.org set up. > > I was just amazed that postgresql.com.au was available (in australia you need > a registered company to get a .com.au address so that's why.) > > At the moment its running on mysql (I know, but they don't support postgres) > but it will work with postgres. The forum software, database and scripts I've > written are all portable so theres no reason why it couldn't be moved to > another domain any time in the future. Obviously at the time I couldn't use > forums.postgresql.org. > > Lets see how it goes, if it does turn out to be useful then we'll have a chat > to the developers. > >> >> A few points though. I think we'd need to disable smileys, bbcode, any form >> of rich text formatting, flash or embedded images. In short, plain text >> only, which is the policy on the mailing list. I think it would be more >> useful if each forum directly corresponded to a mailing list too. What I >> mean is that if there was a forum on the site which didn't match to a >> mailing list, only forum users could use it. > > If someone were to send a reply on the forum all the bbcode would be stripped > before emailing it to the mailing list to keep the mailing list "pure." Is > that what you mean? > >> >> 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 po
Fwd: [GENERAL] The first dedicated PostgreSQL forum
Apple Mail has not been copying the mailing list, so excuse the reposts if you got one. Begin forwarded message: > From: Elliot Chance > Date: 19 November 2010 9:18:18 AM AEDT > To: Daniel Verite > Subject: Re: [GENERAL] The first dedicated PostgreSQL forum > > Is nobody reading the other "Postgres forums ... take 2" mailing list thread? > > The forum will go nowhere unless we can decide how messages are sent to it. > Many people objected to the idea of using the persons name and generic mail > address like: > From: Elliot Chance ; John Smith > > > But so far no one has suggested a way email addresses can be registered ... > you can't have it both ways. I've tried to investigate what mailing list > software postgresql.org uses which no luck. So what to do? > > On 19/11/2010, at 5:31 AM, Daniel Verite wrote: > >> John R Pierce wrote: >> >>> gee, we should have a Facebook wall >> >> As if there wasn't one already :) >> >> http://www.facebook.com/#!/group.php?gid=2324323985&v=wall >> >>> and a Twitter feed too >> >> http://twitter.com/postgresql >> >> Best regards, >> -- >> Daniel >> PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] Fwd: [pgsql-www] Forums at postgresql.com.au
Begin forwarded message: > From: Magnus Hagander > Date: 20 November 2010 3:58:41 AM AEDT > To: Alvaro Herrera > Cc: Dave Page , Elliot Chance , > pgsql-www > Subject: Re: [pgsql-www] Forums at postgresql.com.au > > On Fri, Nov 19, 2010 at 16:14, Alvaro Herrera > wrote: >> Excerpts from Dave Page's message of vie nov 19 11:43:34 -0300 2010: >>> On Fri, Nov 19, 2010 at 1:19 PM, Elliot Chance >>> wrote: However if I had signed up to the forum (and not the mailing list) my From would have to be subscribed for the mailing list to accept it like: Elliot Chance John Smith ... etc. OK, so after a brief background I'd like to organise a solution. Without any other feasible option would this generic address system be allowed? >>> >>> I wouldn't be happy with that as it prevents private replies to the >>> author and would make it easy to send what was intended as a private >>> reply to the public forums by mistake. >> >> Isn't that a secondary use case, though? It would be easy to solve this >> by providing a URL to the post in the forum that you can click; assuming >> the forum interface gives you the option to reply privately. > > That would pretty much make it impossible to use offline. > > That would be annoying, but I guess survivable. But how would that > work for a user that hasn't signed up for the forum? How does it > verify the sender? > > > -- > Magnus Hagander > Me: http://www.hagander.net/ > Work: http://www.redpill-linpro.com/
Re: [GENERAL] [pgsql-www] Forums at postgresql.com.au
On 20/11/2010, at 11:52 PM, Magnus Hagander wrote: > On Sat, Nov 20, 2010 at 12:26, Elliot Chance wrote: >> >> On 20/11/2010, at 9:52 PM, Magnus Hagander wrote: >> >>> On Sat, Nov 20, 2010 at 02:57, Elliot Chance wrote: On 20/11/2010, at 3:58 AM, Magnus Hagander wrote: Isn't that a secondary use case, though? It would be easy to solve this by providing a URL to the post in the forum that you can click; assuming the forum interface gives you the option to reply privately. That would pretty much make it impossible to use offline. That would be annoying, but I guess survivable. But how would that work for a user that hasn't signed up for the forum? How does it verify the sender? The forum uses the same confirmation as the mailing list where an email is sent to the address and they have to click on a link to activate their account - this very standard practice on forum software. >>> >>> Oh, I assumed that - you're missing my point. >>> >>> The point is this: >>> Assume John Doe posts something to the list. I am reading this, and >>> want to use "alvaros suggestion" for doing a direct response. So I >>> click the link that was in the email. *I* am not registrered in the >>> forums. How do I respond to his post in a safe way? >> >> You are registered in the forum already (it does this automatically), you >> simply reply on the mailing list as you have always done. If you feel the >> sudden urge to only reply via the forum then simply use the recover password >> to login and reply from there. > > I can't do that, since all email is sent from the same address. How > will the forum software know which person I was trying to respond to? One very annoying thing about Apple Mail with these lists is that when I hit reply if I don't change the To address to the mailing list or manually add the Cc then it doesn't even get sent to the mailing list. I wouldn't be surprised if a lot of my posts have disappeared like that. When you reply to an email you send it to the person your replying to and Cc the mailing list. When I send to to mailing list I direct my actual To address to the mailing list, i'm not sending these replies to any particular person. The parser script doesn't care who the email comes from or is going to because it uses the in-reply-to field to match up the threads. So this means when you hit reply your email program will say something like this: To: Elliot Chance Cc: pgsql-general@postgresql.org 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. > > I did have a look at https://github.com/mhagander/hamn/blob/master/listsync.py and I an do the submitting the part quite easily myself but how does that activate the user without an email being sent to them? >>> >>> Yeah, that's the part that needs to be added to it. But I don't see >>> any reason that shouldn't be fairly simple - you probably just need to >>> include a set nomail command as well. >> >> Using nomail still requires you to confirm your email address (I know >> because i've tried it.) If there were a magic value you could pass then it >> would defeat the purpose of having email confirmations and people would just >> write scripts to cheat it - like I want to do. > > Uh, no. Not when you're accessing the interface with the proper > password (one that has permissions to do admin actions on the list). > The code in that example does not require confirmation for the > subscriptions. It does, I think, send out the "welcome to the xyz > list" mail, but that should also be easily scriptable away. Theres no way I'm relying on the fact that every person that signs up to the forums will be informed enough to realise that the forum is more-or-less just a front for the mailing list. If I signed up to a forum and got and email saying "welcome to the mailing list" I would think "Um, I didn't sign up to this" and unsubscribe. Now all my posts will be rejected by the mailing list and my posts will goto thin air without me ever knowing. > > When you say offline I assume you mean replying to one or more threads while not connected to the internet, then releasing your outbox when you get back to an internet connection? >>> >>> Yes. For example, when on a plane or somewhere where the cell coverage is >>> bad. >> >> Like I explained this is no problem. After all you still get the same >> problems with a pure mailing list. For example if someone posted the >> question "How do I insert records?" and your on a plane typing "Use INSERT >> ." but before your plane lands someone else has alrea
[GENERAL] Fwd: [pgsql-www] Forums at postgresql.com.au
On 20/11/2010, at 4:04 AM, Alvaro Herrera wrote: > Excerpts from Dave Page's message of vie nov 19 12:22:09 -0300 2010: >> On Fri, Nov 19, 2010 at 3:14 PM, Alvaro Herrera >> wrote: > >>> I wonder if the mailing list would alow posting from an address like >>> forums+1...@postgresql.com.au if only for...@postgresql.com.au is >>> subscribed. The number or string after the + would presumably be the >>> user ID in the forum or some unique identifier. (Extra points if the >>> mailing software at that domain forwards email to the user when sent to >>> that address (or maybe a PM in the forum system) -- this would solve >>> Dave's concern.) >> >> That would solve it, yes. I don't think mj2 will allow that though - >> we've been looking for something similar for sysadmin use. > > So let's patch Mj2. If we use a pattern like the persons unique username: Elliot Chance John Smith 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. > > -- > Álvaro Herrera > The PostgreSQL Company - Command Prompt, Inc. > PostgreSQL Replication, Consulting, Custom Development, 24x7 support
[GENERAL] Fwd: [pgsql-www] Forums at postgresql.com.au
On 20/11/2010, at 9:52 PM, Magnus Hagander wrote: > On Sat, Nov 20, 2010 at 02:57, Elliot Chance wrote: >> >> On 20/11/2010, at 3:58 AM, Magnus Hagander wrote: >> >> On Fri, Nov 19, 2010 at 16:14, Alvaro Herrera >> wrote: >> >> Excerpts from Dave Page's message of vie nov 19 11:43:34 -0300 2010: >> >> On Fri, Nov 19, 2010 at 1:19 PM, Elliot Chance >> wrote: >> >> However if I had signed up to the forum (and not the mailing list) my From >> would have to be subscribed for the mailing list to accept it like: >> >> Elliot Chance >> >> John Smith >> >> ... etc. >> >> OK, so after a brief background I'd like to organise a solution. Without any >> other feasible option would this generic address system be allowed? >> >> I wouldn't be happy with that as it prevents private replies to the >> >> author and would make it easy to send what was intended as a private >> >> reply to the public forums by mistake. >> >> Isn't that a secondary use case, though? It would be easy to solve this >> >> by providing a URL to the post in the forum that you can click; assuming >> >> the forum interface gives you the option to reply privately. >> >> That would pretty much make it impossible to use offline. >> >> That would be annoying, but I guess survivable. But how would that >> work for a user that hasn't signed up for the forum? How does it >> verify the sender? >> >> The forum uses the same confirmation as the mailing list where an email is >> sent to the address and they have to click on a link to activate their >> account - this very standard practice on forum software. > > Oh, I assumed that - you're missing my point. > > The point is this: > Assume John Doe posts something to the list. I am reading this, and > want to use "alvaros suggestion" for doing a direct response. So I > click the link that was in the email. *I* am not registrered in the > forums. How do I respond to his post in a safe way? You are registered in the forum already (it does this automatically), you simply reply on the mailing list as you have always done. If you feel the sudden urge to only reply via the forum then simply use the recover password to login and reply from there. > > >> I did have a look >> at https://github.com/mhagander/hamn/blob/master/listsync.py and I an do the >> submitting the part quite easily myself but how does that activate the user >> without an email being sent to them? > > Yeah, that's the part that needs to be added to it. But I don't see > any reason that shouldn't be fairly simple - you probably just need to > include a set nomail command as well. Using nomail still requires you to confirm your email address (I know because i've tried it.) If there were a magic value you could pass then it would defeat the purpose of having email confirmations and people would just write scripts to cheat it - like I want to do. > > >> When you say offline I assume you mean replying to one or more threads while >> not connected to the internet, then releasing your outbox when you get back >> to an internet connection? > > Yes. For example, when on a plane or somewhere where the cell coverage is bad. Like I explained this is no problem. After all you still get the same problems with a pure mailing list. For example if someone posted the question "How do I insert records?" and your on a plane typing "Use INSERT ." but before your plane lands someone else has already responded to the person. Your email will still be sent so that the person gets two answers. Just like the persons email program will rank the answers by timestamp so will posts to the forum. > > >> That's fine because all the posts are back dated >> to sync with the email send time, so your replies will still appear after a >> given post even if there has been more replies since then. > > I think you're again missing that this was a comment to Alvaros > suggestion, which was the "have a link at the bottom of the email > going back to the forums". > > And you're also missing the fact that I'm talking about doing a > private response to the person who posted it, not a general > to-the-list response. Forums and mailing lists have the same functionality they just do the same things different ways. If you want to use the mailing list you have to use it like a mailing list, if you want to use the forum then you have to use it like a forum. If John Doe signs up to the forum he is expecting the forum to work like a forum. When his answer is posted to the forum thread he will be notified. If in rare cases someone needs to send him a private message or email they can still do so through those features provided inside the forum software. > > > -- > 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] Survey on backing up unlogged tables: help us with PostgreSQL development!
Josh Berkus wrote: >> With the current patches, the data survives a restart just fine. > > Per -hackers, that's not guarenteed. "Not guaranteed" is fine. What people are asking for is "often survives". AFAIK we don't truncate the log file created by the log_filename GUC on every unclean crash and every clean shutdown. Should we? :-) Why not? For people who intend to use these tables to log application data, they'd have the exact same reasons for not wanting them truncated when they don't need to be. -- 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] The first dedicated PostgreSQL forum
Hi, > As has been said previously, an unlinked forum (one which has no > interaction with the mailing list) is destined to fail, as others have > in the past. It's creates a fragmented community and poor support on > such a forum would reflect badly on the PostgreSQL community. > > Mailing lists aren't "old school". They've just got a long history, > and I think you'll find most open source projects probably have a > mailing list (or several) associated with them. I watch this thread at a glance, but I think idea of forum isn't bad as long as it can integrate with mailing list. It's just because you can't say to people "from tomorrow all of you will use forums instead of mailing lists". I generally prefer forums, over mailing lists (if ofcourse forums works fast, with short latancy). But as suggestion from my side, a really good example of functionality is NetBeans formus and mailing lists. There, user can join, as well forums and/or mailing list, each of those is synchronized so every post on forum you can get by mail and vice versa, you can, as well respond by mail to post, and your response will be on forum. And as I see "1st Dedicated Forum" has this. One of nice soultions there is every user has some kind alias e-mail, so you responds to eg "rsmog...@netbeans.org", not to private addresses, and this protects you form receiving spam. Kind regards, Radek -- 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] newbie question - delete before insert
On 11/20/2010 02:43 PM, Thomas Kellerer wrote: > Grant Mckenzie wrote on 20.11.2010 07:00: >> How do people implement insert or upate ( otherwise known as upsert ) >> behaviour in postgres i.e. insert a row if it's key does not exist in >> the database else update the existing row? >> > > You can simply send the UPDATE, if nothing was updated, it's safe to > send the INSERT > > Regards > Thomas > > > something on the line of http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html *Example 38-2. Exceptions with UPDATE/INSERT* -- With Regards Ashish Karalkar
Re: [GENERAL] newbie question - delete before insert
Grant Mckenzie wrote on 20.11.2010 07:00: How do people implement insert or upate ( otherwise known as upsert ) behaviour in postgres i.e. insert a row if it's key does not exist in the database else update the existing row? You can simply send the UPDATE, if nothing was updated, it's safe to send the INSERT Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
