Re: [GENERAL] Google Cloud Platform, snapshots and WAL

> On Mar 20, 2017, at 6:31 AM, Moreno Andreo wrote: > > Hi everyone, > >I have my PostgreSQL 9.5 server running on a VM instance on Google Compute > Engine (Google Cloud Platform) on Debian Jessie (8.3), and I have another > dedicated VM instance that, every night at 3.00, takes a snapsho

Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?

On Aug 31, 2016, at 2:55 PM, Nicolas Grilly wrote: > > It looks like Instagram has been using pg_reorg (the ancestor of pg_repack) > to keep all likes from the same user contiguous on disk, in order to minimize > disk seeks. > > http://instagram-engineering.tumblr.com/post/40781627982/handling

Re: [GENERAL] RAM of Postgres Server

On Jan 7, 2016, at 10:32 PM, Sachin Srivastava wrote: > > > Dear John, > > We are looking at more like 500-600 connections simultaneously in 1 day and I > want to say we get 1 to 12000 connections a day per db. Unless you have 300 cores to service those 500-600 simultaneous connections,

Re: [GENERAL] pgpass (in)flexibility

On Sep 15, 2015, at 12:27 AM, Jim Nasby wrote: > > On 9/15/15 12:48 AM, Ben Chobot wrote: >> We're in a situation where we would like to take advantage of the pgpass >> hostname field to determine which password gets used. For example: >> >> psql -h prod-

[GENERAL] pgpass (in)flexibility

We're in a situation where we would like to take advantage of the pgpass hostname field to determine which password gets used. For example: psql -h prod-server -d foo # should use the prod password psql -h beta-server -d foo # should use the beta password This would *seem* to be simple, just put

Re: [GENERAL] in defensive of zone_reclaim_mode on linux

