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

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

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

Re: [GENERAL] pgpass (in)flexibility

On Sep 15, 2015, at 12:27 AM, Jim Nasby <jim.na...@bluetreble.com> 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: >>

[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

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

[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 pie...@hogranch.com 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

Re: [GENERAL] Optimizing tables for known queries?

On Feb 9, 2014, at 2:48 PM, John Anderson son...@gmail.com 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, we replicate a

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 it. Does it make

Re: [GENERAL] 9.1.9 - 9.1.10 causing corruption

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 them to 9.1.10, but on 3 of the 3 I've

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

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

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 by changing

[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

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

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? If

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

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 curious

[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

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 the

[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

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 indexes

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 about 1M rows per

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 mi...@lattica.com 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

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. What

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. My

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 a

[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

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

On Oct 3, 2012, at 11:50 AM, Tom Lane wrote: Ben Chobot be...@silentmedia.com 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 the planner

[GENERAL] bgwriter and pg_locks

In an attempt to get a hackfix for http://pgfoundry.org/tracker/index.php?func=detailaid=1011203group_id=1000411atid=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 be...@silentmedia.com 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 to any connected

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 empirical

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 be...@silentmedia.com 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

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 run pg_basebackup

[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 format()

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:

[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

Re: [GENERAL] Updates, ShareLocks, RowExclusiveLocks, and deadlocks

On Apr 25, 2012, at 12:35 PM, Tom Lane wrote: Ben Chobot be...@silentmedia.com 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

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

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

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, I

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

On Mar 2, 2012, at 12:58 PM, david.sahag...@emc.com 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) ;

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 number

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 transaction. A small correction: a connection can

[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

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(); 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 added about

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

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

On Oct 27, 2011, at 8:44 AM, Tom Lane wrote: Ben Chobot be...@silentmedia.com 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 surely would be enough, because all

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 I

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

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 surprised

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? Hopefully

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

On Aug 31, 2011, at 10:47 AM, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: I don't have an answer for you, but this report looks suspiciously similar to the one I posted the other day at http://archives.postgresql.org/pgsql-hackers/2011-08/msg01224.php, which, now that I think

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

On Aug 31, 2011, at 11:10 AM, Tom Lane wrote: Ben Chobot be...@silentmedia.com 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 pull something

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 be...@silentmedia.com 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

[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

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 same

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 name) car

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

[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

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'm not sure why though

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 be...@silentmedia.com 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 problem because it's making a bunch

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 be...@silentmedia.com wrote: Well, the query itself was calling a plpgsql function, and the function itself was doing: DECLARE row formatted_replication_queue%ROWTYPE; BEGIN

Re: [GENERAL] understanding pg_locks

On May 21, 2011, at 8:53 AM, Tom Lane wrote: Ben Chobot be...@silentmedia.com 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 modifications

[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

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 able

[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

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 the drives (WAL, data - increasing IO) Oh

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 number of

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

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 store

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

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) to smash them to the same thing. I use justify_hours, and I

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 anything

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 t...@sss.pgh.pa.us wrote: hubert depesz lubaczewski dep...@depesz.com writes: straced postmaster when the problem was happening, and I was opening new connections. strace looks like this: [ backend

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 traffic,

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 output

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

Re: [GENERAL] text vs. varchar

On Jul 21, 2010, at 9:05 AM, Thom Brown wrote: On 21 July 2010 16:58, Ben Chobot be...@silentmedia.com 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 I'm wondering about index usage

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 key of

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 whichever

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 / transactions

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

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 full table scan. I've since fixed the index bloat, but the procedure still seems to be doing full

  1   2   >