Re: [GENERAL] PostgreSQL versus MySQL for GPS Data

2009/3/19 Shane Ambler : > Thomas Kellerer wrote: >> >> Harald Armin Massa, 17.03.2009 15:00: >>> >>> That is: what table size would you or anybody consider really, really >>> large actually? >> >> I recently attended and Oracle training by Tom Kyte and he said (partially >> joking though) that a d

Re: [GENERAL] how to remove the duplicate records from a table

2008/10/7 Yi Zhao <[EMAIL PROTECTED]>: > I have a table contains some duplicate records, and this table create > without oids, for example: > id | temp_id > +- > 10 | 1 > 10 | 1 > 10 | 1 > 20 | 4 > 20 | 4 > 30 | 5 > 30 | 5 > I want get t

Re: [GENERAL] is a unique key on null field bad?

On 20/02/2008, Geoffrey <[EMAIL PROTECTED]> wrote: > > So, we are trying to track down some problems we're having with an > implementation of slony on our database. I've posted to the slony list > about this issue, but I wanted to get a more generic response from the > perspective of postgresql. >

Re: [GENERAL] How to cope with low disk space

On 14/02/2008, Michiel Holtkamp <[EMAIL PROTECTED]> wrote: > > Hello list, > > Just joined, read the archives, but couldn't find a solution to my > problem. My question is: 'How can I best determine when to delete > data?'. Hopefully this is the right place to ask, otherwise kindly > redirect me to

Re: [GENERAL] Conditional updateable view

On 16/12/2007, snacktime <[EMAIL PROTECTED]> wrote: > > I can't seem to find an example of how to add restrictions to the > where clause of an updateable view created via the rule system. For > example I don't want the update to complete if a where clause is > missing entirely, and in some cases I

Re: [GENERAL] Hijack!

On 11/12/2007, Obe, Regina <[EMAIL PROTECTED]> wrote: > > Well said Greg. I have the same problem too of having a crippled mail > reader :) Really I find mid posting hard to follow especially if I'm the > one that posted the question. I hope we aren't going to hit people with > hammers over thi

Re: [GENERAL] top posting

ey are just not used here. It understand you would backwards sentence the wrote I. If English like its grammar bad is posting top. (Sounds like something from Star Wars and the meaning has changed) Peter Childs

Re: [GENERAL] Using hashtext and unique constraints together

On 11/12/2007, Mason Hale <[EMAIL PROTECTED]> wrote: > > > I'm thinking that an insert trigger that ensures (SELECT count(*) FROM > page WHERE hashtext(url) = > hashtext('http://www.postgresql.org') > AND url = ' http://www.postgresql.org' ) = 0 won't work given MVCC,

Re: [GENERAL] Server crashed and now experiencing slow running queries

On 05/12/2007, Keaton Adams <[EMAIL PROTECTED]> wrote: > > > We're running PostgreSQL 8.1.4 on RHEL. I'm running a vacuum analyze on > the mxl_fs_size table to see if that shows anything. > > -Keaton > > > On 12/4/07 10:50 PM, "Keaton Adams" <[EMAIL PROTECTED]> wrote: > > > We have two servers con

Re: [GENERAL] postgresql table inheritance

On 30/11/2007, Lincoln Yeoh <[EMAIL PROTECTED]> wrote: > > Hi, > > Found this post on Slashdot which I found interesting, any comments? --- post follows --- > by Anonymous Coward on Wed Nov 28, '07 03:23 PM (#21509173) > > Speak for your database -- postgresql does. > > Postgresql's "table inheri

Re: [GENERAL] PostgresSQL vs Ingress

On 30/11/2007, Alexander Staubo <[EMAIL PROTECTED]> wrote: > > On 11/30/07, Peter Childs <[EMAIL PROTECTED]> wrote: > > Now We used somthing called Ingres at University (I graduated in 2000) > but > > I've not heard anything about it since and google does not

