[SQL] Question about undefinably query...
Hi all, there was a question on #postgresql (irc-channel) from CyberDuck without an answer. A little example: test=*# select generate_series(1,5), generate_series(6,10) order by 1; generate_series | generate_series -+- 1 | 6 2 | 7 3 | 8 4 | 9 5 | 10 (5 rows) test=*# select generate_series(1,5), generate_series(7,10) order by 1; generate_series | generate_series -+- 1 | 7 1 | 8 1 | 9 1 | 10 2 | 8 2 | 9 2 | 10 2 | 7 3 | 9 3 | 10 3 | 7 3 | 8 4 | 10 4 | 7 4 | 8 4 | 9 5 | 7 5 | 8 5 | 9 5 | 10 (20 rows) Why returns the first select 5 rows (i think, this is okay) and the 2nd 20 rows? Version is a 8.1 on Debian. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] EXECUTE in a funtion to return a VIEW object ID
Hi there, I have tried many ideas to get this working but no luck. Can some show me or explain what is happening EXAMPLE == I am trying to read to 2 text fields to combine them togther to form the name of a VIEW. example SELECT * FROM ( 'april'||'may') ; I have tried the EXECUTE in a function to PREPARE a dynameic select call; EXECUTE 'SELECT * FROM ' || 'select tablename.text_field1 from tablename' || 'select tablename.text_field2 from tablename'; If tablename.text_field1 = "May" and tablename.text_field2 = "Aprl", this function only returns "MayApril" and not the columns and data that I expected. What am I doing wrong here? Has anyone done this before or have any ideas on how to do it ? Thanks
Re: [SQL] Question about undefinably query...
"A. Kretschmer" <[EMAIL PROTECTED]> writes: > there was a question on #postgresql (irc-channel) from CyberDuck without > an answer. A little example: Multiple SRFs in a targetlist behave, um, strangely. I think the rule is that all are cycled until they all chance to report "done" at the same time, so the number of result rows is the least common multiple of the period lengths. This is one of the reasons that SRF-in-targetlist is deprecated ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] A long-running transaction
Andrew Sullivan wrote: On Tue, Apr 03, 2007 at 10:16:13PM +0800, John Summerfield wrote: It is hitting the disk pretty hard now on this machine, but the laptop's still going too, and the disk seems to run about half the time, part of a second running, part idle (but the intervals are getting shorter). It struck me as fairly curious that neither postgresql nor the application was hogging the CPU. Why? Nothing about this seems likely CPU bound. It's probably I/O. I note is number: For the first day or so, the disk light was switching off about half the time. IOwait: 2d 0:46:37.33 28.5% page dea: 16218135 which is pretty awful. Also It was some days in before it became so. Eventually, the machines (I kept it running on the laptop because of the amount of RAM plus another) did begin to thrash, but that certainly wasn't the case at first. Note that the proportion of system time is fairly unusual: user : 1d 3:17:04.03 16.0% page in : 131097310 disk 1: 3079516r20087664w nice : 0:05:39.64 0.1% page out: 197016649 system: 2d 20:38:37.13 40.1% page act: 87906251 The Linux kernel's clearly doing a lot of work, and the disk supports DMA and DMA is turned on, so it's not using A PIO mode. According to hdparm, it's using udma5. For each record, I update a non-key field in another table; the source data for that other table is less than a megabyte. this is a real issue. Basically, you're constrained at the rotation speed of your disk, because for each record, you have to first find then update one row somewhere else. It should be in cache: it's all one transaction, and on the laptop, there's over a Gbyte of RAM. Indeed, I would expect postgresql itself to cache it (except the fact it uses so little RAM suggests it doesn't do that at all). A ---(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: [SQL] A long-running transaction
On Wed, Apr 11, 2007 at 12:41:23AM +0800, John Summerfield wrote: > The Linux kernel's clearly doing a lot of work, and the disk supports You might also be into context-switch hell. What processor, which kernel, and which Postgres version again? > >>For each record, I update a non-key field in another table; the source > >>data for that other table is less than a megabyte. > > > >this is a real issue. Basically, you're constrained at the rotation > >speed of your disk, because for each record, you have to first find > >then update one row somewhere else. > > It should be in cache: it's all one transaction, and on the laptop, It's not in cache if you're updating -- you have to write it. > there's over a Gbyte of RAM. Indeed, I would expect postgresql itself to > cache it (except the fact it uses so little RAM suggests it doesn't do > that at all). What do you have configured as your shared buffers? If you haven't given very much, there won't be much in the way of buffers used, of course. Note that there's a much earlier diminishing return on the size of shared buffers in Postgres than in many systems. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] A long-running transaction
Andrew Sullivan wrote: On Wed, Apr 11, 2007 at 12:41:23AM +0800, John Summerfield wrote: The Linux kernel's clearly doing a lot of work, and the disk supports You might also be into context-switch hell. What processor, which kernel, and which Postgres version again? on opensuse 10.2 (the laptop) model name : Intel(R) Pentium(R) M processor 1500MHz 2.6.18.8-0.1-xen Might not have been xen-enabled, I switch fairly often [EMAIL PROTECTED]:~> rpm -qa postg\* postgresql-server-8.1.5-13 postgresql-libs-8.1.5-13 postgresql-devel-8.1.5-13 postgresql-jdbc-8.1-12.2 postgresql-contrib-8.1.5-13 postgresql-pl-8.1.5-15 postgresql-8.1.5-13 [EMAIL PROTECTED]:~> Also: model name : AMD Sempron(tm) 2400+ 2.6.17-6-server-xen0 [EMAIL PROTECTED]:~$ dpkg --list postg\* | grep ^ii ii postgresql-8.2 8.2.3-1~edgy1 object-relational SQL database, version 8.2 For each record, I update a non-key field in another table; the source data for that other table is less than a megabyte. this is a real issue. Basically, you're constrained at the rotation speed of your disk, because for each record, you have to first find then update one row somewhere else. It should be in cache: it's all one transaction, and on the laptop, It's not in cache if you're updating -- you have to write it. Linux caches writes, I don't think it should be hitting disk at all. The table being updated contains records 7482 (658K raw data) of which probably fewer than 2000 are being updated, and typically the same ones all the time: we're updating the date of the latest trade. there's over a Gbyte of RAM. Indeed, I would expect postgresql itself to cache it (except the fact it uses so little RAM suggests it doesn't do that at all). What do you have configured as your shared buffers? If you haven't given very much, there won't be much in the way of buffers used, of course. Note that there's a much earlier diminishing return on the size of shared buffers in Postgres than in many systems. Laptop (1.25 Gbytes) shared_buffers = 1000 # min 16 or max_connections*2, 8KB each AMD (512 Mbytes less some for video) shared_buffers = 24MB # min 128kB or max_connections*16kB Note we're only running one connexion here, and data transfers (I believe) are tens of bytes: here is a typical data record: 2005-02-03,AAC,1.900,1.800,1.850,1.820,328984,0 A ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
