Re: [GENERAL] Monitoring with pg_controldata
On Wednesday 29 July 2009, Tim Uckun wrote: > > What is the issue here? Some sort of a timezone problem? How do I tell > it what time zone to use? > > Thanks. cron runs programs in a very limited environment. Things like TZ etc. are usually not set. To see what your cron sets, just run a shell script with something like #!/bin/sh env and look at the email you get with the output. Read the cron/crontab manpage. It tells you how to set environment variables for your scripts. HTH UC -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disaster recovery (server died)
On Friday 19 June 2009, Scott Marlowe wrote: > On Fri, Jun 19, 2009 at 8:43 PM, Miguel > > Miranda wrote: > > Well, i just didnt explain in detail, what i have is just the 16897 > > directory where i was storing the database, i tried just copying the > > files but it didnt work, > > should it be posible to import this database is any way? > > Nope, you need the whole data directory. What I don't get is this: you said your CPU died. For me that's the processor or maybe some interpret that as the main board. So why don't you grab the harddisk from that server and plug it into the new one? Maybe something might be corrupt due to the failure, but most of the data should be on the disk (unless you use disks which lie about fsync). Yep - another reason why one has at least a daily backup (in my case 2 replicas for every production server I run. I never had a major failure in over 15 years - knock on wood - but if that happens I don't lose a heck of a lot due to the backups and slony replicas) Uwe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db backup script in gentoo
On Saturday 29 November 2008, Andrus wrote:
> How to create automated backup script in Gentoo which in every night 3:00
> PM backups one database and sends backup
> with ftp using unique file name?
>
> In windows I can use scheduler and script
>
> set FILENAME=%DATE:~8,4%%DATE:~5,2%%DATE:~2,2%MyDbBackup
> pg_dump -Z6 -b -v -f "%FILENAME%.backup" -F c -h localhost -U postgres mydb
> ftp -send -u username -p password "%FILENAME%.backup"
> ftp.backupserver.com
Write a shell script that you store someplace you know (i.e. your database
users home directory)
say you name it: /home/whoever/mybackup.sh
#!/bin/sh
cd /someplace/to/store/backups
pg_dump -Fc -R -O -i -Upostgres mydb > mydb_backup`date +'%Y%m%d'`.sql
[FTP STUFF GOES HERE - see below]
save the file. Do a "chmod 755 /home/whoever/mybackup.sh"
type "crontab -e" being logged in as the user who should run this script
("whoever" in the example above)
In the edior add a line like
0 3 * * * /home/whoever/mybackup.sh
save.
Now you'd just have to figure out the ftp part, because I use rdist to push
the copies to a backup machine.
HTH
Uwe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database access over the Internet...
On Saturday 08 November 2008, Michelle Konzack wrote: > Am 2008-11-08 19:07:35, schrieb Scott Marlowe: > > No, your histrionics aside, it's the way this list works by default, > > and for good reason. If you need it to work differently, there's a > > setting which has been pointed out to you at two times now. Please > > take responsibility for your own life and fix the configuration and > > stop whinging. > > It does not work since the CCs are coming FROM the sender and NOT from > the mailinglist. > > And since most peoples hiting they should delete my address > from the recipients... > > It is very annoying, if I work Outside and must read my WORK E-Mails > with my GSM and haf to walk trough 100 of CCs which are definitively not > important. > > And of course, each CC cost me money... 0.009 Euro/kByte. > Getting per day 1 MByte of CC is annoying. So basically you want everyone here to accomodate your wishes, just because you have a GSM plan that sucks? Get yourself a hotmail or google address and use that to subscribe to this list. Then you can read the responses from a normal computer when you choose to. Actually it's polite and pretty much standard to hit "reply all" and I for one appreciate it when I'm kept on CC, just because originally I was interested in the topic discussed and I might miss the response otherwise. It's really not anyone's responsibility to pay attention to this. If you don't want CC's to your GSM, don't use the GSM's email address to sign up to the list - simple as that. Note: I manually took you off the CC - don't want to be the reason you have to file for bancruptcy. Uwe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Drupal and PostgreSQL - performance issues?
> I have been testing it a bit performance-wise, and the numbers are > worrying. In my test, MySQL (using InnoDB) had a 40% lead in > performance, but I'm unsure whether this is indicative for PostgreSQL > performance in general or perhaps a misconfiguration on my part. In my experience the "numbers are always worrying" in a read-only environment. I've used MySQL, but found it rather disturbing when it comes to integrity. MySQL has just some things I can't live with (i.e. silently ignoring overflowing charater types etc). That aside, MySQL IS fast when it comes to read operations. That's probably because it omits a lot of integrity checks postgres and other standard compliant databases do. I'm running a turbogears website with a couple million pages on postgresql and I don't have any problems, so I guess postgres can be configured to service Drupal just as well. Check your indexes and your work memory (postgresql.conf). You want to have the indexes correct and in my experiene the work memory setting is rather important. You want to have enough work memory for sorted queries to fit the resultset into memory - as always disk access is expensive, so I avoid that by having 2GB memory exclusively for postgres - which allows me to do quite expensive sorts in memory, thus cutting execution time down to a couple milliseconds. Oh, and never forget: explain analyze your queries. That will show you whether your indexes are correct and useful, as well as how things are handled. Once you learn how to read the output of that, you'll be surprised what little change to a query suddenly gives you a performance boost of 500% or more. I had queries take 30 seconds cut down to 80 milliseconds just by setting indexes straight. Keep in mind: postgres will take good care of your data (the most important asset in todays economy). I run all my customers on postgres and did so ever since postgres became postgresql (the times way back then when postgres had it's own query language instead of SQL). With a little care I've never seen postgresql dump or corrupt my data - not a "pull the plug" scenario and not a dumb user SQL injection scenario. I was always able to recover 100% of data (but I always used decent hardware, which IMHO makes a big difference). I've toyed with MySQL (not as deep as postgresql I must admit) and it dumped/corruped my data on more than one occasion. Sure, it can be my proficiency level with MySQL, but personally I doubt that. Postgresql is just rock solid no matter what. Uwe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] A challenge for the SQL gurus out there...
On Sunday 07 September 2008, Gregory Stark wrote: > "Uwe C. Schroeder" <[EMAIL PROTECTED]> writes: > > I want to get a list looking like > > > > forum idthread_id post_id > > 1 6 443 > > 2 9 123 > > 3 3 557 > > ... > > > It all boils down to me not being able to come up with a query that gives > > me the latest post per forum_id. > > In a situation like this I would probably denormalize the tables slightly > by adding a form_id key to the individual posts. That would make it hard to > ever move a thread from one forum to another, though not impossible, but > would help in this case as well as any other time you want to do an > operation on all posts in a forum regardless of thread. > > If you add that column then you could index and get the > result you're looking for instantly with a DISTINCT ON query (which is a > Postgres SQL extension). > > SELECT DISTINCT ON (form_id) >forum_id, thread_id, post_id > FROM thread > ORDER BY forum_id, date DESC > > (actually you would have to make the index on or make > both columns DESC in the query and then re-order them in an outer query) > > Alternatively you could have a trigger on posts which updates a > last_updated field on every thread (and possibly a recent_post_id) then you > could have a query on forums which pulls the most recently updated thread > directly without having to join on form_post at all. That would slow down > inserts but speed up views -- possibly a good trade-off for a forum system. Thanks Gregory. Just to put my final solution on the list: I ended up with a combined approach of what you suggested: I added the forum_id to the posts table and created 2 triggers: one that sets the forum_id in the posts table to the forum_id in the threads table on insert (therefor no change in the application was necessary). The second trigger is to overcome the downside of adding the forum_id to the posts table. On an update to forum_thread.forum_id the trigger updates all posts in that thread to reflect the change in forum_id. That way one can just move the whole thread by changing the forum_id and the posts are moved along by the trigger. Very nice! The query time is now 198ms instead of up to 48seconds !!! Thanks for the idea Uwe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] A challenge for the SQL gurus out there...
or maybe not and I'm just not getting it. So here's the scenario: I have 3 tables forum: with primary key "id" forum_thread: again primary key "id" and a foreign key "forum_id" referencing th primary key of the forum table forum_post: again primary key "id" with a forign key "thread_id" referencing the primary key of the forum_thread table The forum_post table also has a field "date_posted" (timestamp) with an index on it. What I need is an efficient way to create overviews (just think about a forum) I.e. the forum table has 3 records, one for each forum category I want to get a list looking like forum idthread_id post_id 1 6 443 2 9 123 3 3 557 The trick is, that I need the latest post (by the date posted column) for each category (speak forum_id). Due to the keys the forum_thread table has to be involved. I've been thinking about this for hours now, but I just can't come up with a query that will give me 3 records, one for each category showing the latest post. I do have a rather expensive query that involves a lot of sorting, but the forum I'm running has around 4 posts now and the query takes around 4 seconds - which is unacceptable. So there has to be a better way to query this. Currently I'm using a view to assemble a list with the latest post for each forum thread and then I join that view with the forum categories, sort it and limit it. The thing is that the sorting takes waaay to long, simply because I sort a ton of records just to limit them. So my idea was to limit the resultset before sorting takes place, which would probably cut the query execution time to milliseconds instead of seconds and it would deliver predictable results that are not as dependent on number of posts as they are now. The number of posts per thread is usually fairly equal. Even the longest threads won't make it past 1000 posts, so my intention is to sort a maximum of 1000 records instead of 4 (due to the join). It all boils down to me not being able to come up with a query that gives me the latest post per forum_id. So any input would be very much appreciated. Uwe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle and Postgresql
On Sunday 31 August 2008, Christophe wrote: > On Aug 31, 2008, at 7:44 PM, Guy Rouillier wrote: > > CTOs/CIOs like to sleep at night. > > If you buy Oracle, and there's a problem, the conversation with the > CEO is that "Oracle broke." With PG, even if you have exactly the > same level of support, "that database you selected broke." > > The sad reality is that choosing something with a high industry > presence makes it easier to offload blame for failures, especially > when dealing with non-technical management. Sadly I can second that. I've seen quite some companies go with a completely inappropriate product, just because "they're the market leader". And certainly there is the other side of the coin: The CTO who decides doesn't want anyone to rock his boat. So often s/he decides to go with biggest market presence, simply because IF something breaks it's not their fault - the argument being "we went with the best there is, it's their fault". (maybe it's the "best there is", but often not for the application in question). I've got one customer who didn't care - they're running on postgresql for 8 years now and nobody noticed - hehehe. I just told the CEO "it's a SQL database" - which satisfied his curiosity :-) But that's certainly not the case with everyone... Uwe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to copy tables between databases?
On Tuesday 26 February 2008, Kynn Jones wrote: > Is there a simple way to copy a table from one database to another without > generating an intermediate dump file? > TIA! > > Kynn pg_dump -t [table] [database] | psql -U [remoteuser] -h [remotehost] [remotedatabase] comes to mind... You can and maybe have to add more switches to the pg_dump command, but the above is what I'm doing (my local db is set to trust) to copy a table with data to a remote machine Uwe -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(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
Re: [GENERAL] For the SQL gurus out there
Thanks everyone. This was exactly what I needed. I went with connectby as Dante recommended and it works like a charm. UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] For the SQL gurus out there
Ok, something I've been toying around with for a while. Here's the scenario: Imagine a blogging app. I have a table for blogs with a blog_id (primary key) and a table blog_comments also with a comment_id as primary key and a foreign key holding the blog_id the post belongs to. The comments table also has a field that holds a self-reference to comment id for commments on comments (on comments) of a blog. What I would like to do is to create a view that sucks the comments for a given blog_id in the order they should be displayed (very commonly seen in pretty much all blogging apps), i.e. Blog comment 1 comment on comment 1 comment on comment on comment 1 comment 2 etc. Question is, is there a smart way I'm not able to figure out to create a single query on the blog comment table that will return the comments in the right order? Sure I could write a recursive method that assembles the data in correct order, but I'd prefer to leave that to the database to handle in a view. The solution can be very postgresql specific, because I don't intend to run it on any other db server. Any ideas anyone? THX UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Simpler dump?
pg_dump -x -O -s [databasename] > outfile.sql HTH Uwe On Sunday 09 December 2007, Ted Byers wrote: > Is there a way to tell pg_dump to just dump the SQL > statements required to create the tables, sequences, > indeces, keys, &c.? I DON'T need to restore or > recreate things like users, or most other kinds of DB > objects. Just routine DDL statements. Looking > through a dump file for a small database, it looks > like pg_dump is serious overkill, dumping a lot of > stuff I don't need (since I'm just using defaults for > them anyway). > > I am developing a new DB app, to be deployed on a web > based host on the other side of the planet. There is, > at present, no 'data', and the only information to be > transferred consists of the various tables, indeces, > &c. I am creating. > > Obviously, we don't want to put any of my test data on > a server that will in due course be the production > host, when the app goes live (so once my colleagues on > the other side of the planet have had a chance to play > with what I've developed, we'll mostly empty the DB of > test data, except for a small amount of data we've > obtained). I expect that a few tiny csv files I have > here will be ftped to the host and we'd use a simple > script to bulk load that. Another fly in the ointment > is that the hosting company is still using v 8.1.9 and > I am using 8.2.5 on my machine, so I am concerned that > a regular dump and restore may be problematic: it > hasn't worked so far, but then I've spent much of my > time so far wrestling with phppgadmin. :-( > > I'm just looking for something that will save me a > little time. I've created the core of the DB already > on my development machine, using pgAdmin, but I can > recreate it in about a day using Emacs to create a SQL > script that preproduces what I did in pgAdmin. > > Any information would be appreciated. > > Thanks > > Ted > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(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
Re: [GENERAL] CPU
On Monday 03 December 2007, Tom Allison wrote: > is there much of a difference in performance between a XEON, dual > core from intel and a dual core AMD 64 CPU? > > I need a bit of an upgrade and am not sure which, if any, have a > significant advantage for postgres databases. > Personally I've never seen postgresql suck majorly on CPU performance. I guess the biggest speed increase lies in ultra fast I/O, i.e. high spinning disks and battery backed hardware RAID. Databases tend to suck more on I/O than processor unless you do a lot fo sorting, distinct selects etc. Multi or single processor is just a matter of how many clients connect. AFAIK postgresql is not really multi-threaded, but runs each connection (master process) on one processor at a time. So if you have a quad core (or 4 processor machine), you'll have 4 postmasters "processing" any given time - the bottleneck again is I/O because usually all processors share the same ressources (memory and disks). So basically I would invest in fast I/O and would care less about the processors. More memory at hand may also be beneficial. U.C. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Cannot declare record members NOT NULL
I haven't tried it with a view yet - so this may or may not work. But try giving it a shot by declaring a view create view vmovies as select movie_id,movie_text from movies and let your function return setof vmovies Maybe that works - I think it should. Uwe On Wednesday 12 September 2007, Cultural Sublimation wrote: > > Why do you create an extra type for that? > > Just have your method return "movies" > > Hi, > > Thanks for the answer. The simple example obfuscates the fact that in > reality the table has a few extra columns that are omitted from > get_movies_t. Therefore, I cannot return "movies". > > However, your answer did give me an idea: instead of declaring > "get_movies_t" as a record, I declare it as dummy table, and return that > (see code at the end). > This works, though it is *very* ugly. Any other ideas? > > Thanks, > C.S. > > > CREATE TABLE get_movies_t > ( > movie_idint4 NOT NULL, > movie_name text NOT NULL > ); > > CREATE FUNCTION get_movies () > RETURNS SETOF get_movies_t > LANGUAGE sql STABLE > AS > $$ > SELECT movie_id, movie_name FROM movies; > $$; > > > > > > > ___ >_ Shape Yahoo! in your own image. Join our Network Research Panel > today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 > > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Cannot declare record members NOT NULL
Why do you create an extra type for that? Just have your method return "movies" i.e. CREATE FUNCTION get_movies () RETURNS SETOF movies ... ... HTH Uwe On Wednesday 12 September 2007, Cultural Sublimation wrote: > Hi, > > I am not sure if this qualifies as a bug report or a feature request, > but I don't see any way to tell Postgresql that the members of a record > cannot be NULL. This causes all kinds of problems when this record > is used to declare the return type of a function. Suppose I had the > following table: (note that all columns are NOT NULL) > > CREATE TABLE movies > ( > movie_idint4 UNIQUE NOT NULL, > movie_name text NOT NULL, > PRIMARY KEY (movie_id) > ); > > > Suppose also that I didn't want the clients to query the table directly, > but instead they have to go through a function "get_movies" which returned > a record of type "get_movies_t": > > CREATE TYPE get_movies_t AS > ( > movie_idint4, > movie_name text > ); > > > CREATE FUNCTION get_movies () > RETURNS SETOF get_movies_t > LANGUAGE sql STABLE > AS > $$ > SELECT movie_id, movie_name FROM movies; > $$; > > > The problem is that Postgresql tells the client that the function returns > two columns, both of which can be NULL, and this makes a mess on the > client side. Is there anyway I can tell Postgresql that the columns of > get_movies_t are NOT NULL? > > If this is (yet another) defect in the SQL standard, can someone suggest > an alternative that would get around it? > > Thanks for the help! > C.S. > > > > > ___ >_ Catch up on fall's hot new shows on Yahoo! TV. Watch previews, get > listings, and more! http://tv.yahoo.com/collections/3658 > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] CASE in ORDER BY clause
On Saturday 07 July 2007, Lew wrote: > So if your RDBMS sorts NULLs after all other values, then from > > >> select start_date from show_date > >> order by > >> case when start_date > CURRENT_DATE then start_date end desc, > >> case when start_date <= CURRENT_DATE then start_date end asc; > > all rows with start_date > CURRENT_DATE will appear first, in start_date > descending order, > then all rows with start_date <= CURRENT_DATE will appear, in start_date > ascending order. > > Is CURRENT_DATE evaluated once for the query or twice for each row? CURRENT_DATE is evaluated once per transaction. If you run in autocommit - mode, then the single query is wrapped in a transaction by itself. Either way it's never evaluated per occurrence. Uwe -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PHP sucks!! - was: persistent db connections in PHP
On Saturday 16 June 2007, John Smith wrote: > guys, > love both tools but php @ 2.5 *billion* google results is far more > popular than postgresql @ 25 million google results. *if* somebody's > gotto adapt it's not php. php does what it does best in a way that > stuffy academics don't get. Mhhh - what does PHP have to do with Postgresql? Lots of pages just end in .php, which is why the google results are so high - guess what, the tool "html" hits 3.2 billion :-) > > On 6/16/07, PFC <[EMAIL PROTECTED]> wrote: > > "PHP: very loosely typed, does whatever it wants" > > yeah php got a life of its own! sure be a lazy programmer and blame > sql injection etc crap on php or try http://www.xdebug.org/ and > others. > > bottomline- people who know other tools better gripe because their's > isn't as popular. I doubt popularity has anything to do with it. I've used PHP as it emerged back in 1990. I liked it back then because it produced faster results than perl/cgi. The downside was - and IMHO still is - that a lot of application code is embedded in the pages. Ok, nowadays PHP has advanced to a point where you don't really have to do that anymore, but still many people do - resulting in a nice, quickly built but often unmaintainable application. IMHO there are many other web environments that are much better at separating the view from the actual code. In the end, neither of these points has anything to do with postgresql. Personally I'm more inclined to agree with Joshua on this one... Uwe -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] contrib
usually contrib is installed along with postgresql. Look for /usr/lib/pgsql/contrib or /usr/lib/postgresql/contrib and maybe it resides in /usr/share/pgsql/contrib or just type locate contrib Uwe On Thursday 17 May 2007, ABHANG RANE wrote: > Hi, > I have installed postgresql 8.2 on redhat enterprise release WS 4. I > need to use the cube operator which I guess resides in the contrib > modules. Please may I know the link to install the appropricate contrib > module for my linux machine. > > Thanks > Abhang > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SQL Transaction related
Yes it will. Everything INSIDE ONE transaction is visible to that exact transaction. So in your scenario the val1 from the select will see what was inserted - just any other transaction won't unless the current one is committed. Uwe On Wednesday 09 May 2007, Harpreet Dhaliwal wrote: > Hi, > > I have a transaction like following: > > BEGIN > > INSERT INTO tbl_xyz VALUES (val1, val2); > > SELECT INTO wid MAX(val1) FROM tbl_xyz; > > END; > > My question is in the SELECT INTO statement, will I get the value of val1 > from the INSERT INTO in the same transaction > even though the transaction has not ended yet. > I think no. > How would I get that latest value of val1 in the same transaction because > its not committed yet as the transaction has not ended. > > Thanks, > > ~Harpreeet -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Where is contrib?
Thanks Stuart. I somehow overlooked it all the time. In the end I ran updatedb and locate :-) Guess it's time to get some sleep. Uwe On Tuesday 13 March 2007 22:40, Stuart Cooper wrote: > > this may be a very stupid question. I've always used the source rpms for > > new versions. However now I'm faced with a ubuntu server and I never used > > a non-rpm system. > > Since there is no up-to-date postgresql package I grabbed the source for > > 8.1.8 and compiled/installed it. > > So far so good. Now I'm looking for the contrib directory. But it's not > > in the source tarball nor anywhere on ftp.postgresql.org. > > So the question is: where can I find the contrib tree for download? > > It should be where you unpacked your source tarball at > postgresql-8.1.8/contrib > > After running configure, there are instruction in contrib for building > and installing the individually contributed packages. > > Good luck, > Stuart. > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(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
Re: [GENERAL] Where is contrib?
Never mind. Dumb me. Uwe On Tuesday 13 March 2007 22:17, Uwe C. Schroeder wrote: > Hi everyone, > > this may be a very stupid question. I've always used the source rpms for > new versions. However now I'm faced with a ubuntu server and I never used a > non-rpm system. > Since there is no up-to-date postgresql package I grabbed the source for > 8.1.8 and compiled/installed it. > So far so good. Now I'm looking for the contrib directory. But it's not in > the source tarball nor anywhere on ftp.postgresql.org. > So the question is: where can I find the contrib tree for download? > > Thanks > > UC > > -- > Open Source Solutions 4U, LLC 1618 Kelly St > Phone: +1 707 568 3056 Santa Rosa, CA 95401 > Cell: +1 650 302 2405 United States > Fax:+1 707 568 6416 > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Where is contrib?
Hi everyone, this may be a very stupid question. I've always used the source rpms for new versions. However now I'm faced with a ubuntu server and I never used a non-rpm system. Since there is no up-to-date postgresql package I grabbed the source for 8.1.8 and compiled/installed it. So far so good. Now I'm looking for the contrib directory. But it's not in the source tarball nor anywhere on ftp.postgresql.org. So the question is: where can I find the contrib tree for download? Thanks UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Idle in transaction - Explination ..
Well, in very short terms: a "idle" transaction is not committed. This means, when it's a writing transaction, that in the best case you have one or more row locks blocking access to the updated/inserted rows and in the worst case one or more table locks, which will block access to a table completely. On Wednesday 24 January 2007 13:15, Weslee Bilodeau wrote: > Where I work I'm in charge of more then a few PostgreSQL databases. > > I understand why idle in transaction is bad, however I have some > developers who I'm having a real difficult time fully explaining to them > why its bad. > > Oh, and by bad I mean they have transactions that are sitting idle for > 6+ hours at a time. > > Mainly because they don't speak very good English, and my words like > MVCC and VACUUM have them tilting their heads wondering what language > I'm speaking. > > I've tried searching the mailing lists for a good explanation, but > haven't really found one thats easy to translate. > > They are Japanese, but I don't speak Japanese, so finding any resource > in Japanese that explains it is beyond my ability. > > Would anyone happen to have a simple explanation, or a page online thats > written in Japanese that I can pass off that might explain why this is bad? > > Is there a Wiki somewhere that says "101 ways to cause your DBA an > aneurysm" that covers things like this? :) > > > Weslee > > > ---(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 -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(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
Re: [GENERAL] Starting Postgresql
I think you're better off to use the official documentation. http://www.postgresql.org/docs/ (chose the docs for the version you're running on the right side of that page). The docs you're currently referencing are for 7.0 - which is stone-age postgresql. On Wednesday 20 December 2006 11:57, Bob Pawley wrote: > Here's the url http://fusion.gat.com/~osborne/dbdoc/postgres/postmaster.htm > > Bob > > > - Original Message - > From: "Richard Huxton" > To: "Raymond O'Donnell" <[EMAIL PROTECTED]> > Cc: "Postgresql" > Sent: Wednesday, December 20, 2006 11:43 AM > Subject: Re: [GENERAL] Starting Postgresql > > > Raymond O'Donnell wrote: > >> On 20 Dec 2006 at 11:12, Bob Pawley wrote: > >>> which in PostgreSQL is > >> > >> It's not in PostgreSQL - it's the shell of your operating system. In > >> Windows, you get that either by clicking Start -> Run and typing > >> "command" or "cmd" (depending on your version of windows), or by > >> clicking on Start -> Programs -> Accessories -> Command prompt. > > > > But in any case, you probably don't want to start it like that. On > > Windows you probably want to go into the service manager (in > > administrative tools iirc) and on Linux something like > > /etc/init.d/postgresql start - that way you'll get the proper startup > > sequence, setting any environment variables and redirecting logging etc. > > > > By the way - what page were you quoting that intruction from? I don't > > recognise it. I'm particularly puzzled because it referred to "Postgres" > > rather than "PostgreSQL". > > > > -- > > Richard Huxton > > Archonet Ltd > > > > ---(end of broadcast)--- > > TIP 6: explain analyze is your friend > > ---(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 -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Connecting via ssh tunnel
On Monday 27 November 2006 02:38, Weerts, Jan wrote: > Hi all! > > [EMAIL PROTECTED] wrote: > > On Friday 24 November 2006 12:56, ben short wrote: > >> I have a postgresql server setup on a Solaris 10 box. I can > >> connect to the db via psql from the local machine. What I have > >> been trying to do it connect with pgAdmin from my workstation. I > >> have setup the tunnel correctly, local port 5432 and destination > >> localhost:5432. Everytime I try to connect I get the following > >> message. > > > > I guess the tunnel isn't correct. You connect on the same ports - > > seems wrong to me. I'm using a tunnel like this: > > > > ssh -l -L :localhost:5432 > > actually > ssh -l USER -L 5432:localhost:5432 SOMEOTHERHOSTTHANLOCALHOST > > is ok. the "localhost" part is evaluated on the SOMEOTHERHOST > and dns lookup is done there. I have been bitten by this before. > If SOMEOTHERHOST is the same host, this would obviously not > work, since the ssh tunnel and the postgres server cannot > listen on the same port. Thinking about it I agree that this would work, unless you have postgresql running on localhost (which is the case in my installation) Uwe -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Connecting via ssh tunnel
On Friday 24 November 2006 12:56, ben short wrote: > Hi, > > I have a postgresql server setup on a Solaris 10 box. I can connect to > the db via psql from the local machine. What I have been trying to do > it connect with pgAdmin from my workstation. I have setup the tunnel > correctly, local port 5432 and destination localhost:5432. Everytime I > try to connect I get the following message. I guess the tunnel isn't correct. You connect on the same ports - seems wrong to me. I'm using a tunnel like this: ssh -l -L :localhost:5432 and then connect psql on the client machine with psql -h localhost -p -U Uwe -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(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
Re: [GENERAL] running external programs
On Tuesday 07 November 2006 10:55, km wrote: > > > Is it possible in a PLSQL function to call an external program/script > > > residing at /usr/bin and return the result ? > > > > No, because plsql is a trusted language. > > You can't run external commands from such a language. > > Is that a deciding criteria for a language to be flagged trusted or not ? besides other reasons, yes. UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(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
Re: [GENERAL] time value '24:00:00'
On Thursday 02 November 2006 00:59, Richard Huxton wrote: > Uwe C. Schroeder wrote: > >> Ah, times and dates are wonderful things though. For example, '23:59:60' > >> is a valid time (and not equal to 24:00:00 or 00:00:00) every so often. > >> > >> http://en.wikipedia.org/wiki/Leap_second > > > > Yeah, but isn't the third part milliseconds? Doesn't "milli" imply 1000 > > and not 60. I may be totally off here though - well, it's getting late > > Nope - hh:mm:ss.milli > And it's early in London, so it *must* be late in Western U.S.A. - see > you later :-) You're right of course. Seconds! Who would have thought about that :-) It's past 1am, so I guess I should go hit the mattress ... PS: and I heard it's darn cold over there too Uwe -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] time value '24:00:00'
On Thursday 02 November 2006 00:16, Richard Huxton wrote: > Uwe C. Schroeder wrote: > > why don't you just use < '00:00:00'::time > > and avoid the issue? > > > > IMHO there shouldn't even be a 24:00:00, because that would imply that > > there is a 24:00:01 - which there is not. > > It should go from 23:59 to 00:00 > > But then, I didn't write the spec for time in general, so maybe there is > > a 24:00 which is identical to 00:00 > > Ah, times and dates are wonderful things though. For example, '23:59:60' > is a valid time (and not equal to 24:00:00 or 00:00:00) every so often. > > http://en.wikipedia.org/wiki/Leap_second Yeah, but isn't the third part milliseconds? Doesn't "milli" imply 1000 and not 60. I may be totally off here though - well, it's getting late UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(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
Re: [GENERAL] time value '24:00:00'
why don't you just use < '00:00:00'::time and avoid the issue? IMHO there shouldn't even be a 24:00:00, because that would imply that there is a 24:00:01 - which there is not. It should go from 23:59 to 00:00 But then, I didn't write the spec for time in general, so maybe there is a 24:00 which is identical to 00:0= UC On Wednesday 01 November 2006 13:15, [EMAIL PROTECTED] wrote: > hello, > > can the the current time family functions (CURRENT_TIMESTAMP, LOCALTIME, > etc) reach the '24:00:00' value ? > > I want to compare LOCALTIME <= '24:00:00'::TIME and I am curios to know > if LOCALTIME < '24:00:00'::TIME is sufficient. > > > thanks, > razvan radu > > > > ---(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 -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL injection in a ~ or LIKE statement
On Sunday 22 October 2006 12:32, Volkan YAZICI wrote:
> On Oct 20 05:07, [EMAIL PROTECTED] wrote:
> > I'm concerned about whether the usual parameter escaping mechanism is
> > enough in a LIKE or regular expression search.
> >
> > I run a recent Postgres version and use the Python connector psycopg2
> > for a web application. I understand that if I always escape as in
> >
> > dBres=dBcsr.execute('SELECT docText FROM documents WHERE
> > name=%(storyName)s',{'storyName':storyName})
> >
> > then I am doing the right thing.
>
> Please pay attention that [IIRC] psycopg2 uses its own escaping
> mechanism. Therefore, you should better ask this question on psycopg2
> ml.
>
> > I plan to add full text searching also; is the escaping mechanism
> > enough there?
>
> If I were you, I'd ask psycopg2 developers to implement parameters that
> are natively supported by PostgreSQL. With parameters, you won't mess up
> with any escaping or injection related issue.
psycopg2 supports parameters which are escaped properly.
Uwe
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax:+1 707 568 6416
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Log-based repliaction?
For your immediate needs I'd recommend slony. It's a quite reliable replication solution that works quite nicely. It will sync the replica in nearly real-time and you won't have any transactional problems either. Uwe On Friday 20 October 2006 21:22, Tobias Brox wrote: > I would eventually like to: > > 1) find a way to (stress) test a mockup database server. > > 2) fix a near-realtime replication of our production database server > for pulling out statistics and reports. Minor bugs would be > permitable in such a setup. We don't need a full-fledged data > warehouse solution just yet. > > 3) set up a failover solution *) > > Now, why not just get the production database to log all the queries, > and feed them into a replication database? I guess this solution > could be used for archieving any of the three things above. This idea > is very trivial, but I never found any pointers while googling, so I > assume there are some problems with this approach? > > Here is some things I can think of at the moment: > > - logging all queries at the production database will slow it down >considerably (haven't done much testing here) > > - transactional model can easily be broken (postgres can log >transaction starts, commits and rollbacks, and the transactions are >also supposed to be serializable ... so I don't see the issue?) > > - disregarded due to the resource consumption on the replica server. > > - some of the transactions aren't really serializable, or relies on >the exact timestamp for the operation. **) > > - unlike the wal, the log file doesn't get synced for every >transaction, and this will cause delays and potentially data loss. > > ...anything else? > > The simplest thing would be to have one connection open towards the > replica for every connection made towards the production database, run > every query in order, and hope the best - should work good for problem > 1 and problem 2 above. > > Still, maybe better (for 2) to filter out only queries altering the > table and transactions ending with a commit - and do the transactions > one by one, ordered by commit time. Would save quite some CPU cycles > on the replica database compared to the suggestion above. > > I was administring a mysql database quite some years ago ... well, it > was a simple hobby project and we didn't even need transactional > operations. However, we did need point-in-time-recovery. The mysql > way of fixinge this was to write a separate log containing only the > queries involving writing to the database. This log was really nice > to work with, and it could easily be used for replication as well. > > > > *) boss has decided that a failover solution is important and should > be prioritied in 2007 ... even to the point where he's willing to of > throw money at it. If anyone have experiences with failover-solutions > built over a SAN, and can do consultancy services for us, please send > me a private email ;-) > > **) We have had problems with transactions doing "if not exists in > database then insert". Two such transactions can be run synchronously > and result in duplicates. > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Ghost open transaction
do a "ps -ef | grep transact" and look for "idle in transaction" postmaster processes. If you're certain that nobody else is running that transaction (i.e. nobody on the system or the process with the idle transaction has been sitting there for a while and normally the application doesn't have long running transactions) then just kill the process in question (don't kill -9 it!) That will roll the transaction back and close it. Hope that helps Uwe On Friday 20 October 2006 00:04, Naz Gassiep wrote: > I was performing a bunch of INSERTs into a table, users, that has a > unique index on username. During the transaction, my internet connection > dropped. The INSERTs were being done inside a transaction. > > Once I had manhandled my DSL router back online, I went back into a > console to redo the inserts. I found that after I did the first insert, > it appeared to freeze. I thought that my net had dropped out again, but > I was able to Ctrl+C the command and rollback and do it again, with the > same result. The previous connection is obviously still active, locking > the transaction until the fate of the previous insert with that username > is known, i.e., the ghost connection rolls back or commits. > > How do I determine which connection is the ghost connection, and how do > I tell PG to kill it? Also, is it an OS setting to determine the timeout > on open but inactive connections, or a PG setting? > > - Naz. > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance Problem
On Friday 13 October 2006 01:22, Martijn van Oosterhout wrote: > > 1) I have a performance problem as I am trying to insert around 60 > > million rows to a table which is partitioned. So first I copied the > > .csv file which contains data, with COPY command to a temp table > > which was quick. It took only 15 to 20 minutes. Now I am inserting > > data from temp table to original table using insert into org_table > > (select * from temp_table); which is taking more than an hour & is > > still inserting. Is there an easy way to do this? > > Does the table you're inserting into have indexes or foreign keys? > Either of those slow down loading considerably. One commen workaround > is to drop the indexes and constraints, load the data and re-add them. Why do you COPY the data into a temporary table just to do a "insert into org_table (select * from temp_table);" ? Since you're copying ALL records anyways, why don't you just copy the data into the "org_table" directly? Also look for the "autocommit" setting. If autocommit is on, every insert is a transaction on it's own - leading to a lot of overhead. Turning autocommit off and running the inserts in batches of - say 1000 inserts per transaction - will increase speed considerably. UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is it possible to return custom type as proper ROW?
On Wednesday 11 October 2006 10:42, A. Kretschmer wrote: > am Wed, dem 11.10.2006, um 12:56:51 -0400 mailte Tom Lane folgendes: > > Andreas Kretschmer <[EMAIL PROTECTED]> writes: > > > Joe Kramer <[EMAIL PROTECTED]> schrieb: > > >> I want to get: > > >> item_id | last_update > > >> - > > >> 32 | 1234-12-12 12:12:12 > > > > > > Untested: > > > SELECT item_id, last_update from public.new_item(3,2); > > > > Or just > > SELECT * FROM public.new_item(3,2); > > Yes, but i have learned, that 'SELECT * ...' is evil... Well, "SELECT *" is only evil if your application relies on a specific column order to function. The moment you change the table layout and you're using "select *" your application will cease functioning. My app uses tons of select *, but then I wrote an object mapper that queries the information schema at startup - so it's aware of table changes and adjusts accordingly. Uwe -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Varchar concatenate fields as Char or Varchar, not Text
Well, you cast all those fields to be concatenated to text. Why should the db make a varchar out of that? I seriously doubt that 7.x made a varchar of that - but then, 7.2 is very very old. So either cast your fields to varchar (i.e. c.refullname::varchar || d.enname::varchar) or cast the result of the concatenation to a varchar. UC On Monday 07 August 2006 11:20, [EMAIL PROTECTED] wrote: > Last week I upgraded to postgresql 8.1.4 (YEAH!) In my database I have a > view which concatenates three varchar fields to a new field. With > postgresql 7.2.3 the field resulting from the concatenation was a varchar, > with postgresql 8.1.4 the new field is a text field. This is affecting all > kinds of forms in my application. Is there a way I can get the output as > char or varchar? View code is below. > > > CREATE OR REPLACE VIEW vweventsummary AS > SELECT b.ltname, (c.refullname::text || d.enname::text) || f.evname::text > AS evlinkname1, > (g.refullname::text || h.enname::text) || i.evname::text AS evlinkname2, > a.evid, a.evlinktype, a.eventity1, a.evevent1, a.evresource1, a.eventity2, > a.evevent2, a.evresource2 > FROM event a, linktype b, resource c, entity d, event f, resource g, > entity h, event i > WHERE a.evlinktype = b.ltid AND a.evevent1 = f.evid AND > a.evevent2 = i.evid AND a.evresource1 = c.reid > AND a.evresource2 = g.reid AND a.eventity1 = d.enid AND a.eventity2 = > h.enid; > > > *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** > *** *** > Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 > > This e-mail message and any attachment(s) are for the sole use of the > intended recipient(s) and may contain proprietary and/or confidential > information which may be privileged or otherwise protected from > disclosure. Any unauthorized review, use, disclosure or distribution is > prohibited. If you are not the intended recipient(s), please contact the > sender by reply email and destroy the original message and any copies of > the message as well as any attachment(s) to the original message. -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to Backup like in mysql or ms sql server
On Sunday 02 July 2006 01:42, Tino Wildenhain wrote: > Uwe C. Schroeder wrote: > > You can technically just copy & paste the postgresql data directory IF > > YOU SHUT DOWN THE POSTMASTER FIRST! Be aware that this will only work for > > the same version of postgresql. Also: this is not a good way to do it and > > I'd encourage you not to use this as general means of backup (it's ok if > > you want to create a quick clone of an existing database on a second > > machine - provided that the platform and postgresql version on there is > > identical to the source). > > Well, err. thats not completely true with current postgres versions: > > http://www.postgresql.org/docs/current/static/backup-online.html > > ... Ok, you're correct on that one. However I'd rather not encourage someone to mess with WAL and filesystem based backups when s/he hasn't even heard of pg_dump yet, simply because I can already see the next question popping up ... like in "I had a failure and wanted to restore my backup, but everything is messed up now and I can't get it running - help please!" :-) With a standard pg_dump that won't happen, so it's IMHO the safest way to deal with the backup problem for a newbie. On a side-note: that piece of documentation is pretty heavy reading and assumes quite some knowledge about how a DB system like postgresql works internally. For me it's always the least sophisticated approach that solves a given problem. The good old KISS principle applies again :-) Uwe > > >> Is there any way to back-up database like mysql or sql server we just > >> copy and paste. Or maybe there is any tools to copy database when the > >> service is shutdown. > >> > >> Where is postgresql put teh database files? > > Well, thats in the docs ;) (or see above) > > btw, just "copy and paste" w/o preparation is > dangerous with the above databases too. > > Regards > Tino -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to Backup like in mysql or ms sql server
You can technically just copy & paste the postgresql data directory IF YOU SHUT DOWN THE POSTMASTER FIRST! Be aware that this will only work for the same version of postgresql. Also: this is not a good way to do it and I'd encourage you not to use this as general means of backup (it's ok if you want to create a quick clone of an existing database on a second machine - provided that the platform and postgresql version on there is identical to the source). The proper way would be to use pg_dump (a tool that comes along every postgresql installation - see the docs for usage), which will dump the data and structure. There is a second such utility program called pg_restore which will take the dump-file created with pg_dump and restore it to a database of your choosing. Those dumps will be functional between versions and platforms - so that's the way to go. UC On Saturday 01 July 2006 21:01, Joko Siswanto wrote: > Hi all, > > I'm new bie in postgresql. > I use postgresql 8.1 windows version. > How to back-up database in postgresql? Usually i use pgAdmin III by back-up > and restore. > Is there any way to back-up database like mysql or sql server we just copy > and paste. Or maybe there is any tools to copy database when the service is > shutdown. > > Where is postgresql put teh database files? > > Thanks all, > Jokonet -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] OT: job offer
Hope nobody minds a job offer. We have developed an application for the insurance market that is based on: wxPython for the GUI twisted pb as network layer certainly python :-) reportlab/OpenOffice/pyUno for printing zope/plone as alternative web interface. the database backend is postgresql. We're currently in the process of setting up a company to market this software. The software targets a niche market in the insurance business and has been in production for over 3 years with a limited number of clients. We're looking for developers, preferably in the greater San Francisco Bay Area but not a necessity, who have as much experience as possible in any/all of the above technologies. We have certain projects that only need partial knowledge (I'm not disclosing specifics here, I much rather list requirements for the different projects): 1) Solely Plone based development. This basically only requires Plone and postgresql experience. 2) The printing backend needs work. Anyone with good XSLT/RML experience is welcome to apply. Intention is to write a Open Document Format to RML exporter for OpenOffice. We will contribute the exporter back to the community which would benefit the OpenOffice and the reportlab projects. 3) Someone with a solid knowledge of twisted pb (not yet new pb) and wxPython experience. There are technical obstacles to the "twisted/wxPython"-marriage and you should be aware of them. 4) anyone with an open mind and good python experience. SQL database knowledge is pretty much a must for all of the above. We're currently using postgresql, but Oracle and DB2 are not out of the question. If you have commercial insurance knowledge it's even better, but I rather focus on programming skills than insurance knowledge - since the later is much easier to acquire. All of the above projects have the potential to turn into a lucrative permanent occupation. Salary and terms are negotiable. There will be several programmers involved, thus team- and communication skills are a plus. If you're interested please drop me an email. Best UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PSQL Data Type: text vs. varchar(n)
On Thursday 30 March 2006 21:27, Tom Lane wrote: > Chris <[EMAIL PROTECTED]> writes: > > kurt _ wrote: > >> My question: Is a text field just a varchar(Integer.MAX_VALUE)? > > > > varchar has a max of 255 characters, > > You must be using some other database ;-) > > The current Postgres code has a physical limit of 1G bytes for any > column value (and in practice you'll hit the threshold of pain > performance-wise at much less than that). The only real difference > between type "text" and type "varchar(N)" is that you'll incur runtime > overhead checking that values assigned to varchar columns are not any > wider than the specified "N". > > My own take on this is that you should "say what you mean". If you do > not have a clear application-oriented reason for specifying a particular > limit N in varchar(N), you have no business choosing a random value of N > instead. Use text, instead of making up an N. Tom, good point. However, if you design an application that at one point _might_ need to be run on something else than postgres (say oracle or DB2), your're way better off with a varchar than text. UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] distance calculation usng lat and long in miles
There is the earthdistance package in the contrib directory. You may want to
look into that.
On Wednesday 08 March 2006 09:10, [EMAIL PROTECTED] wrote:
> I have the lat and long data. I have created the geom column based on the
> lat and long data as below.
>
>
> UPDATE property SET geom =GeometryFromText('POINT(' || long ||
> ' ' || lat || ')',4326);
>
> Now I have the geom columns in two tables
>
> I am calculating the distance as below
>
> select distance(geom1, geom2)* 69.055
>
>
> It seems to be right. But I want to make sure.
>
> Thanks for help in advance.
>
>
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
--
UC
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax:+1 707 568 6416
---(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
Re: [GENERAL] Wish: remove ancient constructs from Postgres
As long as the SQL standard is supported, support for the "ancient" syntax shouldn't be removed - at least not without a very long period of transition. Do you have any idea how many applications the removal of something simple like the cast operator :: will break? It's not difficult to write standard-compliant code in PostgeSQL - just feel free to use the standard. Nobody forces you to use the uncomliant syntax - but don't try to force thousands of people to rewrite tons of code just because you don't like the old syntax. Oracle has stuff like that, DB2 has too. Things evolve over time and unless you give code-maintainers ample time to fix their applications a sudden drop of old constructs will potentially just harm the project's popularity. I agree with you that the docs and examples should be amended to show standard constructs, just to encourage the adoption of standard compliant statements. Feel free to volunteer some time to make this happen - I'm confident everyone in the community will appreciate it. UC On Sunday 26 February 2006 00:36, Andrus Moor wrote: > It is difficult to write standard-compliant code in Postgres. > There are a lot of constructs which have SQL equivalents but are still used > widely, even in samples in docs! > > For example, there are suggestions using > > now()::CHAR!=foo > > while the correct way is > > CAST(CURRENT_DATE AS CHAR)<>foo > > now() function, :: and != operators should be removed from language. > > I like the Python logic: there is one way > Postgres uses C logic: there are multiple ways. > > Bruce seems to attempt start this process trying implement > escape_string_warning in postgresql.conf . However, this is only very minor > step. Please clean Postgres. > > Andrus. > > > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Same data, different results in Postgres vs. FrontBase
AFAIK NULL is not a value according to SQL spec, so it doesn't match in a "not
in" clause (or any other value comparing clause for that matter, i.e. blabla
>= 10 will not match rows where blabla is null). Therefor I'd say the result
of 30 is correct.
If you want to see null results too you should say so, i.e.
CON.IS_SUBSCRIBED NOT IN ('X', 'P') OR CON.IS_SUBSCRIBED IS NULL
On Saturday 18 February 2006 21:51, Brendan Duddridge wrote:
> Hi,
>
> I have a query that returns 569 rows in FrontBase, but only 30 rows
> in Postgres. The data is the same as I just finished copying my
> entire database over from FrontBase to Postgres.
>
> I've reduced my problem to the following statement and have
> discovered that FrontBase returns null rows along with the rows that
> match the query and PostgreSQL only returns the not null rows.
>
> CON.IS_SUBSCRIBED NOT IN ('X', 'P')
>
> Is that normal? I guess I have to rewrite my queries to handle this
> situation.
>
> Does anyone have any idea why the two database engines might differ
> in this way?
>
> Thanks,
>
>
> Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED]
>
> ClickSpace Interactive Inc.
> Suite L100, 239 - 10th Ave. SE
> Calgary, AB T2G 0V9
>
> http://www.clickspace.com
--
UC
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax:+1 707 568 6416
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Oracle purchases Sleepycat - is this the "other shoe"
On Wednesday 15 February 2006 18:49, Chris wrote:
> > And since MySQL already has got the upperhand in terms of marketing,
> > Oracle would buy MySQL to make it as the low-end alternative. Never mind
> > the lack/immature features in MySQL such as stored proc or trigger.
>
> Mysql 5 has stored procedures and triggers.
>
> The fact that you have to change between different "storage engines" to
> use transactions properly etc is a little weird (and some of the new
> engines are just bizarre), but that's beside the point.
>
> 90% of open-source software is written to use only mysql (and it's not
> easy to switch to another db) - search freshmeat or sourceforge for
> anything postgresql related.. not much there.
>
> Then, even if you do write something to use postgresql a lot of hosts
> don't support it anyway ('mysql is good enough').. so you're stuck.
Well, I guess the moment all the hoster's have to buy commercial licenses for
providing a database they'll switch to PG in no time - or charge more for the
people who absolutely need mysql.
Maybe it's time to write a sophisticated "mysql to postgresql" automation
tool
UC
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax:+1 707 568 6416
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Dropping a database that does not exist
I don't think you have duplicate databases - that would be the first time I heard that postgreSQL supports that. Are you sure you didn't create the database with a name that just doesn't print? I.e. a simple createdb test works. do another createdb "test " (note the space) works too, but when you list the db's you'll see test twice, although the one of them is created with a space at the end, so you can't connect to it or drop it unless you use quotes. Did you use some kind of tool that may have a done this? Most tools use quotes when creating sql statements, so if you accidentially added a space in the dialog you end up with a scenario like you describe. On Friday 10 February 2006 01:42, Tham Shiming wrote: > Hi, > > I've been getting duplicate databases within my server. Dropping one of > them works, but when I try to drop the other, psql will say that the > database does not exist. > > For example: > > db1 > db1 > db2 > db3 > db4 > db4 > db5 > > Running DROP DATABASE db1 for the first time works and I'm left with: > > db1 > db2 > db3 > db4 > db4 > db5 > > Attempting to run DROP DATABASE db1 again will just give me "FATAL: > database "db1" does not exist" > > Same scenario for db4. > > I could ignore the error, but because of the duplicate database, I > cannot make a dump of the server. > > Any one has any ideas why this is happening and how I can solve it? A > similar thing happened previously, but it was with tables within a > database. The only way we solved that was by dropping the database and > recreating the structure, which wasn't the ideal way I wanted to use. > I've got PostgreSQL 8.0.4 running on SuSE 9.3. > > Regards, > Shiming > > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] What's faster?
Depending on your keys neither. Rather let the DB handle the resultset. count(*) is quite slow. How about something like select blablabla from _complex_query order by _key_ (optional DESC or ASC) OFFSET xxx LIMIT 15 where your offset would be a parameter from the php side and is basically the page number of the number of pages you want to display. The only drawback of that is that you will never see the total number of hits. So maybe you do a count(*) ONCE and then use the above query to loop over the resultset - or you don't show the number of pages and just have a "next results" and "previous results" button that adjusts the offset parameter. On Wednesday 08 February 2006 19:45, Silas Justiniano wrote: > Hello all! > > I'm performing a query that returns me hundreds of records... but I > need cut them in pages that have 15 items! (using PHP) > > So, is it faster: > > select blablabal from _complex_query > if (count($result) > 15) show_pages; > show_only_15_rows($result); > > or: > > select count(*) from _complex_query > if ($result1 > 15) show_pages; > select blablabal from _complex_query LIMIT ... (see the LIMIT!) > show $result > > On the first, I can use pg_num_rows instead of count(), too. > > what do you think? > > Thank you! > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Any way to make PG driver obey PrintWarn?
Probably because the notice you see is a notice from the database engine, not from the driver. You can however turn off those notices in postgresql.conf On Monday 06 February 2006 14:30, Tyler MacDonald wrote: > I'm using the DBD::Pg driver and i've specifically turned "PrintWarn" off, > yet I am still getting spammed with messages like this: > > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "aus_flag_pkey" for table "aus_flag" NOTICE: CREATE TABLE / PRIMARY KEY > will create implicit index "aus_password_crypt_pkey" for table > "aus_password_crypt" > > etcetcetc... > > rt.cpan.org doesn't show any active bugs about this. Is this a known issue? > Is it even a bug or am I doing something wrong? Why does Pg feel it > neccessary to tell me that these implicit indexes are being created > anyways? > > Thanks, > Tyler > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PgSQL as part of commercial product
I bet donations to support the project are appreciated. Other than that, the postgreSQL license is BSD - which basically means you can do whatever you want, you just can't sue anyone if it's not working. This has been answered a thousand times, so checking mailing list archives and the FAQ should give you plenty of explanations On Thursday 02 February 2006 16:20, Arun Kannapiran wrote: > Dear Sir/Madam, > > I would appreciate it if you could answer the below queries. > > What are the licencing requirements for PgSQL ? > > The company I work for is building a client-server application with a > PgSQL backend to be sold commercially on the market, are there any > licencing or other payments that need to be made ? > > Thanks, > > Arun Kannapiran > > > > > Do you Yahoo!? > The New Yahoo! Movies: Check out the Latest Trailers, Premiere Photos and > full Actor Database. http://au.movies.yahoo.com > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Allowing Custom Fields
On Friday 27 January 2006 08:25, Aaron Colflesh wrote: > Hello folks, > I've run into a challenge that doesn't appear to have been discussed in > the archives anywhere. > > I'm designing a database that users need to have the ability to > customize some. They just need the ability to add extra fields to an > existing table (oh and they can't touch the predefined fields). So the > database schema so far is table A (existing table), table B (contains a > list of custom field names and other meta data) and table C > (intersection table between A & B containing the values for the custom > fields for each row). That works really well and all but we have > problems with retrieving the data. Due to other requirements related to > reporting we need to be able to present the data in table A along with > any custom fields in a table as if the custom fields were actually > fields on A. I only know of two ways of doing this, and I'm hoping one > of you knows of a third way (I've tried to use a function to do it but > it just doesn't seem to work). You could have the two tables linked with a key, say table A ( custom_key int ) table B (custom_key int) <- and this custom_key references A I'd probably go for a view that is recreated by a trigger on table B. Second alternative would be to just use a join on the tables. I don't know what kind of reporting software you use, but I don't know any that can't do a join on two tables. The worst case scenario would look like SELECT a.*,b.* FROM a JOIN b ON b.custom_key=a.custom_key that will give you one result set. There is a third option. If you know the maximum number of custom columns and possibly their data type, you could add those columns statically, like in table B (custom_key int, cust_field_1 int, cust_field_2 int, ) and then use a third table to label the custom fields, aka table C (cfield1_label varchar(80), cfield2 varchar(80) ) Your application then can grab the label for the field dynamically and the fields in table B wouldn't have to change at all. > > 1. Build the virtual table outside the database in application code > 2. Use triggers on table B to actually create and remove custom fields > on A as they are inserted/removed from B. > > #2 would seem to be the simplest except I'm really not too keen on the > idea of manipulating a table like that on the fly (even though I did > proof of concept it and it seems to be simple enough to be fairly safe > if adequate checks for entries on table B are put into the system). Does > anyone know of a 3rd way of doing it? It seems like this shouldn't be an > all that uncommon task, so I'm hoping there is some slick way of maybe > putting together a function or view to return data rows with a flexible > field layout. So far all the in-db tricks I've come up with have > required me to know what the field names were to generate the final > query anyway, so they don't really gain me anything. > > Thanks, > Aaron C. > > ---(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 -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(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
Re: [GENERAL] Create/Erase 5000 Tables in PostGRE SQL in execution Time
I don't really know what you're trying to accomplish here, but dropping and creating thousands of tables is never a good idea with any database system. You can certainly do that, just don't expect any query to run at their best performance. You'd need to at least do a vacuum before starting to query those tables. Can't you just leave the tables alone and populate them with records? Looks like a bad design to me when you have to drop/create tables as part of the regular operations. On Monday 16 January 2006 09:10, Orlando Giovanny Solarte Delgado wrote: > I am designing a system that it takes information of several databases > distributed in Interbase (RDBMS). It is a system web and each user can to > do out near 50 consultations for session. I can have simultaneously around > 100 users. Therefore I can have 5000 consultations simultaneously. Each > consultation goes join to a space component in Postgis, therefore I need to > store each consultation in PostgreSQL to be able to use all the capacity of > PostGIS. The question is if for each consultation in execution time build > a table in PostGRESQL I use it and then I erase it. Is a system efficient > this way? Is it possible to have 5000 tables in PostGRESQL? How much > performance? > > Thanks for your help! > > > > Orlando Giovanny Solarte Delgado > > Ingeniero en Electrónica y Telecomunicaciones > > Universidad del Cauca, Popayan. Colombia. > > E-mail Aux: [EMAIL PROTECTED] -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] (Select *) vs. (Select id) from table.
On Saturday 07 January 2006 13:50, Michael Trausch wrote: > Mike wrote: > > Hi, > > > > I am trying to make a website where scalability matters. In my quest to > > make my website more scalable I broke down the following SQL statement: > > > > select * from customers limit 100 > > > > to: > > > > select id, updated_date from customers limit 100 > > > > Then my application would check it's cache to see if it has those > > records available and will hit the database with consequent: > > > > select * from customers where id = 4 or id = 9 or id = 19 > > > > Am I really speeding things up by breaking down the SQL statements to > > what's necessary? or is it faster to get everything right at once! > > Well, first, it's never really a good idea to use "SELECT * FROM" in a > production application, against a table. Tables can (and do) change > from one release to another, and if the layout of the table changes, you > could be looking at having to rewrite your code, especially if it relied > on the order of the columns in the tables. It's always better to > specify the columns that you're looking for, since existing columns > should (at least in theory on a production DB) remain present, though > their order can change sometimes, depending on what the DBA does. :) > > Secondly, as far as making your queries more efficient, the only way > that you can really do that is to determine actually how long the > queries are taking. This is relative to the size of the database in > rows, and of course, the data that you're querying against, whether a > table scan is necessary, and all of that. This is the process of > optimizing queries. For a small table, it can be faster sometimes to > just pull all of the records at once (for example, if they're all within > a single page). However, if you're pulling from a large DB, it will be > faster to use smaller queries against it, using well-placed indexes. > > Also, you may wish to consider using views if you really like using * > with SELECT... Since this way, you can just depreciate a view and start > using a new one if the underlying columns are changed. :) Also, if you > use views, you can optimize the view's query when it comes time to > change it, which mess less messing around in the application code, > especially if it is a frequently used query. > > HTH, > Mike > If your application uses a kind of mapping algorithm that is based on the information schema you can use select * without a risk. I'd say a select * is slightly slower than a column name based select, simply because you transfer more information to the application. The time the DB needs to find the record should be the same since the record has to be found before the resultset is assembled. I.e. my application uses an object-relational mapper and returns an object to the application. The object is assembled using the information schema and the columns asked for - if any are asked for specifically. Therefor my app does a lot of select *'s, but it won't break if I add, change or delete columns. It doesn't even break if I rename a column. If you don't use something like that I'd go with the "better" approach of selecting by column name. The only time when this is considerably slower is while you program the queries (more typing :-) ). Views are the way to go when you know there's going to be changes. Views allow you to define the "API" your application relies on. Rules allow you to make the view writeable, so you can effectively hide the layout of your database which is allways a good idea. UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Problem creating stored procedure
Try
CREATE FUNCTION .
On Tuesday 27 December 2005 09:41, Ted Byers wrote:
> I am puzzled. Can ayone explain why I get an error from Postgres on this
> simple stored procedure?
>
> The following is from the pgAdmin III History window:
> -- Executing query:
> CREATE PROCEDURE addEntity (
> fn IN VARCHAR,
> ln IN VARCHAR,
> ivar IN VARCHAR,
> hi IN VARCHAR,
> pw IN VARCHAR,
> ea IN VARCHAR,
> ad IN VARCHAR,
> c IN VARCHAR,
> p IN VARCHAR,
> co IN VARCHAR,
> pc IN VARCHAR
> )
> AS
> DECLARE
> varID INTEGER
> BEGIN
> SELECT int varID uid from uids where email_address=ea;
> IF varID IS NOT NULL THEN
> INSERT INTO addy (uid,address,city,province,country,postal_code)
>VALUES (varID,ad,c,p,co,pc)
> ELSE
> INSERT INTO uids(family_name,first_name,initials,hid,pword,email_address)
>VALUES (ln,fn,ivar,hi,pw,ea)
> INSERT INTO addys(...) VALUES (currval('seq'),ad,c,p,co,pc)
> END IF;
> END
> LANGUAGE 'sql' VOLATILE;
>
> ERROR: syntax error at or near "PROCEDURE" at character 8
>
> Judging from the examples in the manual (around page 600), my procedure
> ought to be fine, but clearly Postgres doesn't like it.
>
>
> Thanks,
>
> Ted
>
>
> R.E. (Ted) Byers, Ph.D., Ed.D.
> R & D Decision Support Software
> http://www.randddecisionsupportsolutions.com/
--
UC
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax:+1 707 568 6416
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.1, OID's and plpgsql
the ctid seems to be the solution to my problem. I'm inserting the record in a transaction so the ctid shouldn't change while the transaction isn't finished (either rolled back or committed). One question though. How would I get the ctid of the just inserted record. GET DIAGNOSTICS only handles row count and oid per the docs. THX UC On Friday 02 December 2005 15:58, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > Maybe the docs should be changed to just say that you should never reuse > > a ctid outside of the transaction you obtained the ctid in? > > That's not a sufficient rule either: someone else could still delete or > update the row while your transaction runs. You'd really have to SELECT > FOR UPDATE or FOR SHARE to be sure the ctid remains stable. (Of course, > this isn't an issue for the case of a row you just inserted yourself, > since no one else can see it yet to change it.) > > The paragraph defining ctid is not the place for a discussion of how it > could be used ... I'm not quite sure where is, though. > > regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.1, OID's and plpgsql
On Thursday 01 December 2005 10:24, Jaime Casanova wrote: > On 12/1/05, Uwe C. Schroeder <[EMAIL PROTECTED]> wrote: > > Hi everyone, > > > > in 8.1 by default tables have no OID's anymore. Since OID's are 4 byte > > it's probably a good idea to discourage the use of them (they produced a > > lot of trouble in the past anyways, particularly with backup/restores > > etc) > > > > Now there's the issue with stored procs. A usual construct would be to > > ... > > ... > > INSERT xx; > > GET DIAGNOSTICS lastoid=RESULT_OID; > > SELECT oid=lastoid; > > > > > > > > Is there anything one could sanely replace this construct with? > > I personally don't think that using the full primary key is really a good > > option. Say you have a 3 column primary key - one being a "serial", the > > others for example being timestamps, one of them generated with "default" > > options. In order to retrieve the record I just inserted (where I don't > > know the "serial" value or the timestamp) I'd have to > > > > 1) store the "nextval" of the sequence into a variable > > 2) generate the timestamp and store it to a variable > > 3) generate the full insert statement and retain the other values of the > > primary key > > 4) issue a select to get the record. > > > > Personally I think this adds unneccessary overhead. IMHO this diminishes > > the use of defaults and sequences unless there is some easier way to > > retrieve the last record. I must be missing something here - am I ? > > > >UC > > If you are using a SERIAL in your PK, why you need the other two > fields? The serial will undoubtly identify a record? > > you just retrieve the current value you inserted with currval > No it doesn't. the serial identifies the record, the timestamp identifies the version/time-validity of the record. If a primary key needs to be something as simple as a serial then we could just keep the OID's as well and pump them up to 32 bytes. curval() doesn't do it, since that will only identify a group of records since my PK is not just a simple int4. sample: create table xxx ( id serial, field varchar, ... ... valid_from timestamptz ) PK is id,valid_from There may be several records with the same id but different valid_from dates. I'm storing a full timestamp, but the application only uses the date part - the timestamp is just to correct for timezones. >From the application logic a record is considered valid until a record with a newer valid_from is found. From that point on the records are referenced depending on several legal factors (this is commercial insurance, lots of lawyers and state/fed regulations) I guess I either stick to the OID's which work fine, or I just have to store the whole PK in variables and forget about defaults. Why not have something like the rowid in oracle? UC ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 8.1, OID's and plpgsql
Hi everyone, in 8.1 by default tables have no OID's anymore. Since OID's are 4 byte it's probably a good idea to discourage the use of them (they produced a lot of trouble in the past anyways, particularly with backup/restores etc) Now there's the issue with stored procs. A usual construct would be to ... ... INSERT xx; GET DIAGNOSTICS lastoid=RESULT_OID; SELECT oid=lastoid; Is there anything one could sanely replace this construct with? I personally don't think that using the full primary key is really a good option. Say you have a 3 column primary key - one being a "serial", the others for example being timestamps, one of them generated with "default" options. In order to retrieve the record I just inserted (where I don't know the "serial" value or the timestamp) I'd have to 1) store the "nextval" of the sequence into a variable 2) generate the timestamp and store it to a variable 3) generate the full insert statement and retain the other values of the primary key 4) issue a select to get the record. Personally I think this adds unneccessary overhead. IMHO this diminishes the use of defaults and sequences unless there is some easier way to retrieve the last record. I must be missing something here - am I ? UC ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Question
How about reading the docs? http://www.postgresql.org/docs/8.1/interactive/server-programming.html is the chapter about stored procs On Tuesday 29 November 2005 09:12, Brandon E Hofmann wrote: > Does PostgreSQL include Stored Procedures supporting the CREATE PROCEDURE > syntax. I notice pgAdmin III has a procedure section in addition to > functions. I thought PostgreSQL implemented Stored Procedures before MySQL > 5.0. > > I have a Sybase implementation with Stored Procedures that I want to > convert to PostgreSQL. Do you have an example you can send me that shows a > Stored Procedure returning a result set that can be displayed by Java? > > Any help you could provide would be greatly appreciated. > > Thanks, > > Brandon > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Where
one full row - NOT two or more rows.
On Thursday 10 November 2005 17:23, Bob Pawley wrote:
> By 'one record' do you mean one full row or one column of one row??
>
> Bob
>
> - Original Message -
> From: "Uwe C. Schroeder" <[EMAIL PROTECTED]>
> To: "Bob Pawley" <[EMAIL PROTECTED]>
> Cc:
> Sent: Thursday, November 10, 2005 5:05 PM
> Subject: Re: [GENERAL] Where
>
> > This will work if you can guarantee that it's only one record
> >
> > INSERT INTO pipe (fluid_id,contain) SELECT (fluid_id,contain) FROM
> > process WHERE contain='ip'
> >
> > otherwise (more than one record) you have to loop over the resultset,
> > something like (off the top of my head)
> >
> > create or replace function base() returns trigger as $$
> > DECLARE
> > myrow RECORD;
> > BEGIN
> >insert into pipe (fluid_id) values (new.fluid_id);
> >for myrow in select * from process where contain = 'ip' loop
> > insert into pipe(fluid_id,contain) values
> > (row.fluid_id,row.contain);
> > if not found then
> >raise exception 'error creating record';
> >end if;
> >end loop;
> >return NULL;
> > END;
> >
> > On Thursday 10 November 2005 15:56, Bob Pawley wrote:
> >> Thank you - what is the correct command???
> >>
> >> Bob
> >>
> >> - Original Message -
> >> From: "Uwe C. Schroeder" <[EMAIL PROTECTED]>
> >> To:
> >> Cc: "Bob Pawley" <[EMAIL PROTECTED]>
> >> Sent: Thursday, November 10, 2005 3:34 PM
> >> Subject: Re: [GENERAL] Where
> >>
> >> > SELECT INTO
> >> > tries to create table
> >> > See:
> >> > http://www.postgresql.org/docs/8.0/interactive/sql-selectinto.html
> >> >
> >> > Why do you do the select into anyways? It does nothing.
> >> > If you try to update table pipe with the select result you have the
> >> > wrong
> >> > command.
> >> >
> >> > UC
> >> >
> >> > On Thursday 10 November 2005 14:24, Bob Pawley wrote:
> >> >> I am attempting to transfer the data in the fluid_id column of table
> >> >> process into column fluid_id of table pipe.
> >> >>
> >> >> This should happen only when column contain of table process holds
> >> >> the value 'ip'.
> >> >>
> >> >> Here is the command that I am having trouble with.
> >> >> ---
> >> >> create table process (fluid_id integer primary key, process varchar,
> >> >> contain varchar);
> >> >>
> >> >> create table pipe ( fluid_id integer not null, contain varchar);
> >> >>
> >> >>
> >> >>
> >> >> create or replace function base() returns trigger as $$
> >> >>
> >> >> begin
> >> >>
> >> >>
> >> >>
> >> >> insert into pipe (fluid_id) values (new.fluid_id);
> >> >>
> >> >> select * into pipe from process where contain = 'ip';
> >> >>
> >> >>
> >> >>
> >> >> return null;
> >> >>
> >> >>
> >> >>
> >> >> end;
> >> >>
> >> >> $$ language plpgsql;
> >> >>
> >> >>
> >> >>
> >> >> create trigger trig1 after insert on process
> >> >>
> >> >>
> >> >>
> >> >> for each row execute procedure base();
> >> >>
> >> >>
> >> >>
> >> >> insert into process (fluid_id, process, contain)
> >> >>
> >> >> values ('2', 'water', 'ip');
> >> >>
> >> >> ---
> >> >> On inserting data this error comes back -
> >> >> ---
> >> >> ERROR: relation "pipe" already exists
> >> >> CONTEXT: SQL statement "SELECT * INTO pipe from process where
> >> >> contain
> >> >> =
> >> >> 'ip'" PL/pgSQL function "base" line 4 at SQL statement
> >> >> --
> >> >> Of course the table pipe does already exist - it is a permanent
> >> >> table.
> >> >>
> >> >> Is the program looking for some other target?? Perhaps a temporary
> >> >> table??
> >> >>
> >> >>
> >> >>
> >> >> Or am I completely screwed up???
> >> >>
> >> >>
> >> >>
> >> >> Bob
> >> >
> >> > --
> >> > UC
> >> >
> >> > --
> >> > Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> >> > Phone: +1 650 872 2425 San Bruno, CA 94066
> >> > Cell: +1 650 302 2405 United States
> >> > Fax:+1 650 872 2417
> >
> > --
> > UC
> >
> > --
> > Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> > Phone: +1 650 872 2425 San Bruno, CA 94066
> > Cell: +1 650 302 2405 United States
> > Fax:+1 650 872 2417
> >
> > ---(end of broadcast)---
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
--
UC
--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax:+1 650 872 2417
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Where
SELECT INTO
tries to create table
See: http://www.postgresql.org/docs/8.0/interactive/sql-selectinto.html
Why do you do the select into anyways? It does nothing.
If you try to update table pipe with the select result you have the wrong
command.
UC
On Thursday 10 November 2005 14:24, Bob Pawley wrote:
> I am attempting to transfer the data in the fluid_id column of table
> process into column fluid_id of table pipe.
>
> This should happen only when column contain of table process holds the
> value 'ip'.
>
> Here is the command that I am having trouble with.
> ---
> create table process (fluid_id integer primary key, process varchar,
> contain varchar);
>
> create table pipe ( fluid_id integer not null, contain varchar);
>
>
>
> create or replace function base() returns trigger as $$
>
> begin
>
>
>
> insert into pipe (fluid_id) values (new.fluid_id);
>
> select * into pipe from process where contain = 'ip';
>
>
>
> return null;
>
>
>
> end;
>
> $$ language plpgsql;
>
>
>
> create trigger trig1 after insert on process
>
>
>
> for each row execute procedure base();
>
>
>
> insert into process (fluid_id, process, contain)
>
> values ('2', 'water', 'ip');
>
> ---
> On inserting data this error comes back -
> ---
> ERROR: relation "pipe" already exists
> CONTEXT: SQL statement "SELECT * INTO pipe from process where contain =
> 'ip'" PL/pgSQL function "base" line 4 at SQL statement
> --
> Of course the table pipe does already exist - it is a permanent table.
>
> Is the program looking for some other target?? Perhaps a temporary table??
>
>
>
> Or am I completely screwed up???
>
>
>
> Bob
--
UC
--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax:+1 650 872 2417
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Where
This will work if you can guarantee that it's only one record
INSERT INTO pipe (fluid_id,contain) SELECT (fluid_id,contain) FROM process
WHERE contain='ip'
otherwise (more than one record) you have to loop over the resultset,
something like (off the top of my head)
create or replace function base() returns trigger as $$
DECLARE
myrow RECORD;
BEGIN
insert into pipe (fluid_id) values (new.fluid_id);
for myrow in select * from process where contain = 'ip' loop
insert into pipe(fluid_id,contain) values (row.fluid_id,row.contain);
if not found then
raise exception 'error creating record';
end if;
end loop;
return NULL;
END;
On Thursday 10 November 2005 15:56, Bob Pawley wrote:
> Thank you - what is the correct command???
>
> Bob
>
> - Original Message -
> From: "Uwe C. Schroeder" <[EMAIL PROTECTED]>
> To:
> Cc: "Bob Pawley" <[EMAIL PROTECTED]>
> Sent: Thursday, November 10, 2005 3:34 PM
> Subject: Re: [GENERAL] Where
>
> > SELECT INTO
> > tries to create table
> > See: http://www.postgresql.org/docs/8.0/interactive/sql-selectinto.html
> >
> > Why do you do the select into anyways? It does nothing.
> > If you try to update table pipe with the select result you have the wrong
> > command.
> >
> > UC
> >
> > On Thursday 10 November 2005 14:24, Bob Pawley wrote:
> >> I am attempting to transfer the data in the fluid_id column of table
> >> process into column fluid_id of table pipe.
> >>
> >> This should happen only when column contain of table process holds the
> >> value 'ip'.
> >>
> >> Here is the command that I am having trouble with.
> >> ---
> >> create table process (fluid_id integer primary key, process varchar,
> >> contain varchar);
> >>
> >> create table pipe ( fluid_id integer not null, contain varchar);
> >>
> >>
> >>
> >> create or replace function base() returns trigger as $$
> >>
> >> begin
> >>
> >>
> >>
> >> insert into pipe (fluid_id) values (new.fluid_id);
> >>
> >> select * into pipe from process where contain = 'ip';
> >>
> >>
> >>
> >> return null;
> >>
> >>
> >>
> >> end;
> >>
> >> $$ language plpgsql;
> >>
> >>
> >>
> >> create trigger trig1 after insert on process
> >>
> >>
> >>
> >> for each row execute procedure base();
> >>
> >>
> >>
> >> insert into process (fluid_id, process, contain)
> >>
> >> values ('2', 'water', 'ip');
> >>
> >> ---
> >> On inserting data this error comes back -
> >> ---
> >> ERROR: relation "pipe" already exists
> >> CONTEXT: SQL statement "SELECT * INTO pipe from process where contain
> >> =
> >> 'ip'" PL/pgSQL function "base" line 4 at SQL statement
> >> --
> >> Of course the table pipe does already exist - it is a permanent table.
> >>
> >> Is the program looking for some other target?? Perhaps a temporary
> >> table??
> >>
> >>
> >>
> >> Or am I completely screwed up???
> >>
> >>
> >>
> >> Bob
> >
> > --
> > UC
> >
> > --
> > Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> > Phone: +1 650 872 2425 San Bruno, CA 94066
> > Cell: +1 650 302 2405 United States
> > Fax:+1 650 872 2417
--
UC
--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax:+1 650 872 2417
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] brain-teaser with CONSTRAINT - any SQL experts?
how about using 2 tables with according unique/primary key constraints and a view to actually access the data (mixing the 2 tables into one) ? On Saturday 08 October 2005 22:36, Miles Keaton wrote: > I'm stuck on a brain-teaser with CONSTRAINT: > > Imagine a table like "lineitems" in a bookstore - where you don't need > an ISBN to be unique because a book will be in buying history more > than once. > > But you DO need to make sure that the ISBN number is ONLY matched to > one book name - NOT to more than one book name. > > This is OK: > isbnname > 1234Red Roses > 1234Red Roses > > This is OK: (two books can have the same name) > isbnname > 1234Red Roses > Red Roses > > This is NOT OK: (an isbn must be tied to one book only!) > isbnname > 1234Red Roses > 1234Green Glasses > > > I know it's tempting to say, "just link a separate table for the book > and don't store the book name" but let's just pretend that's not an > option - because I'm not actually dealing with books : I just made up > this simplified version of something at work, where we can't change > the table : both isbn and name MUST be in the table, and what I'm > trying to do is put a CONSTRAINT on the table definition to protect > against user error, by making sure that any entered isbn is only tied > to one book-name in that table. > > Thoughts? > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- UC -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Oracle buys Innobase
On Saturday 08 October 2005 21:07, Chris Browne wrote:
> [EMAIL PROTECTED] ("Uwe C. Schroeder") writes:
> > Didn't MySQL AB acquire SAPdb (which was Adabas D before)? AFAIK
> > (and you're welcome to correct me since I might very well be wrong)
> > SAPdb supports transactions and foreign keys. If that's the case
> > MySQL AB might be in a position to offer the bells and whistles
> > without InnoDB support if they work out the deficiencies of SAPdb.
>
> They did that indeed, or at least they acquired a license to SAP-DB.
> (I think SAP AG retains license as well; this is akin to the way USL
> sold SysV licenses to many vendors...)
>
> The problems with Max-DB are twofold:
>
> 1. It isn't at all compatible with the "legacy" MySQL applications.
>
> It is essentially a database system with a similar "flavour" to
> Oracle version 7. That's not much similar to MySQL 3.x or 4.x.
>
> 2. The code base was pretty old, pretty creaky, and has a *really*
> heavy learning curve.
>
> It was pretty famous as being *really* difficult to build; throw
> together such things as:
> - It uses a custom set of build tools that were created for a
> mainframe environment and sorta hacked into Python
> - Naming conventions for files, variables, and functions combine
> pseudo-German with an affinity for 8 character names that are
> anything but mnemonic. (Think: "Germans developing on MVS.")
> - I seem to recall there being a Pascal translator to transform
> some of the code into C++...
WOW - careful now. I'm german - but then, there's a reason why I immigrated to
the US :-)
>
> Doing substantial revisions to it seems unlikely. Doing terribly
> much more than trying to keep it able to compile on a few
> platforms of interest seems unlikely.
>
> When they announced at OSCON that MySQL 5.0 would have all of the
> features essential to support SAP R/3, that fit the best theories
> available as to why they took on "MaxDB", namely to figure out the
> minimal set of additions needed to get MySQL to be able to host R/3.
>
> If that be the case, then Oracle just took about the minimal action
> necessary to take the wind out of their sails :-).
SAPdb (aka Adabas D) is something I worked with quite a while ago. And you're
right, the naming schemes and restrictions, as well as severe
incompatibilities with the SQL standard where one of my major reasons to drop
that database in favor of Informix (at that time) and PostgreSQL later on.
It was kind of tough to generate explanatory table names with those kind of
limitations. Nonetheless back then (maybe around 1993) Adabas D was a quite
powerful and considerably cheap alternative to anything serious at the market
- and it was easy to sell to customers (back in germany) just because this
was THE database powering SAP R/3.
But you may be right - considering what the codebase of SAPdb must look like
it's probably unlikely MySQL AB can make any considerable improvements in the
time available.
UC
--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax:+1 650 872 2417
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Oracle buys Innobase
On Saturday 08 October 2005 17:35, Chris Browne wrote: > [EMAIL PROTECTED] writes: > > On Sat, Oct 08, 2005 at 10:31:30AM -0500, Scott Marlowe wrote: > >> What it comes down to is this. MySQL is dual licensed. You can use > >> the GPL version, or the commercial version. In order to sell the > >> commercially licensed version, MySQL must have the rights to all the > >> code in their base. So, in order for MySQL to sell a commercail > >> version of MySQL with innodb support, they have to pay innobase a > >> bit to include it, or rip it out. > > > > I don't understand. If both MySQL and Innodb are GPL licensed, > > commercial or not should make no difference, and they can add all the > > GPL changes they want o the last Innodb GPL release. > > > > What am I missing? > > If they do not hold a fairly unrestricted license to *resell* InnoDB, > then MySQL AB would be unable to sell "traditional proprietary > commercial licenses" to the combination of MySQL and InnoDB, which is > the way that they actually _make money_. > > Based on the comments in Oracle's press release, it appears that MySQL > AB *does* have some form of contract with InnoDB Oy Inc to resell > InnoDB, but that contract expires some time next year. > > If the contract is not renewed, then MySQL AB would only be permitted > to link MySQL (tm) to InnoDB under the conditions of the GPL, which > would mean that MySQL AB could only distribute a MySQL(tm)/InnoDB(tm) > combination under the conditions of the GPL. > > This would essentially *destroy* their revenue model, which is > predicated on the notion of selling people a "traditional proprietary > license" to MySQL+InnoDB on the basis that they should be fearful of > GPL-licensed software as it always forces you to release your code > "for free." (There's some truth to this, but possibly not as much as > MySQL AB would have you believe.) Didn't MySQL AB acquire SAPdb (which was Adabas D before)? AFAIK (and you're welcome to correct me since I might very well be wrong) SAPdb supports transactions and foreign keys. If that's the case MySQL AB might be in a position to offer the bells and whistles without InnoDB support if they work out the deficiencies of SAPdb. -- UC -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Securing Postgres
On Wednesday 05 October 2005 07:37, L van der Walt wrote: > Berend Tober wrote: > > L van der Walt wrote: > >> I would like to secure Postgres completly. > >> > >> Some issues that I don't know you to fix: > >> 1. User postgres can use psql (...) to do anything. > >> 2. User root can su to postgres and thus do anything. > >> 3. Disable all tools like pg_dump > >> > >> How do I secure a database if I don't trust the administrators. > >> The administrator will not break the db but they may not view > >> any information in the databse. > > > > It may be just me and my silly old-fashion attitudes, but I kind of > > think that if your sys admin(s) cannot be trusted, you are pretty much > > screwed. And your hiring process needs fixing, > > > > But being that as it may, maintaining physical security, i.e., keeping > > the host server in a locked room with restricted and recorded access > > and that requires at least two persons present so that collusion is > > required for tampering, disabling remote root login, granting limited > > sys admin privileges with sudo (which records the sudoer activities, > > for auditing purposes) might be a way to accomplish what you are > > looking for. > > Then, I might as well just leave the whole PostgreSQL DB and write my > own mini DB with encrypted XML files. I am sure someone must have an > answer for me. As long as I have the console OR root access you can write whatever you want - it's just a matter of time to read the data. That's true for Windows, Unix, Mac - basically any computer - maybe except the 2 or 3 in the pentagon that use biometric sensors to figure out who wants to fire the nukes. If you can't trust the sysadmin at your customer, the employees can't be trusted either. So even if you encrypt everything, somebody needs to have the key to decrypt, otherwise your whole software is disfunctional. What hinders the employee from giving that password to the sysadmin (over a cup of coffee)? I can't think of ANY system that is safe if someone got the console and/or the root password. As others have said - you need a legal solution. Have your customer sign a non-disclosure agreement plus a EULA that restricts your customer from decompiling, reverse-engineering etc (just download an EULA from Microsoft - their's is pretty complete). Make the penalties for disassembling high enough that it hurts when they do (say $500.000 per case). That certainly depends on the legal system of the country you're selling the software in, so invest the money into a good attorney rather than an encrypted solution. If any of my customers would ask me if they should buy a system where they can't access THEIR data in any other way than using the software that comes with the deal I'd tell them to back off. Most customers on the planet are not interested in your software - they make money from THEIR DATA. I've got a pretty complicated insurance system out there - it took me 5 years to develop and I'm actually distributing it in source together with a plotted copy of UML and database diagrams. The point is: none of my customers ever tried to use the software in any other way than agreed upon. Although I manage the server (I give it to them as part of the deal, so I deliver software plus hardware plus maintenance and off-site backup in one contract) usually someone in the company has the root password just in case I get hit by a bus. Some of my customers even have an agreement that they can modify the software IF either agreed upon in a separate statement OR I can't provide the solution they need. All in all this provides pretty happy customers to me. They know they can use the software even if I go out of business for some reason. Some level of trust is the basis for a good customer relationship (too much trust will kill you though). UC -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(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
[GENERAL] tsearch2 and case
First of all: Happy Independence Day.
I've got a quick question for those with tsearch2 experience.
I set tsearch2 up and it works great (although I'd like to search for phrases
too, but I guess that's not supported at this time).
Anyways, I noted that the search seems to be case sensitive for some search
terms, particularly abbreviations. So if I'm searching with
to_tsquery('ABCD') - where ABCD is a standard abbreviation, I get a lot of
hits, but I get none with to_tsquery('abcd') because the abbreviation is
always upper case in my text.
I'd like the search to be completely case insensitive. Can anyone point me in
the right direction?
Thanks for any help.
UC
--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax:+1 650 872 2417
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] COnsidering a move away from Postgres
I've solved this for my case in 7.4 by defining a view with the desired column layout and the return setof the view. This certainly depends on what you're trying to accomplish. On Thursday 30 June 2005 09:21 am, Jason Tesser wrote: > Can this return multiples? I thought when you dfined columns dynamically > like your example it only returns one record and I need to be able to > return a set. Can your example return a set? > > On Thursday 30 June 2005 10:58 am, Sven Willenberger wrote: > > If I understand the new features correctly, rather than: > > CREATE FUNCTION foo(i int) RETURNS custom_type AS > > and custom_type is (int,text,text) > > you will be able to do the following instead: > > CREATE FUNCTION foo(IN i int, OUT x int, OUT y text, OUT z text) AS ... > > > > As far as hard coding the OUT datatypes, if I understand the docs > > correctly you can even: > > CREATE FUNCTION foo(IN i int, OUT x anyelement, OUT y anyelement, OUT z > > anyelement) AS ... > > > > No custom type needed .. you specify how the output format in the > > argument section itself. > > > > Sven -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(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] Finding points within 50 miles
Actually it does.
I'm using a bounding box too. I have a stored procedure to get me what I need -
here's the relevant part of it.
Explanation: zc is the record holding the point of origin. I just added the
maxdistance definition for this, because in my function its a parameter.
SELECT INTO zc z.* FROM v_profile p JOIN zipcodes z ON
z.zipcode=p.zipcode WHERE p.uid=uid;
IF NOT FOUND THEN
RAISE EXCEPTION \'Cant find member %\',uid;
END IF;
maxdistance:=50;
la_min:=(zc.latn - (maxdistance::float8/70.0));
la_max:=(zc.latn + (maxdistance::float8/70.0));
lo_min:=(zc.longw - (maxdistance::float8/70.0));
lo_max:=(zc.longw + (maxdistance::float8/70.0));
stmt:=''SELECT n.username, n.uid, n.areacode, n.zipcode
geo_distance(point('' || zc.longw ||'',''||
zc.latn ||''),point(z.longw, z.latn))::int as distance,
n.image_thumbnail,n.city, n.state_code
FROM v_new_members n JOIN zipcodes z ON
z.zipcode=n.zipcode
AND (z.latn BETWEEN '' || la_min || '' AND ''
|| la_max || '')
AND (z.longw BETWEEN '' || lo_min || '' AND ''
|| lo_max || '') AND
geo_distance(point(''|| zc.longw
||'',''||zc.latn||''),point(z.longw, z.latn))::int <= ''||maxdistance ;
hope that helps
UC
On Monday 27 June 2005 02:08 am, you wrote:
> Uwe C. Schroeder wrote:
> >in the where clause use something like (requires the earthdistance contrib
> > to be installed):
> >
> >geo_distance(point([origin longitude],[origin latitude]),point([target
> >longitude column],[target latitude column])))::int <= 50
>
> I don't suppose geo_distance really returns a number in miles, does it?
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
--
UC
--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax:+1 650 872 2417
---(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] Finding points within 50 miles
in the where clause use something like (requires the earthdistance contrib to be installed): geo_distance(point([origin longitude],[origin latitude]),point([target longitude column],[target latitude column])))::int <= 50 On Sunday 26 June 2005 04:40 pm, CSN wrote: > If I have a table of items with latitude and longitude > coordinates, is it possible to find all other items > that are within, say, 50 miles of an item, using the > geometric functions > (http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)? > If so, how? > > Thanks, > CSN > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- UC -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(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] setting up PostgreSQL on Linux RHL9 to allow ODBC connections
Not quite correct. TCP needs to be turned on AND an according entry in pg_hba.conf needs to be set up - otherwise the server will just decline to talk to the client. Also - if you're on XP you might want to check the "firewall" settings - which if configured wrong could potentially block connections on port 5432. If you have a default setup of XP it should be fine though. On Wednesday 22 June 2005 08:56 pm, William Yu wrote: > There's nothing on the server side that needs to be configured for > Windows clients to access Postgres via ODBC. As long as TCP/IP is turned > on. Just add a data source and configure the server > ip/name/port/database/etc. > > [EMAIL PROTECTED] wrote: > > Hi, > > I have seen a bunch of different documentation on how to set up to allow > > ODBC, but I am a little confused about how much/what has to be set up to > > allow an ODBC connection from Windows (mostly 2003, some XP) to an > > existing PostgreSQL (7.4.6) database on Linux (RedHat 9 version > > 2.4.20-6smp). > > > > Most of the documentation is talking about setting up Unix to Unix odbc, > > or Windows to Windows, not Windows to Unix/Linux. > > > > Some documentation talks about installing the "full" distribution on > > Windows, but I just want to allow some Windows software that "speaks" > > ODBC (SilkTest) to be able to connect to an existing database. > > > > > > In the document > > http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-redhat, > > it says to download rpms and install them, but I don't really see any > > rpms for this. I see source distributions at > > http://www.postgresql.org/ftp/odbc/versions/src/, but nothing for version > > 7.4, just 7.3 and 8.0. I am reluctant to upgrade to 8.0, as I am not > > sure it is stable yet, and my installation is running so well. > > > > > > The howto-redhat document also talks about running: > > > > > > > > > >psql -d template1 -f /usr/share/pgsql/odbc.sql > > > > > > > > > > > > to modify template1, but what about existing databases? > > > > I'm somewhat nervous about doing anything to potentially mess up anything > > on my Linux server, as this is a production database. > > > > Then, you have to set up the iodbc driver manager, etc. On this server, > > I have had some issues with using rpm's in the past, and have often had > > to resort to building things myself (configure, make, etc.) > > > > I'm also not sure about how to specify a DSN to do the connection from > > the Windows side (I'm primarily a Unix/Linux person). > > > > Does anyone have any specific advice/experiences to offer on setting this > > up as simply as possible? > > > > Thanks, > > Susan > > > > > > > > > > - > >- See our award-winning line of tape and disk-based > > backup & recovery solutions at http://www.overlandstorage.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 > > ---(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 -- UC -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] restarting after power outage
On Wednesday 27 April 2005 15:17, Doug McNaught wrote: > "Uwe C. Schroeder" <[EMAIL PROTECTED]> writes: > > Is this just me or did anyone actually think about adding a UPS to > > the machine and monitor it with NUT ? That way the machine would > > shut down properly, making the whole stale pid-file issue > > irrelevant. > > UPSs fail. People kick out power cords. It's good to be able to deal > with it. > > -Doug You're right about that. Question is how often does this happen to rectify some automated procedure. In case of a hard shutdown there are a whole bunch of things that could potentially go wrong on startup (like fsck failing etc.). So checking up on the machine might be a good idea anyways. I for my part locked the server room - works every time when the cleaning crew comes into the office looking for an outlet to plug the vacuum in. All they take out now is the faxmachine :-) UC -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] restarting after power outage
Is this just me or did anyone actually think about adding a UPS to the machine and monitor it with NUT ? That way the machine would shut down properly, making the whole stale pid-file issue irrelevant. UC On Wednesday 27 April 2005 13:41, Tom Lane wrote: > Philip Hallstrom <[EMAIL PROTECTED]> writes: > > Although I like having a separate startup script that runs first to go > > around removing this and other things as well... > > I think most Unix variants have a specific bootup script that's charged > with doing exactly that; if you can find it, that's a good place to add > a line for postmaster.pid. > > regards, tom lane > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] UltraSPARC versus AMD
Well, you overlook one thing there. SUN has always has a really good I/O performance - something far from negligible for a database application. A lot of the PC systems lack that kind of I/O thruput. Just compare a simple P4 with ATAPI drives to the same P4 with 320 SCSI drives - the speed difference, particularly using any *nix, is surprisingly significant and easily visible with the bare eye. There is a reason why a lot of the financial/insurance institutions (having a lot of transactions in their DB applications) use either IBM mainframes or SUN E10k's :-) Personally I think a weaker processor with top of the line I/O will perform better for DB apps than the fastest processor with crappy I/O. i guess the "my $0.02" is in order here :-) UC On Saturday 23 April 2005 01:06, William Yu wrote: > Looked on AMD's website. 132 for 4x875 on Windows, 126 on Linux. > (Probably Intel compiler on Windows, gcc on Linux.) That gets AMD into > the $100K 16+ processor Sun system area in terms of performance. Of > course, Sun still has a crapload of other uptime/reliability features > built-in to their systems. > > William Yu wrote: > > The numbers don't have the latest dual core Opterons yet. (Don't see > > them on spec.org yet either.) My random guess right now, 4x2 system > > would probably be about 140 SpecINT_rate. It's looking like it's faster > > than have a DC Opteron w/ 1 memory bank versus Dual Opteron w/ 2 memory > > bank because the interconnect between cores inside a DC CPU is so much > > faster than the HT motherboard connect. > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(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] Pgsql config file
On Monday 18 April 2005 12:16, Leif B. Kristensen wrote: > On Monday 18 April 2005 21:07, Gavin M. Roy wrote: > > Thank you for posting to a better list for these questions. Check > > your postgresql.conf file and make sure it's accepting TCP/IP > > connections on the IP you're looking for. If you look in your PGDATA > > directory you should find the config file, and if you open it and > > read it, it's well commented so you should be able to find the > > settting you need to tweak. > > There's one thing I've been wondering about: Why isn't the postgresql > config file in /etc, with all the rest? Because it would make multiple installs of different postgres versions hard/impossible to do. You'd also have file access problems since postgres doesn't run as root. It's also nicely "everything in one spot" UC -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Urgent
have you enabled tcp in postgresql.conf ? the parameter in question is pretty much on top of the file and should read tcpip_socket = true usually postgresql.conf is in /var/lib/pgsql/data/ on a RH system UC On Monday 18 April 2005 11:55, ElayaRaja S wrote: > Hi, > I am using Redhat linux 9. i had configure in pg_hba.conf as > hostpostgres postgres 10.10.0.76 255.255.255.0 password > > If i try to connect with postgresql admin i am getting excpetion as > > An erro has occured: > > Error connecting to the server: could not connect to server: > Connection refuesed(0x274D/10061) > Is the server running on host "10.10.0.76" and accepting > TCP/IP connections on port 5432? > > Please help me. -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(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] psql performance
Don't see a problem pasting this one. Neither to a local nor to a remote ssh (running psql certainly). This is 7.4.7 on redhat and mandrake linux'es I'd suspect it has nothing to do with psql. Can you paste that into a normal ssh / terminal ? On Thursday 14 April 2005 20:05, Joseph Shraibman wrote: > It doesn't matter what the query is. The problem happens before it even > runs the query. Just try pasting select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa' union select > 'aaa'; > > ... and see how long it takes just to get to the point where it executes > the query. > > Incidentally when I did that I only got back one row. What's up with that? > > Dann Corbit wrote: > > What is the query? > > What is the schema for the tables in the query? > > What is the cardinality of the tables? > > > > What does the planner say, when you do this: > > explain > > explain analyze > > > > http://www.faqs.org/docs/ppbook/r26943.htm > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of Joseph > > Shraibman > > Sent: Thursday, April 14, 2005 6:19 PM > > To: pgsql-general > > Subject: [GENERAL] psql performance > > > > How come when I paste a large query into psql it starts off fast but > > then slows to a crawl eating up cpu just echoing the query back to me? > > I'm using psql 7.4.7 > > > > ---(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 > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org -- UC -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(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] PostgreSQL still for Linux only?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Thinking about it you may be right. I guess I'm misstaking it for something
else. Too many "foxes" out here nowadays :-)
To the topic: I don't argue the benefit of a native windows version from a
marketing point of view (although not so from a technical point of view). As
long as MS hasn't filed a chapter 11 the rest of the world will have to deal
with them. Therefor a native windows version is possibly the only way to make
postgresql more popular and sneak it into the one or other fortune 500
company.
On Tuesday 08 March 2005 09:02 pm, Edwin New wrote:
> I don't want to split hairs, but wasn't Firebird originally Interbase? If
> so, you'll find it was originally a *nix product before it was a Windows
> database (back in the Ashton-Tate days for those with long memories).
>
> Edwin New.
>
> -Original Message-
> From: Uwe C. Schroeder [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, 9 March 2005 3:49 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] PostgreSQL still for Linux only?
>
> On Tuesday 08 March 2005 07:24 pm, Tope Akinniyi wrote:
> > Hi,
> >
> > I am wondering at this display of extreme Linux mentality being displayed
> > by the 'top bras' of the PostgreSQL community. And I ask, are we
> > encouraging Windows use of PostgreSQL at all?
> >
> > Take a look at tools being rolled out at PgFoundry on daily basis; all
> > for Linux except the Windows installer. I ask myself what is being done
> > to encourage PostgreSQL Windows users. Nothing is available to them
> > except the Database and PgAdmin. No replication tool, no this, no that.
>
> To be honest - I wouldn't encourage the use of PostgreSQL on Win.
> Neither would I for any database or data warehouse application (which
> probably
> is why SAP put onto their website that they prefer linux to windows
> platforms).
> I think it could even damage the quite good reputation of PostgreSQL - if
> your
> windows box crashes and takes the DB with it - most likely it's not the
> fault
> of a lousy OS, nor the fault of an incompetent sysadmin who forgot to make
> backups - it will be this "shitty" free database system that's to blame.
>
> I wrote quite some software that uses postgresql - never would I tell any
> customer that he could now run it on windows. As a matter of fact I put
> code
>
> like:
>
> if os="win" {
> errormessage("this software is not ported to windows yet");
> exit(99);
> }
>
> into the startup routine - just to make it impossible for the customer to
> run
> it on windows.
>
> > I was troubled when CommandPrompt, the leading Windows support provider
> > responded to a post that their plPHP is for Linux only.
> >
> > Sorry for this: Firebird provides equal tools for Linux and Windows
>
> users.
>
> > We are not the one to tell the Windows users whether they need them.
>
> Firebird was a DOS ISAM DB. It just made it's way to *nix a couple years
> ago.
>
> > Whether Windows is bad or good; Linux is the angel and Windows the devil
>
> is
>
> > not the issue here. PostgreSQL has gone the Windows way and must not be
> > shown to be deficient.
>
> The problem is, that it's a question of perception. Most windows fans don't
> see that "their" OS is pretty instable. So it's not a question if the
> community can do anything to make PostgreSQL look deficient - it's a
> question
> of what people do with it on Win. I had a similar case recently with a
> customer: His MS Office suite crashed at least 3 times a day. So I switched
> him to OpenOffice. Now OO crashed once after a month of perfect operation -
> guess what, the customer is back to MS Office because OO crashed on him and
> MS has this new version that's sooo much better. Call it dumb - but that's
> how a lot of people are. Well, he paid a couple $k to get new licenses and
> is
> back where he was a month ago.
>
> > I am not holding anybody responsible, but I think we need to do a massive
> > re-orientation of the community not to carry the Linux-Windows game too
> > far.
>
> It's just a fact: any unix is a better platform for databases than windows.
> Windows was designed (and mostly still is) as a Desktop operating system -
> and it's fairly good on the desktop. Never trust a server that needs a
> mouse
>
> attached to operate properly. Unix was designed with scalability, stability
> and multiuser-operation in mind - and that's what it's good at. I wouldn't
> want my payroll on a windows box - much
Re: [GENERAL] PostgreSQL still for Linux only?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 08 March 2005 07:24 pm, Tope Akinniyi wrote:
> Hi,
>
> I am wondering at this display of extreme Linux mentality being displayed
> by the 'top bras' of the PostgreSQL community. And I ask, are we
> encouraging Windows use of PostgreSQL at all?
>
> Take a look at tools being rolled out at PgFoundry on daily basis; all for
> Linux except the Windows installer. I ask myself what is being done to
> encourage PostgreSQL Windows users. Nothing is available to them except
> the Database and PgAdmin. No replication tool, no this, no that.
To be honest - I wouldn't encourage the use of PostgreSQL on Win.
Neither would I for any database or data warehouse application (which probably
is why SAP put onto their website that they prefer linux to windows
platforms).
I think it could even damage the quite good reputation of PostgreSQL - if your
windows box crashes and takes the DB with it - most likely it's not the fault
of a lousy OS, nor the fault of an incompetent sysadmin who forgot to make
backups - it will be this "shitty" free database system that's to blame.
I wrote quite some software that uses postgresql - never would I tell any
customer that he could now run it on windows. As a matter of fact I put code
like:
if os="win" {
errormessage("this software is not ported to windows yet");
exit(99);
}
into the startup routine - just to make it impossible for the customer to run
it on windows.
> I was troubled when CommandPrompt, the leading Windows support provider
> responded to a post that their plPHP is for Linux only.
>
> Sorry for this: Firebird provides equal tools for Linux and Windows users.
> We are not the one to tell the Windows users whether they need them.
Firebird was a DOS ISAM DB. It just made it's way to *nix a couple years ago.
> Whether Windows is bad or good; Linux is the angel and Windows the devil is
> not the issue here. PostgreSQL has gone the Windows way and must not be
> shown to be deficient.
The problem is, that it's a question of perception. Most windows fans don't
see that "their" OS is pretty instable. So it's not a question if the
community can do anything to make PostgreSQL look deficient - it's a question
of what people do with it on Win. I had a similar case recently with a
customer: His MS Office suite crashed at least 3 times a day. So I switched
him to OpenOffice. Now OO crashed once after a month of perfect operation -
guess what, the customer is back to MS Office because OO crashed on him and
MS has this new version that's sooo much better. Call it dumb - but that's
how a lot of people are. Well, he paid a couple $k to get new licenses and is
back where he was a month ago.
> I am not holding anybody responsible, but I think we need to do a massive
> re-orientation of the community not to carry the Linux-Windows game too
> far.
It's just a fact: any unix is a better platform for databases than windows.
Windows was designed (and mostly still is) as a Desktop operating system -
and it's fairly good on the desktop. Never trust a server that needs a mouse
attached to operate properly. Unix was designed with scalability, stability
and multiuser-operation in mind - and that's what it's good at. I wouldn't
want my payroll on a windows box - much less my company data.
UC
- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax:+1 650 872 2417
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)
iD8DBQFCLoAijqGXBvRToM4RAu4ZAJ9Ed1kgGzNaFmVCgJSfZS1kAkm9HACfZ5bI
rSX4FvU1RxHR63sg6icE+gU=
=+NPW
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] no self-joins in views?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 You missed something obvious. Executing this query in psql you have no problem, because there is no structure created from the result. A view behaves like a table - try creating a table like create table blah ( id int, id int ); and you'll hit the same error. You have to have different names for the columns. BTW: that's not a postgres problem, ANY relational database will give the same error here. On Thursday 10 February 2005 09:20 am, Christoph Pingel wrote: > >Could you give an actual example? > > Sure. The idea is that 'objects' (persons, books, places) from a > table obj are linked with each other in a link table ool where > objects from obj can appear in an 'subject' or a 'object' column. > Since the relation can be any, this is a very flexible and lean > design for an 'ontology-like' collection of facts. > > As I said, the CREATE VIEW returns an error (column obj_id > duplicated), while the select statement by itself works (with an > additional constraint on s.obj_id, otherwise the db would throw up > hundreds of thousands of rows). > > CREATE VIEW relations_aspect_subject AS > SELECT s.obj_id, s.canonical_name, rlt.dscr, rlt.rlt_id, o.obj_id, > o.canonical_name > FROM obj s, obj o, ool, rlt > WHERE s.obj_id = ool.subject > AND o.obj_id = ool.object > AND rlt.rlt_id = ool.relation > ORDER BY rlt_id > > Do I miss something obvious? > > thanks, > Christoph > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend - -- UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFCC5zdjqGXBvRToM4RAk6bAJ0feXiYMKN0uYHv9qM2S8tH3mAVOwCaAjWv VwPo11ag0tGoOzeclxxFkxI= =xvU0 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] SQL query question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Maybe it's to late for me to think correctly (actually I'm sure of that). I'm going to ask anyways. I have a table like id int4 user_id int4 photo varchar image_type char(1) where image_type is either G or X What I want to do is have ONE query that gives me the count of images of each type per user_id. So if user 3 has 5 photos of type G and 3 photos of type X I basically want to have a result 5,3 It got to be possible to get a query like that, but somehow it eludes me tonight. Any pointers are greatly appreciated. UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFCAdOMjqGXBvRToM4RApgvAJsEUsdl6hrVGqRwJ+NI7JrqQqQ5GgCgkTQN pavTkx47QUb9nr7XO/r/v5k= =B3DH -END PGP SIGNATURE- ---(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] need an advice on running Database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Although with your very little load a manual or cron based vacuum full once a week will be more than enough. I'm doing a biweekly vacuum full with one of my customer's machines (an office application that uses pg as backend) and never had complaints or problems. That setup makes about 5000 transactions a day - still a very low load for postgres. On Wednesday 19 January 2005 03:42 pm, Dann Corbit wrote: > Yes, autovacuum is better. > > I am a fossil from 7.1.3 days. > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 19, 2005 2:10 PM > To: Dann Corbit > Cc: pgsql-general@postgresql.org; [EMAIL PROTECTED]; > Mark > Subject: Re: [GENERAL] need an advice on running Database > > Do you not recommend autovacuum? > > Rick > > > > > "Dann Corbit" > > <[EMAIL PROTECTED]>To: "Mark" > <[EMAIL PROTECTED]>, > Sent by: cc: > > [EMAIL PROTECTED]Subject: Re: > [GENERAL] need an advice on running Database > > tgresql.org > > > > > > 01/19/2005 04:30 PM > > > > > > > > > > Once per day dump database to disk. > Once per day do a vacuum full. > That should be plenty. > > Since there are 1440 minutes per day, you are only looking at 288 > transactions per day. Not exactly a taxing transaction load. > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Mark > Sent: Wednesday, January 19, 2005 12:41 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] need an advice on running Database > > Hi, > I have a small data base ~ 10 tables. each table get > insert/update/delete few times a day. postgresql is running for a > month. > > The load will increase in the near future: insert/update/delete > activity will be at least one in 5 minutes. > > What maintenance should I need to do? > > Thanks, > Mark. > > > > __ > Do you Yahoo!? > All your favorites on one personal page - Try My Yahoo! > http://my.yahoo.com > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > > ---(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 - -- UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFB7v/NjqGXBvRToM4RAsB5AKCTeELPQA9R/kce4rD78X0mJE+JdQCeIIak rs7QSe2KHopOt9AkADg+ofU= =KRuh -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] replicator
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 check out slony. works great On Tuesday 21 December 2004 08:56 pm, Jamie Deppeler wrote: > Can anyone suggest good open source replicator system for pgsql > > Thanks > JD > > ---(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 - -- UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFByQSYjqGXBvRToM4RAssGAKCO6pnSYa516llokOcQ9I6tyZl2IwCgt4tG z57Fwm4PwlLr8RAYSRvtgaE= =phy5 -END PGP SIGNATURE- ---(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] How many views...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sunday 28 November 2004 10:49 pm, Greg Stark wrote: > "Uwe C. Schroeder" <[EMAIL PROTECTED]> writes: > > I could create a view for every node in the system which calculates the > > distance in the result set, making it easy to handle for the application: > > select * from where distance <= 50 > > The problem is, that the data will possibly contain thousands of nodes. > > I'd also need 2 or 3 views per node - which could lead to 50.000 or even > > 100.000 views. > > Normalize. > > Make your view construct the distance for _every_ node. So your query looks > like: > > select * from where node_id = 99 and distance <= 50 Well, in my case a "node_id" would refer to a zipcode (for the basic version, later on maybe even pushing it down to street level using more detailed gps data). The problem I see is that the records the view sits on top of may and will contain several similar records. Think of UPS: they would want to know the distance to any recipient of a parcel, however a lot of those recipients have the same zipcode. I just can't think of a view that retrieves a specific person in that zipcode based on the zipcode. So there would have to be a second parameter to it - or a view for each record. To put it better: The application records customers. table customer ( customer_id int4 primary key, zipcode varchar(10), other stuff about the customer ) A normalized view just wouldn't return that specific customer plainly based on the zipcode, because there could/will be a second or third customer in the same zipcode. For the distance that wouldn't matter, but for the customer info it would. I'm just playing with options right now. Maybe/likely I have to revise the database model. The stored proc works fine, it just could become slow with a lot of customer records. I think Michael's prior post is the better answer - limit the possible coordinates to a subset before starting to calculate the actual distance. > The danger here is you have to be absolutely sure that Postgres is pushing > that clause down into your view or the performance will be bad. But if your > view is at all sane then I think it will. And you'll certainly know if it > isn't. That's what I'm afraid of. The database will potentially contain 100.000 customer records once productive - in the US alone, leaving aside what has to be done about the rest of the world. So, yeah - one will notice the drag on an expensive calculation, particularly because the application has lists that show ALL customers sorted by distance to one (changing) location. UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBqs99jqGXBvRToM4RAsv+AKCsM05f9JR0yMIXfbELrArJ6z9WKACeKfYa nAsM0NRh09R+Zl7eu+FDS/g= =DiJv -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How many views...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sunday 28 November 2004 10:43 pm, Michael Fuhr wrote: > On Sun, Nov 28, 2004 at 06:41:50PM -0800, Uwe C. Schroeder wrote: > > I need to search a lot of locations based on distance (simple zipcode > > match based on longitude and latitude). However I need to calculate the > > distance between each of the nodes, so if you are in xxx I need to get > > the distance to all others in the database. I'm currently doing this with > > a stored procedure that gets the originating zipcode and a maximum > > distance in miles which then selects all other nodes within that search > > radius. This is pretty unhandy, but it works. > > What's unhandy about this approach? I've written stored procedures > that do exactly what you're talking about; they work fine and are > easy to use. > > Are you using a bounding box to limit the number of nodes that > you need to check? For example, if the originating zipcode is > at 40.0N 90.0W and you want to find all other zipcodes within > 50 miles, then you'd only need to check the distance to those > zipcodes with a latitude between about 39.27N - 40.73N and a > longitude between about 89.05W and 90.95W. No zipcode outside > that box could possibly be within 50 miles of the origin, so > there's no need to calculate and check the distances to them. > If you have indexes on latitude and longitude then the search > should be fast. The "unhandy" part is maybe a personal perception. I like stored procs, but in this case the calculation is repeated over and over again (ok, it would be the same with views). Basically the part I don't like is that the proc currently calculates way more values than needed. Because something like where sqrt(pow((lat1 - lat2),2) + pow((long1 - long2),2)) >= 50 certainly calculates the distance of all the records and then compares the result to the 50 mile radius. I'd rather have something that excludes most of the records that aren't in question anyways. How do you come to the lat/long values for the max difference? Is there a general formula for that? This looks like I could omit records too far away from the calculation in the first place. I know - maybe I should dig for those old geometry books that are somewhere in a box, but if you have the base for that handy I'd appreciate if you tell me (I hated math all my life ;-) ) UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBqsp/jqGXBvRToM4RAg/xAJ497wF1pwbzLFHbC/f1UehOoG2iGwCfWKYQ 5cNIUb984sPLtBGudDqspF8= =hsl2 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] How many views...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, a (maybe/probably) stupid idea just popped to my mind: Problem: I need to search a lot of locations based on distance (simple zipcode match based on longitude and latitude). However I need to calculate the distance between each of the nodes, so if you are in xxx I need to get the distance to all others in the database. I'm currently doing this with a stored procedure that gets the originating zipcode and a maximum distance in miles which then selects all other nodes within that search radius. This is pretty unhandy, but it works. The idea: I could create a view for every node in the system which calculates the distance in the result set, making it easy to handle for the application: select * from where distance <= 50 The problem is, that the data will possibly contain thousands of nodes. I'd also need 2 or 3 views per node - which could lead to 50.000 or even 100.000 views. The question: 1) does it make sense to do this performance-wise? 2) does this make sense at all? 3) can postgresql handle that many views? Thanks for any opinions (or better ideas than a stored proc or the views concept) UC -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBqoxujqGXBvRToM4RAusrAJ9e/7jljmE+wNVkeltvErxffCa+xACfba0X b5ClK8BKCdg5cWaWCnqQklE= =iiDR -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] how many JOINs?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Saturday 27 November 2004 02:59 am, Konstantin Danilov wrote: > Hello List! > Do you know how many times can JOIN be used in a query? How many JOINs does > PostgreSQL support in ONE query? > I think you will run out of memory before hitting the limit on the number of joins. UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBqMdGjqGXBvRToM4RAswbAJ98WLPEtnWwXmEpGWiejhmHf4sTDgCaA6p3 v3H98PZeyap0r8vS9ox2GbQ= =x+Qd -END PGP SIGNATURE- ---(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] Last value inserted
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Thursday 11 November 2004 10:23 am, Franco Bruno Borghesi wrote:
> I think the best way would be not to use a SERIAL field, but an INTEGER
> field and a sequence:
a "serial" is just a convenient shortcut to an int with an automatically
created sequence. As proof - just create a table with a serial and dump it
with pg_dump: you'll end up with a table containing an int with a nextval(...
as the default. The only difference is that in case of the "serial" field you
don't name the sequence yourself.
> CREATE SEQUENCE parent_seq;
> CREATE TABLE parent(id INTEGER, descrip CHAR(50));
>
>
> So when you want to insert on the parent table, you obtain the next
> value from the sequence and then you insert in the parent and child
> tables the value you obtained:
>
> newId:=SELECT nextval('parent_seq')
> INSERT INTO parent(id, descrip) VALUES (newId, '');
> INSERT INTO child_1(..., ..., parentId) VALUES (..., ..., newId);
> INSERT INTO child_2(..., ..., parentId) VALUES (..., ..., newId);
> INSERT INTO child_3(..., ..., parentId) VALUES (..., ..., newId);
which amounts to the curval in the same connection.
>
> hope it helps.
>
> MaRCeLO PeReiRA wrote:
> >Hi guys,
> >
> >I am in troubles with a SERIAL field.
> >
> >I have five tables. A parent table and four child
> >tables. When I do the INSERT in the parent table, I
> >have an ID (generated) by the sequence (SERIAL field),
> >and I have to use this ID to reference all child
> >tables.
> >
> >Well, once I do an INSERT in the parent table, how can
> >I know (for sure) which number id was generated by the
> >sequence?
> >
> >Simple example:
> >
> >--
> >CREATE TABLE parent(id SERIAL, descrip CHAR(50));
> >--
> >
> >So,
> >
> >--
> >INSERT INTO parent (descrip) VALUES ('project 1');
> >--
> >
> >How can I now (for sure) with value was generated by
> >the sequence to fill the field ID?
> >
> >(There is lots of users using the software at the same
> >time, so I am not able to use the last_value()
> >function on the sequence.)
> >
> >Best Regards,
> >
> >Marcelo Pereira
> >Brazil
> >
> >
> >
> >
> >
> >___
> >Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora!
> > http://br.acesso.yahoo.com/
> >
> >---(end of broadcast)---
> >TIP 4: Don't 'kill -9' the postmaster
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
- --
UC
- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax:+1 650 872 2417
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFBlFD5jqGXBvRToM4RAmfQAJ9JyQxERqcau1kCnvkrXNmpaGTwzwCgqK6L
7zCpR+uO5pzvDuY/itTYCfs=
=mq0M
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] OID Question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 11 November 2004 07:04 am, Terry Lee Tucker wrote: > Greetings, > > Here is a simple question: > > Is it ok to put a unique index on the oid for my tables? We are in the > process of moving from Progress Software to PostgreSQL. In the Progress > world, you can always uniquely, and quickly find a record by using their > version of oid, which is recid. I remember reading somewhere that the oid > could be duplicated across the cluster, but would not be duplicated in a > single table. Maybe I dreamed it. What is the recommendation regarding this > and why? OID's are unique per database as long as they don't turn over - meaning oid's have a limited range - AFAIK 32 bit integer, so yes, inside a table you could use the oid (assuming you created the table WITH OIDS) to identify a record. HOWEVER: bad design. oid's are likely to go away at some point down the road (maybe in postgresql 12.0 or so :-)) ). Therefor, and since you're migrating anyways, it would be better to add a primary key column to each table. Unless I have a real need for a primary key, I usually just add a "id" column (as PK with default from a sequence) to my tables. Therefor I can always use the id column to identify records. This is portable and easy to migrate if you need to upgrade to a newer version of postgresql - or if you strike it rich and have to go to oracle or db2 it's still portable. Also: having a real column with a key if more failsafe. I once (long long ago) used oids as foreign keys. I remember back then dumping and restoring the db was a pain. Now you could use the "dump oids" option, but if you forget that and you restore the DB your relationship model will be a big pile of trash because the oid's change on restore. I'd recommend to go the extra mile and add at least a unique key column to your tables. Hope that helps UC -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBk6gwjqGXBvRToM4RAu5kAKCbMcYtk7qs3xv+UyrgD0RftGBpbwCgrHPi r8mynfAyne7lRETGLIMCz5E= =Dl/9 -END PGP SIGNATURE- ---(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] Last value inserted
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
option 1) use a stored procedure to create the record. Within a transaction
the last value function will return the correct last value, not the one of a
concurrent insert.
option 2) if you know that this user uses the same connection for all his
queries - or at least the ones in question - you can rely on the last value
being the correct one. Like with transactions, if you use the same connection
the last value will be the correct one.
You're only in trouble if you're not within a transaction and you're not sure
if the connection stays the same for the queries in question. The later could
be due to connection pooling.
UC
On Thursday 11 November 2004 04:59 am, MaRCeLO PeReiRA wrote:
> Hi guys,
>
> I am in troubles with a SERIAL field.
>
> I have five tables. A parent table and four child
> tables. When I do the INSERT in the parent table, I
> have an ID (generated) by the sequence (SERIAL field),
> and I have to use this ID to reference all child
> tables.
>
> Well, once I do an INSERT in the parent table, how can
> I know (for sure) which number id was generated by the
> sequence?
>
> Simple example:
>
> --
> CREATE TABLE parent(id SERIAL, descrip CHAR(50));
> --
>
> So,
>
> --
> INSERT INTO parent (descrip) VALUES ('project 1');
> --
>
> How can I now (for sure) with value was generated by
> the sequence to fill the field ID?
>
> (There is lots of users using the software at the same
> time, so I am not able to use the last_value()
> function on the sequence.)
>
> Best Regards,
>
> Marcelo Pereira
> Brazil
>
>
>
>
>
> ___
> Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora!
> http://br.acesso.yahoo.com/
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (GNU/Linux)
iD4DBQFBk6lnjqGXBvRToM4RAgOXAJiVy9TG9Yv05pegzACw2VPeN7USAKDRYg/N
H0BKK8WT1aOZ+CB3rCl8WQ==
=kiLq
-END PGP SIGNATURE-
---(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] SQL question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thanks for your answers Greg & Vincent. Although I solved the problem by a change of schema - I'm happy that I have something to digest I didn't know before. One never learns enough ... U.C. On Saturday 06 November 2004 03:13 pm, Uwe C. Schroeder wrote: > Here's a question for the SQL guru's out there, which I've been trying to > solve for the last couple of hours. There's got to be a solution to this, > but somehow I can't find it. > > Tables: > > table1 ( > uid int PK, > uname varchar(64) > ) > > table2 ( > uid int FK to table1, > xuid int FK to table 1 > ) > > table3 ( > uid int FK to table1, > yuid int FK to table1 > ) > > There might be more tables of the type like table2 and table3, but I'd > already be happy to solve the puzzle with the 3 tables above. > Ok, assume table1 is the master table - in my case a table used for login > authentication (some columns removed above) > table2 and table3 are tables where the uid always references to the uid in > table1. The second "uid" (xuid and yuid in this example) references to > another uid record in table1. The problem is that there may or may not be > entries in table2 (or table3) referencing a specific uid in their second > uid field. > Maybe some data: > > table1: > 1 test1 > 2 test2 > 3 test3 > > table2: > 1 2 > 1 3 > 3 1 > > table3: > 1 2 > 2 3 > 3 2 > > What I want to do in a view is the following resultset: > > uid uname xuid yuid > 1test1 2 2 > 1test1 3 > 2test2 3 > 3test3 1 > 3test3 2 > > > So basically I want to know which uid is connected to which uid, one > relationship per row. So xuid and yuid shall be identical if records exist > in both table2 and table3 or the value shall be NULL if a corresponding > record can't be found in either table2 or table3. > > Can anyone here help me out? > > Thanks a lot > > UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBjz6bjqGXBvRToM4RApNRAJ9tJzn/3DHSYEZPlGSjzU0H/FsQIwCffw4N XJuHiF0al0pzInvOb3BP1Jg= =490X -END PGP SIGNATURE- ---(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] SQL question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 If someone knows this it would be great - because I'm still curious how to solve it. However I just remodelled my db structure to eliminate the problem (basically I pulled the several tables into one since each of the table2/table3 tables only has 3 fields) so now I do: table2 ( uid int FK to table1, luid int FK to table1, is_in_table3 boolean, is_in_table4 boolean, . ) this just needs a simple select with a join against table1. UC On Saturday 06 November 2004 03:13 pm, Uwe C. Schroeder wrote: > Here's a question for the SQL guru's out there, which I've been trying to > solve for the last couple of hours. There's got to be a solution to this, > but somehow I can't find it. > > Tables: > > table1 ( > uid int PK, > uname varchar(64) > ) > > table2 ( > uid int FK to table1, > xuid int FK to table 1 > ) > > table3 ( > uid int FK to table1, > yuid int FK to table1 > ) > > There might be more tables of the type like table2 and table3, but I'd > already be happy to solve the puzzle with the 3 tables above. > Ok, assume table1 is the master table - in my case a table used for login > authentication (some columns removed above) > table2 and table3 are tables where the uid always references to the uid in > table1. The second "uid" (xuid and yuid in this example) references to > another uid record in table1. The problem is that there may or may not be > entries in table2 (or table3) referencing a specific uid in their second > uid field. > Maybe some data: > > table1: > 1 test1 > 2 test2 > 3 test3 > > table2: > 1 2 > 1 3 > 3 1 > > table3: > 1 2 > 2 3 > 3 2 > > What I want to do in a view is the following resultset: > > uid uname xuid yuid > 1test1 2 2 > 1test1 3 > 2test2 3 > 3test3 1 > 3test3 2 > > > So basically I want to know which uid is connected to which uid, one > relationship per row. So xuid and yuid shall be identical if records exist > in both table2 and table3 or the value shall be NULL if a corresponding > record can't be found in either table2 or table3. > > Can anyone here help me out? > > Thanks a lot > > UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBjXebjqGXBvRToM4RAq+kAKCyK1bwfRBdVoAvoi0boELr90wSmwCdHUEf p9L9Z1OSHwqvYn+ZnDWSTQw= =Ih7b -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] SQL question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Here's a question for the SQL guru's out there, which I've been trying to solve for the last couple of hours. There's got to be a solution to this, but somehow I can't find it. Tables: table1 ( uid int PK, uname varchar(64) ) table2 ( uid int FK to table1, xuid int FK to table 1 ) table3 ( uid int FK to table1, yuid int FK to table1 ) There might be more tables of the type like table2 and table3, but I'd already be happy to solve the puzzle with the 3 tables above. Ok, assume table1 is the master table - in my case a table used for login authentication (some columns removed above) table2 and table3 are tables where the uid always references to the uid in table1. The second "uid" (xuid and yuid in this example) references to another uid record in table1. The problem is that there may or may not be entries in table2 (or table3) referencing a specific uid in their second uid field. Maybe some data: table1: 1 test1 2 test2 3 test3 table2: 1 2 1 3 3 1 table3: 1 2 2 3 3 2 What I want to do in a view is the following resultset: uid uname xuid yuid 1test1 2 2 1test1 3 2test2 3 3test3 1 3test3 2 So basically I want to know which uid is connected to which uid, one relationship per row. So xuid and yuid shall be identical if records exist in both table2 and table3 or the value shall be NULL if a corresponding record can't be found in either table2 or table3. Can anyone here help me out? Thanks a lot UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBjVqVjqGXBvRToM4RAhk5AKCjjGc4VilA45PnPZoKluTNYUP6FACgrRQq XFIvkCIJHyz7TvvV/XxL4Lk= =/vxG -END PGP SIGNATURE- ---(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]: Unable to load libsqlpg.so
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 There is no version of this library linked to a newer postgres version. Symlinking libpq.so.3 to libpq.so.2 doesn't work with 7.4 anymore - at least not here on my mandrake box. I found no way to get this working except for compiling a libpq from a postgres 7.2.x version - not what you want to do. However: kylix comes with an odbc driver. I just set up my unixodbc and told all my reports to use odbc -> works quite fine. So maybe until borland comes up with a newer version of libsqlpg.so you'd have to resort to odbc. Hope that helps UC On Wednesday 03 November 2004 08:18 am, Carlos Ojea Castro wrote: > Oliver Elphick wrote: > >On Wed, 2004-11-03 at 12:56 +0100, Carlos Ojea Castro wrote: > >>>I'm trying to use kylix3 and postgresql 7.4.1. > >>> > >>>My distro was Debian Woody, kernel 2.20. > >>>I make the link /usr/local/pgsql/lib/libpq.so pointing to libpq.so.2.2 > >>>and connection with my database get fine. > >>> > >>>But now, using Debian Sarge Testing, kernel 2.4.27 (I tried also with > >>>kernel 2.6.8-1) that link don't make things work anymore, I get the > >>>error 'Unable to load libsqlpg.so' and I cannot connect with my > > > > > >Did you spell that right? Where does that library come from? It sounds > >as if it belongs to some package that calls Pg; it isn't part of Pg > >itself. > > libsqlpg.so is located at /usr/local/kylix3/bin, and its a link to > /usr/local/kylix3/bin/libsqlpg.so.1.0 > > >>>postgresql database. > >>> > >>>Anyone knows how can I make my connection work again? > >>> > >>>Thank you, > >>>Carlos > >> > >>Finally I get the connection working in Debian Sarge, kernel 2.4.27 > >>doing this: > >>1.- Install libpgsql2.7.2.1-2woody6_i386.deb > > > >I presume you mean libpgsql2-7.2.1-2woody6_i386.deb. This is the > >libpq.so.2 library from PostgreSQL 7.2.1, so it is not very suitable for > >7.4. On the other hand, judging by what you write below, you may not > >actually be using it. > > Yes, I mean libpgsql2-7.2.1-2woody6_i386.deb, maybe that installation is > useless. > > >>2.- in /usr/local/kylix3/bin make the link: ln -s > >>/usr/local/pgsql/lib/libpq.so libpq.so > >>3.- in /usr/local/pgsql/lib make the link: ln -s libpq.so.2.2 libpq.so > > > >That ought to fail if libpq.so is already there, since you aren't using > >-f, or have you got the names reversed? > > Well, I did 'rm libpq.so' before making the link :) > > >libpq.so is only used for linking into binaries when a program is > >compiled; the linked library loaded by a program at runtime is > >libpq.so.x, where x is the library major version number. You seem to be > >wanting to tell kylix that the libpq.so.2 library is > >at /usr/local/pgsql/lib/libpq.so, and at the same time make that a > >symbolic link to the libpq.so.3 library. > > At the same time make that a simbolic link to: libpq.so.2.2 > > >>However, doing that in Debian Sarge, kernel 2.6.8-1 results in an error > >>'Invalid username/password' when I try to connect to the database. > > > >I think I'm not surprised; I don't know if this error is directly caused > >by the library mismatch, but you seem to be going out of your way to > >confuse things! You don't mention running ldconfig, so I'm not even > >sure which library is going to be called; run ldd on your executable and > >see what it says. You might be getting the libpq.so.2 library from the > >libpgsql2 package from 7.2.1 and using it to connect to a 7.4 database; > >or you might be connecting with a library that is masquerading as > >libpq.so.2 but is actually libpq.so.3. > > ldd c_admin_v380: > libX11.so.6 => /usr/X11R6/lib/libX11.so.6 > libpthread.so.0 => /lib/libpthread.so.0 > libdl.so.2 => /lib/libdl.so.2 > libc.so.6 => /lib/libc.so.6 > /lib/ld-linux.so.2 => /lib/ld-linux.so.2 > > >>Any ideas for Debian Sarge, kernel 2.6.8-1? > > > >The kernel version should have nothing to do with it. Library loading > >is handled by the C library, libc6 and ldd.so. > > > >My first suggestion is to get a version of Kylix that is built for > >PostgreSQL 7.4. > > You are right. After reading your post, I tried 'ldconfig > /usr/local/pgsql/lib' and the connection with my database works in my > computer with Debian Sarge kernel 2.6.8-1. > > Thank you very much! > > > > > ---(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 - -- UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBiRoljqGXBvRToM4RApjOAJ0XF8qJOCggaKWuTwf3HofWOixrKgCdGsUN NNCjnXeC+uaCoI0Pn/vKwfY= =u2cd -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: if posti
Re: [GENERAL] adding missing FROM-clause
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 29 October 2004 08:42 am, C G wrote: > Dear All, > > I have a simple join query > > SELECT c1 FROM t1 > INNER JOIN > t2 ON t2.c2 = t1.c2 WHERE t3.c3= t2.c3; ^ Your join doesn't alias anything as t3 I'd say you get rid of the notice once you replace t3 with t1 > > Which gives the expected result but I get the message > NOTICE: adding missing FROM-clause entry for table "t3" > > How do I get rid of this NOTICE, i.e. how should I construct my select > query. > > Many thanks > > Colin > > _ > Want to block unwanted pop-ups? Download the free MSN Toolbar now! > http://toolbar.msn.co.uk/ > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend - -- UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBguJDjqGXBvRToM4RArh0AJ46apSm5O/dX4QYJxuC44yyjYcWHQCfZpMu +UU1SoXolAFx7fEhMUUXp5w= =GRUr -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] newbie
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 log in as root and edit the file /var/lib/pgsql/data/postgresql.conf Right on the top there is a line #tcpip_socket=true make sure this line has no # in front (comment) and see that it has "true" as the value. Second check out pg_hba.conf At the end there are entries of who may access the database. Just to make it work (without security considerations) see that the two lines local all all trust hostall all 127.0.0.1 255.255.255.255 trust are there and don't have a hash (#) in front. Restart postgresql (as root: /etc/init.d/postgresql restart) or just reboot the machine. If you still have problems connecting as postgres (no password) you might want to give the postgres account a password (Mandrake Controlcenter section users, or just start userdrake manually). Hope that helps. UC On Monday 25 October 2004 03:48 pm, Rick wrote: > i installed mandrake 10 on my pc, and i choose postgresql as db, i > installed it before in windows, but in linux i dont have idea what > happends, i remebered that i should create an user, but in linux the > process is auto and i when i try to connect by pgadmin i dont know user and > password =( > > i wrote in user postgre and trusted server but it says > > Error connecting to the server: could not connect to server: Connection > refused > Is the server running on host 127.0.0.1 and accepting > TCP/IP connections on port 5432? > > and the server is up, the service is running, who can help me please?? =( > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings - -- UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBfYf0jqGXBvRToM4RAuG1AJ9A5+l3CXcKmRUAuX9GQoYIWN9a8wCfcCEK wwVdZ7d8rxyBguZSmJY9S0g= =7v9S -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] guaranteeing that a sequence never skips
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sunday 03 October 2004 10:21 am, Scott Marlowe wrote: > On Sun, 2004-10-03 at 08:58, David Garamond wrote: > > Am I correct to assume that SERIAL does not guarantee that a sequence > > won't skip (e.g. one successful INSERT gets 32 and the next might be 34)? > > > > Sometimes a business requirement is that a serial sequence never skips, > > e.g. when generating invoice/ticket/formal letter numbers. Would an > > INSERT INTO t (id, ...) VALUES (SELECT MAX(col)+1 FROM t, ...) suffice, > > or must I install a trigger too to do additional checking? > > You will have to lock the whole table and your parallel performance will > be poor. > There was a thread about this a while back. I'm using a separate counter table and stored procs that increment the value of the counter - similar to nextval used for sequences. My "nextval" locks the "counterrow" in question using "...for update". So while I'm generating the record that requires the sequential number I'm in the same stored proc and therefor in a transaction. If I have to roll back, the counter number in the countertable will roll back too. You just have to make sure your routine to completely generate whatever you have to generate doesn't take long, because parallel uses of the same thing will block until your proc commits or rolls back. UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBYD6KjqGXBvRToM4RAgFOAKCeJnwA6PnXquCrUMwGbR9tQZBxdgCdGqyy nwNbHafAiInSX+WTh5Uzb4o= =Uixo -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Is it possible...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I wasn't able to get a usable answer by googling: Is there a way to create a view on a table that converts rows to columns ? Example: I have a table create table blah ( id int4 serial, parentid int4, pname varchar(64), pvalue varchar(128) ); which is basically a classic key/value pair. Parentid is a field defining that certain rows belong to the same entity, i.e.: 1 1 'firstkey' 'firstvalue' 2 1 'secondkey' 'secondvalue' ... ... 10 2 'firstkey' 'firstvalue' ... All the "parents" have the same set of keys - guaranteed. I'd need to convert this into a table which has the keynames as columns and one row per "parentid", i.e.: parentid firstkey secondkey 1firstvalue secondvalue 2firstvalue secondvalue I hate the idea of writing code that really creates and fills a table, particularly since the original table has a lot of rows. Is the above doable with a view? Any pointers will be appreciated. Thanks UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBK2MfjqGXBvRToM4RAt/dAKCcBaDVMvfP9fIJDy6qFh8euRfnJwCfY8HH hqjmfHNjzTjZ71jdhjv4cIU= =TfAU -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Slony setup help needed
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, has anyone encountered an error when running a slony_setup.pl generated script to set up a master? The problem I hit is that the xxid.v73.sql file aborts with the following error: Aug 10 20:57:57 ibserver postgres[9749]: [12] ERROR: parser: parse error at or near "." at character 909 Aug 10 20:57:57 ibserver postgres[9749]: [13] LOG: query: select 1; Aug 10 20:57:57 ibserver postgres[9749]: [14] ERROR: current transaction is aborted, queries ignored until end of transaction block Aug 10 20:57:57 ibserver postgres[9749]: [15] LOG: query: rollback transaction; Aug 10 20:57:57 ibserver postgres[9749]: [16] LOG: pq_recvbuf: unexpected EOF on client connection I traced the error to the two "CREATE CAST" statements for xxid in the script. According to the pg-docs the types in create cast may not be namespace specified, the script however adds the namespace. Since I couldn't find a bugreport, any reference on google or anything else about this I have to assume it's something local to my setup. If the namespace specification wasn't possible at all, someone else would have run into this problem already. I'm using slony1-1.0.1 on a plain vanilla mandrake 9.1 with postgres 7.3.2 (from the rpm) Any pointers would be greatly appreciated. Uwe - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 - ---(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 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBGctNjqGXBvRToM4RAkpuAKCRMR6DULDdj9uyFEzig5W/jw8gowCdFxPi g0IEiLhqmNhis6aG0Jw5hh4= =hAMR -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Creating an hourly backup
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Why don't you just use a simple shell script run via cron.hourly:
#!/bin/sh
cd /opt/backups
pg_dump -R -O -a -i -Umyuser mydatabase > db_backup`date +'%Y%m%d'`.sql
gzip --best *.sql
I'm using the above for a daily backup in /etc/cron.daily. Look at the options
to pg_dump - they may not be what you want. The above will dump data only, no
structure.
Just a thought.
UC
On Wednesday 04 August 2004 06:29 am, Jason Tesser wrote:
> I am trying to get an hourly backup of postgres. I tried using the
> script below
> but when I try to restore off the dumped file it throws an error that
> says it
> cannot read the data [-1]. If I just type in pg_dump -Fc --file=***
> username= database
> it restores fine. So something is going on with the
> except script, posted below. Can someone send me a working
> cron or except script so I can get an hourly snapshot
> of our databases?
>
> #!/usr/bin/expect -f
>
> ###
> ###
> # Script to backup PostgreSQL Database
> # usage:
> # ./dbbackup.exp [username] [password] [dbname] # # Use this script in a
> cron job to do nightly # backups # Date format is MMHHDDMM
> ###
> ###
>
> set username [lindex $argv 0]
> set password [lindex $argv 1]
> set database [lindex $argv 2]
> set time [timestamp -format %M%H%d%m%Y]
>
> spawn {pg_dump} -Fc --username=dbdevel
> --file=/var/lib/pgsql/backups/$database.$time $database # expect
> "Username:"
> # send "$username\r"
> expect "Password:"
> send "$password\r"
> expect eof
>
> Jason Tesser
> Web/Multimedia Programmer
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
- --
UC
- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax:+1 650 872 2417
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFBEdw6jqGXBvRToM4RAr73AJ9xQ4kTLRuIAKgq57mqvv+9HZh5UwCgx2lL
6EkUiLWqrsZaADkN41i/dK4=
=2mXn
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Can't compile a contrib util: dbsize (probably simply though)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'd say you need to install the postgresql-devel package if you're on a rpm based system. On Thursday 10 June 2004 02:20 pm, Vitaly Belman wrote: > Hello pgsql-general, > > When I am running "make" I get the following: > > gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes > > -Wmissing-declarations -fpic -I. -I~/postgresql-7.4.2/src/include > > -D_GNU_SOURCE -c -o dbsize.o dbsize.c dbsize.c:1: postgres.h: No such > > file or directory > > dbsize.c:7: access/heapam.h: No such file or directory > > dbsize.c:8: catalog/catalog.h: No such file or directory > > dbsize.c:9: catalog/namespace.h: No such file or directory > > dbsize.c:10: commands/dbcommands.h: No such file or directory > > dbsize.c:11: fmgr.h: No such file or directory > > dbsize.c:12: storage/fd.h: No such file or directory > > dbsize.c:13: utils/builtins.h: No such file or directory > > make: *** [dbsize.o] Error 1 > > I don't understand why it can't find these files.. They are indeed in > "~/postgresql-7.4.2/src/include" just as the -I points =(. > > Tips will be much appreciated. > > Regards, > Vitaly Belman > > ICQ: 1912453 > AIM: VitalyB1984 > MSN: [EMAIL PROTECTED] > Yahoo!: VitalyBe > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) - -- UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAyNT5jqGXBvRToM4RAncQAJ4myr6GNUScZR10QWS2pjDsdO1EYACgyZZn k+sqawEng6cPYzRd7xizYgg= =3I45 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Verhindern, dass im Mehrbenutzerbetrieb mit veralteten Daten gearbteitet wird
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Bastian,
warum die Tabelle nicht so aufbauen:
create table jadajada (
internal_number serial primary key,
nr int4,
testdaten varchar(254)
);
die "internal_number" wird dann automatisch von postgres vergeben. Dann kannst
du deine nummern umschieben wie du willst, die interne nummer wird sich nie
ändern. Die interne nummer benutzt du um den delete, update etc. zu
kontrollieren ala:
delete from jadajada where internal_number=12345;
Die Nr. degradiert zu einem normalen datum was beliebig geändert werden kann.
Du gibst die interne nummer in deiner Applikation natürlich nicht aus. Die
wäre für den Benutzer eher verwirrend.
Uwe
On Thursday 06 May 2004 02:40 am, Bastian wrote:
> Hallo Uwe,
>
> zunächst einmal danke für die schnelle Antwort und den Tipp meine
> Anfrage auf Englisch zu stellen. Ich werde das beim nächsten Mal
> berücksichtigen.
>
> Zum Problem: Wenn ich die Datensätze eindeutig identifiziere, kann ich
> das Problem nicht beheben.
> Beispiel:
> Ausgangstabelle:
>
> Nr daten
> 1 testdaten1
> 2 testdaten2
> 3 testdaten3
>
> Wenn nun Benutzer 1 den Datensatz mit der Nr.2 - also "testdaten2" -
> löscht, so sollen sich die Nummern verschieben und die Tabelle sieht
> folgendermaßen aus.
>
> Nr daten
> 1 testdaten1
> 2 testdaten3
>
> Der zweite Benutzer würde dann also bei einem Zugriff auf Nr.2 nicht
> wie gewünscht "testdaten2" löschen, sondern "testdaten3".
> Wenn sich die Nummern nicht verschieben würden, also die Tabelle so
> aussieht:
>
> Nr daten
> 1 testdaten1
> 3 testdaten3
> dann würde der DELETE bzgl. Nr.2 ins Leere gehen.
>
>
> Bastian
>
>
> [EMAIL PROTECTED] ("Uwe C. Schroeder") wrote in message
> news:<[EMAIL PROTECTED]>...
>
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> >
> > Bastian,
> >
> > die liste [EMAIL PROTECTED] is 99% in englisch. Deine Anfrage
> > ha t
> > wesentlich mehr Aussicht auf Erfolg wenn die sie in englisch stellst.
> >
> > Zum Problem: kannst du die Datens tze nicht eindeutig identifizieren ? Um
> >
> > erfolgreich einen Lock zu setzen muss die Tabelle einen primary key
> > haben,
> >
> > der dann eindeutig ist. Damit h ttest du zumindest nicht das Problem da
> > ein
> > Datensatz gel scht wird der "zwar die gew nschte Nr hat, aber nicht meh
> > r der
> > Datensatz ist". Du kannst explizite rowlocks setzen, aber wie gesagt es
> > ist
> >
> > einfacher die rows eindeutig zu identifizieren, dann kann der Benutzer
> > mit
> >
> > der veralteten Ansicht zwar noch l schen, der delete geht dann aber ins
> >
> > leere. Nur ein update auf den veralteten datensatz wird einen fehler
> > erzeugen, den deine Applikation dann mit entsprechender fehlermeldung
> > abfangen muss.
> >
> > UC
> >
> > On Tuesday 04 May 2004 01:50 am, Bastian wrote:
> > > Hi,
> > >
> > > ich benutze PHP und PostgreSQL.
> > > Folgendes Problem: Eine Seite zeigt die Daten, die in einer Tabelle
> > > der DB abgespeichert sind. Der Benutzer w hlt dann einen Datensatz
> > > aus, den er gerne bearbeiten oder l schen m chte. Auf der n chsten
> > > Seite wird die Aktion dann ausgef hrt.
> > > Es ist m glich LOCKS zu setzen, um zu verhindern, dass sich 2 DELETES
> > > bzw. UPDATES in die Quere kommen, bzw. werden implizit gesetzt. Aber
> > > wenn ein Benutzer sich auf Seite 1 also auf der Tabellenabfrageseite
> > > befindet, und ein anderer Benutzer w hrend dessen die Daten in der
> > > Tabelle ver ndert, so arbeitet Benutzer 1 mit den alten Daten und
> > > l scht dann z.B. einen Datensatz der zwar die gew nschte Nr hat, aber
> > > nicht mehr der Datensatz ist, den er eigentlich l schen wollte.
> > > Ist es vielleicht m glich per Abfrage zu pr fen, ob gerade ein LOCK
> > > gesetzt ist?
> > >
> > > Bastian
> > >
> > > ---(end of
> > > broadcast)--- TIP 1: subscribe and unsubscribe
> > > commands go to [EMAIL PROTECTED]
> >
> > - --
> > UC
> >
> > - --
> > Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> > Phone: +1 650 872 2425 San Bruno, CA 94066
> > Cell: +1 650 302 2405 United States
> > Fax:+1 650 872 2417
> > -BEGIN PGP SIGNATURE-
> > Version: GnuPG v1.2.3 (GNU/Linux)
> >
> > iD8DBQFAmSumjqGXBvRToM4RAo
Re: [GENERAL] Verhindern, dass im Mehrbenutzerbetrieb mit veralteten Daten gearbteitet wird
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bastian, die liste [EMAIL PROTECTED] is 99% in englisch. Deine Anfrage hat wesentlich mehr Aussicht auf Erfolg wenn die sie in englisch stellst. Zum Problem: kannst du die Datensätze nicht eindeutig identifizieren ? Um erfolgreich einen Lock zu setzen muss die Tabelle einen primary key haben, der dann eindeutig ist. Damit hättest du zumindest nicht das Problem daß ein Datensatz gelöscht wird der "zwar die gewünschte Nr hat, aber nicht mehr der Datensatz ist". Du kannst explizite rowlocks setzen, aber wie gesagt es ist einfacher die rows eindeutig zu identifizieren, dann kann der Benutzer mit der veralteten Ansicht zwar noch löschen, der delete geht dann aber ins leere. Nur ein update auf den veralteten datensatz wird einen fehler erzeugen, den deine Applikation dann mit entsprechender fehlermeldung abfangen muss. UC On Tuesday 04 May 2004 01:50 am, Bastian wrote: > Hi, > > ich benutze PHP und PostgreSQL. > Folgendes Problem: Eine Seite zeigt die Daten, die in einer Tabelle > der DB abgespeichert sind. Der Benutzer wählt dann einen Datensatz > aus, den er gerne bearbeiten oder löschen möchte. Auf der nächsten > Seite wird die Aktion dann ausgeführt. > Es ist möglich LOCKS zu setzen, um zu verhindern, dass sich 2 DELETES > bzw. UPDATES in die Quere kommen, bzw. werden implizit gesetzt. Aber > wenn ein Benutzer sich auf Seite 1 also auf der Tabellenabfrageseite > befindet, und ein anderer Benutzer während dessen die Daten in der > Tabelle verändert, so arbeitet Benutzer 1 mit den alten Daten und > löscht dann z.B. einen Datensatz der zwar die gewünschte Nr hat, aber > nicht mehr der Datensatz ist, den er eigentlich löschen wollte. > Ist es vielleicht möglich per Abfrage zu prüfen, ob gerade ein LOCK > gesetzt ist? > > Bastian > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] - -- UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAmSumjqGXBvRToM4RAoJTAJ42xi25CzUpgnbjUrEJutTCF9+OxQCbBzSh BxpIwG8QEsIPxQUp39U5Fa8= =7BB1 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