Re: [GENERAL] PostgresSQL vs Ingress

about 22 years ago! Now We used somthing called Ingres at University (I graduated in 2000) but I've not heard anything about it since and google does not return anything. So we might be talking about different products? So I'm slightly confused. Peter Childs

Re: [GENERAL] Unused item pointers - can someone explain?

On 26/11/2007, Elmer <[EMAIL PROTECTED]> wrote: > > On 23 Lis, 10:23, [EMAIL PROTECTED] ("Peter Childs") wrote: > > Yes, however > > > > found x removable > > > > have just been found and are now unused, so on the next run this number &g

Re: [GENERAL] Primary Key

On 21/11/2007, Sascha Bohnenkamp <[EMAIL PROTECTED]> wrote: > > > Fie on you evil synthetic key lovers. Long live the Natural Key! > > think of an foreign key referencing a primary key with 6 columns *urgs* > never! The worse thing I meet is people who think primary keys need to be integer sing

Re: [GENERAL] Unused item pointers - can someone explain?

On 21/11/2007, Elmer <[EMAIL PROTECTED]> wrote: > > Hello, > > I vacuumed one of my database tables. In vacuum output I saw the > following information about unused item pointers: > > There were 19310 unused item pointers. > > As I understand It means that my table's file consists 19310 free > tupl

Re: [GENERAL] "Resurrected" data files - problem?

the instructions on backup in the manual. oh and always, always, always test your backup works before you actually need it! Peter Childs

Re: [GENERAL] 8.3b1 in production?

maller on a freshly installed 8.3 server rather than a 8.1.1server that aint been rebuilt since 8.1.1 was newly out. I was planning to upgrade to 8.3 once its out... Down time for upgrades is somwhat lacking in a 24x7 business. Oh my 8.1 server has been up for well over a year with out being down at all. the database for longer which really show how good postgres really is 377 days uptime on computer and I think that was to move a plug. Peter Childs

Re: [GENERAL] conditional alter table add ?

On 17/10/2007, Lothar Behrens <[EMAIL PROTECTED]> wrote: > > Hi, > > I do convert an UML XMI model to a database script to create the > database schema. > To enable multiple iterations I need conditional alter table add > column like syntax. > > Is there any way to do this ? Not easily in a stra

Re: [GENERAL] Performance Issues

t not with 113" type of condition. For example if > these were telephone number prefixes or something like that, probably > there's some way to do that in a single operation instead of two, and > the selectivity function could produce a much more accurate estimate > saving t

Re: [GENERAL] Reordering columns, will this ever be simple?

so remember some 7.2 drivers not coping correctly with 7.3 due to dropped columns. Don't quote me on that I just remember having some problems about 3 years ago with Qt! Hmm this is beginning to sound like a leak. Peter Childs Decibel! wrote: > > > > On Tue, Aug 07, 2007 at 02:28:

Re: [GENERAL] [NOVICE] alter table table add column

On 31/07/07, Ronald Rojas <[EMAIL PROTECTED]> wrote: > > Oh yes you have a good point. But then I will still have to test insert > and update on views. > > Thanks a lot michael! > > On Tue, 2007-07-31 at 00:56 -0500, Michael Glaesemann wrote: > > On Jul 31, 2007, at 0:23 , Ronald Rojas wrote: > >

Re: [GENERAL] list all columns in db

On 07/06/07, Jon Sime <[EMAIL PROTECTED]> wrote: Jonathan Vanasco wrote: > > Does anyone have a trick to list all columns in a db ? No trickery, just exploit the availability of the SQL standard information_schema views: select table_schema, table_name, column_name from information_s

Creditcard Number Security was Re: [GENERAL] Encrypted column

On 05/06/07, Andrew Sullivan <[EMAIL PROTECTED]> wrote: On Tue, Jun 05, 2007 at 09:28:00AM -0500, Ron Johnson wrote: > > If he is a CC customer, the system (which I am DBA of) bills his > card directly, saving the customer much time and effort. So surely what you have is a completely separate s

