Re: [GENERAL] Planner create a slow plan without an available index
Martijn van Oosterhout wrote: On Wed, Aug 31, 2005 at 09:19:05AM +0200, Ben-Nes Yonatan wrote: If the subtransaction writes at least a tuple, it counts as another transaction. Else it doesn't count. Oh crap I fear that now im in serious troubles Where can I read about this limitation? and beside that what if I count the number of queries and every 900,000 or so I create a subtransaction and continue my process with it, will that work or I'm just trying to be a smart ass with the db? Um, 1 billion transactions is 1 thousand million. So 900,000 inserts/updates are not even one tenth of one percent of the limit for one transaction. Are you really approaching a billion inserts/updates per transaction? That's alot of diskspace being used... Have a nice day, No apprantly I just lack a decent sleep I think that ill stop ask you guys questions before you will decide to get your clubs out... :P In other words I was mistaken and thought about a million and not a billion :) With hopes that this is the end of my bugging :) Thanks alot, Ben-Nes Yonatan ---(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] Planner create a slow plan without an available index
On Wed, Aug 31, 2005 at 09:19:05AM +0200, Ben-Nes Yonatan wrote: > >If the subtransaction writes at least a tuple, it counts as another > >transaction. Else it doesn't count. > > > > Oh crap I fear that now im in serious troubles > Where can I read about this limitation? and beside that what if I count > the number of queries and every 900,000 or so I create a subtransaction > and continue my process with it, will that work or I'm just trying to be > a smart ass with the db? Um, 1 billion transactions is 1 thousand million. So 900,000 inserts/updates are not even one tenth of one percent of the limit for one transaction. Are you really approaching a billion inserts/updates per transaction? That's alot of diskspace being used... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpHffY8nXgNl.pgp Description: PGP signature
Re: [GENERAL] Planner create a slow plan without an available index
Alvaro Herrera wrote: On Tue, Aug 30, 2005 at 10:39:57PM -0500, Bruno Wolff III wrote: On Wed, Aug 31, 2005 at 01:27:30 +0200, Ben-Nes Yonatan <[EMAIL PROTECTED]> wrote: Now again im probably just paranoid but when I'm starting a transaction and in it im making more then 4 billions diffrent queries (select,insert,update,truncate...) and then im closing it, its counted as only one transaction right? (should I duck to avoid the manual? ;)) I believe there is a limit on the number of queries in a transaction of 2 or 4 billion (though this may be just in functions). Ignoring subtransactions, all these queries count as just one transaction. I am not sure how subtransactions are counted. If the subtransaction writes at least a tuple, it counts as another transaction. Else it doesn't count. Oh crap I fear that now im in serious troubles Where can I read about this limitation? and beside that what if I count the number of queries and every 900,000 or so I create a subtransaction and continue my process with it, will that work or I'm just trying to be a smart ass with the db? As always thanks alot, Ben-Nes Yonatan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] or kills performance
My goal is a specific batchid, stock>0, ownerid=1 and either leadfree with a state of 1 or 3 OR not leadfree with a state of 2,3 or 4 The parenthesis are correct: where a.batchid=382 and e.stock>0 and e.ownerid=1 and ( (d.leadfree and leadstateid in (1,3) ) or (not d.leadfree and leadstateid in (2,3,4) ) ) order by partid,leadstateid I checked my indices and every field in both the where clause and the join is already indexed. I tried unioning the 2 queries and it was much faster then with the OR statement. (Took 200 ms as opposed to 2000 ms). The union will work, but it seems like overkill for a simple or clause. Is this the recommended way to do it? select c.partid,c.pnid,c.leadstateid,e.stock from assemblies d join assembliesbatch a on d.assemblyid=a.assemblyid join partsassembly b on b.assemblyid=d.assemblyid join manufacturerpartpn c on c.partid=b.partid join stock e on e.pnid=c.pnid where a.batchid=382 and e.stock>0 and e.ownerid=1 and not d.leadfree and leadstateid in (2,3,4) union select c.partid,c.pnid,c.leadstateid,e.stock from assemblies d join assembliesbatch a on d.assemblyid=a.assemblyid join partsassembly b on b.assemblyid=d.assemblyid join manufacturerpartpn c on c.partid=b.partid join stock e on e.pnid=c.pnid where a.batchid=382 and e.stock>0 and e.ownerid=1 and d.leadfree and leadstateid in (1,3) order by partid,leadstateid "Sim Zacks" <[EMAIL PROTECTED]> writes: > Does it make sense for a simple or in a where clause to kill performance? Did you get the parenthesization correct? Remember that AND binds more tightly than OR. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Invalid page header in pg_class
Hi Tom, Enabling the zero_damaged_pages solved the problem. I am in the process of dumping & restoring. Thanks for the help. Gokul. --- Tom Lane <[EMAIL PROTECTED]> wrote: > gokulnathbabu manoharan <[EMAIL PROTECTED]> > writes: > > In my sample databases the relfilenode for > pg_class > > was 1259. So I checked the block number 190805 of > the > > 1259 file. Since the block size is 8K, 1259 was > in > > two files 1259 & 1259.1. The block number 190805 > > falls in the second file whose block number is > > 58733((190805 - (1G/8K)) = 58733). > > You've got a pg_class catalog exceeding a gigabyte?? > Apparently you've been exceedingly lax about > vacuuming. > You need to do something about that, because it's > surely > hurting performance. > > You did the math wrong --- the damaged block would > be 59733, not > 58733, which is why pg_filedump isn't noticing > anything wrong here. > > It seems almost certain that there are only dead > rows in the > damaged block, so it'd be sufficient to zero out the > block, > either manually with dd or by turning on > zero_damaged_pages. > After that I'd recommend a dump, initdb, reload, > since there may > be other damage you don't know about. > > regards, tom lane > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] optimum settings for dedicated box
On Tue, Aug 30, 2005 at 09:43:19PM -0700, Ian Harding wrote: > Mine in similar, and the only thing I have changed from defaults is > work_mem. It made certain complex queries go from taking forever to > taking seconds. I have a database connection pool limited to 10 > connections, so I set it to 10MB. That means (to me, anyway) that > work_mem will never gobble more then 100MB. Seems OK since I have > 1GB. That's not totally true. A single query can use work_mem for multiple steps, so if work_mem is 10MB a single query could end up using 20MB, 30MB, or even more. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-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] optimum settings for dedicated box
Mine in similar, and the only thing I have changed from defaults is work_mem. It made certain complex queries go from taking forever to taking seconds. I have a database connection pool limited to 10 connections, so I set it to 10MB. That means (to me, anyway) that work_mem will never gobble more then 100MB. Seems OK since I have 1GB. Free space map should probably be tweaked too, if you have lots of updates or deletes. I think. - Ian On 8/30/05, Matt A. <[EMAIL PROTECTED]> wrote: > Wondering what the optimum settings are for an > dedicated postgresql database box? The box is an > 2.8ghz processor, 1gig ram (soon will be 4) and raid 1 > (mirroring) across two 10k rpm SCSI disks. I only have > a single database on it running linux of course. Thanks. > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---(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 > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Help with SPI...
On Tue, Aug 30, 2005 at 01:28:11PM -0600, Cristian Prieto wrote: > Well, the new value is really the content of a memory segment, I > know I could store it again using SPI and an UPDATE statement, but > that means that I need to transform the val value into a string, > and I don't know the length of the string with the scape characters > added. You could use SPI_prepare() and SPI_execp() without having to transform the bytea value into a string; see the SPI documentation and look around for examples that use numbered parameters ($1, $2, etc.). -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Planner create a slow plan without an available index
On Tue, Aug 30, 2005 at 10:39:57PM -0500, Bruno Wolff III wrote: > On Wed, Aug 31, 2005 at 01:27:30 +0200, > Ben-Nes Yonatan <[EMAIL PROTECTED]> wrote: > > > > Now again im probably just paranoid but when I'm starting a transaction > > and in it im making more then 4 billions diffrent queries > > (select,insert,update,truncate...) and then im closing it, its counted > > as only one transaction right? (should I duck to avoid the manual? ;)) > > I believe there is a limit on the number of queries in a transaction of > 2 or 4 billion (though this may be just in functions). > > Ignoring subtransactions, all these queries count as just one transaction. > I am not sure how subtransactions are counted. If the subtransaction writes at least a tuple, it counts as another transaction. Else it doesn't count. -- Alvaro Herrera Architect, www.EnterpriseDB.com "I'm always right, but sometimes I'm more right than other times." (Linus Torvalds) ---(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] Php abstraction layers
Antimon wrote: Hi, I'm working on a new web project based on php and i decided to use PostgreSQL 8.x as dbms. For triggers, views, stored procedures etc. I was going to write a simple wrapper class and use pg_* functions. But some friends adviced me to use an abstraction layer. I checked PEAR:DB and AdoDB. They look pretty but i don't understand why sould i need one? Do yourself a favor and write lightweight wrapper functions. This means that if something needs to be changed (say, a PHP API name change happens) you don't have to rewrite a lot of your code. Additionally, if you do have to port someday to Interbase or even (gasp) MySQL, it becomes possible thought not always straightforward. *Simple* and light database abstractions are very nice because they isolate your framework from the API syntax and after a few years, something could change and then you don't have to rewrite a whole lot. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] About "ERROR: must be *superuser* to COPY to or from a file"
On Tue, Aug 30, 2005 at 11:20:49PM -0400, Greg Stark wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > > Plus, how is the server supposed to KNOW that you have access to the > > file? psql may know who you are, but the server only knows who you are > > in the "postgresql" sense, not the OS sense. > > My original suggestion was that clients connected via unix domain sockets > should be allowed to read any file owned by the same uid as the connecting > client. (Which can be verified using getpeereid/SO_PEERCRED/SCM_CREDS.) > > Alternatively and actually even better and more secure would be passing the fd > directly from the client to the server over the socket. That avoids any > question of the server bypassing any security restrictions. The client is > responsible for opening the file under its privileges and handing the > resulting fd to the server over the socket. > > None of this helps for remote clients of course but remote clients can just > ftp the file to the server anyways and some manual intervention is necessarily > needed by the DBA to create a security policy for them. What do people think about the Oracle method where bulk data operations can only occur in a specified directory? Making that restriction might address some of the security concerns. I don't think we should change COPY in such a way that you *have* to use a specified directory, but if it was an option that helped with the security concerns... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Planner create a slow plan without an available index
On Wed, Aug 31, 2005 at 01:27:30 +0200, Ben-Nes Yonatan <[EMAIL PROTECTED]> wrote: > > Now again im probably just paranoid but when I'm starting a transaction > and in it im making more then 4 billions diffrent queries > (select,insert,update,truncate...) and then im closing it, its counted > as only one transaction right? (should I duck to avoid the manual? ;)) I believe there is a limit on the number of queries in a transaction of 2 or 4 billion (though this may be just in functions). Ignoring subtransactions, all these queries count as just one transaction. I am not sure how subtransactions are counted. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Planner create a slow plan without an available index
On Wed, Aug 31, 2005 at 01:27:30AM +0200, Ben-Nes Yonatan wrote: > Now again im probably just paranoid but when I'm starting a transaction > and in it im making more then 4 billions diffrent queries > (select,insert,update,truncate...) and then im closing it, its counted > as only one transaction right? (should I duck to avoid the manual? ;)) Yes, one transaction. You cannot do more than 4 billion commands -- the limit is 2^32 anyway. -- Alvaro Herrera Architect, www.EnterpriseDB.com "Los románticos son seres que mueren de deseos de vida" ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] About "ERROR: must be *superuser* to COPY to or from a file"
Scott Marlowe <[EMAIL PROTECTED]> writes: > Plus, how is the server supposed to KNOW that you have access to the > file? psql may know who you are, but the server only knows who you are > in the "postgresql" sense, not the OS sense. My original suggestion was that clients connected via unix domain sockets should be allowed to read any file owned by the same uid as the connecting client. (Which can be verified using getpeereid/SO_PEERCRED/SCM_CREDS.) Alternatively and actually even better and more secure would be passing the fd directly from the client to the server over the socket. That avoids any question of the server bypassing any security restrictions. The client is responsible for opening the file under its privileges and handing the resulting fd to the server over the socket. None of this helps for remote clients of course but remote clients can just ftp the file to the server anyways and some manual intervention is necessarily needed by the DBA to create a security policy for them. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Php abstraction layers
IMO I think it really depends on what you want to do. The pg_functions are the *most* robust and full featured. There are problems with PDO (mostly function related at this point). That being said, PDO is great to work with when you get into it, and learn your way around it. If your project is pgsql only and will only be pgsql only, I'd suggest using the pg_ functions. I use both extensively, but only use PDO on projects where I want other programmers to be able to extend my work with other dbms support. If you're new to PHP db programming, PDO might not be the easiest way to go, it's still a little rough around the edges. Good luck and let me know if you need any help with either. There's also a pgsql+php list you might want to jump on: http://www.postgresql.org/community/lists/ subscribe and http://archives.postgresql.org/pgsql-php/ Regards, Gavin On Aug 30, 2005, at 8:00 PM, Greg Stark wrote: "Antimon" <[EMAIL PROTECTED]> writes: Thanks for the reply. I checked new 5.1 pg_ functions and i wanna ask something else. What do you think about PDO? It is not an abstraction layer, just something like wrapper. I thought as it supports both widely used dbmss, php developers would focus on it more than pg or mysqli functions and that can make it powerful. Would it be a good decision to use PDO instead of pg_ functions? My understanding is that PDO is the way and the light. Use PDO. Unfortunately my project began before PDO saw the light of day, but I plan to migrate to it eventually. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Gavin M. Roy 800 Pound Gorilla [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] About "ERROR: must be *superuser* to COPY to or from a file"
Greg Stark <[EMAIL PROTECTED]> writes: > Alternatively and actually even better and more secure would be > passing the fd directly from the client to the server over the socket. Sure ... on the platforms that support that, for the connection types for which they support it. But I think that in the long run we'd regret inventing any SQL operations whose semantics depend on the transport mechanism. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Php abstraction layers
"Antimon" <[EMAIL PROTECTED]> writes: > Thanks for the reply. > I checked new 5.1 pg_ functions and i wanna ask something else. What do > you think about PDO? It is not an abstraction layer, just something > like wrapper. I thought as it supports both widely used dbmss, php > developers would focus on it more than pg or mysqli functions and that > can make it powerful. > Would it be a good decision to use PDO instead of pg_ functions? My understanding is that PDO is the way and the light. Use PDO. Unfortunately my project began before PDO saw the light of day, but I plan to migrate to it eventually. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PQConnectdb SSL (sslmode): Is this a bug
On Tue, Aug 30, 2005 at 10:40:26AM -0700, vishal saberwal wrote: > Root user: > /root/.postgressql: Is this the actual directory name? It's misspelled: it should be ".postgresql", not ".postgressql". -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] update functions locking tables
2005/8/30, Jim C. Nasby <[EMAIL PROTECTED]>: > > FWIW, that where clause might be more efficient as > WHERE pontos_0 > pontos_7. Some databases would be able to use indexes > to answer that (not sure if PostgreSQL could), plus it removes an > operator. It also seems to be cleaner code to me. :) > -- Done, thanks. Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] update functions locking tables
On Tue, Aug 30, 2005 at 08:13:15AM -0300, Clodoaldo Pinto wrote: > 2005/8/29, Michael Fuhr <[EMAIL PROTECTED]>: > > > > In general, writers shouldn't block readers. Have you examined > > pg_locks? Do you know exactly what the blocked queries are, or can > > you find out from pg_stat_activity (stats_command_string must be > > enabled)? Are you doing any explicit locking (LOCK statement)? > > > > This is one of the blocked queries: > select count (*) from times_producao where pontos_0 - pontos_7 > 0; FWIW, that where clause might be more efficient as WHERE pontos_0 > pontos_7. Some databases would be able to use indexes to answer that (not sure if PostgreSQL could), plus it removes an operator. It also seems to be cleaner code to me. :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Get postgresql workin in french...
Hi, I would know how to set the encoding (unicode, ASCII, etc.) for getting postgresql accepting my entry with accent an all the what the french poeple put over there caracter while they write... Well thanks in advance. Just leave me a links our try to explain it. I gonna continu to search why it doesn't work... Cesium __ Find your next car at http://autos.yahoo.ca ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Php abstraction layers
Thanks for the reply. I checked new 5.1 pg_ functions and i wanna ask something else. What do you think about PDO? It is not an abstraction layer, just something like wrapper. I thought as it supports both widely used dbmss, php developers would focus on it more than pg or mysqli functions and that can make it powerful. Would it be a good decision to use PDO instead of pg_ functions? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Php abstraction layers
Hi, I'm working on a new web project based on php and i decided to use PostgreSQL 8.x as dbms. For triggers, views, stored procedures etc. I was going to write a simple wrapper class and use pg_* functions. But some friends adviced me to use an abstraction layer. I checked PEAR:DB and AdoDB. They look pretty but i don't understand why sould i need one? Project will be postgre dependant because of the database structure, i don'T need to support multiple dbms types. Since i may not even have a chance to convert database structure. And php 5.1's postgresql library has all new features implemented. In this situation, what would be the advantage of using an abstraction layer? Thanks. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Is this still valid for current Postgresql versions?
Tony Caduto <[EMAIL PROTECTED]> writes: > "The current implementation of RETURN NEXT for PL/pgSQL stores the > entire result set before returning from the function" > I thought I read somewhere that 8.x did not do this. Sorry. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Is this still valid for current Postgresql versions?
"The current implementation of RETURN NEXT for PL/pgSQL stores the entire result set before returning from the function" I thought I read somewhere that 8.x did not do this. Thanks, Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Planner create a slow plan without an available index
Tom Lane wrote: Martijn van Oosterhout writes: rtree works on multidimesional (geometric) data. It can do range tests (is object A to the left of object B) but it's only applicable if your conditions can be interpreted that way. GiST is for creating custom index types, hardly likely to be useful in your case. Actually either rtree or GIST should be able to do something useful with this, since it's basically a 1-D overlap query. The main problem with GIST is to find a suitable opclass, since there aren't any in the core system. Possibly contrib/seg could be used. regards, tom lane Ok first of all thanks guys as always for your help, and I will try to use rtree to improve my query (hopefuly ill be able to come back and say that it worked :)). I got another question which is not connected and probably its just me being paranoid late at night but still... :) at chapter "21.1.3. Preventing transaction ID wraparound failures" of the postgresql manual its written the following info: "Prior to PostgreSQL 7.2, the only defense against XID wraparound was to re-initdb at least every 4 billion transactions. This of course was not very satisfactory for high-traffic sites, so a better solution has been devised. The new approach allows a server to remain up indefinitely, without initdb or any sort of restart. The price is this maintenance requirement: every table in the database must be vacuumed at least once every billion transactions." My postgresql version is 8.01 (I should have mentioned that at start no? :)) Now again im probably just paranoid but when I'm starting a transaction and in it im making more then 4 billions diffrent queries (select,insert,update,truncate...) and then im closing it, its counted as only one transaction right? (should I duck to avoid the manual? ;)) As always thanks alot! Ben-Nes Yonatan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] About "ERROR: must be *superuser* to COPY to or from
On Mon, 2005-08-29 at 18:59, Tom Lane wrote: > Greg Stark <[EMAIL PROTECTED]> writes: > > I was only suggesting using this from a local unix user where you can > > actually authoritatively say something about the uid of the connecting > > user. I suggested that if the owner of the file matches the uid of the > > connecting user (which you can get on a unix domain socket) > > ... on some platforms ... and half the world connects over TCP even on > local connections ... > > > then there's no reason not to grant access to the file. > > Assuming that the server itself can get at the file, which is > questionable if the file is owned by the connecting user rather than the > server (and, for instance, may be located under a not-world-readable > home directory). And then there are interesting questions like whether > the server and the user see eye-to-eye on the name of the file (consider > server inside chroot jail, AFS file systems, etc). > > There are enough holes in this to make it less than attractive. We'd > spend more time answering questions about "why doesn't this work" than > we do now, and I remain unconvinced that there would be no exploitable > security holes. Plus, how is the server supposed to KNOW that you have access to the file? psql may know who you are, but the server only knows who you are in the "postgresql" sense, not the OS sense. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.1 observation
Tony Caduto <[EMAIL PROTECTED]> writes: > I just noticed that pronargs in pg_proc does not show the full arg > count, seems only to show count of IN args. > shouldn't this show the full arg count including in/out/inout? There was some discussion of that just a day or so ago; so far no one's come up with a reasonable suggestion for what the output should look like. > Also is it ok to talk about the 8.1 beta in this list? Beta testing is generally considered off-topic for -general; try -hackers. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Help with SPI...
Hi, I will try to explain the most possible my
question:
I'm writing a Store Procedure as a C Language
Function in the Database, I need to handle a bytea (binary objetc) and store it
in a modified mode in the database, I've done it in the following
way:
PG_FUNCTION_INFO_V1(myspi);
Datummyspi(PG_FUNCTION_ARGS){ int
ret; bool isnull; bytea *val;
ret = SPI_connect();
ret = SPI_exec("SELECT val FROM
binary_table", 1); if (ret == SPI_OK_SELECT && SPI_processed
> 0) { TupleDesc tupdesc =
SPI_tuptable->tupdesc; SPITupleTable *tuptable =
SPI_tuptable;
val =
DatumGetByteP(SPI_getbinval(tuptable->vals[0], tupdesc, 1,
&isnull)); }
/* Here I use and modified the new version of the
val value */
// I don't know what to do here to store the new
value again
:( SPI_finish(); PG_RETURN_INT32(val);}
Well, the new value is really the content of a
memory segment, I know I could store it again using SPI and an UPDATE statement,
but that means that I need to transform the val value into a string, and I don't
know the length of the string with the scape characters added.
Any idea in how to handle this?
Thanks a lot...
[GENERAL] 8.1 observation
Hi, I just noticed that pronargs in pg_proc does not show the full arg count, seems only to show count of IN args. shouldn't this show the full arg count including in/out/inout? Also is it ok to talk about the 8.1 beta in this list? Thanks, Tony ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] POSS. FEATURE REQ: "Dynamic" Views
On Sat, 2005-08-27 at 09:48, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > >> How is this different from materialized views, which is already on the > >> TODO list? > > > The idea behind the DYNAMIC VIEW is that if you made a DDL change in the > > table it could be reflected in the view. So for example, if you defined > > a view as SELECT * FROM table; and then added a field to the table that > > field would also show up in the view. > > But why exactly is this a good idea? It seems like an absolutely > horrible idea to me. It is oft-repeated advice that you don't use > "SELECT *" ever in production programming, because your applications > will break as soon as any columns are added (or removed, even if they > don't make any use of those columns). The proposed dynamic view > facility would move that instability of results right into the views. > > What's more, I cannot see any benefit to be gained over just issuing > the expanded query directly. You couldn't layer a normal view over > a dynamic view (not having any idea what columns it'll return), nor > even a prepared statement, because those things nail down specific > result columns too. So it's just an awkwardly expressed form of > query macro that can only be used in interactively-issued commands. > > I think the burden of proof is on the proponents of this idea to show > that it's sensible, and it doesn't deserve to be in TODO just because > one or two people think it'd be nice. Actually, I've written a few very abstracted database applications that basically did a select * and then used the libpq stuff to find the column names and types and such and put the data on the screen in an edit form. Such applications know NOTHING about the actual structure of the table or view they are operating on, and rely on getting said data from the database. This makes them very portable. Need another instance of such an app and all you need to do is copy in the files and edit one or two config lines to tell it which table(s) to hit and you're gold. So, there are certain types of applications where select * is pretty useful. I'm not saying I like the idea of dynamic views, but I can see in a few circumstances where they might be useful. I can see far more where they can cause headaches galore. Just pointing out that some applications naturally lend themselves to select * is all. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] GROUP BY requirement
On Fri, 2005-08-26 at 14:39, Bill Moseley wrote: > I'm wondering if adding a GROUP BY (as required by Postgres) will > change the results of a select on a view. > > I have the following view which joins a "class" with a teacher. A > teacher is a "person" and I have an "instructors" link table. > > CREATE VIEW class_list (id, class_time, instructor ) > AS > SELECT DISTINCT ON(class.id) >class.id, class.class_time, person.first_name > > FROM class, instructors, person > WHERE instructors.person = person.id >AND class.id = instructors.class; > > I also have a table "registration" that links students with a class. > The registration table has a "reg_status" column to say if they are > confirmed or on the wait_list. So when showing the above I'd also > like to see how many students are confirmed and on the wait_list. > > DROP VIEW cl; > CREATE VIEW cl (id, class_time, instructor, > confirmed_cnt, wait_list_cnt) > AS > SELECT DISTINCT ON(class.id) >class.id, class.class_time, person.first_name, >sum (CASE WHEN registration.reg_status = 1 THEN 1 ELSE 0 END) as > confirmed_cnt, >sum (CASE WHEN registration.reg_status = 2 THEN 1 ELSE 0 END) as > wait_list_cnt, > > FROM class, instructors, person, registration > WHERE instructors.person = person.id >AND class.id = instructors.class >AND class.id = registration.class > > GROUP BY class.id, class.class_time, person.first_name; > > PostgreSQL requires the GROUP BY. But, I'm not clear how the GROUP BY > might change the results between the two views above. > > http://www.postgresql.org/docs/8.0/static/sql-select.html#SQL-GROUPBY > > says: > > When GROUP BY is present, it is not valid for the SELECT list > expressions to refer to ungrouped columns except within aggregate > functions, since there would be more than one possible value to > return for an ungrouped column. > > Frankly, I cannot see how it might change results of a select between > the two views. Am I missing something? OK, distinct on suffers from this problem. Given the following simple dataset: mytable: a | b -- 1 | 0 1 | 1 select distinct on (a) a,b from mytable; One can see how the possible results are: 1,0 and 1,1, right? All depending on the order in which they are fetched. The same would be true if you could do a group by on a and select b: select a,b from mytable group by a; Right? Now, if it's impossible for your dataset to return such sets, due to the way it's built, it is likely not fully normalized. I.e. you have data like this: classid | instructorname | moreinfo... -- 1 | 'John Smith' | 'information' 1 | 'John Smith' | 'even more information' and so on. Or your join is creating such a data set. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgresql replication
On Wed, Aug 24, 2005 at 11:54:42AM -0400, Chris Browne wrote: > There is a Slony-II project ongoing that is trying to construct a > more-or-less synchronous multimaster replication system (where part of > the cleverness involves trying to get as much taking place in an > asynchronous fashion as possible) that would almost certainly be of no > use to your "use case." Just to emphasise this point: assuming we ever get Slony-II to work, it is all but guaranteed to be useless for cases like the one that started this thread: it'll simply require very fast network connections to work. I've had more than one person ask me when multi-site multimaster is coming, and my answer is always, "Have you started work on it yet?" I think there might be a way to hack up Slony-I to do it -- Josh Berkus gave me a quick outline while at OSCON that made me think it possible -- but AFAIK, nobody is actually doing that work. It's worth noting that single-origin master-slave async replication is tricky, but by no means impossible. Multi-master _anything_ is hard, no question about it; and it more or less always imposes some overhead that you won't like. The question is merely whether you want to pay that price. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(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] Postgresql replication
On Thu, Aug 25, 2005 at 01:44:15PM +0200, Bohdan Linda wrote: > there are some other db solutions which have good performance when doing > this kind of replication across the world. Bluntly, "No." -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Invalid page header in pg_class
gokulnathbabu manoharan <[EMAIL PROTECTED]> writes: > In my sample databases the relfilenode for pg_class > was 1259. So I checked the block number 190805 of the > 1259 file. Since the block size is 8K, 1259 was in > two files 1259 & 1259.1. The block number 190805 > falls in the second file whose block number is > 58733((190805 - (1G/8K)) = 58733). You've got a pg_class catalog exceeding a gigabyte?? Apparently you've been exceedingly lax about vacuuming. You need to do something about that, because it's surely hurting performance. You did the math wrong --- the damaged block would be 59733, not 58733, which is why pg_filedump isn't noticing anything wrong here. It seems almost certain that there are only dead rows in the damaged block, so it'd be sufficient to zero out the block, either manually with dd or by turning on zero_damaged_pages. After that I'd recommend a dump, initdb, reload, since there may be other damage you don't know about. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PQConnectdb SSL (sslmode): Is this a bug
Thanks michael for your response ... I had read the links (you suggested) before, but yes i missed some important points ... hmmm i believe it was me who was wrong again ... I was trying to connect to the server from the same machine server is running on ... well, in this case it has to serve as client as well ... you are right ... Then I create the directory and place the files, but i am still unable to connect ... Root user: /root/.postgressql: total 8 -rw-r--r-- 1 root root 3675 Aug 30 09:16 postgresql.crt -rw--- 1 root root 887 Aug 30 09:16 postgresql.key Postgres user: -bash-2.05b$ ls -al ~/.postgresql/* -rw-r--r-- 1 postgres postgres 3675 Aug 30 09:30 /var/lib/pgsql/.postgresql/postgresql.crt -rw--- 1 postgres postgres 887 Aug 30 09:30 /var/lib/pgsql/.postgresql/postgresql.key -bash-2.05b$ chown postgres:postgres ~/.postgresql/ [EMAIL PROTECTED] serv]# ./bin/test_lib Connection failed: could not open certificate file "/root/.postgresql/postgresql.crt": No such file or directory ret=-1 [EMAIL PROTECTED] serv]# [EMAIL PROTECTED] root]# ll /usr/lib/libpq* -rw-r--r-- 1 postgres root 1480452 Mar 10 2004 /usr/lib/libpq.a lrwxrwxrwx 1 root root 12 Aug 30 09:23 /usr/lib/libpq.so -> libpq.so.3.2 lrwxrwxrwx 1 root root 12 Aug 30 09:23 /usr/lib/libpq.so.3 -> libpq.so.3.2 -rwxr-xr-x 1 postgres root 113988 Mar 10 2004 /usr/lib/libpq.so.3.1 -rwxr-xr-x 1 postgres root 122177 Aug 26 12:55 /usr/lib/libpq.so.3.2 [EMAIL PROTECTED] root]# ll /usr/local/pgsql/lib/libpq* -rw-r--r-- 1 root root 144470 Aug 26 13:17 /usr/local/pgsql/lib/libpq.a lrwxrwxrwx 1 root root 12 Aug 26 13:17 /usr/local/pgsql/lib/libpq.so -> libpq.so.3.2 lrwxrwxrwx 1 root root 12 Aug 26 13:17 /usr/local/pgsql/lib/libpq.so.3 -> libpq.so.3.2 -rwxr-xr-x 1 root root 122177 Aug 26 13:17 /usr/local/pgsql/lib/libpq.so.3.2 [EMAIL PROTECTED] root]# ll /usr/local/pgsql/data/ total 100 drwx-- 20 postgres postgres 4096 Aug 29 10:35 base drwx-- 2 postgres postgres 4096 Aug 30 10:21 global drwx-- 2 postgres postgres 4096 Aug 22 17:48 pg_clog -rw--- 1 postgres postgres 154 Aug 25 17:56 pg_hba.conf -rw--- 1 postgres postgres 1460 Aug 22 17:48 pg_ident.conf drwx-- 2 postgres postgres 4096 Aug 22 17:48 pg_subtrans drwx-- 2 postgres postgres 4096 Aug 22 17:48 pg_tblspc -rw--- 1 postgres postgres 4 Aug 22 17:48 PG_VERSION drwx-- 3 postgres postgres 4096 Aug 29 10:41 pg_xlog -rw--- 1 postgres postgres 11043 Aug 25 17:14 postgresql.conf -rw--- 1 postgres postgres 59 Aug 30 09:44 postmaster.opts -rw--- 1 postgres postgres 47 Aug 30 09:44 postmaster.pid -rw-r--r-- 1 postgres postgres 1298 Aug 24 16:10 root.crt -rw-r--r-- 1 postgres postgres 963 Aug 24 16:10 root.key -rw-r--r-- 1 postgres postgres 3675 Aug 24 16:10 server.crt -rw--- 1 postgres postgres 887 Aug 24 16:10 server.key -rw-r--r-- 1 postgres postgres 2305 Aug 24 13:05 server.req [EMAIL PROTECTED] root]# Connection String: "hostaddr=169.254.59.60 dbname=dbm user=postgres sslmode=prefer" [EMAIL PROTECTED] serv]# ldd ./bin/test_lib linux-gate.so.1 => (0x00138000) libpthread.so.0 => /lib/tls/libpthread.so.0 (0x003c8000) libpq.so.3 => /usr/local/pgsql/lib/libpq.so.3 (0x005de000) libstdc++.so.5 => /usr/lib/libstdc++.so.5 (0x0018d000) libm.so.6 => /lib/tls/libm.so.6 (0x002b) libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x004e7000) libc.so.6 => /lib/tls/libc.so.6 (0x005f7000) /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x00176000) libssl.so.4 => /lib/libssl.so.4 (0x00c6a000) libcrypto.so.4 => /lib/libcrypto.so.4 (0x0076f000) libcrypt.so.1 => /lib/libcrypt.so.1 (0x00caa000) libresolv.so.2 => /lib/libresolv.so.2 (0x003ff000) libnsl.so.1 => /lib/libnsl.so.1 (0x00c53000) libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2 (0x00758000) libkrb5.so.3 => /usr/lib/libkrb5.so.3 (0x00248000) libcom_err.so.2 => /lib/libcom_err.so.2 (0x00111000) libk5crypto.so.3 => /usr/lib/libk5crypto.so.3 (0x00714000) libdl.so.2 => /lib/libdl.so.2 (0x002d5000) libz.so.1 => /usr/lib/libz.so.1 (0x002db000) [EMAIL PROTECTED] serv]# ./bin/test_lib Connection failed: could not open certificate file "/root/.postgresql/postgresql.crt": No such file or directory ret=-1 [EMAIL PROTECTED] serv]# cat /var/lib/pgsql/logfile LOG: database system was shut down at 2005-08-30 09:39:28 PDT LOG: checkpoint record is at 0/65650CD0 LOG: redo record is at 0/65650CD0; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 15622; next OID: 11928398 LOG: database system is ready LOG: could not accept SSL connection: peer did not return a certificate LOG: could not accept SSL connection: peer did not return a certificate [EMAIL PROTECTED] serv]# Where am i going wrong? thanks, vish On 8/29/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Mon, Aug
[GENERAL] optimum settings for dedicated box
Wondering what the optimum settings are for an dedicated postgresql database box? The box is an 2.8ghz processor, 1gig ram (soon will be 4) and raid 1 (mirroring) across two 10k rpm SCSI disks. I only have a single database on it running linux of course. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] PostgreSQL 8.1 changes...
Cristian Prieto wrote: > Is around there any place where I could get the changes in PostgreSQL 8.1? Sure, it is in the developer's version of the manual, under Release Changes: http://www.postgresql.org/developer/beta -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PostgreSQL 8.1 changes...
Is around there any place where I could get the changes in PostgreSQL 8.1? Thanks a lot
Re: [GENERAL] update functions locking tables
30 Aug 2005 10:35:31 -0400, Greg Stark <[EMAIL PROTECTED]>: > > Well a regular vacuum will mark the free space for reuse. If you insert or > update any records the new ones will go into those spots. Make sure you set > the fsm_* parameters high enough to cover all the updates and inserts for the > entire day (or repeat the vacuum periodically even if there are no deletes or > updates going on to create more free space). I will check those fsm_* parameters. > > You should realize that what's going on here is that the old records are still > in your table, marked as deleted. So any sequential scan will take twice as > long as otherwise. I think even index scans could take twice as long too > depending on the distribution of values. > > I'm not saying that's untenable. If all your queries are fast enough then > you're set and it's just a cost of having no downtime. > > -- > greg > > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] detection of VACUUM in progress
On Tue, Aug 30, 2005 at 05:35:13PM +0200, Bohdan Linda wrote: > > Is there any way how to detect running command VACUUM by reading pg_* tables? If you have stats_command_string enabled then you could query pg_stat_activity, but be aware that the results will be stale by the time you see them (there's a slight lag in updating the stats tables, and a VACUUM might start or complete immediately after you issue the query but before you read the results). This method is therefore unreliable. > The idea is to detectect when table is not accessible due maintainance. In modern versions of PostgreSQL a plain VACUUM (without FULL) should have little impact on a table's accessibility unless you're doing something that needs a strong lock (DDL, etc.). But VACUUM FULL and a few other commands do prevent other transactions from accessing a table, so if you want to check for accessibility then you need to check for more than just VACUUM. You might be able to use statement_timeout to make statements fail if they take longer than expected, and infer from the failure that the table is unavailable. But again, that information could be stale by the time you see it -- the table might become available immediately after you decide that it isn't. What problem are you trying to solve? If we knew what you're really trying to do then we might be able to make suggestions. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] update functions locking tables
2005/8/30, Alvaro Herrera <[EMAIL PROTECTED]>: > On Tue, Aug 30, 2005 at 10:18:20AM -0300, Clodoaldo Pinto wrote: > > A vacuum full or a cluster is totally out of reach since each take > > about one hour. > > Even if you cluster/vacuum only the just-loaded table? > No, that would be much faster. The biggest just updated is about 600 thousand rows. I will consider it. > > The biggest table is 170 million rows long. > > I hope this is not the one you are loading daily ... > I load daily 8 times 700+ thousand rows. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] detection of VACUUM in progress
Bohdan Linda <[EMAIL PROTECTED]> writes: > Is there any way how to detect running command VACUUM by reading pg_* tables? > The idea is to detectect when table is not accessible due maintainance. Um, ordinary VACUUM doesn't render the table "not accessible". If you're using VACUUM FULL, maybe the right answer is to not do that. But to answer your question, you could look in pg_locks to see if there's an exclusive lock on the table. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgres optimizer
Like I said it's a research project. There is a feature that I'm trying to add to optimizer, but this feature should only be used when a join is a foreign key join. Hossein Joshua D. Drake wrote: I would be interested in knowing "why" you want to do such a thing? What is it you expect to gain? Sincerely, Joshua D. Drake The planner doesn't think there is any such thing as a "foreign key join". Perhaps you should modify the foreign key triggers (in ri_triggers.c) to collect the information you need. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] or kills performance
"Sim Zacks" <[EMAIL PROTECTED]> writes: > Does it make sense for a simple or in a where clause to kill performance? Did you get the parenthesization correct? Remember that AND binds more tightly than OR. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Php abstraction layers
Would it be a good decision to use PDO instead of pg_ functions? I don't see much benefits in using it, it just provides a common API naming scheme. It's maybe easier in case you work with a gazillion different DBMS and have trouble remembering function names, but that's it. On the other hand it locks you out from special API features / functions of PG. I wouldn't bother with it, especially not in your case. Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] detection of VACUUM in progress
Hello, Is there any way how to detect running command VACUUM by reading pg_* tables? The idea is to detectect when table is not accessible due maintainance. The approach of explicitely setting a flag into status table is not very convenient, while I want to cover also non-systematic launching of this command Regards, Bohdan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.1beta1 RPMs
On Tue, 2005-08-30 at 16:10 +0300, Devrim GUNDUZ wrote: > PostgreSQL RPM Building Project (http://pgfoundry.org/projects/pgsqlrpms) > has built RPMs for Red Hat Linux 9, Red Hat Enterprise Linux 3.0 and 4. > More may come later: > > http://developer.postgresql.org/~devrim/rpms/8.1/beta1/rpms/ > > We hope these RPMs will help more people to test this new great release of > PostgreSQL. Martin Pitt has loaded Debian packages for postgresql-8.1 into the Debian experimental archive. -- 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 5: don't forget to increase your free space map settings
Re: [GENERAL] Cursor declaration
Nigel Horne <[EMAIL PROTECTED]> writes: > I can't get passed this message: > 'Cannot mix placeholder styles "$1" and ":foo"' There's no such message anywhere in the Postgres sources. I suppose it must be coming from whatever client-side library you are using (which you didn't say). regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Planner create a slow plan without an available index
Martijn van Oosterhout writes: > rtree works on multidimesional (geometric) data. It can do range tests > (is object A to the left of object B) but it's only applicable if your > conditions can be interpreted that way. > GiST is for creating custom index types, hardly likely to be useful > in your case. Actually either rtree or GIST should be able to do something useful with this, since it's basically a 1-D overlap query. The main problem with GIST is to find a suitable opclass, since there aren't any in the core system. Possibly contrib/seg could be used. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] update functions locking tables
Clodoaldo Pinto <[EMAIL PROTECTED]> writes: > I'm already doing a vacuum (not full) once a day. > > A vacuum full or a cluster is totally out of reach since each take > about one hour. The biggest table is 170 million rows long. Well a regular vacuum will mark the free space for reuse. If you insert or update any records the new ones will go into those spots. Make sure you set the fsm_* parameters high enough to cover all the updates and inserts for the entire day (or repeat the vacuum periodically even if there are no deletes or updates going on to create more free space). You should realize that what's going on here is that the old records are still in your table, marked as deleted. So any sequential scan will take twice as long as otherwise. I think even index scans could take twice as long too depending on the distribution of values. I'm not saying that's untenable. If all your queries are fast enough then you're set and it's just a cost of having no downtime. -- greg ---(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] Php abstraction layers
In this situation, what would be the advantage of using an abstraction layer? NONE. PEAR::DB is one of the worst classes in PEAR and has lots of ugly code in it. AdoDB is IMHO a bit better, but as you said yourself, there is not a single reason why you should go through all the trouble of using DBMS abstraction layers, if you don't need them. The whole approche of such things is quite daft, because they abstract your DBMS API, but that's it. The different SQL implementations are not portable, not to mention pl/xxx functions, triggers, rules etc. Such things are usually advertised by users of stupid storage engines like MySQL, SqLite and people with very little knowlege. http://www.powerpostgresql.com/Downloads/database_depends_public.swf Though it might be a good idea to write yourself a set of functions / classes to handle escaping of data and make your DBMS work easier. To prevent SQL injections in PHP5.1, take a look at pg_query_params(). Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Cursor declaration
Nigel Horne wrote: How do I declare a cursor in a stored procedure if that cursor contains a WHERE which depends on the argument given to that stored procedure? I can't get passed this message: 'Cannot mix placeholder styles "$1" and ":foo"' The message doesn't give a useful line number, so I don't even know which line it's complaining about. Difficult to say - perhaps the source of the function would help. You don't actually mention what language you're using even. However, I'd look for anywhere you're using ":varname" since that isn't valid plpgsql. Then, I'd look at the OPEN ... CURSOR ... EXECUTE form of cursor opening (see manuals for details). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] update functions locking tables
On Tue, Aug 30, 2005 at 10:18:20AM -0300, Clodoaldo Pinto wrote: > 30 Aug 2005 09:10:51 -0400, Greg Stark <[EMAIL PROTECTED]>: > > Also, if consider doing a "vacuum full" or "cluster" after the batch job to > > clear up the free space (not in a large transaction). That will still take a > > table lock but it may be a small enough downtime to be worth the speed > > increase the rest of the day. > > I'm already doing a vacuum (not full) once a day. > > A vacuum full or a cluster is totally out of reach since each take > about one hour. Even if you cluster/vacuum only the just-loaded table? > The biggest table is 170 million rows long. I hope this is not the one you are loading daily ... -- Alvaro Herrera Architect, www.EnterpriseDB.com "El destino baraja y nosotros jugamos" (A. Schopenhauer) ---(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] or kills performance
Does it make sense for a simple or in a where clause to kill performance?
The statement with the OR takes 2500 ms and the statement without the OR
takes 190 ms:
select c.partid,c.pnid,c.leadstateid,e.stock from assembliesbatch a join
partsassembly b on a.assemblyid=b.assemblyid
join manufacturerpartpn c on c.partid=b.partid join assemblies d on
d.assemblyid=a.assemblyid
join stock e on e.pnid=c.pnid
where a.batchid=382 and e.stock>0 and e.ownerid=1 and
--THIS LINE CAUSING PROBLEM
((d.leadfree and leadstateid in (1,3)) or (not d.leadfree and leadstateid in
(2,3,4)))
order by partid,leadstateid
In this particular query, the first condition does not return any rows. When
I run it as
and not d.leadfree and leadstateid in (2,3,4)
then it takes only 190 ms and when I use only the first clause it takes less
then that and returns no rows.
Following are the 2 explain analyze results. (With Or first)
"Sort (cost=253.83..253.83 rows=1 width=16) (actual time=8368.145..8368.352
rows=62 loops=1)"
" Sort Key: c.partid, c.leadstateid"
" -> Nested Loop (cost=9.61..253.82 rows=1 width=16) (actual
time=14.358..8367.822 rows=62 loops=1)"
"-> Nested Loop (cost=9.61..243.77 rows=1 width=24) (actual
time=14.295..8353.241 rows=699 loops=1)"
" -> Nested Loop (cost=9.61..94.60 rows=16 width=20) (actual
time=0.100..3605.150 rows=291711 loops=1)"
"Join Filter: (((NOT "inner".leadfree) AND
(("outer".leadstateid = 2) OR ("outer".leadstateid = 3) OR
("outer".leadstateid = 4))) OR ("inner".leadfree AND (("outer".leadstateid =
1) OR ("outer".leadstateid = 3"
"-> Nested Loop (cost=0.00..44.14 rows=3 width=16)
(actual time=0.070..27.924 rows=793 loops=1)"
" -> Index Scan using ownerids on stock e
(cost=0.00..26.13 rows=3 width=8) (actual time=0.039..4.433 rows=793
loops=1)"
"Index Cond: (ownerid = 1)"
"Filter: (stock > 0)"
" -> Index Scan using manufacturerpartpn_pkey on
manufacturerpartpn c (cost=0.00..5.99 rows=1 width=12) (actual
time=0.010..0.015 rows=1 loops=793)"
"Index Cond: ("outer".pnid = c.pnid)"
"-> Materialize (cost=9.61..13.80 rows=419 width=5)
(actual time=0.003..1.444 rows=419 loops=793)"
" -> Seq Scan on assemblies d (cost=0.00..9.19
rows=419 width=5) (actual time=0.008..1.931 rows=419 loops=1)"
" -> Index Scan using ix_080c8ff0_5017_42a2_a174_28095b85106e_
on assembliesbatch a (cost=0.00..9.31 rows=1 width=4) (actual
time=0.009..0.009 rows=0 loops=291711)"
"Index Cond: ("outer".assemblyid = a.assemblyid)"
"Filter: (batchid = 382)"
"-> Index Scan using idx_u_assidpartid on partsassembly b
(cost=0.00..10.02 rows=2 width=8) (actual time=0.012..0.012 rows=0
loops=699)"
" Index Cond: (("outer".partid = b.partid) AND (b.assemblyid =
"outer".assemblyid))"
"Total runtime: 8368.708 ms"
(Without OR)
"Sort (cost=1251.95..1251.95 rows=1 width=16) (actual time=634.110..634.333
rows=62 loops=1)"
" Sort Key: c.partid, c.leadstateid"
" -> Nested Loop (cost=9.71..1251.94 rows=1 width=16) (actual
time=3.455..633.817 rows=62 loops=1)"
"-> Hash Join (cost=9.71..1168.03 rows=9 width=24) (actual
time=3.428..370.329 rows=16405 loops=1)"
" Hash Cond: ("outer".assemblyid = "inner".assemblyid)"
" -> Nested Loop (cost=0.00..1154.62 rows=368 width=20)
(actual time=0.080..235.833 rows=16472 loops=1)"
"-> Nested Loop (cost=0.00..44.16 rows=1 width=16)
(actual time=0.053..25.756 rows=699 loops=1)"
" -> Index Scan using ownerids on stock e
(cost=0.00..26.13 rows=3 width=8) (actual time=0.023..4.123 rows=793
loops=1)"
"Index Cond: (ownerid = 1)"
"Filter: (stock > 0)"
" -> Index Scan using manufacturerpartpn_pkey on
manufacturerpartpn c (cost=0.00..6.00 rows=1 width=12) (actual
time=0.010..0.013 rows=1 loops=793)"
"Index Cond: ("outer".pnid = c.pnid)"
"Filter: ((leadstateid = 2) OR (leadstateid
= 3) OR (leadstateid = 4))"
"-> Index Scan using partidpa on partsassembly b
(cost=0.00..1105.87 rows=368 width=8) (actual time=0.011..0.115 rows=24
loops=699)"
" Index Cond: ("outer".partid = b.partid)"
" -> Hash (cost=9.19..9.19 rows=210 width=4) (actual
time=3.324..3.324 rows=0 loops=1)"
"-> Seq Scan on assemblies d (cost=0.00..9.19 rows=210
width=4) (actual time=0.014..1.735 rows=417 loops=1)"
" Filter: (NOT leadfree)"
"-> Index Scan using ix_080c8ff0_5017_42a2_a174_28095b85106e_ on
assembliesbatch a (cost=0.00..9.31 rows=1 width=4) (actual
time=0.009..0
Re: [GENERAL] update functions locking tables
30 Aug 2005 09:10:51 -0400, Greg Stark <[EMAIL PROTECTED]>: > > I think truncate takes a table lock. > Just change it to "delete from times_producao". Thanks, i will try it. > > Also, if consider doing a "vacuum full" or "cluster" after the batch job to > clear up the free space (not in a large transaction). That will still take a > table lock but it may be a small enough downtime to be worth the speed > increase the rest of the day. > I'm already doing a vacuum (not full) once a day. A vacuum full or a cluster is totally out of reach since each take about one hour. The biggest table is 170 million rows long. Regards, Clodoaldo Pinto ---(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] psql from Linux script
Bernard <[EMAIL PROTECTED]> writes: > The postgresql.org server is the only braindead list server I have > seen so far. Well, welcome to the real Internet. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] update functions locking tables
Clodoaldo Pinto <[EMAIL PROTECTED]> writes: > 2005/8/29, Tom Lane <[EMAIL PROTECTED]>: > > > > What is the function doing to the table, exactly? DDL changes generally > > take exclusive locks ... > > This is the transaction: > > begin; > select update_last_date(); > truncate times_producao; I think truncate takes a table lock. Just change it to "delete from times_producao". Also, if consider doing a "vacuum full" or "cluster" after the batch job to clear up the free space (not in a large transaction). That will still take a table lock but it may be a small enough downtime to be worth the speed increase the rest of the day. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 8.1beta1 RPMs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, PostgreSQL RPM Building Project (http://pgfoundry.org/projects/pgsqlrpms) has built RPMs for Red Hat Linux 9, Red Hat Enterprise Linux 3.0 and 4. More may come later: http://developer.postgresql.org/~devrim/rpms/8.1/beta1/rpms/ We hope these RPMs will help more people to test this new great release of PostgreSQL. Please take care of the usual upgrade notes. Regards, - -- Devrim GUNDUZ Kivi Bilişim Teknolojileri - http://www.kivi.com.tr devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFDFFrPtl86P3SPfQ4RAoF7AJ45CEWe4+4pXB+mRPM7B4dC8550ywCfZu+N ufwmAXUFAo0tDXh1RcMtmkg= =MscP -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] About dropped notifications
CSN <[EMAIL PROTECTED]> writes: > I'm considering setting up a script that listens for > notifications for a table and if a row is deleted the > script will delete that row's corresponding files. One way to deal with this would be to have a boolean flag in the table like "deleted". Update that flag to true, and have a partial index "where deleted". Then your daemon can quickly query "select file_name where deleted", process the files and actually complete the deletion. All your other queries need to test "where not deleted" or go through a view with a clause like that. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] update functions locking tables
2005/8/30, Michael Fuhr <[EMAIL PROTECTED]>: > > TRUNCATE acquires an AccessExclusiveLock, which conflicts with all > other lock types. Locks are held until the transaction completes, > so once this lock is acquired no other transactions will be able > to access the table until this transaction commits or rolls back. > > DELETE is slower than TRUNCATE but it won't block readers in other > transactions. > I think it is of great help. I will change it and let you know what happened. Regards, Clodoaldo Pinto ---(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] update functions locking tables
On Tue, Aug 30, 2005 at 08:39:52AM -0300, Clodoaldo Pinto wrote: > > begin; > select update_last_date(); > truncate times_producao; TRUNCATE acquires an AccessExclusiveLock, which conflicts with all other lock types. Locks are held until the transaction completes, so once this lock is acquired no other transactions will be able to access the table until this transaction commits or rolls back. DELETE is slower than TRUNCATE but it won't block readers in other transactions. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Planner create a slow plan without an available index
On Tue, Aug 30, 2005 at 11:25:26 +0200, Ben-Nes Yonatan <[EMAIL PROTECTED]> wrote: > > If btree index is not suitable for this query then which index is? as > far as I understand the rtree index doesnt support range checks and the > hash index is not recommended by almost everyone (including the manual) > so the only one left is the gist, is that the most suitable index for > this query? if so can you give me a link as to where I can learn how to > use such an index efficently? (by the way the only link that worked at > the postgresql manual "Chapter 48. GiST Indexes" is the one which direct > to "the University of California at Berkeley's GiST Indexing Project web > site" the other 2 links direct to 404 pages and I guess that they should > be removed). rtree indexes allow you to quickly check for containment. Range checking is one dimensional containment. ---(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] Select gives the wrong results
> Crystle Numan wrote:
>> Dear all:
>>
>> I am fairly knowledgeable about PostgreSQL but this behaviour is
>> stumping me. Any help would be wonderful. If you think it is a bug,
>> let me now and I'll file one.
>>
>> (select values in DB (date stamps) between Jan 1, 2000 and Jan 1,
>> 2005, no results)
>>
>> db_name=# SELECT * from person_detail WHERE field='2' AND
>> value>'946702800' AND value<'1104555600';
>> id | person | field | value
>> ++---+---
>> (0 rows)
>>
>> (select values in DB (date stamps) between Jan 1, 2003 and Jan 1,
>> 2005, 4 results (!))
>>
>> db_name=# SELECT * from person_detail WHERE field='2' AND
>> value>'1041397200' AND value<'1104555600';
>> id | person | field | value
>> --++---+
>> 1187 |454 | 2 | 1051156800
>> 1188 |460 | 2 | 1053316800
>> 1219 |472 | 2 | 1057723200
>> 1181 |441 | 2 | 1042520400
>> (4 rows)
>>
>> The first select should have those 4 results plus any more. We tried
>> putting quotes (") around the word 'value' to see if that made a
>> difference, and no it didn't. We tried reversing the two clauses and
>> that made no difference.
>>
>> Here's another funny one. Not the one that doesn't belong.
>>
>> db_name=# SELECT * from person_detail WHERE field='2' AND
>> value>='11' AND value<='1104555600';
>> id | person | field | value
>> --++---+
>> 3 |218 | 2 | 1017464400
>> 253 |295 | 2 | 1002340800
>> 514 |323 | 2 | 100155600
>> 1126 |405 | 2 | 1006750800
>> 1179 |439 | 2 | 1035172800
>> 1187 |454 | 2 | 1051156800
>> 1188 |460 | 2 | 1053316800
>> 1219 |472 | 2 | 1057723200
>> 1181 |441 | 2 | 1042520400
>> 1152 |434 | 2 | 1032321600
>> 1129 |410 | 2 | 1024027200
>> (11 rows)
>>
>> Anyone see what's going on here?
>>
>> Thanks!
>> Crystle
>>
>>
>>
>http://archives.postgresql.org
Assuming that value is epoch date,
$ psql test -c "select date(1051156800)"
date
2003-04-24
(1 row)
$ psql test -c "select date(1053316800)"
date
2003-05-19
(1 row)
$ psql test -c "select date(0)"
date
1969-12-31
(1 row)
$ psql test -c "select date(86400)"
date
1970-01-01
(1 row)
Then wouldn't something along the lines of:
SELECT * from person_detail WHERE field='2' AND value between
date(11) and date(1104555600); work
reid
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Resore PG-Data from Files after crash
THX for Help, but it seems that there is no way get it fixed ... So long ... Christian ---(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] revoke on database not working as expected
On Mon, Aug 29, 2005 at 03:07:59PM -0400, Tom Lane wrote: > Stijn Hoop <[EMAIL PROTECTED]> writes: > > template1=# revoke all on database privtest from testpriv; > > That doesn't do what you evidently think it does --- it revokes the > right to create temp tables, and the right to create new schemas, but > not every right in existence. Please read the GRANT/REVOKE manual > pages. (Hint: revoking CREATE on the public schema would get you closer > to what you want.) > > regards, tom lane Thanks for answering! I assure you that I read those pages. In fact, quoted from: http://www.postgresql.org/docs/8.0/interactive/sql-grant.html "Depending on the type of object, the initial default privileges may include granting some privileges to PUBLIC. The default is no public access for tables, schemas, and tablespaces;" This misled me greatly. Apparently this is only for explicitly created schema's? Anyway, I found out about psql's \dn+, and that in combination with your hint was enough: %%% You are now connected to database "privtest" as user "stijn". privtest=# revoke create on schema public from public; REVOKE privtest=# \c privtest testpriv Password: You are now connected to database "privtest" as user "testpriv". privtest=> create table plover (i varchar(40)); ERROR: permission denied for schema public %%% which is what I was after. Many thanks! Might I suggest a hint in this direction somewhere in the text of REVOKE and GRANT? --Stijn -- It's harder to read code than to write it. -- Joel Spolsky, http://www.joelonsoftware.com/articles/fog69.html pgp6wl2J2F5Ra.pgp Description: PGP signature
Re: [GENERAL] psql from Linux script
Dear Marko on the Postgresql Mailing List A whitelist based spam filtering system is simple to understand. postgresql.org emails get through. Individual member emails may not get through. In contrast to what you write, it is actually the list server that is braindead because it creates messages that have: - A "To" header value of pgsql-general@postgresql.org - A "From" header value of the individual sender. So when we hit the reply button of our email client then we get the individual sender where in fact we would prefer to get the list address [EMAIL PROTECTED] The list server should, if it was reasonably functional, add a "Reply-to" header with its own address. I have subscribed to quite a few mailing lists before, and naturally the first thing is to add the list server to the white list before I even subscribe. The postgresql.org server is the only braindead list server I have seen so far. On Tue, 30 Aug 2005 11:24:45 +0300, marko wrote: >[This guy has prove-you-arent-bot filtering] > >On Tue, Aug 30, 2005 at 07:04:52PM +1200, Bernard wrote: >> I would appreciate your help very much. > >Unless you turn off your braindead spam-filtering, you are not worth it. It does matter to me how people value me in the context of my work. However I have a job to do and it will get done anyway. Meeting friendly people makes it a lot easier, and it appears that there are plenty of friendly contributors on this list. Try to learn from these friendly people instead of teaching me your questionable values. > >Sorry, but you are asking help on a public list, think about it a bit... Regards Bernard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] temp_buffers
On Tue, Aug 30, 2005 at 10:14:04AM +0200, Hannes Dorbath wrote: > Can someone give me a little info on what this setting does in 8.1 beta? To learn more about any configuration setting, see "Run-time Configuration" in the "Server Run-time Environment" chapter of the documentation. Here's a link to the beta documentation: http://developer.postgresql.org/docs/postgres/runtime-config.html -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] update functions locking tables
2005/8/29, Tom Lane <[EMAIL PROTECTED]>: > > What is the function doing to the table, exactly? DDL changes generally > take exclusive locks ... This is the transaction: begin; select update_last_date(); truncate times_producao; select kstime(), insert_times_producao(), kstime(); select kstime(), update_ranking_times(), kstime(); truncate usuarios_producao; select kstime(), insert_usuarios_producao(), kstime(); analyze usuarios_producao; select kstime(), update_ranking_usuarios(), kstime(); select kstime(), update_ranking_usuarios_time(), kstime(); select kstime(), update_team_active_members(), kstime(); commit; This is one of the functions: CREATE OR REPLACE FUNCTION update_ranking_usuarios() RETURNS void AS $BODY$declare linha record; rank integer; begin rank := 0; for linha in select usuario from usuarios_producao order by pontos_0 desc, pontos_7 desc, pontos_24 desc loop rank := rank + 1; update usuarios_producao set rank_0 = rank where usuario = linha.usuario ; end loop; -- -- rank := 0; for linha in select usuario from usuarios_producao order by pontos_0 + (pontos_7 / 7) desc, pontos_0 desc loop rank := rank + 1; update usuarios_producao set rank_24 = rank where usuario = linha.usuario ; end loop; -- -- rank := 0; for linha in select usuario from usuarios_producao order by pontos_0 + pontos_7 desc, pontos_0 desc loop rank := rank + 1; update usuarios_producao set rank_7 = rank where usuario = linha.usuario ; end loop; -- -- rank := 0; for linha in select usuario from usuarios_producao order by pontos_0 + (pontos_7 * 30 / 7) desc, pontos_0 desc loop rank := rank + 1; update usuarios_producao set rank_30 = rank where usuario = linha.usuario ; end loop; return; end;$BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT; There is no DDL inside the functions. Regards, Clodoaldo Pinto ---(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] Cursor declaration
How do I declare a cursor in a stored procedure if that cursor contains a WHERE which depends on the argument given to that stored procedure? I can't get passed this message: 'Cannot mix placeholder styles "$1" and ":foo"' The message doesn't give a useful line number, so I don't even know which line it's complaining about. -Nigel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Select gives the wrong results
On Mon, 2005-08-29 at 23:42 +0200, Martijn van Oosterhout wrote: > On Mon, Aug 29, 2005 at 04:48:49PM -0400, Crystle Numan wrote: > > Dear all: > > > > I am fairly knowledgeable about PostgreSQL but this behaviour is > > stumping me. Any help would be wonderful. If you think it is a bug, let > > me now and I'll file one. > > > > (select values in DB (date stamps) between Jan 1, 2000 and Jan 1, 2005, > > no results) > > > > Looks to me like "value" is a string type, is this possible? > > ORDER BY value should make it more obvious. I think you are correct, that because the field is a string the comparison is not working as I expected. Unfortunately, the way we set up the database, I can't change the type to int as other values in this 'value' field contain letters. I'll have to find another way to get the proper comparison. Thanks to all who replied! Crystle -- Crystle Numan, B.Sc., Web Developer Guided Vision: the possibilities are endless 905.528.3095 http://guidedvision.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] update functions locking tables
2005/8/29, Michael Fuhr <[EMAIL PROTECTED]>: > > In general, writers shouldn't block readers. Have you examined > pg_locks? Do you know exactly what the blocked queries are, or can > you find out from pg_stat_activity (stats_command_string must be > enabled)? Are you doing any explicit locking (LOCK statement)? > This is one of the blocked queries: select count (*) from times_producao where pontos_0 - pontos_7 > 0; These selects were done during the updating: select * from pg_locks; relation | database | transaction | pid |mode | granted --+--+-+---+-+- 1813938 | 1813868 | | 7040 | AccessShareLock | t 1813938 | 1813868 | | 7040 | RowExclusiveLock| t 1813938 | 1813868 | | 7040 | ShareLock | t 1813938 | 1813868 | | 7040 | AccessExclusiveLock | t 1813939 | 1813868 | | 7040 | AccessShareLock | t 1813939 | 1813868 | | 7040 | RowExclusiveLock| t 1813939 | 1813868 | | 7040 | ShareLock | t 1813939 | 1813868 | | 7040 | AccessExclusiveLock | t 1813914 | 1813868 | | 24012 | AccessShareLock | f 1813892 | 1813868 | | 7040 | AccessShareLock | t 1813892 | 1813868 | | 7040 | RowExclusiveLock| t 1813914 | 1813868 | | 7040 | AccessShareLock | t 1813914 | 1813868 | | 7040 | RowExclusiveLock| t 1813914 | 1813868 | | 7040 | ShareLock | t 1813914 | 1813868 | | 7040 | AccessExclusiveLock | t 1813896 | 1813868 | | 7040 | AccessShareLock | t 16839 | 1813868 | | 12751 | AccessShareLock | t 2314110 | 1813868 | | 26871 | AccessShareLock | f 1813914 | 1813868 | | 26844 | AccessShareLock | f | | 288553 | 26844 | ExclusiveLock | t | | 288561 | 24012 | ExclusiveLock | t | | 288548 | 7040 | ExclusiveLock | t | | 288558 | 26871 | ExclusiveLock | t 1813914 | 1813868 | | 31212 | AccessShareLock | f 2314110 | 1813868 | | 7040 | AccessShareLock | t 2314110 | 1813868 | | 7040 | RowExclusiveLock| t 2314110 | 1813868 | | 7040 | ShareLock | t 2314110 | 1813868 | | 7040 | AccessExclusiveLock | t | | 288556 | 31212 | ExclusiveLock | t | | 288562 | 12751 | ExclusiveLock | t 1813887 | 1813868 | | 7040 | AccessShareLock | t 2314112 | 1813868 | | 7040 | ShareLock | t 2314112 | 1813868 | | 7040 | AccessExclusiveLock | t 1813907 | 1813868 | | 7040 | AccessShareLock | t 1813911 | 1813868 | | 7040 | AccessShareLock | t (35 rows) select * from pg_stat_user_tables as a inner join pg_locks as b on a.relid = b.relation ; relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | relation | database | transaction | pid |mode | granted -++---+--+--+--+---+---+---+---+--+--+-+---+-+- 1813914 | public | usuarios_producao |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813914 | 1813868 || 24012 | AccessShareLock | f 1813892 | public | last_date |0 |0 | | | 0 | 0 | 0 | 1813892 | 1813868 || 7040 | AccessShareLock | t 1813892 | public | last_date |0 |0 | | | 0 | 0 | 0 | 1813892 | 1813868 || 7040 | RowExclusiveLock| t 1813914 | public | usuarios_producao |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813914 | 1813868 || 7040 | AccessShareLock | t 1813914 | public | usuarios_producao |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813914 | 1813868 || 7040 | RowExclusiveLock| t 1813914 | public | usuarios_producao |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813914 | 1813868 || 7040 | ShareLock | t 1813914 | public | usuarios_producao |0 |0 | 0 | 0 | 0 | 0 | 0 | 1813914 | 1813868 || 7040 | AccessExclusiveLock | t 1813896 | public | times |
Re: [GENERAL] Select gives the wrong results
Crystle Numan wrote: Dear all: I am fairly knowledgeable about PostgreSQL but this behaviour is stumping me. Any help would be wonderful. If you think it is a bug, let me now and I'll file one. (select values in DB (date stamps) between Jan 1, 2000 and Jan 1, 2005, no results) db_name=# SELECT * from person_detail WHERE field='2' AND value>'946702800' AND value<'1104555600'; id | person | field | value ++---+--- (0 rows) You are comparing strings, which is not quite the same as a numerical comparison. The above range is empty, as '9...' > '1...' (even though the left string is shorter). Either compare numbers, or left pad your left string with zeroes until it's the same length as the right string. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World// ---(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] psql from Linux script
Bernard wrote: > So when we hit the reply button of our email client then we get the > individual sender where in fact we would prefer to get the list > address [EMAIL PROTECTED] No, we would not prefer that. If you write to me, then my reply goes to you, no matter by what means your message was conveyed to me. > The list server should, if it > was reasonably functional, add a "Reply-to" header with its own > address. If you would like a different reply behavior activated on your posts, then you are free to add a Mail-Followup-To header to your emails. ---(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] psql from Linux script
On Tue, Aug 30, 2005 at 09:25:07PM +1200, Bernard wrote: > Dear Marko on the Postgresql Mailing List > > A whitelist based spam filtering system is simple to understand. > postgresql.org emails get through. Individual member emails may not > get through. > > In contrast to what you write, it is actually the list server that is > braindead because it creates messages that have: > > - A "To" header value of pgsql-general@postgresql.org > - A "From" header value of the individual sender. No, it forwards the message exactly as you sent it. If you want replies to your message to be sent only to the group use the Reply-to or Mail-followup-to header. See, my message says reply to me and the mail server should not fiddle with that. Search the web, some people say that mailing lists should never fiddle with email headers, others say they should rewrite them completely. We are not going to have this discussion here again, the archives are already full of it. It is a choice and for this group this is the better choice. Read the archives for details. > The postgresql.org server is the only braindead list server I have > seen so far. Then you have not seen many. Most of the ones I'm on do it this way. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpX6WJLI1G1R.pgp Description: PGP signature
Re: [GENERAL] psql from Linux script
On Tue, Aug 30, 2005 at 09:25:07PM +1200, Bernard wrote: > The postgresql.org server is the only braindead list server I have > seen so far. http://www.unicom.com/pw/reply-to-harmful.html It is the rule on technical mailing lists. -- marko ---(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] Planner create a slow plan without an available index
On Tue, Aug 30, 2005 at 11:25:26AM +0200, Ben-Nes Yonatan wrote: > Tom Lane wrote: > >However ... this query is basically going to suck with any btree index, > >because btree can't usefully do range checks on two separate variables. > >There's an exactly similar problem being discussed over in pgsql-novice: > >http://archives.postgresql.org/pgsql-novice/2005-08/msg00243.php > > If btree index is not suitable for this query then which index is? as > far as I understand the rtree index doesnt support range checks and the > hash index is not recommended by almost everyone (including the manual) > so the only one left is the gist, is that the most suitable index for > this query? if so can you give me a link as to where I can learn how to > use such an index efficently? (by the way the only link that worked at > the postgresql manual "Chapter 48. GiST Indexes" is the one which direct > to "the University of California at Berkeley's GiST Indexing Project web > site" the other 2 links direct to 404 pages and I guess that they should > be removed). Basically, no index is really setup for the query as you wrote it. Indexes generally improve performance by taking advantage of order. You have two constants (the two subqueries) and two variables (left and right). Andyou applying range range (not equality) to each one. There is no way to order an index that would give the answer you want. rtree works on multidimesional (geometric) data. It can do range tests (is object A to the left of object B) but it's only applicable if your conditions can be interpreted that way. GiST is for creating custom index types, hardly likely to be useful in your case. I can't tell from your query (and PostgreSQL certainly can't) but are there other constraints such left <= right or something similar for the constants. If so, maybe adding that will help PostgreSQL optimise your query. Maybe indexing on (left+right) might work for you but it all depends on the structure of your data. If you want more help, you're going to need to provide information on your database including what left, right and items actually are. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgp3D2kueRl5i.pgp Description: PGP signature
Re: [GENERAL] psql from Linux script
Hi Bernard, I just ran a test on Solaris... The following syntax is working for me : su - postgres -c "psql template1 -U postgres -c \"ALTER USER postgres WITH PASSWORD 'newpassword';\"" If it can help you... --- Patrick Fiche email : [EMAIL PROTECTED] tel : 01 69 29 36 18 --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Bernard Sent: mardi 30 aout 2005 09:05 To: pgsql-general@postgresql.org Subject: [GENERAL] psql from Linux script Dear Postgresql Specialists I am failing to update the password of the postgresql user from within a Linux installation script run by root: # su - postgres -c echo "ALTER USER postgres WITH PASSWORD 'newpassword';" | psql -U postgres template1 psql: FATAL: Ident authentication failed for user "postgres" In contrast, there is no problem with the same command issued in psql interactively: # su postgres $ psql template1 template1=# ALTER USER postgres WITH PASSWORD 'newpassword'; ALTER USER \q $ exit # The client authentication configuration file pg_hba.conf is in its original state. I would appreciate your help very much. Thanks. Bernard ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] psql from Linux script
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 30 Aug 2005, Marko Kreen wrote: I am failing to update the password of the postgresql user from within a Linux installation script run by root: # su - postgres -c echo "ALTER USER postgres WITH PASSWORD 'newpassword';" | psql -U postgres template1 psql: FATAL: Ident authentication failed for user "postgres" I do not think the psql is ran as user postgres. Yes, this should be rewritten as: echo "ALTER USER postgres WITH PASSWORD 'newpassword';" |su -l \ postgres -c "psql template1" Regards, - -- Devrim GUNDUZ Kivi Bilişim Teknolojileri - http://www.kivi.com.tr devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFDFBkBtl86P3SPfQ4RApBiAKDJY0HlXZSExl+9zXv1Q/bUL6tQAgCbBnBZ vUxKGhAVOAFA2ia9OsBxdHc= =1zxD -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] psql from Linux script
[This guy has prove-you-arent-bot filtering] On Tue, Aug 30, 2005 at 07:04:52PM +1200, Bernard wrote: > I would appreciate your help very much. Unless you turn off your braindead spam-filtering, you are not worth it. Sorry, but you are asking help on a public list, think about it a bit... -- marko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Planner create a slow plan without an available index
Tom Lane wrote: Ben-Nes Yonatan <[EMAIL PROTECTED]> writes: Indexes: "items_items_id_key" UNIQUE, btree (items_id) "items_left" btree (left) "items_left_right" btree (left, right) You could get rid of the items_left index --- it's redundant with the first column of the combined index anyway. bh.com=# EXPLAIN ANALYZE SELECT * FROM items WHERE left>=(SELECT left FROM category WHERE category_id=821) AND right<=(SELECT right FROM category WHERE category_id=821) OFFSET 24 LIMIT 13; Doing OFFSET/LIMIT without an ORDER BY is just asking for trouble. If you were to specify "ORDER BY left, right" that would probably convince the planner to use the index you want. However ... this query is basically going to suck with any btree index, because btree can't usefully do range checks on two separate variables. There's an exactly similar problem being discussed over in pgsql-novice: http://archives.postgresql.org/pgsql-novice/2005-08/msg00243.php regards, tom lane First of all thanks I did succed to use the index that way and to receive less then 80ms responds, but if imporvement is possible I would like to do it. If btree index is not suitable for this query then which index is? as far as I understand the rtree index doesnt support range checks and the hash index is not recommended by almost everyone (including the manual) so the only one left is the gist, is that the most suitable index for this query? if so can you give me a link as to where I can learn how to use such an index efficently? (by the way the only link that worked at the postgresql manual "Chapter 48. GiST Indexes" is the one which direct to "the University of California at Berkeley's GiST Indexing Project web site" the other 2 links direct to 404 pages and I guess that they should be removed). Thanks alot, Ben-Nes Yonatan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] psql from Linux script
On Tue, Aug 30, 2005 at 07:04:52PM +1200, Bernard wrote: > Dear Postgresql Specialists > > I am failing to update the password of the postgresql user from within > a Linux installation script run by root: > > # su - postgres -c echo "ALTER USER postgres WITH PASSWORD > 'newpassword';" | psql -U postgres template1 > psql: FATAL: Ident authentication failed for user "postgres" I do not think the psql is ran as user postgres. > > In contrast, there is no problem with the same command issued in psql > interactively: > > # su postgres > $ psql template1 > template1=# ALTER USER postgres WITH PASSWORD 'newpassword'; > ALTER USER > \q > $ exit > # -- marko ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] temp_buffers
Can someone give me a little info on what this setting does in 8.1 beta? Thanks ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] psql from Linux script
Dear Postgresql Specialists I am failing to update the password of the postgresql user from within a Linux installation script run by root: # su - postgres -c echo "ALTER USER postgres WITH PASSWORD 'newpassword';" | psql -U postgres template1 psql: FATAL: Ident authentication failed for user "postgres" In contrast, there is no problem with the same command issued in psql interactively: # su postgres $ psql template1 template1=# ALTER USER postgres WITH PASSWORD 'newpassword'; ALTER USER \q $ exit # The client authentication configuration file pg_hba.conf is in its original state. I would appreciate your help very much. Thanks. Bernard ---(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
