Re: [GENERAL] manipulating HeapTuples in a libpq client
On Fri, Nov 23, 2007 at 04:51:53PM -0800, Eric Davies wrote: I've got a server function that returns a set of HeapTuples. The server function is invoked in a query sent by a libpq client.However, I haven't spotted a way to decompose the HeapTuple returned to the client. Attempts to call GetAttributeByNum on the client result in the linker complaining about an undefined reference. I'm not entirely sure if this is even supposed to work. Certainly prior to the transmission to the client the tuple will be converted to text, so you'll have to parse the result. The question for me is why are you returning heaptuples and not just a resultset which you can access via the normal functions. There are no client side functions to deal with heaptuples. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
[GENERAL] Disk arrangement in a cheap server
I will build a cheap server and I'm in doubt about what would the the best for performance: 1 - everything in one lonely fast 10,000 rpm Raptor HD; 2 - two cheap 7,200 rpm 16MB cache HDs like this: disk 1 - system and pg_xlog disk 2 - pg_data without pg_xlog or a better arrange suggested by you; 3 - The two cheap HDs above in Raid 0. There will be 4 GB of memory, Fedora 8 and Postgresql 8.3. Regards, Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Disk arrangement in a cheap server
Clodoaldo asked: I will build a cheap server and I'm in doubt about what would the the best for performance: 1 - everything in one lonely fast 10,000 rpm Raptor HD; 2 - two cheap 7,200 rpm 16MB cache HDs like this: disk 1 - system and pg_xlog disk 2 - pg_data without pg_xlog or a better arrange suggested by you; 3 - The two cheap HDs above in Raid 0. There will be 4 GB of memory, Fedora 8 and Postgresql 8.3. You haven't told us much (enough?). What sort of work load ? (OLTP ? Data Warehouse ? Massive loads and the lots of read-only transactions ?) How valuable is your data ? If it is worth much at all lean toward an option that maximizes safety (and plan on a good backup strategy, tested and all that). In general more spindles are faster, but it depends on the actual work load. Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Re: [GENERAL] Disk arrangement in a cheap server
On Nov 24, 2007 5:09 AM, Clodoaldo [EMAIL PROTECTED] wrote: I will build a cheap server and I'm in doubt about what would the the best for performance: 1 - everything in one lonely fast 10,000 rpm Raptor HD; 2 - two cheap 7,200 rpm 16MB cache HDs like this: disk 1 - system and pg_xlog disk 2 - pg_data without pg_xlog or a better arrange suggested by you; 3 - The two cheap HDs above in Raid 0. From a DBA perspective, none of those seem like a good choice, as there's no redundancy. I'd make the two 7200 RPM drives a RAID-1 and have some redundancy so a single disk failure wouldn't lose all my data. then I'd start buying more drives and a good RAID controller if I needed more performance. ---(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] Disk arrangement in a cheap server
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/24/07 09:12, Scott Marlowe wrote: On Nov 24, 2007 5:09 AM, Clodoaldo [EMAIL PROTECTED] wrote: I will build a cheap server and I'm in doubt about what would the the best for performance: 1 - everything in one lonely fast 10,000 rpm Raptor HD; 2 - two cheap 7,200 rpm 16MB cache HDs like this: disk 1 - system and pg_xlog disk 2 - pg_data without pg_xlog or a better arrange suggested by you; 3 - The two cheap HDs above in Raid 0. From a DBA perspective, none of those seem like a good choice, as there's no redundancy. I'd make the two 7200 RPM drives a RAID-1 and have some redundancy so a single disk failure wouldn't lose all my data. then I'd start buying more drives and a good RAID controller if I needed more performance. Remember: disks are *cheap*. Spend an extra US$250 and buy a couple of 500GB drives for RAID 1. You don't mention what OS you'll use, but if you really need cheap then XP Linux do sw RAID, and FreeBSD probably does too. - -- Ron Johnson, Jr. Jefferson LA USA %SYSTEM-F-FISH, my hovercraft is full of eels -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHSE6CS9HxQb37XmcRAhRTAKC4gFKymM0f46jKXpUX2NsUog4dOwCg00WP cDE5xB8Qm+3MDtri40HFrRs= =Vnb7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Disk arrangement in a cheap server
On Nov 24, 2007, at 8:17 AM, Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/24/07 09:12, Scott Marlowe wrote: On Nov 24, 2007 5:09 AM, Clodoaldo [EMAIL PROTECTED] wrote: I will build a cheap server and I'm in doubt about what would the the best for performance: 1 - everything in one lonely fast 10,000 rpm Raptor HD; 2 - two cheap 7,200 rpm 16MB cache HDs like this: disk 1 - system and pg_xlog This doesn't really buy you much. The supposed advantage of having pg_xlog on its own drive is so that the head doesn't need to seek. If it's on the system drive it'll be competing with, at least, syslog. disk 2 - pg_data without pg_xlog or a better arrange suggested by you; 3 - The two cheap HDs above in Raid 0. From a DBA perspective, none of those seem like a good choice, as there's no redundancy. I'd make the two 7200 RPM drives a RAID-1 and have some redundancy so a single disk failure wouldn't lose all my data. then I'd start buying more drives and a good RAID controller if I needed more performance. It depends on what the box is used for, but for most cases where the data is valuable, that sounds like a much better idea. For batch data crunching, where the data is loaded from elsewhere then processed and reported on, the cost of losing the data is very low, and the value of the machine is increased by RAID0-ing the drives to make the crunching faster... RAID0 could be good. That's probably not the case here. Remember: disks are *cheap*. Spend an extra US$250 and buy a couple of 500GB drives for RAID 1. You don't mention what OS you'll use, but if you really need cheap then XP Linux do sw RAID, and FreeBSD probably does too. Disks aren't necessarily cheap. Disks are fairly expensive, especially when you need more spindles than will fit into the servers chassis and you need to move to external storage. Disk n+1 is very expensive, likely more expensive than the cheap 1U server you started with. Two, though, does seem to be false economy for a server that'll be running a database, when you can get a 1U chassis that'll take 4 drives pretty cheaply. Cheers, Steve ---(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] cursors
Hello every one. im trying to make a Loop and i found in the manual this. FOR target IN query LOOP statements END LOOP Can i use cursor instead of the Query in the loop?? , this es more legible than using the open/fetch/close of the cursor. Regard Cesar Alvarez. begin:vcard fn:Cesar Alvarez n:;Cesar Alvarez title:Web Development Asesor and Software Enginner version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] cursors
On 24/11/2007, Cesar Alvarez [EMAIL PROTECTED] wrote: Hello every one. im trying to make a Loop and i found in the manual this. FOR target IN query LOOP statements END LOOP Can i use cursor instead of the Query in the loop?? , this es more legible than using the open/fetch/close of the cursor. Regard Cesar Alvarez. Hello FOR statement use cursor internally. If you wont to use cursor explicitly, you have to use WHILE loop. Regards Pavel Stehule ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] cursors
Thanks, what will be the syntax for that type of for? Pavel Stehule wrote: On 24/11/2007, Cesar Alvarez [EMAIL PROTECTED] wrote: Hello every one. im trying to make a Loop and i found in the manual this. FOR target IN query LOOP statements END LOOP Can i use cursor instead of the Query in the loop?? , this es more legible than using the open/fetch/close of the cursor. Regard Cesar Alvarez. Hello FOR statement use cursor internally. If you wont to use cursor explicitly, you have to use WHILE loop. Regards Pavel Stehule ---(end of broadcast)--- TIP 6: explain analyze is your friend begin:vcard fn:Cesar Alvarez n:;Cesar Alvarez title:Web Development Asesor and Software Enginner version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] cursors
On 24/11/2007, Cesar Alvarez [EMAIL PROTECTED] wrote: Thanks, what will be the syntax for that type of for? DECLARE curs2 CURSOR FOR SELECT * FROM tenk1; c1 integer; c2 integer; BEGIN OPEN curs2; FETCH curs2 INTO c1,c2; WHILE found LOOP ... FETCH curs2 INTO c1,c2; END LOOP; CLOSE curs2; http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS Regards Pavel Stehule Pavel Stehule wrote: On 24/11/2007, Cesar Alvarez [EMAIL PROTECTED] wrote: Hello every one. im trying to make a Loop and i found in the manual this. FOR target IN query LOOP statements END LOOP Can i use cursor instead of the Query in the loop?? , this es more legible than using the open/fetch/close of the cursor. Regard Cesar Alvarez. Hello FOR statement use cursor internally. If you wont to use cursor explicitly, you have to use WHILE loop. Regards Pavel Stehule ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] Migrating from 32 to 64 bit
Hi, I'm currently setting up 2 new servers to act as a PgSQL cluster (8.2). This basically consists in the reinstallation of the OS (64Bit). Question: I'd like to know if it is possible (and wise) to just keep the /var/lib/postgres.. directories from the old 32Bit server to use on the 64Bit version. This is just as a personal interest since I can also just dump and restore the database in about 2.5 hrs. Thanks Laurent ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Migrating from 32 to 64 bit
Douglas McNaught wrote: Nope, not possible--different architectures result in different data layouts. Dump restore is the way to go. Thanks, I'll then dump/restore. Laurent ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Problems with PostGreSQL and Windows 2003
Hello, I'm having serious problems with PostGreSQL and Windows Server 2003 Enterprise Edition. The PostgreSQL Server doesn't start if I set the shared buffers higher than 1GB. All my programs can use only 3 GB of RAM and I have 8GB of RAM. When I monitor the processes I can see that PostGreSQL allocs only 700 MB of memory, and my application 2GB. Total: 3GB. When I try to execute a query in a table about 4 milion registers, my application crashes with an error message Out of memory or invalid sql statement. But the sql statement is ok - if I execute it in a table with less registers, it works and it is very simple. My program was made in Delphi 2006, and I use ADO via ODBC to connect to PostGreSQL. The configuration: PostGreSQL 8.2.5 O.S: Windows Server 2003 Enterprise Edition Service Pack 2 Computer: dual quad core Intel(R) Xeon(R) CPU E5345 @ 2.33GHz 8GB of RAM Physical Address Extension 3 HDs in RAID-5 My boot.ini: [boot loader] timeout=30 default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS [operating systems] multi(0)disk(0)rdisk(0)partition(2)\WINDOWS=Windows Server 2003, Enterprise /fastdetect /PAE /NoExecute=OptOut /3GB PostGreSQL.conf: shared_buffers = 1024MB# min 128kB or max_connections*16kB # (change requires restart) temp_buffers = 32MB# min 800kB #max_prepared_transactions = 5# can be 0 or more # (change requires restart) # Note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem =16MB# min 64kB maintenance_work_mem = 256MB# min 1MB max_stack_depth = 2MB# min 100kB # - Free Space Map - max_fsm_pages = 409600# min max_fsm_relations*16, 6 bytes each # (change requires restart) #max_fsm_relations = 1000# min 100, ~70 bytes each # (change requires restart) #--- # WRITE AHEAD LOG #--- # - Checkpoints - checkpoint_segments = 128# in logfile segments, min 1, 16MB each checkpoint_timeout = 15min# range 30s-1h checkpoint_warning = 30s# 0 is off #--- # QUERY TUNING #--- effective_cache_size = 5120MB The structure of my table: CREATE TABLE public.fato_financeiro ( CODCLI VARCHAR(6), PREST VARCHAR(4) NOT NULL, NUMTRANSVENDA VARCHAR(10) NOT NULL, RECNUM VARCHAR(8) NOT NULL, CODFORNEC VARCHAR(8), TIPO VARCHAR(2), NUMDOC VARCHAR(10), PREST_1 VARCHAR(4), VALOR DOUBLE PRECISION, DTEMISSAO TIMESTAMP WITH TIME ZONE, DTVENC TIMESTAMP WITH TIME ZONE, DTPAG TIMESTAMP WITH TIME ZONE, VPAGO DOUBLE PRECISION, PAGO_PAG VARCHAR(9), ATRASADO VARCHAR(3), CONSTRAINT fato_financeiro_idx PRIMARY KEY(PREST, NUMTRANSVENDA, RECNUM) ) WITHOUT OIDS; SQL statement: select fato_financeiro.TIPO, fato_financeiro.NUMDOC, fato_financeiro.PREST, fato_financeiro.NUMDOC, fato_financeiro.DTVENC, fato_financeiro.DTPAG, fato_financeiro.PAGO_PAG, fato_financeiro.ATRASADO, fato_financeiro.CODCLI, fato_financeiro.CODFORNEC, fato_financeiro.DTEMISSAO from fato_financeiro Thanks, Cláudia Amorim. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Disk arrangement in a cheap server
Why the hell would you buy a 1U chassis in the first place when perfectly good cheap 4U chassis exists that will take 8 or more drives? 1U motherboards are a pain, 1U power supplies are a pain and 1U space for drives sucks. Most tests I've seen these days show that there is very little actual benefit from seperating pg_xlog and tablespace if you have a half decent controller card. Infact you are better off putting it all on one nice RAID 10 to get the good read performance that splitting it up will loose. if you don't have a decent controller card, RAID 0 will suck too. Namely onboard SATA RAID often sucks. Alex On Nov 24, 2007 12:06 PM, Steve Atkins [EMAIL PROTECTED] wrote: On Nov 24, 2007, at 8:17 AM, Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/24/07 09:12, Scott Marlowe wrote: On Nov 24, 2007 5:09 AM, Clodoaldo [EMAIL PROTECTED] wrote: I will build a cheap server and I'm in doubt about what would the the best for performance: 1 - everything in one lonely fast 10,000 rpm Raptor HD; 2 - two cheap 7,200 rpm 16MB cache HDs like this: disk 1 - system and pg_xlog This doesn't really buy you much. The supposed advantage of having pg_xlog on its own drive is so that the head doesn't need to seek. If it's on the system drive it'll be competing with, at least, syslog. disk 2 - pg_data without pg_xlog or a better arrange suggested by you; 3 - The two cheap HDs above in Raid 0. From a DBA perspective, none of those seem like a good choice, as there's no redundancy. I'd make the two 7200 RPM drives a RAID-1 and have some redundancy so a single disk failure wouldn't lose all my data. then I'd start buying more drives and a good RAID controller if I needed more performance. It depends on what the box is used for, but for most cases where the data is valuable, that sounds like a much better idea. For batch data crunching, where the data is loaded from elsewhere then processed and reported on, the cost of losing the data is very low, and the value of the machine is increased by RAID0-ing the drives to make the crunching faster... RAID0 could be good. That's probably not the case here. Remember: disks are *cheap*. Spend an extra US$250 and buy a couple of 500GB drives for RAID 1. You don't mention what OS you'll use, but if you really need cheap then XP Linux do sw RAID, and FreeBSD probably does too. Disks aren't necessarily cheap. Disks are fairly expensive, especially when you need more spindles than will fit into the servers chassis and you need to move to external storage. Disk n+1 is very expensive, likely more expensive than the cheap 1U server you started with. Two, though, does seem to be false economy for a server that'll be running a database, when you can get a 1U chassis that'll take 4 drives pretty cheaply. Cheers, Steve ---(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
