Re: [GENERAL] match an IP address
My post at the bottom. On 9/23/08, Craig Ringer <[EMAIL PROTECTED]> wrote: > > Dodgy forum software. Lots of it uses an IP address as a fake username for > unregistered users, rather than doing the sensible thing and tracking both > IP address and (if defined) username. > > How I'd want to do this if I was designing the setup from scratch would > probably be: > > -- > -- Track user identies > -- > CREATE TABLE user ( > user_id SERIAL PRIMARY KEY, > user_name VARCHAR NOT NULL, > user_retired BOOLEAN > -- and whatever else you want to keep track of about them > ); > > -- This index enforces unique user names across active users, > -- permitting re-use of usernames for since deleted users without > -- losing information about the previous user of that name's activity > -- or losing the inability to differentiate between the old and new > -- owners of that name. > -- > -- I'd revoke DELETE rights on `user' or use a rule that rewrote DELETE > -- to an UPDATE user SET user_retired = 't'. > -- > > CREATE UNIQUE INDEX user_active_name > ON user(user_name) > WHERE (NOT user_retired) > > -- Now, say your problem described in your post is with user activity > -- logging: > > CREATE TABLE access_log ( > -- blah blah > user_id INTEGER REFERENCES user(user_id), > access_ip cidr NOT NULL > ); > > CREATE INDEX access_log_ip ON access_log(access_ip); > > CREATE INDEX access_user > ON access_log(user_id) > WHERE (user_id NOT NULL); > > > > In other words: always store the IP address, and if the user involved was a > registered user store a reference to their user ID as well. Store a > reference to a globally unique user identity number rather than the user > name, and permit reuse of user names without losing information about > distinct username owners. > > If you wanted you could use string user IDs and do away with the synthetic > "user_id" key I've used above, but I suspect you'd regret it down the track. > > If you wanted to look up activity that might be identifed by IP address or > by username, a query like this would do the trick and would simulate the > behaviour your forum software is used to, including the ability of a user to > create a username that's an IP address to throw the whole thing into chaos: > > SELECT * FROM access_user > WHERE COALESCE(user_id, to_char(access_ip)) = matchstring; > > -- > Craig Ringer > No. You have no idea what the design is for. Not forum crap. What happens when you need to store in a table the activity log? ACTIVITY_ID USER_STAMP (currently user_id or ip for registered and unregistered resp.) The idea of storing IP for users is already being done. So what? Everytime they "do" something, you do not store their IP. Why would you? Just store their user id. For unregistered ones however, we store the IP because there is nothing else. There is no user ID for them. What's your logic for getting a user ID for unregistered guys -- invent one automagically? Finally, this SQL: WHERE COALESCE(user_id, to_char(access_ip)) = matchstring; Ever tried this crap on a table of 10 million records on a live website, where this query is happening at 3000 times per second? No such function schtick will match the raw speed of a simpler indexed query. Or did you mean my index should contain the COALESCE already? Tino, I wasn't talking about sessions. This is not about session IDs. A session ID is useless the next time a certain IP address "does something" on the site. You want a commonality for non-registered users across many different sessions. (Apart from the fact that session IDs are usually long hashes which take up space in the table and in the index) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.3.4 rpms for Opensuse10.3 64bit
Hello, a few months ago we started using Postgres on Opensuse10.3-64bit. We installed Postgres 8.3.1 with the (at that time) latest available rpm's. But now Postgres' current version is 8.3.4 and I'm wondering why there are no new rpm's for Opensuse ?!?! The suse build service still offers me 8.3.1 for download :-( (also the directory of PeterEisentraut, where we got the 8.3.1 version, hasn't been updated) Does somebody know how to get more recent rpm's for 64Bit Opensuse ? thanks in advance...GERD... -- 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] match an IP address
Tino Wildenhain wrote: Phoenix Kiula wrote: my IP addresses are stored in a TEXT type field. that field can actually contain usernames like 'joao' or 'scott' and it can contain IP addresses I think this is common DB design on many websites that have registered user IDs. Is it? Name one! Sounds like crappy design to me. My humble suggestion would be to make another column in the table called "user_registered" or something. Make this an int(1). If the user is registered, value is 1, otherwise 0. Maybe "registration" (aka authentication) is mixed up with simple session handling? To update your existing data onetime, run an SQL update query looking for IP pattern with 3 dots (which is likely IP address and not user id) or by the INET conditions in previous suggestions. It could also just be a fdn (hostname) - still I'm a bit puzzled how a username and an IP-address can get into the same field. Dodgy forum software. Lots of it uses an IP address as a fake username for unregistered users, rather than doing the sensible thing and tracking both IP address and (if defined) username. How I'd want to do this if I was designing the setup from scratch would probably be: -- -- Track user identies -- CREATE TABLE user ( user_id SERIAL PRIMARY KEY, user_name VARCHAR NOT NULL, user_retired BOOLEAN -- and whatever else you want to keep track of about them ); -- This index enforces unique user names across active users, -- permitting re-use of usernames for since deleted users without -- losing information about the previous user of that name's activity -- or losing the inability to differentiate between the old and new -- owners of that name. -- -- I'd revoke DELETE rights on `user' or use a rule that rewrote DELETE -- to an UPDATE user SET user_retired = 't'. -- CREATE UNIQUE INDEX user_active_name ON user(user_name) WHERE (NOT user_retired) -- Now, say your problem described in your post is with user activity -- logging: CREATE TABLE access_log ( -- blah blah user_id INTEGER REFERENCES user(user_id), access_ip cidr NOT NULL ); CREATE INDEX access_log_ip ON access_log(access_ip); CREATE INDEX access_user ON access_log(user_id) WHERE (user_id NOT NULL); In other words: always store the IP address, and if the user involved was a registered user store a reference to their user ID as well. Store a reference to a globally unique user identity number rather than the user name, and permit reuse of user names without losing information about distinct username owners. If you wanted you could use string user IDs and do away with the synthetic "user_id" key I've used above, but I suspect you'd regret it down the track. If you wanted to look up activity that might be identifed by IP address or by username, a query like this would do the trick and would simulate the behaviour your forum software is used to, including the ability of a user to create a username that's an IP address to throw the whole thing into chaos: SELECT * FROM access_user WHERE COALESCE(user_id, to_char(access_ip)) = matchstring; -- Craig Ringer -- 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] Triggers not working
I would have called the Entity table the parent table and not the child table as the Account table inherits from Entity. Therefore it appears that the trigger only works on the table where the actual row was added/belongs to. It would be great if triggers on the parent table would work for any row that appears there, even rows added via a child table. It would mean that any new table that inherits the parent table, in my case Entity, would automatically have the global trigger I want enforced. Nice enhancement :) Regards, Dale Harris. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, 23 September 2008 13:43 To: Dale Harris Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Triggers not working "Dale Harris" <[EMAIL PROTECTED]> writes: > The trigger trAccountUpdate got called, but why didn't the trigger > trEntityUpdate get called? Triggers only apply to the exact table they're declared on, not to child tables. It does seem like there might be some use-case for applying a trigger to child tables too, but that's not how it works now. 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] Triggers not working
"Dale Harris" <[EMAIL PROTECTED]> writes: > The trigger trAccountUpdate got called, but why didn't the trigger > trEntityUpdate get called? Triggers only apply to the exact table they're declared on, not to child tables. It does seem like there might be some use-case for applying a trigger to child tables too, but that's not how it works now. 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] Help with query to return indexes (including functional ones!) on a given table
"Philip Hallstrom" <[EMAIL PROTECTED]> writes:
> I'm trying to add functional index support to Rails' Active Record and
> am getting stuck when it comes to a method Rails has to print out the
> indexes associated with a given table.
> The SQL being run is below:
> SELECT distinct i.relname, d.indisunique, a.attname
> FROM pg_class t, pg_class i, pg_index d, pg_attribute a
> WHERE i.relkind = 'i'
> AND d.indexrelid = i.oid
> AND d.indisprimary = 'f'
> AND t.oid = d.indrelid
> AND t.relname = 'employers'
> AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN
> ('public') )
> AND a.attrelid = t.oid
> AND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum
> OR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum
> OR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum
> OR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum
> OR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )
> ORDER BY i.relname;
Well, the problem with this is that it only considers simple index keys,
ie, not indexed expressions. The multi-argument version of
pg_get_indexdef() would probably help. Something like
SELECT i.relname, d.indisunique, pg_get_indexdef(i.oid, a.attnum, false)
FROM pg_class t, pg_class i, pg_index d, pg_attribute a
WHERE i.relkind = 'i'
AND d.indexrelid = i.oid
AND d.indisprimary = 'f'
AND t.oid = d.indrelid
AND t.relname = 'employers'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN
('public') )
AND a.attrelid = i.oid
ORDER BY i.relname;
Note I've flipped the meaning of the "a" table to be attributes of the
index not of the table; this is a handy way to get all the attnum
values (index column numbers) we need to pass to pg_get_indexdef().
BTW, why are you suppressing primary keys?
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] how to return the first record from the sorted records which may have duplicated value.
yes, thanks u very much, it's work:) regards, Yi On Fri, 2008-09-19 at 11:06 +0200, Andreas Kretschmer wrote: > Yi Zhao <[EMAIL PROTECTED]> schrieb: > > > hi all: > > I have a table with columns(>2) named "query", "pop", "dfk". > > what I want is: > > when I do some select, if the column "query" in result records have > > duplicate value, I only want the record which have the maximum value of > > the "pop". > > > > for example, the content of table: > > query pop dfk > > --- > > abc30 1 --max > > foo 20 lk --max > > def 16 kj --max > > foo 15 fk --discard > > abc 10 2 --discard > > bar 8are --max > > > > the result should be: > > query pop dfk > > --- > > abc30 1 > > foo 20 lk > > def 16 kj > > bar 8are > > test=*# select * from d; > query | pop | dfk > ---+-+- > abc | 30 | 1 > foo | 20 | lk > def | 16 | kj > foo | 15 | fk > abc | 10 | 2 > bar | 8 | are > (6 Zeilen) > > Zeit: 0,213 ms > test=*# select distinct on (query) * from d order by query, pop desc; > query | pop | dfk > ---+-+- > abc | 30 | 1 > bar | 8 | are > def | 16 | kj > foo | 20 | lk > (4 Zeilen) > > Hint: distinct on isn't standard-sql, it's an PG-extension. > > > Andreas > -- > Really, I'm not out to destroy Microsoft. That will just be a completely > unintentional side effect. (Linus Torvalds) > "If I was god, I would recompile penguin with --enable-fly." (unknown) > Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° > -- 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] pg_start_backup() takes too long
Ivan Zolotukhin wrote:
> Hello,
>
> What is the reason for
>
> select pg_start_backup('label');
>
> taking 10 minutes on not so loaded system even right after manual checkpoint?
No idea; something is seriously wrong if that is happening. Do the
database server logs or kernel logs show anything unusual?
--
Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
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] how to return the first record from the sorted records which may have duplicated value.
yes, > select distinct max(pop),query from test > group by query test=# select distinct max(pop),query from bar group by query; max | query -+--- 8 | bar 16 | def 20 | foo 30 | abc but, I want to get the records contains more than two columns(max, query, "dfk"), so, if I use group by, max, distinct keywords, I should use this sql and get the result as below: test=# select distinct max(pop),query, dfk from bar group by query, dfk; max | query | dfk -+---+- 8 | bar | are 10 | abc | 2 15 | foo | fk 16 | def | kj 20 | foo | lk 30 | abc | 1 btw: *distinct on* is useful:) thanks, On Fri, 2008-09-19 at 09:00 -0700, Lennin Caro wrote: > > > --- On Fri, 9/19/08, Yi Zhao <[EMAIL PROTECTED]> wrote: > > > From: Yi Zhao <[EMAIL PROTECTED]> > > Subject: [GENERAL] how to return the first record from the sorted > records which may have duplicated value. > > To: "pgsql-general" > > Date: Friday, September 19, 2008, 8:51 AM > > hi all: > > I have a table with columns(>2) named "query", > > "pop", "dfk". > > what I want is: > > when I do some select, if the column "query" in > > result records have > > duplicate value, I only want the record which have the > > maximum value of > > the "pop". > > > > for example, the content of table: > > query pop dfk > > --- > > abc30 1 --max > > foo 20 lk --max > > def 16 kj --max > > foo 15 fk --discard > > abc 10 2 --discard > > bar 8are --max > > > > the result should be: > > query pop dfk > > --- > > abc30 1 > > foo 20 lk > > def 16 kj > > bar 8are > > > > now, I do it like this(plpgsql) > > > > declare hq := ''::hstore; > > begin > > for rc in execute 'select * from test order by pop > > desc' loop > > if not defined(hq, rc.query) then > > hq := hq || (rc.query => '1')::hstore; > > return next rc; > > end if; > > end loop; > > --- > > language sql/plpgsql will be ok. > > > > ps: I try to use "group by" or "max" > > function, because of the > > multi-columns(more than 2), I failed. > > > > thanks, > > any answer is appreciated. > > > > regards, > > > > > this query work for me > > > select distinct max(pop),query from test > group by query > > > please reply your results > > thanks... > > > > > -- 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] Triggers not working
Hi Tom, The trigger trAccountUpdate got called, but why didn't the trigger trEntityUpdate get called? Regards, Dale Harris. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, 22 September 2008 22:22 To: Dale Harris Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Triggers not working "Dale Harris" <[EMAIL PROTECTED]> writes: > I'm running PostgreSQL 8.3.3 and I'm having trouble with triggers not always > working. I have the following tables and functions as documented below. My > problem is that if I perform an update on the Entity table and modify the > Code field, why doesn't the trigger for the Entity table execute? (Row was > initially added via the Account table.) Worksforme ... regression=# insert into "Account" values(1,'code','name'); INSERT 0 1 regression=# select * from "Entity"; ID | Code | Name | Modified | ModifiedBy +--+--+---+ 1 | code | name | 2008-09-22 08:19:51.70-04 | postgres (1 row) regression=# update "Entity" set "Code" = 'foo' where "ID" = 1; NOTICE: trAccountUpdate being called for UPDATE of Account. UPDATE 1 regression=# select * from "Entity"; ID | Code | Name | Modified | ModifiedBy +--+--+---+ 1 | foo | name | 2008-09-22 08:20:18.10-04 | postgres (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] match an IP address
> > I think this is common DB design on many websites that have registered > > user IDs. > > > > Is it? Name one! Sounds like crappy design to me. > It might sound crappy design to you, but for websites that allow users to do something while they are registered OR unregistered, will choose to have this for query speed. (Registered user goes in as "testuser" while an unregistered one goes as his IP address--some websites also use cookies but they're less reliable.) We can make this very relationally sound and whatnot, but having one field to go to, whether you are registered or not, makes it much simpler. Most websites have to allow for dots in their user ID these days as people prefer to have their email address as user ID. Which means that the dot checking of an IP address may not work to distinguish IP addresses (unregistered user) from registered user IDs. In this scenario, for query speed, again, if there is a column that tells us whether this user is registered or not it helps a great deal. The INET match condition is not good enough for speed for most modern websites with any sizeable traffic. I even wrote a function that converts IP to INET integer and vice versa, but no great boost in query speed that could compare to an indexed query on user_id and user_reg. Welcome your thoughts on how you would do it. -- 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] match an IP address
Phoenix Kiula wrote: my IP addresses are stored in a TEXT type field. that field can actually contain usernames like 'joao' or 'scott' and it can contain IP addresses I think this is common DB design on many websites that have registered user IDs. Is it? Name one! Sounds like crappy design to me. My humble suggestion would be to make another column in the table called "user_registered" or something. Make this an int(1). If the user is registered, value is 1, otherwise 0. Maybe "registration" (aka authentication) is mixed up with simple session handling? To update your existing data onetime, run an SQL update query looking for IP pattern with 3 dots (which is likely IP address and not user id) or by the INET conditions in previous suggestions. It could also just be a fdn (hostname) - still I'm a bit puzzled how a username and an IP-address can get into the same field. Next, include the columns "user_id" and "user_registered" in the same index. This won't increase the size of the index too much, as the user_registered field is a small INT number. But from now on your SQL queries: ...WHERE user_id = 'testuser' AND user_registered = 1 will return much faster. I have found this to the most convenient and fastest solution in PGSQL instead of haivng INET in the WHERE clauses. Can you give example on where the inet entry is going to be used? Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] match an IP address
> > my IP addresses are stored in a TEXT type field. that field can actually > > contain usernames like 'joao' or 'scott' and it can contain IP > > addresses I think this is common DB design on many websites that have registered user IDs. My humble suggestion would be to make another column in the table called "user_registered" or something. Make this an int(1). If the user is registered, value is 1, otherwise 0. To update your existing data onetime, run an SQL update query looking for IP pattern with 3 dots (which is likely IP address and not user id) or by the INET conditions in previous suggestions. Next, include the columns "user_id" and "user_registered" in the same index. This won't increase the size of the index too much, as the user_registered field is a small INT number. But from now on your SQL queries: ...WHERE user_id = 'testuser' AND user_registered = 1 will return much faster. I have found this to the most convenient and fastest solution in PGSQL instead of haivng INET in the WHERE clauses. -- 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] match an IP address
Hi, Joao Ferreira gmail wrote: well... my IP addresses are stored in a TEXT type field. that field can actually contain usernames like 'joao' or 'scott' and it can contain IP addresses Any reason not to change this in the first place? For a quick fix you could use regex to find the records likely containing an ip-address notation. Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] match an IP address
On Mon, Sep 22, 2008 at 11:16 AM, Joao Ferreira gmail <[EMAIL PROTECTED]> wrote: > well... > > my IP addresses are stored in a TEXT type field. that field can actually > contain usernames like 'joao' or 'scott' and it can contain IP > addresses Then cast them to inet and use the method I showed above: postgres=# create table b as select a::text from inettest ; SELECT postgres=# select * from b; a 192.168.0.1/32 192.168.1.1/32 10.0.0.1/32 (3 rows) postgres=# select a from b where '192.168.0.1/0' >> a::inet; a 192.168.0.1/32 192.168.1.1/32 10.0.0.1/32 (3 rows) postgres=# select a from b where '192.168.0.1/24' >> a::inet; a 192.168.0.1/32 -- 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] match an IP address
well...
my IP addresses are stored in a TEXT type field. that field can actually
contain usernames like 'joao' or 'scott' and it can contain IP
addresses
:(
joao
On Mon, 2008-09-22 at 11:13 -0600, Scott Marlowe wrote:
> On Mon, Sep 22, 2008 at 10:59 AM, Joao Ferreira gmail
> <[EMAIL PROTECTED]> wrote:
> > hello all,
> >
> > I'm unable to build a LIKE or SIMILAR TO expression for matching and ip
> > address
> >
> > 192.168.90.3
> > 10.3.2.1
>
> As already mentioned inet / cidr types should work. Example:
>
> postgres=# create table inettest (a inet);
> CREATE TABLE
> postgres=# insert into inettest values
> ('192.168.0.1'),('192.168.1.1'),('10.0.0.1');
> INSERT 0 3
> postgres=# select a from inettest where '192.168.0.1/16' >> a;
> a
> -
> 192.168.0.1
> 192.168.1.1
> (2 rows)
> postgres=# select a from inettest where '192.168.0.1/24' >> a;
> a
> -
> 192.168.0.1
> (1 row)
> postgres=# select a from inettest where '192.168.0.1/0' >> a;
> a
> -
> 192.168.0.1
> 192.168.1.1
> 10.0.0.1
> (3 rows)
>
--
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] match an IP address
On Mon, Sep 22, 2008 at 10:59 AM, Joao Ferreira gmail
<[EMAIL PROTECTED]> wrote:
> hello all,
>
> I'm unable to build a LIKE or SIMILAR TO expression for matching and ip
> address
>
> 192.168.90.3
> 10.3.2.1
As already mentioned inet / cidr types should work. Example:
postgres=# create table inettest (a inet);
CREATE TABLE
postgres=# insert into inettest values
('192.168.0.1'),('192.168.1.1'),('10.0.0.1');
INSERT 0 3
postgres=# select a from inettest where '192.168.0.1/16' >> a;
a
-
192.168.0.1
192.168.1.1
(2 rows)
postgres=# select a from inettest where '192.168.0.1/24' >> a;
a
-
192.168.0.1
(1 row)
postgres=# select a from inettest where '192.168.0.1/0' >> a;
a
-
192.168.0.1
192.168.1.1
10.0.0.1
(3 rows)
--
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] match an IP address
On 22/09/2008 17:59, Joao Ferreira gmail wrote: > I'm unable to build a LIKE or SIMILAR TO expression for matching and ip > address There are built-in types in PG for handling IP addresses - are they any use to you? If not, there's a useful site here which may get you started: http://regexlib.com/default.aspx Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] match an IP address
On Mon, Sep 22, 2008 at 05:59:25PM +0100, Joao Ferreira gmail wrote: > I'm unable to build a LIKE or SIMILAR TO expression for matching and ip > address > 192.168.90.3 > 10.3.2.1 > any help please... any reason not to use standard inet datatype? which does the validation. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] match an IP address
hello all, I'm unable to build a LIKE or SIMILAR TO expression for matching and ip address 192.168.90.3 10.3.2.1 any help please... thanks joao -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help with query to return indexes (including functional ones!) on a given table
Hi all -
I'm trying to add functional index support to Rails' Active Record and
am getting stuck when it comes to a method Rails has to print out the
indexes associated with a given table.
The SQL being run is below:
SELECT distinct i.relname, d.indisunique, a.attname
FROM pg_class t, pg_class i, pg_index d, pg_attribute a
WHERE i.relkind = 'i'
AND d.indexrelid = i.oid
AND d.indisprimary = 'f'
AND t.oid = d.indrelid
AND t.relname = 'employers'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN
('public') )
AND a.attrelid = t.oid
AND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum
OR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum
OR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum
OR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum
OR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )
ORDER BY i.relname;
This returns the following:
relname| indisunique | attname
--+-+-
foo_idx | f | name
foo_idx | f | url
index_employers_on_name | f | name
But that doesn't show a functional index I created on the employers
table. This does:
careers_development=# select indexname, indexdef from pg_indexes where
tablename = 'employers';
indexname| indexdef
-+---
employers_pkey| CREATE UNIQUE INDEX
employers_pkey ON employers USING btree (id)
index_employers_on_name | CREATE INDEX index_employers_on_name ON
employers USING btree (name)
index_employers_on_url | CREATE INDEX index_employers_on_url ON
employers USING btree (lower((url)::text))
foo_idx | CREATE INDEX foo_idx ON
employers USING btree (name, url)
I don't know enough about PG's internals to quite know what I need to
query on, but what I'd like is to have a query that returns the first
result set with the following addition:
relname| indisunique | attname
--+-+-
index_employers_on_urlf lower(url)
Is there anyway to do that beyond parsing the CREATE INDEX string?
Some way to modify that first query to include the functional index
and somehow get the function part of it into that last column?
Appreciate any pointers any of you might have on this.
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] How to remove duplicate subqueries
I have query SELECT ... ( SELECT ... FROM (q1) p1 WHERE ... UNION ALL SELECT ... FROM (q1) p2 WHERE ... ) p3 GROUP BY f1,f2,f3 This query contains q1 query twice. q1 takes long time to execute. PostgreSQL probably will execute it two times. How to force PostgreSQL 8.1 to execute q1 only once ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Thesis resource help
Hello, i'm starting to write my thesis for the Degree of Master of Science in Information Systems, it's topic "Principals and methods of hashing in relational database technology with practical verification of collisions occurrence", i would like to focus on PgSQL. I would like to ask someone kind if could refer to more-in-depth resources about: - PgSQL history - the most important areas of using hashing in the PgSQL, i should focus on - how to build windows binaries - anything else on-topic? Thank you for your kind help. -- 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] Triggers not working
"Dale Harris" <[EMAIL PROTECTED]> writes: > I'm running PostgreSQL 8.3.3 and I'm having trouble with triggers not always > working. I have the following tables and functions as documented below. My > problem is that if I perform an update on the Entity table and modify the > Code field, why doesn't the trigger for the Entity table execute? (Row was > initially added via the Account table.) Worksforme ... regression=# insert into "Account" values(1,'code','name'); INSERT 0 1 regression=# select * from "Entity"; ID | Code | Name | Modified | ModifiedBy +--+--+---+ 1 | code | name | 2008-09-22 08:19:51.70-04 | postgres (1 row) regression=# update "Entity" set "Code" = 'foo' where "ID" = 1; NOTICE: trAccountUpdate being called for UPDATE of Account. UPDATE 1 regression=# select * from "Entity"; ID | Code | Name | Modified | ModifiedBy +--+--+---+ 1 | foo | name | 2008-09-22 08:20:18.10-04 | postgres (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] R-tree, order by, limit
Anton Belyaev wrote: Mark, thanks for the suggestion. I examined PostGIS some time ago. It is too complex for my simple task and it gives no advantages for me: Well okay but bear in mind the PostGIS is the de-facto standard for most open source GIS tools. Programs like QGIS et al can visualise the content of PostGIS tables just by pointing it towards the relevant database - the in-built PostgreSQL geometry types aren't supported by anything as far as I know. And don't forget coordinate re-projection - PostGIS also allows you to re-project between latitude/longitude and local map spatial reference systems on the fly. For spatial indexing it uses the same GiST-based R-tree. Not quite. The PostGIS indexes have been improved to include selectivity functions to allow the planner to determine when it should use the spatial index. AFAIK the in-built PostgreSQL types use fixed values, so the choice of index usage will be incredibly naive and often wrong on larger datasets mixing spatial and non-spatial columns as part of the search query. And PostGIS does not offer that "population" or "priority" queries I need. Maybe. But you may find the wiki at http://postgis.refractions.net/support/wiki/ is a good starting point for code examples. ATB, Mark. -- Mark Cave-Ayland Sirius Corporation - The Open Source Experts http://www.siriusit.co.uk T: +44 870 608 0063 -- 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] PDF Documentation for 8.3?
On Fri, Sep 19, 2008 at 08:56:50PM +0200, Michelle Konzack wrote: > Note: The american "Letter" format sucks, because I am printing > two A4 pages on ONE A4 side and with the "Letter" format > I get very huge borders... I find the psnup command (part of psutils) useful here, I tend to use: pdftops file.pdf - | psnup -2 -b-20 -m20 > file.ps To print two pages per side and expand them so there's less wasted space; the 20 is the size of the border. I had a good look for tools that would run through the document to automatically find the real paper size and scale it appropriately, but couldn't find anything maybe a year ago. If anyone knows of anything like this I'd be glad to know! Sam -- 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] R-tree, order by, limit
On Sun, 21 Sep 2008, "Anton Belyaev" <[EMAIL PROTECTED]> writes: > And the questions about population remain the same: > How to avoid examination of all the towns in the rectangle knowing > that we need only 10 biggest? > Does population worth including into a (3D) point (In order to create > a 3D R-tree)? Does Postgres perform ODRER/LIMIT efficiently in this > case? Can we see the EXPLAIN ANALYZE for both situations -- with and without geographic data types/functions? Regards. -- 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] R-tree, order by, limit
2008/9/22 Mark Cave-Ayland <[EMAIL PROTECTED]>: >>> I am implementing a map application. There are towns with altitude, >>> longitude and population. >>> One of the tasks is to be able to query N biggest (by population) >>> towns within a rectangle. > Have you considered using PostGIS? (http://postgis.refractions.net). It > implements the OGC SFS for geometries and is compatible with a large number > of open source viewers/tools such as Mapserver, Geoserver, QGIS, OGR etc... Mark, thanks for the suggestion. I examined PostGIS some time ago. It is too complex for my simple task and it gives no advantages for me: For spatial indexing it uses the same GiST-based R-tree. And PostGIS does not offer that "population" or "priority" queries I need. Anton. -- 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] R-tree, order by, limit
2008/9/21 Martijn van Oosterhout <[EMAIL PROTECTED]>: > On Sun, Sep 21, 2008 at 06:17:39PM +0400, Anton Belyaev wrote: >> Geometry types and functions use R-tree indexes anyways. >> >> I can rephrase the query using geometry language of Postgres: >> SELECT * FROM towns WHERE towns.coordinates <@ box(alt1, long1, alt2, >> long2) ORDER BY population LIMIT 10; >> >> And the questions about population remain the same: >> How to avoid examination of all the towns in the rectangle knowing >> that we need only 10 biggest? > > I don't know if it solves your problem, but you should be able to do a > multi-column GiST index with both the position data and the population > data in it. However, I'm unsure if postgresql will correctly use the > index to solve the order by... Martijn, thanks for you reply. Implementing a 3D R-tree index in Postgres is only possible via implementation of GiST interface. At least, this is the only approach I consider, because implementing a brand new index access method requires much more than just classic R-tree implementation. So, yes, question remains the same, but a bit updated: How efficiently Postgres handles ORDER BY + LIMIT when using GiST? (Particularly, when an R-tree is implemented via GiST). Anton. -- 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] Getting cozy with weekly PITR
pg_start_backup will flush old transactions, thus you have full DB backup. Unless you want them archived, no need to keep them Regards, Bohdan On Mon, Sep 22, 2008 at 09:41:47AM +0200, Joey K. wrote: > During week 2, after the base backup, can we remove week 1's base and WAL > files? -- 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] R-tree, order by, limit
Anton Belyaev wrote: I am implementing a map application. There are towns with altitude, longitude and population. One of the tasks is to be able to query N biggest (by population) towns within a rectangle. Hi Anton, Have you considered using PostGIS? (http://postgis.refractions.net). It implements the OGC SFS for geometries and is compatible with a large number of open source viewers/tools such as Mapserver, Geoserver, QGIS, OGR etc... ATB, Mark. -- Mark Cave-Ayland Sirius Corporation - The Open Source Experts http://www.siriusit.co.uk T: +44 870 608 0063 -- 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] Largest PostgreSQL 8.x DB someone is running?
On Mon, Sep 22, 2008 at 7:14 AM, Keaton Adams <[EMAIL PROTECTED]> wrote: > What is the the largest PostgreSQL 8.x database that is running in a > production environment that you are aware of? We top out at roughly 400 GB > but have a need for a new project to go much, much larger (in the several TB > range). I am attempting to get a feel for how large one should take a > single PostgreSQL database, given all of the operational concerns such as > overall performance with a thousand+ concurrent users, times/space > requirements for backups and restores, how to upgrade to newer upcoming > versions of the software, etc. especially since there are no parallel > operations/features in the product. > plProxy can be used in several ways to use more than one server for serving one database. Our largest OLTP table is over 1 TB in size and we use more than 10 quite cheap servers to keep it online. We use plProxy for horizontal splitting data over servers, vertical splitting functionality into separate servers and clusters of servers and last but not least good old remote calls between servers. http://kaiv.wordpress.com/2007/07/27/postgresql-cluster-partitioning-with-plproxy-part-i/ regards, Asko > > Any information you can provide would be very helpful. > > Thanks, > > Keaton > > > >
Re: [GENERAL] Synchronize two similar tables: recursive triggers
If anyone is interested, here is my solution to my problem, which I hope will be obsolete when the issues with the FDO PostGIS provider are fixed. I have also successfully tested this setup out with foreign key constraints in the primary table only --- the secondary tables just use primary key constraints. I ended up using a third table to store information for update synchronization operations (rather than query pg_stat_activity, since I couldn't see the use in it): CREATE TABLE prim.sync ( source text NOT NULL, CONSTRAINT sync_pkey PRIMARY KEY (source) ) WITH (OIDS=FALSE); BEGIN IF TG_OP = 'INSERT' THEN IF (TG_TABLE_SCHEMA = 'prim') AND (SELECT count(id) = 0 FROM second.mytable WHERE id = NEW.id) THEN INSERT INTO second.mytable(id, fname, num) VALUES(NEW.id, NEW.fname, NEW.num); ELSIF (TG_TABLE_SCHEMA = 'second') AND (SELECT count(id) = 0 FROM prim.mytable WHERE id = NEW.id) THEN INSERT INTO prim.mytable(id, fname, num) VALUES(NEW.id, NEW.fname, NEW.num); END IF; ELSIF TG_OP = 'UPDATE' THEN IF (SELECT count(*) = 0 FROM prim.sync WHERE source=TG_TABLE_NAME) THEN INSERT INTO prim.sync VALUES (TG_TABLE_NAME);--First trigger fire IF TG_TABLE_SCHEMA = 'prim' THEN UPDATE second.mytable SET fname = NEW.fname, num = NEW.num WHERE id = OLD.id; ELSIF TG_TABLE_SCHEMA = 'second' THEN UPDATE prim.mytable SET fname = NEW.fname, num = NEW.num WHERE id = OLD.id; END IF; ELSE--This is the second and last trigger fire DELETE FROM prim.sync WHERE source = TG_TABLE_NAME; END IF; ELSIF TG_OP = 'DELETE' THEN IF TG_TABLE_SCHEMA = 'prim' THEN DELETE FROM second.mytable WHERE id = OLD.id; ELSIF TG_TABLE_SCHEMA = 'second' THEN DELETE FROM prim.mytable WHERE id = OLD.id; END IF; ELSE RAISE EXCEPTION 'TG_OP %', TG_OP; END IF; RETURN NEW; END; CREATE TRIGGER prim_sync AFTER INSERT OR UPDATE OR DELETE ON prim.mytable FOR EACH ROW EXECUTE PROCEDURE prim.sync_mytable_fn(); CREATE TRIGGER second_sync BEFORE INSERT OR UPDATE OR DELETE ON "second".mytable FOR EACH ROW EXECUTE PROCEDURE prim.sync_mytable_fn(); Michael Toews wrote: > The INSERT and DELETE TG_OPs are straightforward (the simplest solution for > these is that the existence of the primary key can be checked in the other > table), however the UPDATE handler is really confusing. > > Is it possible for a trigger function to know where an UPDATE originated > (user vs trigger)? I'm not sure how a trigger could know the first to be > fired, or how many times it has passed between. Any other ideas? Thanks again. > > -Mike > > Pavel Stehule wrote: > >> Hello >> >> ad colum that will contains info about source of value >> >> like >> >> create table a(a integer, from_trigger bool); >> create table b(a integer, from_trigger bool); >> >> create or replace function synchronize_handler_a() >> returns trigger as $$ >> begin >> if not new.from_trigger then >> new.from trigger := true; >> insert into b values(new.*); >> end if; >> return new; >> end; >> $$ language plpgsql; >> >> this is protection under resursive triggers >> >> regards >> Pavel Stehule >> >> >> >> >> 2008/9/18 Michael Toews <[EMAIL PROTECTED]>: >> >> >>> Hi all, >>> >>> I need to have two tables that are mostly synchronized in my database, such >>> that an edit to a row in one is made to the other, and vice versa. >>> Normally, this is done using views with rules, however my situation does >>> not allow editable views (http://trac.osgeo.org/fdo/ticket/346). So, I need >>> to have two database tables. >>> >>> The other thing is that the two tables are not identical, as I need to omit >>> columns with "advanced" data types in one of the tables (another bug: >>> http://trac.osgeo.org/fdo/ticket/394). The two tables also need to be >>> isolated in different schemata. >>> >>> Here are some example tables: >>> >>> CREATE SCHEMA prim; >>> CREATE SCHEMA second; >>> >>> CREATE TABLE prim.mytable >>> ( >>> id integer, >>> fname character varying, >>> num real, >>> timestmp timestamp with time zone, -- not in second.mytable >>> CONSTRAINT mytable_pkey PRIMARY KEY (id) >>> ) WITH (OIDS=FALSE); >>> >>> CREATE TABLE second.mytable >>> ( >>> id integer, >>> fname character varying, >>> num real, >>> CONSTRAINT mytable_pkey PRIMARY KEY (id) >>> ) WITH (OIDS=FALSE); >>> >>> >>> To synchronized the two tables, I plan to use a trigger function to handle >>> INSERT, UPDATE and DELETE events, using TG_OP and TG_TABLE_SCHEMA. (If >>> there are better solutions that don't use triggers, stop me here and fill >>> me in). >>> >>> What I'm having difficulty designing is how to deal with recursive >>> triggers, since I require two-way communication. For example: >>> >>> 1. change on prim.mytable fires trigger to sync change on second.mytable >>> 2. change from (1) on second.mytable fires trigger to sync change on >>> prim.mytable >>> 3. c
[GENERAL] Getting cozy with weekly PITR
Hello, Thanks for advice/suggestions on PITR so far. We have finally decided to do weekly PITR base backups. Just have one nagging question. == week 1==- * pg_start_backup() * tar -cvzf basebackup_week1.tar.gz pgdata/ * pg_stop_backup() cp WAL1 week1/wal/ .. cp WAL2 week1/wal/ ==*== Week 1, we perform base backup and WAL will be backed up by PG. ==week 2== * pg_start_backup() * tar -cvzf basebackup_week2.tar.gz pgdata * pg_stop_backup() cp WAL1 week2/wal/ ... cp WAL2 week2/wal ... rm -f basebackup_week1.tar.gz rm -rf week1/wal ==*== During week 2, after the base backup, can we remove week 1's base and WAL files? I assume this is okay since week 2's PITR backup is as good as new but just wanted to be doubly sure about it before we get rid of week 1's backup. Steve
Re: [GENERAL] [HACKERS] macport for libpqxx
On Sat, Sep 20, 2008 at 5:25 PM, Darren Weber
<[EMAIL PROTECTED]> wrote:
> Hi Dave,
>
> Thanks for getting back to me. Please find attached a draft Portfile
> for libpqxx-2.6.9 (the stable version). It's easy to read the
> Portfile to see what is going on. I think it should work fine, but I
> would appreciate any advice about any configure options that should be
> enabled.
There's nothing much to configure from what I can see.
> I've got a problem within macports (not specific to pg or libpqxx).
> MacPorts will not locate the pg_config.sh file during the macport
> build. I can't just modify the $PATH env because the macport build
> ignores it. There is an internal variable called $binpath in
> macports, but it's read-only. I can't figure out how to get the
> macport configure process to find the right pg_config. Any help
> appreciated.
Can you do something like:
configure { system "cd ${workpath}/${distname}-${version} &&
${configure.env} && PATH=/foo/bar:$PATH ./configure ${configure.args}"
}
Alternatively, the libpqxx docs say you can set ${PG_CONFIG} to the
path to pg_config, so perhaps you can set that in configure.env (that
sounds like the cleanest option).
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
