Re: [GENERAL] Advise on memory usage limitation by PostgreSQL on Windows

Venkata , Not really an advice but the wiki has specific comments about shared_buffers values in Windows environment. See… http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS I use a Windows 7 64b, 16 GB RAM, 4 cores. According to the above link I

[GENERAL] OR vs UNION vs UNION ALL

Hi, I've reworked a bit an application to make 1 query (with many OR) instead of thousands (2900 in this bench), but was a bit disappointed with the perf. Here follow a quick bench and 2 questions at the end: Here is my table CREATE TABLE properties ( item_id text NOT NULL, calendar_id uuid

Re: [GENERAL] numeric data type

Alvaro, thank you, that worked. > Date: Tue, 22 Sep 2015 18:57:38 -0300 > From: alvhe...@2ndquadrant.com > To: jpablolorenze...@hotmail.com > CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org > Subject: Re: [GENERAL] numeric data type > > Juan Pablo L. wrote: > > Hi Alvaro, thank you for

Re: [GENERAL] The curious case of two inserts, a shrinking xmax, and a ShareLock on transaction

Jeff Dik wrote: > Is there any way to inspect a multixact via psql to see what transaction ID > values it has? I wasn't able to find anything while searching for an hour > or so. There's the function pg_get_multixact_members(xid), =# select * from pg_get_multixact_members('1'); xid | mode

Re: [GENERAL] The curious case of two inserts, a shrinking xmax, and a ShareLock on transaction

On Tue, Sep 22, 2015 at 10:44 PM, Alvaro Herrera wrote: > Jeff Dik wrote: > > > I'd really love to learn: > > > > 1. Why the xmax for foo_id1 goes from 696 to 1 and what does that > >mean? > > When two transactions want to lock the same row, the xmax field is a >

Re: [GENERAL] The curious case of two inserts, a shrinking xmax, and a ShareLock on transaction

On Wed, Sep 23, 2015 at 11:26 AM, Alvaro Herrera wrote: > Jeff Dik wrote: > > > Is there any way to inspect a multixact via psql to see what transaction > ID > > values it has? I wasn't able to find anything while searching for an > hour > > or so. > > There's the

[GENERAL] Postgresql HA questions

I have a couple of questions regarding setting up and administering a high availability Postgresql "cluster". At the moment the setup I have consists of two servers set up with streaming replication using the named replication slots feature of Postgresql 9.4 (currently running 9.4.1). Let me know

[GENERAL] PostgreSQL and Windows 10 upgrade

Does anyone running PostgreSQL 9.3 on Windows 7 has upgraded to Windows 10? Successfully or not. Daniel

Re: [GENERAL] Table using more disk space than expected

Could you please check this after running Vacuum Analyze. I know there may not be big difference. As par my analysis this is free space available in table but not free in respect to server space. Like table contains 3 type of space. 1 Live row space 2 Dead row space 3 Free space available for

Re: [GENERAL] Table using more disk space than expected

Steve Pritchard writes: > -- Average length of a row in bytes: > select avg(octet_length(t.*::text)) FROM observation t; > -- 287 bytes That measurement technique doesn't have a lot to do with reality, I'm afraid. The contrib/pgstattuple module could give you a more

[GENERAL] Table using more disk space than expected

I have a large table in Postgres 9.4.4 called 'observation', which is by far the largest item in the database. When I pg_dump this table the resulting file is about 9GB. However I've noticed that the pg data directory uses about 30GB (excluding pg_xlog). Looking at the space usage: -- Size of

Re: [GENERAL] Table using more disk space than expected

Tom, Thanks for the tip about pgstattuple - I hadn't discovered that (and I hadn't realised that it's bundled in the 9.4.4 distribution). This is what I get: SELECT * FROM pgstattuple('observation'); table_len 21,954,740,224 tuple_count 34,373,274 tuple_len 9,307,650,026

[GENERAL] pgcrypto

Hi, i created extension pgcrypto on public with postgres user.But while trying to use from my own schma suppose qa. when i run digest in function in my qa CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$ SELECT encode(digest($1, 'sha1'), 'hex') $$ LANGUAGE SQL STRICT

Re: [GENERAL] OR vs UNION vs UNION ALL

On Wed, Sep 23, 2015 at 8:38 AM, Etienne Champetier wrote: > Two questions: > 1) Is it normal to have such a big difference between OR and UNION and > should i always prefer UNION when possible? yes, it's unfortunate but true. I consider automatic transformation of

Re: [GENERAL] Table using more disk space than expected

Hi Steve: On Wed, Sep 23, 2015 at 7:25 PM, Steve Pritchard wrote: > I thought that autovacuum should recover the free space, however I see now > from the documentation that it doesn't (and that this is deliberate): > I'll do a VACUUM FULL, which I expect to reduce

Re: [GENERAL] pgcrypto

On Wed, Sep 23, 2015 at 8:46 AM, Ramesh T wrote: > Hi, > i created extension pgcrypto on public with postgres user.But > while trying to use from my own schma suppose qa. > > when i run digest in function in my qa > > CREATE OR REPLACE FUNCTION sha1(bytea)

[GENERAL] Automatically Updatable Foreign Key Views

Greetings. I love PostgreSQL's support of automatically updatable views, limited though it is. I would like to point out what I believe is another case where views can be updated, without ambiguity. I'm going to call this a "foreign key view". For example, given a view V which joins a table C

Re: [GENERAL] Automatically Updatable Foreign Key Views

On 24 September 2015 at 13:32, Raymond Brinzer wrote: > > Any thoughts on this would be welcome. This is something which I > would personally find exceptionally valuable; if there are problems > with the idea, I'd like to know. As well, if my description isn't > clear