Re: [GENERAL] Rounding datetimes

e space map settings date_trunc('minute',interval) see http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC Peter Childs

Re: [GENERAL] are foreign keys realized as indexes?

On 09/05/07, Lew <[EMAIL PROTECTED]> wrote: Felix Kater wrote: > I am not bound to indexes, however, wonder if foreign keys itself are > non-atomic functionality. I mean: if foreign keys are based on some > other lower level functionality like indexes or anything else which I > could use as a su

Re: [GENERAL] Business days

On 26/04/07, Greg Sabino Mullane <[EMAIL PROTECTED]> wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 >> However, you quickly run into the problem of holidays. While you >> could construct a helper table listing all the holidays, ones that >> don't fall on the same day every year (e.g

Re: [GENERAL][OT] cutting out the middleperl

On 27/03/07, Merlin Moncure <[EMAIL PROTECTED]> wrote: On 22 Mar 2007 14:58:15 -0700, Kev <[EMAIL PROTECTED]> wrote: > Hi everyone, > > I'm still in the design phase of a project. I was just wondering if > anyone has any thoughts or experience on the idea of cutting the P out > of the LAMP (or i

Re: [GENERAL] select all matches for a regular expression ?

On 23/02/07, Anton Melser <[EMAIL PROTECTED]> wrote: Hi, I need to be able to get all the matches for a particular regexp from a text field that I need to use in another query in a function. Is this possible with plpgsql? Do I have to install the perl language? Cheers Anton -

Re: [GENERAL] Questions about horizontal partitioning

ner join (read normal join) the column has to exist in both tables to be in the final result. If your doing an outer join it depends upon its type (left, right or full) and then postgres may not optimise it out. Peter Childs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Autovacuum Improvements

ell here. Bit like the max connections varible. If we run out of workers we just have to wait for one to finish. I think we need one daemon to analyse what needs vacuuming and then lauch workers to do the actual work.. Peter Childs That you have a "foot gun" is guaranteed; I think

Re: [GENERAL] advanced index (descending and table-presorted descending)

On 21/11/06, Vic Cekvenich <[EMAIL PROTECTED]> wrote: Can pgSQL 8.x do descending indexes like mySQL 5.1? (so 1st column is descending and rest are asscending?) Can pgSQL 8.x do "physically" sorted table (like a forced index order) so we don't need order by? tia, .V No and I don't really

Re: [GENERAL] [ANNOUNCE] == PostgreSQL Weekly News - April 01 2005 ==

teh c0mm|_|n1t'/!!1!! Either this message is a joke, Or you have all gone mad? I see a postpostgres on the horizon. Peter Childs == PostgreSQL Product News == PL/APL procedural language project launched http://www.pgfoundry.org/projects/plapl Bricolage 1.8.7.2.9.11.0.6.201.7a released.

Re: [GENERAL] problems with 8.0 Rc4

On Tue, 11 Jan 2005, Bruno Wolff III wrote: > On Tue, Jan 11, 2005 at 15:40:07 +, > Peter Childs <[EMAIL PROTECTED]> wrote: > > > > I thought pg_autovaccum was going to be built into 8.0 or was that > > only a rumor. > > Due to some unfortunate

[GENERAL] problems with 8.0 Rc4

of time. I'm planning to store my backup on CD with a new set of CDs for each backup period. Whats the basic principle to do this and how can I save on the number of discs I need, My database is 2.8Gb on disk currently after a fresh load from dump, the dump is 766Mb uncompressed. Pete

Re: [GENERAL] Link to development version of docs on website?

ng on my debain box. Currently its causing a panic in ext3 mid dump load I'll post more details later... Peter Childs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] 8.0 rc2 Problem

