[GENERAL] Is pgbouncer still maintained?

Hi all, I hope I'm posting this in the correct place. We use pgbouncer, as I'm sure many other people do, but we are becoming increasingly worried by the lack of a new release since February 2016 and a slowdown in development activity on the master branch. https://github.com/pgbouncer/pgbouncer

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

codes. Regards, Steven 2017-07-03 9:18 GMT+08:00 Michael Paquier : > On Mon, Jul 3, 2017 at 10:08 AM, Steven Chang > wrote: > > Hello : > > Please avoid top-posting. > > >PG VERSION : PPAS 9.3 , enterprisedb > >os version : 2.6.32-358.el6.x86_64 &g

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

1. I found this document : https://www.postgresql.org/message-id/20140811083748.2536.10437%40wrigleys.postgresql.org 2. Return only on row if query data using where equal condition, but got 2 rows by like condition Steven 2017-07-01 22:05 GMT+08:00 Adrian Klaver : > On 06/30/2017 09:42 PM

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

Uh...we also met duplicate rows with primary key column through restoring database by pg_basebackup. H. I don't think its an issue with primary key index corruption. 2017-07-01 7:30 GMT+08:00 Adrian Klaver : > On 06/30/2017 07:33 AM, Timokhin Maxim wrote: > >> Sure,

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

Interesting!! We also met the same situation on PK running on PPAS 9.0 last night. When surfing Internet, got returned this URL : https://www.postgresql.org/message-id/20140811083748.2536.10437%40wrigleys. postgresql.org

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

On Sun, Jun 11, 2017 at 8:21 AM, Tom Lane wrote: > Yeah, I've been watching this thread and trying to figure out how to > explain that part; I suspected a cause of this form but couldn't > make that theory match the 9-iterations observation. (I still can't.) > I walked through the Java code in

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

On Sat, Jun 10, 2017 at 11:10 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > https://www.postgresql.org/docs/current/static/sql-prepare.html > > Specifically, the notes section. That seems to fit the behavior. Thanks; I wasn't aware of that feature of prepared statements. I change

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

The problem doesn't appear to be specific to the JDBC driver. Tried a quick version of this in Python for grins with a database that was already populated by the Java code (sadly, the psycopg2 library doesn't directly support prepared statements): import psycopg2 import time conn = psycopg2.conn

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

