[GENERAL] Mem usage/leak - advice needed
In trying to investigate a possible memory issue that affects only one of our servers, I have been logging the process list for postgres related items 4 times a day for the past few days. This server uses postgres 7.4.6 + slon 1.1.0 on Debian i686 (Linux server2 2.6.8.1-4-686-smp) and is a slon slave in a two server replicated cluster. Our master DB (similar setup) does not exbibit this problem at all - only the subscriber node... The load average starts to go mental once the machine has to start swapping (ie starts running out of physical RAM). The solution so far is to stop and restart both slon and postgres and things return to normal for another 2 weeks. I know other people have reported similar things but there doesn't seem to be an explanation or solution (other than stopping and starting the two processes). Can anyone suggest what else to look at on the server to see what might be going on? Appreciate any help or advice anyone can offer. I'm not a C programmer nor a unix sysadmin, so any advice needs to be simple to understand. Thanks John The first log is 14th Dec and the second is the 22nd Dec. You can see the slon process (id=27844) using more memory over time. It's memory map and the postmaster are posted below too. ~/meminfo # cat 200512141855.log 27806 1 1052 15288 0.0 0.1 /usr/local/pgsql/bin/postmaster 27809 27806 812 6024 0.0 0.0 pg: stats buffer process 27810 27809 816 5032 0.0 0.0 pg: stats collector process 27821 27806 10744 16236 0.1 1.0 pg: postgres bp_live 192.168.22.76 idle 27842 1 620 2324 0.0 0.0 /usr/local/pgsql/bin/slon -d 1 bprepl4 27844 27842 5920 66876 0.0 0.5 /usr/local/pgsql/bin/slon -d 1 bprepl4 27847 27806 10488 16020 0.0 1.0 pg: postgres bp_live [local] idle 27852 27806 12012 17020 1.1 1.1 pg: postgres bp_live [local] idle 27853 27806 11452 16868 0.0 1.1 pg: postgres bp_live [local] idle 27854 27806 10756 16240 0.0 1.0 pg: postgres bp_live [local] idle ~/meminfo # cat 200512220655.log 27806 1 940 15288 0.0 0.0 /usr/local/pgsql/bin/postmaster 27809 27806 752 6024 0.0 0.0 p: stats buffer process 27810 27809 764 5032 0.0 0.0 pg: stats collector process 27821 27806 4684 16236 0.0 0.4 pg: postgres bp_live 192.168.22.76 idle 27842 1 564 2324 0.0 0.0 /usr/local/pgsql/bin/slon -d 1 bprepl4 27844 27842 2368 70096 0.0 0.2 /usr/local/pgsql/bin/slon -d 1 bprepl4 27847 27806 4460 16020 0.0 0.4 pg: postgres bp_live [local] idle 27852 27806 11576 17020 1.0 1.1 pg: postgres bp_live [local] idle 27853 27806 11328 16868 0.0 1.0 pg: postgres bp_live [local] idle 27854 27806 4640 16240 0.0 0.4 pg: postgres bp_live [local] idle The top listing (right now is) - the key thing is the kswapd0 process. Once physical memory becomes exhausted, the server goes into rapid decline as the swap burden increases... top-08:27:27 up 43 days, 42 min, 1 user, load average: 0.01, 0.04, 0.00 Tasks: 85 total, 1 running, 84 sleeping, 0 stopped, 0 zombie Cpu(s): 0.1% us, 0.0% sy, 0.0% ni, 99.4% id, 0.5% wa, 0.0% hi, 0.0% si Mem: 1035612k total, 1030512k used, 5100k free,46416k buffers Swap: 497972k total, 157088k used, 340884k free,28088k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 27821 postgres 16 0 16236 6480 14m S 0.3 0.6 14:00.34 postmaster 18939 root 16 0 2044 1040 1820 R 0.3 0.1 0:00.02 top 1 root 16 0 1492 136 1340 S 0.0 0.0 0:05.43 init 2 root RT 0 000 S 0.0 0.0 0:02.51 migration/0 3 root 34 19 000 S 0.0 0.0 0:00.02 ksoftirqd/0 4 root RT 0 000 S 0.0 0.0 0:05.35 migration/1 5 root 34 19 000 S 0.0 0.0 0:00.05 ksoftirqd/1 6 root RT 0 000 S 0.0 0.0 0:04.91 migration/2 7 root 34 19 000 S 0.0 0.0 0:00.00 ksoftirqd/2 8 root RT 0 000 S 0.0 0.0 0:21.87 migration/3 9 root 34 19 000 S 0.0 0.0 0:00.00 ksoftirqd/3 10 root 5 -10 000 S 0.0 0.0 0:00.20 events/0 11 root 5 -10 000 S 0.0 0.0 0:00.06 events/1 12 root 5 -10 000 S 0.0 0.0 0:00.01 events/2 13 root 5 -10 000 S 0.0 0.0 0:00.00 events/3 14 root 8 -10 000 S 0.0 0.0 0:00.00 khelper 15 root 7 -10 000 S 0.0 0.0 0:00.00 kacpid 67 root 5 -10 000 S 0.0 0.0 19:26.36 kblockd/0 68 root 5 -10 000 S 0.0 0.0 0:59.05 kblockd/1 69 root 5 -10 000 S 0.0 0.0 0:08.40 kblockd/2 70 root 5 -10 000 S 0.0 0.0 0:10.17 kblockd/3 82 root 15 0 000 S 0.0 0.0 624:18.25 kswapd0 [snipped] The memory map for the slon process is below. cat /proc/27844/maps 08048000-08067000 r-xp 08:0c 198200
[GENERAL] Is CREATE TYPE an alias for CREATE DOMAIN?
Hi Could somebody please tell me if CREATE TYPE is equivalent to CREATE DOMAIN? If not is there a work around --- Regards John Dean, co-author of Rekall, the only alternative to MS Access ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Running with fsync=off
On Wed, Dec 21, 2005 at 11:30:15PM -0800, Benjamin Arai wrote: I want to be able to do large updates on an existing backed up database with fsync=off but at the end of the updates how do I ensure that the data gets synced? Do you know if that actually makes it much faster? Maybe you're better off splitting WAL into a seperate disk. Somebody said running sync ; sync; sync from the console. This seems reasonable but why not just sync or is there another command I should ruyn after the update either in postgres or the console? The reason is partly historical. On some OSes running sync only starts the process but returns immediatly. However, there can only be one sync at a time so the second sync waits for the first the finish. The third is just for show. However, on Linux at least the one sync is enough. Don't you need to restart postgres to change that parameter anyway? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpvPIzJf0vhs.pgp Description: PGP signature
Re: [GENERAL] Is CREATE TYPE an alias for CREATE DOMAIN?
John Dean wrote: Hi Could somebody please tell me if CREATE TYPE is equivalent to CREATE DOMAIN? If not is there a work around What do you mean by equivalent? You wouldn't use them in the same way, and I'm not sure what a work-around would consist of. What are you trying to do? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Indexes on character type columns
Hi. Is there a limitation of the length of a char or varchar(x) column for indexing? cug smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Indexes on character type columns
On Thu, Dec 22, 2005 at 11:24:12AM +0100, Guido Neitzer wrote: Hi. Is there a limitation of the length of a char or varchar(x) column for indexing? For btrees at least, yes. Around a third of a page or about 2713 bytes by default. For bigger things you probably want tsearch anyway. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpkX149HRSRy.pgp Description: PGP signature
Re: [GENERAL] Indexes on character type columns
On 22.12.2005, at 11:27 Uhr, Martijn van Oosterhout wrote: For btrees at least, yes. Around a third of a page or about 2713 bytes by default. For bigger things you probably want tsearch anyway. Thanks. cug smime.p7s Description: S/MIME cryptographic signature
[GENERAL] About Maximum number of columns
Hi All. I have a question about the Maximum number of columns in a table ? In FAQ for PostgreSQL,I can find this description : Maximum number of columns in a table? 250-1600 depending on column types But , I want to know what type is 1600 limit , and what type is 250 limit . it is important for me , thanks . Regards , zhao xin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] About Maximum number of columns
zhaoxin wrote: Hi All. I have a question about the Maximum number of columns in a table ? In FAQ for PostgreSQL,I can find this description : Maximum number of columns in a table? 250-1600 depending on column types But , I want to know what type is 1600 limit , and what type is 250 limit . it is important for me , thanks . Have you tried creating the columns you need in the type you need? What happens? Are you sure hundreds of columns in a table is the best design for your problem? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] About Maximum number of columns
I have to face this trouble , it is not I want , but a historical problem . so , can you tell me ? Richard Huxton wrote: zhaoxin wrote: Hi All. I have a question about the Maximum number of columns in a table ? In FAQ for PostgreSQL,I can find this description : Maximum number of columns in a table? 250-1600 depending on column types But , I want to know what type is 1600 limit , and what type is 250 limit . it is important for me , thanks . Have you tried creating the columns you need in the type you need? What happens? Are you sure hundreds of columns in a table is the best design for your problem? -- 以上、よろしくお願いいたします。 - Zhao Xin NEC-CAS Software Laboratories Co.,Ltd. Tel : 8233-4433-425 Telnet : 8-0086-22-425 E-mail:[EMAIL PROTECTED] ---(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] About Maximum number of columns
zhaoxin schrieb: I have to face this trouble , it is not I want , but a historical problem . so , can you tell me ? Try it out. I'd change the future of that history though. You can expect much better performany on virtually any RDBMS with appropriate schema. ++Tino PS: try to send text-only to mailinglists ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] About Maximum number of columns
zhaoxin wrote:
I have to face this trouble , it is not I want , but a historical problem .
so , can you tell me ?
Sure, but you'll need to say what column-types you have.
Below is a small script to generate a table with lots of columns.
#!/bin/perl -w
use strict;
my $tbl = 'test_text';
my $typ = 'text';
my $num_cols = 1500;
print CREATE TABLE $tbl (\n;
for (my $c=0; $c$num_cols; $c++) {
print col$c $typ,\n;
}
print PRIMARY KEY (col0)\n;
print );\n;
exit;
You can run it with something like:
perl mk_script.pl | psql -Urichardh richardh
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] ODBC connection string, MS Access
Hello. Could you, please, help me to optimize my connection string (MS Access 2003, PostgreSQL 8.1.1. and psqlodbc-08_01_0102)? ' PG_ODBC_PARAMETER ACCESS_PARAMETER' *' READONLY A0' PROTOCOL A1' FAKEOIDINDEX A2 'A2 must be 0 unless A3=1' SHOWOIDCOLUMN A3' ROWVERSIONING A4' SHOWSYSTEMTABLES A5' CONNSETTINGS A6' FETCH A7' SOCKET A8' UNKNOWNSIZES A9 ' range [0-2]' MAXVARCHARSIZE B0' MAXLONGVARCHARSIZE B1' DEBUG B2' COMMLOG B3' OPTIMIZER B4 ' note that 1 = _cancel_ generic optimizer...' KSQO B5' USEDECLAREFETCH B6' TEXTASLONGVARCHAR B7' UNKNOWNSASLONGVARCHAR B8' BOOLSASCHAR B9' PARSE C0' CANCELASFREESTMT C1' EXTRASYSTABLEPREFIXES C2 'Connection stringCONNECTIONSTRING:strConnInfo = "ODBC;Driver=" Driver ";Server=" SERVER ";Port=" PORT ";Database=" DATABASE ";"strConnUserPass = "Uid=" USERNAME ";Pwd=" PASSWORD ";"strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=" A6 ";A7=100;A8=" SOCKET ";A9=1;" _"B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=0;" _"C0=0;C1=0;C2=dd_;" There are some options I don't understand, for example "Parse statements" and "Fetch/Declare" and don't know how would it affect performances... Also, there is a problem with the limited length of connection string that Access can handle, so I have a problem that I can't input some bigger usernames and passwords. Therefore I would like to remove some unneccessary parameters, if possible. Which parameters could be safely removed? Thanks in advance, Zlatko
Re: [GENERAL] contrib extenstions
[EMAIL PROTECTED] (S McLurkin) writes: Is there some place where I can find information on all the contrib extenstions? Download the sources, and examine each directory for its documentation. There is commonly a README file... -- output = (cbbrowne @ ntlug.org) http://cbbrowne.com/info/sap.html Survival in a world of words is best made, if at all, through clever appeal to ambiguity. -- Robert Bolt ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Indices for select count(*)?
On Wed, Dec 21, 2005 at 04:54:08PM -0500, Greg Stark wrote: MSSQL presumably has the entire table cached in RAM and postgres doesn't. Even if MSSQL can scan just the index (which postgres can't do) I would only expect a factor of 2-4x. Hm. Unless perhaps this table is extremely wide? How large are these records? Back when I was using other databases more often, it wasn't uncommon to see a 10x speed improvement on count(*) from using an index. This is an area where PostgreSQL is seriously behind other databases. Of course having vastly superior concurrency goes a long way towards offsetting that in the real world, but it would be a Good Thing if we could get some form of tuple visibility into indexes, as has been discussed in the past. -- 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] Toolkit for creating editable grid
Am 2005-12-16 21:52:07, schrieb Andrus: Has anyone used OpenOffice Base? Just a thought. Or Rekall - it's a bit immature, but it might do what you want. The dreaded MS Access can do what you describe in about 4 minutes... Postgres lacks easy GUI frontend with report generation capabilities like Access. Sorry, but you compare apples with pears. I can not remember that the Microsoft SQL Server has such things. PostgreSQL != Access PostgreSQL ~ MS SQL Server Greetings Michelle -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/8845235667100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Indices for select count(*)?
On Thu, Dec 22, 2005 at 08:52:08AM -0600, Jim C. Nasby wrote: Back when I was using other databases more often, it wasn't uncommon to see a 10x speed improvement on count(*) from using an index. This is an area where PostgreSQL is seriously behind other databases. Of course having vastly superior concurrency goes a long way towards offsetting that in the real world, but it would be a Good Thing if we could get some form of tuple visibility into indexes, as has been discussed in the past. Actually, ISTM the trend is going the other way. MySQL has instant select count(*), as long as you're only using ISAM. Recent versions of MSSQL use an MVCC type system and it also scans the whole table. Oracle is the only one I've found that has any optimisation on this front. The thing is, it *is* possible to change PostgreSQL to do counts via the index. The problem is, the cost is high enough that we're reasonably sure most people don't want to pay it. I've neverneeded an exact row count of a large table (estimates are good enough) so I'm not sure I'd be willing to pay a price to have it. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpakvQBH4HiP.pgp Description: PGP signature
Re: [GENERAL] Indices for select count(*)?
On Thu, Dec 22, 2005 at 04:10:50PM +0100, Martijn van Oosterhout wrote: Actually, ISTM the trend is going the other way. MySQL has instant select count(*), as long as you're only using ISAM. Recent versions of No comment. MSSQL use an MVCC type system and it also scans the whole table. Oracle is the only one I've found that has any optimisation on this front. I think this is more an indication of the power of MVCC over traditional locking rather than the importance of indexes covering (reading just an index to satisfy a query). Index covering can be a huge benefit, and I'd be surprised if MS didn't come out with some way to do it in a future version. I'm actually a bit surprised they don't do it in SQL2005. The thing is, it *is* possible to change PostgreSQL to do counts via the index. The problem is, the cost is high enough that we're reasonably sure most people don't want to pay it. I've neverneeded an exact row count of a large table (estimates are good enough) so I'm not sure I'd be willing to pay a price to have it. I didn't think the method of adding the imperfect known_visible bit to the indexes had that much overhead, but it's been a while since those discussions took place. I do recall some issue being raised that will be very difficult to solve (though again I don't remember the details now). I agree that SELECT count(*) FROM table; is a pretty bogus use case. SELECT count(*) FROM table WHERE field = blah; isn't though, and people often depend on that being extremely fast. When you can do index covering, that case usually is very fast, and PostgreSQL can be much slower. Of course, there are ways around that, but it's more work (and something that I'd bet most developers wouldn't think of). -- 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
[GENERAL] Sorting array field
Hi, Can anyone point me toward an SQL function (whether built-in or an add-on) that will allow me to sort the contents of an array datatype in an SQL query? Something like this: select sort(my_array_field) from my_table; Thanks! Pete
Re: [GENERAL] Questions about server.
On Wed, Dec 21, 2005 at 09:46:59AM +, Richard Huxton wrote: max chin wrote: 1.) What I knew is when too many users access a database at the same time, it will slow down database server process. My question is how to make database server process more faster even if a lot of users access information in database at the same time? What kind of technology is needed to help database server process more faster ? The first step is to find out *why* it is too slow - not enough memory? Not enough disk bandwidth? Not enough CPU? Badly planned queries? Badly written application? So - the first thing to do is to start monitoring your server closely. Also, an experienced PostgreSQL consultant would probably be able to look at both the database configuration as well as the overall system architecture and make recommendations based on just his experience. 4.) Sometimes IIS web server is unavailable and the web pages can' t display for clients. Can you tell me the reasons and the methods to overcome the problems? Although there are many people who use IIS in big installations, I'm not one of them. For maximum reliability, I'd guess Apache on top of one of the BSD's would be a winner, but probably not an option for you. You should also consider having more than one web server connecting to the database, with some kind of connection pooling. THANKS YOU. CAN YOU GIVE ME YOUR ANSWER AS FAST AS POSSIBLE BECAUSE I NEED THESE IMPORTANT ANSWER URGENTLY. If you're a student on work placement, I'm not sure you should have to deal with these sort of issues, certainly not urgently. I tend to agree with Richard. I'm guessing that you're dealing with some kind of e-commerce site that costs your employer money any time it's unavailable. No offense to you, but if the website is that important to them they should have access to someone who has experience with high availability. They should find a PostgreSQL consultant who can at least advise them on their needs, and provide guidance and training to you and other people on staff. Most people I know who do consulting like that have many years of database experience (maybe decades) and at least a few years of PostgreSQL experience to guide them. While the PostgreSQL community is an outstanding resource, it's not the same as having a person to talk to on the phone and get out to your office. In some ways it's better, but in others it's worse. Of course, being 'the new guy' you may not be in a position to recommend this to your boss, but it is something to think about. http://www.postgresql.org/support/professional_support_asia is a listing of companies offering commercial support in Asia. Disclosure: I work for a company that provides commercial support, although we don't have much presence in Asia. Having said all that, here's some things you should look at: If the data's important, having 1 hour old backups might well not be good enough. PITR with default settings will get you 5-minute old backups, but that's probably still more delay than is desirable. Slony should normally be only a few seconds behind a master (if even that much), so that's probably the best way to go. It is more difficult to setup, though. Make certain you're vacuuming enough! This is a huge gotcha for people new to PostgreSQL. Make sure your FSM (Free Space Map) settings are high enough. If you mail the list with the last 10 lines from a vacuumdb -av we can advise you. See also http://pervasivepostgres.com/instantkb13/article.aspx?id=10087. The default shared_buffers setting of 1000 is very small and is typically only suited for a desktop machine. Generally recommended settings are 10-25% of server memory, but keep in mind that shared_buffers is in 8K pages. You should also take a look at http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html for what different postgresql.conf parameters do. And http://www.powerpostgresql.com/PerfList is a good general guide to database performance. -- 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] view or index to optimize performance
On Wed, Dec 21, 2005 at 10:49:29PM +0100, Klein Bal?zs wrote: I thought that if I used a view to retrieve data its content might be cached so it would make the query faster. No. A view is essentially exactly the same as inserting the view definition into the query that's using it. IE: CREATE VIEW v AS SELECT * FROM t; SELECT * FROM v becomes: SELECT * FROM (SELECT * FROM t) v; What you could do is partition the table so that critical information is stored in a smaller table while everything else goes to a larger table. You can then do a UNION ALL view on top of that to 'glue' the two tables together. You can even define rules so that you can do updates on the view. http://cvs.distributed.net/viewcvs.cgi/stats-sql/logdb/ has an example that's similar to this. Note that you'll need an appropriate index on the large table so that PostgreSQL can quickly tell it doesn't contain values that are in the small table. Or, in 8.1 you could use a constraint. You could also do this with inherited tables instead of views. -- 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] query for a time interval
On Wed, Dec 21, 2005 at 11:52:56AM -0800, Mark wrote: SELECT id FROM mq WHERE now - start_date time_to_live; The problem is you can't use an index on this, because you'd need to index on (now() - start_date), which obviously wouldn't work. Instead, re-write the WHERE as: WHERE start_date now() - time_to_live -- 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] Is CREATE TYPE an alias for CREATE DOMAIN?
On Thu, Dec 22, 2005 at 09:36:52AM +, Richard Huxton wrote: John Dean wrote: Hi Could somebody please tell me if CREATE TYPE is equivalent to CREATE DOMAIN? If not is there a work around What do you mean by equivalent? You wouldn't use them in the same way, and I'm not sure what a work-around would consist of. What are you trying to do? Some (most?) database's idea of 'creating a type' is actually what we consider creating a domain, since many databases don't support users adding arbitrary types to the system. I suspect this user is trying to port some code over... -- 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] Indices for select count(*)?
Jim C. Nasby [EMAIL PROTECTED] writes: I didn't think the method of adding the imperfect known_visible bit to the indexes had that much overhead, but it's been a while since those discussions took place. I do recall some issue being raised that will be very difficult to solve (though again I don't remember the details now). I doubt very much any visibility information will ever make it into the indexes. The cost to update it in all the indexes terrible, and when would that update even happen? The proposal that had the most going for it was to maintain a bit in the FSM or something like it that was your known visible bit. That would speed up index scans and vacuums too. It would largely solve the problem with vacuuming large tables that have mostly untouched pages. The reason Oracle gets away with this is because they use optimistic MVCC where the new record replaces the old one entirely. They keep the old records in a separate space entirely. You pay the costs elsewhere instead. In Oracle every update requires updating the rollback segment too, and if you have a very busy table each record can cause you a second (or even third or fourth) read in the rollback segment. And you pay these costs on *all* scans. -- greg ---(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] Indices for select count(*)?
On Thu, 2005-12-22 at 09:33, Jim C. Nasby wrote: On Thu, Dec 22, 2005 at 04:10:50PM +0100, Martijn van Oosterhout wrote: Actually, ISTM the trend is going the other way. MySQL has instant select count(*), as long as you're only using ISAM. Recent versions of No comment. MSSQL use an MVCC type system and it also scans the whole table. Oracle is the only one I've found that has any optimisation on this front. I think this is more an indication of the power of MVCC over traditional locking rather than the importance of indexes covering (reading just an index to satisfy a query). Index covering can be a huge benefit, and I'd be surprised if MS didn't come out with some way to do it in a future version. I'm actually a bit surprised they don't do it in SQL2005. I wouldn't mind a with visibility switch for indexes that you could throw when creating them for this purpose. But burdening all indexes with this overhead when most wouldn't need it is not, IMHO, a good idea. I seem to remember Tom saying that there was a race condition issue though with updating the table AND the index at the same time, that they could be out of sync for a fraction of a second or something like that. So, if we had this kind of thing, the indexes and / or tables would have to be locked for updates. Again, for a reporting database, no big deal. For a transactional database, very big deal. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] About Maximum number of columns
On Thu, Dec 22, 2005 at 07:22:12PM +0800, zhaoxin wrote: Hi All. I have a question about the Maximum number of columns in a table ? In FAQ for PostgreSQL,I can find this description : Maximum number of columns in a table? 250-1600 depending on column types But , I want to know what type is 1600 limit , and what type is 250 limit . it is important for me , thanks . I'm pretty sure I've read the reason for the limit somewhere in the source code, but I can't remember where. It's probably somewhere in http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/. I know that the exact upper limit isn't actually 1600 fields, it's more like 1643 or something. But, as others have said, just try creating your table and see what happens. If it fails, you might be able to get it to work by increasing the block size. And as others have said, this is almost certainly a horrible schema that needs to be fixed, badly. Luckily, thanks to views and rules, you could probably fix it without actually changing any of the client code. -- 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] Toolkit for creating editable grid
On Thu, Dec 22, 2005 at 03:52:36PM +0100, Michelle Konzack wrote: Am 2005-12-16 21:52:07, schrieb Andrus: Has anyone used OpenOffice Base? Just a thought. Or Rekall - it's a bit immature, but it might do what you want. The dreaded MS Access can do what you describe in about 4 minutes... Postgres lacks easy GUI frontend with report generation capabilities like Access. Sorry, but you compare apples with pears. I can not remember that the Microsoft SQL Server has such things. PostgreSQL != Access PostgreSQL ~ MS SQL Server Note that many people have had good results by using Access as a front-end to PostgreSQL. -- 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] Is CREATE TYPE an alias for CREATE DOMAIN?
Jim C. Nasby wrote: On Thu, Dec 22, 2005 at 09:36:52AM +, Richard Huxton wrote: John Dean wrote: Hi Could somebody please tell me if CREATE TYPE is equivalent to CREATE DOMAIN? If not is there a work around What do you mean by equivalent? You wouldn't use them in the same way, and I'm not sure what a work-around would consist of. What are you trying to do? Some (most?) database's idea of 'creating a type' is actually what we consider creating a domain, since many databases don't support users adding arbitrary types to the system. I suspect this user is trying to port some code over... CREATE DOMAIN builds on an existing data type and adds additional characteristics and checks to the type. It is sort of like a macro for types. CREATE TYPE creates a new data type, independent of existing data types, and usually requires C code and a shared object file to load into the database. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 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] About Maximum number of columns
On Thu, 2005-12-22 at 10:10, Jim C. Nasby wrote: On Thu, Dec 22, 2005 at 07:22:12PM +0800, zhaoxin wrote: Hi All. I have a question about the Maximum number of columns in a table ? In FAQ for PostgreSQL,I can find this description : Maximum number of columns in a table? 250-1600 depending on column types But , I want to know what type is 1600 limit , and what type is 250 limit . it is important for me , thanks . I'm pretty sure I've read the reason for the limit somewhere in the source code, but I can't remember where. It's probably somewhere in http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/. I know that the exact upper limit isn't actually 1600 fields, it's more like 1643 or something. But, as others have said, just try creating your table and see what happens. If it fails, you might be able to get it to work by increasing the block size. And as others have said, this is almost certainly a horrible schema that needs to be fixed, badly. Luckily, thanks to views and rules, you could probably fix it without actually changing any of the client code. The limit has to do with the fact that all the header info for each column must fit in a single block (8K default). I seem to recall someone stating that increasing block size to 16k or 32k could increase this number by about 2x or 4x. Not sure if it'll work, but it might be worth the effort if you're stuck keeping some legacy app happy long enough to replace it with a well designed system. Oh to be able to travel back in time and smack people for designing 1600 column tables... :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Is CREATE TYPE an alias for CREATE DOMAIN?
Jim C. Nasby wrote: Some (most?) database's idea of 'creating a type' is actually what we consider creating a domain, Which databases do such a thing? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] two shared memory segments?
On Wednesday December 21 2005 8:24 pm, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: I have a cluster configured for ~800MB of shared memory cache (shared_buffers = 10), but ipcs shows TWO shared memory segments of ~800MB belonging to that postmaster. What kind of a problem do I have here? I'd say that you had a backend crash, causing the postmaster to abandon the original shared memory segment and make a new one, but the old segment is still attached to by a couple of processes. Does that make sense even if the creating pid is the same for both? There was a bug awhile back whereby the stats support processes failed to detach from shared memory and thus would cause a dead shmem segment to hang around like this. What PG version are you running? This is an old 7.3.7 cluster. Ed ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Indices for select count(*)?
I wouldn't mind a with visibility switch for indexes that you could
throw when creating them for this purpose. But burdening all indexes
with this overhead when most wouldn't need it is not, IMHO, a good idea.
that would add complexity to the index code for... just one case?
what about a set of functions instead...
one function to create all necesary triggers to maintain a different
table with a count for the table, and one function that retrieves that
info
select start_counter_on_table('table_name');
select get_counter_on_table('table_name');
of course, this could be usefull just for the case of select * from
table... but that case is the whole problem...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] About Maximum number of columns
Scott Marlowe wrote: On Thu, 2005-12-22 at 10:10, Jim C. Nasby wrote: On Thu, Dec 22, 2005 at 07:22:12PM +0800, zhaoxin wrote: Hi All. I have a question about the Maximum number of columns in a table ? In FAQ for PostgreSQL,I can find this description : Maximum number of columns in a table? 250-1600 depending on column types But , I want to know what type is 1600 limit , and what type is 250 limit . it is important for me , thanks . I'm pretty sure I've read the reason for the limit somewhere in the source code, but I can't remember where. It's probably somewhere in http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/. I know that the exact upper limit isn't actually 1600 fields, it's more like 1643 or something. But, as others have said, just try creating your table and see what happens. If it fails, you might be able to get it to work by increasing the block size. And as others have said, this is almost certainly a horrible schema that needs to be fixed, badly. Luckily, thanks to views and rules, you could probably fix it without actually changing any of the client code. The limit has to do with the fact that all the header info for each column must fit in a single block (8K default). I seem to recall someone stating that increasing block size to 16k or 32k could increase this number by about 2x or 4x. Not sure if it'll work, but it might be worth the effort if you're stuck keeping some legacy app happy long enough to replace it with a well designed system. Yes, that is correct. Increasing the block size can increase the maximum number of columns. Certain columns like int4 are 4 bytes, while text/varchar/char can be placed in toast tables so only the pointer has to fix in the table, and I think the header is 8 bytes. However, the fixed limit is 1600. Here is a comment from the code: /*-- * MaxHeapAttributeNumber limits the number of (user) columns in a table. * This should be somewhat less than MaxTupleAttributeNumber. It must be * at least one less, else we will fail to do UPDATEs on a maximal-width * table (because UPDATE has to form working tuples that include CTID). * In practice we want some additional daylight so that we can gracefully * support operations that add hidden resjunk columns, for example * SELECT * FROM wide_table ORDER BY foo, bar, baz. * In any case, depending on column data types you will likely be running * into the disk-block-based limit on overall tuple size if you have more * than a thousand or so columns. TOAST won't help. *-- */ #define MaxHeapAttributeNumber 1600/* 8 * 200 */ -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 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: [ADMIN] [GENERAL] Running with fsync=off
Martijn van Oosterhout kleptog@svana.org writes: On Wed, Dec 21, 2005 at 11:30:15PM -0800, Benjamin Arai wrote: Somebody said running sync ; sync; sync from the console. This seems The reason is partly historical. On some OSes running sync only starts the process but returns immediatly. However, there can only be one sync at a time so the second sync waits for the first the finish. The third is just for show. However, on Linux at least the one sync is enough. No, the second and third are both a waste of time. sync tells the kernel to flush any dirty buffers to disk, but doesn't wait for it to happen. There is a story that the advice to type sync twice was originally given to operators of an early Unix system, as a quick-and-dirty way of making sure that they didn't power the machine down before the sync completed. I don't know if it's true or not, but certainly the value would only appear if you type syncRETURNsyncRETURN so that the first sync is actually issued before you type the next one. Typing them all on one line as depicted is just a waste of finger motion. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Stored procedure
I am learning how to create stored procedures using pgAdmin and Postgres. I have a couple of questions. 1) For all of my tables (admittedly not many, yet), I created columns with the type of integer to serve as indices. I now learned (well, latelast might)in your documentation that Postgres has additional types (serial, serial4, c.) that are integers that are autoincremented. I tried, therefore, to change these columns to type serial but received an error stating that type serial does not exist. Was the documentation I read mistaken, or is it a problem with pgAdmin, or did I make a mistake? Should I drop the columns in question and recreate them as having type serial(is that possible when the column is used as a foreign key in other tables)? 2) Suppose I have a lookup table with an autoincremented integer index column, used as a foreign key in a second table, and I want a stored procedure to insert data into a second table that uses the index from the first as a foreign key. Now, the stored procedure must: a) check the name passed for the second column of the first table to see if it exists there, and if not insert it b) whether the name provided for the second column had to be inserted or not, retrieve the index that corresponds to it c) execute the insert into the second table using the index value retrieved from the first as the value for the foreign key column in the second table. Doing all this in Java or C++ is trivial, and I have done so when using a database that didn't have stored procedures, but it isn't clear to me how to do this using only SQL inside a stored procedure. I have just learned this morning that MySQL would allow the following inside a stored procedure: INSERT INTO foo (auto,text) VALUES(NULL,'text'); # generate ID by inserting NULLINSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); # use ID in second table I have yet to figure out how to modify this to verify that 'text' isn't already in foo, and return its index for use in foo2 if it is, but that's another question (I'd want the values in the second column in foo to be unique). But I am curious to know if Postgres has something equivalent to "LAST_INSERT_ID()". Can one embed the first insert above in an if/else block inside a stored procedure, placing the index in a variable that has scope local to the procedure, and use that variable in the second insert? Thanks, Ted R.E. (Ted) Byers, Ph.D., Ed.D.R D Decision Support Softwarehttp://www.randddecisionsupportsolutions.com/
Re: [GENERAL] Stored procedure
On 12/22/05, Ted Byers [EMAIL PROTECTED] wrote:
I am learning how to create stored procedures using pgAdmin and Postgres. I
have a couple of questions.
1) For all of my tables (admittedly not many, yet), I created columns with
the type of integer to serve as indices.
columns doesn't serve as indices... columns could be indexed,
instead... there is a difference...
I now learned (well, late last might) in your documentation that Postgres has
additional types (serial, serial4, c.) that are integers that are
autoincremented.
serial is not a type is a shorthand for integer with a default
expresion that retrives next value in a sequence...
I tried, therefore, to change these columns to type serial but
received an error
stating that type serial does not exist.
how did you try? what was the exact error you receive?
Was the documentation I read mistaken, or is it a problem with pgAdmin, or
did I
make a mistake? Should I drop the columns in question and recreate them as
having type serial (is that possible when the column is used as a foreign key
in
other tables)?
no... unless you drop the foreign key constraint as well
2) Suppose I have a lookup table with an autoincremented integer index
column, used as a foreign key in a second table,
indexed column... no index column (there is not such a thing)
and I want a stored procedure to insert data into a second table that uses the
index from the first as a foreign key.
i think you are overusing the word index
Now, the stored procedure must:
a) check the name passed for the second column of the first table to see if
it exists there, and if not insert it
if exists(select * from table2 where fld = value_from_second_fld_table1) then
...
end if;
b) whether the name provided for the second column had to be inserted or
not, retrieve the index that corresponds to it
you don't retrieve indexes...
c) execute the insert into the second table using the index value retrieved
from the first as the value for the foreign key column in the second table.
Doing all this in Java or C++ is trivial, and I have done so when using a
database that didn't have stored procedures, but it isn't clear to me how to
do this using only SQL inside a stored procedure.
I have just learned this morning that MySQL would allow the following inside
a stored procedure:
INSERT INTO foo (auto,text)
VALUES(NULL,'text'); # generate ID by inserting NULL
and this of course is bad... if a insert NULL i want the NULL to be inserted.
SQL Standard way of doing things is ommiting the auto incremental fld at all
INSERT INTO foo (text) VALUES ('text');
INSERT INTO foo2 (id,text)
VALUES(LAST_INSERT_ID(),'text'); # use ID in second table
INSERT INTO foo2 (id, text) VALUES (currval('seq'), 'text')
I have yet to figure out how to modify this to verify that 'text' isn't
already in foo, and return its index for use in foo2 if it is, but that's
another question (I'd want the values in the second column in foo to be
unique). But I am curious to know if Postgres has something equivalent to
LAST_INSERT_ID().
currval()
Can one embed the first insert above in an if/else
block inside a stored procedure, placing the index in a variable that has
scope local to the procedure, and use that variable in the second insert?
Thanks,
Ted
R.E. (Ted) Byers, Ph.D., Ed.D.
R D Decision Support Software
http://www.randddecisionsupportsolutions.com/
you should read the manual in the sections about triggers, sequences,
and so on...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] reading EXPLAIN output
merge join (cost=0.00..348650.65 rows=901849 width=12)
merge cond {blah}
join filter {blah}
index scan using {blah index on blah} (cost=0.00..289740.65
rows=11259514 width=8)
index scan using {blah index on blah} (cost=0.00..17229.93
rows=902085 width=8)
This query takes about 3 minutes to run and I'm trying to figure out
why. From a tutorial and the docs, I gather that the ..largenum part
is the number of page reads required, so I understand where 289740 and
17229 come from. But what about 348650 page reads for the merge
join? My conjecture is that the joined keys are being stored on disk
(if that's how the internals of postgresql works) and have to be re-read
for the rest of the query. Is that right? Does that mean I could speed
this up by giving more RAM to store it in?
When I do EXPLAIN ANALYZE, the actual values come out like this:
merge join: (actual time=170029.404..170029.404)
index scan: (actual time=27.653..84373.805)
index scan: (actual time=45.681..7026.928)
This seems to confirm that it's the final merge join that takes
forever. Because it is writing to and reading from disk?
---(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] Sorting array field
On Thu, Dec 22, 2005 at 08:38:46AM -0700, Pete Deffendol wrote:
Can anyone point me toward an SQL function (whether built-in or an add-on)
that will allow me to sort the contents of an array datatype in an SQL
query?
For integer arrays see contrib/intarray.
SELECT sort('{5,2,3,1,9,7}'::int[]);
sort
---
{1,2,3,5,7,9}
(1 row)
I don't recall if any of the contrib modules can sort arrays of
other types; if not then look for something at a site like pgfoundry
or GBorg. If you have PL/Ruby then it couldn't get much easier:
CREATE FUNCTION sort(arg text[]) RETURNS text[] AS $$
arg.sort
$$ LANGUAGE plruby IMMUTABLE STRICT;
SELECT sort('{zz,xx yy,cc,aa,bb}'::text[]);
sort
---
{aa,bb,cc,xx yy,zz}
(1 row)
Another way would be to write a set-returning function that returns
each item in the array as a separate row, and another function that
uses an array constructor to put the rows back together in order
(this example should work in 7.4 and later):
CREATE FUNCTION array2rows(anyarray) RETURNS SETOF anyelement AS '
BEGIN
FOR i IN array_lower($1, 1) .. array_upper($1, 1) LOOP
RETURN NEXT $1[i];
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE FUNCTION sort(anyarray) RETURNS anyarray AS '
SELECT array(SELECT * FROM array2rows($1) ORDER BY 1)
' LANGUAGE sql IMMUTABLE STRICT;
SELECT data, sort(data) FROM foo;
data | sort
---+---
{dd,cc,bb,aa} | {aa,bb,cc,dd}
{zz,xx yy,cc,aa,bb} | {aa,bb,cc,xx yy,zz}
(2 rows)
I'm not sure if there are easier ways; these are what first came
to mind.
--
Michael Fuhr
---(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] Why is create function bringing down the Backend server?
I'm trying to add additional functionality to the contrib/pgcrypto branch (at least for my own use, although ideally, I'd like to make whatever additions good enough as to be accepted as part of the PG distribution) Anyway, I wanted to add hash functions (SHA-1 is already there, so I'd like to add SHA-256 and SHA-512 at the very least, and maybe also, for completeness, SHA-224 and SHA-384). Anyway, I started with an implementation of SHA-1 that I already have (since it is easy to test/debug, as I only have to compare it with the already-existing sha1 function in pgcrypto). I got it to work nicely, and I tried several millions randomly- generated strings, and the result of my hash function matches the result of pgcrypto's sha1 function. The problem is, when I execute the SQL statement: create or replace function sha1 ; for the second time (i.e., after making modifications and recompiling), the *backend* crashes -- it then restarts automatically, and then I run again the create or replace statement, and it works now (and the function seems to work fine -- well, in its final version it does). I know the list of possible causes may be nearly infinite, so I put the modified file (I removed most of the other stuff from the original pgcrypto.c file, and left the pg_digest function, which is the one that computes hashes, and the one that I used as a model to create mine): http://www.mochima.com/tmp/pgcrypto.c I also modified the SQL script file to include my function; this (when I execute this script) is precisely the moment at which the PG backend crashes (well, it shuts down anyway): http://www.mochima.com/tmp/pgcrypto.sql Any ideas of what I'm doing wrong? BTW, I compiled with the provided Makefile, then copy the .so files to /usr/local/pgsql/lib directory, and ran /sbin/ldconfig (that directory is included in my /etc/ld.so.conf file). I'm running PG 7.4.9 on a Linux FC4 on a Dual-Core Athlon64 (kernel x86_64-smp). Thanks for any comments/feedback! (please by kind, as this is my first attempt ever at creating PG functions -- but please be tough! Don't hold back valuable feedback just because you don't want to hurt my baby feelings! :-)) Carlos -- ---(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] reading EXPLAIN output
David Rysdam [EMAIL PROTECTED] writes:
merge join (cost=0.00..348650.65 rows=901849 width=12)
merge cond {blah}
join filter {blah}
index scan using {blah index on blah} (cost=0.00..289740.65
rows=11259514 width=8)
index scan using {blah index on blah} (cost=0.00..17229.93
rows=902085 width=8)
This query takes about 3 minutes to run and I'm trying to figure out
why. From a tutorial and the docs, I gather that the ..largenum part
is the number of page reads required, so I understand where 289740 and
17229 come from. But what about 348650 page reads for the merge
join?
You're misreading it. An upper node's cost includes the cost of its
children. So the actual cost estimate for the join step is 41680.07.
When I do EXPLAIN ANALYZE, the actual values come out like this:
merge join: (actual time=170029.404..170029.404)
That seems a bit odd ... is there only one row produced? Could you show
us the entire EXPLAIN ANALYZE output, rather than your assumptions about
what's important?
Increasing work_mem won't help a merge join, but if you can get it large
enough to allow a hash join to be used instead, that might be a win.
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] Why is create function bringing down the Backend server?
Carlos Moreno [EMAIL PROTECTED] writes: The problem is, when I execute the SQL statement: create or replace function sha1 ; for the second time (i.e., after making modifications and recompiling), the *backend* crashes Getting a stack trace from that core dump might be illuminating. Better yet, attach to the backend with gdb before you execute the crash-triggering statement, and let gdb trap the crash. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [Slony1-general] Mem usage/leak - advice needed
On 12/22/05, John Sidney-Woollett [EMAIL PROTECTED] wrote: In trying to investigate a possible memory issue that affects only one of our servers, I have been logging the process list for postgres related items 4 times a day for the past few days. This server uses postgres 7.4.6 + slon 1.1.0 on Debian i686 (Linux server2 2.6.8.1-4-686-smp) and is a slon slave in a two server replicated cluster. Our master DB (similar setup) does not exbibit this problem at all - only the subscriber node... The load average starts to go mental once the machine has to start swapping (ie starts running out of physical RAM). The solution so far is to stop and restart both slon and postgres and things return to normal for another 2 weeks. I know other people have reported similar things but there doesn't seem to be an explanation or solution (other than stopping and starting the two processes). Can anyone suggest what else to look at on the server to see what might be going on? Appreciate any help or advice anyone can offer. I'm not a C programmer nor a unix sysadmin, so any advice needs to be simple to understand. The memory usage growth is caused by the buffers in the slave slon daemon growing when long rows go through them. The buffers never shrink while the slon daemon is running. How big is the largest rows which slon replicates? One suggestion I have seen is to recompile slon to use fewer buffers. Another is to set a ulimit for memory size to automatically kill the slon daemons when they get too big. The watchdog will then restart them. Alternatively, your strategy of restarting the slon daemons each week will work (you don't need to restart postgres). I came up with a patch which shrinks the buffers when they go above a certain size. This doesn't fix the problem of lots of big rows happening at once but it fixes the gradual growth. - Ian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] reading EXPLAIN output
Tom Lane wrote:
David Rysdam [EMAIL PROTECTED] writes:
merge join (cost=0.00..348650.65 rows=901849 width=12)
merge cond {blah}
join filter {blah}
index scan using {blah index on blah} (cost=0.00..289740.65
rows=11259514 width=8)
index scan using {blah index on blah} (cost=0.00..17229.93
rows=902085 width=8)
This query takes about 3 minutes to run and I'm trying to figure out
why. From a tutorial and the docs, I gather that the ..largenum part
is the number of page reads required, so I understand where 289740 and
17229 come from. But what about 348650 page reads for the merge
join?
You're misreading it. An upper node's cost includes the cost of its
children. So the actual cost estimate for the join step is 41680.07.
When I do EXPLAIN ANALYZE, the actual values come out like this:
merge join: (actual time=170029.404..170029.404)
That seems a bit odd ... is there only one row produced? Could you show
us the entire EXPLAIN ANALYZE output, rather than your assumptions about
what's important?
Increasing work_mem won't help a merge join, but if you can get it large
enough to allow a hash join to be used instead, that might be a win.
regards, tom lane
I'm looking for certain anomalies, so the end result should be zero rows.
merge join (cost=0.00..348650.65 rows=901849 width=12) (actual
time=170029.404..170029.404 rows=0 loops=1)
merge cond {blah}
join filter {blah}
index scan using {blah index on blah} (cost=0.00..289740.65
rows=11259514 width=8) (actual time=29.227..85932.426 rows=11256725 loops=1)
index scan using {blah index on blah} (cost=0.00..17229.93 rows=902085
width=8) (actual time=39.896..6766.755 rows=902236 loops=1)
Total runtime: 172469.209 ms
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Newbie Question: FAQ for database optimization?
On Tue, Dec 20, 2005 at 10:21:54PM +0100, Alexander Scholz wrote: Hi, is there a newbie's FAQ / book / link for howto optimize databases with PostgreSQL? Background: Customer has the Windows* (sorry g) Postgres 8.1.0 standard installation out of the box. A table has 2.5 mio records. No indizes defined, primary key (sequence) does exist. In pgAdmin select count(*) takes over 30 seconds, That sounds about right. If you want to cache this result, there are ways to do that, and there are approximations to the result if you're interested in such things. an update affecting 70'000 records takes minutes... An index on the (set of) column(s) the WHERE clause refers to would very likely help. For example, if your update looks like: UPDATE foo SET bar = 555 WHERE baz = 'blurf'; You could get some mileage out of indexing the baz column. See the docs on CREATE INDEX for the syntax. I am sure PostgreSQL could do better, we just need to tune the database. (I hope so at least!) What action and/or reading can you recommend? (We quickly need some 'wow' effects to keep the customer happy sigh). There are archives of the pgsql-performance mailing list at http://archves.postresql.org/ for a lot of this. For things you don't find there, you can either post here or go to irc://irc.freenode.net/postgresql, where there are friendly, helpful people, and occasionally Yours Truly. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Sorting array field
On Thu, Dec 22, 2005 at 08:38:46AM -0700, Pete Deffendol wrote:
Hi,
Can anyone point me toward an SQL function (whether built-in or an
add-on) that will allow me to sort the contents of an array datatype
in an SQL query?
Something like this:
select sort(my_array_field) from my_table;
Here's one way using only SQL. I do not make any guarantees about its
performance, though ;)
CREATE TABLE my_table (my_array text[]);
INSERT INTO my_table VALUES('{r,e,d,q}');
INSERT INTO my_table VALUES('{c,b,a}');
INSERT INTO my_table VALUES('{one,two,three,four}');
SELECT
ARRAY(
SELECT t.my_array[s.i]
FROM generate_series(
array_lower(my_array,1), /* usually 1 */
array_upper(my_array,1)
) AS s(i)
ORDER BY t.my_array[s.i]
) AS sorted_array
FROM my_table t
ORDER BY sorted_array DESC;
HTH :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
---(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] Why is create function bringing down the Backend server?
On 12/22/05, Carlos Moreno [EMAIL PROTECTED] wrote: The problem is, when I execute the SQL statement: create or replace function sha1 ; for the second time (i.e., after making modifications and recompiling), the *backend* crashes -- it then restarts automatically, and then I run again the create or replace statement, and it works now (and the function seems to work fine -- well, in its final version it does). You should see if there's something in server log. And then indeed, try to gdb it. You can run Postgres in non-daemon mode with command 'postgres -D datadir database'. The stripped pgcrypto.c you posted - your wrapper function looks fine, only problem I see is that you deleted function find_provider that is used by pg_digest, so there will be undefined function in final .so. But that should not crash the server, so gdb trace could be still useful. Anyway, I wanted to add hash functions (SHA-1 is already there, so I'd like to add SHA-256 and SHA-512 at the very least, and maybe also, for completeness, SHA-224 and SHA-384). For SHA2 hashes it should be enough to compile pgcrypto against OpenSSL 0.9.8. Or upgrade to PostgreSQL 8.1, where they are included. Ofcourse, that is no fun. If you want to hack, you could try adding SHA224 to the SHA2 implementation in 8.1. There are currently only SHA256/384/512 hashes implemented. (AFAIR it is basically truncated SHA256 but with different init vector) -- marko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Stored procedure
Hi Jaime,
Thanks.
I'd suggest the manual be edited by an educator, since it is a little dense
for someone coming to it for the first time. Once I read your reply to me,
and reread the manual, I understood. But on first reading, it is a little
too dense and short on examples.
Regarding serial:
I now learned (well, late last might) in your documentation that
Postgres has
additional types (serial, serial4, c.) that are integers that are
autoincremented.
serial is not a type is a shorthand for integer with a default
expresion that retrives next value in a sequence...
I tried, therefore, to change these columns to type serial but
received an error
stating that type serial does not exist.
how did you try? what was the exact error you receive?
I tried:
ALTER TABLE People.addy ALTER COLUMN aid TYPE serial
and the error I received is:
ERROR: type serial does not exist
I understand this now, but it seems pgAdmin creates the illusion serial can
be treated like genuine types by including serial along with all the other
types in the drop down list used to set type when creating a new column.
Regarding autoincrement:
INSERT INTO foo (auto,text)
VALUES(NULL,'text'); # generate ID by inserting NULL
and this of course is bad... if a insert NULL i want the NULL to be
inserted.
In programming in C++, I often pass a value of null or 0 as an argument to a
function; this is done to use the null value as a flag to control the
behaviour of the function at a very fine degree of granularity. This is a
commonly used and powerful idiom in C++ programming.It is curious, though,
that on thinking about this, I have not used this idiom nearly as much when
I am programming in Java. I can't explain why.
SQL Standard way of doing things is ommiting the auto incremental fld at
all
INSERT INTO foo (text) VALUES ('text');
INSERT INTO foo2 (id,text)
VALUES(LAST_INSERT_ID(),'text'); # use ID in second table
INSERT INTO foo2 (id, text) VALUES (currval('seq'), 'text')
On reading more about this, my inclination was to do as you did. However, I
would point out that the sample code I showed here was taken directly from
the MySQL reference manual. If it matters, I can provide the precise
location in the manual. I guess some folk disagree with you about how good
or bad it is. I'll reserve judgement until I have more experience working
with databases.
Assuming I have set up a sequence called 'seq', and set the default value of
id in foo to be nextval('seq'), then the following is getting close to what
I need (there seems to be only one thing left - what do I replace the
question mark with in order to get the id value from the initial select and
pass it to the insert in the first block):
if exists(select id from foo where x = text) then
INSERT INTO foo2 (foo_id, foo2_text) VALUES (?,more_text)
else
INSERT INTO foo (text) VALUES ('text')
INSERT INTO foo2 (foo_id, foo2_text) VALUES (currval('seq'),
more_text)
end if;
The second block of the conditional statement looks like it ought to
properly handle inserting new data into foo, autoincrementing id in foo and
providing the value of id to the insert into foo2. However, for the first
block, there is no way to know where 'text' is located in the table, so it
is necessary to get the value of id from the SQL statement used as the
argument for exists() and pass it to the insert into foo2 (where the
question mark is located).
Thanks for your time.
Ted
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Stored procedure
Assuming I have set up a sequence called 'seq', and set the default value of
id in foo to be nextval('seq'), then the following is getting close to what
I need (there seems to be only one thing left - what do I replace the
question mark with in order to get the id value from the initial select and
pass it to the insert in the first block):
if exists(select id from foo where x = text) then
INSERT INTO foo2 (foo_id, foo2_text) VALUES (?,more_text)
else
INSERT INTO foo (text) VALUES ('text')
INSERT INTO foo2 (foo_id, foo2_text) VALUES (currval('seq'),
more_text)
end if;
The second block of the conditional statement looks like it ought to
properly handle inserting new data into foo, autoincrementing id in foo and
providing the value of id to the insert into foo2. However, for the first
block, there is no way to know where 'text' is located in the table, so it
is necessary to get the value of id from the SQL statement used as the
argument for exists() and pass it to the insert into foo2 (where the
question mark is located).
Thanks for your time.
Ted
maybe you can rewrite this to something else:
in the declare section declare a var
declare
var1 foo.id%TYPE;
[...and then in the begin section, where all code happens...]
select into var1 id from foo where x = text;
if var1 is not null then
INSERT INTO foo2 (foo_id, foo2_text) VALUES (var1,more_text)
else
INSERT INTO foo (text) VALUES ('text')
INSERT INTO foo2 (foo_id, foo2_text) VALUES (currval('seq'),
more_text)
end if;
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] [Slony1-general] Mem usage/leak - advice needed
Thanks for your response. None of the data rows are wide (as far as I can remember). We don't have any blob data, and any text fields only contain several hundred bytes at most (and even those would be rare). Just stopping and starting the slon process on the slave node doesn't seem to help much. Stopping postgres on the slave itself seems to be also required. I'm wondering if this requirement is due to the continued running of the slon psocess on the master. Does it makes sense that shutting down the slave postgres db is necessary? Or would stopping and restarting ALL slon processes on all nodes mean that I wouldn't have to stop and restart the slave postgres DB? Thanks John Ian Burrell wrote: On 12/22/05, John Sidney-Woollett [EMAIL PROTECTED] wrote: In trying to investigate a possible memory issue that affects only one of our servers, I have been logging the process list for postgres related items 4 times a day for the past few days. This server uses postgres 7.4.6 + slon 1.1.0 on Debian i686 (Linux server2 2.6.8.1-4-686-smp) and is a slon slave in a two server replicated cluster. Our master DB (similar setup) does not exbibit this problem at all - only the subscriber node... The load average starts to go mental once the machine has to start swapping (ie starts running out of physical RAM). The solution so far is to stop and restart both slon and postgres and things return to normal for another 2 weeks. I know other people have reported similar things but there doesn't seem to be an explanation or solution (other than stopping and starting the two processes). Can anyone suggest what else to look at on the server to see what might be going on? Appreciate any help or advice anyone can offer. I'm not a C programmer nor a unix sysadmin, so any advice needs to be simple to understand. The memory usage growth is caused by the buffers in the slave slon daemon growing when long rows go through them. The buffers never shrink while the slon daemon is running. How big is the largest rows which slon replicates? One suggestion I have seen is to recompile slon to use fewer buffers. Another is to set a ulimit for memory size to automatically kill the slon daemons when they get too big. The watchdog will then restart them. Alternatively, your strategy of restarting the slon daemons each week will work (you don't need to restart postgres). I came up with a patch which shrinks the buffers when they go above a certain size. This doesn't fix the problem of lots of big rows happening at once but it fixes the gradual growth. - Ian ---(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] Why is create function bringing down the Backend server?
Marko Kreen wrote: On 12/22/05, Carlos Moreno [EMAIL PROTECTED] wrote: The problem is, when I execute the SQL statement: create or replace function sha1 ; for the second time (i.e., after making modifications and recompiling), the *backend* crashes -- it then restarts automatically, and then I run again the create or replace statement, and it works now (and the function seems to work fine -- well, in its final version it does). You should see if there's something in server log. The only thing that does show does not seem to say much: LOG: server process (PID 12885) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing Not sure what the meaning of signal 11 is with PG (AFAIR, it's one of the SIGUSER values, right?) And then indeed, try to gdb it. You can run Postgres in non-daemon mode with command 'postgres -D datadir database'. The stripped pgcrypto.c you posted - your wrapper function looks fine, only problem I see is that you deleted function find_provider that is used by pg_digest, so there will be undefined function in final .so. Oh no!! That was only in the function I posted, so that the file is kept as short as possible -- in the one that I compiled, I left everything untouched, and only added my functions. But that should not crash the server, so gdb trace could be still useful. Ok, will try to do it and post any interesting discoveries (I can't find any core files, so I guess I'll have to try gdbing it) Anyway, I wanted to add hash functions (SHA-1 is already there, so I'd like to add SHA-256 and SHA-512 at the very least, and maybe also, for completeness, SHA-224 and SHA-384). For SHA2 hashes it should be enough to compile pgcrypto against OpenSSL 0.9.8. Or upgrade to PostgreSQL 8.1, where they are included. Ofcourse, that is no fun. Hahahaha -- why do I keep being naive and making the same mistake over and over!!! :-) As much as it is indeed no fun, it is also good to know (and I didn't know that OpenSSL 0.9.8 had them either, so thanks for the double pointer!) If you want to hack, you could try adding SHA224 to the SHA2 implementation in 8.1. Sounds like a plan :-) Thanks, Carlos -- ---(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] view or index to optimize performance
thanks for the help What you could do is partition the table so that critical information is stored in a smaller table while everything else goes to a larger table. I was thinking the other way round - maybe I can split the large table by creating a materialized view. But than I read that it is maybe unnecessary to create a materialized view because pg Materialized views sound a useful workaround, if your database doesn't have a query cache. If you do have a query cache, then you already effectively have eager or lazy materialized views (depending on your isolation level): Just use your normal view (or query) and let the database figure it out. Quote from Farce Pest in http://spyced.blogspot.com/2005/05/materialized-views-in-postgresql.html But later in the same blog it seems to indicate that there is a choice to either use or not use the query cache of pg. So I don't know now how this cache works and whether it could help me in this. SWK ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] view or index to optimize performance
=?iso-8859-1?Q?Klein_Bal=E1zs?= [EMAIL PROTECTED] writes: But later in the same blog it seems to indicate that there is a choice to either use or not use the query cache of pg. Hm? There is no query cache in PG. 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
Re: [GENERAL] Inheritance Algebra
On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote: On Wed, Dec 21, 2005 at 01:52:34PM +0100, Martijn van Oosterhout wrote: On Sun, Dec 04, 2005 at 10:59:10PM -0700, Trent Shipley wrote: Relational Constraint Inheritance Algebra With regard to class and attribute uniqueness It's taken a while to digest this and sorry for the delay. While I find the ideas intreguing there is a little voice in the back of my head asking: practical applications? I would assume quite a few people would use table inheritance in a simple way were it available in a more convenient fashion: to transport fields, primary and foreign keys to child tables. I am not clear on why this sort of scenario benefits more from CREATE TABLE's INHERITS clause than the LIKE clause (assuming that LIKE copied the appropriate table properties). Indeed, the recursive SELECT associated with INHERITS might be undesirable. If I understand you [Karsten] correctly then the really elegant way to do this is with a DECLARE or DEFINE TABLE|INDEX|FOREIGN KEY|... definition_name (definition_clause) (The choice of DECLARE or DEFINE would depend on the SQL list of reserved words.) Then instantiate the declared object with something like: CREATE TABLE|INDEX|... object_name USING definition_name. Changes in definition (ALTER DEFINITION)should optionally cascade to instantiated objects. Use ALTER TABLE to create variant tables. Very useful for creating things that often get quashed and re-created, like temporary tables and indexes. Also very useful for things that should be uniform but get attached to many tables, like annoying ubiquitous check constraints, indexes, or foreign keys. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] problems with currval and rollback
Hi all,
So I started to make some changes with my code here and there
(nothing to do with the relevant lines) and suddenly currval and
rollback don't work.
try{
db.setAutoCommit(false);
addLM.setInt(1, lm.getOrigin());
...
addLM.executeUpdate();
sql = db.createStatement();
ResultSet result = sql.executeQuery(SELECT
currval('lm_id_seq'););
if (result. next()){
db.commit();
db.setAutoCommit(true);
else{
db.rollback(); //reverse all changes
db.setAutoCommit(true);
}
catch(SQLException ex){
try {
db.rollback();
db.setAutoCommit(true);
} catch (SQLException e) {
throw e;
}
throw ex;
}
What happens is that the it inserts a db, but cannot get a value back
using currval (ERROR: ERROR: currval of sequence lm_id_seq is not
yet defined in this session 55000 ) , yet the db gets the addlm
inserted.
Shouldn't it rollback?
Furthermore, why would currval suddenly stop working?
Much thanks for your feedback in advance.
-assad
---(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] problems with currval and rollback
Uh, you really didn't give us enough information to diagnose this. I
recommend you find the queries that are being run by Java and type them
into psql to see if they work there. If not, please show them to us.
---
Assad Jarrahian wrote:
Hi all,
So I started to make some changes with my code here and there
(nothing to do with the relevant lines) and suddenly currval and
rollback don't work.
try{
db.setAutoCommit(false);
addLM.setInt(1, lm.getOrigin());
...
addLM.executeUpdate();
sql = db.createStatement();
ResultSet result = sql.executeQuery(SELECT
currval('lm_id_seq'););
if (result. next()){
db.commit();
db.setAutoCommit(true);
else{
db.rollback(); //reverse all changes
db.setAutoCommit(true);
}
catch(SQLException ex){
try {
db.rollback();
db.setAutoCommit(true);
} catch (SQLException e) {
throw e;
}
throw ex;
}
What happens is that the it inserts a db, but cannot get a value back
using currval (ERROR: ERROR: currval of sequence lm_id_seq is not
yet defined in this session 55000 ) , yet the db gets the addlm
inserted.
Shouldn't it rollback?
Furthermore, why would currval suddenly stop working?
Much thanks for your feedback in advance.
-assad
---(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
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] is this a bug or I am blind?
Tom has applied a patch to fix this and backpatched it to all relivant branches. He might be preparing a summary email about this. --- Mage wrote: Martijn van Oosterhout wrote: On Sat, Dec 17, 2005 at 05:01:15PM -0500, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: I think the real solution is to implement COLLATE support. Maybe so, but we still need to figure out what we're doing for the back branches, and that won't be it ... To be honest, there are really only a handful of locales that suffer from this issue, so perhaps we should document it and move on. I don't agree. Usually I read the whole documentation of the software I use, but you cannot presume that every user even with good sql skills will check the documentation for a thing he wouldn't imagine. With knowing the background it is understandable locale problem, but in the user's point of view it's a weird and serious bug which shouldn't be there. Using hu_HU with latin2 is a normal marrying. Some users (including me) don't always read the known issues chapter, even for a good quality software. Mage ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] problems with currval and rollback
I am not sure what do you mean (In terms of what more do you need).
For the java code:
The insert works. (cause i see it in the db).
the currval does not work anymore. (see error message below).
And for some reason, it still inserts into the db, regardless of the
rollback (and setAutocommit(false))
For the SQL code itself
[here is the query being performed]
INSERT INTO lm values (DEFAULT, DEFAULT, DEFAULT, 'jarraa', DEFAULT,
ROW(point(0,0),0), '', 'jarraa','jarraa', '', 'blah', DEFAULT);
SELECT currval('lm_id_seq');
I perform this back to back in the db, and the currval works fine.
What gives? It used to work in the java code, but now it doesn't!
Also, please comment on your thoughts about the rollback.
Much thanks for your help.
-assad
On 12/22/05, Bruce Momjian pgman@candle.pha.pa.us wrote:
Uh, you really didn't give us enough information to diagnose this. I
recommend you find the queries that are being run by Java and type them
into psql to see if they work there. If not, please show them to us.
---
Assad Jarrahian wrote:
Hi all,
So I started to make some changes with my code here and there
(nothing to do with the relevant lines) and suddenly currval and
rollback don't work.
try{
db.setAutoCommit(false);
addLM.setInt(1, lm.getOrigin());
...
addLM.executeUpdate();
sql = db.createStatement();
ResultSet result = sql.executeQuery(SELECT
currval('lm_id_seq'););
if (result. next()){
db.commit();
db.setAutoCommit(true);
else{
db.rollback(); //reverse all changes
db.setAutoCommit(true);
}
catch(SQLException ex){
try {
db.rollback();
db.setAutoCommit(true);
} catch (SQLException e) {
throw e;
}
throw ex;
}
What happens is that the it inserts a db, but cannot get a value back
using currval (ERROR: ERROR: currval of sequence lm_id_seq is not
yet defined in this session 55000 ) , yet the db gets the addlm
inserted.
Shouldn't it rollback?
Furthermore, why would currval suddenly stop working?
Much thanks for your feedback in advance.
-assad
---(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
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] problems with currval and rollback
Assad Jarrahian wrote:
I am not sure what do you mean (In terms of what more do you need).
For the java code:
The insert works. (cause i see it in the db).
the currval does not work anymore. (see error message below).
And for some reason, it still inserts into the db, regardless of the
rollback (and setAutocommit(false))
For the SQL code itself
[here is the query being performed]
INSERT INTO lm values (DEFAULT, DEFAULT, DEFAULT, 'jarraa', DEFAULT,
ROW(point(0,0),0), '', 'jarraa','jarraa', '', 'blah', DEFAULT);
SELECT currval('lm_id_seq');
I perform this back to back in the db, and the currval works fine.
What gives? It used to work in the java code, but now it doesn't!
Also, please comment on your thoughts about the rollback.
Much thanks for your help.
I recommend you ask jdbc questions on the jdbc email list.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] query for a time interval
On Thu, Dec 22, 2005 at 09:47:11AM -0600, Jim C. Nasby wrote: On Wed, Dec 21, 2005 at 11:52:56AM -0800, Mark wrote: SELECT id FROM mq WHERE now - start_date time_to_live; The problem is you can't use an index on this, because you'd need to index on (now() - start_date), which obviously wouldn't work. Instead, re-write the WHERE as: WHERE start_date now() - time_to_live Unless I'm missing something that wouldn't use an index either, because the planner wouldn't know what value to compare start_date against without hitting each row to find that row's time_to_live. But something like this should be able to use an expression index on (start_date + time_to_live): WHERE start_date + time_to_live now() -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] problems with currval and rollback
Hi,
Refer http://www.postgresql.org/docs/8.1/static/functions-sequence.html
On 12/23/05, Assad Jarrahian [EMAIL PROTECTED] wrote:
Hi all,
So I started to make some changes with my code here and there
(nothing to do with the relevant lines) and suddenly currval and
rollback don't work.
try{
db.setAutoCommit(false);
addLM.setInt(1, lm.getOrigin());
...
addLM.executeUpdate();
sql = db.createStatement();
ResultSet result = sql.executeQuery(SELECT
currval('lm_id_seq'););
if (result. next()){
db.commit();
db.setAutoCommit(true);
else{
db.rollback(); //reverse all changes
db.setAutoCommit(true);
}
catch(SQLException ex){
try {
db.rollback();
db.setAutoCommit(true);
} catch (SQLException e) {
throw e;
}
throw ex;
}
What happens is that the it inserts a db, but cannot get a value back
using currval (ERROR: ERROR: currval of sequence lm_id_seq is not
yet defined in this session 55000 ) , yet the db gets the addlm
inserted.
Shouldn't it rollback?
Furthermore, why would currval suddenly stop working?
Much thanks for your feedback in advance.
-assad
---(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
--
Regards
Pandu
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] problems with currval and rollback
Without seeing the SQL statements you executed in the session it is difficult to see your problem. Getting a current sequence after a rollback is no problem (in 8.0 anyway). Please note though, the sequence itself is NOT rolled back. This is correct behaviour. Currval will return the last sequence retrieved for the session (using nextval), even if other sessions have accessed the sequence in the intervening time. Eddy Assad Jarrahian wrote: Hi all, So I started to make some changes with my code here and there (nothing to do with the relevant lines) and suddenly currval and rollback don't work. snip content=code/ What happens is that the it inserts a db, but cannot get a value back using currval (ERROR: ERROR: currval of sequence lm_id_seq is not yet defined in this session 55000 ) , yet the db gets the addlm inserted. Shouldn't it rollback? Furthermore, why would currval suddenly stop working? Much thanks for your feedback in advance. -assad ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