On Mon, 27 Dec 2004, Peter Childs wrote: > Tom Lane wrote: > > >Peter Childs <[EMAIL PROTECTED]> writes: > > > > > >>2. I loaded out 7.4 database on via a dump and reload. Once done I keep > >>getting errors when it trys to analyse (I'm

Re: [GENERAL] 8.0 rc2 Problem

Tom Lane wrote: Peter Childs <[EMAIL PROTECTED]> writes: 2. I loaded out 7.4 database on via a dump and reload. Once done I keep getting errors when it trys to analyse (I'm using autovacuum) Nothing unsuall just a straght forward pg_dump from 7.4 loaded into 8.0. Dec 27 07

[GENERAL] 8.0 rc2 Problem

us of transaction 2684354560 Dec 27 07:34:45 ex37 postgres[5745]: [28-2] DETAIL: could not open file "/data/db/pg_clog/0A00": No such file or directory Dec 27 07:34:45 ex37 postgres[5745]: [28-3] STATEMENT: ANALYZE "public"."driverworklog" This is running on an almost

Re: [GENERAL] Trigger

On Thu, 23 Oct 2003, Alvaro Herrera wrote: > On Thu, Oct 23, 2003 at 08:16:27AM +0100, Peter Childs wrote: > > > > > > On Wed, 22 Oct 2003, scott.marlowe wrote: > > > > > On Wed, 22 Oct 2003, Peter Childs wrote: > > > > > > > Is

Re: [GENERAL] Trigger

On Thu, 23 Oct 2003, Tom Lane wrote: > Peter Childs <[EMAIL PROTECTED]> writes: > > Great idea shame drivers to get at these are rare. Anyway you > > still need a trigger to fire the notify and these get sent when the query > > is done not when its commented. hm

Re: [GENERAL] Recomended FS

ead Disk 1, Read Disk 2, Regenate Data. Peter Childs > > -- > Canaan Surfing Ltd. > Internet Service Providers > Ben-Nes Michael - Manager > Tel: 972-4-6991122 > Fax: 972-4-6990098 > http://www.canaan.net.il > -- &g

Re: [GENERAL] XOR logical operator

brackets... I think improvements could be made. (10 minute job if that) Peter Childs > > i can't right it like this: > ( > (field1 is NUll or field2 is NUll) > and (field1 is NUll or field3 is NUll) > and (field2 is NUll or field3 is NUll) > ) > > But if i have alot of

Re: [GENERAL] A conditional DROP TABLE function

sales; The advantage of this is you keep the indexes. Peter Childs > > which we see all the time in the logs. I want to show the logs to none > db folk -- so we can't have those error messages in it. > > (There must be some explaination why postgresql (and Oracl

Re: [GENERAL] Optimizer picks an ineffient plan

x27;t really see anything against storing stats on "Prepared Statements" and Views like we do on Tables. Maybe indexs on View would be useful but keeping them uptodate would be a hazard. Peter Childs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Is SQL silly as an RDBMS<->app interface?

a (b,c) VALUES (1,2); UPDATE a set b=1, c=2 WHERE d=3; Why not INSERT INTO a set b=1, c=3; its certainly more readable and consistent. Parsing is hard work No Meta Standard (How do you find out the structure of your table using pure SQL? Very difficult to operate with Trees and

Re: [GENERAL] different transaction handling between postgresql and

k. While in this simple case ignoring the statment may look fine in more complex examples (where the is more data in the table...) this can mean data loss and massive problems! Peter Childs > > Mike Mascari > [EMAIL PROTECTED] > > > > ---

Re: [GENERAL] different transaction handling between postgresql and

ve this feeling the reason Oracle gives this result may be again because transactions have been switched off. If you want the second result in Postgres just switch auto-commit on! Peter Childs > Is there a way to make Postgresql behave like the other databases? > Which other Databases act

Re: [GENERAL] Question regarding performance (large objects involved)

e whole database you may be better off dumping the database and rebuilding. This is the information I have gathered from reading this and the other postgres newsgroups over the last few months. I would surgest that somthing like this was added to the manual. If I'm wrong (whi