On Sat, Jun 10, 2017 at 1:46 AM, Alban Hertroys wrote: > I notice that you're declaring your ResultSet variable inside the loop, which means that you create and destroy it frequently. I've been told that this is a pattern that the GC has trouble keeping up with (although that was around the Java 5

[GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

PostgreSQL 9.6.3 on OS X Sierra, JDBC driver version 42.1.1. I noticed that one of my queries was slowing down after a few invocations. Narrowed it down to an issue with bind variables and LIKE conditions. Very consistently, on a given connection, the first 9 times a SELECT containing a LIKE whose

Re: [GENERAL] dump to pg

hello, not possible in your case. To my understading, no tool can directly transform binary dump(Oralce & MSSQL) to csv file format. Steven 2017-06-02 5:37 GMT+08:00 Nicolas Paris : > > If they aren't too big, you might get away by installing the express > edition

Re: [GENERAL] index duplicates primary key, but is used more?

also affected by some parameters. Steven 2017-06-03 7:02 GMT+08:00 jonathan vanasco : > i'm doing a performance audit and noticed something odd. > > we tested a table a while back, by creating lots of indexes that match > different queries (30+). > > for simplicity, he

Re: [GENERAL] Current best practice for maximum shared_buffers settings on big hardware?

study and test for developing your own experiences . Best Regards, Steven 2017-05-25 1:34 GMT+08:00 Scott Marlowe : > On Wed, May 24, 2017 at 6:24 AM, Bill Moran > wrote: > > > > A few years ago, I was working with "big" servers. At least, they were > > big for tha

[GENERAL] shard_manager extension on PGXN

, but also text or other type. And you don't present how to get the target partition through function in usage section. Would you mind releasing more details ? Regards, Steven

Re: [GENERAL] [ADMIN] calculating table and index size

Hello, try pgstattuple() and pgstatindex() , I think you will figure it out. Steven 2017-04-05 16:56 GMT+08:00 Guillaume Lelarge : > Hi, > > 2017-04-05 9:44 GMT+02:00 Günce Kaya : > >> Hi all, >> >> I have some questions about calculating table and index si

Re: R: [GENERAL] Slow queries on very big (and partitioned) table

> Unfortunately, that'll require locking each table and scanning it to make > sure that the CHECK constraint isn't violated. Actually, CHECK constraints can be added with the NOT VALID clause. New tuples will be checked immediately, while the validation of existing tuples can be done later usin

Re: [GENERAL] Build exclusion constraints USING INDEX

> I was wondering if there was any way to break down the creation of a new > exclusion constraint into stages such that table locks most likely to affect > performance during production hours are not taken. > > Something like: > > CREATE INDEX CONCURRENTLY new_index ON my_table USING gist (column

[GENERAL] Build exclusion constraints USING INDEX

rently available, but I wanted to check that and see if there are any workarounds. Thanks, Steven.

[GENERAL] Row value expression much faster than equivalent OR clauses

A library my application is using does a "scan a batch at a time" loop over a table of events, keeping track of its last position so it can start the next query in the right place. SELECT eventIdentifier, aggregateIdentifier, sequenceNumber, timeStamp, payloadType, payloadRevision, payloa

Re: [GENERAL] Request to share approach during REINDEX operation

>>> In my production system, there are lot of read write operation performed >>> every hour. >>Apologies if this sounds patronising but I just wanted to check - you know >>that indexes are updated automatically when write operations occur, right? >Yes. You are correct. OK - I think the folks her

Re: [GENERAL] Request to share approach during REINDEX operation

> In my production system, there are lot of read write operation performed > every hour. Apologies if this sounds patronising but I just wanted to check - you know that indexes are updated automatically when write operations occur, right? This email is confidential. If you are not the intende

Re: [GENERAL] Moving pg_xlog

ICT) just creates the symlink for you. Steve. Dr. Steven Winfield Scientist D: +44 (0)1223 755 776 [Cantab email sig]

Re: [GENERAL] What is the 'data2' directory for?

On Tue, 25 Oct 2016, Tom Lane wrote: Steven Hirsch writes: I notice that PostgreSQL (both 9.5.4 and 9.6) create a 'data2' directory as a peer to the 'data' directory I specified at database initialization. What is this directory for and what parameter determines its l

[GENERAL] What is the 'data2' directory for?

I notice that PostgreSQL (both 9.5.4 and 9.6) create a 'data2' directory as a peer to the 'data' directory I specified at database initialization. What is this directory for and what parameter determines its location? -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) T

[GENERAL] pglogical Replication CONFLICT

I am using PostgreSQL 9.4 with pglogical replication. I have 1 provider node and 1 subscriber in 2 separate locations. The subscriber subscribes to 1 database on the provider. Every evening a function runs that DELETE's & INSERT's to tables that are replicated to the subscriber and causes CONFLI

Re: [GENERAL] Very slow update / hash join

er, tuning the database to accomodate them is a better bet. It would also be worth checking whether the I/O was entirely due to sequential reads or may have been swapping. procinfo, vmstat, or just top can tell you about that. -- Steven Lembark

[GENERAL] Trying to create array of enum to array of text for exclusion constraint

map { $1::text }" in plsql. */ $$ ; or is there something built in that I have missed? Note: Performance will not be an issue here as the table is not updated all that frequently. Any references appreciated. -- Steven Lembark 364

Re: [GENERAL] Custom column ordering

I see. Thanks again! I removed the "port" data type from my implementation and just used the ORDER BY... USING clause and the explain output/results is providing what I expect.StevenThat is an editing mistake.  I mean> hasegeli=# SELECT port FROM device_port ORDER BY cast_to_port(port);

Re: [GENERAL] Custom column ordering

rather than the "text" type, even though you haven't performed a cast?Steven-Emre Hasegeli wrote: -To: Steven Xu From: Emre Hasegeli Date: 03/05/2016 09:30AMCc: "pgsql-general@postgresql.org" Subject: Re: [GENERAL] Custom column ordering>   - Why is PostgreSQL n

[GENERAL] Custom column ordering

Hi all,I'm trying to order some rows based on port names, a text column, using some domain-specific knowledge for Netdisco, an open-source application. In particular, I'm trying to do this without having to redo the entire design for the database. Note that in this database, there are no foreign ke

Re: [GENERAL] master slave failover - secondary slaves

Thanks Andreas - that looks ideal. Steven Livingstone wrote: > Hi all, I am relatively new to Postgres but after some some work master/slave > replication and failover working. > > I can use a trigger file to promote my first slave to a new master but where I > am confused (from

[GENERAL] master slave failover - secondary slaves

new master and point to it for replication etc. One place says it should be automatic (I don't find that) and another suggests you need to reconfigure each slave to point to the new Master (and I guess restart each). Can anyone clear this up? Many Thanks, steven

Re: [GENERAL] Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

David Rowley November 14, 2015 at 12:32 AM The problem is that the optimizer is unable to use hash join or merge joins when you have the IN() condition as the join condition, the reason for this is that you're effectively saying to join on any of 3 condit

Re: [GENERAL] Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

David G. Johnston November 13, 2015 at 10:48 PM ​ ​IN semantics w.r.t NULL can result in atrocious performance in some instances. I cannot speak to this one in particular but I'm curious if [...] WHERE setting_id = 1 AND setting_value = 'common_1' AND ( ow

[GENERAL] Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

We have a table, call it "multi_id", that contains columns with IDs of various kinds of objects in my system, and another table that's a generic owner/key/value store for object attributes (think configuration settings, and I'll refer to this table as "settings"). To wit: -

[GENERAL] Effecient time ranges in 9.4/9.5?

type.html> [2] <http://www.postgresql.org/docs/9.4/static/rangetypes.html> [3] <http://stackoverflow.com/questions/28017891/postgres-custom-range-type> [4] <http://grokbase.com/t/postgresql/pgsql-general/128355kvhc/range-types-in-9-2> [5] <https://wiki.postgresql.org/wiki/Working_wit

Re: [GENERAL] partitoning expert : Partitonning with specialization of one column type

Your child could be: CREATE TABLE child-1( patchn pcpatchn ) INHERITS (father); From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rémi Cura Sent: Wednesday, April 01, 2015 8:41 AM To: PostgreSQL General Subject: [GENERAL] partitoning expert : Par

Re: [GENERAL] Autovacuum query

Another option, depending on the nature of the data and deletes, would be to partition the table. I created 7 tables that inherited from the original, one table for each day of the week. A nightly cron job then runs, leaving alone yesterday's and today's tables but truncating the other 5. Run

[GENERAL] Populating missing dates in postgresql data

No I need to do a count of comm failures by day, so I need to populate the check-in date field. Please help! Best Regards, Steve ***** Steven Lavrenz, MS, EIT Doctoral Research Fellow, Ph.D. Candidate Purdue University | Transportation Engineering H

Re: [GENERAL] Name spacing functions and stored procedures

I’ve always used schemas – usually one for each of the business processes. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Pavel Stehule Sent: Thursday, March 19, 2015 12:38 AM To: Tim Uckun Cc: pgsql-general Subject: Re: [GENERAL] Name spacing fu

Re: [GENERAL] Basic Question on Point In Time Recovery

incremental pg_dump's onto tmpfs. Advantage there is that the dump iteslf has effectively no write I/O overhead: you can dump to tmpfs and then [bg]zip to stable storage w/o beating up the disks, which becomes a real problem with comodity-grade hardware. -- Steven Lembark

[GENERAL] Constraints and inheritance

I have a table that has over 100K rows of GIS data, including a raster and an insertdatetime timestamp columns. This table is continually loaded with data with processes on the back side querying the data and populating other tables depending on characteristics of the data. Today a row is rea

[GENERAL] Last reclustering time

Is there any way to find when a table was last successfully CLUSTER'd, say from pg_catalog? Last analyzed and last vacuumed times are available, but I can't seem to find anything to do with reclustering. Thanks, Steve.

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

>> On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane wrote: >> Susan Cassidy writes: >> > Is there any way to let a transaction "see" the inserts that were done >> > earlier in the transaction? >> >> It works that way automatically, as long as you're talking about separate >> statements within one tra

Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?

On Apr 16, 2014, at 4:27 PM, Susan Cassidy wrote: > Is there any way to let a transaction "see" the inserts that were done > earlier in the transaction? I want to insert a row, then later use it within > the same transaction. > > If not, I will have to commit after each insert, and I don't

Re: [GENERAL] Is it safe to stop postgres in between pg_start_backup and pg_stop_backup?

On Apr 2, 2014, at 3:08 PM, Jacob Scott wrote: > Hi, Hello there ;) > > > Does upgrading a a disk being used by postgres (9.1, on Ubuntu) with the > following process sound safe? > • pg_start_backup > • Take a filesystem snapshot (of a volume containing postgres data but > not

Re: [GENERAL] Trimming transaction logs after extended WAL archive failures

On Mar 27, 2014, at 5:29 AM, Michael Paquier wrote: > On Thu, Mar 27, 2014 at 1:42 AM, Steven Schlansker > wrote: >> >> On Mar 25, 2014, at 7:58 PM, Adrian Klaver wrote: >>>> >>> Yea, vacuum just marks space as available for reuse it does not actua

Re: [GENERAL] Trimming transaction logs after extended WAL archive failures

On Mar 26, 2014, at 9:04 AM, Jeff Janes wrote: > On Tue, Mar 25, 2014 at 6:33 PM, Jeff Janes wrote: > On Tuesday, March 25, 2014, Steven Schlansker wrote: > Hi everyone, > > I have a Postgres 9.3.3 database machine. Due to some intelligent work on > the part of someon

Re: [GENERAL] Trimming transaction logs after extended WAL archive failures

On Mar 25, 2014, at 7:58 PM, Adrian Klaver wrote: > On 03/25/2014 04:52 PM, Steven Schlansker wrote: >> > >>> Some more questions, what happens when things begin to dawn on me:) >>> >>> You said the disk filled up entirely with log files yet currently t

Re: [GENERAL] Trimming transaction logs after extended WAL archive failures

On Mar 25, 2014, at 4:45 PM, Adrian Klaver wrote: > On 03/25/2014 04:17 PM, Steven Schlansker wrote: >> >> On Mar 25, 2014, at 4:02 PM, Adrian Klaver wrote: >> >>> On 03/25/2014 03:54 PM, Steven Schlansker wrote: >>>> >>>> O

Re: [GENERAL] Trimming transaction logs after extended WAL archive failures

On Mar 25, 2014, at 4:02 PM, Adrian Klaver wrote: > On 03/25/2014 03:54 PM, Steven Schlansker wrote: >> >> On Mar 25, 2014, at 3:52 PM, Adrian Klaver wrote: >> >>> On 03/25/2014 01:56 PM, Steven Schlansker wrote: >>>> Hi everyone, >>>>

Re: [GENERAL] Trimming transaction logs after extended WAL archive failures

On Mar 25, 2014, at 3:52 PM, Adrian Klaver wrote: > On 03/25/2014 01:56 PM, Steven Schlansker wrote: >> Hi everyone, >> >> I have a Postgres 9.3.3 database machine. Due to some intelligent work on >> the part of someone who shall remain nameless, the WAL arc

[GENERAL] Trimming transaction logs after extended WAL archive failures

o fix this situation, however I already know that the response from the mailing list will be “that’s insane, don’t do that”. So what is the correct solution to pursue here? The steady state of the machine should have enough space, I just need to reclaim some of it... Thanks for any guidanc

Re: [GENERAL] [JDBC] Cannot insert to 'path' field using EclipseLink

> On Mar 12, 2014, at 10:12 AM, Daryl Foster wrote: > > java.lang.ClassCastException: org.postgresql.geometric.PGpath cannot be cast > to org.postgresql.geometric.PGpath > That's a sure sign of ClassLoader confusion. Make sure there is only one copy of the driver jar in your application or

Re: [GENERAL] Timing for release with fix for "Nov2013ReplicationIssue"

On Dec 3, 2013, at 2:15 PM, Tom Lane wrote: > Steven Schlansker writes: >> I’ve seen murmuring on the list regarding >> https://wiki.postgresql.org/wiki/Nov2013ReplicationIssue > >> Is there an ETA on a release with the bug fix for this? I’m putting off >>

[GENERAL] Timing for release with fix for "Nov2013ReplicationIssue"

might force my hand :) Thanks, Steven -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Does LC_CTYPE affect performance, index use?

