Re: [GENERAL] Postmaster cannot start
""Chun Yit(Chronos)"" <[EMAIL PROTECTED]> wrote > postmaster give me error every time i try to start it > LOG: redo starts at A/46315F50 > PANIC: btree_delete_page_redo: uninitialized right sibling > So the last resort I can think of is to use pg_resetxlog to pass the startup failure -- but no gaurantee that your database is still consistent after that :-(. So before you do that, make a copy of your database for backup. Maybe a deeper look into the problem is not needed, since 7.4.5 is too old, and try to use some newer version. Regards, Qingqing ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postmaster cannot start
"Chun Yit\(Chronos\)" <[EMAIL PROTECTED]> writes: > PANIC: btree_delete_page_redo: uninitialized right sibling > LOG: startup process (PID 5043) was terminated by signal 6 > LOG: aborting startup due to startup process failure That's pretty ugly :-(. I think your only hope to get out of it is to use pg_resetxlog. After that, you will undoubtedly need to reindex pg_class in the problem database. After that, if you're very lucky, you'll be able to pg_dump. If you can get a clean dump, then initdb and reload --- it would not be wise to keep using the database. I'd suggest first taking a filesystem backup of the $PGDATA directory as it stands, so you can get back to where you are if this fails to work. Assuming you can get back up and running, you should look into (a) why you are running an old, known-buggy PG release, and (b) whether you have flaky hardware, as per comments already made upthread. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postmaster cannot start
"Qingqing Zhou" <[EMAIL PROTECTED]> writes: But not sure why it reports the following error message (which looks like a post-commit cleanup caused error): DEBUG: AbortCurrentTransaction PANIC: cannot abort transaction 14135438, it was already committed I think this is an artifact of the fact that VACUUM FULL commits its own transaction before it starts the final index cleanup pass. We ought to think of a better way to handle that sometime. I don't recall having seen a PANIC like this reported before, but on reflection it seems like this would be guaranteed to happen for any ERROR condition occurring during that last pass. An error there would be pretty improbable, but surely not impossible. I have check my past postgresql log file, i did not see this error come out (i did vacuum full every night). As for the OP's problem, it seems pretty suspicious that we got through one cycle of vacuuming pg_class_relname_nsp_index and then the second one failed with what seems to be a bad block link. If that bad link was there before, why didn't it fail the first time through? I'm wondering about flaky hardware ... Any tool to check am i having a flaky hardware? Regards Beh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postmaster cannot start
4) how can i solve this problem? The base table pg_class should be ok(pg_class_oid_ind indicates both have the same cardinality). Try to reindex pg_class as the superuser. but not i not be able to reindex the table because i cannot start the postmaster. postmaster give me error every time i try to start it this is the error msg DEBUG: found "/usr/local/pgsql/bin/postgres" using argv[0] LOG: could not create IPv6 socket: Address family not supported by protocol DEBUG: max_safe_fds = 985, usable_fds = 1019, already_open = 5 DEBUG: found "/usr/local/pgsql/bin/postmaster" using argv[0] LOG: could not resolve "localhost": Temporary failure in name resolution LOG: database system was interrupted while in recovery at 2006-05-15 04:14:39 MYT HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. LOG: checkpoint record is at A/46315F10 LOG: redo record is at A/46315F10; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 14135366; next OID: 51809028 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at A/46315F50 PANIC: btree_delete_page_redo: uninitialized right sibling LOG: startup process (PID 5043) was terminated by signal 6 LOG: aborting startup due to startup process failure Regards Beh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Friendly catalog views
On Mon, 2006-05-15 at 17:12 -0500, Jim C. Nasby wrote: > On Wed, May 10, 2006 at 02:08:46PM -0300, Bruno Almeida do Lago wrote: > > Sometime ago I saw a project with the purpose of creating Oracle views over > > the PostgreSQL catalog. > > > > I can't remember/find it anymore. Do you know if it's still available? > > > > Is there any other project aiming to create friendly catalog views? > > Not oracle views, but there is > http://pgfoundry.org/projects/newsysviews/ . This looks very interesting and useful... However, the documentation notes that it was hoped to get included into 8.1, but I presume that it didn't make it in there. Are there any indications as to whether it might get included in 8.2? -- Russ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] GUI Interface
You should try the new MacBook Jim! The 17" is out now. I used to have the 17" PB G4, but now I have a 15" MacBook Pro with 2GB of RAM. It's a sweet machine! Very fast too. 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 On May 15, 2006, at 4:22 PM, Jim C. Nasby wrote: On Fri, May 12, 2006 at 02:02:27PM -0400, Bruce Momjian wrote: Scott Marlowe wrote: On Fri, 2006-05-12 at 11:39, Joshua D. Drake wrote: I am regularly dealing with customers, and specifically developers that are running Linux+PostgreSQL on the server... but their desktop of choice is MacOSX. And to follow up on this, I just saw that Tom Lane, Buddha guru of PostgreSQL runs a Powerbook ;) Now now, he didn't say it was his. Could be his mother's... (ducks to avoid tomato thrown by Bruce...) I remember he got some type of Mac laptop while he was at Greatbridge, but when they went bust, the parent company didn't know what to do with a Mac, so they let him keep it. (I didn't use a laptop at the time.) Might be the same one. Anyway, it would make an interesting reason for choosing a Mac. I can see the commerical now. :-) I actually use a laptop running XP. I got it for the Win32 port, and because I use putty/ssh, Mozilla, and Gaim 99% of the time, it doesn't matter what OS I use. I could install a unix on it, but there seems to be no need because all my unix work is done on my server via ssh. I was prepared to hate OS X and it's silly one-button-ness, but I bought a 17" powerbook anyway, because to me that's what a laptop should be; plenty of screen real estate but also thin and light. Then I started using OS X and came to realize (to quote from Jurasic Park) "this is unix, I know this!" Granted, when it comes to administration it's a fair bit different, but I think OS X is about the best desktop environment a unix geek could ask for. All the tools you've grown accustomed to are right there and work just fine. No need to even ssh anywhere for them. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] error handling in cast functions for user defined types
Don Y <[EMAIL PROTECTED]> writes: > I'm writing a set of casts to/from various user defined > types. As is unexpected, there are cases where one > data type doesn't neatly map to another (for certain > values). In these cases I emit an INVALID_PARAMETER_VALUE > or OUT_OF_RANGE error -- depending on the situation. > But, should I also PG_RETURN_NULL()? elog(ERROR) doesn't return to your function --- think of it as being like exit(). So it's pointless to code anything after it. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] error handling in cast functions for user defined types
Hi, I'm writing a set of casts to/from various user defined types. As is unexpected, there are cases where one data type doesn't neatly map to another (for certain values). In these cases I emit an INVALID_PARAMETER_VALUE or OUT_OF_RANGE error -- depending on the situation. But, should I also PG_RETURN_NULL()? I note the cast of int4's to int2's signal similar errors -- *but* returns "(int2) value". Is the return value just *ignored* when the error is signaled? Thanks! --don ---(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] parameter passing from java program
On Mon, 15 May 2006, dfx wrote:
I am spending about 10 hours on little problem: to pass a parameter (text)
to a PostgreSQL (v. 8.1 on Win2000). The
relevant code is:
java side:
private CallableStatement cs;
cs = conn.prepareCall(INSERT_CONGRESSO);
cs.setString(1, itemCongresso.getCongresso()); //return a String
cs.execute();
...
public static final String INSERT_CONGRESSO = "{SELECT ins_congressoa(?)}";
You're mixing your calling syntax. You need to write either:
conn.prepareCall("{ ? = call ins_congressoa(?)}");
or
conn.prepareStatement("SELECT ins_congressoa(?)");
Kris Jurka
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] pg_dump after transaction id wraparound failure
And I just demonstrated it on a test 7.4.7 database here (Debian 7.4.7-6 to be precise). test=# select ctid, xmin, xmax, cmin, cmax, * from test; ctid |xmin| xmax | cmin | cmax | x ---++--+--+--+--- (0,1) |541 |0 |0 |0 | 1 (0,2) | 2147484153 |0 |0 |0 | 2 (0,3) | 2147484183 |0 |0 |0 | 3 (3 rows) ### Row is still there... test=# select ctid, xmin, xmax, cmin, cmax, * from test; ctid |xmin| xmax | cmin | cmax | x ---++--+--+--+--- (0,2) | 2147484153 |0 |0 |0 | 2 (0,3) | 2147484183 |0 |0 |0 | 3 (2 rows) ### OMG, row is gone test=# vacuum test; VACUUM test=# select ctid, xmin, xmax, cmin, cmax, * from test; ctid |xmin| xmax | cmin | cmax | x ---++--+--+--+--- (0,1) | 2 |0 |0 |0 | 1 (0,2) | 2147484153 |0 |0 |0 | 2 (0,3) | 2147484183 |0 |0 |0 | 3 (3 rows) ### Yay row is back test=# select version(); version --- PostgreSQL 7.4.7 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.5 (Debian 1:3.3.5-12) (1 row) Have a ncie day, -- To Clarify - So the consensus is to vacuum the entire database? This should take care of any possible loss of data. Then after this I should pg_dump? Thanks again --Mike ---(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] crashed winxp
Hi guys i have a database on WINXP machine unfotunatly, the OS crashed . i want to retriev my data how can this be done. Regards Yahoo! Messenger with Voice. PC-to-Phone calls for ridiculously low rates.
Re: [GENERAL] GUI Interface
On Fri, May 12, 2006 at 02:02:27PM -0400, Bruce Momjian wrote: > Scott Marlowe wrote: > > On Fri, 2006-05-12 at 11:39, Joshua D. Drake wrote: > > > > I am regularly dealing with customers, and specifically developers that > > > > are running Linux+PostgreSQL on the server... but their desktop of > > > > choice is MacOSX. > > > > > > And to follow up on this, I just saw that Tom Lane, Buddha guru of > > > PostgreSQL runs a Powerbook ;) > > > > Now now, he didn't say it was his. Could be his mother's... > > > > (ducks to avoid tomato thrown by Bruce...) > > I remember he got some type of Mac laptop while he was at Greatbridge, > but when they went bust, the parent company didn't know what to do with > a Mac, so they let him keep it. (I didn't use a laptop at the time.) > Might be the same one. > > Anyway, it would make an interesting reason for choosing a Mac. I can > see the commerical now. :-) > > I actually use a laptop running XP. I got it for the Win32 port, and > because I use putty/ssh, Mozilla, and Gaim 99% of the time, it doesn't > matter what OS I use. I could install a unix on it, but there seems to > be no need because all my unix work is done on my server via ssh. I was prepared to hate OS X and it's silly one-button-ness, but I bought a 17" powerbook anyway, because to me that's what a laptop should be; plenty of screen real estate but also thin and light. Then I started using OS X and came to realize (to quote from Jurasic Park) "this is unix, I know this!" Granted, when it comes to administration it's a fair bit different, but I think OS X is about the best desktop environment a unix geek could ask for. All the tools you've grown accustomed to are right there and work just fine. No need to even ssh anywhere for them. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Friendly catalog views
On Wed, May 10, 2006 at 02:08:46PM -0300, Bruno Almeida do Lago wrote: > Sometime ago I saw a project with the purpose of creating Oracle views over > the PostgreSQL catalog. > > I can't remember/find it anymore. Do you know if it's still available? > > Is there any other project aiming to create friendly catalog views? Not oracle views, but there is http://pgfoundry.org/projects/newsysviews/ . -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] understanding explain data
On Wed, May 10, 2006 at 11:00:14PM +1000, chris smith wrote: > On 5/10/06, Alban Hertroys <[EMAIL PROTECTED]> wrote: > >Sim Zacks wrote: > >> Something such as: with this explain data, adding an index on table tbl > >> column A would drastically improve the efficiency. Or at least an > >> application that would say, the least efficient part of your query is on > >> this part of the code so that you could more easily figure out what to > >> do about it. > > > >The latter part is the most useful IMO, optimizing usually needs a > >(human) brain to put things into the right perspective. Adding an index > >can speed up your queries only so much, a more optimal data presentation > > (like moving calculations to insert/update instead of select) can do a > >lot more sometimes. > > > >It looks like something like that shouldn't be too hard to write... > >Maybe it even does exist already. Personally I'd prefer a command line > >tool ;) > >It would help if you can pipe the output of explain analyze to an > >external tool from within psql. > > I've thought about writing a similar tool.. I'm about 30% of the way :) > > It's written in python and can grab the queries out of the db logs.. > but the harder part is working out the explain output.. also taking in > to consideration an index might be available but not the best option > for the query. > > I guess the easiest way to check is to have the script turn seq scans > off when it runs explain and go from there. > > If anyone's interested in helping it go further contact me off list > (can put it on pgfoundry.org and go from there if need be). There's been some discussion about allowing EXPLAIN to produce machine-readable output, probably in XML. I agree that it would be a lot easier if there was some way you could take explain output and plug it into some tool that would present an easier to understand format. I tend to shy away from helping people that post EXPLAIN just because it's such a PITA to read (especially if their email program word-wrapped the output). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Concatenate WAL contents
On Wed, May 10, 2006 at 11:55:49AM +0200, Houssais Hugues wrote: > Hi, > > In order to unify many distant Postgres databases, we decided to use WAL > archiving. The idea is to concatenate a distant WAL archive at the > beginning of a local WAL archive before launching PITR restore. > > Does anyone know how to concatenate WAL contents, i.e. decode and encode > WAL files using the address of backup files (aaa in x.aaa.backup > files)? It's not possible to do multi-master WAL-based replication, because it describes table changes at a binary level. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Exporting postgres query to CSV
On Tue, May 09, 2006 at 08:57:57AM -0700, John Purser wrote: > > I am running postgres 7.4.7 on debian sarge. > First: > psql -U -o --pset format=unaligned > --pset fieldsep=',' -c '' -d > > I think that will give you the output you were after assuming you're > scripting psql and that you replace the values in <> with appropriate > values. The syntax is slightly different from the psql command line. > > Second: > man psql is your friend. Third: 7.4.7 is ancient and suffers from a number of data loss bugs. If you need to stick with 7.4 you should at least be running the most current version. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Unify distant Postgres databases
On Tue, May 09, 2006 at 10:10:50AM +0200, Houssais Hugues wrote: > Each distant database works on its own domain of data. Then no conflict > should happen during updates. > One thing I have not specified is that the distant databases don't handle > global data but only data collected at the local level. > Slony-1 seems not to provide replication from multi-partial databases to one > global database. But maybe I'm wrong... Can you tell me more about this use > of Slony? Slony-I is in fact single-master. Slony-II will be multi-master, but it's also vaporware right now. What you can do is setup different tables for each set of local data and replicate all of those back to a central location. That location can then merge everything together and push it back out. You can also just have each location be a master for it's local data and have all the other locations subscribe to it. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] What is your favorite front end for user interaction to postgresql databases?
On Mon, May 08, 2006 at 04:17:53PM -0700, Casey Duncan wrote: > From personal experience (and others will disagree), I find putting > logic in the database to be a bad idea. I only use stored procedures > for triggers and the like, and I try to avoid those whenever possible. > > Here are my reasons why: > > - I don't like the PL/pgSQL language (yes there are alternatives, but > they have their own drawbacks) Huh? Just use whatever language you're already writing in. > - It's complex to test and upgrade (we actually wrote non-trivial > infrastructure to automate both) Uhm... CREATE OR REPLACE FUNCTION. Unless you're changing parameters it works fine. As for testing, I fail to see how it's more difficult than testing the same thing using external code. I will grant that unit testing is harder though, since you don't have as many opportunities to hook into the code, but if you create a set of known test data it's not all that difficult. > - It's difficult to debug (compared to external languages like python > or java) > - It's difficult to profile, therefore getting good performance can > be difficult Actually, I think there's a commercial product that allows you to do both, but I'm not sure. It would certanly be nice if it was built in. > I had a very complex system coded in stored procedures that performed > poorly and was hard to maintain. It's now refactored into java/ > hibernate code that's simpler to understand, performs much better and > is easy to extend and maintain. Of course that's just my particular > case and obviously YMMV. If hibernate is performing better it's due to application design. A lot of times people try and approach database development the same way you'd approach procedural coding, which is a bad idea. Hibernate and other products go to great lengths (ie: caching) to try and make procedural coding techniques work well on databases. > Stored procs could make a lot of sense if you have many different > clients accessing the db in different ways and you want to strictly > enforce business rules across all of them. I had no such requirements > in my case. You sure there won't every be any other apps hitting that database? :) Part of how Pervasive makes money is dealing with exactly that kind of attitude... "nothing else will ever have to communicate with this system". > In any case I would strongly recommend doing the simplest thing that > you can get away with. If your business rules can be fulfilled with > grants, views and constraints alone, use them. Or maybe more accurately, do what you have the expertise for. If you've got a good database developer on staff there's a lot to be said for putting stuff into procedures, especially if it's database-intensive. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgres vs. oracle for very large tables
On Mon, 2006-05-15 at 14:24, TJ O'Donnell wrote: > I've written some extensions to postgres to implement > chemical structure searching. I get inquiries about > the performance of postgres vs. oracle. This is a huge > topic, with lots of opinions and lots of facts. But, > today I got some feedback stating the opinion that: > "Postgres performance diminishes with large tables > (we’ll be going to upwards of hundreds of millions of rows)." Some points. 1: A lot of people's experience with PostgreSQL was last with version 7.1, or 6.5 or something like that. Even as far back as 7.4 performance was not all that great. with the release of 8.1, and with 8.2 on the horizon, I've found PostgreSQL to be on an even footing with Oracle. And for a lot of what I do with databases, even 7.4 is a fair match. 2: Every database gets diminishing performance with larger tables. The data can't fit in memory, you have to scan your disks for more info, less of the select sets can fit into cache memory, and so on. I know the basic gist of this is that postgresql slows down more than oracle. My point is that a lot of people assume Oracle is fast, but don't test it, and do test PostgreSQL. When PostgreSQL slows down they use Oracle, assuming it's gotta be faster. Sometimes it is, sometimes it isn't. 3: Your usage patterns are EVERYTHING. We don't know what those are. Is this a reporting / data warehousing database? Or is this an airline reservation system? Big difference. 4: Nominally, the biggest limiting factor for both Oracle and PostgreSQL is the hardware you're running it on. They both tend to run well on similar hardware (multiple AMD 64 CPUs with lots of RAM and a really fast RAID array.) So, if Oracle is gonna cost you $120,000 a year to license, you get to put that into your PostgreSQL over and above what you'd put in your Oracle server, and, multiply that by how long you'll keep the system online. Three years is typical for a server. So, imagine 3x your oracle license fees in hardware. Think storage arrays with dozens or hundreds of drives under a server with dozens or CPUS and dozens of gigs of ram. 5: You're gonna need to load test this to see. You can get the oracle 10G express for free. I think that one handles multiple connections, but is limited to 2 gigs of data. Make up identical datasets in both databases, tune them, and see which is faster handling your load. > Is this pure speculation, opinion, known fact? > Does anyone know of measured performance of postgres > vs. oracle, specifically with very large tables? It's opinion, and probably somewhat informed, but we all have our biases. What I've found is that there are some things Oracle is quite fast at, and other things that it's not so fast at. And you can't publish a performance comparison of Oracle and anything else cause Oracle will sue you for breaching your user contract, which forbids such things. ---(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
[GENERAL] postgres vs. oracle for very large tables
I've written some extensions to postgres to implement chemical structure searching. I get inquiries about the performance of postgres vs. oracle. This is a huge topic, with lots of opinions and lots of facts. But, today I got some feedback stating the opinion that: "Postgres performance diminishes with large tables (we’ll be going to upwards of hundreds of millions of rows)." Is this pure speculation, opinion, known fact? Does anyone know of measured performance of postgres vs. oracle, specifically with very large tables? TJ O'Donnell www.gnova.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Where I find examples of pqtrace ???
On Sat, May 06, 2006 at 12:08:32AM -0300, Marcelo Fabiano da Silveira wrote: > Hi, > > I Have some questions of use PQtrace in Windows' systens > > 1- The implementation of PQtrace, is possible ONLY with non bloking > connections ??? > 2- Please, I need same samples of implementation of PQtrace. Since no one's replied... you might have better luck on pgsql-interfaces. Unfortunately I don't know very much about PQtrace... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] can't reindex a couple of tables
On Fri, May 05, 2006 at 12:41:56PM -0600, Brendan Duddridge wrote: > They both have a click_count column that we update with "update > attribute set click_count = click_count + 1;" and the same for the > attribute_value table. Postgres is getting hung up on any transaction > that attempts to update the click_count. I've vacuum analyzed both > tables and that worked fine. Now I tried to reindex them and Postgres > is just locking up and never finishing. I had to cancel the reindex. I'm sure it would finish if you gave it enough time. Why are you reindexing? > The attribute table has only 3434 rows in it. > The attribute_value table has only 548735 in it. > > Either one I try to reindex causes that Postgres connection to hang > until I cancel the reindex command. Try looking in pg_locks. Reindex needs an exclusive lock IIRC, so anything else that's hitting the table will have to finish before the reindex can start. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] GUI Interface
> -Original Message- > From: Tony Caduto [mailto:[EMAIL PROTECTED] > Sent: 13 May 2006 14:58 > To: Dave Page > Subject: Re: [GENERAL] GUI Interface > > All that page mentions is the weird quirks on win32, I am > sure you know what those are, i.e. windows suddenly lose > there size and position info and appear in the upper left > corner of the screen in a semi minimized state, there are others. > Fix the problems and I will remove the thing about the > quirks. Hi Tony, I have eventually found a bug which did what you described - when closing a minimised window, an incorrect size/position value was returned by wxWidgets and saved. This was certainly not a cross-platform quirk as you described it (I've seen the exact same bug in VB apps before now). This has now been fixed in SVN. Please state what other 'quirks' you are referring to, and if they are bugs I will fix them. If not, please update your site per your statement above. Regards, Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Erro ao tentar inserir um array de bytes
entendi, só que o problema é q estou usando
annotations do hibernate p/a gerar esse campo
@NotNull(message =
FramexMessages.FRAMEX_REQUIRED_FIELD) @Lob @Basic(fetch = FetchType.EAGER)
public byte[] getArquivoImagem() {
return arquivoImagem; }
alguém jah passou por esse problema, de usar um
campo bytea com annotations do hibernate ?
- Original Message -
From:
William Leite
Araújo
To: Rafael Alves
Sent: Monday, May 15, 2006 2:18 PM
Subject: Re: [GENERAL] Erro ao tentar
inserir um array de bytes
Ao que entendi, está tentando guardar
imagens no banco. Talvez o uso do tipo "bytea" resolva.
On 5/15/06, Rafael
Alves <
[EMAIL PROTECTED]> wrote:
Estou com problemas ao tentar inserir um array
de bytes em um campo do tipo oid.
Caused by:
org.springframework.jdbc.UncategorizedSQLException : Hibernate operation:
could not insert:
[br.com.atlantico.maragato.cadastrosauxiliares.model.ImagemTO];
uncategorized SQLException for SQL [insert into imagem (arquivo_imagem,
tipo, titulo, id) values (?, ?, ?, ?)]; SQL state [25P01]; error code [0];
Objetos Grandes não podem ser usados no modo de
efetivação automática (auto-commit).; nested exception is
org.postgresql.util.PSQLException: Objetos Grandes não podem ser usados no
modo de efetivação automática (auto-commit).at
org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:96)at
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:257)at
org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)at
org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:411)at
org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:370)at
org.springframework.orm.hibernate3.HibernateTemplate.persist(HibernateTemplate.java:719)at
br.com.atlantico.framex.dao.hibernate.GenericHibernateDAO.create(GenericHibernateDAO.java:70)at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)at
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)at
java.lang.reflect.Method.invoke(Unknown Source)at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:335)at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:181)at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)at
org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke(ThrowsAdviceInterceptor.java:118)...
27 more
essa é a
excessão gerada pelo meu código java.
CREATE TABLE imagem( id int8 NOT
NULL, arquivo_imagem oid NOT NULL, tipo int4 NOT
NULL, titulo varchar(100) NOT NULL, CONSTRAINT
imagem_pkey PRIMARY KEY (id), CONSTRAINT imagem_titulo_key UNIQUE
(titulo)) WITHOUT OIDS;ALTER TABLE imagem OWNER TO
postgres;--
William Leite AraújoEspecialista em
Geoprocessamento- UFMGBacharel em Ciêncida da
Computação - UFMGMSN: [EMAIL PROTECTED]
ICQ:222159351GTalk: [EMAIL PROTECTED]Yahoo: [EMAIL PROTECTED]Skype: william.bh
Re: [GENERAL] pg_dump after transaction id wraparound failure
On Mon, May 15, 2006 at 03:04:47PM -0400, Tom Lane wrote: > Martijn van Oosterhout writes: > > Vacuum will make anything that disappeared by wraparound in the last > > billion transactions reappear, so a databasewide vacuum should solve > > all your problems, no need to dump... > > Oh, I had forgotten about that. Did we establish that 7.4.* works that > way? I think so, but obviously I've been programming too many hours in > a row :-( Well, I demonstrated it for 8.1beta here: http://archives.postgresql.org/pgsql-general/2005-11/msg00097.php And I just demonstrated it on a test 7.4.7 database here (Debian 7.4.7-6 to be precise). test=# select ctid, xmin, xmax, cmin, cmax, * from test; ctid |xmin| xmax | cmin | cmax | x ---++--+--+--+--- (0,1) |541 |0 |0 |0 | 1 (0,2) | 2147484153 |0 |0 |0 | 2 (0,3) | 2147484183 |0 |0 |0 | 3 (3 rows) ### Row is still there... test=# select ctid, xmin, xmax, cmin, cmax, * from test; ctid |xmin| xmax | cmin | cmax | x ---++--+--+--+--- (0,2) | 2147484153 |0 |0 |0 | 2 (0,3) | 2147484183 |0 |0 |0 | 3 (2 rows) ### OMG, row is gone test=# vacuum test; VACUUM test=# select ctid, xmin, xmax, cmin, cmax, * from test; ctid |xmin| xmax | cmin | cmax | x ---++--+--+--+--- (0,1) | 2 |0 |0 |0 | 1 (0,2) | 2147484153 |0 |0 |0 | 2 (0,3) | 2147484183 |0 |0 |0 | 3 (3 rows) ### Yay row is back test=# select version(); version --- PostgreSQL 7.4.7 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.5 (Debian 1:3.3.5-12) (1 row) Have a ncie day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] pg_dump after transaction id wraparound failure
On Mon, 2006-05-15 at 14:07, Michael L. Boscia wrote: > On 5/15/06, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > This is one of those rare instances when I'd shut down the server and > > take a file system backup if I could. However, at 1.4 TB, that might be > > a bit difficult... :) > > What do I do with a filesystem backup after its done? If I copy and > restore somewhere won't I experience all the same issues? Well, imagine that things get worse (like that catalog corruption you mentioned)... If you have a file system backup you can at least get back to where you are right now. Better to have a huge, useless backup you don't need laying about than to be thinking 5 minutes after the vacuum "Man, I wish I had a file system backup..." ---(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] pg_dump after transaction id wraparound failure
On 5/15/06, Scott Marlowe <[EMAIL PROTECTED]> wrote: On Mon, 2006-05-15 at 13:48, Martijn van Oosterhout wrote: > On Mon, May 15, 2006 at 11:44:01AM -0700, Michael L. Boscia wrote: > > Hello- > > > > I currently have a 1.4 TB database (pg version 7.4.8) that is experiencing > > transaction id wraparound. I plan to pg_dump the database and initdb and > > reload. I may reload into an 8.1 database at this time. > > > > I am curious how to make sure that I indeed get all the data out of the > > tables from a pg_dump. I want to ensure that there would not be anything > > "hidden" by the transaction id wraparound failure. > > pg_dump obviously won't see anything that's hidden by wraparound > failure. Yes- this does make sense. > > Vacuum will make anything that disappeared by wraparound in the last > billion transactions reappear, so a databasewide vacuum should solve > all your problems, no need to dump... Are we positive that a database wide vacuum will solve this? I am concerned that while some transactions may "appear" some might still be affected by wraparound failure > > My current plan is selectively dump a portion of the data (most current, > > organized in tables by date ex xxx_2006_may) and reload. I plan to vaccuum > > each table before dumping. Please warn me if this is not enough. Any > > assistance is greatly appreciated. > > I'd say do the vacuum first, dumping the data isn't really helping much > in this case I think (although for backups it's obviously important). The only reason I would like to dump is to rid myself of a possibly corrupt pg_catalog tablespace and any issues that may arise out of this. This database was not cared for properly, in that vacuums were not properly scheduled, and I would like to get all the important data out and start over with a new initdb. I am just not sure that I would be getting all the data out. This is one of those rare instances when I'd shut down the server and take a file system backup if I could. However, at 1.4 TB, that might be a bit difficult... :) What do I do with a filesystem backup after its done? If I copy and restore somewhere won't I experience all the same issues? Thanks so much for your help -- Mike Boscia mikeboscia at gmail dot com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_dump after transaction id wraparound failure
Martijn van Oosterhout writes: > Vacuum will make anything that disappeared by wraparound in the last > billion transactions reappear, so a databasewide vacuum should solve > all your problems, no need to dump... Oh, I had forgotten about that. Did we establish that 7.4.* works that way? I think so, but obviously I've been programming too many hours in a row :-( regards, tom lane ---(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] pg_dump after transaction id wraparound failure
"Michael L. Boscia" <[EMAIL PROTECTED]> writes: > My current plan is selectively dump a portion of the data (most current, > organized in tables by date ex xxx_2006_may) and reload. I plan to vaccuum > each table before dumping. Please warn me if this is not enough. Any > assistance is greatly appreciated. Do NOT vacuum. There is zero potential upside and it might delete some of your data. It might be worthwhile to try to set the transaction counter back --- depends on whether you are more worried about losing the oldest data or newest data. Or you could take two sets of dumps and compare. regards, tom lane ---(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] pg_dump after transaction id wraparound failure
On Mon, May 15, 2006 at 08:48:54PM +0200, Martijn van Oosterhout wrote: > Vacuum will make anything that disappeared by wraparound in the last > billion transactions reappear, so a databasewide vacuum should solve > all your problems, no need to dump... Also, for future reference. If you have a lot of read-only tables (and judging by the names of your tables it looks like you might), then running VACUUM FREEZE on a table will put it in a state where you never have to vacuum it again (until you do an insert/update). Put another way, anything in a table at the point you run a VACUUM FREZE over it is protected from wraparound. Over time all tables will convert to this state, just an explicit freeze makes it quicker, for a 1.4TB data this might be important. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] pg_dump after transaction id wraparound failure
On Mon, 2006-05-15 at 13:48, Martijn van Oosterhout wrote: > On Mon, May 15, 2006 at 11:44:01AM -0700, Michael L. Boscia wrote: > > Hello- > > > > I currently have a 1.4 TB database (pg version 7.4.8) that is experiencing > > transaction id wraparound. I plan to pg_dump the database and initdb and > > reload. I may reload into an 8.1 database at this time. > > > > I am curious how to make sure that I indeed get all the data out of the > > tables from a pg_dump. I want to ensure that there would not be anything > > "hidden" by the transaction id wraparound failure. > > pg_dump obviously won't see anything that's hidden by wraparound > failure. > > Vacuum will make anything that disappeared by wraparound in the last > billion transactions reappear, so a databasewide vacuum should solve > all your problems, no need to dump... > > > My current plan is selectively dump a portion of the data (most current, > > organized in tables by date ex xxx_2006_may) and reload. I plan to vaccuum > > each table before dumping. Please warn me if this is not enough. Any > > assistance is greatly appreciated. > > I'd say do the vacuum first, dumping the data isn't really helping much > in this case I think (although for backups it's obviously important). This is one of those rare instances when I'd shut down the server and take a file system backup if I could. However, at 1.4 TB, that might be a bit difficult... :) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] GUI Interface
> -Original Message- > From: Florian G. Pflug [mailto:[EMAIL PROTECTED] > Sent: 15 May 2006 19:05 > To: Dave Page > Cc: Tino Wildenhain; Kenneth Downs; pgsql-general@postgresql.org > Subject: Re: [GENERAL] GUI Interface > > Here are the details of my configuration which shows the problem: > Ubuntu Dapper (i386 version, using gnome+metacity) > pgadmin-1.4.2, installed by doing: > > apt-get source pgadmin3 > dpkg-buildpackage -us -uc -b Oh, you managed to reproduce it again? Interesting it's on Ubuntu again... Anyhoo, I've got me an ISO, will build a VM shortly. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg_dump after transaction id wraparound failure
On Mon, May 15, 2006 at 11:44:01AM -0700, Michael L. Boscia wrote: > Hello- > > I currently have a 1.4 TB database (pg version 7.4.8) that is experiencing > transaction id wraparound. I plan to pg_dump the database and initdb and > reload. I may reload into an 8.1 database at this time. > > I am curious how to make sure that I indeed get all the data out of the > tables from a pg_dump. I want to ensure that there would not be anything > "hidden" by the transaction id wraparound failure. pg_dump obviously won't see anything that's hidden by wraparound failure. Vacuum will make anything that disappeared by wraparound in the last billion transactions reappear, so a databasewide vacuum should solve all your problems, no need to dump... > My current plan is selectively dump a portion of the data (most current, > organized in tables by date ex xxx_2006_may) and reload. I plan to vaccuum > each table before dumping. Please warn me if this is not enough. Any > assistance is greatly appreciated. I'd say do the vacuum first, dumping the data isn't really helping much in this case I think (although for backups it's obviously important). Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] pg_dump after transaction id wraparound failure
Hello- I currently have a 1.4 TB database (pg version 7.4.8) that is experiencing transaction id wraparound. I plan to pg_dump the database and initdb and reload. I may reload into an 8.1 database at this time. I am curious how to make sure that I indeed get all the data out of the tables from a pg_dump. I want to ensure that there would not be anything "hidden" by the transaction id wraparound failure. My current plan is selectively dump a portion of the data (most current, organized in tables by date ex xxx_2006_may) and reload. I plan to vaccuum each table before dumping. Please warn me if this is not enough. Any assistance is greatly appreciated. Mike-- Mike Bosciamikeboscia at gmail dot com
Re: [GENERAL] GUI Interface
Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Florian G. Pflug Sent: 14 May 2006 18:50 To: Tino Wildenhain Cc: Kenneth Downs; pgsql-general@postgresql.org Subject: Re: [GENERAL] GUI Interface It only happens in pgAdmin III, though, so it must be some strange interaction between wxWindows and GTK. I believe that the window manager is part of the problem too, because I've used KDE (together with kwin) for the last few years, and while it had this freezing-problem initially, it went away with some update. I believed that a wx or pgadmin update solved this, but now I'm using Gnome (together with metacity), and the problem is back... :-( I'm unable to reproduce this problem with pgAdmin SVN-trunk built with wxGTK 2.6.3 on Slackware-current with KDE. Can anyone else reproduce this bug with a 1.4 build of pgAdmin with wx 2.6.x? Here are the details of my configuration which shows the problem: Ubuntu Dapper (i386 version, using gnome+metacity) pgadmin-1.4.2, installed by doing: apt-get source pgadmin3 dpkg-buildpackage -us -uc -b greetings, Florian Pflug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump index/constraint creation order
On May 14, 2006, at 12:27 AM, Ed L. wrote: While watching a 9-hour 60GB network load from 7.4.6 pg_dump into 8.1.2, I noticed the order in which indices and constraints are created appears to be their creation order. If you use the 8.1.2 pg_dump to make the dump from your 7.4.6 DB, what is the order like? I suspect it would be more to what you're expecting. In general, you should use the pg_dump corresponding to the version into which you are loading for best results. smime.p7s Description: S/MIME cryptographic signature
[GENERAL] Erro ao tentar inserir um array de bytes
Estou com problemas ao tentar inserir um array de bytes em um campo do tipo oid. Caused by: org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not insert: [br.com.atlantico.maragato.cadastrosauxiliares.model.ImagemTO]; uncategorized SQLException for SQL [insert into imagem (arquivo_imagem, tipo, titulo, id) values (?, ?, ?, ?)]; SQL state [25P01]; error code [0]; Objetos Grandes não podem ser usados no modo de efetivação automática (auto-commit).; nested exception is org.postgresql.util.PSQLException: Objetos Grandes não podem ser usados no modo de efetivação automática (auto-commit).at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:96)at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:257)at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:411)at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:370)at org.springframework.orm.hibernate3.HibernateTemplate.persist(HibernateTemplate.java:719)at br.com.atlantico.framex.dao.hibernate.GenericHibernateDAO.create(GenericHibernateDAO.java:70)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)at java.lang.reflect.Method.invoke(Unknown Source)at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:335)at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:181)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)at org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke(ThrowsAdviceInterceptor.java:118)... 27 more essa é a excessão gerada pelo meu código java. CREATE TABLE imagem( id int8 NOT NULL, arquivo_imagem oid NOT NULL, tipo int4 NOT NULL, titulo varchar(100) NOT NULL, CONSTRAINT imagem_pkey PRIMARY KEY (id), CONSTRAINT imagem_titulo_key UNIQUE (titulo)) WITHOUT OIDS;ALTER TABLE imagem OWNER TO postgres;
Re: [GENERAL] [PORTS] Compiling on 8.1.3 on Openserver 5.05
Mark Campbell wrote: > Hi All > > I downloaded version 7.3 and compiled that. > > First I had to compile newer versions of "m4" and "bison" > > At least I now have an up and running postgres server > > Thanks for all the help > > Mark Campbell > > > Confidentiality Notice: http://ucs.co.za/conf.html ^^^ Now there is a great idea! Instead of a huge block of legal text at the bottom of the email, just link to the content. Seems that would keep the lawyers and the email readers happy. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Getting information about sequences
Martijn van Oosterhout writes: > On Mon, May 15, 2006 at 10:44:15AM -0400, Tom Lane wrote: >> I haven't been able to think of a way to do that, unless you want to >> assume the existence of a plpgsql helper function. There's an open >> request to list last_values in psql's "\ds", and it'd be real nice >> to be able to do it all in one query for that. > Long term I see a few ways of dealing with this: > [ ideas snipped ] Yeah (actually the point about the update-in-place code being relevant had just occurred to me too). However, none of these are really desirable solutions from psql's point of view, because they could only work in 8.2 and later (or whenever we implemented them). It'd be nicer if \ds still worked against back-rev servers, which means we need a solution that works with the current server API. I'm thinking that psql will need to pull the main \ds query result, and then manually issue a select against each of the sequences (ick). On the other hand this may be the best thing anyway, since it's entirely likely that some of those selects would fail for permissions reasons, and we don't want the whole \ds operation to go down in flames just because you don't have select rights on one sequence. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Getting information about sequences
On Mon, May 15, 2006 at 10:44:15AM -0400, Tom Lane wrote: > I haven't been able to think of a way to do that, unless you want to > assume the existence of a plpgsql helper function. There's an open > request to list last_values in psql's "\ds", and it'd be real nice > to be able to do it all in one query for that. Long term I see a few ways of dealing with this: - Pull that idea of storing all sequences in one table off the shelf and implement it. The new heap up-date-in-place function may come in handy there. - Find a way of allowing functions to be declared inline, to avoid creating system functions continuously. I don't know if there's precedent for this. - Create a function called: gettable(reloid) returns record, that takes a relation OID and returns all the records in it. If all the tables you're dealing with have a similar structure, you can use this to iteratoe over, sequences just being a special case. There's probably more, but that's all I can think of right now. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Getting information about sequences
Martijn van Oosterhout writes: > On Mon, May 15, 2006 at 03:55:49PM +0300, Forums @ Existanze wrote: >> I have been able to get the sequence names, but I can get their values all >> in one query. > Select sequence.last_value; The hard part of the request is to do it "all in one query", though, ie somehow show select relname, last_value from pg_class where relkind = 'S'; I haven't been able to think of a way to do that, unless you want to assume the existence of a plpgsql helper function. There's an open request to list last_values in psql's "\ds", and it'd be real nice to be able to do it all in one query for that. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Bug in ordered views?
Nis Jorgensen <[EMAIL PROTECTED]> writes: > Try removing the DISTINCT ON from your view - that should make things > clearer to you. When t.approved is true, the row is joined to all rows > of the datum table satisfying the criteria. The sort order you specify > does not guarantee a unique ordering of the rows, which explains the > inconsistency between the two cases. More specifically, look at this: select t.test_id,d.projekt_id,d.datum,t.datum, t.id, t.approved, t.test_text FROM datum d JOIN test t ON (t.projekt_id = d.projekt_id OR t.approved IS TRUE) AND t.datum <= d.datum ORDER BY t.test_id DESC, d.projekt_id DESC, d.datum DESC, t.datum DESC; test_id | projekt_id | datum| datum| id | approved | test_text -+++++--+--- 2 | 2 | 2006-05-16 | 2006-05-16 | 4 | f| new 2 | 2 | 2006-05-16 | 2006-05-15 | 2 | t| old 2 | 2 | 2006-05-15 | 2006-05-15 | 2 | t| old 2 | 1 | 2006-05-16 | 2006-05-15 | 2 | t| old 2 | 1 | 2006-05-15 | 2006-05-15 | 2 | t| old 1 | 2 | 2006-05-16 | 2006-05-15 | 1 | t| old 1 | 2 | 2006-05-16 | 2006-05-15 | 3 | f| new * 1 | 2 | 2006-05-15 | 2006-05-15 | 3 | f| new * 1 | 2 | 2006-05-15 | 2006-05-15 | 1 | t| old 1 | 1 | 2006-05-16 | 2006-05-15 | 1 | t| old 1 | 1 | 2006-05-15 | 2006-05-15 | 1 | t| old (11 rows) The two rows I've marked with * are identical in all the columns that are used in the DISTINCT ON and ORDER BY clauses, which means it's unspecified which one you get out of the DISTINCT ON. I'm not entirely sure why adding the test_id condition changes the results, but it may be an artifact of qsort() behavior. Anyway you need to constrain the ORDER BY some more to ensure you get well-defined results from the DISTINCT ON. regards, tom lane ---(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] GUI Interface
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kenneth DownsSent: 15 May 2006 15:14To: pgsql-general@postgresql.orgSubject: Re: [GENERAL] GUI Interface Kenneth Downs wrote: Dave Page wrote: kernel 2.6.8KDE 3.3.2gtk+ 2.4.14pgadmin3 1.2.2The window manager would be whatever the default for KDE is. OK, the first thing you really should do is upgrade to pgAdmin 1.4.2 and (if building form source) wxWidgets 2.6.3. There have been *loads* of fixes since 1.2.x in pgAdmin and wxWidgets 2.5.x which is what 1.2 was normally built against.I'm upgrading now to 1.4.1, that's the latest package available for gentoo. My final note on this thread unless and until I can reproduce the problem.pgadmin3 v 1.4 looks very nice! I like the ability to pick another database, and the helpful hints are a leap forward over previous versions. Good job! Thanks :-) Regards, Dave.
Re: [GENERAL] GUI Interface
Kenneth Downs wrote: Dave Page wrote: kernel 2.6.8 KDE 3.3.2 gtk+ 2.4.14 pgadmin3 1.2.2 The window manager would be whatever the default for KDE is. OK, the first thing you really should do is upgrade to pgAdmin 1.4.2 and (if building form source) wxWidgets 2.6.3. There have been *loads* of fixes since 1.2.x in pgAdmin and wxWidgets 2.5.x which is what 1.2 was normally built against. I'm upgrading now to 1.4.1, that's the latest package available for gentoo. My final note on this thread unless and until I can reproduce the problem. pgadmin3 v 1.4 looks very nice! I like the ability to pick another database, and the helpful hints are a leap forward over previous versions. Good job! begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(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] Getting information about sequences
On Mon, May 15, 2006 at 03:55:49PM +0300, Forums @ Existanze wrote: > Hello all, > > I would like to ask if anyone can explain or point me to some sort of query > that would get the sequences for a database along with the current value > they contain. > > I have been able to get the sequence names, but I can get their values all > in one query. From the documentation I can't use currval() because it needs > setval() to precede it in the same session. Select sequence.last_value; It's not guarenteed to give a value that unique across users, that's what currval/nextval are for. For if you just want an idea of where it's up to, this will do. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Postmaster cannot start
"Qingqing Zhou" <[EMAIL PROTECTED]> writes: > But not sure why it reports the following error > message (which looks like a post-commit cleanup caused error): > DEBUG: AbortCurrentTransaction > PANIC: cannot abort transaction 14135438, it was already committed I think this is an artifact of the fact that VACUUM FULL commits its own transaction before it starts the final index cleanup pass. We ought to think of a better way to handle that sometime. I don't recall having seen a PANIC like this reported before, but on reflection it seems like this would be guaranteed to happen for any ERROR condition occurring during that last pass. An error there would be pretty improbable, but surely not impossible. As for the OP's problem, it seems pretty suspicious that we got through one cycle of vacuuming pg_class_relname_nsp_index and then the second one failed with what seems to be a bad block link. If that bad link was there before, why didn't it fail the first time through? I'm wondering about flaky hardware ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postmaster cannot start
""Chun Yit(Chronos)"" <[EMAIL PROTECTED]> wrote > saw from the log file, it's possible that server crash during > vacuum process... > > Question : > 1) what happen to my database server? what the error meaning? > It looks like index "pg_class_relname_nsp_index" (which is an index on pg_class) is corrupted. But not sure why it reports the following error message (which looks like a post-commit cleanup caused error): DEBUG: AbortCurrentTransaction PANIC: cannot abort transaction 14135438, it was already committed > 2) it is my daily maintenance routine causing that? No. > 3) it is ok to run vacuum full every day ? Yes. There is no apparent bad side effect of vacuum full except that it would block other accesses on the target table. But since you do it per day(guess you do it on a nightly schedule), so this should not be a problem. > 4) how can i solve this problem? > The base table pg_class should be ok(pg_class_oid_ind indicates both have the same cardinality). Try to reindex pg_class as the superuser. Regards, Qingqing ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Bug in ordered views?
Sebastian Böck wrote: > Hello all, > > I think I found a little but annoying bug in views when ordering is > involved. First, my version of Postgres: > > PostgreSQL 8.1.3 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) > 3.4.4 [FreeBSD] 20050518 > > Please try the following: > > CREATE TABLE datum ( > projekt_id INTEGER NOT NULL, > datum DATE NOT NULL, > UNIQUE (projekt_id, datum) > ) WITHOUT OIDS; > > CREATE TABLE test ( > id SERIAL PRIMARY KEY, > projekt_id INTEGER NOT NULL, > datum DATE NOT NULL, > approved BOOLEAN NOT NULL DEFAULT FALSE, > test_id INTEGER, > test_text TEXT > ) WITHOUT OIDS; > > CREATE OR REPLACE VIEW bug AS > SELECT DISTINCT ON (test_id,projekt_id,datum) > t.id, d.projekt_id, d.datum, t.approved, > t.test_id, t.test_text > FROM datum d > JOIN test t ON > (t.projekt_id = d.projekt_id OR t.approved IS TRUE) AND > t.datum <= d.datum > ORDER BY t.test_id DESC, d.projekt_id DESC, d.datum DESC, t.datum DESC; > > INSERT INTO datum (projekt_id,datum) VALUES (1,now()); > INSERT INTO datum (projekt_id,datum) VALUES (1,now()+'1d'::interval); > INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES > (1,now(),1,'old'); > INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES > (1,now(),2,'old'); > > UPDATE test SET approved = TRUE WHERE projekt_id = 1; > > INSERT INTO datum (projekt_id,datum) VALUES (2,now()); > INSERT INTO datum (projekt_id,datum) VALUES (2,now()+'1d'::interval); > INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES > (2,now(),1,'new'); > INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES > (2,now()+'1d'::interval,2,'new'); > > Now do a simple select: > > SELECT * FROM bug; > > id | projekt_id | datum| approved | test_id | test_text > +++--+-+--- > 4 | 2 | 16.05.2006 | f| 2 | new > 2 | 2 | 15.05.2006 | t| 2 | old > 2 | 1 | 16.05.2006 | t| 2 | old > 2 | 1 | 15.05.2006 | t| 2 | old > 3 | 2 | 16.05.2006 | f| 1 | new > 1 | 2 | 15.05.2006 | t| 1 | old > 1 | 1 | 16.05.2006 | t| 1 | old > 1 | 1 | 15.05.2006 | t| 1 | old > > And now constrain the above select: > > SELECT * FROM bug WHERE test_id = 1; > > id | projekt_id | datum| approved | test_id | test_text > +++--+-+--- > 1 | 2 | 16.05.2006 | t| 1 | old > 1 | 2 | 15.05.2006 | t| 1 | old > 1 | 1 | 16.05.2006 | t| 1 | old > 1 | 1 | 15.05.2006 | t| 1 | old > > Notice that the should be 1 line with test_text showing "new"! Try removing the DISTINCT ON from your view - that should make things clearer to you. When t.approved is true, the row is joined to all rows of the datum table satisfying the criteria. The sort order you specify does not guarantee a unique ordering of the rows, which explains the inconsistency between the two cases. /Nis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] GUI Interface
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kenneth DownsSent: 15 May 2006 14:24Cc: pgsql-general@postgresql.orgSubject: Re: [GENERAL] GUI Interface Dave Page wrote: kernel 2.6.8KDE 3.3.2gtk+ 2.4.14pgadmin3 1.2.2The window manager would be whatever the default for KDE is. OK, the first thing you really should do is upgrade to pgAdmin 1.4.2 and (if building form source) wxWidgets 2.6.3. There have been *loads* of fixes since 1.2.x in pgAdmin and wxWidgets 2.5.x which is what 1.2 was normally built against. I'm upgrading now to 1.4.1, that's the latest package available for gentoo. OK. Gentoo has no package named wxwidgets, but we do have wxGTK, which is at version 2.6.2. Does this make any sense? Yup, that's the GTK specific version - there's also wxMSW, wxMac etc. From here I will no longer consciously try to avoid the hang-up, hoping that if it continues to happen we'll find out sooner rather than later. OK, thanks. Regards, Dave.
Re: [GENERAL] GUI Interface
Dave Page wrote: kernel 2.6.8 KDE 3.3.2 gtk+ 2.4.14 pgadmin3 1.2.2 The window manager would be whatever the default for KDE is. OK, the first thing you really should do is upgrade to pgAdmin 1.4.2 and (if building form source) wxWidgets 2.6.3. There have been *loads* of fixes since 1.2.x in pgAdmin and wxWidgets 2.5.x which is what 1.2 was normally built against. I'm upgrading now to 1.4.1, that's the latest package available for gentoo. Gentoo has no package named wxwidgets, but we do have wxGTK, which is at version 2.6.2. Does this make any sense? >From here I will no longer consciously try to avoid the hang-up, hoping that if it continues to happen we'll find out sooner rather than later. begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Getting information about sequences
Hello all, I would like to ask if anyone can explain or point me to some sort of query that would get the sequences for a database along with the current value they contain. I have been able to get the sequence names, but I can get their values all in one query. >From the documentation I can't use currval() because it needs setval() to precede it in the same session. Any ideas? Best Regards, Fotis
Re: [GENERAL] GUI Interface
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Florian G. Pflug > Sent: 14 May 2006 18:50 > To: Tino Wildenhain > Cc: Kenneth Downs; pgsql-general@postgresql.org > Subject: Re: [GENERAL] GUI Interface > > It only happens in pgAdmin III, though, so it must be some > strange interaction between wxWindows and GTK. I believe that > the window manager is part of the problem too, because I've > used KDE (together with kwin) for the last few years, and > while it had this freezing-problem initially, it went away > with some update. I believed that a wx or pgadmin update > solved this, but now I'm using Gnome (together with > metacity), and the problem is back... :-( I'm unable to reproduce this problem with pgAdmin SVN-trunk built with wxGTK 2.6.3 on Slackware-current with KDE. Can anyone else reproduce this bug with a 1.4 build of pgAdmin with wx 2.6.x? Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Pass in variable from user???
Use 'EXECUTE' to dynamically build SQL:http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -MikeOn 13 May 2006 14:15:52 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED] > wrote:Using PL/PGSQL, I am trying to create a procedure to display the count of rows in any single table of a database. The End-user would pass in atable name and the prodecure would display the table name with the rowcount.I am able to hardcode the variable for table and get the appropriate results from my count function (see below), but cannot pass in avariable and have the function work. Any suggesstions???CREATE FUNCTION get_table_count(tablename text) RETURNS integer AS $$ DECLARE --tablename ALIAS FOR $1; rowcount INTEGER; BEGINSELECT INTO rowcount count(*) FROM tablename;RETURN rowcount; END;$$ LANGUAGE 'plpgsql';---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Pass in variable from user???
am 13.05.2006, um 14:15:52 -0700 mailte [EMAIL PROTECTED] folgendes: > Using PL/PGSQL, I am trying to create a procedure to display the count > of rows in any single table of a database. The End-user would pass in a > table name and the prodecure would display the table name with the row > count. > I am able to hardcode the variable for table and get the appropriate > results from my count function (see below), but cannot pass in a > variable and have the function work. Any suggesstions??? > > CREATE FUNCTION get_table_count(tablename text) RETURNS integer AS $$ > DECLARE > > --tablename ALIAS FOR $1; > > rowcount INTEGER; > BEGIN > > SELECT INTO rowcount count(*) FROM tablename; You can't do SL direct with such parameters, but you can use EXECUTE. Please read: http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Triggers in C - Segmentation Fault
The core file usually appears in the data directory of the backend. If you still can't find it, you can try attaching gdb to the running backend. After the connection has started, find the backend (not the postmaster) and attach using: gdb -p Once connected, type "cont" and proceed with the actions to trigger the segfault. When the segfault happens, gdb will catch it and you can type "bt" to see where you are... Have a ncie day, On Mon, May 15, 2006 at 11:47:24AM +0100, Chris Coleman wrote: > Hi, > > I have done the below command and restarted the server and built my > triggers with the -g command using gcc, but I cannot seem to find any > core files nor any reference to where postgres may place them. Where > would they normally appear? > > Cheers > Chris > > >Probably the easiest thing to do is make sure your functions are > >compiled with debugging and enable core dump by running "ulimit -S -c > >unlimited" before starting the server. You can then use gdb to > >pin-point where it dies... > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] trigger TOASTing quicker?
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Tom Lane) wrote: > Martijn van Oosterhout writes: > > I beleive you can set it to EXTERNAL, which it will always toast. > > I don't think that will help; if the overall row size is below the > threshold, the code is not going to pick it apart to see if anything > is saying "toast me anyway!". And it shouldn't do so IMHO; the overall > cost in cycles would be catastrophic, because most tables aren't going > to have such columns. > > There was discussion just yesterday of making the TOAST thresholds > more configurable, but I didn't see anyone stepping up with a > concrete proposal (much less volunteering to create a patch). > > regards, tom lane Well, I suppose I could blank pad the column :-) That would compress really well, too. Or is that exceptionally evil? tia, arturo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Triggers in C - Segmentation Fault
Hi, I have done the below command and restarted the server and built my triggers with the -g command using gcc, but I cannot seem to find any core files nor any reference to where postgres may place them. Where would they normally appear? Cheers Chris Probably the easiest thing to do is make sure your functions are compiled with debugging and enable core dump by running "ulimit -S -c unlimited" before starting the server. You can then use gdb to pin-point where it dies... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Bug in ordered views?
Hello all, I think I found a little but annoying bug in views when ordering is involved. First, my version of Postgres: PostgreSQL 8.1.3 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518 Please try the following: CREATE TABLE datum ( projekt_id INTEGER NOT NULL, datum DATE NOT NULL, UNIQUE (projekt_id, datum) ) WITHOUT OIDS; CREATE TABLE test ( id SERIAL PRIMARY KEY, projekt_id INTEGER NOT NULL, datum DATE NOT NULL, approved BOOLEAN NOT NULL DEFAULT FALSE, test_id INTEGER, test_text TEXT ) WITHOUT OIDS; CREATE OR REPLACE VIEW bug AS SELECT DISTINCT ON (test_id,projekt_id,datum) t.id, d.projekt_id, d.datum, t.approved, t.test_id, t.test_text FROM datum d JOIN test t ON (t.projekt_id = d.projekt_id OR t.approved IS TRUE) AND t.datum <= d.datum ORDER BY t.test_id DESC, d.projekt_id DESC, d.datum DESC, t.datum DESC; INSERT INTO datum (projekt_id,datum) VALUES (1,now()); INSERT INTO datum (projekt_id,datum) VALUES (1,now()+'1d'::interval); INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES (1,now(),1,'old'); INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES (1,now(),2,'old'); UPDATE test SET approved = TRUE WHERE projekt_id = 1; INSERT INTO datum (projekt_id,datum) VALUES (2,now()); INSERT INTO datum (projekt_id,datum) VALUES (2,now()+'1d'::interval); INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES (2,now(),1,'new'); INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES (2,now()+'1d'::interval,2,'new'); Now do a simple select: SELECT * FROM bug; id | projekt_id | datum| approved | test_id | test_text +++--+-+--- 4 | 2 | 16.05.2006 | f| 2 | new 2 | 2 | 15.05.2006 | t| 2 | old 2 | 1 | 16.05.2006 | t| 2 | old 2 | 1 | 15.05.2006 | t| 2 | old 3 | 2 | 16.05.2006 | f| 1 | new 1 | 2 | 15.05.2006 | t| 1 | old 1 | 1 | 16.05.2006 | t| 1 | old 1 | 1 | 15.05.2006 | t| 1 | old And now constrain the above select: SELECT * FROM bug WHERE test_id = 1; id | projekt_id | datum| approved | test_id | test_text +++--+-+--- 1 | 2 | 16.05.2006 | t| 1 | old 1 | 2 | 15.05.2006 | t| 1 | old 1 | 1 | 16.05.2006 | t| 1 | old 1 | 1 | 15.05.2006 | t| 1 | old Notice that the should be 1 line with test_text showing "new"! Did I miss anything or is it a bug? Sebastian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Help!
Hi to all! I am a student in Computer Science, and in Databases' Technology course I must to write a report on physical structures of DBMS's. I choosed PostgreSQL; I looked the sources, and I have understood the block structure. And relations? And databases? How are structured? Thanks!!! ---(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
[GENERAL] Pass in variable from user???
Using PL/PGSQL, I am trying to create a procedure to display the count of rows in any single table of a database. The End-user would pass in a table name and the prodecure would display the table name with the row count. I am able to hardcode the variable for table and get the appropriate results from my count function (see below), but cannot pass in a variable and have the function work. Any suggesstions??? CREATE FUNCTION get_table_count(tablename text) RETURNS integer AS $$ DECLARE --tablename ALIAS FOR $1; rowcount INTEGER; BEGIN SELECT INTO rowcount count(*) FROM tablename; RETURN rowcount; END; $$ LANGUAGE 'plpgsql'; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] GUI Interface
On Fri, 2006-05-12 at 07:31 -0500, Tony Caduto wrote: > Dave Page wrote: > > Only runs on Windows though doesn't it? > > > > Regards, Dave > > > > > Hardly a limitation since Linux and other Unix based operating systems > account for a trivial percent of the desktop market. I would guess that > more regular people (NON FOSS developers) access Postgresql from a win32 > Desktop not a Unix one. > When Linux gets above 20% it might make sense to make applications for > it, or if there was a thriving RAD IDE like Delphi. Mono is shaping up > and so is Lazarus, but > they are not there yet, and WXwidgets/Python etc is not productive at > all, and Java is slow. > By the way PGLA actually works OK with the latest version of WINE so it > can run on Linux, just not natively. > It runs like a dog in Wine and is extremely unstable in my experience. The only thing I run under Wine is IE6 and I only do that because I need to test webapps on it. As for the desktop market, it may be true the Windows accounts for the majority of the *entire* desktop market, but I'd be willing to bet that the profile of the desktop market containing customers who would want or need an RDBMS GUI would be very different. Everyone at my place of work who would have a need for such an app runs linux, which would currently put PG Lightning Admin out of the picture (which I might add is a Damn Shame because it looks really nice). > I like many people dual boot Linux and I can tell you I spend most of my > time in win32 because applications I need don't exist for Linux. > > Later, > -- Russ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Creating of User and Database
After you install postgres using 'root', create your postgres database cluster (initdb ) as "postgres" rather than "root". Your database files therefore should be owned by "postgres" not root. -- Husam http://firstdba.googlepages.com Martijn van Oosterhout wrote: > On Thu, May 11, 2006 at 10:35:12AM +0800, APSC, Patrick Chee Seng Onn wrote: > > Hi, > > > > I've just installed postgresql onto my system but unable to successfully > > add new users and databases. I would receive a error message: > > > > createuser: could not connect to database template1: FATAL user "root" > > does not exist > > > > createdb: could not connect to database template1: FATAL user "root" > > does not exist > > > > what is the error I am facing anyway? > > On installation the only user is "postgres". You don't say what system > you are running but the server should be configured so that running > createuser as the same user as the server should work. Once you've > created additional users you're set. > > You can use -U to set the user to connect as. > > Have a nice day, > -- > Martijn van Oosterhout http://svana.org/kleptog/ > > From each according to his ability. To each according to his ability to > > litigate. > > --MnLPg7ZWsaic7Fhd > Content-Type: application/pgp-signature > Content-Transfer-Encoding: base64 > Content-Disposition: inline; > filename="signature.asc" > Content-Description: Digital signature > X-Google-AttachSize: 190 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Postmaster cannot start
Hi, my postgresql version 7.4.5 cannot start until this morning, when i check the log file, it give me this error this is part of my log file DEBUG: vacuuming "pg_catalog.pg_class"DEBUG: "pg_class": found 9823 removable, 1017 nonremovable row versions in 205 pagesDETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 148 to 196 bytes long. There were 20 unused item pointers. Total free space (including removable row versions) is 1477460 bytes. 183 pages are or will become empty, including 183 at the end of the table. 9 pages containing 19260 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec.DEBUG: index "pg_class_oid_index" now contains 1017 row versions in 35 pagesDETAIL: 9823 index row versions were removed. 28 index pages have been deleted, 28 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.02 sec.DEBUG: index "pg_class_relname_nsp_index" now contains 726 row versions in 197 pagesDETAIL: 6124 index row versions were removed. 126 index pages have been deleted, 126 are currently reusable. CPU 0.00s/0.03u sec elapsed 0.18 sec.WARNING: index "pg_class_relname_nsp_index" contains 726 row versions, but table contains 1017 row versionsHINT: Rebuild the index with REINDEX.DEBUG: "pg_class": moved 57 row versions, truncated 205 to 20 pagesDETAIL: CPU 0.00s/0.00u sec elapsed 0.34 sec.DEBUG: index "pg_class_oid_index" now contains 1017 row versions in 35 pagesDETAIL: 57 index row versions were removed. 28 index pages have been deleted, 28 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec.ERROR: could not open segment 1 of relation "pg_class_relname_nsp_index" (target block 1183847760): No such file or directoryDEBUG: AbortCurrentTransactionPANIC: cannot abort transaction 14135438, it was already committedDEBUG: child process (PID 4834) was terminated by signal 6LOG: server process (PID 4834) was terminated by signal 6LOG: terminating any other active server processesDEBUG: sending SIGQUIT to process 3337DEBUG: sending SIGQUIT to process 3336DEBUG: sending SIGQUIT to process 3335WARNING: terminating connection because of crash of another server processDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.HINT: In a moment you should be able to reconnect to the database and repeat your command.WARNING: terminating connection because of crash of another server processDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. Can someone please explain to me what happen to my database, cause i not so clear, i have a daily maintenance routine that always trigger at 4am morning. 1) will run end of day job. (every day) 2) will run vacuum full database. (every day) 3) will run analyze database. (every day) 4) will run reindex database. (every day) saw from the log file, it's possible that server crash during vacuum process... Question : 1) what happen to my database server? what the error meaning? 2) it is my daily maintenance routine causing that? 3) it is ok to run vacuum full every day ? 4) how can i solve this problem? Thanks Regards Beh
Re: [GENERAL] GUI Interface
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kenneth DownsSent: 14 May 2006 23:12To: pgsql-general@postgresql.orgSubject: Re: [GENERAL] GUI Interface gentoo linux on an AMD. here are some stats, if you tell me any other packages you need versions for I can provide those. Thanks. kernel 2.6.8KDE 3.3.2gtk+ 2.4.14pgadmin3 1.2.2The window manager would be whatever the default for KDE is. OK, the first thing you really should do is upgrade to pgAdmin 1.4.2 and (if building form source) wxWidgets 2.6.3. There have been *loads* of fixes since 1.2.x in pgAdmin and wxWidgets 2.5.x which is what 1.2 was normally built against. Regards, Dave.
