Re: [GENERAL] match an IP address
Phoenix Kiula wrote: My post at the bottom. ... 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.) And here it gets wrong. Obviously you would store the session id or if you have a lot of relations, use a sequence generated key for session_id (compare with my design in the other post, in this case session_id would be serial and you'd have a field session_key text with the index for the cookies in the sessions table instead) The idea of storing IP for users is already being done. So what? Abandon this idea I'd say. Its based on the wrong asumption IP addresses map to users in 1:1 relation. 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 You have 10 million people active the same time in your site? 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. Well actually this is. You are just naming it differently. 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) Yes but only active ones. btw, given IP is in every request, where is your username coming from? Apart from basic auth, there is no way of having a userid tied to the request directly, so how are you doing this? Tino smime.p7s Description: S/MIME Cryptographic Signature
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...
use this regular expression:
'^[0-9]{1,3}(.[0-9]{1,3}){3}$'
warning: do not use "like" or "similar to".
proper way to use it:
select * from table where field ~ '^[0-9]{1,3}(.[0-9]{1,3}){3}$';
this regexp is not 100% fault proof - it will happily return rows like:
'300.999.998.7'
but for most of the cases it should be enough. if you need it to match
only ips, and filter out things like '300.999.999.999' - let me know.
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
Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
Gerd König wrote: 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 answer is quite simply that no one is volunteering to do the work. -- 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] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
2008/9/23 Peter Eisentraut <[EMAIL PROTECTED]>: > Gerd König wrote: >> >> 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 answer is quite simply that no one is volunteering to do the work. Oh, I thought you were looking after that build. If it's not being maintained, we'll need to remove it from the download pages unless someone else can volunteer? -- 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
Re: [GENERAL] match an IP address
Phoenix Kiula wrote: 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? Please forgive my attempt to help you based on a woefully insufficient description of your problem and situation. I will not make any attempt to do so again. -- 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] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
On Tue, 2008-09-23 at 09:06 +0100, Dave Page wrote: > 2008/9/23 Peter Eisentraut <[EMAIL PROTECTED]>: > > Gerd König wrote: > >> > >> 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 answer is quite simply that no one is volunteering to do the work. > > Oh, I thought you were looking after that build. If it's not being > maintained, we'll need to remove it from the download pages unless > someone else can volunteer? I'll look at doing that. We need the SUSE builds also. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
On Tue, Sep 23, 2008 at 9:49 AM, Simon Riggs <[EMAIL PROTECTED]> wrote: >> Oh, I thought you were looking after that build. If it's not being >> maintained, we'll need to remove it from the download pages unless >> someone else can volunteer? > > I'll look at doing that. We need the SUSE builds also. Great, thanks Simon. If you can commit to ongoing maintenance then we'll get you on the packagers list so you can get advance notice of releases and having input on the scheduling details. -- 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
Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
On Tue, 2008-09-23 at 10:05 +0100, Dave Page wrote: > On Tue, Sep 23, 2008 at 9:49 AM, Simon Riggs <[EMAIL PROTECTED]> wrote: > > >> Oh, I thought you were looking after that build. If it's not being > >> maintained, we'll need to remove it from the download pages unless > >> someone else can volunteer? > > > > I'll look at doing that. We need the SUSE builds also. > > Great, thanks Simon. If you can commit to ongoing maintenance then > we'll get you on the packagers list so you can get advance notice of > releases and having input on the scheduling details. Well, strangely, committing to ongoing maintenance will be easier than doing the first one. ;-) -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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
thank you depesz
it seems a pretty good fix for my problem. Actually yestreday I came up
with something similar but your's is better.
cheers
joao
On Tue, 2008-09-23 at 09:26 +0200, hubert depesz lubaczewski wrote:
> 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...
>
> use this regular expression:
>
> '^[0-9]{1,3}(.[0-9]{1,3}){3}$'
>
> warning: do not use "like" or "similar to".
> proper way to use it:
>
> select * from table where field ~ '^[0-9]{1,3}(.[0-9]{1,3}){3}$';
>
> this regexp is not 100% fault proof - it will happily return rows like:
> '300.999.998.7'
>
> but for most of the cases it should be enough. if you need it to match
> only ips, and filter out things like '300.999.999.999' - let me know.
>
> Best regards,
>
> depesz
>
--
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
> Please forgive my attempt to help you based on a woefully insufficient > description of your problem and situation. I will not make any attempt to do > so again. Actually it was not my problem, this is a thread started by some one else. I use Gmail so I see the entire thread as a "conversation" and the context is maintained. You should try it. Anyway, sorry that you feel bad. To others: thanks for your suggestions, but this issue is not one of session IDs, nor is it solved by storing IP addresses separately (which does not assume 1:1 correlation between user and IP). We'll let that be. Let's just say that in *many* online situations it is vital for querying speed to have the same column that stores users -- both registered and unregistered. A query in SQL that matches against an IP address regexp to identify the unregistered ones may work for some with smaller databases, which is great, and if it doesn't (the "~" match is simply not practical for large busy websites), then consider a small separate column that stores the registration status as a flag. 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] match an IP address
Phoenix Kiula wrote: > 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? Hmm. My previous response may have been overly grumpy. The point I was *trying* to make is that shoving a username/id and an IP address into a single field is probably not ideal. At least in my experience you pay for this sort of optimisation (if it even works out as an optimisation in the first place) down the track. I have the misfortunate to have to administrate a system full of such multi-use fields, and have developed a real loathing for the approach. If you don't want to store IPs for registered users, I'd use: user_id INTEGER, ip cidr, CONSTRAINT must_have_userstamp CHECK ( user_id IS NOT NULL OR ip IS NOT NULL) ... and yes, I'd use a functional index to look it up, or even a trigger-maintained cache of the text representation if I had to. Then again, I guess I'm lucky enough to work in environments where data integrity and correctness is a priority and the resources available are a good fit to the tasks the database needs to do. -- Craig Ringe -- 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
> If you don't want to store IPs for registered users, I'd use: > > user_id INTEGER, > ip cidr, > CONSTRAINT must_have_userstamp > CHECK ( user_id IS NOT NULL OR ip IS NOT NULL) > > ... and yes, I'd use a functional index to look it up, or even a > trigger-maintained cache of the text representation if I had to. Then Ok, this is an idea. And I do agree that multifunction fields are a potential pain in the distant future. My questions: 1. What extra tax will this constraint levy on an INSERT or UPDATE on this table? There are about 100,000 inserts a day, and over three times as many UPDATES. The concurrency is pretty high -- I mean sometimes 1,000 users at the same time but no more than that. If the additional cost of insertion/updating is not too heavy, I suppose this could be a nice approach. 2. Why not have an INET field...why a CIDR? What's the benefit? It stores those pesky ".../8" type additional data which one has to mask with functions. Would INET work just as well? 3. Storage wise does this add significantly? How much space does an INET field take as opposed to, say, a VARCHAR field? 4. Most importantly, how would you structure the index for this? I would much rather have a fast "=" in my sql's WHERE clause. No "OR" etc. Any thoughts? 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] match an IP address
Hi, Phoenix Kiula wrote: Please forgive my attempt to help you based on a woefully insufficient description of your problem and situation. I will not make any attempt to do so again. To others: thanks for your suggestions, but this issue is not one of session IDs, nor is it solved by storing IP addresses separately (which does not assume 1:1 correlation between user and IP). We'll let that be. Let's just say that in *many* online situations it is vital for querying speed to have the same column that stores users -- both registered and unregistered. A query in SQL that matches against an IP if not registered, where is the user coming from? The IP is clearly not an identifier for a user. You (and the OP) should disregard that idea. address regexp to identify the unregistered ones may work for some with smaller databases, which is great, and if it doesn't (the "~" match is simply not practical for large busy websites), then consider a small separate column that stores the registration status as a flag. The user id itself would serve as that flag. If non NULL -> user known, otherwise unknown. Sounds easy, no? No regex at all! :) Thanks. Thx ;) Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] match an IP address
Phoenix Kiula wrote: If you don't want to store IPs for registered users, I'd use: user_id INTEGER, ip cidr, CONSTRAINT must_have_userstamp CHECK ( user_id IS NOT NULL OR ip IS NOT NULL) ... and yes, I'd use a functional index to look it up, or even a trigger-maintained cache of the text representation if I had to. Then Ok, this is an idea. And I do agree that multifunction fields are a potential pain in the distant future. My questions: 1. What extra tax will this constraint levy on an INSERT or UPDATE on this table? There are about 100,000 inserts a day, and over three times as many UPDATES. The concurrency is pretty high -- I mean sometimes 1,000 users at the same time but no more than that. If the additional cost of insertion/updating is not too heavy, I suppose this could be a nice approach. 2. Why not have an INET field...why a CIDR? What's the benefit? It stores those pesky ".../8" type additional data which one has to mask with functions. Would INET work just as well? 3. Storage wise does this add significantly? How much space does an INET field take as opposed to, say, a VARCHAR field? 4. Most importantly, how would you structure the index for this? I would much rather have a fast "=" in my sql's WHERE clause. No "OR" etc. Any thoughts? Thanks Use the best of two worlds - consider memcached and use the db only when you create/update an entry so that you can restore it if memcached (perhaps as a consequence of a server reboot) gets restarted. http://www.slideshare.net/vishnu/livejournals-backend-a-history-of-scaling best regards, Marcus -- 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: > 1. What extra tax will this constraint levy on an INSERT or UPDATE on > this table? There are about 100,000 inserts a day, and over three > times as many UPDATES. The concurrency is pretty high -- I mean > sometimes 1,000 users at the same time but no more than that. If the > additional cost of insertion/updating is not too heavy, I suppose this > could be a nice approach. The best answer there is to do some testing. I wouldn't expect much of a cost, but would recommend testing it to be sure. > 2. Why not have an INET field...why a CIDR? What's the benefit? It > stores those pesky ".../8" type additional data which one has to mask > with functions. Would INET work just as well? Yes, it would. I was just getting my types muddled. > 3. Storage wise does this add significantly? How much space does an > INET field take as opposed to, say, a VARCHAR field? AFAIK nulls are not stored, they're just flagged in the null bitmap. As such, there should be no or almost no storage cost. > 4. Most importantly, how would you structure the index for this? I > would much rather have a fast "=" in my sql's WHERE clause. No "OR" > etc. Any thoughts? I'd try a functional index first. If that didn't do the job, I'd use a trigger-maintained column _purely_ as an optimisation (ie I could drop it and lose no data) that stored text representations of the data. Honestly, though, I expect the functional index would be more than good enough and probably wouldn't have much of an INSERT/UPDATE cost. Again, of course, I'd test before setting anything in stone. -- Craig Ringer -- 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] match an IP address
Craig Ringer wrote: Phoenix Kiula wrote: 1. What extra tax will this constraint levy on an INSERT or UPDATE on this table? There are about 100,000 inserts a day, and over three times as many UPDATES. The concurrency is pretty high -- I mean sometimes 1,000 users at the same time but no more than that. If the additional cost of insertion/updating is not too heavy, I suppose this could be a nice approach. ... I'd try a functional index first. If that didn't do the job, I'd use a trigger-maintained column _purely_ as an optimisation (ie I could drop it and lose no data) that stored text representations of the data. Honestly, though, I expect the functional index would be more than good enough and probably wouldn't have much of an INSERT/UPDATE cost. Again, of course, I'd test before setting anything in stone. And do not forget you know beforehand if you are going to lookup a user or an IP. So I fail to see what problem needs to be solved here :( Maybe we can get an enlightenment on where every data comes from and whats going to be its usage? Maybe we can then work out a better solution at all? Peephole optimizing is great but sometimes the effect is better if you just open the door :-) Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] match an IP address
On Sep 23, 2008, at 12:26 AM, hubert depesz lubaczewski wrote:
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...
use this regular expression:
'^[0-9]{1,3}(.[0-9]{1,3}){3}$'
warning: do not use "like" or "similar to".
proper way to use it:
select * from table where field ~ '^[0-9]{1,3}(.[0-9]{1,3}){3}$';
this regexp is not 100% fault proof - it will happily return rows
like:
'300.999.998.7'
It'll also return rows like 3L33T0o7 - which normally I'd not be too
bothered about, but on a forum that's a fairly typical username. :)
Gotta escape that period:
^[0-9]{1,3}(?:\.[0-9]{1,3}){3}$
Cheers,
Steve
--
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
<...snip...> > > I'd try a functional index first. If that didn't do the job, I'd use a > trigger-maintained column _purely_ as an optimisation (ie I could drop > it and lose no data) that stored text representations of the data. > Honestly, though, I expect the functional index would be more than good > enough and probably wouldn't have much of an INSERT/UPDATE cost. <.../snip...> I made a test table and tried a functional index (coalescing the two columns). Works! Now let me try that on a mirror on my full real table with millions of rows and report back. Sounds like a cleaner design so I may be on to something! Hope this helps the original poster with some ideas too. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error in ALTER DATABASE command
In Postgresql 8.2.9 on Windows, you cannot rename a database if the name contains mixed case. To replicate: 1) Open the pgadmin tool. 2) Create a database named "MixedCase" (using the UI, not using a query window or using PSQL) 3) Open a query window, or use PSQL to issue the following command ALTER DATABASE MixedCase RENAME TO anything_else; PostgreSQL will respond with: ERROR: database "mixedcase" does not exist SQL state: 3D000 This does not happen if you create the database using a manual query in pgadmin, or if you use psql. Both of those tools will create the database as "mixedcase" instead of "MixedCase" I am using: "PostgreSQL 8.2.9 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" I guess for now, I have to dump and reload my database. :( Postgresql seems to force many things to lower case. Is it a bug that the admin tool lets you create a database with mixed case names? Or is it a bug that you cannot rename them thereafter? -- 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] Error in ALTER DATABASE command
On Tue, Sep 23, 2008 at 11:49 AM, William Garrison <[EMAIL PROTECTED]> wrote: > In Postgresql 8.2.9 on Windows, you cannot rename a database if the name > contains mixed case. > 3) Open a query window, or use PSQL to issue the following command > ALTER DATABASE MixedCase RENAME TO anything_else; > PostgreSQL will respond with: > ERROR: database "mixedcase" does not exist > SQL state: 3D000 You need to quote the identifier using double-quotes to avoid case-folding (this is a general rule): ALTER DATABASE "MixedCase" RENAME TO anything_else; > Postgresql seems to force many things to lower case. Yes, it's actually a variation on the SQL standard, which specifies forcing to upper case (as Oracle does). > Is it a bug that the > admin tool lets you create a database with mixed case names? The admin tool is a separate project and works by its own rules. > Or is it a bug > that you cannot rename them thereafter? The FAQ has a good section on identifier case-folding and quoting, I think. Probably worth a read. -Doug -- 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] Error in ALTER DATABASE command
William Garrison wrote: In Postgresql 8.2.9 on Windows, you cannot rename a database if the name contains mixed case. To replicate: 1) Open the pgadmin tool. 2) Create a database named "MixedCase" (using the UI, not using a query window or using PSQL) 3) Open a query window, or use PSQL to issue the following command ALTER DATABASE MixedCase RENAME TO anything_else; PostgreSQL will respond with: ERROR: database "mixedcase" does not exist SQL state: 3D000 Did you try: ALTER DATABASE "MixedCase"? Note the use of double-quotes around the name. I don't have any Windows machines to try this with but it appears you just need to add the double-quotes to prevent PostgreSQL from folding to lower-case. Cheers, Steve -- 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] Error in ALTER DATABASE command
On 23/09/2008 16:49, William Garrison wrote: > In Postgresql 8.2.9 on Windows, you cannot rename a database > if the name contains mixed case. Yes you can, in 8.3 anyway: postgres=# create database "TeSt"; CREATE DATABASE postgres=# \l List of databases Name | Owner | Encoding --+--+-- [snip] teSt | postgres | UTF8 [snip] postgres=# alter database "TeSt" rename to "tEsT"; ALTER DATABASE postgres=# \l List of databases Name | Owner | Encoding --+--+-- [snip] tEsT | postgres | UTF8 [snip] > 3) Open a query window, or use PSQL to issue the following command > ALTER DATABASE MixedCase RENAME TO anything_else; PostgreSQL will > respond with: ERROR: database "mixedcase" does not exist SQL state: > 3D000 Have you tried quoting the database name, thus? - ALTER DATABASE "MixedCase" RENAME TO anything_else; This ought to do it. > This does not happen if you create the database using a manual query > in pgadmin, or if you use psql. Both of those tools will create the > database as "mixedcase" instead of "MixedCase" [snip] > Postgresql seems to force many things to lower case. Is it a bug > that the admin tool lets you create a database with mixed case names? > Or is it a bug that you cannot rename them thereafter? No, it's PostgreSQL's (well-documented) behaviour - as you noted, it folds names to lower-case unless you specifically quote them. I'd hazard a guess that PgAdmin is quoting the database name behind the scenes, hence you can create mixed-case names. 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
[GENERAL] pg_dump | pg_sql: insert commands and foreign key constraints
I have several .SQL files created from pg_dump, and I find that when I feed them into psql that I get tons of foreign key errors because the INSERT statements in the dump are not in the correct order. After reading the docs, mailing lists, and googling, I see posts saying this problem was fixed back in the 7.x days. I'm using postgres 8.2.9. This database doesn't do any "unusual" foreign key constraints like check constraints, functions in the constraints, or circular foreign keys. From the looks of the SQL dump, it is just simply in the wrong order (it might even be alphabetical... the first table starts with c. But I haven't looked at the entire 26GB dump to see if that is the case). Since I did a data only dump, I think my only option is to create the schema, manually disable all the constraints, then restore, then re-enable the constraints. I'm looking for 2 things: 1) other workarounds 2) someone else who can confirm that this bug is either fixed, or not fixed. If it is supposedly fixed, then I guess I need to make a smaller version of my database to demonstrate the problem. -- 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_dump | pg_sql: insert commands and foreign key constraints
On Tuesday 23 September 2008, William Garrison <[EMAIL PROTECTED]> wrote: > 1) other workarounds > 2) someone else who can confirm that this bug is either fixed, or not > fixed. If it is supposedly fixed, then I guess I need to make a smaller > version of my database to demonstrate the problem. AFAIK, the dumps created by pg_dump create all the constraints after the table data is all loaded - there are no foreign keys in place when the data is restored, so conflicts are not possible. You might need to elaborate on how you're restoring this database. -- Alan -- 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] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
Hi, On Tue, 2008-09-23 at 09:49 +0100, Simon Riggs wrote: > I'll look at doing that. We need the SUSE builds also. I actually built 8.3.4 on SLES 10.2 on..err..Friday, while building Fedora/RH RPMs. 8.3.1 spec of SLES is broken IMHO, and it requires special attention from someone who is experienced in packaging (me :-) ) and who is familiar with pg code.. I fixed spec file. The only remaining issue is libpgport.a. If someone who is familiar to PG code can comment on how this file is built, I can push that spec file to my repo, so that Simon or others can continue maintaining it. That file is built on Fedora/RHEL, but I'm not sure why SLES skips it. I need that file for Slony-I on SLES,since AFAICS Slony version is 1.2.6 on SLES :-( I don't have access to that SLES server. I'll ask my friend to post the spec to me, and I'll let you know about the status. Regards, -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
On Tue, 2008-09-23 at 21:05 +0300, Devrim GÜNDÜZ wrote: > Hi, > > On Tue, 2008-09-23 at 09:49 +0100, Simon Riggs wrote: > > I'll look at doing that. We need the SUSE builds also. > > I actually built 8.3.4 on SLES 10.2 on..err..Friday, while building > Fedora/RH RPMs. 8.3.1 spec of SLES is broken IMHO, and it requires > special attention from someone who is experienced in packaging (me :-) ) > and who is familiar with pg code.. > > I fixed spec file. The only remaining issue is libpgport.a. If someone > who is familiar to PG code can comment on how this file is built, I can > push that spec file to my repo, so that Simon or others can continue > maintaining it. That file is built on Fedora/RHEL, but I'm not sure why > SLES skips it. I need that file for Slony-I on SLES,since AFAICS Slony > version is 1.2.6 on SLES :-( > > I don't have access to that SLES server. I'll ask my friend to post the > spec to me, and I'll let you know about the status. Having looked at where we are now, it's going to take more than a little work to get everything in order. Peter had a good go at it, but that was a few releases ago. I've been wanting to make SUSE releases better for a few years now, so am happy to take this on long term. It's not a snap decision since I build on OpenSUSE daily, but from source, not RPMs. As you say, you're in a better position to sort out a stopgap in the short term and I can then take on the task after feature freeze when I'll have more time. In any case, we'll need to make them as consistent as possible between different Linuxes so I'll be relying on your packaging experience. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
Dave Page wrote: 2008/9/23 Peter Eisentraut <[EMAIL PROTECTED]>: Gerd König wrote: 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 answer is quite simply that no one is volunteering to do the work. Oh, I thought you were looking after that build. If it's not being maintained, we'll need to remove it from the download pages unless someone else can volunteer? I am looking after it, but I cannot guarantee real-time releases with the resources available to me. Certainly, one or two more people joining the looking after would be welcome. I did some work recently to put all the pieces in place, so at this time we basically only need to replace the tarballs and release once in a while. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
Added to TODO under features not wanted: Incomplete itemObfuscated function source code (not wanted) Obfuscating function source code has minimal protective benefits because anyone with super-user access can find a way to view the code. To prevent non-super-users from viewing function source code, remove SELECT permission on pg_proc. --- Merlin Moncure wrote: > On Tue, Sep 16, 2008 at 9:15 AM, Glyn Astill <[EMAIL PROTECTED]> wrote: > > > > As much as I'm impressed with the "we do it properly or not at all" > > attitude, it'd be nice if there was an option to stop the casual user from > > viewing code. > > > > I'll admit to obfusicating bits and pieces using C, even though the > > function and everything it acts on are tied down with permissions. I > > understand in reality it provides no real extra security but somehow users > > being able to easily view something they don't have access to execute > > beyond it's name just feels wrong. > > This is one of those threads that reappears like magic every six > months or so. The last round of discussion went longer than normal > including a couple of routes to implementation. > > One big reason why nothing hasn't been done is that there is a decent > 'low tech' obfuscation tactic already: remove select access from > pg_proc to the user accounts in question and 'public'. This will > essentially disable casual browsing of procedure code from user > accounts. > > Any real solution should focus on: > *) key management (any serious discussion with encryption starts here) > *) other things you can do with function source besides encryption > > for example, take a look at one idea I had (not at all vetted, but a start): > http://archives.postgresql.org/pgsql-performance/2007-12/msg00337.php > > merlin > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- 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] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
Devrim GÜNDÜZ wrote: On Tue, 2008-09-23 at 09:49 +0100, Simon Riggs wrote: I'll look at doing that. We need the SUSE builds also. I actually built 8.3.4 on SLES 10.2 on..err..Friday, while building Fedora/RH RPMs. 8.3.1 spec of SLES is broken IMHO, and it requires special attention from someone who is experienced in packaging (me :-) ) and who is familiar with pg code.. Yeah, as you can see here https://build.opensuse.org/project/show?project=server:database:postgresql SLES builds have been broken for a while. I have not analyzed that yet. Bugs and patches welcome. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Debian packages for Postgres 8.2
Hi, I'm running several productive servers on Debian etch (stable) with Postgres 8.2 which has been in lenny (testing) and made available for etch through the backports project [1]. Unfortunately, they discontinued maintaining 8.2 and switched to 8.3 in testing and thus also for the backports. As I don't currently want to switch to 8.3 due to the involved downtime and upgrading troubles involved. So I've compiled up to date Debian packages for Postgres 8.2.10. You can get them (maybe just temporarily) from here: http://www.bluegap.ch/debian, I'm providing packages as etch-backports for amd64 and i386. Upgrading from earlier 8.2 backports should work just fine. I'm trying to convince the backports people to re-add Postgres 8.2. As soon as that happens my own repository will probably disappear again. Please drop me a note if you are interested in 8.2 for etch. (Postgres 8.3 should become available via the backports within a few days, I guess). Regards Markus Wanner [1]: backports of newer software for stable Debian versions: http://www.backports.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] Slony vs Longiste
I need to set up master vs slave replication. My use case is quite simple. I need to back up a small but fairly complex(30 MB data, 175 tables) DB remotely over T1 and be able to switch to that if the main server fails. The switch can even be a script run manually. Can someone either comment in as much detail as possible or point me to a comparison of Slony vs Longiste. Or some other option I have not heard of? From what I read Longiste is easy to set up while I got a quote for Slony setup for 5-10k. -- 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] Error in ALTER DATABASE command
I found out about the quoting thing about 30 seconds after I made the post. :) Thanks everyone who replied. Douglas McNaught wrote: On Tue, Sep 23, 2008 at 11:49 AM, William Garrison <[EMAIL PROTECTED]> wrote: In Postgresql 8.2.9 on Windows, you cannot rename a database if the name contains mixed case. 3) Open a query window, or use PSQL to issue the following command ALTER DATABASE MixedCase RENAME TO anything_else; PostgreSQL will respond with: ERROR: database "mixedcase" does not exist SQL state: 3D000 You need to quote the identifier using double-quotes to avoid case-folding (this is a general rule): ALTER DATABASE "MixedCase" RENAME TO anything_else; Postgresql seems to force many things to lower case. Yes, it's actually a variation on the SQL standard, which specifies forcing to upper case (as Oracle does). Is it a bug that the admin tool lets you create a database with mixed case names? The admin tool is a separate project and works by its own rules. Or is it a bug that you cannot rename them thereafter? The FAQ has a good section on identifier case-folding and quoting, I think. Probably worth a read. -Doug
Re: [GENERAL] Slony vs Longiste
On Tue, Sep 23, 2008 at 03:36:51PM -0500, Jason Long wrote: > From what I read Longiste is easy to set up while I got a quote for Slony > setup for 5-10k. I can set up Slony for way less than that, FWIW. But Londiste is intended to be easier to set up than Slony. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] pg_dump | pg_sql: insert commands and foreign key constraints
Alan Hodgson wrote: > On Tuesday 23 September 2008, William Garrison <[EMAIL PROTECTED]> > wrote: >> 1) other workarounds >> 2) someone else who can confirm that this bug is either fixed, or not >> fixed. If it is supposedly fixed, then I guess I need to make a smaller >> version of my database to demonstrate the problem. > > AFAIK, the dumps created by pg_dump create all the constraints after the > table data is all loaded - there are no foreign keys in place when the data > is restored, so conflicts are not possible. It's a data only dump; the constraints already exist as part of the schema, and the SQL generated by pg_dump won't disable them for the load (as that might permit invalid data to be loaded; there's no guarantee that the constraints currently defined are the same ones as were present when the dump was taken). If you really want to load the data, at present you either need to load the tables in the right order, either by manually chopping and changing the dump or by using a -Fc dump and pg_restore, or you need to disable triggers before the load and accept the risk of invalid data being loaded. IIRC a patch was circulating (maybe applied to 8.4?) that tries to map foreign-key relationships and where possible dump data in dependency order so that data-only dumps without circular foreign key references will restore correctly with no special user action. -- 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
[GENERAL] Using a User-Def function in a query
I've written several user-defined functions (UDFs) for converting
dates to unix time, every which way.
They work find, ala
# select dtu_dmony('22 Sep 2008');
dtu_dmony
1222066800
(1 row)
Returns an integer.
---
Here's a typical query I often run (why I wrote the functions
# select count(distinct username) from stats where eventtime >
1222066800 ;
count
---
1460
(1 row)
---
but when I try to use the function in a query
# select count(distinct username) from stats where eventtime >
dtu_dmony('22 Sep 2008') ;
it never comes back...
---
Is it the table-like formatting that's killing me?
How do I get around this?
Thanks!
Ralph Smith
smithrn at here washington.edu
=
Re: [GENERAL] pg_dump | pg_sql: insert commands and foreign key constraints
Craig Ringer <[EMAIL PROTECTED]> writes: > IIRC a patch was circulating (maybe applied to 8.4?) that tries to map > foreign-key relationships and where possible dump data in dependency > order so that data-only dumps without circular foreign key references > will restore correctly with no special user action. Yeah. Historically pg_dump has not worried about foreign keys at all during data-only dumps. As of CVS HEAD there is some code in there that will sort the tables according to foreign key constraints, although it is possible to have circular constraints or self-referential constraints that defeat this. 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] Using a User-Def function in a query
Ralph Smith <[EMAIL PROTECTED]> writes:
> I've written several user-defined functions (UDFs) for converting
> dates to unix time, every which way.
> ... but when I try to use the function in a query
> # select count(distinct username) from stats where eventtime >
> dtu_dmony('22 Sep 2008') ;
> it never comes back...
Did you EXPLAIN that query? Is it using the index I suppose you've got
on eventtime? I'll bet that it's not, and that the reason why not is
that you didn't mark the function IMMUTABLE (or STABLE, which is the
correct marking if it depends on the timezone setting). The planner
won't try to use volatile functions in index conditions.
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] Slony vs Longiste
Jason Long <[EMAIL PROTECTED]> writes: > I need to set up master vs slave replication. > > My use case is quite simple. I need to back up a small but fairly > complex(30 MB data, 175 tables) DB remotely over T1 and be able to > switch to that if the main server fails. The switch can even be a > script run manually. > > Can someone either comment in as much detail as possible or point me > to a comparison of Slony vs Longiste. Or some other option I have not > heard of? You can also consider using pg-pool[1] or sequioa[2] which will automatically handle high-availability if any of the servers fails. Regards. [1] http://pgpool.projects.postgresql.org/ [2] http://sequoia.continuent.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