uot;, and using LOWER() to case-fold strings for sorting, and equality. The motivation for setting LC_CTYPE = "en_US.UTF-8", instead of "C", is to gain at least some degree of case-folding for international characters. Does this sound like a reasonable plan? Thanks, Steven

Re: [GENERAL] Trying to create DB / user to import some data

On Sep 26, 2013, at 10:28 PM, mdr wrote: > > create user import_dbms_user with password 'import_dbms'; > create database import_dbms_db; > grant all privileg > However when I try to run psql from the command line: > psql -h localhost -U import_dbms_user -WI enter password when prompted > Passwo

Re: [GENERAL] JDBC driver for Postgres 9.3

On Sep 26, 2013, at 6:35 AM, "Kohler Manuel" wrote: > Hi, > we are developing a Java based software with Postgres as a DB. > Could someone tell me if there will be a JDBC driver for 9.3 out soon or > is it safe and recommended to use the latest JDBC driver available? > Currently we are using: >

Re: [GENERAL] Blowfish Encrypted String

On Sep 26, 2013, at 10:55 AM, Craig Boyd wrote: > Dmitriy, > > Thank you very much for the reply! > > Right...got the the data type...but how do I actually insert a binary string > into the column? What does the SQL look like? > For the moment assume I have the following bit of binary: 4PO

