Re: [GENERAL] PostgreSQL slammed by PHP creator
> > I've > > never used a hammer to put in a screw. > > So I guess you're one of those ivory-tower theory-purist academic types, at > least when it comes to home repairs. No no. When it comes to everything. I have read all the books and watched Norm Abraham till my eyes bled. There's not much I don't know would woodwork and constructing a colonial American home. Except perhaps the physical aspects, cause I can barely lift a tea-cup let alone a hammer. >As a more practical person myself, let > me just say that sometimes a 3lb hammer is exactly the right tool to get a > screw set in quickly ;-) My girlfriend occasionally mentions putting small screws in with big hammers. I would retort, but then I would have to stop eating. Statement of confidentiality: This e-mail message and any accompanying attachments may contain information that is IN-CONFIDENCE and subject to legal privilege. If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or attachments. If you have received this message in error, please notify the sender immediately and delete this message. * This e-mail message has been scanned for Viruses and Content and cleared by the Ministry of Health's Content and Virus Filtering Gateway * ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL slammed by PHP creator
All of my cool witty retorts to all of these posts are being ruined by the fact that all of my mails are being scrutinized by the moderators first. Everyone is getting in before me. Mine are going to seem like the slow witted back-water cousins to these sharp NY barristers. [EMAIL PROTECTED] wrote on 18/09/2006 10:31:34 a.m.: > A case in point: > > When it comes to driving a screw into a material (like wood) in which > the screw creates the mating helix itself when first driven in, you > might find it helpful to hammer a small portion of the screw into the > receiving part, so the screw "gets an initial hold". It is much easier > then to drive it fully in. Note that you do not need a heavy hammer and > in fact, using a heavy hammer in such cases might even be > counter-productive, since you would lose the sense of precision/accuracy > required: you would want to hammer only as much of the screw into the > wood as is needed for the screw to get "an initial hold" in the > receiving material and no more -- if you want to take advantage of the > greater holding power of a mating helix. > > P. > > Scott Ribe wrote: > >> I've > >> never used a hammer to put in a screw. > >> > > > > So I guess you're one of those ivory-tower theory-purist academic types, at > > least when it comes to home repairs. As a more practical person myself, let > > me just say that sometimes a 3lb hammer is exactly the right tool to get a > > screw set in quickly ;-) > > > > > > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match Statement of confidentiality: This e-mail message and any accompanying attachments may contain information that is IN-CONFIDENCE and subject to legal privilege. If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or attachments. If you have received this message in error, please notify the sender immediately and delete this message. * This e-mail message has been scanned for Viruses and Content and cleared by the Ministry of Health's Content and Virus Filtering Gateway * ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Table Inheritance / VARCHAR search question
I would say that splitting the data will work ok if ( and only if ) you can remove some duplication of data and therefore reduce disk usage. If it won't, it'll not really save you anything, and it may increase disk space with the additional db overheads of another set of table meta information, indexes on the new table etc etc. I have a similar width table with 7.5M rows ( no nulls ) and I get reasonable search times running on a 2GHz AMD64 with 2GB ram. Varchars on disk ( if I remember correctly ) take little or no space when they are null. ( A pg developer may need to comment for postgres ) It definitely is for Oracle ( and a good reason to rebuild tables regularly if you have a lot of inserts / updates and deletes going on ). Cheers Simon [EMAIL PROTECTED] wrote on 20/09/2006 06:15:03 a.m.: > Hi, > > I'm hoping someone on this list can save me some unnecessary > benchmarking today > > I have the following table in my system > >BIGSERIAL , INT , INT, VARCHAR(32) > > There are currently 1M records , it will grow to be much much > bigger. It's used as a search/dispatch table, and gets the most > traffic on my entire app. > > I'm working on some new functionality, which will require the same 3 > colums as above but with 3 new VARCHAR(32) columns >BIGSERIAL , INT , INT, +VARCHAR(32) , +VARCHAR(32) , +VARCHAR(32) > > ie, the new function shares the same serial and the the 2 INT columns > > I'm trying to get this to work efficiently on speed and on disk space. > > i've figured that my options are: > > a)one table with everything in it >pro: > simple >possible con: > when i had something similar in mysql 4 years ago, i had to make > all the varchars chars , because speed was awful. under this system, > 80% of the 3 new VARCHAR fields will always be null, so that disk > waste will be noticable. thats only IF there is a speed issue with > VARCHAR searching. > > b) keep current table, create new table that inherits and has the 3 > new fields >pro: simple >possible con: > i can't find any documentation on how an inherit works behind the > scenes. is the data cloned into the new table? is there a join on > every search? if this is constantly doing a join behind the scenes, > thats probably not going to work for me > > c) move to a 3 table structure >table1- serial >table2 - current table, bigserial is not bigint >table3- bigint + 3 varchars > >pro: > obviously will work >con: > a lot of restructuring > > i was going to have both table share a seqeunce, but then i > remembered that the id is foreign keyed by other tables > > if anyone can offer a suggestion, i'd be greatly appreciative > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly Statement of confidentiality: This e-mail message and any accompanying attachments may contain information that is IN-CONFIDENCE and subject to legal privilege. If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or attachments. If you have received this message in error, please notify the sender immediately and delete this message. * This e-mail message has been scanned for Viruses and Content and cleared by the Ministry of Health's Content and Virus Filtering Gateway * ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Issue with order by for type varchar
Hi All, I am trying to order a select by the primary key which is a varchar field. I have two known conditions, 1. the varchar always contains 28 characters. ( and, yes, I know the irony of using a variable array on a known fixed length field ) 2. the content of this field contains only the characters used in base 64 encoding. However, when I do an order by I get misplacing of characters so character sequences like "000+..." are coming after "". Is there any way of adding a function that would make the order by do ordering on this field in the same way as the Java Collections.sort() method? Cheers Simon Simon Kelly Java Developer Information Systems Development Information Technology Shared Services Ministry of Health DDI: Mobile: http://www.moh.govt.nz mailto:[EMAIL PROTECTED] Statement of confidentiality: This e-mail message and any accompanying attachments may contain information that is IN-CONFIDENCE and subject to legal privilege. If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or attachments. If you have received this message in error, please notify the sender immediately and delete this message. * This e-mail message has been scanned for Viruses and Content and cleared by the Ministry of Health's Content and Virus Filtering Gateway * ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Superuser lost access to particular database
This may sound a bit weird, but have you tried logging in as user1 and then granting the permission's to the superuser? Simon Kelly Java Developer Information Systems Development Information Technology Shared Services Ministry of Health DDI: Mobile: http://www.moh.govt.nz mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] wrote on 12/09/2006 02:02:12 p.m.: > I have one database owned by "user1" which as of 4 days ago the superuser, > pgsql, can't see any tables. > > I noticed I had pg_dumpalls from 4 days ago.. stuck.. upon research I > discovered that if I login as the superuser to the problem database that it > can not see any of the tables owned by the regular user. The superuser is > able to see system tables with \dS, but none of the regular ones with \d > > If I login as 'user1' all the tables are there. > > I tried "grant all on pgsql to database ", but that did not help. > Also tried to do a grant for particular table, but got error that it was not > found. > > Tried a pg_dump as the database owner, but it didn't work. > > Basically I have this database that only the DB owner can use.. and the > postgresql superuser can't see any tables and it is freezing the pg_dumpall > process. > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq Statement of confidentiality: This e-mail message and any accompanying attachments may contain information that is IN-CONFIDENCE and subject to legal privilege. If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or attachments. If you have received this message in error, please notify the sender immediately and delete this message. * This e-mail message has been scanned for Viruses and Content and cleared by the Ministry of Health's Content and Virus Filtering Gateway * ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Issue with order by for type varchar
Thanks Martijn.
Simon Kelly
Java Developer
Information Systems Development
Information Technology Shared Services
Ministry of Health
DDI:
Mobile:
http://www.moh.govt.nz
mailto:[EMAIL PROTECTED]
[EMAIL PROTECTED] wrote on 13/09/2006 01:33:15 a.m.:
> On Tue, Sep 12, 2006 at 09:51:20AM +1200, [EMAIL PROTECTED] wrote:
> > Hi All,
> >
> > I am trying to order a select by the primary key which is a varchar
field.
> >
> > 1. the varchar always contains 28 characters. ( and, yes, I know the
irony
> > of using a variable array on a known fixed length field )
>
> It doesn't make any difference in space usage.
>
> > However, when I do an order by I get misplacing of characters so
character
> > sequences like "000+..." are coming after "".
>
> Check your LC_COLLATE setting ("show all" should tell you).
>
> > Is there any way of adding a function that would make the order by do
> > ordering on this field in the same way as the Java Collections.sort()
> > method?
>
> PostgreSQL uses the collation supported by your OS (you didn't say
> what OS you're running). I believe Java has its own collation system?
> Getting them to equal in general would be difficult, but in your case
> it should work.
>
> Which collation are you using in Java and which in postgres?
>
> Have a nice day,
> --
> Martijn van Oosterhout http://svana.org/kleptog/
> > From each according to his ability. To each according to his
> ability to litigate.
> [attachment "signature.asc" deleted by Simon Kelly/MOH]
Statement of confidentiality: This e-mail message and any accompanying
attachments may contain information that is IN-CONFIDENCE and subject to
legal privilege.
If you are not the intended recipient, do not read, use, disseminate,
distribute or copy this message or attachments.
If you have received this message in error, please notify the sender
immediately and delete this message.
*
This e-mail message has been scanned for Viruses and Content and cleared
by the Ministry of Health's Content and Virus Filtering Gateway
*
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL slammed by PHP creator
[EMAIL PROTECTED] wrote on 15/09/2006 04:40:04 a.m.: > > > I wasn't able to find anything the "article" worth discussing. If you > > give up A, C, I, and D, of course you get better performance- just like > > you can get better performance from a wheel-less Yugo if you slide it > > down a luge track. > > I don't think a Yugo would give you better performance no matter what > you do ;) I found in my youth that a mini slides better on it roof than on it's wheels. As a comment on his points though, isn't it better to use the right tool for the job rather than try to force one tool to do everything? I've never used a hammer to put in a screw. I don't use what is basically a web page generator to improve the performance of my database. > > > > > -M > > > > ---(end of broadcast)--- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > > -- > > === The PostgreSQL Company: Command Prompt, Inc. === > Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 > Providing the most comprehensive PostgreSQL solutions since 1997 > http://www.commandprompt.com/ > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq Statement of confidentiality: This e-mail message and any accompanying attachments may contain information that is IN-CONFIDENCE and subject to legal privilege. If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or attachments. If you have received this message in error, please notify the sender immediately and delete this message. * This e-mail message has been scanned for Viruses and Content and cleared by the Ministry of Health's Content and Virus Filtering Gateway * ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL][Off Topic] PostgreSQL slammed by PHP creator
> > I found in my youth that a mini slides better on it roof than on it's > > wheels. > > I can top that :). I hit a telephone pole at 135MPH in a 66 Cuda :) > If it hadn't been for the gravel around that corner, I would have beat > the other car too. I used to dream of hitting 70mph in the mini. I also had nightmares about hitting other things in the mini and on one memorable occation the nightmares came true and I hit the back of my mates LandRover. Guess who came off worst :o) Statement of confidentiality: This e-mail message and any accompanying attachments may contain information that is IN-CONFIDENCE and subject to legal privilege. If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or attachments. If you have received this message in error, please notify the sender immediately and delete this message. * This e-mail message has been scanned for Viruses and Content and cleared by the Ministry of Health's Content and Virus Filtering Gateway * ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
