Re: [GENERAL] Can this pl/pgsql be simplified?
On Fri, 2005-11-25 at 12:19 -0800, CSN wrote: I have a trigger function that simply updates item counts when the items table changes (member_id or active changes). I'm curious if this bit of the code can be simplified? :) thanks csn ELSIF TG_OP = 'UPDATE' THEN IF (OLD.member_id is NULL and NEW.member_id is not null) or (OLD.member_id is not NULL and NEW.member_id is null) or OLD.member_id NEW.member_id THEN IF OLD.member_id is not null then IF OLD.active is true then update members set items_submitted=items_submitted-1, items_approved=items_approved-1 where id=OLD.member_id; ELSE update members set items_submitted=items_submitted-1 where id=OLD.member_id; END IF; END IF; IF NEW.member_id is not null then IF NEW.active is true then update members set items_submitted=items_submitted+1, items_approved=items_approved+1 where id=NEW.member_id; ELSE update members set items_submitted=items_submitted+1 where id=NEW.member_id; END IF; END IF; ELSIF OLD.active is false and NEW.active is true then update members set items_approved=items_approved+1 where id=NEW.member_id; ELSIF OLD.active is true and NEW.active is false then update members set items_approved=items_approved-1 where id=NEW.member_id; END IF; I think this is logically equivalent: IF OLD.member IS DISTINCT FROM NEW.member then IF OLD.member_id is not null then update members set items_submitted=items_submitted-1, items_approved=items_approved-(CASE WHEN OLD.active THEN 1 ELSE 0 END) where id=OLD.member_id; END IF; IF NEW.member_id is not null then update members set items_submitted=items_submitted+1, items_approved=items_approved+(CASE WHEN NEW.active THEN 1 ELSE 0 END) where id=NEW.member_id; END IF; ELSIF OLD.active NEW.active then update members set items_approved=items_approved+(CASE WHEN NEW.active THEN 1 ELSE -1 END) where id=NEW.member_id; END IF; -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] invalid page header in block 597621 of relation...error
On 26/11/05 4:48 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: I deleted the two datasets in mba_data_base that were affected by the empty pages, I also deleted the relevant two rows in measured_bioassay_base... But maybe it didn't do the right thing with the toast table for these two rows? Evidently the missing data in the toast table is associated with yet a different dataset. I'd suggest first looking into the toast table to see if you can confirm that the missing data corresponds to a swath of zeroed-out pages. If that's the case then it gives even more urgency to the need to find out what's going wrong with your filesystem (or possibly your disk drive, but my gut feel is that this is a kernel filesystem problem). The other thing you'd need to do is figure out which dataset you have to reload. A tedious way to do this is something like select sum(length(bigfield)) from maintable where dataset = 'xxx'; for various values of xxx until you see the error. Well I tracked down which row went wrong and deleted that dataset also, the backups worked fine and it seems to be ok now. Not really sure what caused all this, all these datasets (190 in total) went in in one batch the other day, so for some reason 3 of them got screwed up. Anyway I have upgraded the box to linux 2.6.14.3, so I will keep an eye on it and see how things go. Thanks again for your help Tom Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] PostgreSQL and SAN/NAS technologies
Hi everyone, We're looking at investing in a new storage system in order to run PostgreSQL and the advice we are getting is to move away from our current SAN solution to a NAS solution. Can anyone offer any advice/experience on using NAS devices to run a PostgreSQL database? I have seen posts on the lists suggesting that NFS could not offer the correct semantics to ensure database consistency in terms of flushing data to disk, but the pre-sales technical team insist it can, even though I pointed out on their demo system that even MS-SQL server won't install its data directory on the NAS without a special driver. So have there been any improvements in the NFS protocol that would make this the case? I suspect not, but thought I would ask on the list to make sure. Secondly, we currently run the database over 1Gb fibre. Assuming that NFS is a no-go, the other solution is to use the NAS and run iSCSI over 1Gb Ethernet. Slightly off-topic for pgsql-general, but can anyone offer any performance comparisons between our existing 1Gb SAN and a 1Gb NAS solution? My feeling is that the SAN will be faster but I have no real way of testing. Finally, if it helps, the database server is a dual Opteron with 12Gb RAM running FC4. Thanks in advance, Mark. WebBased Ltd 17 Research Way Plymouth PL6 8BT T: +44 (0)1752 797131 F: +44 (0)1752 791023 http://www.webbased.co.uk http://www.infomapper.com http://www.swtc.co.uk This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Usability Question
[EMAIL PROTECTED] wrote: Reading through the FAQ and Online docs I found the comment that in most cases virus protecting and/or firewall software causes this problem and the recommendation to stop using or even to uninstall this software. It is hard to believe that this is a serious advice. Is one really expected to quite using antivirus/firewall software if one wants to use postgres? I would imagine that this would be a knock-out criteria for the product not just for me, but for a lot of people. I think the point is to stop the anti-virus software interfering with PostgreSQL accessing its files. Obviously this sort of thing can cause problems with any database. The problem is - how do you provide detailed advice on how to do this for every version of every anti-virus package in every configuration? So - if you don't know how to configure your anti-virus/firewall software, or read system/application logs, there's not much the project can do to even help. That seems to include a lot of Windows users (who may even just be running the av/firewall software that came with the machine and not even know what it's doing for them). The two things you need to allow are: 1. Access to whatever network port(s) you have PG listening on. 2. Access to PG's files (and they're all in one place by default) to the various PG processes. Alternatively, you could switch to one of the Unix/Linux/BSD distributions which tend to manage system integration a little better than MS-Windows. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] need help in designing a database.
Haim Ashkenazi wrote: Hi I'm writing an application that calculates working hours for billing. there are three levels of billing. one for regular hours, one for evening/nights, and one for saturdays/holidays. My first decision is wether I make these calculations in a database view (If I understand correctly, it would be calculated only once - when creating the entry or modify it - or am I wrong?) Wrong. A view is just a way of transforming one query into another - it re-runs the query every time. or just put the regular working hours in the database, and calculate it in the application (and this way it would be calculated every time I view the page)? If I'm right and it's better to put it in the database, I really need help designing the database... (of-course it's an open-source project). my idea is to create one table that define default hours/days ranges for the three levels of billing, one table to define client specific ranges, a table with charging information (charge per hour for every level of billing for every customer), tables that define custommers, and job details and a final view that summerize everything. my main problem is how do I define an hour range from friday 15:00 to sunday 08:00, and how to create a function that compares the working hours with this range? Hmm - sounds to me like you either: 1. Need to allow some time to go and learn the basics of relational theory, and then some SQL and PostgreSQL admin. 2. Find another team-member for your project who has these skills. 3. Find another project that has solved the same problems. Join that project or re-use elements (licence allowing). Now, #3 seems to be the best approach to me. Any timesheet/billing application will have to deal with the sort of problem you're describing, and it makes sense to learn what approaches others have tried. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postmaster does not come up
it just times out and nothing was there in the logs ... i just specified the log file name while starting postmsater but it had nothing From: hubert depesz lubaczewski [mailto:[EMAIL PROTECTED]Sent: Fri 11/25/2005 1:22 PMTo: surabhi.ahujaCc: pgsql-general@postgresql.orgSubject: Re: [GENERAL] postmaster does not come up *** Your mail has been scanned by InterScan VirusWall. ***-*** On 11/25/05, surabhi.ahuja [EMAIL PROTECTED] wrote: if i try to start postmaster ...it times out. what can be the possible cause of it . I also have seen a core file being generated. i ll again copy paste the script i am using for starting up and shutting down postmaster POSTGRES_LOG="$SDCHOME/nuevo/logfiles/postgreslog"what does the log say?depesz
Re: [GENERAL] regarding the apostrophe character
Title: Re: [GENERAL] regarding the apostrophe character and what about the backslashcharacter ... do we need to escape it as well? or it is treated as a normal character From: Michael Glaesemann [mailto:[EMAIL PROTECTED]Sent: Fri 11/25/2005 4:17 PMTo: surabhi.ahujaCc: pgsql-general@postgresql.orgSubject: Re: [GENERAL] regarding the apostrophe character ***Your mail has been scanned by InterScan VirusWall.***-***On Nov 25, 2005, at 13:40 , surabhi.ahuja wrote: in which version will the use of "Escape string syntax " be supported ..I know they work in 8.1. You can check the release notes:http://www.postgresql.org/docs/current/interactive/release.htmlThe SQL standard way of escaping single-quotes is ''.test=# select version();version--PostgreSQL 8.1.0 on powerpc-apple-darwin8.3.0, compiled by GCCpowerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc.build 5026)(1 row)test=# select 'foo''s bar' as sql_standardtest-# , e'foo\'s bar' as e_escapetest-# , $$foo's bar$$ as dollar_quote;sql_standard | e_escape | dollar_quote--+---+--foo's bar | foo's bar | foo's bar(1 row)Michael Glaesemanngrzm myrealbox com
Re: [GENERAL] regarding the apostrophe character
On Nov 28, 2005, at 21:20 , surabhi.ahuja wrote: and what about the backslash character ... do we need to escape it as well? or it is treated as a normal character What have you tried? What do the docs say? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] strange behaviour in plpgsql:null arguments
Hi, I think I found something that is strange. I can't detect whether i passed a NULL to stored procedure in plpgsql when it happens that I use composites I warn that I haven't read the sql standards though. Here is the code: CREATE TYPE ttype AS ( t1 int, t2 int ); CREATE OR REPLACE FUNCTION ttypetest(t ttype) RETURNS ttype AS $$ BEGIN IF t IS NULL THEN RAISE EXCEPTION 'NULL input'; END IF; RETURN t; END $$ LANGUAGE plpgsql; SELECT ttypetest(NULL); I also find it quite difficult to unset an element from array in postgres. Regards, Peter Filipov ---(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] Help with COPY Error: invalid UTF-8 byte ....
Hi, I am having a problem with a copy command, saying ERROR: invalid UTF-8 byte sequence detected... The problem actually is that the entire copy job terminates instead of just ignoring the record in question. Is there a way to have faulty records ignored only without terminating the entire copy job? Alex ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Errors with temporary tables
Jens Wendelmuth [EMAIL PROTECTED] writes: I'm a newbie with PG and i do not understand the meaning of a table's rowtype not getting dropped when the table is dropped. Every table has an associated composite type of the same name, which represents the type of each row of the table. This type entry should go away automatically if the table is dropped. We've seen a few trouble reports that look like that mechanism failed for some reason, but no one yet has any clue why. Is this a failure of us or PG(8.1.0)? Is there a way to solve/workaround this error? It's certainly not your fault, but with so little information about what's happening or what causes it, it's hard to say how to fix it or avoid it. If you can come up with a test case that lets other people reproduce the problem, I'm sure we could fix it in short order... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] need help in designing a database.
On Mon, 28 Nov 2005 10:32:53 +, Richard Huxton wrote: Haim Ashkenazi wrote: Hi I'm writing an application that calculates working hours for billing. there are three levels of billing. one for regular hours, one for evening/nights, and one for saturdays/holidays. My first decision is wether I make these calculations in a database view (If I understand correctly, it would be calculated only once - when creating the entry or modify it - or am I wrong?) Wrong. A view is just a way of transforming one query into another - it re-runs the query every time. or just put the regular working hours in the database, and calculate it in the application (and this way it would be calculated every time I view the page)? If I'm right and it's better to put it in the database, I really need help designing the database... (of-course it's an open-source project). my idea is to create one table that define default hours/days ranges for the three levels of billing, one table to define client specific ranges, a table with charging information (charge per hour for every level of billing for every customer), tables that define custommers, and job details and a final view that summerize everything. my main problem is how do I define an hour range from friday 15:00 to sunday 08:00, and how to create a function that compares the working hours with this range? Hmm - sounds to me like you either: 1. Need to allow some time to go and learn the basics of relational theory, and then some SQL and PostgreSQL admin. 2. Find another team-member for your project who has these skills. 3. Find another project that has solved the same problems. Join that project or re-use elements (licence allowing). Now, #3 seems to be the best approach to me. Any timesheet/billing application will have to deal with the sort of problem you're describing, and it makes sense to learn what approaches others have tried. thanx, I was already told that a view gets calulated every time. in this case I'll write the logic in java (seems to be a nice chalange for a beginer :) ). Bye -- Haim ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Errors with temporary tables
Hi Tom, Tom Lane wrote: The first of these looks like the same problem we've seen reported before of a table's rowtype not getting dropped when the table is dropped. I'm a newbie with PG and i do not understand the meaning of a table's rowtype not getting dropped when the table is dropped. Is this a failure of us or PG(8.1.0)? Is there a way to solve/workaround this error? If this error occurs, what happens to the SQL query? Is it completely processed or aborted? The second one is new though. Can you reproduce it? If so, a debugger backtrace from the errfinish() call would be very helpful. Currently we're not able to directly reproduce this strange error. If so it will be posted. Thanks in advance and best regards Jens ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] how to get size of array in function
Hi list, i made one function and the argument of that function is string array, now how can i get the size of that array?? Thanks, Amit Soni -- Netcore Solutions Pvt. Ltd. Website: http://www.netcore.co.in Spamtraps: http://cleanmail.netcore.co.in/directory.html Support FAQ : http://support.netcore.co.in/ -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL and SAN/NAS technologies
Hi, i have seen both mysql and postgresql running on Network Aplliance Filer via NFS (UNIX/BSD Server). NFS is slower than SAN or local Disks, with NFS it is easier to move Data from one server to another server. NFS tuning is tricky, Netapp NFS with snaphosts are great. But if you have a lot of write access I would recommend SAN or local Disks with RAID (they are much cheaper). If you are using SAN or NAS you should have a good network and the SAN or NAS should be clustered. Your opteron system is this a HP DL 385 ? There you can use up to six 300 GByte SCSI Disk with a RAID Controller(with up to 192 MB Cache) and it would be normally the cheapest solution. -Franz -Ursprüngliche Nachricht- Von: Mark Cave-Ayland [mailto:[EMAIL PROTECTED] Gesendet: Montag, 28. November 2005 11:31 An: pgsql-general@postgresql.org Betreff: [GENERAL] PostgreSQL and SAN/NAS technologies Hi everyone, We're looking at investing in a new storage system in order to run PostgreSQL and the advice we are getting is to move away from our current SAN solution to a NAS solution. Can anyone offer any advice/experience on using NAS devices to run a PostgreSQL database? I have seen posts on the lists suggesting that NFS could not offer the correct semantics to ensure database consistency in terms of flushing data to disk, but the pre-sales technical team insist it can, even though I pointed out on their demo system that even MS-SQL server won't install its data directory on the NAS without a special driver. So have there been any improvements in the NFS protocol that would make this the case? I suspect not, but thought I would ask on the list to make sure. Secondly, we currently run the database over 1Gb fibre. Assuming that NFS is a no-go, the other solution is to use the NAS and run iSCSI over 1Gb Ethernet. Slightly off-topic for pgsql-general, but can anyone offer any performance comparisons between our existing 1Gb SAN and a 1Gb NAS solution? My feeling is that the SAN will be faster but I have no real way of testing. Finally, if it helps, the database server is a dual Opteron with 12Gb RAM running FC4. Thanks in advance, Mark. WebBased Ltd 17 Research Way Plymouth PL6 8BT T: +44 (0)1752 797131 F: +44 (0)1752 791023 http://www.webbased.co.uk http://www.infomapper.com http://www.swtc.co.uk This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] how to get size of array in function
Amit Soni [EMAIL PROTECTED] schrieb: Hi list, i made one function and the argument of that function is string array, now how can i get the size of that array?? Please read http://www.postgresql.org/docs/8.1/interactive/functions-array.html You can use array_lower/array_upper or array_dims. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] ANN: PgBrowse-1.3 is available.
PgBrowse ver 1.3 is a generic Postgresql database browser that works on Windows, Macintosh and Linux platforms that is written in Tcl/Tk. A Starpack is available for Linux/x86 and an application bundle is available for MacOSX. A couple of features that help differentiate this (free) tool. 1) No postresql software is actually needed on the client. 2) PgBrowser can display graphical images stored in the database as bytea or large objects (via the Img package). 3) PgBrower supports SQL libraries of your favorite queries. At startup PgBrowser looks for ~/SQLScripts and builds a menu of all members of the directory that end in .sql. Subdirectories will generate the appropriate submenu. 4) PgBrowser supports a history of queries/commands passed to the backend. Previous commands can be easily recalled from the keyboard. Gracefully exiting the program will cause the history to be stored in ~/SQLScripts as HiStOrY.tcl. This file will be sourced at program startup to recover the command history. 5) If PgBrowser is running on a Mac or Linux system that has psql located in a standard location, it is possible to execute psql commands from within PgBrowser. 6) Individual fields can be extracted and saved to user specified files. This includes bytea and large object fields. 7) A simple grid based database table editor is now available to make changes in the database. For more information and download visit: http://homepage.mac.com/levanj/TclTk Suggestions for improvements and bug fixes gladly accepted. Thanks, Jerry ---(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] Help with COPY Error: invalid UTF-8 byte ....
Alex wrote: Hi, I am having a problem with a copy command, saying ERROR: invalid UTF-8 byte sequence detected... The problem actually is that the entire copy job terminates instead of just ignoring the record in question. Is there a way to have faulty records ignored only without terminating the entire copy job? Not with COPY - you might want to look for a project called (iirc) pgloader though which might be useful to you. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] intarray index
Hello, I have a question about IntArray contrib package. The docs says: ...current implementation provides index support for one-dimensional array of int4's - gist__int_ops, suitable for small and medium size of arrays (used on default), and gist__intbig_ops for indexing large arrays (we use superimposed signature with length of 4096 bits to represent sets)... How many elements within single array is suggested within small/medium/large array ? I have arrays with up to 200 elements - which index I should use ? Thanks. ML ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] problem with psql?
We just ran an OS update on an Xserve which runs our PostgreSQL server, and now it seems that pg_ctl status doesn't report the correct status. ~ postgres$ pg_ctl status pg_ctl: neither postmaster nor postgres running ~ postgres$ ps auxw | grep postgres postgres 491 0.0 -0.133156 1160 ?? S 1:09PM 0:00.16 /usr/local/pgsql/bin/postmaster -D /Volumes/DBRAID/pgsql/data postgres 629 0.0 -0.031844216 ?? S 1:09PM 0:00.27 postgres: logger process postgres 631 0.0 -0.033140744 ?? S 1:09PM 0:00.06 postgres: writer process postgres 632 0.0 -0.031860276 ?? S 1:09PM 0:00.02 postgres: archiver process postgres 633 0.0 -0.032864164 ?? S 1:09PM 0:00.02 postgres: stats buffer process postgres 634 0.0 -0.031888284 ?? S 1:09PM 0:00.03 postgres: stats collector process We are able to connect to the instance (using psql), but cannot shut down or restart the instance using pg_ctl (it just reports that the server is not running). We are running PostgreSQL version 8.0.0 and Mac OS X 10.3.9. Can anyone tell us what's going on? Thanks! Tim ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] sub select as a data column
I am having trouble coming up with words to describe exactly what I need so searching for a solution is getting frustrating! If this has been answered twelve hundred times - please forgive me! I want to do in postgresql what I do in oracle: select year report_year, sum(amount), sum(select amount from my_table where year = report_year) from my_table group by report_year; I see lots of stuff on subselect in the from clause - but how do you subselect as a data column? Thanks for the help - it is SEVERLY appreciated! Ronni
Re: [GENERAL] Why pgAdmin III guru suggests VACUUM in 8.1
On Wed, Nov 23, 2005 at 04:56:58PM +0200, Andrus wrote: No. autovacuum is turned ON by default in 8.1 XP Hrm, interesting that it's different than on Unix. I read from the docs you mentioned that Postgres has low maintenance needs compared to other databases. So I'm expecting that there is no need to tune something. Initial tuning != maintenance. Many of PostgreSQL's default settings are extremely conservative and will benefit from being increased on almost any hardware. There's extensive discussion of this to be found in the pgsql-performance archives, but take a look at shared_buffers and work_memory at a minimum. -- 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] Slow pgdump
I'm making a bit of a guess here, but I suspect the issue is that a single large dump will hold a transaction open for the entire time. That will affect vacuums at a minimum; not sure what else could be affected. On Tue, Nov 22, 2005 at 05:13:44PM -0800, Patrick Hatcher wrote: OS - RH3 Pg - 7.4.9 Ram - 8G Disk-709G Raid 0+1 We are having a pgdump issue that we can't seem to find an answer for Background: Production server contains 11 databases of which 1 database comprises 85% of the 194G used on the drive. This one large db contains 12 schemas. Within the schemas of the large db, there maybe 1 or 2 views that span across 2 schemas. If we do a backup using pgdump against the entire database, it will take upwards of 8+ hours for the backup to complete. If we split the backup up to do a pgdump for the first 10 dbs and then do a pgdump by schema on the 1 large db, the the backup takes only 3.5hrs The other than using the schema switch, there is no compression happening on either dump. Any ideas why this might be happening or where we can check for issues? TIA Patrick Hatcher Development Manager Analytics/MIO Macys.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- 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] intarray index
On Mon, 28 Nov 2005, Marek Lewczuk wrote: Hello, I have a question about IntArray contrib package. The docs says: ...current implementation provides index support for one-dimensional array of int4's - gist__int_ops, suitable for small and medium size of arrays (used on default), and gist__intbig_ops for indexing large arrays (we use superimposed signature with length of 4096 bits to represent sets)... How many elements within single array is suggested within small/medium/large array ? I have arrays with up to 200 elements - which index I should use ? gist__intbig_ops Thanks. ML ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] problem with psql?
Timothy Perrigo [EMAIL PROTECTED] writes: We are able to connect to the instance (using psql), but cannot shut down or restart the instance using pg_ctl (it just reports that the server is not running). We are running PostgreSQL version 8.0.0 and Mac OS X 10.3.9. Can anyone tell us what's going on? Sounds to me like you're running pg_ctl with a $PGDATA setting that doesn't match where the server actually lives. Try show data_directory; in psql to verify what the server thinks $PGDATA is. A less likely possibility is that some outside force removed the $PGDATA/postmaster.pid file. If that's the case, signal the postmaster to shut down using kill -TERM (equivalent of normal shutdown) or kill -INT (equivalent of fast shutdown) and then restart it to recreate the pid file. (pg_ctl stop is actually just a wrapper around these signal operations...) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] sub select as a data column
Phong Ronni Bounmixay [EMAIL PROTECTED] writes: I want to do in postgresql what I do in oracle: select year report_year, sum(amount), sum(select amount from my_table where year = report_year) from my_table group by report_year; That doesn't really work in Oracle does it? It violates the SQL spec in at least three ways. Try something like select year as report_year, sum(amount), sum((select amount from my_table b where b.year = a.year)) from my_table a group by year; (which should work in Oracle too, or any other SQL-spec-compliant database). Note the extra parentheses ... they're not optional. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Group By?
So are you trying to get a list of all 'mon's and 'valve's for each
given association?
On Wed, Nov 23, 2005 at 04:31:04PM -0800, Bob Pawley wrote:
Guy
Sorry about the chart. It held together when I sent it to myself.
I'll try to make it clear in the way you suggest, by a truncated example.
Create table control (device_id serial, type varchar, association int4)
Create table auto_control (loop_id serial, monitor int4, valve int4)
Insert into control (type, association) Note - mon and valve are types of
device that together make a loop. A loop can be from 1 to 7 devices..
Values ('mon', '1') - serial 1
Values ('valve', '2') - serial 2
Values ('mon', '2') - serial 3
Values ('valve', '1') - serial 4
Values ('valve', '2') - serial 5
I want to transfer the serial device_id number for mon '1' into the same
row as valve '1' in the tables auto_control. Similarily
Those two rows would look like this.
Table (loop_id serial, monitor int4, valve int4)
Row 1 ( 1, 1, 4, )
Row 2 (2, 2, 3, 5)
Once this is done the devices will be organized into loops and each device
in the loop will have a direct link to other parts of the database.
I would like to know if this is possible with SQL, or would it be more
suited to the host language?
Would it be possible in SQL to have the information transferred into the
auto_control table as the information is being entered or would the control
table need to be fully completed?
Hope this is finally clear.
Thanks for you help.
Bob
- Original Message -
From: Guy Rouillier [EMAIL PROTECTED]
To: PostgreSQL General pgsql-general@postgresql.org
Sent: Wednesday, November 23, 2005 2:17 PM
Subject: Re: [GENERAL] Group By?
Bob Pawley wrote:
Bruno
The table I previously sent came through distorted and probabley
caused misunderstanding.
The table control and auto_control are both permanent table. I want to
reshuffle how the information is associated from one table to another
with the link between table by way of the device_id.
Following is the example which I stabilized and tested for e-mail.
Sorry, Bob, I think the reason you haven't gotten a response is that the
information you are trying to convey below is very unclear. Perhaps you
can just provide a create table statement and a bunch of insert
statements? Then summarize again what you are trying to accomplish.
Bob
Control
device_id
type
association
serial
varchar
int4
1
mon
1
2
valve
2
3
valve
1
4
mon
2
5
valve
1
Auto_control
loop_id
mon
valve_a
valve_b
serial
int4
int4
int4
1
1
3
5
2
2
4
- Original Message -
From: Bruno Wolff III [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Guy Rouillier [EMAIL PROTECTED]; Postgre General
pgsql-general@postgresql.org
Sent: Monday, November 21, 2005 10:07 PM
Subject: Re: Group By?
On Mon, Nov 21, 2005 at 21:53:10 -0800,
Bob Pawley [EMAIL PROTECTED] wrote:
Here's what I want to do.
Table control contains values (mon and valves) that are associated
by numbers inserted into the associated column.
I want to transfer the serial _id number of the items associated by
the value '1' into the appropriate columns of the first row of the
table auto_control. All items associated with the value '2' into the
second row - etc. etc.
You don't really want to do that. Tables have fixed numbers of
columns and what you want to do doesn't result in a fixed number of
columns.
If you want to generate a report with that format, then I think there
is a contrib module (crosstabs?) that will do this kind of thing. You
could also have a report app do it for you. In the report app method,
you would be best to return rows ordered by association and then
device_ID and have the app check for when the association value
changes.
Is this best accomplished by a 'group by' command or subset???
Bob
Control
device_ID type association
serial varchar int4
1 mon 1
2 valve 2
3 valve 1
4 mon 2
5 valve 1
Auto_control
loop_id mon valve valve
serial int4 int4 int4
1 1 3 5
2 2 4
- Original Message -
From: Guy Rouillier [EMAIL PROTECTED]
To: Postgre General pgsql-general@postgresql.org
Sent: Monday, November 21, 2005 4:25 PM
Subject: Re: [GENERAL] Group By?
Converted your message to plain text as preferred on most mailing
lists.
Bob Pawley wrote:
I want to take the serial ID of several values in different rows in
one table and insert them into a single
Re: [GENERAL] invalid page header in block 597621 of relation...error
On Thu, Nov 24, 2005 at 02:59:28PM -0500, Qingqing Zhou wrote: Tom Lane [EMAIL PROTECTED] wrote At this point I think there's no question that your filesystem is dropping blocks :-(. It is very interesting to follow this thread. But at this point, can you explain more why there is no question is file system's fault? Not to put words in Tom's mouth, but as he said there's very few ways that PostgreSQL will leave a blank page laying around, and the tests he had the OP perform show that this almost certainly isn't one of those cases. That means something other than PostgreSQL is dropping data. Since it's apparently multiples of 4k it's reasonable to suspect the kernel or the filesystem; it's pretty unlikely it's the drives. -- 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] dumping data to version control using pg_dump?
Closest you'll come is using copy... psql -c create temp table ...; copy from temp_table to file There has been some discussion about allowing COPY to work from either a view or an arbitrary SELECT statement; check the -hackers archives. On Thu, Nov 24, 2005 at 06:21:30PM +1100, Klint Gore wrote: Is there any way to get pg_dump to run a statement before dumping? I'd like to do something like pg_dump -a -c create temp table params as select * from params where key=blah; -d dev_db -t params -f /svn/db/params.blah svn commit /svn/db/params.blah I'd rather avoid doing psql -c create table params_svn as select * from params where key = blah; pg_dump . psql -c drop table params_svn replace tmptablename realtablename svn commit ... klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- 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] Set a blank password for a db user
Read up on pg_hba.conf: http://www.postgresql.org/docs/8.1/interactive/client-authentication.html You might not need to use password authentication. Short of that, have a look at http://www.postgresql.org/docs/current/static/libpq-pgpass.html On Thu, Nov 24, 2005 at 12:37:57AM -0800, Rembrandt wrote: Hi, I'm new to postgresql and i have an easy question to ask. I have installed postgresql 8.1 on windows 2000 and i want to use it with my app developed with php 5 + apache 2. All works well but i need to know if this is possible : I use pgAdminIII as administration tool an i need to create a restriceted grants db user that haven't a password set so i can connect from php using this statement : pg_connect (dbname=dbname user=username password=) As i saw, if i leave the password blank for the user username (inside the pgAdmin) i cant connect to db from php pages. Instead, if i set a password for the user and use this statement : pg_connect (dbname=dbname user=username password=aaa) the connection is established Is there a manner to avoid this ? Thanks in advance, Claudio ---(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 -- 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] sub select as a data column
You both are SO right! I sent that off and tried a couple of things and realized I was close but not close enough. I really appreciate the kind responses. Sometimes it's so hard to ask a question without feeling so foolish and then feeling worse when the answers are sarcastic. Thank you! Good luck to you both! You do such a good job in this group! RonniOn 11/28/05, Tom Lane [EMAIL PROTECTED] wrote: Phong Ronni Bounmixay [EMAIL PROTECTED] writes: I want to do in postgresql what I do in oracle: select year report_year,sum(amount), sum(select amount from my_table where year = report_year) from my_table group by report_year;That doesn't really work in Oracle does it?It violates the SQL specin at least three ways.Try something like select year as report_year, sum(amount), sum((select amount from my_table b where b.year = a.year))from my_table agroup by year;(which should work in Oracle too, or any other SQL-spec-compliant database).Note the extra parentheses ... they're not optional.regards, tom lane
[GENERAL] BUG when migrating from 8.0 to 8.1 with create temp table
Hi all,
I just migrated from 8.0 to 8.1 and one of my stored procecure just
hang when trying to create a temp table
Here is my call:
select * from usp_Comptabilite_AgeDeCompteClient('M', null,
'2005-01-28', '1', '1', '0', null)
If you look below in the log you will see that the function just hang
when creating T_CP1 table.
Is that a know bug .. this code worked with 8.0
Thanks for your help
Best Regards
/David
--- FUNCTION
CREATE OR REPLACE FUNCTION usp_Comptabilite_AgeDeCompteClient(VARCHAR,
VARCHAR, DATE, BOOLEAN, BOOLEAN, INT, VARCHAR) RETURNS refcursor AS $$
DECLARE
companyId ALIAS FOR $1;
Compte ALIAS FOR $2;
DateRef ALIAS FOR $3;
DateType ALIAS FOR $4;
Tri ALIAS FOR $5;
NBJour ALIAS FOR $6;
BORRNUMR ALIAS FOR $7;
DateRef_ DATE;
ref refcursor;
statement varchar(4000);
temp RECORD;
BEGIN
IF (DateRef IS NOT NULL) THEN
DateRef_:=DateRef;
ELSE
DateRef_:=CURRENT_DATE;
END IF;
-- Toutes les ressources présentes (incluant le regroupement comptable)
EXECUTE '
CREATE TEMP TABLE T_RR (
RRNUM VARCHAR(10) PRIMARY KEY
) ON COMMIT DROP';
EXECUTE '
CREATE TEMP TABLE T_CR1 (
CRNUM INT PRIMARY KEY,
CRYPNUM VARCHAR(10),
CRMONT DECIMAL,
CRDATE DATE,
CRRRNUM VARCHAR(10),
CRACNUM VARCHAR(10),
GLNUM VARCHAR(10),
GLDESC_PRI varchar (100),
GLDESC_SEC varchar (100),
NUM VARCHAR(10),
RRGROUP INT
) ON COMMIT DROP';
EXECUTE '
CREATE TEMP TABLE T_CP1 (
CRNUM INT PRIMARY KEY,
Paye DECIMAL
) ON COMMIT DROP';
EXECUTE '
CREATE TEMP TABLE T_RA (
RRNUM VARCHAR(10),
RANUM INTEGER
) ON COMMIT DROP';
EXECUTE '
CREATE TEMP TABLE T_CR2 (
CRNUM INT,
CRMONT NUMERIC,
CRDATE DATE,
CRRRNUM VARCHAR(10),
CRACNUM VARCHAR(10),
Solde NUMERIC,
GLNUM VARCHAR(10),
GLDESC_PRI VARCHAR(100),
GLDESC_SEC VARCHAR(100),
NUM VARCHAR(10),
RRGROUP INT
) ON COMMIT DROP';
-- Sélection des infos des comptes à recevoir
statement := ' INSERT INTO T_CR1 ( CRNUM, CRYPNUM, CRMONT, CRDATE,
CRRRNUM, CRACNUM,
GLNUM, GLDESC_PRI, GLDESC_SEC,
NUM,
RRGROUP)
LOG-
OG: statement: select * from usp_Comptabilite_AgeDeCompteClient('M',
null, '2005-01-28', '1', '1', '0', null)
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
t_rr_pkey for table t_rr
CONTEXT: SQL statement
CREATE TEMP TABLE T_RR (
RRNUM VARCHAR(10) PRIMARY KEY
) ON COMMIT DROP
PL/pgSQL function usp_comptabilite_agedecompteclient line 28 at
execute statement
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
t_cr1_pkey for table t_cr1
CONTEXT: SQL statement
CREATE TEMP TABLE T_CR1 (
CRNUM INT PRIMARY KEY,
CRYPNUM VARCHAR(10),
CRMONT DECIMAL,
CRDATE DATE,
CRRRNUM VARCHAR(10),
CRACNUM VARCHAR(10),
GLNUM VARCHAR(10),
GLDESC_PRI varchar (100),
GLDESC_SEC varchar (100),
NUM VARCHAR(10),
RRGROUP INT
) ON COMMIT DROP
PL/pgSQL function usp_comptabilite_agedecompteclient line 33 at
execute statement
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
t_cp1_pkey for table t_cp1
CONTEXT: SQL statement
CREATE TEMP TABLE T_CP1 (
CRNUM INT PRIMARY KEY,
Paye DECIMAL
) ON COMMIT DROP
PL/pgSQL function usp_comptabilite_agedecompteclient line 48 at
execute statement
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Strange VACUUM behaviour
One issue is that pg_toast tables can't vacuum rows until their
respective rows have been deleted by vacuuming the base table. But it's
still odd that the count decreases by 4 each time you run it.
As for the length of time, that could be due to heavily loaded hardware.
You might do better if you increase vacuum_memory (or whatever the
setting was called in 7.4...)
That index does have about 20% bloat though; so a reindex would probably
be a good idea.
You might ask on the slony list...
On Fri, Nov 25, 2005 at 02:34:45PM +0100, Florian G. Pflug wrote:
Hi
We started a VACUUM (not a VACUUM FULL) on one of your postgres 7.4.9
databases a few days ago. It's still running yet, and says the
folloing about once per second:
INFO: index pg_toast_2144146_index now contains 1971674 row versions
in 10018 pages
DETAIL: 4 index row versions were removed.
2489 index pages have been deleted, 0 are currently reusable.
The number of row versions decreases by 4 each time the message is logged.
The file belonging to pg_toast_2144146_index has about 80MB,
for pg_toast_2144146 there are 6 files, five of them are
1GB, the last one is about 5MB in size. The original relation
(the one that references pg_toast_2144146 in it's reltoastrelid field)
has one datafile of 11MB.
The original relation is called image, and is defined the following:
Table public.image
Column | Type | Modifiers
---++---
id| bigint | not null
image_code_id | bigint |
mandant_id| bigint |
name | text |
dat | text |
mime | text |
size | bigint |
md5 | bytea |
path | text |
copyright | character varying(255) |
Indexes:
image_pkey primary key, btree (id)
i_image_id btree (id)
Triggers:
_gti_denyaccess_17 BEFORE INSERT OR DELETE OR UPDATE ON image FOR
EACH ROW EXECUTE PROCEDURE _gti.denyaccess('_gti')
The table is part of a slony tableset, which is subscribed on this database.
Is there a reason that this vacuum takes so long? Maybe some lock
contention because slony replicates into this table?
greetings, Florian Pflug
--
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] Group By?
Yes I am trying to insert all valves into the same row as their associated
mon.
Bob
- Original Message -
From: Jim C. Nasby [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Guy Rouillier [EMAIL PROTECTED]; PostgreSQL General
pgsql-general@postgresql.org
Sent: Monday, November 28, 2005 2:18 PM
Subject: Re: [GENERAL] Group By?
So are you trying to get a list of all 'mon's and 'valve's for each
given association?
On Wed, Nov 23, 2005 at 04:31:04PM -0800, Bob Pawley wrote:
Guy
Sorry about the chart. It held together when I sent it to myself.
I'll try to make it clear in the way you suggest, by a truncated example.
Create table control (device_id serial, type varchar, association int4)
Create table auto_control (loop_id serial, monitor int4, valve int4)
Insert into control (type, association) Note - mon and valve are types of
device that together make a loop. A loop can be from 1 to 7 devices..
Values ('mon', '1') - serial 1
Values ('valve', '2') - serial 2
Values ('mon', '2') - serial 3
Values ('valve', '1') - serial 4
Values ('valve', '2') - serial 5
I want to transfer the serial device_id number for mon '1' into the same
row as valve '1' in the tables auto_control. Similarily
Those two rows would look like this.
Table (loop_id serial, monitor int4, valve int4)
Row 1 ( 1, 1, 4, )
Row 2 (2, 2, 3, 5)
Once this is done the devices will be organized into loops and each
device
in the loop will have a direct link to other parts of the database.
I would like to know if this is possible with SQL, or would it be more
suited to the host language?
Would it be possible in SQL to have the information transferred into the
auto_control table as the information is being entered or would the
control
table need to be fully completed?
Hope this is finally clear.
Thanks for you help.
Bob
- Original Message -
From: Guy Rouillier [EMAIL PROTECTED]
To: PostgreSQL General pgsql-general@postgresql.org
Sent: Wednesday, November 23, 2005 2:17 PM
Subject: Re: [GENERAL] Group By?
Bob Pawley wrote:
Bruno
The table I previously sent came through distorted and probabley
caused misunderstanding.
The table control and auto_control are both permanent table. I want to
reshuffle how the information is associated from one table to another
with the link between table by way of the device_id.
Following is the example which I stabilized and tested for e-mail.
Sorry, Bob, I think the reason you haven't gotten a response is that the
information you are trying to convey below is very unclear. Perhaps you
can just provide a create table statement and a bunch of insert
statements? Then summarize again what you are trying to accomplish.
Bob
Control
device_id
type
association
serial
varchar
int4
1
mon
1
2
valve
2
3
valve
1
4
mon
2
5
valve
1
Auto_control
loop_id
mon
valve_a
valve_b
serial
int4
int4
int4
1
1
3
5
2
2
4
- Original Message -
From: Bruno Wolff III [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Guy Rouillier [EMAIL PROTECTED]; Postgre General
pgsql-general@postgresql.org
Sent: Monday, November 21, 2005 10:07 PM
Subject: Re: Group By?
On Mon, Nov 21, 2005 at 21:53:10 -0800,
Bob Pawley [EMAIL PROTECTED] wrote:
Here's what I want to do.
Table control contains values (mon and valves) that are associated
by numbers inserted into the associated column.
I want to transfer the serial _id number of the items associated by
the value '1' into the appropriate columns of the first row of the
table auto_control. All items associated with the value '2' into the
second row - etc. etc.
You don't really want to do that. Tables have fixed numbers of
columns and what you want to do doesn't result in a fixed number of
columns.
If you want to generate a report with that format, then I think there
is a contrib module (crosstabs?) that will do this kind of thing. You
could also have a report app do it for you. In the report app method,
you would be best to return rows ordered by association and then
device_ID and have the app check for when the association value
changes.
Is this best accomplished by a 'group by' command or subset???
Bob
Control
device_ID type association
serial varchar int4
1 mon 1
2 valve 2
3 valve 1
4 mon 2
5 valve 1
Auto_control
loop_id mon valve valve
serial int4 int4 int4
1 1 3 5
2 2 4
- Original Message -
From: Guy Rouillier [EMAIL PROTECTED]
To: Postgre General pgsql-general@postgresql.org
Sent: Monday, November 21, 2005 4:25 PM
Subject: Re: [GENERAL] Group By?
Converted your message to plain text as preferred on most mailing
lists.
Bob Pawley wrote:
I want to take the serial ID of
Re: [GENERAL] Login limitation?
On Sun, Nov 27, 2005 at 12:32:06PM +0100, Egy?d Csaba wrote: Hi All, I'd like to know if there is a method to let a user login into only predefined databases? I know that I could edit pg_hba.conf to achive this, but - there will be many databases on the server with the same structure but with different data - there will be local admins who must be able to create new local users (of their own db) - it is a win32 client application... so editing pg_hba.conf is not too easy... So I'd need an administrative method (command?) which is capable to define (in the server level) a set of databases (0, 1 or more) for every user which she can login and prevent her from logging in to any other databases. This data should be modified via SQL statements like GRANT. I tried to REVOKE all priviges from a user on a db, but the user still able to login. Another question is that she can't do anything. Any ideas? I think you're basically stuck with pg_hba.conf. There's been some functions added to 8.1 that make it possible to do some more administrative stuff with config files via SQL, but I'm not sure if they'd cover this case. I can see where this could be a problem for people providing hosting; if enough other users request this functionality it might make it onto the TODO list. -- 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] Generic Q about max(id) vs ORDER BY ID DESC LIMIT 1
On Sun, Nov 27, 2005 at 11:38:57PM +1100, Brendan Jurd wrote: Already done in 8.1. Here's an excerpt from the Release Notes: Automatically use indexes for MIN() and MAX() (Tom) In previous releases, the only way to use an index for MIN() or MAX() was to rewrite the query as SELECT col FROM tab ORDER BY col LIMIT 1. Index usage now happens automatically. Which query form will generally be faster in 8.1 (or will they be exactly the same)? They'll effectively be the same: stats=# explain select id from stats_participant where id is not null order by id limit 1; QUERY PLAN - Limit (cost=0.00..3.40 rows=1 width=4) - Index Scan using stats_participant_pkey on stats_participant (cost=0.00..1486391.76 rows=436912 width=4) Filter: (id IS NOT NULL) (3 rows) stats=# explain select min(id) from stats_participant; QUERY PLAN - Result (cost=3.40..3.41 rows=1 width=0) InitPlan - Limit (cost=0.00..3.40 rows=1 width=4) - Index Scan using stats_participant_pkey on stats_participant (cost=0.00..1486391.76 rows=436912 width=4) Filter: (id IS NOT NULL) (5 rows) stats=# -- 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] problem with psql?
I've heard from others that OS X's remote desktop equivalent uses an internal PosgreSQL database in an embedded install that uses the default PostgreSQL port. That could be the cause of your trouble... On Mon, Nov 28, 2005 at 01:24:37PM -0600, Timothy Perrigo wrote: We just ran an OS update on an Xserve which runs our PostgreSQL server, and now it seems that pg_ctl status doesn't report the correct status. ~ postgres$ pg_ctl status pg_ctl: neither postmaster nor postgres running ~ postgres$ ps auxw | grep postgres postgres 491 0.0 -0.133156 1160 ?? S 1:09PM 0:00.16 /usr/local/pgsql/bin/postmaster -D /Volumes/DBRAID/pgsql/data postgres 629 0.0 -0.031844216 ?? S 1:09PM 0:00.27 postgres: logger process postgres 631 0.0 -0.033140744 ?? S 1:09PM 0:00.06 postgres: writer process postgres 632 0.0 -0.031860276 ?? S 1:09PM 0:00.02 postgres: archiver process postgres 633 0.0 -0.032864164 ?? S 1:09PM 0:00.02 postgres: stats buffer process postgres 634 0.0 -0.031888284 ?? S 1:09PM 0:00.03 postgres: stats collector process We are able to connect to the instance (using psql), but cannot shut down or restart the instance using pg_ctl (it just reports that the server is not running). We are running PostgreSQL version 8.0.0 and Mac OS X 10.3.9. Can anyone tell us what's going on? Thanks! Tim ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- 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] Group By?
Try this (untested):
INSERT INTO auto_control( monitor, valve )
SELECT m.device_id, v.device_id
FROM control m
JOIN control v ON (m.association = v.association)
;
On Mon, Nov 28, 2005 at 03:41:53PM -0800, Bob Pawley wrote:
Yes I am trying to insert all valves into the same row as their associated
mon.
Bob
- Original Message -
From: Jim C. Nasby [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Guy Rouillier [EMAIL PROTECTED]; PostgreSQL General
pgsql-general@postgresql.org
Sent: Monday, November 28, 2005 2:18 PM
Subject: Re: [GENERAL] Group By?
So are you trying to get a list of all 'mon's and 'valve's for each
given association?
On Wed, Nov 23, 2005 at 04:31:04PM -0800, Bob Pawley wrote:
Guy
Sorry about the chart. It held together when I sent it to myself.
I'll try to make it clear in the way you suggest, by a truncated example.
Create table control (device_id serial, type varchar, association int4)
Create table auto_control (loop_id serial, monitor int4, valve int4)
Insert into control (type, association) Note - mon and valve are types of
device that together make a loop. A loop can be from 1 to 7 devices..
Values ('mon', '1') - serial 1
Values ('valve', '2') - serial 2
Values ('mon', '2') - serial 3
Values ('valve', '1') - serial 4
Values ('valve', '2') - serial 5
I want to transfer the serial device_id number for mon '1' into the same
row as valve '1' in the tables auto_control. Similarily
Those two rows would look like this.
Table (loop_id serial, monitor int4, valve int4)
Row 1 ( 1, 1, 4, )
Row 2 (2, 2, 3, 5)
Once this is done the devices will be organized into loops and each
device
in the loop will have a direct link to other parts of the database.
I would like to know if this is possible with SQL, or would it be more
suited to the host language?
Would it be possible in SQL to have the information transferred into the
auto_control table as the information is being entered or would the
control
table need to be fully completed?
Hope this is finally clear.
Thanks for you help.
Bob
- Original Message -
From: Guy Rouillier [EMAIL PROTECTED]
To: PostgreSQL General pgsql-general@postgresql.org
Sent: Wednesday, November 23, 2005 2:17 PM
Subject: Re: [GENERAL] Group By?
Bob Pawley wrote:
Bruno
The table I previously sent came through distorted and probabley
caused misunderstanding.
The table control and auto_control are both permanent table. I want to
reshuffle how the information is associated from one table to another
with the link between table by way of the device_id.
Following is the example which I stabilized and tested for e-mail.
Sorry, Bob, I think the reason you haven't gotten a response is that the
information you are trying to convey below is very unclear. Perhaps you
can just provide a create table statement and a bunch of insert
statements? Then summarize again what you are trying to accomplish.
Bob
Control
device_id
type
association
serial
varchar
int4
1
mon
1
2
valve
2
3
valve
1
4
mon
2
5
valve
1
Auto_control
loop_id
mon
valve_a
valve_b
serial
int4
int4
int4
1
1
3
5
2
2
4
- Original Message -
From: Bruno Wolff III [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Guy Rouillier [EMAIL PROTECTED]; Postgre General
pgsql-general@postgresql.org
Sent: Monday, November 21, 2005 10:07 PM
Subject: Re: Group By?
On Mon, Nov 21, 2005 at 21:53:10 -0800,
Bob Pawley [EMAIL PROTECTED] wrote:
Here's what I want to do.
Table control contains values (mon and valves) that are associated
by numbers inserted into the associated column.
I want to transfer the serial _id number of the items associated by
the value '1' into the appropriate columns of the first row of the
table auto_control. All items associated with the value '2' into the
second row - etc. etc.
You don't really want to do that. Tables have fixed numbers of
columns and what you want to do doesn't result in a fixed number of
columns.
If you want to generate a report with that format, then I think there
is a contrib module (crosstabs?) that will do this kind of thing. You
could also have a report app do it for you. In the report app method,
you would be best to return rows ordered by association and then
device_ID and have the app check for when the association value
changes.
Is this best accomplished by a 'group by' command or subset???
Bob
Control
device_ID type association
serial
Re: [GENERAL] problem with psql?
[Reordering top-posted reply] On Mon, Nov 28, 2005 at 01:24:37PM -0600, Timothy Perrigo wrote: We just ran an OS update on an Xserve which runs our PostgreSQL server, and now it seems that pg_ctl status doesn't report the correct status. snip / We are able to connect to the instance (using psql), but cannot shut down or restart the instance using pg_ctl (it just reports that the server is not running). We are running PostgreSQL version 8.0.0 and Mac OS X 10.3.9. Can anyone tell us what's going on? On Nov 29, 2005, at 8:56 , Jim C. Nasby wrote: I've heard from others that OS X's remote desktop equivalent uses an internal PosgreSQL database in an embedded install that uses the default PostgreSQL port. That could be the cause of your trouble... Conflicts with the ARD-installed server has been an issue for me on and off in the past, though I haven't been able to connect to the server using psql without specifying the ard database, user, and password. Also, I believe only the ARD server has the embedded PostgreSQL server (and an old one at that). Do you actually have the ARD server software installed on the Xserve? (My guess is you may be using the client software that is installed with the OS.) Also, could you check who owns those processes? The ARD-installed server will probably be owned by someone (such as daemon, though I don't recall off the top of my head) other than your normal postgres server. If you find it *is* a problem with ARD (which I am inclined to doubt), you can alter the port used by the ARD-installed PostgreSQL server by changing the port it uses in /var/db/RemoteManagement/RMDB/rmdb.data/postgresql.conf Hope this helps. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Group By?
Thank you I'll give it a try.
Bob
- Original Message -
From: Jim C. Nasby [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Guy Rouillier [EMAIL PROTECTED]; PostgreSQL General
pgsql-general@postgresql.org
Sent: Monday, November 28, 2005 4:13 PM
Subject: Re: [GENERAL] Group By?
Try this (untested):
INSERT INTO auto_control( monitor, valve )
SELECT m.device_id, v.device_id
FROM control m
JOIN control v ON (m.association = v.association)
;
On Mon, Nov 28, 2005 at 03:41:53PM -0800, Bob Pawley wrote:
Yes I am trying to insert all valves into the same row as their
associated
mon.
Bob
- Original Message -
From: Jim C. Nasby [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Guy Rouillier [EMAIL PROTECTED]; PostgreSQL General
pgsql-general@postgresql.org
Sent: Monday, November 28, 2005 2:18 PM
Subject: Re: [GENERAL] Group By?
So are you trying to get a list of all 'mon's and 'valve's for each
given association?
On Wed, Nov 23, 2005 at 04:31:04PM -0800, Bob Pawley wrote:
Guy
Sorry about the chart. It held together when I sent it to myself.
I'll try to make it clear in the way you suggest, by a truncated
example.
Create table control (device_id serial, type varchar, association int4)
Create table auto_control (loop_id serial, monitor int4, valve int4)
Insert into control (type, association) Note - mon and valve are types
of
device that together make a loop. A loop can be from 1 to 7 devices..
Values ('mon', '1') - serial 1
Values ('valve', '2') - serial 2
Values ('mon', '2') - serial 3
Values ('valve', '1') - serial 4
Values ('valve', '2') - serial 5
I want to transfer the serial device_id number for mon '1' into the
same
row as valve '1' in the tables auto_control. Similarily
Those two rows would look like this.
Table (loop_id serial, monitor int4, valve int4)
Row 1 ( 1, 1, 4, )
Row 2 (2, 2, 3, 5)
Once this is done the devices will be organized into loops and each
device
in the loop will have a direct link to other parts of the database.
I would like to know if this is possible with SQL, or would it be more
suited to the host language?
Would it be possible in SQL to have the information transferred into
the
auto_control table as the information is being entered or would the
control
table need to be fully completed?
Hope this is finally clear.
Thanks for you help.
Bob
- Original Message -
From: Guy Rouillier [EMAIL PROTECTED]
To: PostgreSQL General pgsql-general@postgresql.org
Sent: Wednesday, November 23, 2005 2:17 PM
Subject: Re: [GENERAL] Group By?
Bob Pawley wrote:
Bruno
The table I previously sent came through distorted and probabley
caused misunderstanding.
The table control and auto_control are both permanent table. I want to
reshuffle how the information is associated from one table to another
with the link between table by way of the device_id.
Following is the example which I stabilized and tested for e-mail.
Sorry, Bob, I think the reason you haven't gotten a response is that
the
information you are trying to convey below is very unclear. Perhaps
you
can just provide a create table statement and a bunch of insert
statements? Then summarize again what you are trying to accomplish.
Bob
Control
device_id
type
association
serial
varchar
int4
1
mon
1
2
valve
2
3
valve
1
4
mon
2
5
valve
1
Auto_control
loop_id
mon
valve_a
valve_b
serial
int4
int4
int4
1
1
3
5
2
2
4
- Original Message -
From: Bruno Wolff III [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
Cc: Guy Rouillier [EMAIL PROTECTED]; Postgre General
pgsql-general@postgresql.org
Sent: Monday, November 21, 2005 10:07 PM
Subject: Re: Group By?
On Mon, Nov 21, 2005 at 21:53:10 -0800,
Bob Pawley [EMAIL PROTECTED] wrote:
Here's what I want to do.
Table control contains values (mon and valves) that are associated
by numbers inserted into the associated column.
I want to transfer the serial _id number of the items associated by
the value '1' into the appropriate columns of the first row of the
table auto_control. All items associated with the value '2' into the
second row - etc. etc.
You don't really want to do that. Tables have fixed numbers of
columns and what you want to do doesn't result in a fixed number of
columns.
If you want to generate a report with that format, then I think there
is a contrib module (crosstabs?) that will do this kind of thing. You
could also have a report app do it for you. In the report app method,
you would be best to return rows ordered by association and then
device_ID and have the app check for when the association value
changes.
Is this best accomplished by a 'group by' command or subset???
Bob
Control
[GENERAL] Errors upgrading from 7.3 to 8.1
Currently running Postgres 7.3.9 32bit on Fedora Core 1, and upgrading to PG 8.1 64bit on Centos 4. When I load the file, psql -U dbname dbname.sql I get this error: ERROR: invalid UTF-8 byte sequence detected near byte 0x96 when inserting fields that seem to contain HTML. What could be causing this? My understanding is that pg_dump should properly escape things so that I'm not trying to dump/load things improperly. The dumps are made (on the PG 7.3 server) pg_dump -d -f $OUTPUT.pgsql $db Are being restore with (on the new 8.1 server) psql -U $db -e $OUTPUT.pgsql -Ben -- The best way to predict the future is to invent it. - XEROX PARC slogan, circa 1978 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Errors upgrading from 7.3 to 8.1
you should use the 8.1 pg_dump not the 7.3 one. Give it a try -- Original Message --- From: Benjamin Smith [EMAIL PROTECTED] To: Postgres General pgsql-general@postgresql.org Sent: Mon, 28 Nov 2005 16:22:18 -0800 Subject: [GENERAL] Errors upgrading from 7.3 to 8.1 Currently running Postgres 7.3.9 32bit on Fedora Core 1, and upgrading to PG 8.1 64bit on Centos 4. When I load the file, psql -U dbname dbname.sql I get this error: ERROR: invalid UTF-8 byte sequence detected near byte 0x96 when inserting fields that seem to contain HTML. What could be causing this? My understanding is that pg_dump should properly escape things so that I'm not trying to dump/load things improperly. The dumps are made (on the PG 7.3 server) pg_dump -d -f $OUTPUT.pgsql $db Are being restore with (on the new 8.1 server) psql -U $db -e $OUTPUT.pgsql -Ben -- The best way to predict the future is to invent it. - XEROX PARC slogan, circa 1978 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings --- End of Original Message --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Errors upgrading from 7.3 to 8.1
FYI in case you still have the problem after using pg_dump from 8.1 (we did) you may have an encoding mismatch between your 7.3 and 8.1 databases. Our 7.x database was SQL_ASCII while 8.1 defaults to creating databases as UTF-8. I had similar invalid UTF-8 byte sequence errors. I fixed it by using: createdb --encoding=SQL_ASCII [your db here] When creating the database in 8.1. We were then able to load without a problem. Not sure you're in the exact same situation, but hope it helps. Stuart -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jim Buttafuoco Sent: Monday, November 28, 2005 4:27 PM To: [EMAIL PROTECTED]; Postgres General Subject: Re: [GENERAL] Errors upgrading from 7.3 to 8.1 you should use the 8.1 pg_dump not the 7.3 one. Give it a try -- Original Message --- From: Benjamin Smith [EMAIL PROTECTED] To: Postgres General pgsql-general@postgresql.org Sent: Mon, 28 Nov 2005 16:22:18 -0800 Subject: [GENERAL] Errors upgrading from 7.3 to 8.1 Currently running Postgres 7.3.9 32bit on Fedora Core 1, and upgrading to PG 8.1 64bit on Centos 4. When I load the file, psql -U dbname dbname.sql I get this error: ERROR: invalid UTF-8 byte sequence detected near byte 0x96 when inserting fields that seem to contain HTML. What could be causing this? My understanding is that pg_dump should properly escape things so that I'm not trying to dump/load things improperly. The dumps are made (on the PG 7.3 server) pg_dump -d -f $OUTPUT.pgsql $db Are being restore with (on the new 8.1 server) psql -U $db -e $OUTPUT.pgsql -Ben -- The best way to predict the future is to invent it. - XEROX PARC slogan, circa 1978 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings --- End of Original Message --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq If you have received this e-mail in error or wish to read our e-mail disclaimer statement and monitoring policy, please refer to http://www.drkw.com/disc/email/ or contact the sender. ---(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] Getting cannot remove lock from HTAB during VACUUM FULL
Title: Getting cannot remove lock from HTAB during VACUUM FULL All, During our weekly VACUUM FULL we get a ERROR: cannot remove lock from HTAB message and then the database appears to hang completely. Other processes can connect to the database, but all queries hang. We must stop the database and restart it to recover. The two times this has occurred, the hang did not happen in the same place during the VACUUM. This behavior has only started recently; the database has been running smoothly for over a year prior to this. We're using Postgresql 7.4.5 on an Intel box running Red Hat AS 2.1. Has anyone seen this before or can anyone shed some light into what might be causing this? Thanks, Stuart If you have received this e-mail in error or wish to read our e-mail disclaimer statement and monitoring policy, please refer to http://www.drkw.com/disc/email/ or contact the sender.
Re: [GENERAL] Installation trouble
Steve Crawford wrote: On Monday 31 October 2005 13:00, Tom Lane wrote: Steve Crawford [EMAIL PROTECTED] writes: if I try to ensure the C locale I keep running up against: FATAL: XX000: failed to initialize lc_messages to We've seen a few reports of this before, but never been able to identify the cause. What platform are you running on, exactly? Did you build your own PG executables; if not, where did you get them from? Vanilla built from source (./configure ; make ; make install). Platform: SuSE Linux 8.2 (i586) uname -a: Linux web2 2.4.20-4GB #1 Mon Mar 17 17:54:44 UTC 2003 i686 unknown unknown GNU/Linux Selected environment variables: CPU=i686 HOME=/var/lib/pgsql HOSTTYPE=i386 LANG=en_US LC_COLLATE=POSIX LOGNAME=postgres MACHTYPE=i686-suse-linux OSTYPE=linux PATH=/usr/local/bin:/usr/bin:/usr/X11R6/bin:/bin:/usr/games I did not install PostgreSQL when the machine was originally built _but_ certain client libraries are installed by YaST to satisfy dependencies. I do not believe any server-related libraries were installed. I also tried substituting the new libraries for the old as well as removing them from /usr/lib entirely without effect. For completeness and guc.c line numbers, here's the whole output: [EMAIL PROTECTED]:~ initdb --no-locale /var/lib/pgsql/data The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory /var/lib/pgsql/data ... ok creating directory /var/lib/pgsql/data/global ... ok creating directory /var/lib/pgsql/data/pg_xlog ... ok creating directory /var/lib/pgsql/data/pg_xlog/archive_status ... ok creating directory /var/lib/pgsql/data/pg_clog ... ok creating directory /var/lib/pgsql/data/pg_subtrans ... ok creating directory /var/lib/pgsql/data/base ... ok creating directory /var/lib/pgsql/data/base/1 ... ok creating directory /var/lib/pgsql/data/pg_tblspc ... ok selecting default max_connections ... 10 selecting default shared_buffers ... 50 creating configuration files ... ok creating template1 database in /var/lib/pgsql/data/base/1 ... FATAL: XX000: failed to initialize lc_messages to LOCATION: InitializeGUCOptions, guc.c:2389 child process exited with exit code 1 initdb: removing contents of data directory /var/lib/pgsql/data Cheers, Steve Progress Report: Started over. A drive failure prevented the machine from rebooting so I started from scratch with a new drive and a fresh install of SuSE 8.2 (Linux web2 2.4.20-4GB #1 Mon Mar 17 17:54:44 UTC 2003 i686 unknown unknown GNU/Linux). This time I PostgreSQL 8.1.0 (previously 8.0.3) compiled from source with vanilla ./configure ; make ; make install. Symlinked the binaries to /usr/bin. Same result (whether I specify the full real path to initdb or not). Additional searching still yields lots of messages with the question but none with the answer. Cheers, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Default and PQexecParams
[EMAIL PROTECTED] wrote: Hi, How could one differentiate between DEFAULT and 'DEFAULT' as parameters to PQexecuteParams? I assume you mean the libpq function PQexecParams(), and you want to use a parameterized query for INSERT or UPDATE. I don't think it is possible to use DEFAULT because it is a keyword, and only a value expression will work as a parameter. With NULL vs 'NULL', PQexecParams() has a special case to handle NULL (null pointer in the paramValues array). ---(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 cannot remove lock from HTAB during VACUUM FULL
Stock, Stuart [EMAIL PROTECTED] writes: During our weekly VACUUM FULL we get a ERROR: cannot remove lock from HTAB message and then the database appears to hang completely. Try updating --- I see a 7.4.8 fix that might be relevant. 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] Installation trouble
Steve Crawford [EMAIL PROTECTED] writes: creating template1 database in /var/lib/pgsql/data/base/1 ... FATAL: XX000: failed to initialize lc_messages to We've seen this reported occasionally before, but none of the PG developers have ever been able to reproduce it. Do you have any LC_xxx environment variables besides what you showed? Could you try running initdb under strace -f -o logfile and send me the output (off-list, it'll likely be big)? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] sub select as a data column
you were close, try the following (untesting). You need the alias on the outer my_table for this to work select year report_year, sum(amount), sum(select amount from my_table where year = a.year) from my_table a group by report_year; -- Original Message --- From: Phong Ronni Bounmixay [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Mon, 28 Nov 2005 13:18:41 -0700 Subject: [GENERAL] sub select as a data column I am having trouble coming up with words to describe exactly what I need so searching for a solution is getting frustrating! If this has been answered twelve hundred times - please forgive me! I want to do in postgresql what I do in oracle: select year report_year, sum(amount), sum(select amount from my_table where year = report_year) from my_table group by report_year; I see lots of stuff on subselect in the from clause - but how do you subselect as a data column? Thanks for the help - it is SEVERLY appreciated! Ronni --- End of Original Message --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Errors upgrading from 7.3 to 8.1
Title: AW: [GENERAL] Errors upgrading from 7.3 to 8.1 Hello! We experienced the exact same problems. You may solve the problem by feeding the dump through iconv. See my earlier message on this issue http://archives.postgresql.org/pgsql-general/2005-11/msg00799.php On top of that you'd be well advised to try dumping using pg_dump of postgresql 8.1. Kind regards Markus -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] im Auftrag von Benjamin Smith Gesendet: Di 11/29/2005 01:22 An: Postgres General Betreff: [GENERAL] Errors upgrading from 7.3 to 8.1 Currently running Postgres 7.3.9 32bit on Fedora Core 1, and upgrading to PG 8.1 64bit on Centos 4. When I load the file, psql -U dbname dbname.sql I get this error: ERROR: invalid UTF-8 byte sequence detected near byte 0x96 when inserting fields that seem to contain HTML. What could be causing this? My understanding is that pg_dump should properly escape things so that I'm not trying to dump/load things improperly. The dumps are made (on the PG 7.3 server) pg_dump -d -f $OUTPUT.pgsql $db Are being restore with (on the new 8.1 server) psql -U $db -e $OUTPUT.pgsql -Ben -- The best way to predict the future is to invent it. - XEROX PARC slogan, circa 1978 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