Re: [GENERAL] Deduplication and transaction isolation level

On Sep 25, 2013, at 6:04 AM, Merlin Moncure wrote: > On Tue, Sep 24, 2013 at 10:19 PM, François Beausoleil > wrote: >> Hi all! >> >> I import many, many rows of data into a table, from three or more computers, >> 4 times per hour. I have a primary key, and the query I use to import the >> da

Re: [GENERAL] ZFS snapshots - to use pg_start_backup() and pg_stop_backup() or no?

On Sep 11, 2013, at 4:29 PM, Gregory Haase wrote: > I was trying to figure out how to get the following syntax to work: > > echo "select pg_start_backup('zfs_snapshot'); \\! zfs snapshot > zroot/zpgsql@test; \\ select pg_stop_backup();" | psql postgres I do: psql -c "select pg_start_backup('

Re: [GENERAL] Best Postgresql books

PostgreSQL 9.0 High Performance: http://www.amazon.com/books/dp/184951030X On Jul 18, 2013, at 3:11 PM, Pedro Costa wrote: > Hi guys, > > Can anyone tell me the best books about postgresql? Specialy about tunning > and querys performances > Thanks > > > > Enviado do meu tablet Samsung

Re: [GENERAL] backend hangs at sendto() and can't be terminated

rupt to quit 00:02:00 recvfrom(10, postgres 24402 0.0 10.7 3505628 2639032 ? Ss Jul01 0:21 postgres: event event 10.29.62.21(39485) idle It is a psql process that I launched from the command line 10 days ago: steven 24401 0.0 0.0 166824 2532 pts/2TJul01 0:00 psql -U event

Re: [GENERAL] backend hangs at sendto() and can't be terminated

o quit 00:02:00 recvfrom(10, postgres 24402 0.0 10.7 3505628 2639032 ? Ss Jul01 0:21 postgres: event event 10.29.62.21(39485) idle It is a psql process that I launched from the command line 10 days ago: steven 24401 0.0 0.0 166824 2532 pts/2TJul01 0:00 psql -U event

Re: [GENERAL] unique constraint violations

On Jun 26, 2013, at 11:04 AM, pg noob wrote: > > Hi all, > > There are some places in our application where unique constraint violations > are difficult to avoid due to multithreading. > What we've done in most places to handle this is to retry in a loop. > > Generally it starts by checking

Re: [GENERAL] Index over only uncommon values in table

On Jun 18, 2013, at 2:29 PM, Jeff Janes wrote: > On Tue, Jun 18, 2013 at 12:17 PM, Steven Schlansker > wrote: > Hi everyone, > > I assume this is not easy with standard PG but I wanted to double check. > > I have a column that has a very uneven distribution of values.

Re: [GENERAL] Index over only uncommon values in table

On Jun 18, 2013, at 1:49 PM, David Johnston wrote: > Steven Schlansker-3 wrote >> At some point, the code changes, and CURRENT_VERSION gets incremented. >> Rows then slowly (over a period of days / weeks) get "upgraded" to the new >> current version, in batch

Re: [GENERAL] Index over only uncommon values in table

On Jun 18, 2013, at 12:23 PM, John R Pierce wrote: > On 6/18/2013 12:17 PM, Steven Schlansker wrote: >> 1) The common value is not known at schema definition time, and may change >> (very slowly) over time. > > how could a value thats constant in 95% of the rows change, u

[GENERAL] Index over only uncommon values in table

bined with prepared statements, sadly…) The table size is expected to approach the 0.5 billion row mark within the next few months, hence my eagerness to save even seemingly small amounts of per-row costs. Curious if anyone has a good way to approach this problem. Thanks, Steven -- Sent

