Re: [GENERAL] Lost rows/data corruption?
Quoting Andrew Hall <[EMAIL PROTECTED]>: > > Do you happen to have the same type disks in all these systems? That could > > > point to a disk cache "problem" (f.e. the disks lying about having written > > > data from the cache to disk). > > > > Or do you use the same disk parameters on all these machines? Have you > > tried using the disks w/o write caching and/or in synchronous mode > > (contrary to "async"). > > It's all pretty common stuff, quite a few customers use standard IDE > (various flavours of controller/disk), some now use SATA (again various > brands) and the rest use SCSI. The kernel we use is the standard Linus > approved kernel with the inbuilt drivers as part of the kernel. We don't > supply any non-default parameters to the disk controllers. > > Thanks for your suggestion on write caching, I'll look into this, I'm also > tempted to try a different journalling FS too. > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > I'm a little late on this thread but in regards to the SATA support. 2.4.29 in my experience is really the first kernel that decent SATA support (i.e. much better data throughput). I think that would corresponse to 2.6.9 or .10 but even before you get into all that. I am curious to know what do you mean by "standard Linus kernel". Do you not compile your own kernels for the hardware platform being used? -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL still for Linux only?
Quoting "Joshua D. Drake" <[EMAIL PROTECTED]>: > Shelby Cain wrote: > > >--- "Uwe C. Schroeder" <[EMAIL PROTECTED]> wrote: > > > > > > > >>>The problem is, that it's a question of > >>> > >>> > >>perception. Most windows fans don't > >> > >> > >>>see that "their" OS is pretty instable. > >>> > >>> > > > >That may have been true in 1995. However, in this day > >and age most Windows fans don't see that their OS as > >unstable because it isn't - unless of course you are > >referring to the non-NT variations. > > > > > O.k. I don't want to start an OS war here. However > there are a couple of things I know. > > 1. As of Windows 2000, Windows is reasonably stable. > However there is a caveat, it still can not perform > under load (read slowness, possible crash) like Linux > or other UNIX variants can. > > 2. As of Windows 2003, Windows is very stable and > performs fairly well under load. However it still > can not keep up with Linux or other UNIX variants. > > The majority of the problem with Windows in these > days is people who hire other people with little > pieces of paper that say they are knowledgeable. > > A properly managed Windows server can be reliable, > can perform reasonably well, if you have the expertise > to do so. This is not that much unlike UNIX. The difference > is that UNIX requires the expertise, Windows makes you > feel like you have it when you don't. > > Sincerely, > > Joshua D. Drake > > > > > > >Regards, > > > >Shelby Cain > > > > > > > > > >__ > >Celebrate Yahoo!'s 10th Birthday! > >Yahoo! Netrospective: 100 Moments of the Web > >http://birthday.yahoo.com/netrospective/ > > > >---(end of broadcast)--- > >TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > > > > -- > Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC > Postgresql support, programming shared hosting and dedicated hosting. > +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com > PostgreSQL Replicator -- production quality replication for PostgreSQL > > The only additional thing I would add to this if it hasn't been mentioned already is that 2000 had/has some major security issues and even though 2003 is more secure out of the box from what I've experienced so far, I would **never** trust a windows box to anything other than my LAN using private IP blocks and if it has inbound access via a public IP then it would more certainly be behind another firewall that is NAT'ing/Port Forwarding its traffic. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL still for Linux only?
Quoting "Joshua D. Drake" <[EMAIL PROTECTED]>: > > > > > >The only additional thing I would add to this if it hasn't been mentioned > >already is that 2000 had/has some major security issues and even though 2003 > is > >more secure out of the box from what I've experienced so far, I would > **never** > >trust a windows box to anything other than my LAN using private IP blocks > and if > >it has inbound access via a public IP then it would more certainly be > behind > >another firewall that is NAT'ing/Port Forwarding its traffic. > > > > > Nobody should ever put a server regardless of OS on a public IP. > It should always be firewalled/Nat/Port Forwarding. > > Sincerely, > > Joshua D. Drake > > > > > -- > Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC > Postgresql support, programming shared hosting and dedicated hosting. > +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com > PostgreSQL Replicator -- production quality replication for PostgreSQL > > As with all things technology there is an art too it as well- several ways to do things. I don't, for instance, NAT/Port forward public interfaces for Linux hosts because in my experience they can be hardened without much ambiguity to be placed there. Similarly, I don't feel the same is true with most of the windows variants so for security sake increased an network complexity is justified. My point is that along with the performance issues this thread has point out, data security is another reason to consider a non-windows platform to run your production database. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Why database is corrupted after re-booting
Just to add another story... I've been running PostgreSQL on Linux since the 6.x days and back then I was almost always on IDE drives with an EXT2 filesystem. To date, the worse class of experiences I've had was going through the fs recovery steps for EXT2. In those cases I never lost data in the database even when I might have lost files. Once XFS became an in kernel option for Linux, I moved almost all my servers to that filesystem whether they are IDE or SCSI. In a recent experience where I was forced to hard reset a server with XFS and IDE drives, the box came right back up with no data loss. There is only one case of a major "problem" I've have in the last 8 years or so and I posted to this list and with Tom's help I was able to get the box online. That wasn't a filesystem problem though. Its off topic but (for those interested) that thread, "Database Recovery Procedures", was from September 16, 2003. It had to deal with padding out one of the pg_clog files in a 7.3.x system. Quoting "Welty, Richard" <[EMAIL PROTECTED]>: > Wes Williams writes: > >Even with a primary UPS on the *entire PostgreSQL server* does one still > >need, or even still recommend, a battery-backed cache on the RAID > controller > >card? [ref SCSI 320, of course] > > >If so, I'd be interest in knowing briefly why. > > it can be a lot faster. > > if the raid controller knows it has a battery backup, then it'll be free > to do whatever it sees fit in terms of write order. > > some controllers (the ibm serveraid 4 units that i have a couple of, for > example) won't do this unless they know the battery is there, they have no > option for overriding that setting. > > richard > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why database is corrupted after re-booting
Actually, because I lost several thousands of dollars or equipement a couple of years ago, I recommended these "brickwall" products to a company. http://brickwall.com/index.htm We actually never deployed these units (grounding the communications lines ended up being a much cheaper solution) but I did talk and engineer at the company and apparently they have some hospitals as client that use unitss. I'm won't get into the technology of how they work since you can read that yourself but I remember having a warm and fuzzy after my conversation. I will pull one quote from their web site though... "Unlike MOVs, TRANS-ZORBS and similar shunt based surge protectors that use elements weighing less than 1/4 ounce, Brick Wall surge protectors can easily absorb any surge repeatedly with absolutely no degradation." The important phrase here is "...absorb any surge repeatedly with absolutely no degradation." Quoting Tom Lane <[EMAIL PROTECTED]>: > Alex Stapleton <[EMAIL PROTECTED]> writes: > > suspicion is that if the power failure isn't a particularly fast one, > > (e.g. you overloaded a fuse somewhere, fuses are insanely slow to > > fail compared to alternatives like MCBs) then your RAID card's RAM > > will get corrupted as the voltage drops or the system memory will > > resulting in bad data getting copied to the RAID controller as RAM > > seems to be pretty sensitive to voltage variations in experiments > > i've done on my insanely tweak-able desktop at home. I would of > > though ECC probably helps, but it can only correct so much. > > Any competently designed battery-backup scheme has no problem with this. > > What can seriously fry your equipment is a spike (ie, too much voltage > not too little). Most UPS-type equipment includes surge suppression > hardware that offers a pretty good defense against this, but if you get > a lightning strike directly where the power comes into your building, > you're going to be having a chat with your insurance agent. There is > nothing made that will withstand a point-blank strike. > > regards, tom lane > > ---(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 > -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] clustering by partial indexes
This might have been discussed before but I wanted to know if clustering tables by partial indexes will be availble in a later release of pgSQL? For the record, this is the error I get in 8.1: iprism=# cluster hrs_idx on report; ERROR: cannot cluster on partial index "hrs_idx" hrs_idx is defined as: iprism=# \d hrs_idx Index "public.hrs_idx" Column | Type +-- stamp | timestamp with time zone btree, for table "public.report", predicate (thehour(stamp) >= 0::double precision AND thehour(stamp) <= 23::double precision) -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] clustering by partial indexes
Quoting Tom Lane <[EMAIL PROTECTED]>:
> "Keith C. Perry" <[EMAIL PROTECTED]> writes:
> > This might have been discussed before but I wanted to know if clustering
> tables
> > by partial indexes will be availble in a later release of pgSQL?
>
> What in the world would it mean to do that?
I'm not sure I understand your question. Right now you can cluster tables based
on an index but when I tried to do that with a partial index I got an error.
That information was in my first email. Perhaps a more basic question why can't
I cluster by a partial index. Here is the information again cut & pasted from
pgadminIII v1.4 (I omitted the table ddl previously).
CREATE TABLE report
(
"match" int4 NOT NULL,
"action" varchar(16),
stamp timestamptz NOT NULL,
account varchar(32),
ipaddress inet,
profile varchar(16),
rating text,
url text,
CONSTRAINT report_pkey PRIMARY KEY ("match", stamp)
)
WITHOUT OIDS;
CREATE INDEX hrs_idx
ON report
USING btree
(stamp)
WHERE thehour(stamp) >= 0::double precision AND thehour(stamp) <= 23::double
precision;
CREATE OR REPLACE FUNCTION thehour(timestamptz)
RETURNS float8 AS
$BODY$
begin
return extract(hour from $1);
end;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
Now when I go into the database with psql...
Welcome to psql 8.1.0, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
iprism=# \h cluster
Command: CLUSTER
Description: cluster a table according to an index
Syntax:
CLUSTER indexname ON tablename
CLUSTER tablename
CLUSTER
iprism=# cluster hrs_idx on report;
ERROR: cannot cluster on partial index "hrs_idx"
iprism=#
--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com
This email account is being host by:
VCSN, Inc : http://vcsn.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] What HW / OS is recommeded
Quoting Scott Marlowe <[EMAIL PROTECTED]>: > On Thu, 2004-12-16 at 06:39, Michael Ben-Nes wrote: > > I think and please correct me that Postgres loves RAM, the more the > better. > > > > Any way RAID5 is awful with writing, go with RAID1 ( mirroring ) > > With battery backed cache and a large array, RAID 5 is quite fast, even > with writes. Plus with a lot of drives in a mostly read environment, > it's quite likely that each read will hit a different drive so that many > parallel requests can be handled quite well. The general rule I use is > 6 or fewer drives will do better in RAID 1+0, 7 or more will tend to do > better with RAID 5. > > > Perl is very slow, maybe you can use PHP ? > > While mod_perl and its relations have never been fast running under > apache in comparison to PHP, it's no slouch, paying mostly in startup > time, not run time. For complex apps, the startup time difference > becomes noise compared to the run time, so it's no big advantage to > PHP. I really like PHP by the way. But Perl is pretty nice too. I run apache2, ssl, mod_perl and php. I have yet to hear complaints from my perl or php programmer. Without have another PHP vs. Perl "thing" lets all agree that they are both pretty nice :) > Run the Unix OS you're most comfortable with, knowing that PostgreSQL > gets lots of testing on the free unixes more so than on the commercial > ones. Give it a machine with plenty of RAM and a fast I/O subsystem, > and two CPUS and you'll get good performance. If your needs exceed the > performance of one of these machines, you're probably better off going > to a pgpool / slony cluster than trying to build a bigger machine. I'm not sure I heard any mention of filesystems but I've been moving all my EXT3 filesystems to XFS. Some other journaling filesystem that you might want to look into are JFS and ReiserFS. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 3: 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
Re: [GENERAL] PostgreSQL users on webhosting
planner will ignore your desire to choose an index scan if > your > > > > joining column's datatypes do not match > > > > > > > > > ---(end of broadcast)--- > > > TIP 5: Have you checked our extensive FAQ? > > > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > ---(end of broadcast)--- > > TIP 3: 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 > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 3: 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
Re: [GENERAL] Moving a database between servers
Quoting "Michael ." <[EMAIL PROTECTED]>: > I am trying to move a postgresql database from one > server to another. The original server is 7.1.3, and > the new one is 7.3.4. > > I went on the old and used the command: > pg_dumpall > dump > > On the new: > psql -e < dump > > I get this for multiple functions. (Error follows > surrounded by *'s) > > CREATE FUNCTION "transaction_visit" (integer) RETURNS > integer AS ' > select visit_id > from ( > select * >from visit_history as vh > where sequence = ( > select max(sequence) > from visit_history >where vh.firm_id = firm_id > and vh.patient_id = > patient_id > and vh.visit_id = visit_id > ) >) as v > where v.firm_id = (select firm_id from > current_transaction) >and v.patient_id = $1 >and v.person_id = (select person_id from > current_transaction) >and v.inked is null > ' LANGUAGE 'sql'; > > ERROR: Relation "current_transaction" does not > exist > > > The current_transaction is a TEMPORARY table created > like this: > CREATE FUNCTION "open_session" (integer,integer) > RETURNS integer AS ' > create temporary table current_transaction as > select $1 as firm_id, >$2 as person_id, >timeofday()::timestamp as > transaction_timestamp, >timeofday()::timestamp as > history_timestamp; > select 1; > ' LANGUAGE 'sql'; > > > > > > How do I get these functions to import correctly if it > fails due to this "current_transaction" table not > being found? (only due to it being a temporarily table > created by a different command) I had problem moving form 7.1.3 to 7.4 too. The easiest thing to do is to (if you can) use 7.4's pg_dump. Just setup the TCP/IP connectivity if you don't have it already and dump using a superuser account. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(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] [NOVICE] PostgreSQL Training
Quoting Rick Gigger <[EMAIL PROTECTED]>: > > Ok, I see what you're trying to do. In looking at this it occurs to me > that one > > of the way to aid in this effort is through more tech documents. For > instance, > > I have asked before what is the recommended procedure or stategy for > recovering > > a database that has "crashed". Something like that is wide open (and > might not > > even be the correct language) but several tech notes addressing specific > > scenarios would not only aid in actually helping someone but would also > document > > real situation that could then be tested on. Extending that scenario to > other > > area would build a nice library/knowledge base for the community which was > be > > more formalize and more efficient that searching through the newsgroups. > > It think this would be great not because I want some sort of certification > but rather because it would be nice to have a nice organized way of learning > (or teaching a new employee or something) both basic and advanced postgres > features. > > rg > I think you summed up exactly what I was trying to get out. We can put all the material together that someone would use to be certified but there should not be an emphasis on it. After reading/studying a training manual or guide, it should be completely a personal choice. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [NOVICE] PostgreSQL Training
Quoting Bruce Momjian <[EMAIL PROTECTED]>: > John Gibson wrote: > > > > > > > > >> "Linux" training is not standardized by any measure either. Lots of > > >> companies and "institutions" offer their own training courses. Some > > >> of these grow to be fairly well recognized and are offered in similar > > >> form repeatedly in different locations, but that is not > > >> "standardized" in the sense you propose. > > > > > > > > > This is not exactly true. In the marketplace the Red Hat Linux > > > certification (at least in the US) is pretty much considered the > > > standard. > > > > This makes Bret's point for him. Red Hat invested in providing > > training. It is just a de-facto standard, nothing more. > > Imagine if Linus or the Linux kernel guys tried to standardize Linux > training --- it would be a mess. > > Also, though lots of people want training, seems that want _free_ > training. They aren't flooding my Atlanta classes, that's for sure. I > give classes at many conferences around the world too, and I get usually > 20-40 people --- not exactly a flood either. Maybe they want me to come > to their house? :-) Tell me what your wife is cooking for dinner > before I decide. :-) > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > LOL, Bruce tell you what- I'm a pretty good cook. Maybe I'll talk to Drexel about a catered certification event! That definitely be *bam* taking it up a notch!! -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(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] [NOVICE] PostgreSQL Training
Quoting Peter Eisentraut <[EMAIL PROTECTED]>: > Keith C. Perry wrote: > > That situtation is a little different though since Linux comes is > > various distributions. Eventually people with get that Linux = Red > > Hat is NOT true. Heck, IBM is probably the best at promoting Linux > > these days in the mainstream. With PostgreSQL, we're just one "disto" > > so once the ball really gets rolling, its going to pick up speed > > quickly. > > Well, we're moving more into a direction where PostgreSQL is just a > "kernel" and you have to look around and search the other applications > yourself, such as language bindings, GUI tools, etc. This trend has > both advantages and disadvantages, but providing comprehensive > information through any means is becoming more of a challenge. > > -- > Peter Eisentraut > Microsoft Certified Solitaire Player > *laff* so you're an MCSP huh? I might have to use that one Peter- I wondered some time ago about why my Pg.pm module was no longer included in the release and of course I was soon introducted to gborg which I thought was a great idea. It didn't occur to me that it was a conscious direction that PG was talking. "Kernelizing" PG would definitely help advocate/market long term. Talking some familiar examples. Today Linux runs on everything- embedded this and embedded that. I almost shed a tear when I say it on an Ipod! Same thing with Apache which is probably the next great OSS success story. Some tech companies that survived the late 90's realized they could create products because of all the OSS that was already out there. I've reviewed so many things in the last two years its not even funny. Then I get the "what do you think question"... "What do I think?? I think hiring a developer/programmer will save you a lot of money. All that is, is Linux with " If more of those companies actually advertised what they were building their products off of, it would be a great help to OSS. Some do but all should. Either way, to see PostgreSQL talked about with Linux and Apache will be a great day. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [NOVICE] PostgreSQL Training
Quoting "Marc G. Fournier" <[EMAIL PROTECTED]>: > On Thu, 11 Dec 2003, Peter Eisentraut wrote: > > > Keith C. Perry wrote: > > > That situtation is a little different though since Linux comes is > > > various distributions. Eventually people with get that Linux = Red > > > Hat is NOT true. Heck, IBM is probably the best at promoting Linux > > > these days in the mainstream. With PostgreSQL, we're just one "disto" > > > so once the ball really gets rolling, its going to pick up speed > > > quickly. > > > > Well, we're moving more into a direction where PostgreSQL is just a > > "kernel" and you have to look around and search the other applications > > yourself, such as language bindings, GUI tools, etc. This trend has > > both advantages and disadvantages, but providing comprehensive > > information through any means is becoming more of a challenge. > > IMHO, "promoting" software should be a function on the web site, and not > including it as part of the distribution ... is there an open source > distro of something like freshmeat that we could put up (so that we don't > have to 'yet again recreate the wheel')? > > > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 > Marc, Isn't our Gborg loosely equivalent to Freshmeat? -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] tablespaces in 7.5?
Quoting Brian Maguire <[EMAIL PROTECTED]>: > That's right it's a big one from a performance and admin perspective. DB2, > Oracle and Informix have tablespaces. It appears that it has been in the > postgres crosshair for a few years now. I'm not sure how much has been > completed so far. > > > > Few scenarios where they are really important: > > 1. >Right now a database can be as fast as one disk. Tablespaces allow you to > distribute database objects across multiple physical locations. A big index > or table can live on separate disks distributing the io activity. > > 2. > Say you are close to running out of disk space and want to grow some of the > data onto another disk. Table spaces allow you to alter the table space and > more easily move the big table or indexes onto a different disk rather than > just moving the entire db to a bigger single disk. > > 3. > Say there is a part of a database that you want to backup every hour, but > backing up entire database is overkill. You can set it up so it backs up > different table spaces at different times. > > 4. > Couple other features of tablespaces are that they allow you to allocate > space to a specific tablespace and to take only part of a database offline or > online very easily. > > > > More detail on what they are how to mange them with oracle... > http://www.engin.umich.edu/caen/wls/software/oracle/server.901/a88856/c04space.htm > > http://www.siue.edu/~dbock/cmis565/ch8-tablespaces.htm > http://www-rohan.sdsu.edu/doc/oracle/server803/A54641_01/ch8.htm > > > -Original Message- > From: John Sidney-Woollett [mailto:[EMAIL PROTECTED] > Sent: Sat 12/13/2003 4:38 AM > To: Keith C. Perry > Cc: Bruce Momjian; Brian Maguire; [EMAIL PROTECTED] > Subject: Re: [GENERAL] tablespaces in 7.5? > > > > My (limited) understanding is that it will give you the ability to: > > i) decide what data resides in what tablespace, (database, schema, > indexes, data [coarser -> finer grain]). > ii) where the tablespace data is physically located, allowing you to > distribute your database across disks, or disk arrays. > > John Sidney-Woollett > > Keith C. Perry said: > > Quoting Bruce Momjian <[EMAIL PROTECTED]>: > > > >> Brian Maguire wrote: > >> > I am curious if tablespaces are going to be seriously targeted > >> > for the next version. It really opens up new levels of scalability > >> > and is a killer feature from an administration perspective. > >> > >> I hope so! > >> > >> -- > >> Bruce Momjian| http://candle.pha.pa.us > >> [EMAIL PROTECTED] | (610) 359-1001 > >> + If your life is a hard drive, | 13 Roberts Road > >> + Christ can be your backup.| Newtown Square, Pennsylvania > >> 19073 > >> > >> ---(end of broadcast)--- > > >> TIP 3: 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 > >> > > > > Excuse my ignorance but what will namespaces give us? I though PG schema > > > provided the namespace functionality- 'least the way I am understanding > > the term. > > > > -- > > Keith C. Perry, MS E.E. > > Director of Networks & Applications > > VCSN, Inc. > > http://vcsn.com > > > > > > This email account is being host by: > > VCSN, Inc : http://vcsn.com > > > > ---(end of broadcast)--- > > TIP 9: the planner will ignore your desire to choose an index scan if your > > > joining column's datatypes do not match > > > > > ---(end of broadcast)-------TIP > 6: Have you searched our list archives? >http://archives.postgresql.org > Ok, thats for the response. I take it a PG namespace = Oracle table space (or namespace is simply the generic term). I can see s
Re: [GENERAL] Perl "with-perl" configuration option
Quoting dj trombley <[EMAIL PROTECTED]>: > >No, the --with-perl option builds PL/Perl, which is server-side. I > recommend using the module DBD::Pg for connecting to the server from a > perl script and executing queries, but there are probably other modules > as well. > > > I read that the "--with-perl" option "Build the PL/Perl server-side > > language". Does that mean the the switch is necessary to connect to > > the PostgreSQL database if you are using a perl program? > > > > It seems to me that it is only for allowing the use of perl *inside* > > of postgres and if one is just using perl programs to access the > > database from the outside this switch is not necessary. I want to > > make sure I give the applications programmers the tools they need. > > > > > Yes, but be sure to CREATE LANGUAGE in your database so you can use it. > > > Also, does the PL/pgSQL server-side language get built automatically > > by default? > > > -dj > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > Just FYI... Typically the "other" perl module is Pg.pm (http://gborg.postgresql.org/project/pgperl/projdisplay.php). -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] tablespaces in 7.5?
Quoting Mike Nolan <[EMAIL PROTECTED]>: > > Ok, thats for the response. I take it a PG namespace = Oracle table space > (or > > namespace is simply the generic term). I can see some definite benefits > > especially with disk i/o throughput though I thought database partitioning > (I > > think that is what its called) would provide the same thing. > > I could be wrong, but I think 'namespace' is an existing concept in > PG that is a way of organizing objects into logical groups. > > As I recall, the group working on it decided to call it a 'directory' rather > > than a 'tablespace', because of concerns that the latter word might be > proprietary to Oracle. I've lost touch with the rest of the members in > that group, though, since the computer I was using for PG development > purposes got zapped by lightning in August. > -- > Mike Nolan > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > I think that is what I was getting confused with before- schemas... http://www.postgresql.org/docs/7.4/static/catalog-pg-namespace.html -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(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] tablespaces in 7.5?
Quoting Greg Stark <[EMAIL PROTECTED]>: > "Keith C. Perry" <[EMAIL PROTECTED]> writes: > > > Ok, thats for the response. I take it a PG namespace = Oracle table space > (or > > namespace is simply the generic term). > > Actually if you check back you'll notice you're the first person to say > "namespace". The original question was about "tablespaces" > > -- > greg Whoa, I sure did- my apologies. That would also explain my confusion. Ok, so on PG- namespaces = yes, via schemas tablesspaces = forthcoming -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [NOVICE] PostgreSQL Training
Quoting Peter Eisentraut <[EMAIL PROTECTED]>: > Amy Young wrote: > > In the mean time, I will investigate the "21 day" book (I have used > > the series many times!) and hope the PostgreSQL community will > > recognize the need for some training classes > > I don't see that there is a lack of availability of training > opportunities. Just ask any of the PostgreSQL consultants and they > will do custom training for you. > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > Great idea!! As a part of advocacy perhaps we in the community should add "training" as one of the things we do. I'm sure some of us have done training before but if we mobilize this effort more formally from within, we could quickly have quite a bit of "trainers" once we decide how to divide up the knowledge (i.e. training levels). I think Bruce's said his materials are on his web site so perhaps we should start there with the intention of repackaging that information for community distribution. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Duplication to External Server
Quoting David Hofmann <[EMAIL PROTECTED]>: > I'm setting up 2 database servers. The first is on our local network which > is our staging server. The second is an external server setup at my hosting > company. On a nightly bases I want to copy all the data from the local > Postgre database to the production server at hosting company overriding > whatever was there previously. > > Does anyone have any suggestions on an easy was to do this ? > > _ > Tired of slow downloads? Compare online deals from your local high-speed > providers now. https://broadband.msn.com > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > You should check out some of the replication software on gborg.postgresql.org but off the top of my head I would say that if you truely want to copy the data up to the server you can use the psql program over an SSL connection. I'm not quite sure if psql supports SSL natively but you can always tunnel the connection with ssh if your ISP supports it. If you are using 7.4 you could even use pg_dump with ssh in a similar manner. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Is my MySQL Gaining ?
I've asked this before and I'll apologize now if there was a response but how does http://gborg.postgresql.org NOT fill this. Quoting Chris Travers <[EMAIL PROTECTED]>: > Hi all; > > The problem with trying to maintain an image of unity is that PostgreSQL is > moving in a direction of being sort of like a kernel. In this sense, we > already are unified. But regarding new types, client libs, etc. then unity > is neither necessary nor desirable IMO. > > If that is something that some people see here as important, maybe they can > start their own PostgreSQL "distributions." Maybe we can link to them via > the PostgreSQL advocacy site :-) > > Best Wishes, > Chris Travers > > - Original Message - > From: "Dave Cramer" <[EMAIL PROTECTED]> > To: "Robert Treat" <[EMAIL PROTECTED]> > Cc: "Marc G. Fournier" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > Sent: Sunday, December 28, 2003 5:31 AM > Subject: Re: [GENERAL] Is my MySQL Gaining ? > > > > Well, I'm not suggesting that we force them to do anything, just give > > the appearance of unity, this should be possible with tools available, > > no? > > > > Dave > > On Sat, 2003-12-27 at 16:57, Robert Treat wrote: > > > But your examples also lists things like interface libraries. For > > > postgresql to do that, we would have to pick specific interfaces > > > applications / libraries, then have them all centralize their > > > development/release process around the main distribution. If you can get > > > everyone to agree to this (and I recommend starting by picking the > > > official python interface), we can start down a unified path, but I > > > don't see it happening. > > > > > > Robert Treat > > > > > > On Sat, 2003-12-27 at 09:41, Dave Cramer wrote: > > > > Regardless of the reasons, perception is reality. If we appear to be > > > > disheveled then we are. > > > > > > > > I would think that it should be possible to give the appearance of > unity > > > > without actually requiring a full time web-master? > > > > > > > > > > > > Dave > > > > > > > > On Fri, 2003-12-26 at 12:43, Marc G. Fournier wrote: > > > > > On Fri, 26 Dec 2003, Dave Cramer wrote: > > > > > > > > > > > One thing that they do have over postgres is a unified experience, > one > > > > > > doesn't have to go to n different sites to find things, such as > > > > > > interface libraries, advocacy sites, development sites, etc. > > > > > > > > > > Course they don't ... cause they have one, full time, paid webmaster > that > > > > > has nothing else on his plate ... one advantage to being able to > control > > > > > everything is the ability to keep everything centralized ... > > > > > > > > > > > > > > > > > Dave > > > > > > > > > > > > On Fri, 2003-12-26 at 11:53, Marc G. Fournier wrote: > > > > > > > On Fri, 26 Dec 2003, B. van Ouwerkerk wrote: > > > > > > > > > > > > > > > I think I will switch to PG anywhere soon but sometimes it's > hard to > > > > > > > > find whatever information I need. Google is a great help but I > would > > > > > > > > expect it in the docs. > > > > > > > > > > > > > > Like ... ? > > > > > > > > > > > > > > > > > > > > > Marc G. Fournier Hub.Org Networking Services > (http://www.hub.org) > > > > > > > Email: [EMAIL PROTECTED] Yahoo!: yscrappy > ICQ: 7615664 > > > > > > > > > > > > > ---(end of broadcast)--- > > TIP 3: 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 > > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Is my MySQL Gaining ?
Quoting Shridhar Daithankar <[EMAIL PROTECTED]>: > On Sunday 28 December 2003 11:15, D. Dante Lorenso wrote: > > The only SQL customizations that MySQL has that I really miss in > PostgreSQL > > are the commands: > > > > SHOW DATABASES; > > \l > > > SHOW TABLES; > > \dt > > > DESC table; > > \d tablename > > > > > That was ubber simple to do in MySQL. To this day, I have trouble with > > that in PostgreSQL. I'm constantly doing: > > > > psql> \? > > psql> help; > > ERROR: syntax error at or near "help" at character 1 > > psql> \h > > ... > > * damnit, that's not it...* > > psql> \? > > psql> \d > > * ok, now which flag do I use for tables vs functions..etc?* > > \df for functions and \dt for tables. > > Problem is psql is unique though very powerful. I need to use oracle's > sql-plus on HP-UX at times(Otherwise I crawl back to TOAD) and I don't think > > it is nowhere near to psql. > > or may be I play with postgresql more than oracle..:-) anyways > > > I finally figure it out, I just end up forgetting again later. I still > > have no clue how I'd find the same data without using psql. In MySQL > > I can run those queries from PHP, PERL...etc. I know you can find that > > data in system tables in PostgreSQL, but I don't wanna muck around with > > all that. I just wanna do something as simple as MySQL. > > Well, actually I would say it is great way of learning postgresql internals. > > There is a switch -E to psql which shows you queries sent to server for each > > command you provide. > > Problem with mysql is the approach is easy to start with but adding those > command in your standard list of SQL commands falls out on standard > compliance and maintainability. > > Another post on this thread mentioned postgresql should run against oracle. > Sole reason postgresql v/s mysql debate should exist is to provide > comparision in feasibility study. The hurdles you mentioned are true but that > > are just part of bit steeper learning curve of a standard way of doing > things.. > > Shridhar This is what I don't get. Why do people thing learn PG is going to be like learning MySQL in the first place? Because its OSS?? I certainly hope not. This is apples to oranges. I read someone say the documentation was "light" too. I'm not sure what that meant but I looked for at the 3 inch doubled side binded of my 7.3.2 docs- admin,user &,programmer- its as big as my J2EE binder. Not very scientific I know :) Seriously though, when people indicate PG is "hard", I hear, "if it was easy everone would be doing it". -$0.02 > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Is my MySQL Gaining ?
Quoting Tony <[EMAIL PROTECTED]>: > Sadly a company will believe anything that a consultant they trust tells > them. Otherwise there'd be little point in hiring a consultant to give > them advice would there? There are different levels of trust and in addition sometimes consultants are used for feasibility studies- "how would you do this?" If you're telling me you've never been in a situation where a client called you in because they want to implement a project with certain products or other specification because they have "done the research and want to proceed this way" then I'm very glad to hear that. No matter how much you are trusted as a consultant or technical advisor you are still just a guide. That means it is possible for your client is "wander off the path". I remember in the not so long ago days when people wanted to run certain hardware or software because to not do so would give the perception that you were not up to par. Sometimes what is used has nothing to do with using the best product for the job. That seems to be a sub-text of this thread. > It seems rather illogical that you'd refuse to work with a company that > had been given potentially sub-standard advice, based on what appears to > be a theological view? I'm sure the MySQL folks don't think they are sub-standard. A fair amount of my business is "clean up" so if someone said, "we have an app on MySQL that is not working for us" I would most definitely be interested. If someone said to me what DB do I use to build applications, I would say PG. If then someone says to me that "well we're a MySQL shop" then I would have to hear more because depending on what they want to do, I might not take on that project. There is nothing illogical or theological in that. > Either that or you have more consulting work than you know what to do > with, that you can afford to base business decisions on an ideological > basis. This really doesn't make sense. Are you telling me you are going to accept any an all work regardless of competency and confidence in that product? Would you really build a financial application on MySQL? We both know that we all have a certain ideology (read: religion) when it comes to our trade. To be clear, I'm not saying anything against someone who would use MySQL for a financial app. I'm just saying that I would not (or at least try very hard not to) involve myself in that project or any other project where I thought there was a bad design or implementation. When you are a smaller operation your reputation is going to weigh in a lot more than a larger company. I do not want my name to be tied to something sub-standard. If a consultant values his or her reputation I don't see how you can NOT consider what products you are willing to put your name on the line for. > If I chose not to work with companies that used Windows as servers > (because IMHO, Windows is not a good server environment) my house > would've been repossessed, and I'd have probably starved by now. > > T. 12 years ago calling myself a consultant one day meant putting in a netware 3.11 server for a bunch of PCs and MACs and pulling coax. Did I want to do that- I can't really say because at the time I had to eat. That for me is on the outer fringes of this thread. Few organzations are NOT using Windows somewhere, and an increasing number of organizations are starting understand OSS solutions. So both world are merging so it not about avoiding and one thing. Its about picking an choosing your battles. > > Keith C. Perry wrote: > > > The way I look at it is that I probably don't want to deal with a > >company that thinks that MySQL on windows is "good environment". > > > > > > > -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Is my MySQL Gaining ?
Quoting Gaetano Mendola <[EMAIL PROTECTED]>: > Chris Travers wrote: > > Regarding the importance of PostgreSQL on Windows. > > > > For example, I am developing a hotel reservation management application > > using Python and PostgreSQL (http://sourceforge.net/projects/openres). > This > > will only run on Linux and UNIX, so in order to get this to run on > Windows, > > I need to use either MySQL or Firebird. Or aI can require Cygwin. But > that > > is a bit over the top IMO, for a small hotel or B&B to consider, > especially > > because I want to run it if possible on existing equipment to keep > > implimentation costs down. > > Who cares about where the GUI must run? Chris and his client- > May you please explain me why the GUI must be on the same DB server? > After all is better have the user's hand far away from the datas. If its a small hotel or B&B I would think an addtional workstation might be cost prohibitive. Then again, that might simply be the way they want it. > > Regards > Gaetano Mendola > > > > ---(end of broadcast)------- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(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 my MySQL Gaining ?
Quoting Shridhar Daithankar <[EMAIL PROTECTED]>: > On Monday 29 December 2003 12:47, Tom Lane wrote: > > Shridhar Daithankar <[EMAIL PROTECTED]> writes: > > > That is right. but that fact remains that postgresql documentation is > > > just sufficient. If you read the manual and follow it religously to > comma > > > and fullstop, it tells you everythings. But it certainly isn't a place > > > where you can glance over it and get hang of it. > > > > This is surely true, and I've not seen anyone denying it. The people > > Well, for newbies to postgresql, let's state this fact upfront and not make > them discover it..:-) > > > who are doing development are, um, not strong at documentation (I > > include myself here). What we need are some folks to step up and > > improve the documentation --- and then maintain it in the face of future > > changes. Any volunteers out there? This is an open-source project > > after all, and that means "scratch your own itch" among other things... > > If you ask me, let's not do that. Not at least on a grand scale. Isolated > areas are OK on case by case basis.. > > I regualrly use development build documentation from > developers.postgresql.org > and I have seen the documentation in source code. In my view, postgresql > developers do document it very clearly whenever required. > > If we dilute the documentation too much, that will make things simpler > initially but that will simply create a maintainance nightmare as one has to > > maintain much larger amount of documentation. > > And once you get used to precise style of postgresql documentation, going > back > to anything else is a pain. ( MSDN.. I scream at nights but I digress). > > IMO documentation of postgresql is fine overall. What we need to do is. > > 1. State upfront that this is not handholding. > > It will make lots of things easier and offload work of expanding documents > given limited human resources working on the project. A disclaimer is far > easier to maintain than a manual..:-) > > And it will prepare anybody for upcoming hardships..:-) > > 2. Document and reuse it. > > Personally I would like to see responses on general and oter such list as > URLs. If we answer it repeatedly, let's document it and point the people to > them. Let them dig around 3-4 URLs around it and they will have islands of > enlightenments. Over the period, these island will merge in a great > landscape..:-) > > Just a thought.. > > Shridhar > > P.S. If somebody thinks I can not imagine how a newbie feels, I will agree. > But looking back, dumbing down anything is not good in long term..an > experience that is > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > Shridhar, I tend to agree with you. I personally think the docs are very good and have the techical depth warranted for a product like PostgreSQL. On the other hand for the ad & m (advocacy and marketing) side of things. I'm betting some clearly labelled tutorials/guide next to the disclaimer about the the main docs be more of a reference would appease those who might be a bit green to a product of PG breadth and depth (heck I still think I'm in the category sometimes). 'bout two weeks ago there was another thread where certificating/training et al were discussed and one of the things that I had mentioned was that in that regard, we should probably have more tutorial/guide based on real world scenarios available on techdocs. Although I don't think I qualified to write for the main docs, I definitely can contribute to the techdocs in the manner I just mentioned. Matter a fact, I finally finish my first one "Using PostgreSQL for Domino 6 RDBMS Backends". I'm doing the final read now so hopefully I can get it over to Robert for posting. Perhaps the "newer" folks on the list could tell us what type of guides they want to see. I'm sure someone has a wish list somewhere. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 3: 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
Re: [GENERAL] Is my MySQL Gaining ?
Quoting "Joshua D. Drake" <[EMAIL PROTECTED]>: > > >This concerns me. This is the second time recently someone has said > something > >is NOT documented and it it turn out it is. > > > >So my question is (no offense to anyone) are the web sites not "clear" > enough to > >find information quickly or are people just being lax/lazy when they are > searching. > > > > > > > Well, at anything greater than 1024x768 the "docs" link on the main site > is near invisible. The font size is fine, but combined with the color scheme > and location, it can be hard to spot... Mainly, I think because the page > is so busy. Agreed- I was hoping some else would say that. > If you look at the front page the first thing you see is News which is fine, > but IMHO the first thing should be the nav bar comes before News but > News is big, bold print. > > Also searching the PostgreSQL docs is a useless venture. I just typed in > trigger and hit search 20 seconds later I am still waiting. I mentioned that earlier in this thread. > Why don't we just add Google search to the page? > > Sincerely, > > Joshua D. Drake That and it is possible to propose a new layout. Something that is somewhat consistant across the major sites (www,gborg,techdoc,advocacy)? And yes, I'd be will to do some work on that. > > > > > -- > Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC > Postgresql support, programming, shared hosting and dedicated hosting. > +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com > > -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 3: 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
Re: [GENERAL] 'like' refuses to use an index???
Quoting Dima Tkach <[EMAIL PROTECTED]>: > Nah... > This is a different story - for teh index to be useful, the *beginning* > of your search string must be known. > So "like '00423%" and "~ '^00423'" should both work, but "like '%423'" > and "~ '00423'" both won't - it's like searching a telephone book for > somebody, whose last name ends with "erry" (as opposed to begins with > "Perr"). > > Dima > > > Keith C. Perry wrote: > > >I wanted to know this too because I notice that using like with wildcards > >appears to be similar to a regular expression in that the index is not used. > > >This is what I have... > > > >ethernet=# select version(); > > version > >-- > > PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 > >(1 row) > > > >ethernet=# \d vendors > > Table "public.vendors" > > Column | Type | Modifiers > >-+---+--- > > header | character(6) | > > company | character varying(80) | > >Indexes: > >"vender_id_idx" btree (header) > > > > > >ethernet=# explain select * from vendors where header like '000423'; > > QUERY PLAN > > >- > > Index Scan using vender_id_idx on vendors (cost=0.00..113.26 rows=36 > width=68) > > Index Cond: (header = '000423'::bpchar) > > Filter: (header ~~ '000423'::text) > >(3 rows) > > > > > >Ok, that made sense- > > > >ethernet=# explain select * from vendors where header like '%000423%'; > >QUERY PLAN > >-- > > Seq Scan on vendors (cost=0.00..151.15 rows=3 width=68) > > Filter: (header ~~ '%000423%'::text) > >(2 rows) > > > >This didn't make sense until I did... > > > >ethernet=# explain select * from vendors where header like '0004%'; > > QUERY PLAN > > >- > > Index Scan using vender_id_idx on vendors (cost=0.00..113.26 rows=36 > width=68) > > Index Cond: ((header >= '0004'::bpchar) AND (header < '0005'::bpchar)) > > Filter: (header ~~ '0004%'::text) > >(3 rows) > > > >which again made sense because of the header's size but both- > > > >ethernet=# explain select * from vendors where header ~* '0004'; > >QUERY PLAN > >--- > > Seq Scan on vendors (cost=0.00..151.15 rows=58 width=68) > > Filter: (header ~* '0004'::text) > >(2 rows) > > > >ethernet=# explain select * from vendors where header ~* '000423'; > >QUERY PLAN > >-- > > Seq Scan on vendors (cost=0.00..151.15 rows=3 width=68) > > Filter: (header ~* '000423'::text) > >(2 rows) > > > >are sequentially scanned which means that regex's do not use indexes. Is > that > >right also? > > > > > > > > Ahhh, so it is!! So let me ask you this. In order to build an index that would be able to handle something like "lastname like '%erry'", would you need that full text search patch in contrib (tsearch?) or could you do it with an index on a function? -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 'like' refuses to use an index???
Quoting Mike Nolan <[EMAIL PROTECTED]>: > > You didn't initdb in C locale ... > > The documentation section on localization could use some enhancements > and maybe some more examples. > > The 'c' locale isn't very well defined in the docs, except to say that it > is 'special' and is the default if no other locale is defined. That > section doesn't mention that you need that locale to get 'like' to > use an index, for example. > > However, I think RH always sets the LANG environmental variable, so > that's going to be picked up by initdb, which means that the C locale > will NOT be used unless specifically asked for. Other OS packages may > also force the choice of a default LANG value. > > Dumb question of the hour: How does one find out what locale a > DB is initialized in? > -- > Mike Nolan > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > According to the doc its the pg_controldata utility but when I run it, I get an error: "could not open file "-D/global/pg_control" for reading: No such file or directory" I wonder if that is because I didn't use a locale when I initialized the database. (My locale -a return "C") -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] 'like' refuses to use an index???
Quoting Tom Lane <[EMAIL PROTECTED]>: > Mike Nolan <[EMAIL PROTECTED]> writes: > > However, I think RH always sets the LANG environmental variable, so > > that's going to be picked up by initdb, which means that the C locale > > will NOT be used unless specifically asked for. Other OS packages may > > also force the choice of a default LANG value. > > Yeah. There have been some discussions on pgsql-hackers about > defaulting to C locale instead of honoring LANG, but we haven't done > anything. > > > Dumb question of the hour: How does one find out what locale a > > DB is initialized in? > > In 7.4, you can just "show lc_collate". In prior versions you need > to use pg_controldata to see what's stored in pg_control. Show the pg_controldata program work in 7.4? If not, shouldn't it be removed from the docs in favor of the show method of finding out the locale. > BTW, 7.4 also has a specialized index opclass that can be used to create > LIKE-compatible indexes even if you are using a non-C locale. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] TPC-C and Postgres
Quoting Mark Kirkwood <[EMAIL PROTECTED]>: > > > > >On page 7 of this document: > > > >"Earlier versions of Postgres were often unstable and quite often there > were > >even data losses. The Postgres developer team has invested a great deal of > time > >in regression tests, which guarantee high stability and data security. > Releases > >now only occur after longer Beta phases. The new versions of Postgres have > >become considerably more effective (see box Benchmarks ). > > > >There are not known to be any serious stability problems with MySQL. With > the > >new feature of replicability, the system has taken another major step in > the > >direction of increased failure safety. Especially in conjunction with PHP, > MySQL > >achieves good performance values in web applications (see box Benchmarks ). > A > >paper by the founder of MySQL, Michael Widenius on performance optimisation > is > >recommended in this respect" > > > > > >H... > > > > > > > > Sounds like he has Mysql And Pg confused... :-) Yea, thats what I thought- 2001 was not that long ago... > e.g : > > Recently a colleage of mine, who is fond of Mysql initiated a friendly > challenge to the effect "Mysql can do big queries just as well as Pg". > > I suggested he try my Data Warehouse benchmark > (http://sourceforge.net/projects/benchw) > with size 10 (about 10G or so). Nice real nice. I need to probably get that and run some test against MSSQL. I'm working on developing my own test dataset for some environments tests in the social services world but your software would be useful for some "raw" tests. > I went away, produced my results (using Pg 7.4) and mailed them to > him... however at his end - he cannot get Mysql to load the data (it > kills his connection,.. thats nice). He is not impressed at all, and is > considering using Pg! > > (he was using Mysql 4.0.15) Once again proof is in the pudding! > regards > > Mark > > > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 3: 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
Re: [GENERAL] Postgress and MYSQL
Quoting "Joshua D. Drake" <[EMAIL PROTECTED]>: > > >You're kidding me... > > > > > > > No I am not... You missed the point, there is no official Linux website. > Linux.org is ran by one guy, > linux.com is run by VA (I actually used to have [EMAIL PROTECTED]), I own > Linuxdoc.org and Linuxdoc.com... > The closest you get to a "linux" website is kernel.org. > Sincerely, > > Joshua D. Drake Then the word "official" is ambiguous and perhaps not even applicable. If a site is NOT run by Linus is it not official? Due to how Linux is structured is hard to say what is "official" (if you believe that concept applies). That is one of the things MS is expliot about Linux and OSS in general. > >linux.org > >linux.com (e, way to cluttered these days) > >linuxhq.org > >linuxhq.com (looks like their undergoing a facelift) > > > > > >I seem to remember someone saying that PG is supposed to be leaning towards > >being more like a "kernel" so I would like one day the site might become > >conceptually like linux.org in the sense that they point you toward the > various > >distributions of Linux. > > > >Comments? > > > > > > > > > -- > Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC > Postgresql support, programming shared hosting and dedicated hosting. > +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com > PostgreSQL Replicator -- production quality replication for PostgreSQL > -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How should I get started?
Quoting Leif K-Brooks <[EMAIL PROTECTED]>: > I've been programming web applications in PHP/MySQL for a few years. > It's worked pretty well, but I've been getting annoyed with the lack of > more advanced features lately. After some reading, I've decided to > switch to Perl/PostgreSQL. > > I'll be discarding all database data for other reason anyway, so moving > data isn't an issue. I just want to learn how to best use some of > PostgreSQL's cool features. Is there any documentation about that? I've > looked at the official docs, but they're very dry. Any suggestions? > > > ---(end of broadcast)--- > TIP 3: 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 > Well from me, "glad to hear it". My environment is Linux, Apache, mod_perl (or perl for simple things) and PG. I'm been using this environment in one for or another since 1997. When you say "cool features" I'm not sure what you've looked at but if you could tell us what you've read that you like, I'm sure someone will respond if you'd like to know more about specific items. I will say this though, the "cool"-ness of PG (or "slick"-ness as I like to call it) really comes in the integration of PG to your environment. I'm at the point now where I can't build an application without using PG for the data storage, maniplulation or mining. PG copuled with perl really have become an invaluable tool for me. Hopefully you'll find the same. In regards to the docs, they are really meant to be more of a reference. Plus you'll have to get used to how the community leads here present information. If you haven't already, I would encourage you to check our the http://techdocs.postgresql.org site to read about some more hands on and in depth information. There is also http://gborg.postgresql.org where you can download some of the other software tools, interfaces, etc that you may need develope and deploy your applications. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 3: 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
Re: [GENERAL] upgrade postgres 7.1 to 7.4.2
Quoting csysi <[EMAIL PROTECTED]>: > Hello, i upgrade postgres version 7.1 to version 7.4.2 and it tell me that > the databases are incompatible. > > Can i convert the databases ? > > > Thank´s for the help > > > Jorge > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > If you could post the exact error that you are getting and how you are proceeding that would be helpful. However, I've upgraded 7.1.3 to 7.4 and I had lots of problems until I used the pg_dumpall from 7.4. In my case, it had two boxes so I ran the pg_dump from the 7.4 box to the 7.1.3. Basically, you are dumping over the network. Its very straight forward. All you have to do is set up the appropriate parameters in the pg_hba.conf file of your 7.1 box and follow the directions in the man page of pg_dumpall (see the "-h" parameter). If you are not on a network, you could install 7.4 version in a different directory and configure to run on a different port. You still dump via IP this way. You can run both systems head to head that way and see how things went. Good luck- -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Help with query: indexes on timestamps
Ok, I've tried a number of things here and I know I'm missing something but at
this point my head is spinning (i.e. lack of sleep, too much coffee, etc...)
My environment is PG 7.4.3 on Linux with 512Mb of ram and swap. This was just
upgraded from 7.4 (just to make sure I'm current). Some of my settings in
postgresql are giving fatal errors but I don't think my issue is related to my
query problems. I also have a laptop running with the same basic specs (no
raid, slower processor).
I use a recent pgadmin-III as my client.
We're also running this query in MS-SQL.
I have a table with with 1 million records in it. Here is the definition
CREATE TABLE report
(
match int4,
action varchar(16),
stamp timestamptz,
account varchar(32),
ipaddress inet,
profile varchar(16),
rating text,
url text
)
WITHOUT OIDS;
The is one index:
CREATE INDEX stamp_idx
ON report
USING btree
(stamp);
That query I'm running is:
SELECT date_part('hour'::text, report.stamp) AS "hour", count(*) AS count
FROM report
GROUP BY date_part('hour'::text, report.stamp)
ORDER BY date_part('hour'::text, report.stamp);
Here is the plan I get:
QUERY PLAN
Sort (cost=47420.64..47421.14 rows=200 width=8)
Sort Key: date_part('hour'::text, stamp)
-> HashAggregate (cost=47412.00..47413.00 rows=200 width=8)
-> Seq Scan on report (cost=0.00..42412.00 rows=100 width=8)
(4 rows)
Now from from I understand that, the index I created would not be used since I
would be looking at every row to do the date part. The query under 7.4 ran in
about 8 seconds. In 7.4.3, its taking 37 seconds for the same plan (which is
fine for the system not be tuned yet). On my laptop its taking 6 seconds.
MS-SQL is taking 8 seconds. These runs are after I do vacuum full, vacuum
analyse and reindex on the database and table respectively
My question: How can I get this query to use an index build on the date_part
function. On the MS-SQL side, creating a computed column with the date part and
then don't an index on that column bring the query done to 2 seconds.
I tried creating this function:
CREATE OR REPLACE FUNCTION whathour(timestamptz)
RETURNS int4 AS
'begin
return date_part(\'hour\',$1);
end;'
LANGUAGE 'plpgsql' IMMUTABLE;
and then and index:
CREATE INDEX hour_idx
ON report
USING btree
(stamp)
WHERE whathour(stamp) >= 0 AND whathour(stamp) <= 23;
but I get the same plan- which makes sense to me because I'm again inspect
quiet a few row. I'm sure I'm missing something...
I couldn't see from the docs how to make a column equal a function (like
MS-SQL's computed column) but to me it seems like I should not have to do
something like that since it really is wasting space in the table. I hoping a
partial index or a function index will solve this and be just as efficient.
However, that method **does** work. Is there a better way?
Thanks to all in advance.
--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com
This email account is being host by:
VCSN, Inc : http://vcsn.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Help with query: indexes on timestamps
Quoting Richard Huxton <[EMAIL PROTECTED]>:
> Keith C. Perry wrote:
> >
> > I have a table with with 1 million records in it. Here is the definition
> >
> > CREATE TABLE report
> > (
> > match int4,
> > action varchar(16),
> > stamp timestamptz,
> > account varchar(32),
> > ipaddress inet,
> > profile varchar(16),
> > rating text,
> > url text
> > )
> > WITHOUT OIDS;
> >
> > The is one index:
> >
> > CREATE INDEX stamp_idx
> > ON report
> > USING btree
> > (stamp);
> >
> > That query I'm running is:
> >
> > SELECT date_part('hour'::text, report.stamp) AS "hour", count(*) AS count
> >FROM report
> > GROUP BY date_part('hour'::text, report.stamp)
> > ORDER BY date_part('hour'::text, report.stamp);
>
> You will always get a sequential scan with this query - there is no
> other way to count the rows.
>
> With PostgreSQL being MVCC based, you can't know whether a row is
> visible to you without checking it - visiting the index won't help. Even
> if it could, you'd still have to visit every row in the index.
>
> Assuming the table is a log, with always increasing timestamps, I'd
> create a summary table and query that.
Yea, actually it a proxy server log each month the databasae is 500k records. I
have two months loaded only to put some stress on the server. Some ever month
I'm loading the data just so I can do some analysis. The optimization question
came up when one of the other database folks wanted to play with the database in
MS-SQL server.
How can I add a column that respresents a function that returns just the
date_part? I wondering if that will increase the speed of the query in similar
fashion as the MS-SQL did.
I hadn't though about the MVCC vs. file locking issue. The MS-SQL server does
not have any load on it and I'm sure if other users were hitting it the same
table with the same query, PG would be perform better.
--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com
This email account is being host by:
VCSN, Inc : http://vcsn.com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] on connect trigger?
Quoting [EMAIL PROTECTED]: > Is there any kind of mechanism in pg 7.3 for doing something like what I > would describe as a "login trigger" procedure or maybe "on connect" > trigger, i.e., a way to specify a stored procedure to run when a user > connects to the database? > > What I'm thinking is this. Right now, my end-user GUI application calls a > procedure which updates the user account expiration date whenever they > log in. The idea is that accounts that are never used will expire, > eventually, and those that are active will continually have the > expiration date pushed > further ahead each time they log in. > > I'd like to not depend on the application making this call, because other > applications which connect to the same database will be written, and the > developers might not build in this same explicit procedure call -- it > really is the kind of thing that is best done on the backend. > > ~Berend Tober Berend, I've got something like that setup on an e-communities site I built. There was already a "last action" query/report I had so what I have setup on as part of the database nightly vacuum is to first delete any account that did not have any actions for over a year. You don't need to do a trigger to do that. You just need to make a cron job that run at whatever is an acceptable interval. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Retoring non-administrative user databases
Quoting Peter Eisentraut <[EMAIL PROTECTED]>: > Keith C. Perry writes: > > > What am I missing? > > A reproduceable test case. > > -- > Peter Eisentraut [EMAIL PROTECTED] > ??? Ok, lets try the question this way... What is a method of dumping and restoring a complete database cluster when that cluster contains users that are NOT allowed to create databases. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Retoring non-administrative user databases
I was going a test run through of of moving my 7.1.3 databases to 7.4RC1 and I have a problem with creating databases for my users that do not have administrative accounts. By that I mean, these users are NOT allow to create databases. So the process was this: On the 7.1.3 server: pg_dumpall -c > dump.db On the 7.4RC1 server: psql -f dump.db template1 or psql < dump.db Either style has the same result. Also, I usually don't use pg_retore but in this case I tried: bin/pg_restore -d template1 --ignore-version --use-set-session-authorization dump.db the error I got was: pg_restore: [archiver] input file does not appear to be a valid archive I'm also tried pg_restore with a 7.3.4 database file and the result was the same on the 7.4 server. What am I missing? -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] building 7.4 with plperl
Quoting Gianni Mariani <[EMAIL PROTECTED]>: > Gianni Mariani wrote: > > > > > Before I go deep into this - does anyone have the quick fix for this ? > > > > Some facts - the 7.3.4 version of plperl.c has the same errors in the > > 7.4 tree. > > The 7.4 version of plperl.c (with some error handling API calls > > commented out) compiles fine in the 7.3.4 tree. > > (Same machine - same install of perl !) Points to using some > > alternate perl API probably by macro collision ? > > /* Define to 1 to build client libraries as thread-safe code. >(--enable-thread-safety) */ > #define USE_THREADS 1 > > So this seems to be the collision. > > --enable-thread-safety is a new option for libpq - however this collides > with perl's use of the same macro. > > I suspect that the right answer would be to change the name USE_THREADS > to PG_USE_THREADS for pg. > > Quick and nasty work around patch: > > --- plperl.c.7.4Thu Sep 4 08:16:39 2003 > +++ plperl.cMon Nov 17 23:07:05 2003 > @@ -55,6 +55,7 @@ > #include "catalog/pg_proc.h" > #include "catalog/pg_type.h" > > +#undef USE_THREADS > /* perl stuff */ > #include "EXTERN.h" > #include "perl.h" > > another fix would be to make plplerl use the explicit api. > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > I had this same issue as well but now I'm *slightly* concerned since most of my code is perl. How soon would issue be reviewed? (not that I'm NOT going to use your patch for right now). -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] building 7.4 with plperl (Updated)
Quoting "Keith C. Perry" <[EMAIL PROTECTED]>: > Quoting Gianni Mariani <[EMAIL PROTECTED]>: > > > Gianni Mariani wrote: > > > > > > > > Before I go deep into this - does anyone have the quick fix for this ? > > > > > > Some facts - the 7.3.4 version of plperl.c has the same errors in the > > > 7.4 tree. > > > The 7.4 version of plperl.c (with some error handling API calls > > > commented out) compiles fine in the 7.3.4 tree. > > > (Same machine - same install of perl !) Points to using some > > > alternate perl API probably by macro collision ? > > > > /* Define to 1 to build client libraries as thread-safe code. > >(--enable-thread-safety) */ > > #define USE_THREADS 1 > > > > So this seems to be the collision. > > > > --enable-thread-safety is a new option for libpq - however this collides > > with perl's use of the same macro. > > > > I suspect that the right answer would be to change the name USE_THREADS > > to PG_USE_THREADS for pg. > > > > Quick and nasty work around patch: > > > > --- plperl.c.7.4Thu Sep 4 08:16:39 2003 > > +++ plperl.cMon Nov 17 23:07:05 2003 > > @@ -55,6 +55,7 @@ > > #include "catalog/pg_proc.h" > > #include "catalog/pg_type.h" > > > > +#undef USE_THREADS > > /* perl stuff */ > > #include "EXTERN.h" > > #include "perl.h" > > > > another fix would be to make plplerl use the explicit api. > > > > > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > I had this same issue as well but now I'm *slightly* concerned since most of > my > code is perl. How soon would issue be reviewed? (not that I'm NOT going to > use > your patch for right now). > > -- > Keith C. Perry, MS E.E. > Director of Networks & Applications > VCSN, Inc. > http://vcsn.com I normally wouldn't reply to myself but I didn't have the original message. I just built 7.4 on a Slackware 9.1 release with the following configure command: ./configure --enable-thread-safety --with-perl --with-openssl --with-tcl I did not get any errors. The perl version was 5.8.0 and GCC version was 3.2.3 On the box that did get errors, it was perl 5.6.1 and gcc 2.95.3 (slackware 8.0 me thinks) I hope this additional information helps. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] building 7.4 with plperl
Quoting Gianni Mariani <[EMAIL PROTECTED]>: > Keith C. Perry wrote: > > >I had this same issue as well but now I'm *slightly* concerned since most of > my > >code is perl. How soon would issue be reviewed? (not that I'm NOT going to > use > >your patch for right now). > > > > I suspect that this is only an issue when you use > "--enable-thread-safety" which according to the release notes is only > for libpq and only fixes MT issues on connection start-up. So > theoretically, if you're using plperl in V7.3.4 or earlier, you simply > don't need "--enable-thread-safety" and so you may compile happily > without it. (That's the theory anyway). > > This certainly needs to be addressed (patch or document) but it's not at > all an issue for someone migrating from an earlier release (not a > regression). I hope that you're slightly concerned *no more*. > > G I figured that much and yes I'm not concerned anymore but it does seem like it might be version issue as well with perl and/or gcc since I did have a successful compilation. I've got a project to move all my servers to Slackware 9.1 which will make this non-issue for me but for the original poster your patch or omitting the "--enable-thread-safety" option look like equally good resolutions. Thanks -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] uploading files
Quoting Rick Gigger <[EMAIL PROTECTED]>: > What is the best method for storing files in postgres? Is it better to use > the large object functions or to just encode the data and store it in a > regular text or data field? > > > ---(end of broadcast)--- > TIP 3: 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 > Rick, This has been discussed recently so you might want to dig through the archives but one thing to be aware of is that with large objects, you have have to dump your database in a different format- a non-text format. This is less portable than storing files as bytea's which can be dumped in the text and non-text formats. Argueably is all you use is PosgreSQL then this might not be a big deal. Performance-wise I'm not qualified to speak to which is "better". As always, it is going to depend on your specific application and environment. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(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] Storing a chain
Quoting Johan Fredrik Øhman <[EMAIL PROTECTED]>: > Hi, > I'm seeking some opinion on the following problem > > For those familiar with "iptables/netfilter", I am actually storing firewall > rules in the database. The order of these rules is critical, so there has to > be some kind of system to how they are ordered. As I see it there are at > least 2 approaches. > > 1) Use a Sequence number in the table. This way it is possible to use ORDER > BY sequencenumber to retrive the correct list. This works, but you have to > do some massive updating when you what to insert a rule between i.e "12" and > "13". > > > CREATE TABLE FW_CHAIN ( > FW_CHAIN_ID INT4 not null, > PARENT_IDINT4 null, > fields .. > constraint PK_FW_CHAIN primary key (FW_CHAIN_ID) > ); > > 2) Use a Parent_Id, as described intended in the table above. I feel that > this is a little prettier, even if it might be somewhat less efficient. > Unfortunately, I'm really not sure how to perform the queries. As I see it > some kind of recursive select statement is needed. Cursors ? Stored > Procedure? > > If somebody has the time, I'd really appreciate some links to relevant > examples, documentation or advice on how to solve this. > > -- > Johan Fredrik Øhman Very interesting idea. I build Linux firewalls with netfilter as well and I never considered storing the rules in a database since as routers I keep them pretty closed. However, if I were to do this, I would use a non-integer field that would contain the rule number (i.e. the place in the chain). When you insert the rule you could simply take the mid-point between the two rules. So for example to insert between rule 10 and 11 you would use 10.5. That would work for some time but would become unreadable if you have a lot of updates happening (i.e. your intrustion detection system automatically writes rules). To handle that, you could cron a job to renumber the rules with whole numbers so again: current rule order: 10.0 11.0 12.0 after 1st update 10.0 10.5 11.0 12.0 after second update 10.0 10.5 11.0 11.5 12.0 after 3rd update 10.0 10.25 10.5 11.0 11.5 12.0 after a renumbering 10 11 12 13 14 15 You'll want to vacuum after that renumbering as well. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PGSQL on shared hosting
Quoting Robert Treat <[EMAIL PROTECTED]>:
> On Fri, 2003-11-21 at 04:41, Holger Klawitter wrote:
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > Am Friday 21 November 2003 09:49 schrieben Sie:
> > > > i am a newbie to the pgsql world, so pls bear with a possibly stupid
> > > > question. i want to test out pgsql but i only have a shared hosting
> > > > account. is it possible to install pgsql without root access, only for
> > > > my
> > > > account? my webhost uses redhat i think.
> > >
> > > No. The "make install" step requires a root access.
> >
> > Wrong! If you install into a writeable directory e.g.
> > ./configure --prefix=/home/user/postgres
> > make install will work just fine (or at least it did with me).
> >
> > The only problem is that you cannot start postgres automatially that easily
> at
> > boot time.
> >
>
> A bit hackey, but you could write a cronjob to check for postmaster.pid
> every few minutes and start postmaster if it does not exist. (assumes
> your provider gives you cron access along with those software compiling
> privileges). this wouldnt give you an at boot time start, but at least a
> shortly after boot time start. you'd need to make that script behave
> properly after system crash too.
>
> Robert Treat
> --
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
An even more basic question is what is the hosters policy on users running their
own servers.
I've got clients that do but only after they are granted permission to do so.
Having clients run they own servers as administratively not a good idea since
thing can very quick get our of control if you don't know what is running.
'Course these days you can tune your kernel and shell parameters to minimize
that. Still, I would ask first to see if maybe they'll install it system-wide
first...
As previously mentioned you could just go with an ISP that has already been
running PostgreSQL for some time. I've notice quite a few service providers on
the list. Some are probably listed on techdocs but I noticed my entry hasn't
shown up yet.
--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com
This email account is being host by:
VCSN, Inc : http://vcsn.com
---(end of broadcast)---
TIP 3: 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
Re: [GENERAL] Feature Request for 7.5
Jan, To continue the brain-dump. I was curious how the GC protocol was going to be implemented (if you had any ideas thus far). Several years ago, I started working on a network security and intrusion detection system for a client where the audit/logging system needed to be redundant- they wanted 3 servers each on a different LANs in fact. The work in that design was centered around making sure that the aggregated data set was exactly the same on each of the 3 servers. Not only were all the event timestamps the same but the events were ordered the same way in the logs. The solution I was working on was a multicast IPv4 (possibly IPv6) network where the "packet" of information had an id of some sort and the event data inside the datagram had a timestamp (of course). The obviously problem is that multicasting is not reliable so in order sure all event were on all servers, there would be a periodic polling that would give a server with say 2 missing event the chance to "catch-up". This catch-up" function make sure all events were ordered an had the same last event. This would be much more of an issue with the server a couple of hops away than with a server on the same LAN. The client never went ahead with the system so I apologize for not having some reference examples. This is totally different from what true replication is amongst a group of database servers but it seems to me that if the servers are in multicast group, at least transactions would be theoretically sent to all servers at the same time. I would think that a homogenous system of servers is already ordering events the same way so transactions would occur properly unless it was missed. A "catch-up" function here was be difficult to implement because if the servers are committing asyncronously then you can't catch-up and one of your datasets has lost integrity. Syncronously (meaning, "we'll all commit now because we all agree on the current list of transactions") seems a bit messy and not as scalable. I didn't mean to get into all that but how the GC is going to work in this project is something that I'm curious about. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com Quoting Jan Wieck <[EMAIL PROTECTED]>: > The following is more or less a brain-dump ... not finally thought out > and not supposed to be considered a proposal at this time. > > The synchronous multi-master solution I have in mind needs a few > currently non existent support features in the backend. One is > non-blocking locks and another one is a callback mechanism just before > marking a transaction in clog as committed. > > It will use reliable group communication (GC) that can guarantee total > order. There is an AFTER trigger on all replicated tables. A daemon > started for every database will create a number of threads/subprocesses. > Each of these workers has his separate DB connection and is a member of > a different group in the GC. The number of these groups determines the > maximum number of concurrent UPDATE-transactions, the cluster can handle. > > At the first call of the trigger inside of a transaction (this is the > first modifying statement), the trigger allocates one of the replication > groups (possibly waiting for one to become free). It now communicates > with one daemon thread on every database in the cluster. The triggers > now send the replication data into this group. It is not necessary to > wait for the other cluster members as long as the GC guarantees FIFO by > sender. > > At the time the transaction commits, it sends a commit message into the > group. This message has another service type level which is total order. > It will wait now for all members in the replication group to reply with > the same. When every member in the group replied, all agreed to commit > and are just before stamping clog. > > Since the service type is total order, the GC guarantees that either all > members get the messages in the same order, or if one cannot get a > message a corresponding LEAVE message will be generated. Also, all the > replication threads will use non-blocking locking. If any of them ever > finds a locked row, it will send an ABORT message into the group, > causing the whole group to roll back. > > This way, either all members of the group reach the "just before > stamping clog" state together and know that everyone got there, or they > will get an abort or leave message from any of their co-workers and roll > back. > > There is a gap between reporting "ready" and really stamping clog in > which a database might crash. This will cause all other cluster members > to go ahead and commit while the crashed DB does not commit. But this is >
Re: [GENERAL] Perl / mod_perl / PostgreSQL was: Good open source mailing list system PHP / Postgresql
Quoting Alvaro Herrera <[EMAIL PROTECTED]>: > On Fri, Dec 05, 2003 at 05:06:34PM +0100, Alvar Freude wrote: > > > - -- Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > > > Someone pointed out on this list some time ago that you can work around > > > the performance issue of starting a Perl interpreter and the compiling > > > phase by using PersistentPerl. > > > > you should use mod_perl, but it is *much* more then "CGI scripting on > > steroids": > > Well, my applications are not web based at all, so mod_perl is not an option > in this case. [sniped] Actually that is not quiet true. You can use Apache as a Perl server. I've never done it before but what I gather from the documentation is that you can have the server run your code. There is a start file I think for all the perl related "stuff" for Apache and in that file you can have a script load. > Though I still don't see why should pick mod_perl over PersistentPerl, if I > were to build a web-app? I have used HTML::Template for, well, HTML > templates; > though it is not exactly pretty, it works as intended. (Smarty templates > for PHP appear to be much better, but I don't like PHP.) You have perl write the template on the fly- no need for anything else really. On the more basic level, you could use put your HTML page in a perl script and replace what you want with variables. On the otherside of the spectrum you can have perl read/send parameters to your users and have pages build dynamically based on that. I generally do this way so I rarely write a complete page of HTML. I just use perl to assemble those pieces based on user input and the required business logic. The EIS is of course PostgreSQL. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Re: How do I list all tables?
I think I might have described this wrong- "\dt" works the same in
version 6 and 7. However "\d *" shows you the detail of **each** table
in the database. So if you two tables called "status" and "tasks", the
output of "\d *" would be:
Table= status
+--+--+---+
| Field | Type|
Length|
+--+--+---+
| statid | int4
| 4 |
| flag | varchar()
|80 |
| color| char()
| 6 |
+--+--+---+
Table= tasks
+--+--+---+
| Field | Type|
Length|
+--+--+---+
| taskid | int4
| 4 |
| jid | int4
| 4 |
| conid| int4
| 4 |
| workdone | varchar()
| 800 |
| date | char()
|10 |
+--+--+---+
if these were the only tables in the database. Since version 7 of pgSQL
doesn't seem to support this, I would like to know what is the
alternative way to list all of the tables AND their field descriptions?
Thanks
cwz wrote:
> use \dt
>
> Note:
> \d{t|i|s|v}list tables/indices/sequences/views
> \d{p|S|l} list permissions/system tables/lobjects
> \dalist aggregates
> \dd [object] list comment for table, type, function, or operator
> \dflist functions
> \dolist operators
> \dTlist data types
>
> Curt
