Re: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL

On Thu, Apr 3, 2014 at 1:19 PM, Tom Lane wrote: > Amit Langote writes: >> On Thu, Apr 3, 2014 at 12:54 PM, Tom Lane wrote: >>> Some experimentation suggests that we are smart about "DEFAULT NULL" >>> unless the column type requires a length-coercion cast, in which >>> case the default expression

Re: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL

On Thu, Apr 3, 2014 at 1:19 PM, Tom Lane wrote: > Amit Langote writes: >> On Thu, Apr 3, 2014 at 12:54 PM, Tom Lane wrote: >>> Some experimentation suggests that we are smart about "DEFAULT NULL" >>> unless the column type requires a length-coercion cast, in which >>> case the default expression

Re: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL

Amit Langote writes: > On Thu, Apr 3, 2014 at 12:54 PM, Tom Lane wrote: >> Some experimentation suggests that we are smart about "DEFAULT NULL" >> unless the column type requires a length-coercion cast, in which >> case the default expression involves a function call, and that doesn't >> get elid

Re: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL

On Thu, Apr 3, 2014 at 12:54 PM, Tom Lane wrote: > Amit Langote writes: >> When I do the following: > >> ALTER TABLE table ADD COLUMN numeric(x) DEFAULT NULL; > >> The table is rewritten whereas notes section on the manual page for >> ALTER TABLE says otherwise (which holds true for most of the c

Re: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL

Amit Langote writes: > When I do the following: > ALTER TABLE table ADD COLUMN numeric(x) DEFAULT NULL; > The table is rewritten whereas notes section on the manual page for > ALTER TABLE says otherwise (which holds true for most of the cases > though). Try it without the explicit DEFAULT claus

[GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL

Hi, When I do the following: ALTER TABLE table ADD COLUMN numeric(x) DEFAULT NULL; The table is rewritten whereas notes section on the manual page for ALTER TABLE says otherwise (which holds true for most of the cases though). http://www.postgresql.org/docs/devel/static/sql-altertable.html As

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 05:30 PM, Rob Sargent wrote: On 04/02/2014 06:06 PM, Adrian Klaver wrote: On 04/02/2014 02:27 PM, Rob Sargent wrote: On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: On 04/02/2014 02:36 PM, Adrian Klaver wrote: On 04/02/2014 01:14 PM, Rob

Re: [GENERAL] SSD Drives

While I have two friends who work at FusionIO, and have great confidence in their products, we like to deploy more conventional SATA SSDs at present in our servers. We have been running various versions of Intel's enterprise and data center SSDs in production for several years now and couldn'

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 06:06 PM, Adrian Klaver wrote: On 04/02/2014 02:27 PM, Rob Sargent wrote: On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: On 04/02/2014 02:36 PM, Adrian Klaver wrote: On 04/02/2014 01:14 PM, Rob Sargent wrote: On 04/02/2014 01:56 PM, Stev

[GENERAL] table insert/primary key question

Hey everyone, I have a table like so: Receiptlimitid: BIGINT (Primary Key) Profitcenterid: BIGINT Receiptnumber: INTEGER All are set to Not Null also. My question is, if I have an insert that goes idle in transaction for a while before it commits, will it stop all other inserts from happening

Re: [GENERAL] SSD Drives

We used 4x OCZ Deneva 2 in a RAID configuration. Worked well for us for over 2 years with no hardware issues. We switched to SSD because we had a very write-intensive application (30 million rows/day) that spinning disks just couldn't keep up with. On 4/2/2014 6:09 PM, Shaun Thomas wrote: O

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 04:36 PM, Jeff Janes wrote: On Wed, Apr 2, 2014 at 12:37 PM, Rob Sargent > wrote: I'm playing with various data models to compare performance and practicalities and not sure if I should be surprised by the numbers I'm getting. I hope this r

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 02:27 PM, Rob Sargent wrote: On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: On 04/02/2014 02:36 PM, Adrian Klaver wrote: On 04/02/2014 01:14 PM, Rob Sargent wrote: On 04/02/2014 01:56 PM, Steve Atkins wrote: Have you tried moving the

Re: [GENERAL] SSD Drives

On Wed, Apr 2, 2014 at 4:09 PM, Shaun Thomas wrote: > On 04/02/2014 04:55 PM, Bret Stern wrote: > >> Care to share the SSD hardware you're using? > > > We use these: > > http://www.fusionio.com/products/iodrive2/ > > The older versions of these cards can read faster than a RAID-10 of 80x15k > RPM

Re: [GENERAL] simple update query stuck

Ok, thanks. I'll keep that in mind. On Tue, Apr 1, 2014 at 7:45 PM, Andrew Sullivan wrote: > On Tue, Apr 01, 2014 at 07:00:16PM -0400, Tom Lane wrote: > > > one of the clients, in a way that isn't visible to the deadlock detector. > > One way for that to happen without any external interconnec

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 04:36 PM, Jeff Janes wrote: On Wed, Apr 2, 2014 at 12:37 PM, Rob Sargent > wrote: I'm playing with various data models to compare performance and practicalities and not sure if I should be surprised by the numbers I'm getting. I hope this r

Re: [GENERAL] COPY v. java performance comparison

On Wed, Apr 2, 2014 at 12:37 PM, Rob Sargent wrote: > I'm playing with various data models to compare performance and > practicalities and not sure if I should be surprised by the numbers I'm > getting. I hope this report isn't too wishy-washy for reasoned comment. > > One model says a genotype

Re: [GENERAL] SSD Drives

On 04/02/2014 04:55 PM, Bret Stern wrote: Care to share the SSD hardware you're using? We use these: http://www.fusionio.com/products/iodrive2/ The older versions of these cards can read faster than a RAID-10 of 80x15k RPM SAS drives, based on our tests from a couple yeas ago. Writes aren'

Re: [GENERAL] libpq - lack of support to set the fetch size

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Second, the feature needed to do this without even > using a cursor was added 1.5 years ago (PQsetSingleRowMode). The DBD::Pg > was just not taught how to use it yet. True. And we were hoping for something better, so we can make one request

Re: [GENERAL] Lock problem

On Wed, Apr 2, 2014 at 3:01 PM, Igor Neyman wrote: > > >> -Original Message- >> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- >> ow...@postgresql.org] On Behalf Of Victor Sterpu >> Sent: Wednesday, April 02, 2014 2:25 PM >> To: Victor Sterpu; Merlin Moncure >> Cc: Postgre

Re: [GENERAL] SSD Drives

Care to share the SSD hardware you're using? I've used none to date, and have some critical data I would like to put on a development server to test with. Regards, Bret Stern On Wed, 2014-04-02 at 15:31 -0500, Shaun Thomas wrote: > On 04/02/2014 02:50 PM, Brent Wood wrote: > > > http://it-blog

Re: [GENERAL] Lock problem

On 02 Apr 2014, at 20:13, Victor Sterpu wrote: > There may pass a few days or weeks until next lock. > But I don't undesrtand why the whole table is locked if there is one > uncommited transaction. > > The following scenario might be the cause: > 1. Transaction is started > 2. the client appli

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: On 04/02/2014 02:36 PM, Adrian Klaver wrote: On 04/02/2014 01:14 PM, Rob Sargent wrote: On 04/02/2014 01:56 PM, Steve Atkins wrote: Well indeed there are copious LOG/HINT pairs along the lines of LO

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 02:04 PM, Rob Sargent wrote: On 04/02/2014 02:36 PM, Adrian Klaver wrote: On 04/02/2014 01:14 PM, Rob Sargent wrote: On 04/02/2014 01:56 PM, Steve Atkins wrote: Well indeed there are copious LOG/HINT pairs along the lines of LOG: checkpoints are occurring too frequently (

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 02:36 PM, Adrian Klaver wrote: On 04/02/2014 01:14 PM, Rob Sargent wrote: On 04/02/2014 01:56 PM, Steve Atkins wrote: Impatience got the better of me and I killed the second COPY. This time it had done 54% of the file in 6.75 hours, extrapolating to roughly 12 hours to do the

Re: [GENERAL] COPY v. java performance comparison

On Apr 2, 2014, at 1:14 PM, Rob Sargent wrote: > On 04/02/2014 01:56 PM, Steve Atkins wrote: >> On Apr 2, 2014, at 12:37 PM, Rob Sargent >> wrote: >> >>> >>> Impatience got the better of me and I killed the second COPY. This time it >>> had done 54% of the file in 6.75 hours, extrapolating

Re: [GENERAL] COPY v. java performance comparison

Just for a comparison ... I wrote a Java program that copies data from MySQL -> Postgres, using the Copy impelmentation in the JDBC driver. I've occasionally seen 50,000+ rows/sec from this program, but the speed is highly dependent on the table structure. Tables that are very wide tend to run

Re: [GENERAL] Bug in user pg_ident.conf mapping code?

On 04/02/2014 03:42 PM, Adrian Klaver wrote: If you want a sort of hyphen. I know. Unfortunately, the user in question gets his name from our Active Directory system. We have PAM set up to maintain local users based on the username assigned there, so the hyphen is stuck. Trust me, we lamen

Re: [GENERAL] Bug in user pg_ident.conf mapping code?

On 04/02/2014 01:35 PM, Shaun Thomas wrote: On 04/02/2014 03:27 PM, Adrian Klaver wrote: Who are you connecting as dude-guy or dudeguy? Looks to me like: psql -d some_db -U dude-guy You are correct. I was assuming the translation went system -> PostgreSQL, not the other way around. Like, by

Re: [GENERAL] Bug in user pg_ident.conf mapping code?

On 04/02/2014 03:27 PM, Adrian Klaver wrote: Who are you connecting as dude-guy or dudeguy? Looks to me like: psql -d some_db -U dude-guy You are correct. I was assuming the translation went system -> PostgreSQL, not the other way around. Like, by setting -U dude-guy, PostgreSQL checked pe

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 01:14 PM, Rob Sargent wrote: On 04/02/2014 01:56 PM, Steve Atkins wrote: On Apr 2, 2014, at 12:37 PM, Rob Sargent wrote: I'm playing with various data models to compare performance and practicalities and not sure if I should be surprised by the numbers I'm getting. I hope this

Re: [GENERAL] SSD Drives

On 04/02/2014 02:50 PM, Brent Wood wrote: http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html While interesting, these results are extremely out of date compared to current drives. Current chips and firmware regularly put out 2-10 times better performance than even

Re: [GENERAL] Bug in user pg_ident.conf mapping code?

On 04/02/2014 10:01 AM, Shaun Thomas wrote: Hey, So, I've gotten this on 9.3.4 after having a complaint from a user with a hyphen in his name, but I dug further. This is at the top of my pg_hba.conf file: localallallpeer map=local And this is my pg_ident.conf: localdude-guy

Re: [GENERAL] SSD Drives

have you seen this? http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html Brent Wood Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington |

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 01:56 PM, Steve Atkins wrote: On Apr 2, 2014, at 12:37 PM, Rob Sargent wrote: I'm playing with various data models to compare performance and practicalities and not sure if I should be surprised by the numbers I'm getting. I hope this report isn't too wishy-washy for reasoned c

Re: [GENERAL] SSD Drives

On 04/02/2014 02:37 PM, Bret Stern wrote: Any opinions/comments on using SSD drives with postgresql? Using SSDs with PostgreSQL is fine, provided they have an onboard capacitor to ensure data integrity. The main concern with SSD drives, is that they essentially lie about their sync status. T

Re: [GENERAL] Lock problem

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Victor Sterpu > Sent: Wednesday, April 02, 2014 2:25 PM > To: Victor Sterpu; Merlin Moncure > Cc: PostgreSQL General > Subject: Re: [GENERAL] Lock problem > > I'm s

Re: [GENERAL] COPY v. java performance comparison

On Apr 2, 2014, at 12:37 PM, Rob Sargent wrote: > I'm playing with various data models to compare performance and > practicalities and not sure if I should be surprised by the numbers I'm > getting. I hope this report isn't too wishy-washy for reasoned comment. > > One model says a genotype i

Re: [GENERAL] pg_stat_activity

Jeff Janes wrote > On Wed, Apr 2, 2014 at 12:00 PM, Bala Venkat < > akpgeek@ > > wrote: > >> We are using postgres 9.0. When I looked at the pg_stat_activity table. >> >> I have some rows where there is difference of 2 hours between >> backend_start and xact_start >> >> But there is only few m

[GENERAL] COPY v. java performance comparison

I'm playing with various data models to compare performance and practicalities and not sure if I should be surprised by the numbers I'm getting. I hope this report isn't too wishy-washy for reasoned comment. One model says a genotype is defined as follows: Table "public.oldstyle"

[GENERAL] SSD Drives

Any opinions/comments on using SSD drives with postgresql? -- 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] pg_stat_activity

On Wed, Apr 2, 2014 at 12:00 PM, Bala Venkat wrote: > We are using postgres 9.0. When I looked at the pg_stat_activity table. > > I have some rows where there is difference of 2 hours between > backend_start and xact_start > > But there is only few milli seconds between xact_start and query_sta

Re: [GENERAL] Lock problem

I'm sure is not right, but is a there a server side solution for such sitations? A configuration - timeout for idle transactions. -- Original Message -- From: "Victor Sterpu" To: "Victor Sterpu" ; "Merlin Moncure" Cc: "PostgreSQL General" Sent: 4/2/2014 9:13:22 PM Subject: Re[2]: [G

Re: [GENERAL] Lock problem

There may pass a few days or weeks until next lock. But I don't undesrtand why the whole table is locked if there is one uncommited transaction. The following scenario might be the cause: 1. Transaction is started 2. the client application is closed because of a power surge, the started trans

[GENERAL] Bug in user pg_ident.conf mapping code?

Hey, So, I've gotten this on 9.3.4 after having a complaint from a user with a hyphen in his name, but I dug further. This is at the top of my pg_hba.conf file: localallallpeer map=local And this is my pg_ident.conf: localdude-guy dudeguy local/(.*)$ \1 I've reloa

Re: [GENERAL] Lock problem

I followed all your advice and it is obiuos that this log will show exactly what I need to debug the situation. Great tip, thank you. -- Original Message -- From: "Merlin Moncure" To: "Victor Sterpu" Cc: "PostgreSQL General" Sent: 4/2/2014 7:08:08 PM Subject: Re: [GENERAL] Lock proble

Re: [GENERAL] Lock problem

On Wed, Apr 2, 2014 at 11:00 AM, Victor Sterpu wrote: > All my transactions have commit or rollback. Well, you have to verify that. There's a couple of ways to do it. Probably the most direct is to: 1. Make sure database is logging pids in log_line_prefix (this is a good idea all around) 2. turn

Re: [GENERAL] Lock problem

-- Original Message -- From: "Igor Neyman" To: "Victor Sterpu" ; "pgsql-general@postgresql.org" Sent: 4/2/2014 6:29:17 PM Subject: RE: [GENERAL] Lock problem From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Victor Sterpu Sent: We

Re: [GENERAL] Lock problem

-- Original Message -- From: "Merlin Moncure" To: "Victor Sterpu" Cc: "PostgreSQL General" Sent: 4/2/2014 6:49:28 PM Subject: Re: [GENERAL] Lock problem On Wed, Apr 2, 2014 at 10:19 AM, Victor Sterpu wrote: Hello I have a problem that it seems to be very hard to debug. Proble

Re: [GENERAL] Lock problem

-- Original Message -- From: "Tom Lane" To: "Victor Sterpu" Cc: pgsql-general@postgresql.org Sent: 4/2/2014 6:31:13 PM Subject: Re: [GENERAL] Lock problem "Victor Sterpu" writes: I have a problem that it seems to be very hard to debug. Problem is from some postgresql locks. I use

Re: [GENERAL] Lock problem

On Wed, Apr 2, 2014 at 10:19 AM, Victor Sterpu wrote: > > Hello > > I have a problem that it seems to be very hard to debug. > Problem is from some postgresql locks. I use PostgreSQL 9.1.8. > > I runned this query to fid the locks: > > SELECT bl.pid AS blocked_pid, >a.usename

Re: [GENERAL] Table Vacuum Taking a Long Time

Hi Eliot, > If I continue to run vacuum > analyze on it, it continues to take about 90 seconds each time. > [..] > I was under the impression that if nothing had been done to the table > since it was last vacuumed, that it would return immediately. in addition to what others already said, I'd e

Re: [GENERAL] Lock problem

"Victor Sterpu" writes: > I have a problem that it seems to be very hard to debug. > Problem is from some postgresql locks. I use PostgreSQL 9.1.8. You haven't actually explained what your problem is. > I runned this query to fid the locks: > SELECT bl.pid AS blocked_pid, a.usename AS blocked_us

Re: [GENERAL] Lock problem

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Victor Sterpu Sent: Wednesday, April 02, 2014 11:19 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Lock problem Hello   I have a problem that it seems to be very hard to debug. Problem is f

[GENERAL] Lock problem

Hello I have a problem that it seems to be very hard to debug. Problem is from some postgresql locks. I use PostgreSQL 9.1.8. I runned this query to fid the locks: SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.current_query AS b

Re: [GENERAL] Insert zero to auto increment serial column

On Wed, Apr 02, 2014 at 08:17:12AM -0400, loc wrote: > value, MySQL also works this way. With PostgreSQL I will need to do a lot > of code modification to my Aubit4GL programs, since I will need to either > insert with the key word default or omit the serial column in the insert > statement. Why

Re: [GENERAL] Table Vacuum Taking a Long Time

Eliot Gable wrote: > I have a table which is about 12 GB in size. It has had a vacuum full > analyze run on it, and then immediately after, I run vacuum analyze and it > takes about 90 seconds to complete. If I continue to run vacuum analyze on > it, it continues to take about 90 seconds each time.

Re: [GENERAL] Table Vacuum Taking a Long Time

Hi! Le 2014-04-02 à 10:26, Eliot Gable a écrit : > I have a table which is about 12 GB in size. It has had a vacuum full analyze > run on it, and then immediately after, I run vacuum analyze and it takes > about 90 seconds to complete. If I continue to run vacuum analyze on it, it > continues

[GENERAL] Table Vacuum Taking a Long Time

I have a table which is about 12 GB in size. It has had a vacuum full analyze run on it, and then immediately after, I run vacuum analyze and it takes about 90 seconds to complete. If I continue to run vacuum analyze on it, it continues to take about 90 seconds each time. This system has a single 7

Re: [GENERAL] Insert zero to auto increment serial column

On 04/02/2014 08:24 AM, Scott Marlowe wrote: Triggers are almost always better here and really aren't that hard to write. Not only do I have to agree with this statement, I feel it's important to add a clarification regarding your original question. Translating serial values submitted as 0

Re: [GENERAL] Insert zero to auto increment serial column

loc wrote > Setting the serial column to null to auto increment would also > work for me. Can you set it to a literal value DEFAULT? Only helps for the insert case (not copy) but that is the mechanism that is used to specify a column and ask for the default. David J. -- View this message in

Re: [GENERAL] Insert zero to auto increment serial column

On 04/02/2014 05:17 AM, loc wrote: I'm currently using an Informix Innovator-C database with Aubit4GL and I would like to migrate to PostgreSQL, it looks like the transition will not be too difficult, however there is one feature that I would like added to PostgreSQL. Where is the best place to

Re: [GENERAL] Insert zero to auto increment serial column

On Wed, Apr 2, 2014 at 6:53 AM, Jayadevan M wrote: > Will a rule work? > http://www.postgresql.org/docs/9.3/static/sql-createrule.html There are a couple of issues you face if you use a rule, copy commands ignore rules, and rules are slower. Triggers are almost always better here and really aren

Re: [GENERAL] Insert zero to auto increment serial column

Will a rule work? http://www.postgresql.org/docs/9.3/static/sql-createrule.html On Wed, Apr 2, 2014 at 5:47 PM, loc wrote: > I'm currently using an Informix Innovator-C database with Aubit4GL and I > would like to migrate to PostgreSQL, it looks like the transition will not > be too difficult,

Re: [GENERAL] Insert zero to auto increment serial column

loc wrote: > I'm currently using an Informix Innovator-C database with Aubit4GL and I > would like to migrate to > PostgreSQL, it looks like the transition will not be too difficult, however > there is one feature that > I would like added to PostgreSQL. Where is the best place to request a > f

[GENERAL] Insert zero to auto increment serial column

I'm currently using an Informix Innovator-C database with Aubit4GL and I would like to migrate to PostgreSQL, it looks like the transition will not be too difficult, however there is one feature that I would like added to PostgreSQL. Where is the best place to request a feature add? With Informix