Re: [GENERAL] upsert functionality

On May 15, 2013, at 11:52 PM, Thomas Kellerer wrote: > Sajeev Mayandi, 16.05.2013 07:01: >> Hi, >> >> Our company is planning to move to postreSQL. We were initially using >> sybase where upsert functionality was available using "insert on >> existing update" clause. I know there multiple ways

Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID

796.98 BogoMIPS).(2 socket x 2 hyperthread x 6 cores) 96GB ECC RAM Filesystem is ext4 on LVM on hardware RAID 1+0 Adaptec 5405 Database is very much read heavy, but there is a base load of writes and bursts of much larger writes. I don't have specifics regarding how it breaks dow

Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID

On May 10, 2013, at 11:38 AM, Merlin Moncure wrote: >> >> PostgreSQL configuration changes: >> synchronous_commit = off >> > > that's good info, but it should be noted that synchronous_commit > trades a risk of some data loss (but not nearly as much risk as > volatile storage) for a big increa

Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID

egments = 1024 checkpoint_timeout = 10min checkpoint_warning = 8min shared_buffers = 32gb temp_buffers = 128mb work_mem = 512mb maintenance_work_mem = 1gb Linux sysctls: vm.swappiness = 0 vm.zone_reclaim_mode = 0 vm.dirty_bytes = 134217728 vm.dirty_background_bytes = 1048576 Hope that helps, St

