Re: [GENERAL] Fractions of seconds in timestamps
On 2012-04-24, rihad wrote: > As PostgreSQL stores timestamps with a fractional part, does it mean that > WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:59' might miss > records with values of f equal to 23:59:59.1234 or so? yes, it does. BETWEEN doesn't work well for timestamps. you have to do it the long way f >= '2012-04-23 00:00:00' AND f < '2012-04-24 00:00:00' -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fractions of seconds in timestamps
What about using WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-24 00:00:00'? On 04/25/2012 09:52 AM, Jasen Betts wrote: On 2012-04-24, rihad wrote: As PostgreSQL stores timestamps with a fractional part, does it mean that WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:59' might miss records with values of f equal to 23:59:59.1234 or so? yes, it does. BETWEEN doesn't work well for timestamps. you have to do it the long way f>= '2012-04-23 00:00:00' AND f< '2012-04-24 00:00:00'
Re: [GENERAL] how robust are custom dumps?
On Tue, Apr 24, 2012 at 10:04 PM, Thom Brown wrote: > What was the experience? Is it possible you had specified a > compression level without the format set to custom? That would result > in a plain text output within a gzip file, which would then error out > if you tried to restore it with pg_restore, but would be perfectly > valid if you passed the uncompressed output directly into psql. > yes, probably. I remember that it was a binary file, but i didn't know about the possibility of gzip in pg_dump. Possibly the 2 GB size limit for a FAT partition was exceeded, but that would have resulted in an error, so i would have known. i think it's time to restore my trust in the custom dumps. :) i do have one suggestion. pg_restore only gives a user this feedback, when he makes this mistake:"pg_restore: [archiver] input file does not appear to be a valid archive". Would it be feasible for pg_restore to detect that it is a different pg_dump format and inform the user about it? Cheers, WB -- "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
Re: [GENERAL] how robust are custom dumps?
On Wed, Apr 25, 2012 at 09:42, Willy-Bas Loos wrote: > On Tue, Apr 24, 2012 at 10:04 PM, Thom Brown wrote: >> >> What was the experience? Is it possible you had specified a >> compression level without the format set to custom? That would result >> in a plain text output within a gzip file, which would then error out >> if you tried to restore it with pg_restore, but would be perfectly >> valid if you passed the uncompressed output directly into psql. > > > yes, probably. I remember that it was a binary file, but i didn't know about > the possibility of gzip in pg_dump. > Possibly the 2 GB size limit for a FAT partition was exceeded, but that > would have resulted in an error, so i would have known. We used to have a bug/lackoffeature in pg_dump at the 2GB boundary as well, IIRC, specifically on Win32. Maybe you were hit by that one.. > i think it's time to restore my trust in the custom dumps. :) Yes. > i do have one suggestion. > pg_restore only gives a user this feedback, when he makes this > mistake:"pg_restore: [archiver] input file does not appear to be a valid > archive". > > Would it be feasible for pg_restore to detect that it is a different pg_dump > format and inform the user about it? The main one you'd want to detect is plain I think - and I don't know if we can reliably detect that. It could be just a generic textfile, after all - how would we know the difference? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory
Stacking views is a bad practice. It usually means that you are making the db do a lot of unnecessary work, scanning tables more than once when you don't even need them. According to your description, you have 3 layers of views on partitioned tables. I can imagine that that leaves the planner with a lot of possible query plans, a lot of interaction and a lot of statistics to read. do you have any special settings for the statistics on these tables? and could you please post the non-default settings in your postgresql.conf file? $ grep ^[^#] /etc/postgresql/9.1/main/postgresql.conf|grep -e ^[^[:space:]] Would be helpful to see if you have any statistics or planner stuff altered. Cheers, WBL On Wed, Apr 25, 2012 at 8:18 AM, Toby Corkindale < toby.corkind...@strategicdata.com.au> wrote: > Hi, > I'm hitting some peculiar behaviour. I'm currently on Pg 9.1.3 on a 64bit > Debian system. > > I have a database which is moderately large - 20 GByte or so - and > contains that data split up over dozens of tables, which are themselves > partitioned. > Queries are usually only run against fairly small, partitioned, sets of > data. > > These queries generally run fairly fast. Performance is not a problem. > > However Postgres is chewing up huge amounts of memory just to create the > query plan! > > For example, even if I just run > EXPLAIN SELECT a_column FROM a_view > WHERE partition_id = 1; > > Then the postgres backend process takes several seconds to return, and in > the worst example here, is hogging more than 3Gbyte once it comes back. (It > doesn't free that up until you close the connection) > > The query plan that comes back does seem quite convoluted, but then, the > view is a query run over about eight other views, each of which is pulling > data from a few other views. The actual underlying data being touched is > only *a few dozen* small rows. > > As I said, the query runs fast enough.. however we only need a handful of > these queries to get run in separate connections, and the database server > will be exhausted of memory. Especially since the memory isn't returned > until the end of the connection, yet these connections typically stay up > for a while. > > I wondered if there's anything I can do to reduce this memory usage? And, > is this a bug? > > I've posted the output of the query plan here: > https://gist.github.com/2487097 > > > Thanks in advance, > Toby > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
Re: [GENERAL] how to make an SQL UPDATE from record returning function
On Tue, 2012-04-24 at 12:10 +0200, Thomas Kellerer wrote: > Rafal Pietrak, 24.04.2012 09:02: > > > > is not an option, since the function is *very* expensive (multiple join > > of large tables - inventories, history, etc). > > > > Is there a syntax workaround that I could possibly use to get the effect > > of launching my_function just once? > > With 9.1 you could probably achieve this using a writeable CTE. > > Although I have to admit I don't really understand what your function is > returning. > Does the function return a result set or scalar values? > Basically, it's at shopping cart update. It ranks updated an item with costommer rating of that item with respect to other orders and current stock (so that when the cart is only presented, the values are there, precomputted on update). There is more then one value to precompute, so the function returns a set of values. (earlier I only needed to precompute just one value and everything was so easy then... :). Could you pls give me an example of how the "writeable CTE"? This does not ring a bell here. -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to group by similarity ?
Andreas writes: > How would I group the table so that it shows groups that have > similarity () > x ? > > Lets say the table looks like this: > > id, txt > 1, aa1 > 2, bb1 > 3, cc1 > 4, bb2 > 5, bb3 > 6, aa2 > ... > > How would a select look like that shows: > > id, txt, group_id > 1, aa1, 1, > 6, aa2, 1, > 2, bb1, 2, > 4, bb2, 2, > 5, bb3, 2, > 3, cc1, 3 I could only come up with this convoluted query: WITH grp (t1, id, t2) AS ( SELECT t1.txt, t1.id, t2.txt FROM tbl t1 LEFT JOIN tbl t2 ON t2.txt > t1.txt WHERE t2.txt IS NULL OR similarity(t1.txt, t2.txt) > 0 ) SELECT t1, min(id) FROM ( SELECT t1, id FROM grp UNION ALL SELECT t2, id FROM grp WHERE t2 IS NOT NULL ) dummy GROUP BY t1 ORDER BY t1 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to group by similarity ?
Andreas writes: > How would I group the table so that it shows groups that have > similarity () > x ? > > Lets say the table looks like this: > > id, txt > 1, aa1 > 2, bb1 > 3, cc1 > 4, bb2 > 5, bb3 > 6, aa2 > ... > > How would a select look like that shows: > > id, txt, group_id > 1, aa1, 1, > 6, aa2, 1, > 2, bb1, 2, > 4, bb2, 2, > 5, bb3, 2, > 3, cc1, 3 The following query will do that, but it's convoluted: WITH grp (t1, id, t2) AS ( SELECT t1.txt, t1.id, t2.txt FROM tbt t1 LEFT JOIN tbt t2 ON t2.txt > t1.txt WHERE t2.txt IS NULL OR similarity(t1.txt, t2.txt) > 0 ) SELECT t1, min(id) FROM ( SELECT t1, id FROM grp UNION ALL SELECT t2, id FROM grp WHERE t2 IS NOT NULL ) dummy GROUP BY t1 ORDER BY t1 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how robust are custom dumps?
On Wed, Apr 25, 2012 at 9:51 AM, Magnus Hagander wrote: > We used to have a bug/lackoffeature in pg_dump at the 2GB boundary as > well, IIRC, specifically on Win32. Maybe you were hit by that one.. Yes, possibly. I didn't even know how to make a compressed plain dump, but that doesn't really plea my case :/ > > i do have one suggestion. > > pg_restore only gives a user this feedback, when he makes this > > mistake:"pg_restore: [archiver] input file does not appear to be a valid > > archive". > > > > Would it be feasible for pg_restore to detect that it is a different > pg_dump > > format and inform the user about it? > > The main one you'd want to detect is plain I think - and I don't know > if we can reliably detect that. It could be just a generic textfile, > after all - how would we know the difference? > Well, on linux you could make pg_dump run /usr/bin/file on the file to see what kind it is. If it is gzipped, suggest that it might be a gzipped plain dump, if it is plain text, suggest that it might be a plain dump (etc, also bzip2). That's all. You don't have to be sure that it is valid, just say a bit more than "does not appear to be a valid archive". Help a user in a bad situation. Only, i know that postgres runs on many platforms, so you probably can't run /usr/bin/file on all of those (or might not be installed on linux machine). So it probably should be part of pg_restore itself. WBL -- "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory
Hi Willy-Bas, Thanks for your reply. I realise that stacking the views up like this complicates matters, but the actual views are fairly simple queries, and each one individually is only looking at a few dozen rows. (Eg. selecting min, max or average value from a small set, grouped by one column) >From the point of view of creating reporting queries, it's a nice and logical >way to build up a query, and we didn't think it would present any problems.. >and even on a well-populated database, the query runs very fast. It's just the >astounding amount of memory used that presents difficulties. Looking at the postgresql.conf for non-default settings, the notable ones are: max_connections = 200 ssl = false shared_buffers = 256MB max_prepared_transactions = 16 # although they aren't used for the group of queries in question maintenance_work_mem = 128MB # work_mem is left at default of 1MB effective_io_concurrency = 2 random_page_cost = 3.0 effective_cache_size = 512MB geqo = on geqo_threshold = 12 geqo_effort = 7 Some other things are non-default, like checkpoints, streaming-replication stuff, but those shouldn't have any effect. The memory settings (shared buffers, effective cache) might seem to be set quite conservatively at the moment, given the memory available in the machine -- but since we can exhaust that memory with just a few connections, it seems fair. Cheers, Toby - Original Message - From: "Willy-Bas Loos" To: "Toby Corkindale" Cc: "pgsql-general" Sent: Wednesday, 25 April, 2012 6:05:37 PM Subject: Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory Stacking views is a bad practice. It usually means that you are making the db do a lot of unnecessary work, scanning tables more than once when you don't even need them. According to your description, you have 3 layers of views on partitioned tables. I can imagine that that leaves the planner with a lot of possible query plans, a lot of interaction and a lot of statistics to read. do you have any special settings for the statistics on these tables? and could you please post the non-default settings in your postgresql.conf file? $ grep ^[^#] /etc/ postgresql /9.1/main/ postgresql . conf | grep -e ^[^[:space:]] Would be helpful to see if you have any statistics or planner stuff altered. Cheers, WBL On Wed, Apr 25, 2012 at 8:18 AM, Toby Corkindale < toby.corkind...@strategicdata.com.au > wrote: Hi, I'm hitting some peculiar behaviour. I'm currently on Pg 9.1.3 on a 64bit Debian system. I have a database which is moderately large - 20 GByte or so - and contains that data split up over dozens of tables, which are themselves partitioned. Queries are usually only run against fairly small, partitioned, sets of data. These queries generally run fairly fast. Performance is not a problem. However Postgres is chewing up huge amounts of memory just to create the query plan! For example, even if I just run EXPLAIN SELECT a_column FROM a_view WHERE partition_id = 1; Then the postgres backend process takes several seconds to return, and in the worst example here, is hogging more than 3Gbyte once it comes back. (It doesn't free that up until you close the connection) The query plan that comes back does seem quite convoluted, but then, the view is a query run over about eight other views, each of which is pulling data from a few other views. The actual underlying data being touched is only *a few dozen* small rows. As I said, the query runs fast enough.. however we only need a handful of these queries to get run in separate connections, and the database server will be exhausted of memory. Especially since the memory isn't returned until the end of the connection, yet these connections typically stay up for a while. I wondered if there's anything I can do to reduce this memory usage? And, is this a bug? I've posted the output of the query plan here: https://gist.github.com/2487097 Thanks in advance, Toby -- Sent via pgsql-general mailing list ( pgsql-general@postgresql.org ) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: FW: Really heart touching.........
Best Regards, Abbas On Wed, Apr 25, 2012 at 11:16 AM, Akhila Banu Rumi < akhilabanu_r...@infosys.com> wrote: > Really Heart touching … > > ** ** > > *From:* Amey Ratnakar Prabhu > *Posted At:* Wednesday, April 25, 2012 9:16 AM > *Posted To:* HYD General > *Conversation:* Really heart touching. > *Subject:* Really heart touching. > > ** ** > > My friend , unable to stop his tears, has confined himself to the restroom > and we are able to hear him cry loudly. > > Really full of sentiments and emotions. Early morning touch chesindi raaa* > *** > > ** ** > > *From:* Avinash Peravali > *Posted At:* Wednesday, April 25, 2012 8:58 AM > *Posted To:* HYD General > *Conversation:* Really heart touching. > *Subject:* Really heart touching. > > ** ** > > *Got as Forwarded…* > > * * > > Really heart touching. > * > I was walking around in a Big Bazaar store making shopping, when I saw > a Cashier talking to a boy couldn't have been more than 5 or 6 years > old.. > > > The Cashier said, 'I'm sorry, but you don't have enough money to buy > this doll. Then the little boy turned to me and asked: ''Uncle, are > you sure I don't have enough money?'' > > I counted his cash and replied: ''You know that you don't have enough > money to buy the doll, my dear.'' The little boy was still holding the > doll in his hand. > > Finally, I walked toward him and I asked him who he wished to give > this doll to. 'It's the doll that my sister loved most and wanted so > much . I wanted to Gift her for her BIRTHDAY. > > > I have to give the doll to my mommy so that she can give it to my > sister when she goes there.' His eyes were so sad while saying this. > 'My Sister has gone to be with God.. Daddy says that Mommy is going to > see God very soon too, so I thought that she could take the doll with > her to give it to my sister...'' > > My heart nearly stopped. The little boy looked up at me and said: 'I > told daddy to tell mommy not to go yet. I need her to wait until I > come back from the mall.' Then he showed me a very nice photo of him > where he was laughing. He then told me 'I want mommy to take my > picture with her so my sister won't forget me.' 'I love my mommy and I > wish she doesn't have to leave me, but daddy says that she has to go > to be with my little sister.' Then he looked again at the doll with > sad eyes, very quietly.. > > I quickly reached for my wallet and said to the boy. 'Suppose we check > again, just in case you do have enough money for the doll?'' > > 'OK' he said, 'I hope I do have enough.' I added some of my money to > his without him seeing and we started to count it. There was enough > for the doll and even some spare money. > > The little boy said: 'Thank you God for giving me enough money!' > > > Then he looked at me and added, 'I asked last night before I went to > sleep for God to make sure I had enough money to buy this doll, so > that mommy could give It to my sister. He heard me!'' 'I also wanted > to have enough money to buy a white rose for my mommy, but I didn't > dare to ask God for too much. But He gave me enough to buy the doll > and a white rose. My mommy loves white roses.' > > > I finished my shopping in a totally different state from when I > started. I couldn't get the little boy out of my mind. Then I > remembered a local > > newspaper article two days ago, which mentioned a drunk man in a > truck, who hit a car occupied by a young woman and a little girl. The > little girl died right away, and the mother was left in a critical > state. The family had to decide whether to pull the plug on the > life-sustaining machine, because the young woman would not be able to > recover from the coma. Was this the family of the little boy? > > Two days after this encounter with the little boy, I read in the news > paper that the young woman had passed away.. I couldn't stop myself as > I bought a bunch of white roses and I went to the funeral home where > the body of the young woman was exposed for people to see and make > last wishes before her burial. She was there, in her coffin, holding a > beautiful white rose in her hand with the photo of the little boy and > the doll placed over her chest. I left the place, teary-eyed, feeling > that my life had been changed forever... > > The love that the little boy had for his mother and his sister is still, > to this day, hard to imagine. And in a fraction of a second, a drunk driver > had taken all this away from him. > > **Please DO NOT DRINK & DRIVE. > ** > **Now you have 2 choices: > > 1) Forward this message, or > > 2) Ignore it as if it never touched your heart. > > For those who prefer to think that God is not watching over us go > ahead and delete this. For the rest of us. pass this on. > > The value of a man or woman resides in what he or she gives, not in what > they are capable of receiving.* > > ** ** > > CAUTION - Disclaimer * > This e-mail
Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory
would it be possible to reproduce the same query without using any views? you could see the difference in memory usage. if that doesn't explain, try also without inheritance, by using the ONLY keyword (and UNION ALL). If it's really only a couple of rows, you might as well post a dump somewhere? Then i could reproduce. WBL On Wed, Apr 25, 2012 at 10:51 AM, Toby Corkindale < toby.corkind...@strategicdata.com.au> wrote: > Hi Willy-Bas, > Thanks for your reply. > > I realise that stacking the views up like this complicates matters, but > the actual views are fairly simple queries, and each one individually is > only looking at a few dozen rows. (Eg. selecting min, max or average value > from a small set, grouped by one column) > From the point of view of creating reporting queries, it's a nice and > logical way to build up a query, and we didn't think it would present any > problems.. and even on a well-populated database, the query runs very fast. > It's just the astounding amount of memory used that presents difficulties. > > Looking at the postgresql.conf for non-default settings, the notable ones > are: > > max_connections = 200 > ssl = false > shared_buffers = 256MB > max_prepared_transactions = 16 > # although they aren't used for the group of queries in question > maintenance_work_mem = 128MB > # work_mem is left at default of 1MB > effective_io_concurrency = 2 > random_page_cost = 3.0 > effective_cache_size = 512MB > geqo = on > geqo_threshold = 12 > geqo_effort = 7 > > Some other things are non-default, like checkpoints, streaming-replication > stuff, but those shouldn't have any effect. > > The memory settings (shared buffers, effective cache) might seem to be set > quite conservatively at the moment, given the memory available in the > machine -- but since we can exhaust that memory with just a few > connections, it seems fair. > > Cheers, > Toby > > - Original Message - > From: "Willy-Bas Loos" > To: "Toby Corkindale" > Cc: "pgsql-general" > Sent: Wednesday, 25 April, 2012 6:05:37 PM > Subject: Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory > > > Stacking views is a bad practice. It usually means that you are making the > db do a lot of unnecessary work, scanning tables more than once when you > don't even need them. > According to your description, you have 3 layers of views on partitioned > tables. > I can imagine that that leaves the planner with a lot of possible query > plans, a lot of interaction and a lot of statistics to read. > > do you have any special settings for the statistics on these tables? > and could you please post the non-default settings in your postgresql.conf > file? > $ grep ^[^#] /etc/ postgresql /9.1/main/ postgresql . conf | grep -e > ^[^[:space:]] > > Would be helpful to see if you have any statistics or planner stuff > altered. > > Cheers, > > WBL > > > > On Wed, Apr 25, 2012 at 8:18 AM, Toby Corkindale < > toby.corkind...@strategicdata.com.au > wrote: > > > Hi, > I'm hitting some peculiar behaviour. I'm currently on Pg 9.1.3 on a 64bit > Debian system. > > I have a database which is moderately large - 20 GByte or so - and > contains that data split up over dozens of tables, which are themselves > partitioned. > Queries are usually only run against fairly small, partitioned, sets of > data. > > These queries generally run fairly fast. Performance is not a problem. > > However Postgres is chewing up huge amounts of memory just to create the > query plan! > > For example, even if I just run > EXPLAIN SELECT a_column FROM a_view > WHERE partition_id = 1; > > Then the postgres backend process takes several seconds to return, and in > the worst example here, is hogging more than 3Gbyte once it comes back. (It > doesn't free that up until you close the connection) > > The query plan that comes back does seem quite convoluted, but then, the > view is a query run over about eight other views, each of which is pulling > data from a few other views. The actual underlying data being touched is > only *a few dozen* small rows. > > As I said, the query runs fast enough.. however we only need a handful of > these queries to get run in separate connections, and the database server > will be exhausted of memory. Especially since the memory isn't returned > until the end of the connection, yet these connections typically stay up > for a while. > > I wondered if there's anything I can do to reduce this memory usage? And, > is this a bug? > > I've posted the output of the query plan here: > https://gist.github.com/2487097 > > > Thanks in advance, > Toby > > -- > Sent via pgsql-general mailing list ( pgsql-general@postgresql.org ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > > -- > "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth > > -- "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
Re: [GENERAL] Fractions of seconds in timestamps
On 2012-04-25, Valentin Militaru wrote: > This is a multi-part message in MIME format. > --050404030901030607030308 > Content-Type: text/plain; charset=UTF-8; format=flowed > Content-Transfer-Encoding: 7bit > > What about using > > WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-24 00:00:00'? > that could match the first microsecond of 2012-04-24 otherwise not a prolem :) another option is BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:60' or even BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:60.99' these are reliant on documented behaviours, but documented inosyncratic behaviours, behaviours that could potentially be improved. such that it woulkd no longer be reliable. >> you have to do it the long way >> >> f>= '2012-04-23 00:00:00' AND f< '2012-04-24 00:00:00' >> this way is mathematically correct and relies on standard guaranteed behaviours only. -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory
- Original Message - > From: "Willy-Bas Loos" > To: "Toby Corkindale" > Cc: "pgsql-general" > Sent: Wednesday, 25 April, 2012 7:16:50 PM > Subject: Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory > > would it be possible to reproduce the same query without using any > views? > you could see the difference in memory usage. It is possible to do it in stages instead, and if done that way, memory usage (and explain plans) are quite normal. However to change everything would be very time consuming to re-code so I would rather avoid that. > if that doesn't explain, try also without inheritance, by using the > ONLY keyword (and UNION ALL). Have tried something similar to that (accessing partition slices directly). It didn't change anything. > If it's really only a couple of rows, you might as well post a dump > somewhere? Then i could reproduce. Well, the data touched by the query is only a handful of rows, but it's a handful of rows in 20GB of *other* rows. Although the query plan correctly estimates it's only going to touch on a few. I'm pretty sure we didn't see this crazy memory usage in earlier testing, before the database was starting to get populated. Toby > > WBL > > > > On Wed, Apr 25, 2012 at 10:51 AM, Toby Corkindale < > toby.corkind...@strategicdata.com.au > wrote: > > > Hi Willy-Bas, > Thanks for your reply. > > I realise that stacking the views up like this complicates matters, > but the actual views are fairly simple queries, and each one > individually is only looking at a few dozen rows. (Eg. selecting > min, max or average value from a small set, grouped by one column) > From the point of view of creating reporting queries, it's a nice and > logical way to build up a query, and we didn't think it would > present any problems.. and even on a well-populated database, the > query runs very fast. It's just the astounding amount of memory used > that presents difficulties. > > Looking at the postgresql.conf for non-default settings, the notable > ones are: > > max_connections = 200 > ssl = false > shared_buffers = 256MB > max_prepared_transactions = 16 > # although they aren't used for the group of queries in question > maintenance_work_mem = 128MB > # work_mem is left at default of 1MB > effective_io_concurrency = 2 > random_page_cost = 3.0 > effective_cache_size = 512MB > geqo = on > geqo_threshold = 12 > geqo_effort = 7 > > Some other things are non-default, like checkpoints, > streaming-replication stuff, but those shouldn't have any effect. > > The memory settings (shared buffers, effective cache) might seem to > be set quite conservatively at the moment, given the memory > available in the machine -- but since we can exhaust that memory > with just a few connections, it seems fair. > > Cheers, > Toby > > > - Original Message - > From: "Willy-Bas Loos" < willy...@gmail.com > > To: "Toby Corkindale" < toby.corkind...@strategicdata.com.au > > Cc: "pgsql-general" < pgsql-general@postgresql.org > > Sent: Wednesday, 25 April, 2012 6:05:37 PM > Subject: Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of > memory > > > Stacking views is a bad practice. It usually means that you are > making the db do a lot of unnecessary work, scanning tables more > than once when you don't even need them. > According to your description, you have 3 layers of views on > partitioned tables. > I can imagine that that leaves the planner with a lot of possible > query plans, a lot of interaction and a lot of statistics to read. > > do you have any special settings for the statistics on these tables? > and could you please post the non-default settings in your > postgresql.conf file? > $ grep ^[^#] /etc/ postgresql /9.1/main/ postgresql . conf | grep -e > ^[^[:space:]] > > > > Would be helpful to see if you have any statistics or planner stuff > altered. > > Cheers, > > WBL > > > > On Wed, Apr 25, 2012 at 8:18 AM, Toby Corkindale < > toby.corkind...@strategicdata.com.au > wrote: > > > Hi, > I'm hitting some peculiar behaviour. I'm currently on Pg 9.1.3 on a > 64bit Debian system. > > I have a database which is moderately large - 20 GByte or so - and > contains that data split up over dozens of tables, which are > themselves partitioned. > Queries are usually only run against fairly small, partitioned, sets > of data. > > These queries generally run fairly fast. Performance is not a > problem. > > However Postgres is chewing up huge amounts of memory just to create > the query plan! > > For example, even if I just run > EXPLAIN SELECT a_column FROM a_view > WHERE partition_id = 1; > > Then the postgres backend process takes several seconds to return, > and in the worst example here, is hogging more than 3Gbyte once it > comes back. (It doesn't free that up until you close the connection) > > The query plan that comes back does seem quite convoluted, but then, > the view is a query run over about eight other views, each of which > is
Re: [GENERAL] Fractions of seconds in timestamps
2012/4/25 Jasen Betts : > On 2012-04-25, Valentin Militaru wrote: >> This is a multi-part message in MIME format. >> --050404030901030607030308 >> Content-Type: text/plain; charset=UTF-8; format=flowed >> Content-Transfer-Encoding: 7bit >> >> What about using >> >> WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-24 00:00:00'? >> > > that could match the first microsecond of 2012-04-24 > otherwise not a prolem :) > > another option is BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:60' > > or even BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:60.99' > > these are reliant on documented behaviours, but documented > inosyncratic behaviours, behaviours that could potentially be improved. > such that it woulkd no longer be reliable. > >>> you have to do it the long way >>> >>> f>= '2012-04-23 00:00:00' AND f< '2012-04-24 00:00:00' >>> > > this way is mathematically correct and relies on standard guaranteed > behaviours only. > > -- > ⚂⚃ 100% natural Correct Jasen! In my opinion the use of BETWEEN (as it is currently defined) makes very little sense (if any) at least for time stamps. What I've seen so far with time periods are always defined as either "[...)" or "(..]". -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PHP SQL Color Syntax that is Postgresql & GPL3 Compatible?
Hi. I'm looking for an Open Source PHP code that will take plain text SQL and turn it into colorful HTML. If it could take messy code and clean up indents and such (a la SQLinForm), that would be a nice bonus. Ideally it would understand many flavors of SQL, but handling Postgresql syntax is most important. I want to include this in my own project, so it needs to be redistributable and specifically GPL3 compatible. Again ideally, it would be a standalone and fairly lightweight piece of code, rather than having to embed some monster package. Does anyone know of such a beast? TIA for any help provided. Ken Tanzer -- AGENCY Software A data system that puts you in control *http://agency-software.org/* ken.tan...@agency-software.org (253) 245-3801
Re: [GENERAL] PHP SQL Color Syntax that is Postgresql & GPL3 Compatible?
On Apr 25, 2012, at 6:57 AM, Ken Tanzer wrote: > Hi. I'm looking for an Open Source PHP code that will take plain text SQL > and turn it into colorful HTML. If it could take messy code and clean up > indents and such (a la SQLinForm), that would be a nice bonus. Ideally it > would understand many flavors of SQL, but handling Postgresql syntax is most > important. I want to include this in my own project, so it needs to be > redistributable and specifically GPL3 compatible. Again ideally, it would be > a standalone and fairly lightweight piece of code, rather than having to > embed some monster package. > > Does anyone know of such a beast? TIA for any help provided. Not stand alone, but the phpPgAdmin project colors SQL/PostgreSQL code and I believe the license is GPL. http://phppgadmin.sourceforge.net/doku.php?id=start John DeSoi, Ph.D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Psql dosent log error messages on windows
When i execute a sql script trough psql, it shows me errors in the console window but it dosent write this errors in a log file. psql.exe -h localhost -d test -U postgres -w -f C:/test_files/test.sql >> C:/test_files/pg.log psql.exe -h localhost -d test -U postgres -w -f C:/test_files/test.sql -o C:/test_files/pg.log Configuration settings : log_destination = 'stderr,eventlog,csvlog' logging_collector = on client_min_messages = notice log_min_messages = warning The server is started as : pg_ctl.exe -l "C:/test_files/log/pg.log" -D "C:/pgsql_9_1_1/data" start Also no error messages this log file. But the errors are in the eventlog of windows. Thanks for help. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Psql dosent log error messages on windows
the windows user that owns the process ("postgres" by default) needs to
have the right to write in the folder to write a server log.
by default, this user has very few privileges (for good reasons - security).
about psql not writing that log:
>> means to redirect "standard out" to a file. But "standard error" is not
affected by that.
add 2>&1 to write errors to that log too.
hth,
WBL
On Wed, Apr 25, 2012 at 2:11 PM, Armand Turpel wrote:
> When i execute a sql script trough psql, it shows me errors in the console
> window but it dosent write this errors in a log file.
>
> psql.exe -h localhost -d test -U postgres -w -f C:/test_files/test.sql >>
> C:/test_files/pg.log
> psql.exe -h localhost -d test -U postgres -w -f C:/test_files/test.sql -o
> C:/test_files/pg.log
>
> Configuration settings :
> log_destination = 'stderr,eventlog,csvlog'
> logging_collector = on
> client_min_messages = notice
> log_min_messages = warning
>
> The server is started as :
>
> pg_ctl.exe -l "C:/test_files/log/pg.log" -D "C:/pgsql_9_1_1/data" start
>
> Also no error messages this log file.
> But the errors are in the eventlog of windows.
>
> Thanks for help.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general
>
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
Re: [GENERAL] Psql dosent log error messages on windows
like so:
http://support.microsoft.com/kb/110930
On Wed, Apr 25, 2012 at 2:27 PM, Willy-Bas Loos wrote:
> the windows user that owns the process ("postgres" by default) needs to
> have the right to write in the folder to write a server log.
> by default, this user has very few privileges (for good reasons -
> security).
>
> about psql not writing that log:
> >> means to redirect "standard out" to a file. But "standard error" is
> not affected by that.
> add 2>&1 to write errors to that log too.
>
> hth,
>
> WBL
>
>
>
> On Wed, Apr 25, 2012 at 2:11 PM, Armand Turpel <
> armand.turpel.m...@gmail.com> wrote:
>
>> When i execute a sql script trough psql, it shows me errors in the
>> console window but it dosent write this errors in a log file.
>>
>> psql.exe -h localhost -d test -U postgres -w -f C:/test_files/test.sql
>> >> C:/test_files/pg.log
>> psql.exe -h localhost -d test -U postgres -w -f C:/test_files/test.sql
>> -o C:/test_files/pg.log
>>
>> Configuration settings :
>> log_destination = 'stderr,eventlog,csvlog'
>> logging_collector = on
>> client_min_messages = notice
>> log_min_messages = warning
>>
>> The server is started as :
>>
>> pg_ctl.exe -l "C:/test_files/log/pg.log" -D "C:/pgsql_9_1_1/data" start
>>
>> Also no error messages this log file.
>> But the errors are in the eventlog of windows.
>>
>> Thanks for help.
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/**mailpref/pgsql-general
>>
>
>
>
> --
> "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
>
>
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
[GENERAL] postgresql log parsing to report on user/db access
Is anyone aware of a quick solution for producing user/db access reports from pgsql syslog format logs? in other words, I have a bunch of lines such as: > Apr 22 06:39:04 147283-db3 postgres[13252]: [1800-1] > user=database1_remote,db=sqm_remote_database1 LOG: connection authorized: > user=database1_remote database=sqm_remote_database1 > Apr 22 06:39:04 147283-db3 postgres[13252]: [1801-1] > user=database1_remote,db=sqm_remote_database1 LOG: disconnection: session > time: 0:00:00.01 user=database1_remote > Apr 22 06:39:04 147283-db3 postgres[13254]: [1799-1] > user=[unknown],db=[unknown] LOG: connection received: host=172.25.150.172 > port=58088 > Apr 22 06:39:04 147283-db3 postgres[13254]: [1800-1] > user=database1_remote,db=sqm_remote_database1 LOG: connection authorized: > user=database1_remote database=sqm_remote_database1 > Apr 22 06:39:04 147283-db3 postgres[13254]: [1801-1] > user=database1_remote,db=sqm_remote_database1 LOG: disconnection: session > time: 0:00:00.02 user=database1_remote > Apr 22 06:39:12 147283-db3 postgres[13259]: [1799-1] > user=[unknown],db=[unknown] LOG: connection received: host=172.25.150.162 > port=40768 > Apr 22 06:39:12 147283-db3 postgres[13259]: [1800-1] > user=database3_remote,db=sqm_remote_database3 LOG: connection authorized: > user=database3_remote > Apr 22 06:39:12 147283-db3 postgres[13259]: [1801-1] > user=database3_remote,db=sqm_remote_database3 LOG: disconnection: session > time: 0:00:00.01 user=database3_remote > Apr 22 06:39:16 147283-db3 postgres[13263]: [1799-1] > user=[unknown],db=[unknown] LOG: connection received: host=172.25.150.69 > port=37739 > Apr 22 06:39:16 147283-db3 postgres[13263]: [1800-1] > user=database2_remote,db=sqm_remote_database2 LOG: connection authorized: > user=database2_remote database=sqm_remote_database2 > Apr 22 06:39:16 147283-db3 postgres[13263]: [1801-1] > user=database2_remote,db=sqm_remote_database2 LOG: disconnection: session > time: 0:00:00.01 user=database2_remote > Apr 22 06:39:18 147283-db3 postgres[13264]: [1799-1] > user=[unknown],db=[unknown] LOG: connection received: host=172.25.150.69 > port=37740 and I'd like to be able to generate weekly reports that provide rolled up statistics on which user connected to which db, how many times, etc. I've used pg_fouine for doing reporting on queries, but it doesn't really deal with tracking user/db access. Before I try to whip up something myself with perl or something, I wanted to see if anyone's already done something similar. thanks in advance-- -- Larry J. Prikockis System Administrator 240-965-4597 (direct) lprikoc...@vecna.com http://www.vecna.com http://www.vecnamedical.com http://vecnamed.blogspot.com/ Vecna 6404 Ivy Lane Suite 500 Greenbelt, MD 20770 Phone: 240-965-4500 Fax: 240-547-6133 Better Technology, Better World (TM) The contents of this message may be privileged and confidential. Therefore, if this message has been received in error, please delete it without reading it. Your receipt of this message is not intended to waive any applicable privilege. Please do not disseminate this message without the permission of the author. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql log parsing to report on user/db access
On 04/25/2012 06:36 AM, Larry J Prikockis wrote: Is anyone aware of a quick solution for producing user/db access reports from pgsql syslog format logs? in other words, I have a bunch of lines such as: Apr 22 06:39:04 147283-db3 postgres[13252]: [1800-1] user=database1_remote,db=sqm_remote_database1 LOG: connection authorized: user=database1_remote database=sqm_remote_database1... and I'd like to be able to generate weekly reports that provide rolled up statistics on which user connected to which db, how many times, etc. What is the etc? You should be able to parse it pretty quickly with awk but if you want additional stats like length of time a user connected it will involve additional work. Have you considered logging to the csv format? http://www.postgresql.org/docs/9.1/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG This may be an easier approach as you can just pull the data into a table and report from there. Of course you can pre-filter the CSV with grep or similar to avoid importing uninteresting lines. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Updates, ShareLocks, RowExclusiveLocks, and deadlocks
We have a few daemon process that constantly pull batches of logs from a work queue and then insert into or update a single table in a single transaction, ~1k rows at a time. I've been told the transaction does nothing other than insert and update on that table, and I can verify the table in question has no triggers. This is 9.1.3. What we've noticed is that most updates come with a RowExclusiveLock, according to pg_locks, which is what you'd expect from the manual. Unfortunately, sometimes, some of those update transactions have ShareLocks, which doesn't make any sense to us, because we're not taking explicit locks and the manual claims ShareLocks only automatically come from index creation, which we're also not doing explicitly. This is a problem because it's showing up as deadlocks, every few minutes. I've enabled logging all queries and can verify there are no explicit locks an ORM might be doing under our noses. So at this point, I'm confused. If we're not explicitly locking, running triggers that might be locking, or creating indices, where are these ShareLocks coming from? Any suggestions on how to track it down? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updates, ShareLocks, RowExclusiveLocks, and deadlocks
Ben Chobot writes: > We have a few daemon process that constantly pull batches of logs from a work > queue and then insert into or update a single table in a single transaction, > ~1k rows at a time. I've been told the transaction does nothing other than > insert and update on that table, and I can verify the table in question has > no triggers. This is 9.1.3. > What we've noticed is that most updates come with a RowExclusiveLock, > according to pg_locks, which is what you'd expect from the manual. > Unfortunately, sometimes, some of those update transactions have ShareLocks, > which doesn't make any sense to us, because we're not taking explicit locks > and the manual claims ShareLocks only automatically come from index creation, > which we're also not doing explicitly. This is a problem because it's showing > up as deadlocks, every few minutes. You'd really need to provide more information here, but what I suspect is that you're seeing row-level locks in process of being acquired. For implementation reasons that type of activity will transiently try to acquire ShareLock on another transaction's XID. Or this might be something different, but without seeing a full copy of the pg_locks rows you're talking about, it's hard to be sure. In any case, I'd speculate that the underlying cause is conflicting updates on the same row, and/or foreign-key-related row locks. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using copy with a file containing blank rows
- Original Message - From: Adrian Klaver On 03/15/2012 09:17 AM, George Weaver wrote: Hi All, I am trying to use COPY to import postgresql logs into a postgresql database for further review and sorting. Are you using the CSV format to log to the Postgres log?: http://www.postgresql.org/docs/9.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG [life intervenes...] Thanks Adrian - I wasn't using the CSV format - I am now! It solves the blank line problem I was encountering. George The problem I'm encountering happens when COPY hits a blank row: development=# COPY log development-# FROM 'D:/PostgreSQL/9.1/pg_log/postgresql-2012-03-15_093730.log' development-# WITH DELIMITER '^' NULL AS ''; ERROR: missing data for column "ip_address" CONTEXT: COPY log, line 2: " " Line 2 is a blank line. Is there any way to use COPY with blank lines or do the blank lines have to removed before using COPY? AFAIK, you have to remove blank lines. Thanks, George -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updates, ShareLocks, RowExclusiveLocks, and deadlocks
On Apr 25, 2012, at 12:35 PM, Tom Lane wrote: > Ben Chobot writes: >> We have a few daemon process that constantly pull batches of logs from a >> work queue and then insert into or update a single table in a single >> transaction, ~1k rows at a time. I've been told the transaction does nothing >> other than insert and update on that table, and I can verify the table in >> question has no triggers. This is 9.1.3. >> What we've noticed is that most updates come with a RowExclusiveLock, >> according to pg_locks, which is what you'd expect from the manual. >> Unfortunately, sometimes, some of those update transactions have ShareLocks, >> which doesn't make any sense to us, because we're not taking explicit locks >> and the manual claims ShareLocks only automatically come from index >> creation, which we're also not doing explicitly. This is a problem because >> it's showing up as deadlocks, every few minutes. > > You'd really need to provide more information here, but what I suspect > is that you're seeing row-level locks in process of being acquired. > For implementation reasons that type of activity will transiently > try to acquire ShareLock on another transaction's XID. Or this might > be something different, but without seeing a full copy of the pg_locks > rows you're talking about, it's hard to be sure. In any case, I'd > speculate that the underlying cause is conflicting updates on the same > row, and/or foreign-key-related row locks. So, if I understand what you're saying, if I have two connections each transactionally updating many rows, then each transaction will need to acquire a RowExclusiveLock for each row (as documented), and also (as not documented?) each acquisition will temporarily acquire a ShareLock on the other transaction's transactionid? That seems to fit what I'm seeing in pg_locks, and I suppose if there is an overlap in rows between to two transactions, and if those updates happened in the wrong order, then we'd get deadlock. I just assumed we'd see that in the logs as deadlocks due to waiting for RowExclusiveLocks, while it sounds like you are saying the log will show them as ShareLocks? If that's the case, would doing the updates in, say, primary key order solve this problem? I'm pretty sure we're just pulling things out of the queue and running them in random order. If that's not the case, then what information would be helpful in understanding what's going on? All of pg_locks or just the locks related to the virtualtransactionid of the update with the SharedLock? There are no foreign keys related to this table. Either way, thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updates, ShareLocks, RowExclusiveLocks, and deadlocks
Ben Chobot writes: > So, if I understand what you're saying, if I have two connections each > transactionally updating many rows, then each transaction will need to > acquire a RowExclusiveLock for each row (as documented), and also (as not > documented?) each acquisition will temporarily acquire a ShareLock on the > other transaction's transactionid? That seems to fit what I'm seeing in > pg_locks, and I suppose if there is an overlap in rows between to two > transactions, and if those updates happened in the wrong order, then we'd get > deadlock. I just assumed we'd see that in the logs as deadlocks due to > waiting for RowExclusiveLocks, while it sounds like you are saying the log > will show them as ShareLocks? I don't have all the details in my head, but if you deliberately provoke a deadlock by making two transactions update the same two rows in opposite orders, you'll soon find out what it looks like in the log. > If that's the case, would doing the updates in, say, primary key order solve > this problem? I'm pretty sure we're just pulling things out of the queue and > running them in random order. Any consistent ordering ought to dodge that type of problem. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updates, ShareLocks, RowExclusiveLocks, and deadlocks
On Wed, Apr 25, 2012 at 01:12:37PM -0600, Ben Chobot wrote: > So, if I understand what you're saying, if I have two connections > each transactionally updating many rows, then each transaction will > need to acquire a RowExclusiveLock for each row (as documented), and > also (as not documented?) each acquisition will temporarily acquire a > ShareLock on the other transaction's transactionid? That seems to > fit what I'm seeing in pg_locks, and I suppose if there is an overlap > in rows between to two transactions, and if those updates happened in > the wrong order, then we'd get deadlock. I just assumed we'd see > that in the logs as deadlocks due to waiting for RowExclusiveLocks, > while it sounds like you are saying the log will show them as > ShareLocks? I think what you're missing here is that RowExclusiveLocks are taken by marking the row itself. If two transactions want to lock the same row, transaction A marks the row, then transactions B sees the marking and so must wait until transaction A completes. To do this transaction B tries to take a lock on the transaction A. Since each transaction has an exclusive lock on itself, the effect is that transaction B waits for transaction A to complete. Apparently this is done using a ShareLock, but I think any locktype would do. But taking a lock on another transaction is a pretty common way to wait on another transaction. And these locks only appear when needed. > If that's the case, would doing the updates in, say, primary key > order solve this problem? I'm pretty sure we're just pulling things > out of the queue and running them in random order. If you're taking locks it's always better to be consistant about the order, so it may help, yes. > If that's not the case, then what information would be helpful in > understanding what's going on? All of pg_locks or just the locks > related to the virtualtransactionid of the update with the > SharedLock? There are no foreign keys related to this table. Updating a row locks it against other updates, because the second update needs to know which version of the row it's updating. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Updates, ShareLocks, RowExclusiveLocks, and deadlocks
On Apr 25, 2012, at 1:31 PM, Tom Lane wrote: > I don't have all the details in my head, but if you deliberately provoke > a deadlock by making two transactions update the same two rows in > opposite orders, you'll soon find out what it looks like in the log. Heh, duh. Looks like your first guess was correct, as usual. Conflicting updates in two transactions show up in the logs as a ShareLock deadlock. Thanks!
Re: [GENERAL] Updates, ShareLocks, RowExclusiveLocks, and deadlocks
Martijn van Oosterhout writes: > I think what you're missing here is that RowExclusiveLocks are taken by > marking the row itself. More specifically: row-level locks are not reflected in pg_locks at all. A RowExclusiveLock entry in pg_locks reflects a *table* level lock, which is taken by any INSERT/UPDATE/DELETE command on the table, independently of how many rows it modifies (even zero). The purpose of that type of lock is to prevent concurrent DDL changes on the table, not to serialize the row-level operations. There are locks that will show up in pg_locks that are taken transiently when attempting to mark a row modified, but they don't persist after the mark has been made, and the details have changed from time to time in different PG versions. So I'd suggest testing it to see exactly what you get. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how robust are custom dumps?
On Wed, 2012-04-25 at 10:40 +0200, Willy-Bas Loos wrote: > On Wed, Apr 25, 2012 at 9:51 AM, Magnus Hagander wrote: > > > We used to have a bug/lackoffeature in pg_dump at the 2GB boundary as > > well, IIRC, specifically on Win32. Maybe you were hit by that one.. > > Yes, possibly. I didn't even know how to make a compressed plain dump, but > that doesn't really plea my case :/ > > > > > i do have one suggestion. > > > pg_restore only gives a user this feedback, when he makes this > > > mistake:"pg_restore: [archiver] input file does not appear to be a valid > > > archive". > > > > > > Would it be feasible for pg_restore to detect that it is a different > > pg_dump > > > format and inform the user about it? > > > > > The main one you'd want to detect is plain I think - and I don't know > > if we can reliably detect that. It could be just a generic textfile, > > after all - how would we know the difference? > > > > > > Well, on linux you could make pg_dump run /usr/bin/file on the file to see > what kind it is. If it is gzipped, suggest that it might be a gzipped plain > dump, if it is plain text, suggest that it might be a plain dump (etc, > also bzip2). That's all. > You don't have to be sure that it is valid, just say a bit more than "does > not appear to be a valid archive". Help a user in a bad situation. > > Only, i know that postgres runs on many platforms, so you probably can't > run /usr/bin/file on all of those (or might not be installed on linux > machine). So it probably should be part of pg_restore itself. > pg_restore will do so for plain backups on 9.2: $ pg_dump b1 > b1.dump $ pg_restore -d b2 b1.dump pg_restore: [archiver] input file appears to be a text format dump. Please use psql. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Formatting time for INSERT INTO
The table has a column 'coll_time' of type time without time zone. New rows for the table are in a .sql file and the time values throw an error at the colon between hours:minutes. Do time values need to be quoted? TIA, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Formatting time for INSERT INTO
On Apr 25, 2012, at 4:01 PM, Rich Shepard wrote: > The table has a column 'coll_time' of type time without time zone. New > rows for the table are in a .sql file and the time values throw an error at > the colon between hours:minutes. Do time values need to be quoted? Yes, (date)time values need to be quoted as if they were strings. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Formatting time for INSERT INTO
On Wed, 25 Apr 2012, Ben Chobot wrote: Yes, (date)time values need to be quoted as if they were strings. Thanks, Ben. I thought that was the case but wanted to confirm it. Much appreciated, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] empty role names in pg_dumpall output
Hi, PostgreSQL 9.0.4 I have this in pg_dumpall -g output (non-empty role names changed): GRANT "" TO a GRANTED BY postgres; GRANT "" TO b GRANTED BY c; GRANT "" TO b GRANTED BY c; GRANT "" TO b GRANTED BY c; GRANT "" TO b GRANTED BY c; GRANT "" TO "" GRANTED BY c; GRANT "" TO "" GRANTED BY postgres; GRANT "" TO "" GRANTED BY postgres; There is no CREATE ROLE "" ... in the output. select * from pg_authid where length(rolname)<1 -- returns no rows I would like to get rid of these roles, but how? I appreciate any hints / ideas. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How can I see if my code is "concurrency safe"?
Hi there! Today I realised that my knowledge concerning how postgres handles concurrency is not very good, and its even worse when it comes to using that knowledge in real-life. Let me give you an example. I have this table create table userpositions ( userID int, positionID int, unique (userID,positionID)); For a given userID there can be many positionIDs. There are then two operations performed on this table, the first is "select positionID from userpositions where userID=..." to get all positions for a user, and the second is to replace all positions for the user with new positions. For this I was thinking about running it in a transaction begin; delete from userpositions where userID=...; insert into userpositions (userID,positionID) values ; commit; But will this work? I don't want select to return empty results, I don't want two inserts running causing a unique violation. Experimenting with it tells me yes, it will work, but how should I reason to "convinse" my self that it will work? Quoting the docs: "The partial transaction isolation provided by Read Committed mode is adequate for many applications, and this mode is fast and simple to use; however, it is not sufficient for all cases. Applications that do complex queries and updates might require a more rigorously consistent view of the database than Read Committed mode provides." How do I know I'm not creating one of those complex queries? Or to put it differntly, how do you identify a complex query with potential issues? Any special techniques to analyze? /J -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How can I see if my code is "concurrency safe"?
On Apr 25, 2012, at 5:17 PM, Janne H wrote:
> Hi there!
>
> Today I realised that my knowledge concerning how postgres handles
> concurrency is not very good, and its even worse when it comes to using that
> knowledge in real-life.
>
> Let me give you an example.
> I have this table
>
> create table userpositions ( userID int, positionID int, unique
> (userID,positionID));
>
> For a given userID there can be many positionIDs.
>
> There are then two operations performed on this table, the first is "select
> positionID from userpositions where userID=..." to get all positions for a
> user, and the second is to replace all positions for the user with new
> positions. For this I was thinking about running it in a transaction
>
> begin;
> delete from userpositions where userID=...;
> insert into userpositions (userID,positionID) values ;
> commit;
>
> But will this work? I don't want select to return empty results, I don't want
> two inserts running causing a unique violation.
> Experimenting with it tells me yes, it will work, but how should I reason to
> "convinse" my self that it will work?
>
> Quoting the docs: "The partial transaction isolation provided by Read
> Committed mode is adequate for many applications, and this mode is fast and
> simple to use; however, it is not sufficient for all cases. Applications that
> do complex queries and updates might require a more rigorously consistent
> view of the database than Read Committed mode provides."
>
>
> How do I know I'm not creating one of those complex queries? Or to put it
> differntly, how do you identify a complex query with potential issues? Any
> special techniques to analyze?
Think about it this way: once one session starts a transaction, any
modifications it makes are invisible to other sessions until you commit your
transaction, at which point they all become visible atomically. (Unless those
other sessions have explicitly said, "yes, I want to play with fire" and
changed their isolation mode to something other than Read Committed.)
So given what you've said, it seems like you should be set, so long as you
don't have two different sessions try to insert the same {userID,positionID}
tuple. If that happens, then the second one that tries to commit will fail. You
can avoid that by using a sequence for positionID, which will result in gaps
and non-sequential IDs, but also no uniqueness failures.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory
Hi, Just wondering if anyone else has thoughts on this? I'm still suspicious that this is a bug. If I run EXPLAIN (or the query itself) on a database that has all the schemas and tables created, but just the relevant data touched by the query loaded.. then everything is fine. The query plan is still hundreds of lines long, but running it doesn't use much RAM. So I think that eliminates work_mem-related issues. It really does seem like it's purely the query plan itself that is consuming all the memory. Has anyone else seen this? Thanks, Toby On 25/04/12 16:18, Toby Corkindale wrote: Hi, I'm hitting some peculiar behaviour. I'm currently on Pg 9.1.3 on a 64bit Debian system. I have a database which is moderately large - 20 GByte or so - and contains that data split up over dozens of tables, which are themselves partitioned. Queries are usually only run against fairly small, partitioned, sets of data. These queries generally run fairly fast. Performance is not a problem. However Postgres is chewing up huge amounts of memory just to create the query plan! For example, even if I just run EXPLAIN SELECT a_column FROM a_view WHERE partition_id = 1; Then the postgres backend process takes several seconds to return, and in the worst example here, is hogging more than 3Gbyte once it comes back. (It doesn't free that up until you close the connection) The query plan that comes back does seem quite convoluted, but then, the view is a query run over about eight other views, each of which is pulling data from a few other views. The actual underlying data being touched is only *a few dozen* small rows. As I said, the query runs fast enough.. however we only need a handful of these queries to get run in separate connections, and the database server will be exhausted of memory. Especially since the memory isn't returned until the end of the connection, yet these connections typically stay up for a while. I wondered if there's anything I can do to reduce this memory usage? And, is this a bug? I've posted the output of the query plan here: https://gist.github.com/2487097 Thanks in advance, Toby -- .signature -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How can I see if my code is "concurrency safe"?
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Ben Chobot
> Sent: Wednesday, April 25, 2012 7:29 PM
> To: Janne H
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How can I see if my code is "concurrency safe"?
>
> On Apr 25, 2012, at 5:17 PM, Janne H wrote:
>
> > Hi there!
> >
> > Today I realised that my knowledge concerning how postgres handles
> concurrency is not very good, and its even worse when it comes to using
that
> knowledge in real-life.
> >
> > Let me give you an example.
> > I have this table
> >
> > create table userpositions ( userID int, positionID int, unique
> > (userID,positionID));
> >
> > For a given userID there can be many positionIDs.
> >
> > There are then two operations performed on this table, the first is
> > "select positionID from userpositions where userID=..." to get all
> > positions for a user, and the second is to replace all positions for
> > the user with new positions. For this I was thinking about running it
> > in a transaction
> >
> > begin;
> > delete from userpositions where userID=...;
> > insert into userpositions (userID,positionID) values ; commit;
> >
> > But will this work? I don't want select to return empty results, I don't
want
> two inserts running causing a unique violation.
> > Experimenting with it tells me yes, it will work, but how should I
reason to
> "convinse" my self that it will work?
> >
> > Quoting the docs: "The partial transaction isolation provided by Read
> Committed mode is adequate for many applications, and this mode is fast
> and simple to use; however, it is not sufficient for all cases.
Applications that
> do complex queries and updates might require a more rigorously consistent
> view of the database than Read Committed mode provides."
> >
> >
> > How do I know I'm not creating one of those complex queries? Or to put
it
> differntly, how do you identify a complex query with potential issues? Any
> special techniques to analyze?
>
> Think about it this way: once one session starts a transaction, any
> modifications it makes are invisible to other sessions until you commit
your
> transaction, at which point they all become visible atomically. (Unless
those
> other sessions have explicitly said, "yes, I want to play with fire" and
changed
> their isolation mode to something other than Read Committed.)
>
> So given what you've said, it seems like you should be set, so long as you
> don't have two different sessions try to insert the same
{userID,positionID}
> tuple. If that happens, then the second one that tries to commit will
fail. You
> can avoid that by using a sequence for positionID, which will result in
gaps
> and non-sequential IDs, but also no uniqueness failures.
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
=
Please keep in mind that simply adding in a "SERIAL" (sequence) value to
avoid duplicate keys is dangerous as well. While it avoids a physical
duplicate you are now able to insert a logical duplicate into the system.
Specific to your code could you just perform the following?
UPDATE userpositions SET userid = new_userid WHERE userid = old_userid;
In response to Ben: it appears that positionID is a FK in this situation
which means whether the corresponding PK is serial or not is irrelevant.
To Janne: If this indeed is a foreign key situation (multi-multi reference
table) you should define both columns using "FOREIGN KEY ... REFERENCES ..."
Since you are inserting records for a different userID than the one you are
deleting you currently have no checking in place to ensure that one of your
INSERT values is not already on the table. You also have not defined the
relationship between position and user (i.e., can more than one person hold
a given position). Assuming each position can only have one person active
at a time you should define the positionID field as UNIQUE by itself as
well. Then, if you ensure you only add records that correspond to the
deleted records shown you can ensure that no duplicates will exist since
someone trying to add a duplicate position before you commit will have to
wait on the DELETE to unlock the position and by the time that happens you
will already have insert a new record for that position.
RISKY: Your risk, as shown, is between obtaining your "values" and running
the transaction delete/insert your "values" become invalid. Say after you
select but before you delete/insert someone adds a new position to the user.
Your delete will remove the newly added position but your subsequent insert
will not have the new data and thus that position will end up un-filled
after your routine. Same thing applies if they delete a position entry -
you will end up filling it again. It is also possible that the posit
Re: [GENERAL] Explain verbose query with CTE
Bartosz Dmytrak writes:
> [ EXPLAIN VERBOSE fails for ]
> WITH t as (
> INSERT INTO "tblD1" (id, "Data1")
> VALUES ('a', 123)
> RETURNING *)
> UPDATE "tblBase"
> SET "SomeData" = 123
> WHERE id = 'a';
I've applied a patch for this. Thanks for the report!
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How can I see if my code is "concurrency safe"?
On Wed, Apr 25, 2012 at 4:17 PM, Janne H wrote: > Hi there! > > Today I realised that my knowledge concerning how postgres handles > concurrency is not very good, and its even worse when it comes to using that > knowledge in real-life. I think what everyone here is trying to say is that while PostgreSQL is very good at handling concurrency itself, you may want to be careful about making sure your relational constraints are correct. This means proper use of multi-column keys (either as primary keys, unique constraints, or foreign keys). If you do a good job here, PostgreSQL will handle your concurrency needs for you. On an internal level, look at how MVCC works. It isn't really a question of overall concurrency so much as it is a matter of concurrency performance, since locking is no longer as necessary. Also check out the serializable transaction level, though the need for this sort of concurrency protection is rare. Best Wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How can I see if my code is "concurrency safe"?
On Wed, 25 Apr 2012 16:17:53 -0700 (PDT) Janne H wrote: > Hi there! > > Today I realised that my knowledge concerning how postgres handles > concurrency is not very good, and its even worse when it comes to using that > knowledge in real-life. > > Let me give you an example. > I have this table > > create table userpositions ( userID int, positionID int, unique > (userID,positionID)); > > For a given userID there can be many positionIDs. > > There are then two operations performed on this table, the first is "select > positionID from userpositions where userID=..." to get all positions for a > user, and the second is to replace all positions for the user with new > positions. For this I was thinking about running it in a transaction > > begin; > delete from userpositions where userID=...; > insert into userpositions (userID,positionID) values ; > commit; > > But will this work? I don't want select to return empty results, I don't want > two inserts running causing a unique violation. > Experimenting with it tells me yes, it will work, but how should I reason to > "convinse" my self that it will work? > > Quoting the docs: "The partial transaction isolation provided by Read > Committed mode is adequate for many applications, and this mode is fast and > simple to use; however, it is not sufficient for all cases. Applications that > do complex queries and updates might require a more rigorously consistent > view of the database than Read Committed mode provides." > > > How do I know I'm not creating one of those complex queries? Or to put it > differntly, how do you identify a complex query with potential issues? Any > special techniques to analyze? Just try it. Open two psql sessions and manually enter the commands one at a time, alternating between windows. Try some scenarios that you're concerned about and see what happens. Experience will teach you a lot very quickly. I can say, from experience, that what you have above is not sufficient to protect you from empty result sets and duplicate keys in rare, but annoyingly persistent cases. Especially if your front-end is a web-app, you may be shocked to find out how often users will double-click instead of single click, thus resulting in the exact same queries running close enough together to conflict with each other. Of course, this can be fixed in many ways, but understanding why it happens is the first step to choosing the best approach for your application. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory
Toby Corkindale writes: > Just wondering if anyone else has thoughts on this? > I'm still suspicious that this is a bug. Well, if you were to provide a reproducible test case, somebody might be motivated to look into it. There could be a memory leak in the planner somewhere, but without a test case it's not very practical to go look for it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] empty role names in pg_dumpall output
=?UTF-8?Q?Filip_Rembia=C5=82kowski?= writes: > PostgreSQL 9.0.4 > I have this in pg_dumpall -g output (non-empty role names changed): > GRANT "" TO a GRANTED BY postgres; > GRANT "" TO b GRANTED BY c; > GRANT "" TO b GRANTED BY c; > GRANT "" TO b GRANTED BY c; > GRANT "" TO b GRANTED BY c; > GRANT "" TO "" GRANTED BY c; > GRANT "" TO "" GRANTED BY postgres; > GRANT "" TO "" GRANTED BY postgres; Hmm. A look at the code in pg_dumpall suggests that the problem is unmatched entries in pg_auth_members, ie this query: SELECT ur.rolname AS roleid FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid is returning some null results. You might look into that catalog and see if you can figure out what happened. As far as making the problem go away is concerned, deleting the bogus pg_auth_members rows should do it; but it would be a good idea to try to figure out what happened first. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory
On 26/04/12 13:11, Tom Lane wrote: Toby Corkindale writes: Just wondering if anyone else has thoughts on this? I'm still suspicious that this is a bug. Well, if you were to provide a reproducible test case, somebody might be motivated to look into it. There could be a memory leak in the planner somewhere, but without a test case it's not very practical to go look for it. Hi Tom, Thanks for responding.. I'm trying to work on a test case, but it's quite tricky. It'll need to be something like a script that generates a tonne of partitions at the very least. I don't know if the actual amount of data in the partitions is part of the problem or not. Would a Perl-based script that built up a database like that be a useful test case for you? For what it's worth, I discovered something quite interesting. The memory usage only blows out when I do an update based on the results of the query. But not if I just select the results on their own, nor if I do the update using those values on its own. ie. Method #1, uses all the memory and doesn't return it: explain update line set status = 'foo' where file_id=725 and line.lineno in ( select line from complex_view where file_id=725 ); Method #2, also uses all the memory: explain update line set status = 'foo' from complex_view v where line.lineno = v.line and line.file_id=725 and v.file_id=725; Method #3, which uses next to no memory: explain select line from complex_view where file_id=725; Method #4, which also uses next to no memory: explain create temp table foo as select line from complex_view; where file_id=725; update line set status = 'foo' from foo where line.lineno=foo.line and file_id=725; -Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory
Toby Corkindale writes: > On 26/04/12 13:11, Tom Lane wrote: >> Well, if you were to provide a reproducible test case, somebody might be >> motivated to look into it. There could be a memory leak in the planner >> somewhere, but without a test case it's not very practical to go look >> for it. > Would a Perl-based script that built up a database like that be a useful > test case for you? Yeah, sure, just something that somebody else can run to duplicate the problem. > For what it's worth, I discovered something quite interesting. The > memory usage only blows out when I do an update based on the results of > the query. Hm, is the update target an inheritance tree? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory
On 26/04/12 15:30, Tom Lane wrote: Toby Corkindale writes: On 26/04/12 13:11, Tom Lane wrote: Well, if you were to provide a reproducible test case, somebody might be motivated to look into it. There could be a memory leak in the planner somewhere, but without a test case it's not very practical to go look for it. Would a Perl-based script that built up a database like that be a useful test case for you? Yeah, sure, just something that somebody else can run to duplicate the problem. For what it's worth, I discovered something quite interesting. The memory usage only blows out when I do an update based on the results of the query. Hm, is the update target an inheritance tree? The target is the parent table of a bunch of partitions. The actual rows being updated live in those child tables. Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory
Toby Corkindale writes: > On 26/04/12 15:30, Tom Lane wrote: >> Hm, is the update target an inheritance tree? > The target is the parent table of a bunch of partitions. How many would "a bunch" be, exactly? I'm fairly sure that the complex view would get re-planned for each target table ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