> On Sep 6, 2015, at 4:07 AM, Andres Freund wrote: > > To me that sounds like the negative impact of transparent hugepages > being mitigated to some degree by zone reclaim mode (which'll avoid some > cross-node transfers). FWIW: $ cat /sys/kernel/mm/transparent_hugepage/enabled always madvise

[GENERAL] in defensive of zone_reclaim_mode on linux

Over the last several months, I've seen a lot of grumbling about how zone_reclaim_mode eats babies, kicks puppies, and basically how you should just turn it off and live happily ever after. I thought I should add a counterexample, because that advice has not proven very good for us. Some facts

Re: [GENERAL] SSD Drives

On Apr 3, 2014, at 12:47 PM, John R Pierce wrote: > On 4/3/2014 9:26 AM, Joe Van Dyk wrote: >> Related, anyone have any thoughts on using postgresql on Amazon's EC2 SSDs? >> Been looking at >> http://aws.amazon.com/about-aws/whats-new/2013/12/19/announcing-the-next-generation-of-amazon-ec2-hi

Re: [GENERAL] Optimizing tables for known queries?

On Feb 9, 2014, at 2:48 PM, John Anderson wrote: > What I'm wondering is if there is a more denormalized view of this type of > data that would make those of types of queries quicker? That sounds like a materialized view?

Re: [GENERAL] PG replication across DataCenters

On Dec 9, 2013, at 8:09 AM, Thomas Harold wrote: > On 11/22/2013 5:57 AM, Albe Laurenz wrote: >> Kaushal Shriyan wrote: >>> I have read on the web that Postgresql DB supports replication >>> across data centers. Any real life usecase examples if it has been >>> implemented by anyone. >> >> Well,

Re: [GENERAL] 9.1.9 -> 9.1.10 causing corruption

For posterity, it appears my issues were https://wiki.postgresql.org/wiki/Nov2013ReplicationIssue. On Nov 4, 2013, at 3:48 PM, Ben Chobot wrote: > Anybody? I've tried this again on another streaming replication server, and > again had pg_toast errors until I re-basebackup'd

Re: [GENERAL] 9.1.9 -> 9.1.10 causing corruption

I'll have drained the clients from it anyway, so that's not a big deal for a temporary thing. On Nov 1, 2013, at 1:44 PM, Ben Chobot wrote: > I've got a bunch of independent database clusters, each with a couple of > streaming replication slaves. I'm starting to upgrade

[GENERAL] 9.1.9 -> 9.1.10 causing corruption

I've got a bunch of independent database clusters, each with a couple of streaming replication slaves. I'm starting to upgrade them to 9.1.10, but on 3 of the 3 I've tried so far, this has somehow resulted in data corruption. I'm hoping it was the upgrade itself that caused the corruption, inste

Re: [GENERAL] async streaming and recovery_target_timeline=latest

On Jul 28, 2013, at 5:29 PM, Amit Langote wrote: > I think, the WAL recycling on standby names the recycled segments with > the latest timelineID (in this case it's 0x10) which creates WALs that > there shouldn't have been like 0010146A0001 instead of > 000F146A0001. This p

Re: [GENERAL] async streaming and recovery_target_timeline=latest

Anybody? On Jul 3, 2013, at 3:23 PM, Ben Chobot wrote: > We have an async streaming setup using 9.1.9 and 3 nodes - let's call them A, > B, and C. A is the master, B and C are slaves. Today, A crashed, so we made B > be the master and told C to follow along with the switch b

[GENERAL] async streaming and recovery_target_timeline=latest

We have an async streaming setup using 9.1.9 and 3 nodes - let's call them A, B, and C. A is the master, B and C are slaves. Today, A crashed, so we made B be the master and told C to follow along with the switch by changing the primary_conninfo in it's recovery.conf, making sure the history fil

Re: [GENERAL] 2 postgresql server on the same station : conflict?

On May 28, 2013, at 2:54 AM, image wrote: > Hello, > > On the same station, i have 2 postgresql server: one for my postgis db > (v9.1) and so another installed with opener^7 (9.2). Unfortunalty, i noticed > i'm obliged to stop service for my postgresql postgis (9.1) in order to use > openerp7 (po

Re: [GENERAL] Basic question on recovery and disk snapshotting

On Apr 27, 2013, at 10:40 AM, Yang Zhang wrote: > My question really boils down to: if we're interested in using COW > snapshotting (a common feature of modern filesystems and hosting > environments), would we necessarily need to ensure the data and > pg_xlog are on the same snapshotted volume? I

Re: [GENERAL] Hosting PG on AWS in 2013

On Apr 8, 2013, at 2:15 AM, Vincent Veyron wrote: > Could someone explain to me the point of using an AWS instance in the > case of the OP, whose site is apparently very busy, versus renting a > bare metal server in a datacenter? Well, at least in my experience, you don't go to AWS because the da

Re: [GENERAL] Hosting PG on AWS in 2013

On Apr 6, 2013, at 6:51 PM, David Boreham wrote: > First I need to say that I'm asking this question on behalf of "a friend", > who asked me what I thought on the subject -- I host all the databases > important to me and my livelihood, on physical machines I own outright. That > said, I'm curi

Re: [GENERAL] Splitting Postgres into Separate Clusters?

On Mar 9, 2013, at 11:54 AM, Paul Jungwirth wrote: > Hello, > > I'm running a specialized search engine that indexes a few tens of millions > of web pages, keeping everything in Postgres, and one problem I'm starting to > see is poor cache hit rates. My database has two or three tables just for

[GENERAL] speeding up ALTER ... SET NOT NULL

I'm in an unfortunate position of needing to add a unique, not null index to a very large table with heavy churn. Without having much impact, I can add a NULL column that reads default values from a sequence for new rows, and then do batch updates over time to fill in the old values but then

[GENERAL] bug, bad memory, or bad disk?

We have a Postgres server (PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit) which does streaming replication to some slaves, and has another set of slaves reading the wal archive for wal-based replication. We had a bit of fun yesterday w

Re: [GENERAL] Determine if an index is a B-tree, GIST, or something else?

On Jan 17, 2013, at 10:03 AM, Paul Jungwirth wrote: > Is there any way to determine, by querying pg_index and other pg_* tables, > whether an index was created as `USING something`? I've already got a big > query joining pg_class, pg_index, etc. to pull out various attributes about > the index

Re: [GENERAL] How to store clickmap points?

On Jan 8, 2013, at 2:12 AM, aasat wrote: > Hi, > > I want to store clickmap points (X, Y and hits value) for website > > I currently have table like this > > CREATE TABLE clickmap ( > page_id integer, > date date, > x smallint, > y smallint, > hits integer > ) > > But this generated abou

Re: [GENERAL] large database

On Dec 11, 2012, at 2:25 AM, Chris Angelico wrote: > On Tue, Dec 11, 2012 at 7:26 AM, Mihai Popa wrote: >> Second, where should I deploy it? The cloud or a dedicated box? > > Forget cloud. For similar money, you can get dedicated hosting with > much more reliable performance. We've been looking

Re: [GENERAL] PSA: XFS and Linux Cache Poisoning

On Nov 12, 2012, at 7:37 AM, Shaun Thomas wrote: > Hey everyone, > > We recently got bit by this, and I wanted to make sure it was known to the > general community. > > In new(er) Linux kernels, including late versions of the 2.6 tree, XFS has > introduced dynamic speculative preallocation. Wh

Re: [GENERAL] Streaming replication failed to start scenarios

On Oct 22, 2012, at 6:57 AM, chinnaobi wrote: > Hi Laurenz Albe, > > I have tested using cygwin rsync in windows 2008 R2, just after restart the > server. > > for 10 GB it took nearly 5 minutes to sync, > for 50 GB it took nearly 30 minutes, -- too long Though there were no big > changes. > >

Re: [GENERAL] Trajectory of a [Pg] DBA

On Oct 4, 2012, at 1:44 PM, Thalis Kalfigkopoulos wrote: > Hi all. > > I'd like to tap into the list's experience regarding the job of a DBA > in general and Pg DBA in particular. > > I see that most of the DBA job posts ask for Sr or Ssr which is > understandable given that databases are among

Re: [GENERAL] surprising behavior or nothing to see here?

On Oct 3, 2012, at 11:50 AM, Tom Lane wrote: > Ben Chobot writes: >> 4. What might cause autovacuum analyze to make an index perform worse >> immediately, when a manual vacuum analyze does not have the same affect? And >> I'm not talking about changing things so t

[GENERAL] surprising behavior or nothing to see here?

Today we saw a couple behaviors in postgres we weren't expecting, and I'm not sure if there's something odd going on, or this is all business as usual and we never noticed before. In steady-state, we have a 32-core box with a fair amount of ram acting as a job queue. It's constantly busy insert

[GENERAL] bgwriter and pg_locks

In an attempt to get a hackfix for http://pgfoundry.org/tracker/index.php?func=detail&aid=1011203&group_id=1000411&atid=1376, I'm wonder if it's true that, when looking at pg_locks, the only pid which will have virtualxid = '1/1' and virtualtransaction = '-1/0' will be the bgwriter. That seems

Re: [GENERAL] can we avoid pg_basebackup on planned switches?

On Aug 7, 2012, at 9:32 AM, Fujii Masao wrote: > On Mon, Aug 6, 2012 at 3:29 AM, Ben Chobot wrote: >> >> Oh, I would have though that doing a clean shutdown of the old master (step >> 1) would have made sure that all the unstreamed wal records would be flushed >>

Re: [GENERAL] maximum number of databases and / or schemas in a single database instance

On Aug 4, 2012, at 12:24 PM, Menelaos PerdikeasSemantix wrote: > The following page: > > http://www.postgresql.org/about/ > > mentions some limits but not the following: > > [1] maximum number of databases per database server instance > [2] maximum number of schemas per database > > Is there e

Re: [GENERAL] can we avoid pg_basebackup on planned switches?

On Aug 5, 2012, at 11:12 AM, Fujii Masao wrote: > On Sat, Jul 28, 2012 at 2:00 AM, Ben Chobot wrote: >> We make heavy use of streaming replication on PG 9.1 and it's been great for >> us. We do have one issue with it, though, and that's when we switch master

Re: [GENERAL] can we avoid pg_basebackup on planned switches?

Anybody? On Jul 27, 2012, at 10:00 AM, Ben Chobot wrote: > We make heavy use of streaming replication on PG 9.1 and it's been great for > us. We do have one issue with it, though, and that's when we switch master > nodes - currently, the documentation says that you must r

[GENERAL] can we avoid pg_basebackup on planned switches?

We make heavy use of streaming replication on PG 9.1 and it's been great for us. We do have one issue with it, though, and that's when we switch master nodes - currently, the documentation says that you must run pg_basebackup on your old master to turn it into a slave. That makes sense when the

Re: [GENERAL] Promotion of standby to master

On Jun 29, 2012, at 12:16 PM, Andy Chambers wrote: > I understand that it's possible to promote a "hot standby" pg server > simply by creating the failover file. In a scenario where there are > multiple standby servers, is it possible to point the other standby > servers to the new master without

Re: [GENERAL] Simple method to format a string?

On Jun 20, 2012, at 7:43 AM, Emi Lu wrote: > Good morning, > > Is there a simply method in psql to format a string? > > For example, adding a space to every three consecutive letters: > > abcdefgh -> *** *** *** > > Thanks a lot! > Emi I'm unaware of such a function (it seems like a generic f

Re: [GENERAL] evaluating subselect for each row

On Jun 3, 2012, at 10:55 AM, Scott Ribe wrote: > As part of anonymizing some data, I want to do something like: > > update foo set bar = (select bar2 from fakes order by random() limit 1); it may or may not be an option, but "update foo set bar=md5(bar)" is a pretty simple way to redact data. -

Re: [GENERAL] why jasperserver has been changed from MySQL to PostGreSQL

On May 22, 2012, at 7:31 AM, farhad koohbor wrote: > My question is that why jasperserver changed its mind to PostGreSQL. Which of > the features of PostGreSQL are powerful than MySQL? > Could you please give me a clue? Postgres is more SQL-compliant and tends to work better at larger scale than

Re: [GENERAL] .pgpass not working

On May 4, 2012, at 9:30 AM, Rebecca Clarke wrote: > I do not want to touch the pg_hba.conf so I have generated the .pgpass file. > The permissions is set to 600, and I have correctly inputted the details into > .pgpass, there are no leading spaces. > > myhostname:myport:*:postgres:mypassword >

[GENERAL] logging query result size?

I'm not seeing anything in the docs, but is there a way in 9.1 to log the size of the query result in the same way that we can log the duration of the query? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailp

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 > > crea

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

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

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 th

[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 que

Re: [GENERAL] configuring RAID10 for data in Amazon EC2 cloud?

On Mar 27, 2012, at 8:25 AM, Welty, Richard wrote: > does anyone have any tips on this? Linux Software Raid doesn't seem to be > doing a very good job here, but i may well have missed something. > iostat -x 5 is your friend. We've been struggling with a similar setup recently, and the TL;DR sum

Re: [GENERAL] current thinking on Amazon EC2?

On Mar 19, 2012, at 10:59 AM, Welty, Richard wrote: > i just finished this thread from May of last year, and am wondering if this > still represents consensus thinking about postgresql deployments in the EC2 > cloud: > > http://postgresql.1045698.n5.nabble.com/amazon-ec2-td4368036.html > Yes,

Re: [GENERAL] || versus concat( ), diff behavior

On Mar 2, 2012, at 12:58 PM, wrote: > Can anybody please point me to where this "difference of behavior" is > explained/documented ? > Thanks, > -dvs- > > -- version = 9.1.3 > do $$ > declare > v_str char(10); > begin > v_str := 'abc' ; > raise info '%', concat(v_str, v_str) ; > raise info

Re: [GENERAL] Re: One transaction by connection - commit subdetails without release master transaction?

On Feb 29, 2012, at 3:00 PM, Alban Hertroys wrote: > On 29 Feb 2012, at 20:44, Ben Chobot wrote: > >>> As I see the PGSQL supports one transaction per connection. Is this >>> information ok? >> >> Yes, in postgres a connection can support only one transa

Re: [GENERAL] Re: One transaction by connection - commit subdetails without release master transaction?

On Feb 29, 2012, at 4:11 AM, Durumdara wrote: > Dear Anybody! > > I replace the long question to some shorter: > > As I see the PGSQL supports one transaction per connection. Is this > information ok? Yes, in postgres a connection can support only one transaction. If you wish to reduce the num

[GENERAL] Streaming replication failover

I'm in the process of setting up a 9.1-based SR cluster, and I've got a question on how failover is expected to work in the case of multiple slaves. http://www.postgresql.org/docs/9.1/static/warm-standby-failover.html says: "Some people choose to use a third server to provide backup for the new

[GENERAL] checking for table bloat

As I'm sure many people know, check_postgres.pl has a wonderful (if rather arcane) query to check table bloat, which has been copied all over the intarwebz. When I try to use this query one one of my databases I'm told my table (which has had no deletes) is wasting a whole lot of bytes, but no a

Re: [GENERAL] invalid memory alloc request size

On Dec 26, 2011, at 8:08 AM, Ben Chobot wrote: > Yesterday I had a problem on a 64-bit 9.1.1 install: > > # select version(); >

[GENERAL] invalid memory alloc request size

Yesterday I had a problem on a 64-bit 9.1.1 install: # select version(); version ---

Re: [GENERAL] Initdb fails on openwrt in "creating template1 database"

On Dec 23, 2011, at 1:58 AM, Clemens Eisserer wrote: >>> There are about 800mb free space on /, any idea what could be the problem >>> here? >> >> Could you be running out of file system nodes? > > Don't think so - its a standard ext4 filesystem on a flush drive, > nothing special. > Also I've

Re: [GENERAL] Fwd: postgres 9.0.4 Streaming related question ..

On Dec 7, 2011, at 8:45 AM, akp geek wrote: > Small Clarification. I have gone through the documentation. I did not find > any thing related to start ans stop replication after the replication is setup > > 1. If I shutdown the primary > 2. Shutdown the slave > 3. start Primary > 4. start slave

[GENERAL] insert locking issue for PG 9.0

Our application has a table that looks like: create table jobs ( id int, first boolean ); What we need is for the app to be able to shove data into jobs with an assigned id, and guarantee that first is only true for one id. In other words, we could easily enforce what we want b

Re: [GENERAL] User feedback requested on temp tables usage for Hot Standby

On Oct 27, 2011, at 5:13 PM, Simon Riggs wrote: > Some people have asked for the ability to create temp tables on a Hot > Standby server. > > I've got a rough implementation plan but it would have some > restrictions, so I would like to check my understanding of the use > case for this feature so

Re: [GENERAL] WAL file size vs. data file size

On Oct 27, 2011, at 8:44 AM, Tom Lane wrote: > Ben Chobot writes: >> Today I tried to restore a 70GB database with the standard "pg_dump -h >> old_server <∑> | psql -h new_server <∑>" method. I had 100GB set aside for >> WAL files, which I figured

[GENERAL] WAL file size vs. data file size

Today I tried to restore a 70GB database with the standard "pg_dump -h old_server <…> | psql -h new_server <…>" method. I had 100GB set aside for WAL files, which I figured surely would be enough, because all of the data, including indices, is only 70GB. So I was a bit surprised when the restore

Re: [GENERAL] Any was to prevent a join if no columns are selected from a view?

On Sep 29, 2011, at 4:57 PM, Jason Long wrote: > I thought I had read somewhere that Postges could ignore a join if it > was not necessary because there were no columns from the table or view > selected in the query. Is this possible? This sounds like incorrect logic to me, so I would be surpris

Re: [GENERAL] Quick Date/Time Index Question

On Sep 22, 2011, at 2:13 PM, David Johnston wrote: > Hey, > > On 9.0.4 > > I have a database field that stores a timestamp to second+ precision; > however, I want to search against it only to day precision. If I leave the > field in second precision and try to “WHERE field BETWEEN date0 AND

Re: [GENERAL] Materialized views in Oracle

On Sep 21, 2011, at 1:17 PM, Mike Christensen wrote: > So I used to think materialized views in Postgres would be an awesome > feature. That is until I had to endure the hell hole which is Oracle's > implementation.. what a complete joke.. did MS SQL's indexed views do any > better? Hopeful

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

On Aug 31, 2011, at 11:53 AM, Ben Chobot wrote: > On Aug 31, 2011, at 11:10 AM, Tom Lane wrote: > >> Ben Chobot writes: >>> Tom, if there's anything else we can provide that might you out, let me >>> know. >> >> If you could extract a self-cont

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

On Aug 31, 2011, at 11:10 AM, Tom Lane wrote: > Ben Chobot writes: >> Tom, if there's anything else we can provide that might you out, let me know. > > If you could extract a self-contained test case for the bad estimation, > that would be useful. OK, we'll pul

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

On Aug 31, 2011, at 10:47 AM, Tom Lane wrote: > Peter Eisentraut writes: >> I don't have an answer for you, but this report looks suspiciously >> similar to the one I posted the other day at >> , >> which, now that I think about i

[GENERAL] regression between 8.4.8 and 8.4.2?

We recently took a copy of our production data (running on 8.4.2), scrubbed many data fields, and then loaded it onto a qa server (running 8.4.8). We're seeing some odd planner performance that I think might be a bug, though I'm hoping it's just idiocy on my part. I've analyzed things and looked

Re: [GENERAL] Syncing Data to Production DB Server

On Aug 18, 2011, at 5:36 AM, Adarsh Sharma wrote: > Dear All, > > I want some views on the below requirements : > > 1. I have a Postgres DB server with 25 GB database. It has more than 110 > tables. > I am using Postgresql 8.3 on a CentOs. > 2. I have another system laptop that contains the s

Re: [GENERAL] variant column type

On Jul 26, 2011, at 10:02 AM, salah jubeh wrote: > > Hello, > > suppose the following scenario > > the car speed is 240 > the car has an airbag > > Here the first value is integer and the second value is boolean. Consider > that I have this table structure > > feature (feature id feature n

Re: [GENERAL] Detecting memory leaks with libpq?

On Jul 19, 2011, at 6:28 AM, Craig Ringer wrote: > Note that some "leaks" that are reported are _normal_ in most software. There > is absolutely no harm in not free()ing a structure that's allocated only once > during init and never messed with afterwards. The OS clears the memory > anyway, so

Re: [GENERAL] Need suggestion

On Jun 1, 2011, at 1:08 AM, Carl von Clausewitz wrote: > Hello Everyone, > > I got a new project, with 100 user in Europe. In this case, I need to handle > production and sales processes an its documentations in PostgreSQL with PHP. > The load of the sales process is negligible, but every user

Re: [GENERAL] temp files getting me down

On May 25, 2011, at 7:36 PM, Merlin Moncure wrote: > On Wed, May 25, 2011 at 6:44 PM, Ben Chobot wrote: >> >> >> Well, the query itself was calling a plpgsql function, and the function >> itself was doing: >> >> DECLARE >>row

Re: [GENERAL] temp files getting me down

On May 25, 2011, at 2:57 PM, Merlin Moncure wrote: > On Wed, May 25, 2011 at 2:14 PM, Ben Chobot wrote: >> On May 25, 2011, at 9:31 AM, Ben Chobot wrote: >> >>> I'm running 9.0.3, and recently started getting temp files being created. >>> This is a proble

Re: [GENERAL] temp files getting me down

On May 25, 2011, at 9:31 AM, Ben Chobot wrote: > I'm running 9.0.3, and recently started getting temp files being created. > This is a problem because it's making a bunch of dirty buffers that have to > be flushed to disk and my poor little disk isn't up to the task. I&

[GENERAL] temp files getting me down

I'm running 9.0.3, and recently started getting temp files being created. This is a problem because it's making a bunch of dirty buffers that have to be flushed to disk and my poor little disk isn't up to the task. I'm not sure why though, because this is the explain verbose for the queries that

Re: [GENERAL] understanding pg_locks

On May 21, 2011, at 8:53 AM, Tom Lane wrote: > Ben Chobot writes: >> We recently had an issue where a misbehaving application was running a long >> transaction that modified a bunch of rows, and this was holding up other >> transactions that wanted to do similar modific

[GENERAL] understanding pg_locks

We recently had an issue where a misbehaving application was running a long transaction that modified a bunch of rows, and this was holding up other transactions that wanted to do similar modifications. No surprising there. But what I'm unclear of is how this was showing up in pg_locks. The bloc

Re: [GENERAL] Named advisory locks

On Apr 5, 2011, at 7:35 AM, rihad wrote: > No, what I meant was that we're already using ints for a different purpose in > another app on the same server, so I cannot safely reuse them. Aren't > advisory lock ID's unique across the whole server? The sole purpose of the > string ID is to be abl

Re: [GENERAL] multi-tenant vs. multi-cluster

On Mar 18, 2011, at 12:34 PM, Nicholson, Brad (Toronto, ON, CA) wrote: >>> b) its own postgresql processes (many of them) running in memory >> >> I believe this is entirely a function of client connections. > > With a single instance, you can use connection pooling to reduce the overall > numb

Re: [GENERAL] multi-tenant vs. multi-cluster

On Mar 18, 2011, at 11:47 AM, Ivan Voras wrote: > On 18/03/2011 19:17, Ben Chobot wrote: > >> if we're talking an extra 50MB of memory per cluster, that will start to add >> up. > > Consider this: each such cluster will have: > > a) its own database files on

[GENERAL] multi-tenant vs. multi-cluster

We're considering using postgres as a way to host database services for many, many independent applications. One obvious way to do this is with schemas, roles, and proper permissions, but that still leaves open the possibility for some poorly written application to leave open transactions and af

Re: [GENERAL] Hit by the out of memory killer last night

On Jan 31, 2011, at 7:55 AM, Bryan Murphy wrote: > Last night we were hit by the out of memory killer. Looking at the following > graph, you can clearly see unusual memory growth. This is a database server > running Postgres 9.0.0. [snip] > Any advice? What should I be looking for? Any pa

Re: [GENERAL] Postgresql as a dictionary coder backend?

On Jan 23, 2011, at 3:29 AM, Attila Nagy wrote: > Hello, > > I'm looking for a database backend for a dictionary coder project. It would > have three major tasks: > - take a text corpus, get their words and substitute each word by a 64 bit > integer (the word:integer is always constant) and sto

Re: [GENERAL] Inconsistent time interval formatting

On Jan 13, 2011, at 1:15 PM, John R Pierce wrote: > On 01/13/11 1:08 PM, Ben Chobot wrote: >> On Jan 13, 2011, at 11:03 AM, Tom Lane wrote: >> >>> If you don't care about that, you can use justify_hours (I think that's >>> the right function

Re: [GENERAL] Inconsistent time interval formatting

On Jan 13, 2011, at 11:03 AM, Tom Lane wrote: > If you don't care about that, you can use justify_hours (I think that's > the right function) to smash them to the same thing. I use justify_hours, and I still get entries like '1 day 35:31:10' intermixed with the entires I'd expect like '2 days 03

Re: [GENERAL] Inconsistent time interval formatting

On Jan 13, 2011, at 9:34 AM, Allen Chen wrote: > Has anyone else out there noticed inconsistencies in how pgsql formats time > intervals over 1 day? > > For example, I have a query that returns a column of intervals and I get > output like this: > > 30:30:00 > 1 day 03:02:47 > 1 day 01:38:34 >

Re: [GENERAL] Tool for data modeling and ER diagram

On Dec 7, 2010, at 10:39 AM, Jaiswal Dhaval Sudhirkumar wrote: > Hi List, > > What is the best tool of data modeling and ER diagram for PostgreSQL. > http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools

Re: [GENERAL] how can i bugfix "idle in transaction" lockups ?

On Nov 30, 2010, at 7:21 AM, Jonathan Vanasco wrote: > on a project, i find myself continually finding the database locked up with > "idle in transaction" connections > > are there any commands that will allow me to check exactly what was going on > in that transaction ? > > i couldn't find an

Re: [GENERAL] PostgreSQL hanging on new connections?

On Nov 29, 2010, at 12:57 PM, Vick Khera wrote: > On Mon, Nov 29, 2010 at 1:23 PM, Tom Lane wrote: >> hubert depesz lubaczewski writes: >>> straced postmaster when the problem was happening, and I was opening new >>> connections. strace looks like this: >>> [ backend hangs on semop immediately a

Re: [GENERAL] Integral PG DB replication

On Jul 26, 2010, at 9:09 AM, Gauthier, Dave wrote: > Hi: > > Will DB replication be integral in v9? If so, when (approx) will that be out? > > I have a need for this functionality to replicate a read-only copy of a DB > where the master and slave are 2 time zones away. Estimating DML traffi

Re: [GENERAL] Getting statistics for each sql statement?

On Jul 22, 2010, at 4:50 AM, Stefan-Michael Guenther wrote: > Hello, > > is it possible to get statistics on the usage of different sql statements, > e.g. how many INSERT or UPDATE statements per day? > > log_statement_stats doesn't seem to be the right parameter or I haven't found > the outpu

Re: [GENERAL] text vs. varchar

On Jul 21, 2010, at 9:05 AM, Thom Brown wrote: > On 21 July 2010 16:58, Ben Chobot wrote: >> Is there any difference between "text" and "varchar" data types? (Not >> varchar(n), just varchar.) I can't see a different from the manual page, but >>

[GENERAL] text vs. varchar

Is there any difference between "text" and "varchar" data types? (Not varchar(n), just varchar.) I can't see a different from the manual page, but I'm wondering about index usage or something similarly subtle. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] Efficient Way to Merge Two Large Tables

On Jul 13, 2010, at 1:46 PM, Joshua Rubin wrote: > Hi, > > I have two tables each with nearly 300M rows. There is a 1:1 > relationship between the two tables and they are almost always joined > together in queries. The first table has many columns, the second has > a foreign key to the primary k

Re: [GENERAL] getting the last N tuples of a query

On Jul 8, 2010, at 4:17 PM, Edmundo Robles L. wrote: > Hi! > > if a want the first 5,10,N tuples of a query (even without order) > i just have to do a: > select * from table limit 10; That does not get the first 10 tuples, it merely gets 10 tuples. The database is free to return whiche

Re: [GENERAL] Count actual transaction per minute?

On May 12, 2010, at 9:12 AM, Melvin Davidson wrote: > Can anyone tell me how to measure _actual_ transactions per minute on a > PostgreSQL server. I am not talking about using pgbench, as I am not > interested in determining what is possible, but rather the actual count of > queries / transacti

Re: [GENERAL] how to invalidate a stored procedure's plan?

On Apr 23, 2010, at 6:00 PM, Tom Lane wrote: > Ben Chobot writes: >> I have a procedure that queries a table. This should be fast because of an >> index, but some index bloat has caused the index to become expensive, and so >> the procedure has cached a plan that uses a

  1   2   >