Re: [GENERAL] Simple SQL INSERT to avoid duplication failed: why?

On May 1, 2013, at 9:36 AM, "Carlo Stonebanks" wrote: > I have to ask myself, is it more likely that I have discovered some PG > anomaly in 9.0 that no one has ever noticed, or that the client has > accidentally launched the process twice and doesn't know it? Given my (admittedly limited) exper

Re: [GENERAL] Simple SQL INSERT to avoid duplication failed: why?

is SSI -- http://wiki.postgresql.org/wiki/Serializable which means that if you run this transaction at isolation level SERIALIZABLE you will get serialization failures instead of duplicate key exceptions, which makes it easy to retry until success. So now we run any code that looks like this a

Re: [GENERAL] pg_restore from split files

On Apr 22, 2013, at 12:47 PM, akp geek wrote: > pg_dump dbname -n schemaname -t table_name -Fc | split -b 500m -t table.dump Since you split the files outside of the Postgres world, you have to combine them again. Roughly, cat table.dump.* > table.dump.combined pg_restore --usual-arguments t

[GENERAL] Using an index for IS DISTINCT FROM queries

Index Cond: (unpacker_version IS NULL) (10 rows) but this sucks to do such tuning by munging the query. Is there some case where these are not equivalent? If they are equivalent, would this be a reasonable case for the planner to consider and optimize by itself? Thanks, Steven --

Re: [GENERAL] Queries seldomly take >4s while normally take <1ms?

On Apr 9, 2013, at 11:25 AM, Scott Marlowe wrote: > One of the most common causes I've seen for this is linux's vm.*dirty* > settings to get in the way. Like so many linux kernel "optimizations" this > one looks good on paper but gives at best middling improvements with > occasional io storms

Re: [GENERAL] Testing Technique when using a DB

On Mar 12, 2013, at 8:09 PM, Joe Van Dyk wrote: > On Mar 12, 2013, at 8:42 AM, Perry Smith wrote: > >> >> The other thought is perhaps there is a "snap shot" type concept. I don't >> see it in the list of SQL commands. A "snap shot" would do exactly what it >> sounds like. It would take

Re: [GENERAL] best config

On Feb 6, 2013, at 9:55 AM, Roberto Scattini wrote: > > hi steven, > > > we have two new dell poweredge r720. based on recommendations from this > > list we have configued the five disks in raid10 + 1 hot spare. > > You might mention a bit more about how you

Re: [GENERAL] best config

On Feb 6, 2013, at 8:14 AM, Roberto Scattini wrote: > hi list, > > we have two new dell poweredge r720. based on recommendations from this list > we have configued the five disks in raid10 + 1 hot spare. You might mention a bit more about how your drives are configured. 5 drives in a RAID1+

[GENERAL] Using partial index in combination with prepared statement parameters

or otherwise). I ask largely because we use prepared statements everywhere and this makes it very hard to use partial indices, which would offer us significant performance gains. Does anyone know of any acceptable workaround? Is there continued interest in maybe improving the PostgreSQL behavior

Re: [GENERAL] Combine Date and Time Columns to Timestamp

e: 2012-10-29 | 10:19 | 2012-10-30 | 09:40 steven=# create temporary table date_test (d varchar, t varchar); CREATE TABLE steven=# insert into date_test values('2010-08-23', '8:04:33'); INSERT 0 1 steven=# select d::date + t::interval from date_test; ?colum

Re: [GENERAL] Noticed something odd with pgbench

On Nov 16, 2012, at 11:59 AM, Richard Huxton wrote: > On 16/11/12 19:35, Shaun Thomas wrote: >> Hey guys, >> >> So, we have a pretty beefy system that runs dual X5675's with 72GB of RAM. >> After our recent upgrade to 9.1, things have been... odd. I managed to track >> it down to one setting:

[GENERAL] Range constraint with unique index

I have a table that has an integer and a int8range. What I want is to add a constraint that stops anyone adding This email transmission is confidential and intended solely for the person or organisation to whom it is addressed. If you are not the intended recipient, you must not copy, dis

Re: [GENERAL] Confirming \timing output

On Aug 23, 2012, at 11:13 AM, "Gauthier, Dave" wrote: > With \timing set on, I run an update statement and it reports > > Time: 0.524 ms > > Is that really 0.524 ms? As in 524 nanoseconds? 0.524ms = 524000ns Perhaps you meant microseconds? 0.524ms = 524us If all your data happens to

Re: [GENERAL] Ignore hash indices on replicas

On Aug 19, 2012, at 2:37 PM, Jeff Davis wrote: > On Tue, 2012-07-10 at 00:09 -0700, Steven Schlansker wrote: >> I understand that the current wisdom is "don't use hash indices", but >> (unfortunately?) I have benchmarks that >> show that our particular appli

Re: [GENERAL] Ignore hash indices on replicas

On Aug 19, 2012, at 8:01 PM, Scott Marlowe wrote: > On Tue, Jul 10, 2012 at 1:09 AM, Steven Schlansker > wrote: >> I'm using Postgres hash indices on a streaming replica master. >> As is documented, hash indices are not logged, so the replica does not have >

Re: [GENERAL] Postgres will not compile on Mac 10.8 with contrib/uuid-ossp

On Jul 30, 2012, at 7:35 PM, Tom Lane wrote: > Steven Schlansker writes: >> It is not possible to compile Postgres contrib/uuid-ossp on the newest >> release of Mac OS X, 10.8 > > This looks like some variant of the same issue that OSSP's uuid > package has had

[GENERAL] Postgres will not compile on Mac 10.8 with contrib/uuid-ossp

It is not possible to compile Postgres contrib/uuid-ossp on the newest release of Mac OS X, 10.8 The specific compile error: make -C uuid-ossp install /bin/sh ../../config/install-sh -c -d '/usr/local/Cellar/postgresql/9.1.3/lib' /usr/bin/clang -Os -w -pipe -march=native -Qunused-arguments -I

Re: [GENERAL] Replication/cloning: rsync vs modification dates?

I think it's pretty easy to show that timestamp+size isn't good enough to do this 100% reliably. Imagine that your timestamps have a millisecond resolution. I assume this will vary based on OS / filesystem, but the point remains the same no matter what size it is. You can have multiple writes

[GENERAL] Ignore hash indices on replicas

ound, but I do apologize if I've missed it in the manual or it is extremely obvious how you do this :) Thanks, Steven -- 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] Suboptimal query plan fixed by replacing OR with UNION

On Jul 6, 2012, at 9:24 PM, Gurjeet Singh wrote: > On Thu, Jul 5, 2012 at 7:16 PM, Steven Schlansker wrote: > > On Jul 5, 2012, at 3:51 PM, Tom Lane wrote: > > > Steven Schlansker writes: > >> Why is using an OR so awful here? > > > > Because the OR

Re: [GENERAL] Suboptimal query plan fixed by replacing OR with UNION

On Jul 5, 2012, at 6:35 PM, Jasen Betts wrote: > I note you've decided to rewrite this query as a union > >> SELECT * FROM account >> WHERE user_id in >>(SELECT user_id FROM account >> WHERE id = >> ANY('{-02f6-379d-c000-00026810,-0320-b467-c000-00026810,000

Re: [GENERAL] Suboptimal query plan fixed by replacing OR with UNION

On Jul 5, 2012, at 3:51 PM, Tom Lane wrote: > Steven Schlansker writes: >> Why is using an OR so awful here? > > Because the OR stops it from being a join (it possibly needs to return > some rows that are not in the semijoin of the two tables). > >> Why does it

[GENERAL] Suboptimal query plan fixed by replacing OR with UNION

27;{-02f6-379d-c000-00026810,-0320-b467-c000-00026810,-000d-cefb-c000-00026810}'::uuid[])) (16 rows) Wow! Changing the query from using an OR clause to a UNION DISTINCT with two SELECTs reduced the cost from 1379485.60 to 3366.35! And the gains are rea

[GENERAL] local server service account

Good morning!   When I installed version 8.3.12, I used a local account as the service account. Well, we're getting into an issue where because it's a local account it gets removed from the "logon as a service" list because its not a domain account (part of our global policy object).    So my q

[GENERAL]

Good morning!   When I installed version 8.3.12, I used a local account as the service account. Well, we're getting into an issue where because it's a local account it gets removed from the "logon as a service" list because its not a domain account (part of our global policy object).    So my q

Re: [GENERAL] replay doesn't catch up with receive on standby

> Was there idle-in-transaction in the master when the problem happened? Shouldn't have been, but that's what I was wondering, too. I didn't check. Not sure I know how to check. That was my guess and I mostly wanted to confirm that that could happen. Does seem like an edge case. I don't expect

  1   2   >