Re: [GENERAL] Naming conventions for column names

[Alvaro Herrera]

Sachin Kotwal wrote:

> 3. Notify or highlight these changes in release notes because this can
> break some existing tools and user code.

Notifying people when their tools no longer work with a new server is
not the problem; they figure that out pretty quickly once they try the
new version.  The problem is that if you change any names, the
application developers need to provide version-specific queries that
work across all the PG versions they want to support.  That leads to
some pretty horrible code, annoyed developers, bad publicity for Pg
("you guys don't love your app developers!"), etc.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Naming conventions for column names

[Alvaro Herrera]

Sachin Kotwal wrote:

> I believe these naming conventions will be at two levels:
> 
> 1. Internal code of PostgreSQL , structures getting used internally
> 2. SQL/C functions get executed at the time of database initialization to
> create default objects and system catalogs.
> 
> 
> I will see how much modifications/efforts need to be done and will come
> back again if it is feasible.
> 
> My intension is to improve naming conventions and increase naming string
> where naming conventions are correct but make shorten.

I think the proper amount of effort to rename existing system catalog
columns is zero.

Also, I think it's pretty difficult to change column names on views that
have already been released.  The compatibility break for existing tools
is just too large.

A valuable service would be to ensure that any new views, and new
columns on existing views, have sensible names.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] xmin increasing within a transaction block?

[Alvaro Herrera]

Luca Ferrari wrote:

> Any other idea?

None here.  Maybe try attaching a debugger, setting a breakpoint on
AssignTransactionId, and grab backtraces when it is hit.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] xmin increasing within a transaction block?

[Alvaro Herrera]

Luca Ferrari wrote:
> Hi all,
> I suspect this has a trivial explaination, but this is what I'm experiencing:
> 
> > CREATE TABLE foo( i int );
> > BEGIN;
> * > INSERT INTO foo(i) VALUES( 1 );
> * > INSERT INTO foo(i) VALUES( 2 );
> * > SELECT xmin, cmin, xmax, cmax, i FROM foo;
>  xmin | cmin | xmax | cmax | i
> --+--+--+--+---
>  2466 |0 |0 |0 | 1
>  2467 |1 |0 |1 | 2
> (2 rows)

With this example both rows show the same xmin to me, which is what I'd
expect.

> Why is xmin greater than the current transaction id (and most notably
> not "fixed")?

Something is using subtransactions there.  My first guess would be that
there are triggers with EXCEPTION blocks, but your example doesn't show
any.  Or maybe you have event triggers.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] REASSIGN OWNED simply doesn't work

[Alvaro Herrera]

David G. Johnston wrote:

> ​You could at least fix the documentation bug since this superuser-only
> restriction doesn't show up and is in fact contradicted by the sentence
> ​"REASSIGN OWNED requires privileges on both the source role(s) and the
> target role."  The error message that comes back seems like it could be
> improved as well.

alvherre=# create role owner1;
CREATE ROLE
alvherre=# create role owner2;
CREATE ROLE
alvherre=# create role sam login;
CREATE ROLE
alvherre=# grant owner1 to sam;
GRANT ROLE
alvherre=# grant owner2 to sam;
GRANT ROLE
alvherre=# set session authorization owner1;
SET
alvherre=> create table owner1_table ();
CREATE TABLE
alvherre=> \q
RESET

$ psql alvherre -U sam

alvherre=> reassign owned by owner1 to owner2;
REASSIGN OWNED
alvherre=> \d 
  Listado de relaciones
 Esquema │Nombre│ Tipo  │ Dueño  
─┼──┼───┼
 public  │ owner1_table │ tabla │ owner2


> The word "privileges" there seems odd too, wouldn't "membership" be more
> appropriate?
> 
> https://www.postgresql.org/docs/10/static/sql-reassign-owned.html

I can change that.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Non-overlapping updates blocking each other

[Alvaro Herrera]

Did you try using SELECT FOR NO KEY UPDATE instead of SELECT FOR UPDATE?

However:

Seamus Abshere wrote:

> My current theory is that, since the table is not clustered by id, rows
> with very distant ids get stored in the same page, and the whole page is
> locked during an update.

But we only lock one heap page at a time, not many, so it's hard to see
how that would deadlock.

> Or something.

Given your reluctance to share more details, it seems you'd have to do
with that explanation.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] REASSIGN OWNED simply doesn't work

[Alvaro Herrera]

Sam Gendler wrote:
> psql 9.6.3 on OS X.
> 
> I'm dealing with a production database in which all db access has been made
> by the same user - the db owner, which isn't actually a superuser because
> the db runs on amazon RDS - amazon retains the superuser privilege for its
> own users and makes non-superuser role with createrole and createdb
> privileges for use as the primary role by the AWS account.

It's true that REASSIGN OWNED is limited to a very particular scenario.
It was written to support the specific case of wanting to drop a role,
and that can only be done by a superuser, so why would it matter that
REASSIGN OWNED itself could not be run by a superuser?

However, I do not apologize for not thinking about your particular
scenario.  I had my itch, and I scratched it; you have yours, yet you
complain that I did not scratch it in advance?

Feel free to submit a patch (or convince/hire someone to do it for you)
to add the new feature of allowing it to work without superuser privs.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] startup process stuck in recovery

[Alvaro Herrera]

Tom Lane wrote:
> Christophe Pettus  writes:
> > The problem indeed appear to be a very large number of subtransactions, 
> > each one creating a temp table, inside a single transaction.  It's made 
> > worse by one of those transactions finally getting replayed on the 
> > secondary, only to have another one come in right behind it...
> 
> Hmm, I tried to reproduce this and could not.  I experimented with
> various permutations of this:

This problem is probably related to commit 9b013dc238c, which AFAICS is
only in pg10, not 9.5.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Writing on replicas?

[Alvaro Herrera]

Ivan Voras wrote:

> The reporting code is *almost* read-only, with the major exception being
> that it creates temp tables for intermediate results. The main tables are
> not written to, just the temp tables.
> 
> Some time ago when I've asked what to do in this situation, the answer was
> that maybe a future version could allow this setup to work. So, I'm asking
> again: was something changed in 10.0 which would allow it?

No, but you could use logical replication (with either pglogical or
pg10's more limited native features).

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] archive_command fails but works outside of Postgres

[Alvaro Herrera]

Scott Marlowe wrote:
> On Fri, Aug 18, 2017 at 12:40 PM, twoflower  wrote:
> > I changed my archive_command to the following:
> >
> > archive_command = 'gsutil cp /storage/postgresql/9.6/main/%p
> > gs://my_bucket/pg_xlog/'

> > 2017-08-18 18:34:25.057 GMT [1436][0]: [104321] WARNING:  archiving
> > transaction log file "0001038B00D8" failed too many times, will
> > try again later

> Sounds like it depends on some envvar it doesn't see when run from the
> postmaster. If you sudo -u postgres and run it does it work?

I saw one installation with "gsutil cp" in archive_command recently.  It
had the CLOUDSDK_PYTHON environment variable set in the archive_command
itself.  Maybe that's a problem.

Another possible problem might be the lack of %f (this command seems to
rely on the file being the same name at the other end, which isn't
necessarily so)  and the fact that %p is supposed to be the path of the
file, so you shouldn't qualify it with the full path.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Multixact members limit exceeded

[Alvaro Herrera]

Thomas Munro wrote:

> One thing I noticed is that there are ~4 billion members (that's how
> many you have when you run out of member space), but only ~128 million
> multixacts, so I think the average multixact has ~32 members.
> Considering the way that multixacts grow by copying and extending by
> one, by the time you've created a multixact with n members you've
> eaten a total of n! member space with an average size of n/2 per
> multixact...  So one way to hit that average would be to repeatedly
> build ~64 member multixacts, or if mixed with smaller ones then you'd
> need to be intermittently building even larger ones.  A thundering
> herd of worker processes repeatedly share-locking the same row or
> something like that?

Note that the behavior of 9.5 is quite different from that of 9.3/9.4;
in the older releases there is some nasty behavior involving multiple
subtransactions of the same transaction grabbing a lock on the same
tuple, whereby the multixact grows without bound.  In 9.5 we fixed that.
There's a patch in the thread for bug #8470 that fixes it for 9.3 and
probably it can be applied to 9.4 too, not sure.  You can see it here:
https://www.postgresql.org/message-id/20150410161704.gh4...@alvh.no-ip.org
Look for the "simple" formulation.  I don't think I posted an updated
version later, but it was never applied.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Multixact members limit exceeded

[Alvaro Herrera]

Peter Hunčár wrote:
> Hi,
> 
> Thank you, yes those are the 'urgent' tables, I'd talk to the developers
> regarding the locks.I too think, there's something 'fishy' going on.

I bet you have a lot of subtransactions -- maybe a plpgsql block with an
EXCEPTION clause that's doing something per-tuple.  In certain cases
in 9.3 and 9.4 that can eat a lot of multixacts.  9.5 is much better in
that regard -- I suggest considering an upgrade there.

> Anyway, could it be that autovacuum blocks manual vacuum? Because I ran
> vacuum (full, verbose) and some tables finished quite fast, with huge
> amount of io recorded in the monitoring, but some of them are kind of
> stuck?

Whenever autovacuum is marked "for wraparound", it'll block a manual
vacuum.  An autovacuum worker not so marked would get cancelled by the
manual vacuum.

> Which brings me to the second question, how can I cancel autovacuum?

pg_cancel_backend() should do it, regardless of whether it's for
wraparound or not (but if it is, autovacuum will launch another worker
for the same table quickly afterwards).

> One particular table before vacuum full:
> 
>relname| relminmxid | table_size
> --++
>  delayed_jobs | 1554151198 | 21 GB
> 
> And after vacuum full:
> 
>relname| relminmxid | table_size
> --++
>  delayed_jobs | 1554155465 | 6899 MB
> 
> Shouldn't be the relminmxid changed after vacuum full, or am I not
> understanding something?

But it did change ... the problem is that it didn't change enough (only
4000 multixacts).  Maybe your multixact freeze min age is too high?
Getting rid of 15 GB of bloat is a good side effect, though, I'm sure.

What are the freeze settings?
select name, setting from pg_settings where name like '%vacuum%';

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Manage slot in logical/pglogical replication

[Alvaro Herrera]

--- Begin Message ---



Hi List,
  I'm m extremely please to  see the logical replication aka: 
transaction replication feature been implemented in Pg 10,  very nice 
work done by the contrib of this module/feature!
  Since  here is mentioned the "replication slots" are located on 
master replication host, if the master goes offline unexpected what 
will  be state of slave server :

-will accept  transactions( writes connection)  ?
 will rollback the uncommitted transactions? and move to read-write 
state/promote as new  master? ( re cgf slots)
-should the DBA consider cfg replication slots on different  host than 
master ( or at least mirror to a 3 side) ?



Thank you
Isabella
-
On 07/13/2017 09:11 AM, pgsql-general-ow...@postgresql.org wrote:

Message Digest
Volume 1 : Issue 15352 : "text" Format

Messages in this Issue:
   Re: Systemd support (was:Re: Please say it isn't so)
   Re: Systemd support (was:Re: Please say it isn't so)
   Re: Manage slot in logical/pglogical replication
   I can't cancel/terminate query.
   Re: Get table OID
   Re: BDR node removal and rejoin
   Re: I can't cancel/terminate query.

--

Date: Thu, 13 Jul 2017 10:49:01 -0400
From: Vick Khera <vi...@khera.org>
To: pgsql-general@postgresql.org
Subject: Re: Systemd support (was:Re: Please say it isn't so)
Message-ID: <cald+dcf+fxqssurkpzvzhlnbwzxs8optrsfkela8xtn62c9...@mail.gmail.com>

What exactly does the configure flag to enable systemd support do? It seems
to me that building software to the systemd platform is just the same as
building it for windows vs unix or any other platform. One can only hope it
doesn't cause the others to wither away.

On Wed, Jul 12, 2017 at 3:20 AM, Mark Morgan Lloyd <
markmll.pgsql-gene...@telemetry.co.uk> wrote:


On 12/07/17 05:00, Steve Litt wrote:


Hi all,

Please tell me this is a mistake:

https://wiki.postgresql.org/wiki/Systemd

Why a database system should care about how processes get started is
beyond me. Systemd is an entangled mess that every year subsumes more
and more of the operating system, in a very non-cooperative way.

There are almost ten init systems. In every one of those init systems,
one can run a process supervisor, such as runit or s6 or
daemontools-encore, completely capable of starting the postgres server.

Every year, systemd further hinders interoperability, further erodes
interchangeability of parts, and continues to address problems with
WONTFIX. In the long run, you do your users no favor by including
init-system specific code in Postgres or its makefiles. If systemd
can't correctly start Postgres, I guarantee you that s6 or runit,
running on top of systemd, can.

Postgres doesn't care which language makes a query to it. Why
should Postgres care which init system started it? I hope you can free
Postgres of init-specific code, and if for some reason you can't do
that, at least don't recommend init-specific code.


OTOH since systemd is what's being supported by a significant number of
distributions it makes sense to at least try to work robustly with it.

While my preference would have been to have made such a change at a major
version transition, the reality is that database systems are competitive,
and not keeping up with the underlying platform would have been very much
to PostgreSQL's disadvantage,

OP: Please note that you do yourself no favours at all by posting a
subject line which could very easily be misinterpreted as spam.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Attachment of type text/html removed.]

--

Date: Thu, 13 Jul 2017 11:24:10 -0400
From: Tom Lane <t...@sss.pgh.pa.us>
To: Vick Khera <vi...@khera.org>
Cc: pgsql-general@postgresql.org
Subject: Re: Systemd support (was:Re: Please say it isn't so)
Message-ID: <17764.1499959...@sss.pgh.pa.us>

Vick Khera <vi...@khera.org> writes:

What exactly does the configure flag to enable systemd support do?

Not a lot.  A quick grep for USE_SYSTEMD says it does nothing except
add code in the postmaster to report ready/not-ready state transitions
by calling sd_notify().  We have significantly more lines of
documentation concerning systemd than we do code.

 regards, tom lane


----------

Date: Thu, 13 Jul 2017 11:55:31 -0400
From: Alvaro Herrera <alvhe...@2ndquadrant.com>
To: dpat <denni@gmail.com>
Cc: pgsql-general@postgresql.org
ubject: Re: Manage slot in logicaS
l/pglogical replication
Message-ID: <20170713155531.hfzfxdvibw46bn6x@alvherre.pgsql>

dpat wrote:


i have configure a master-replica replication with new pglogical 2.0.

Re: [GENERAL] Manage slot in logical/pglogical replication

[Alvaro Herrera]

dpat wrote:

> i have configure a master-replica replication with new pglogical 2.0.
> I have to replicate data over MPLS/VPN, so there is a possibility that the
> link temporarily interrupts.
> I know that you have to be accurately estimated pg_xlog folder.
> How can I handle the prolonged interruption of the link?
> Can I just extend the folder? Or drop the slot?

Yeah, data accumulates in the origin side ("master"), so you need to
make sure you have sufficient room in pg_xlog to hold all the data
generated during the network interruption.

Dropping the slot would of course release the disk space, but it would
mean that you'd have to re-create the replica afterwards.  I suppose you
could have pg_xlog large enough to hold as much data as possible for a
mid-sized network interruption, and drop the slot as a last resource if
a connection interrupts for long enough that you risk causing
reliability problems in the origin.

> I can create a consumer who temporarily saves the data in an external file?

I don't think so.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

[Alvaro Herrera]

Ken Tanzer wrote:

> I didn't see any options for dealing with this, though I'm hoping I'm
> missing something easy or obvious.  Any suggestions or help would be
> appreciated.  Thanks.

pg_dump doesn't promise that its output is compatible with servers older
than itself.  I'm afraid you're stuck with filtering the output somehow
to remove or maybe comment out those lines.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[Alvaro Herrera]

Peter Geoghegan wrote:
> On Mon, Jun 19, 2017 at 8:36 AM, Jeff Janes  wrote:
> > Unfortunately, it is only implemented in very narrow circumstances.  You
> > have to be doing bitmap index scans of many widely scattered rows to make it
> > useful.  I don't think that this is all that common of a situation.  The
> > problem is that at every point in the scan, it has to be possible to know
> > what data block it is going to want N iterations in the future, so you can
> > inform the kernel to pre-fetch it.  That is only easy to know for bitmap
> > scans.
> 
> I think that you could prefetch in index scans by using the
> pointers/downlinks in the immediate parent page of the leaf page that
> the index scan currently pins. The sibling pointer in the leaf itself
> is no good for this, because there is only one block to prefetch
> available at a time.

Surely you could prefetch all the heap pages pointed to by index items
in the current leaf index page ...

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] autovacuum holds exclusive lock on table preventing it from to be updated

[Alvaro Herrera]

Dmitry O Litvintsev wrote:
> Hi
> 
> Since I have posted this nothing really changed. I am starting to panic 
> (mildly).  

...

> vacuum_cost_delay = 50ms

Most likely, this value is far too high.  You're causing autovacuum to
sleep for a very long time with this setting.  Hard to say for certain
without seeing the cost_limit value and the other related parameters,
but it's most certainly what's causing you pain.  The default of 20ms is
already too high for most users.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[Alvaro Herrera]

chuma.of...@ww-cs.de wrote:
> Dear Sir/Madam
> 
>  
> 
> How would i unsubscribe the general post?

I have removed you from pgsql-general.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Alvaro Herrera]

Harry Ambrose wrote:
> Hi,
> 
> Please find the jar attached (renamed with a .txt extension as I know some
> email services deem jars a security issue).
> 
> The jar accepts the following arguments:
> 
> $1 = host
> $2 = database
> $3 = username
> $4 = password
> $5 = port
> 
> It returns its logging to STDOUT. Please let me know if you require further
> info.

I'm unable to run this file.  Maybe it was corrupted in transit, given
that it was considered to be text.

$ md5sum toast-corrupter-aio.jar 
7b1f5854c286f9b956b9442afd455b7a  toast-corrupter-aio.jar

$ java -jar toast-corrupter-aio.jar 
Error: Invalid or corrupt jarfile toast-corrupter-aio.jar

Even unzip complains (after extracting a bunch of JDBC .class files)

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Alvaro Herrera]

ADSJ (Adam Sjøgren) wrote:
> Our database has started reporting errors like this:
> 
>   2017-05-31 13:48:10 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
> toast value 14242189 in pg_toast_10919630

Does the problem still reproduce if you revert commit
6c243f90ab6904f27fa990f1f3261e1d09a11853?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Deadlock with single update statement?

[Alvaro Herrera]

Justin Pryzby wrote:

> detail|Process 26871 waits for ShareLock on transaction 13693505; blocked by 
> process 26646.
> Process 26646 waits for ShareLock on transaction 13693504; blocked by process 
> 26871.
> Process 26871: SELECT db_column_name,table_name FROM 
> huawei_m2000_counter_details ORDER BY ctid FOR UPDATE
> Process 26646: SELECT db_column_name,table_name FROM 
> huawei_m2000_counter_details ORDER BY ctid FOR UPDATE

Uh, this is locking the whole table.  Is there no useful WHERE?  What
you should be doing is SELECT WHERE  ORDER BY  FOR
UPDATE where  is indexed.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Alvaro Herrera]

Thomas Kellerer wrote:
> Tom Lane schrieb am 26.05.2017 um 20:18:
> > > I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL 
> > > distribution) to upgrade a 9.6 cluster.
> > > pg_upgrade --check fails with the following messages:
> > 
> > > could not load library "$libdir/pgxml":
> > > ERROR:  could not load library 
> > > "d:/etc/postgres-10/pgsql/lib/postgresql/pgxml.dll": unknown error 126
> > 
> > Apparently BigSQL forgot to include contrib/xml2 in their distribution;
> > you should ping them about that one.
> 
> I wasn't sure where the problem is, I will report this to BigSQL as well.
> 
> However, the xml2 extension is included. The extension control file as well 
> as libxml2-2.dll.

But the error message is looking for pgxml.dll, not libxml2-2.dll.
Those are clearly different files.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Top posting....

[Alvaro Herrera]

George Neuner wrote:
> On Thu, 11 May 2017 13:43:52 -0400, Tom Lane 
> wrote:

> >Personally, when I've scrolled down through a couple of pages of quoted
> >and re-quoted text and see no sign of it ending any time soon, I tend
> >to stop reading.
> 
> I agree 100%.  But excessive brevity can make it so a reader can't
> follow the conversation.  Users of web forums often assume *you* can
> easily look back up the thread because *they* can.  In my experience,
> it isn't always easy to do.

Fortunately, we (postgresql.org) have set up our mailing list archives
so that it _is_ possible to look back entire threads.  Our archives have
proven time and again an extremely valuable resource, and we pride on
their quality and completeness (and the fact that we never ever break
links even when the website is rewritten).  There is always full context
in these lists, if you need it.  

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] [p...@vivation.com: Python versus Other Languages using PostgreSQL]

[Alvaro Herrera]

--- Begin Message ---
Hello,

I noticed that most of the largest web platforms that use PostgreSQL as
their primary database, also use Python as their primary back-end language.
Yet, according to every benchmark I could find over the last couple of
years, back-end languages like PHP, HHVM, and Node.JS outperform Python by
2x to 8x!

So here are my questions:

1) Why do the largest web applications that use PostgreSQL also use Python,
even though Python is significantly slower than it's biggest competitors?

2) Can PostgreSQL just as easily be used with PHP or Node.js? If not, why
not?

3) Can PostgreSQL be made to work seamlessly to take advantage of the
superior performance of HHVM or Node.js?


Thank you in advance!

~Paul


Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
--- End Message ---

[GENERAL] [plnc...@gmail.com: Can I safe my Data?]

[Alvaro Herrera]

-- 
Álvaro Herrera Developer, https://www.PostgreSQL.org/
"The problem with the facetime model is not just that it's demoralizing, but
that the people pretending to work interrupt the ones actually working."
   (Paul Graham)
--- Begin Message ---
Hello.. My name is Rian.

I have a problem, my Database attacked by ransomware virus. Some file has
encrypt. for pg_hba and postgresql.conf i have a backup, but for some file
like pg_filenode.map i dont have.

this a log when i start the postgres:

2017-05-08 10:08:17 ICT FATAL:  relation mapping file
"global/pg_filenode.map" contains invalid data

thanks for kindness.


postgresql-2017-05-08_100800.log
Description: Binary data


pg_filenode.map.id_2343700345_fgb45ft3pqamyji7.onion
Description: Binary data


pg_internal.init.id_2343700345_fgb45ft3pqamyji7.onion
Description: Binary data


PG_VERSION
Description: Binary data
--- End Message ---

-- 
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] cluster on brin indexes?

[Alvaro Herrera]

Samuel Williams wrote:
> I see this, but no follow up:
> 
> https://www.postgresql.org/message-id/CAEepm%3D2LUCLZ2J4cwPv5DisHqD9BE_AXnqHGqf0Tj-cvtiqVcQ%40mail.gmail.com
> 
> So, is it possible or not?

The general idea seems like it should be doable, but I haven't looked at
it in detail.  Contributions welcome.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Autovacuum stuck for hours, blocking queries

[Alvaro Herrera]

Tom Lane wrote:

> Also you might want to look into how you got into a situation where
> you have an anti-wraparound vacuum that's taking so long to run.

If there are ALTERs running all the time, regular (non-anti-wraparound)
vacuums would be canceled and never get a chance to run.  Eventually,
autovacuum decides it's had enough and doesn't cancel anymore, so
everyone else gets stuck behind.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Autovacuum stuck for hours, blocking queries

[Alvaro Herrera]

Scott Marlowe wrote:

> Then a ddl gets in line. It has to wait on the vacuum, and the vacuum,
> set to run super slow. And everybody waits. On vacuum.

Note that this is normally not seen, because autovacuum cancels itself
when somebody is blocked behind it -- until the table reaches the
freeze_max_age limit, and then autovacuum is a for-wraparound one that
is no longer terminated, and then everybody has to wait on it.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Ynt:[GENERAL] postgresql : could not serialize access due to read/write dependencies among transactions

[Alvaro Herrera]

Neslisah Demirci wrote:

> First I started conversation between two person ;
> 
> 
> sql: 'INSERT INTO "X" 
> ("id","customer","createdAt","updatedAt","CompanyId") VALUES 
> (DEFAULT,\'905322653555\',\'2017-01-17 19:13:37.751 +00:00\',\'2017-01-17 
> 19:13:37.751 +00:00\',\'1\') RETURNING *;' },
> 
> 
> name: 'error', length: 274, severity: 'ERROR', code: '40001', detail: 'Reason 
> code: Canceled on identification as a pivot, during write.', hint: 'The 
> transaction might succeed if retried.', position: undefined, 
> internalPosition: undefined, internalQuery: undefined, where: undefined, 
> schema: undefined, table: undefined, column: undefined, dataType: undefined, 
> constraint: undefined, file: 'predicate.c', line: '4605', routine: 
> 'OnConflict_CheckForSerializationFailure',
> 
> 
> 
> Secondly ; i inserted messages as below;
> 
> 
> Begin;
> 
> 'INSERT INTO "Y" 
> ("id","body","from","to","state","data","xId","createdAt","updatedAt","CompanyId","MessageTypeId","ConvId")
>  VALUES 
> (DEFAULT,\'messagetext\',\'905309788255\',\'905309788200\',\'customer\',NULL,\'463DA712296218E0D4\',\'2017-01-17
>  06:43:19.228 +00:00\',\'2017-01-17 06:43:19.228 +00:00\',\'1\',1,23286) 
> RETURNING *;'
> 
> commit;

Are there triggers or foreign keys in these tables?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] some amazing stuff

[Alvaro Herrera]

Edmundo Robles wrote:
> Please, administrator  mark this  like spam.

Yes, I removed the offending address on sight.  We do not remove emails
from the archive, though.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] checkpoint clarifications needed

[Alvaro Herrera]

Tom DalPozzo wrote:

> Hi,
> so let's suppose that the WAL is:
> LSN 10: start transaction 123
> LSN 11: update tuple 100
>checkpoint position here (not a record but just for understanding)
> LSN 12: update tuple 100
> LSN 13: update tuple 100
> LSN 14: checkpoint record ( postion=11)
> LSN 15: update tuple 100
> and that the system crashes now, before ending to write all the
> transaction's recs to the WAL  (other updates and commit record missing).
> 
> At the replay, starting from LSN 12, the entire page we had at LSN 11 is
> written to the disk, though carrying inconsistent data.
> Then we can even replay up to the end of WAL but always getting
> inconsistent data.
> BUT, you say, as the tuple is not commited in the WAL, only the old version
> of the tuple will be visible? Right?

Yes -- all the updated tuples are invisible because the commit record
for transaction 123 does not appear in wal.  A future VACUUM will remove
all those tuples.  Note that precisely for this reason, the original
version of the tuple had not been removed yet.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] checkpoint clarifications needed

[Alvaro Herrera]

Tom DalPozzo wrote:

> 2) I see that a checkpoint position can be right in the middle of a group
> of records related to a transaction (in the example, transaction id 10684).
> So a checkpoint position is NOT a consistency state point, right?

> 4) If I'm right at 2) then, between the checkpoint position (1/F00A7448 )
> and the checkpoint record position (1/FCBD7510) there must be a point where
> the DB is in a consistency state. If not, in case of crash just after
> writing the checkpoint record to the WAL and its position to pg_control,
> the system would replay from the checkpoint position (known by  last
> checkpoint record) without finding a consistency state. Right?
> 
> 5) How can we define, in terms of log records, a consistency state position?

Whether any individual tuple in the data files is visible or not depends
not only on the data itself, but also on the commit status of the
transactions that created it (and deleted it, if any).  Replaying WAL
also updates the commit status of transactions, so if you're in the
middle of replaying WAL, you may be adding tuples to the data files, but
those tuples will not become visible until their commit records are also
updated.

You can stop replaying WAL at any point, and data will always be in a
consistent state.  Some data tuples might be "from the future" and those
will not be visible, which is what makes it all consistent.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] postgres pg_bulkload c filter function in c programming

[Alvaro Herrera]

rajmhn wrote:

> But, how this can be accomplished when it have 100's of columns from source.
> Need to apply transformations only for few columns as mentioned above. 

Did you try pgloader?  See http://pgloader.io/

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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_repack and Postgres versions > 9.4

[Alvaro Herrera]

Gaetano Mendola wrote:
> I wonder why this is not a VACUUM option.

Because nobody has written the patch.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[Alvaro Herrera]

said assemlal wrote:
> Hello,
> 
> PG: 9.4
> CentOS 6
> 
> I am writing functions to lock results.
> 
> Let's take an example:
> 
> CREATE OR REPLACE FUNCTION usp_locking_my_result($1 VARCHAR(50), $2
> VARCHAR(50))
> RETURNS TEXT AS $$
> SELECT value
> FROM my_table
> WHERE field1 = $1 AND field2 = $2 FOR UPDATE;
> $$ LANGUAGE SQL;

Why do you want to lock these results?

> What happens if one of those arguments are empty and database finds
> results?  ( I think they will locked )

What do you mean "empty"?  If you pass the empty string, the query will
return rows that have the empty string in those columns.  Note that the
NULL value is not the same as the empty string.  If the function is
defined as STRICT (also spelled RETURNS NULL ON NULL INPUT), then the
function is not even called if you pass NULL arguments, and it simply
returns NULL without locking anything.

> Should I check the inputs and raise an exception if there are empty ?

Not necessarily ... depends on what you want to happen.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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_class (system) table increasing size.

[Alvaro Herrera]

dhaval jaiswal wrote:
> Adding to above.  Below are the outputs.

I just meant that you need to ANALYZE all these system catalogs so that
autovacuum can pick up vacuuming them to remove dead tuples.  Do not
leave autovacuum turned off anymore.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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_class (system) table increasing size.

[Alvaro Herrera]

dhaval jaiswal wrote:
> I did check and found it was the bloated size of pg_class which was slowing 
> down the performance.
> 
> It got fixed by adding in routine maintenance task. Things are fine now.

Good to know.

> However, I want to know how come pg_class  (system table) get 
> bloated/affected.
> 
> What could be the possible ways, where i can look into.

The most common cause is high traffic temp table usage.

> Due to business impact auto vacuum is off.

Yeah, that's a really bad idea and you should turn it on and configure
it so that it doesn't impact business.  Having it turned off is
definitely not recommended.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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_class (system) table increasing size.

[Alvaro Herrera]

dhaval jaiswal wrote:
> select * from pg_stat_sys_tables where relname = 'pg_class';
> 
> -[ RECORD 1 ]---+---
> relid   | 1259
> schemaname  | pg_catalog
> relname | pg_class
> seq_scan| 1838
> seq_tup_read| 3177416
> idx_scan| 1027456557
> idx_tup_fetch   | 959682909
> n_tup_ins   | 0
> n_tup_upd   | 0
> n_tup_del   | 0
> n_tup_hot_upd   | 0
> n_live_tup  | 0
> n_dead_tup  | 0
> n_mod_since_analyze | 0
> last_vacuum |
> last_autovacuum |
> last_analyze|
> last_autoanalyze|
> vacuum_count| 0
> autovacuum_count| 0
> analyze_count   | 0
> autoanalyze_count   | 0
> 
> 
> Yes, the size of pg_class table is of 5 GB.  However, the existing row is 
> only 2380 only. It's got fragmented.

Looks like you lost the stat data awhile ago (probably due to a server
crash, or pg_stats_reset()) and it never got updated.  I suggest doing
"ANALZYE pg_class" to create initial stats; that might prompt autovacuum
to vacuum the table.  If the bloat is excessive, vacuuming might take a
very long time, in which case perhaps consider VACUUM FULL (but be very
aware of its consequences first).

I think it's likely that this has happened to other catalogs as well, so
check the pg_stat_sys_tables view for other entries with all zeroes in
the n_tup_* columns.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] MultiXact member wraparound protections are disabled

[Alvaro Herrera]

avi Singh wrote:
> Your right we looked back in our old logs and we do see the messages there
> as well. Still what I'm not getting is since we restarted the database
> after SAN FC re-cable effort auto-vacuum is running on all the threads
> continuous. I have never seen auto-vacuum using all the threads 24*7 on
> this database. Any thoughts ?

It's trying to ensure all tables are correctly frozen.  As I recall,
that's working per spec and you should just let it run until it's done.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] MultiXact member wraparound protections are disabled

[Alvaro Herrera]

AnandKumar, Karthik wrote:
> Thanks. We started seeing this error right after a SAN FC re-cable effort - 
> so yes, that would make sense. 
> We’ll do a little more digging to see if the  could have gotten removed.
> If that’s an older file that we have in our filesystem backups, is it safe to 
> restore from there?

Sure, the files are immutable after they are completed.  I worry that if
the system removed it automatically, it would just remove it again,
though.  Shouldn't happen on 9.4.5, but it seems just too much of a
coincidence that that file was removed.

Changes such as FC recabling should not cause anything like this.  I
mean, why a pg_multixact file and not a table data file?  Very fishy.

I'd advise to verify your older logs at the time of restarts whether the
"multixact protections are enabled" message has ever appeared, or it has
always been "protections are disabled".  Maybe you've had the problem
for ages and just never noticed ...

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] MultiXact member wraparound protections are disabled

[Alvaro Herrera]

AnandKumar, Karthik wrote:
> root@site-db01a:/var/lib/pgsql/cmates/data # ls pg_multixact/members
>   0001  0002  0003  0004  0005  0006  0007  0008  0009  000A  000B  000C  
> 000D  000E  000F  0010  0011  0012  0013  0014  0015  0016  0017  0018  0019  
> 001A  001B
> root@site-db01a:/var/lib/pgsql/cmates/data # ls pg_multixact/offsets
> 0001  0002  0003  0004  0005  0006  0007  0008  0009  000A  000B

> postgres@site-db01a:~ $ /usr/pgsql-9.4/bin/pg_controldata 
> /var/lib/pgsql/cmates/data

> Latest checkpoint's NextMultiXactId:  784503
> Latest checkpoint's NextMultiOffset:  1445264
> Latest checkpoint's oldestMultiXid:   1
> Latest checkpoint's oldestMulti's DB: 16457

This looks perfectly normal, except that the pg_multixact/offsets/
file is gone.  oldestMultiXid is 1 so I don't see how could have the
file gotten removed.  Has this been upgraded recently from a previous
9.3 or 9.4 version?  There have been bugs in this area but they've been
fixed now for some time.

The  file could have been removed manually, perhaps?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] MultiXact member wraparound protections are disabled

[Alvaro Herrera]

AnandKumar, Karthik wrote:
> Hi,
> 
> We run postgres 9.4.5.
> 
> Starting this morning, we started seeing messages like the below:
> Oct 12 14:07:15 site-db01a postgres[11253]: [106430-1] app=,user=,db=,ip=LOG: 
>  MultiXact member wraparound protections are disabled because oldest 
> checkpointed MultiXact 1 does not exist on disk
> Oct 12 14:09:26 site-db01a postgres[11253]: [106526-1] app=,user=,db=,ip=LOG: 
>  MultiXact member wraparound protections are disabled because oldest 
> checkpointed MultiXact 1 does not exist on disk
> Oct 12 14:14:18 site-db01a postgres[11253]: [106608-1] app=,user=,db=,ip=LOG: 
>  MultiXact member wraparound protections are disabled because oldest 
> checkpointed MultiXact 1 does not exist on disk
> 
> Our autovacuum_freeze_max_age = 175000.
> 
> site=# SELECT datname, age(datfrozenxid) FROM pg_database;
>  datname  |age
> ---+
> site  | 1645328344
> template0 | 1274558807
> bench | 1274558807
> postgres  | 1324283514
> template1 | 1274558807
> 
> So we’re about 100 mil transactions away before we start vacuuming to prevent 
> wraparound.
> 
> We’re running precautionary vacuums on our largest offenders to try and drop 
> our transaction ids
> 
> What I’d request some clarity on is the message above. What does it mean that 
> "oldest checkpointed MultiXact does not exist on disk”? Would we lose data if 
> we did have to wrap around?
> 
> Is this telling us we’re not vacuuming effectively enough?

Ugh.  Can you share the output of pg_controldata and the list of files
in pg_multixact/members and pg_multixact/offset?

The problem here is that multixact vacuuming is separate from xid
vacuuming, so you need to be looking at datminmulti rather than
datfrozenxid.  It may be that multixact wrap around has already
occurred.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] BRIN indexes and ORDER BY

[Alvaro Herrera]

Darren Lafreniere wrote:

> "In addition to simply finding the rows to be returned by a query, an index
> may be able to deliver them in a specific sorted order. This allows a
> query's ORDER BY specification to be honored without a separate sorting
> step. Of the index types currently supported by PostgreSQL, only B-tree can
> produce sorted output — the other index types return matching rows in an
> unspecified, implementation-dependent order."
> 
> We found a pgsql-hackers thread from about a year ago about optimizing
> ORDER BY for BRIN indexes. Tom Lane suggested that he was working on it:
> https://www.postgresql.org/message-id/11881.1443393360%40sss.pgh.pa.us

Tom said he was working on some infrastructure planner changes
("upper-planner path-ification"), not that he was working on improving
usage of BRIN indexes.  As far as I know, nobody has worked on that.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

[Alvaro Herrera]

Tom Lane wrote:
> Thomas Kellerer  writes:
> > for some reason pg_upgrade failed on Windows 10 for me, with an error 
> > message that one specifc _vm file couldn't be copied.
> 
> Hmm ... a _vm file would go through rewriteVisibilityMap(), which is new
> code for 9.6 and hasn't really gotten that much testing.  Its error
> reporting is shamefully bad --- you can't tell which step failed, and
> I wouldn't even put a lot of faith in the errno being meaningful,
> considering that it does close() calls before capturing the errno.

So we do close() in a bunch of places while closing shop, which calls
_close() on Windows; this function sets errno.  Then we call
getErrorText(), which calls _dosmaperr() on the result of
GetLastError().  But the last-error stuff is not set by _close; I suppose
GetLastError() returns 0 in that case, which promps _doserrmap to set errno to 
0.
http://stackoverflow.com/questions/20056851/getlasterror-errno-formatmessagea-and-strerror-s
So this wouldn't quite have the effect you say; I think it'd say
"Failure while copying ...: Success" instead.

However surely we should have errno save/restore.

Other than that, I think the _dosmaperr() call should go entirely.
Moreover I think getErrorText() as a whole is misconceived and should be
removed altogether (why pstrdup the string?).  There are very few places
in pg_upgrade that require _dosmaperr; I can see only copyFile and
linkFile.  All the others should just be doing strerror() only, at least
according to the manual.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] PgSQL versions supported on ubuntu 16 and debian 8

[Alvaro Herrera]

John R Pierce wrote:
> On 9/19/2016 4:18 AM, MEERA wrote:
> >
> >Could someone please provide us this information?
> 
> was answered nearly a week ago.

Hmm, yeah, but you didn't CC the OP, and she is not subscribed.

Meera: you can see the answer here:
https://www.postgresql.org/message-id/flat/CABZh%3DWaeB5%2BMBnoaJ6QB2dy%3D1G3DuO368VFxBoSiWXtiZpdcHw%40mail.gmail.com

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Any work on better parallelization of pg_dump?

[Alvaro Herrera]

Jehan-Guillaume de Rorthais wrote:

> > Yeah.  I recall there being some stupid limitation in ALTER TABLE .. ADD
> > CONSTRAINT USING INDEX to create a primary key from a previously
> > existing unique index, which would be very good to fix (I don't recall
> > what it was, but it was something infuriatingly silly). 
> 
> Could you elaborate? I already had to implement some custom scripts to
> restore some tables using this method. The scripts were using psql and
> "xargs -P" to restore the indexes and the PK outside of pg_restore. 

Ahh, nevermind.  What I remembered was that if you have an UNIQUE
constraint, you cannot update its index to be a primary key, and you
cannot remove the associated constraint without dropping the index; you
have to make a new unique index instead.  If you have a plain UNIQUE
index, it works fine.  In other words,

-- this fails: the unique index is already associated with a constraint
create table ioguix (a int unique);
alter table ioguix add primary key using index ioguix_a_key ;

-- this works
create unique index ioguix_2_idx on ioguix (a);
alter table ioguix add primary key using index ioguix_2_idx ;

> > I suggest you start coding that ASAP.
> 
> I did start, but with no success so far as the code in pg_dump/pg_restore is
> quite obscure at a first look. The few attempt I did to catch the PK creation
> statement and split it in 2 distincts statements failed to be processed in
> parallel IIRC. I probably dropped the patch in the meantime though.

pg_dump is *really* obscure, and its parallel processing even more so.
That just says that we need more people to try and figure it out!

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Any work on better parallelization of pg_dump?

[Alvaro Herrera]

Jehan-Guillaume de Rorthais wrote:
> On Mon, 29 Aug 2016 13:38:03 +0200
> hubert depesz lubaczewski  wrote:
> 
> > Hi,
> > we have rather uncommon case - DB with ~ 50GB of data, but this is
> > spread across ~ 8 tables.
> > 
> > Running pg_dump -Fd -jxx dumps in parallel, but only data, and MOST of
> > the time is spent on queries that run sequentially, and as far as I can
> > tell, get schema of tables, and sequence values.
> > 
> > This happens on Pg 9.5. Are there any plans to make getting schema
> > faster for such cases? Either by parallelization, or at least by getting
> > schema for all tables "at once", and having pg_dump "sort it out",
> > instead of getting schema for each table separately?

Depesz: I suggest you start coding ASAP.

> Another issue I found in current implementation is how pg_restore deal with 
> PK.
> As it takes an exclusif lock on the table, it is executed alone before indexes
> creation. 
> 
> Splitting the PK in unique index creation then the constraint creation might
> save a lot of time as other index can be built during the PK creation.

Yeah.  I recall there being some stupid limitation in ALTER TABLE .. ADD
CONSTRAINT USING INDEX to create a primary key from a previously
existing unique index, which would be very good to fix (I don't recall
what it was, but it was something infuriatingly silly).  I suggest you
start coding that ASAP.

(Two new contributors to pg_dump!  Yay!)

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Determining table change in an event trigger

[Alvaro Herrera]

Jonathan Rogers wrote:
> I am trying to use an event trigger to do something when a column
> changes. I can declare an event trigger "ON sql_drop WHEN TAG IN ('ALTER
> TABLE')" to get dropped columns. However, I can't figure out any good
> way to determine when a column has been added or altered.
> 
> I can declare an event trigger "ON ddl_command_end WHEN TAG IN ('ALTER
> TABLE')" but that gets unwanted events such as disabling triggers on a
> table. Function pg_event_trigger_ddl_commands() returns rows with column
> "command" of type "pg_ddl_command" which contains "a complete
> representation of the command, in internal format." According to the
> docs, this cannot be output directly, but it can be passed to other
> functions to obtain different pieces of information about the command.
> However, I cannot find any other functions which operate on the type
> pg_ddl_command. Am I missing something? Is the documentation lacking?

Yeah, that type can only be processed by C functions.  You'd need to
write a C function to examine the structure and see whether it matches
what you need.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Any reasons for 'DO' statement not returning result?

[Alvaro Herrera]

Xtra Coder wrote:
> May you have the link to 'DO'-discussion to take a look on it? I was trying
> to google for something like that, but word 'DO' is too generic to bring
> useful results :(

Probably this is one:
https://www.postgresql.org/message-id/51b624c6@2ndquadrant.com

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Critical failure of standby

[Alvaro Herrera]

James Sewell wrote:

> 2016-08-12 04:43:53 GMT [23614]: [5-1] user=,db=,client=  (0:0)LOG:  
> consistent recovery state reached at 3/8811DFF0
> 2016-08-12 04:43:53 GMT [23614]: [6-1] user=,db=,client=  (0:XX000)FATAL:  
> invalid memory alloc request size 3445219328
> 2016-08-12 04:43:53 GMT [23612]: [3-1] user=,db=,client=  (0:0)LOG:  
> database system is ready to accept read only connections
> 2016-08-12 04:43:53 GMT [23612]: [4-1] user=,db=,client=  (0:0)LOG:  
> startup process (PID 23614) exited with exit code 1
> 2016-08-12 04:43:53 GMT [23612]: [5-1] user=,db=,client=  (0:0)LOG:  
> terminating any other active server processes
> 2016-08-12 04:43:53 GMT [23612]: [6-1] user=,db=,client=  (0:0)LOG:  
> archiver process (PID 23627) exited with exit code 1

What version is this?

Hm, so the startup process finds the consistent point (which signals
postmaster so that line 23612/3 says "ready to accept read-only conns")
and immediately dies because of the invalid memory alloc error.  I
suppose that error must be while trying to process some xlog record, but
without a xlog address it's difficult to say anything.  I suppose you
could try to pg_xlogdump WAL starting at the last known good address
3/8811DFF0 but I wouldn't know what to look for.

One strange thing is that xlog replay sets up an error context, so you
would have had a line like "xlog redo HEAP" etc, but there's nothing
here.  So maybe the allocation is not exactly in xlog replay, but
something different.  We'd need to see a backtrace in order to see what.
Since this occurs in the startup process, probably the easiest way is to
patch the source to turn that error into PANIC, then re-run and examine
the resulting core file.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Postgres Pain Points 2 ruby / node language drivers

[Alvaro Herrera]

support-tiger wrote:

> It would be great if the Ruby and Node drivers can be brought under the
> Postgres team umbrella and make them as reliable and clearly documented as
> the Python or jdbc drivers.

Sadly, the PostgreSQL development group does not have the manpower to
maintain or document client drivers.  Excepting libpq (the C library)
and ecpg (the embedded C preprocessor), every driver is maintained by
external groups.

Maybe you can contribute to the effort of maintaining those external
drivers yourselves, or at least improving their docs.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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_archivecleanup standalone bash script

[Alvaro Herrera]

Patrick B wrote:
> This has been resolved.

How?

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Uber migrated from Postgres to MySQL

[Alvaro Herrera]

Patrick B wrote:
> >
> > I think it's safe to say that that has absolutely nothing to do
> > with the size being 3TB.  They symptoms you report are a little
> > thin to diagnose the actual cause.
> 
> might be... we're using SATA disks... and that's a big problem. But still..
> the size of the DB is indeed a problem.

Andrew is correct -- the size of the database is not a limitation for
pg_upgrade.  Disk tech is not relevant either.  You may run into the
problem that you don't have enough disk space, but then that is not a
database or pg_upgrade problem, is it?

Other things might cause issues, but since you haven't actually reported
the problem, we don't know what is or whether there is any possible fix.

Then again, if you want to report a pg_upgrade failure, I suggest you
open a thread of your own rather than hijack this one.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] question on parsing postgres sql queries

[Alvaro Herrera]

Kevin Grittner wrote:

> On the other hand, try connecting to a database with
> psql and typing:
> 
> \h create index
> 
> ... (or any other command name).  The help you get there is fished
> out of the docs.

BTW I noticed a few days ago that we don't have a "where BLAH can be one
of" section for the window_definition replaceable term in the help for
SELECT.  We omit these sections for trivial clauses, but I think WINDOW
is elaborate enough that it should have one.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] pasting a lot of commands to psql

[Alvaro Herrera]

Tom Lane wrote:
> Francisco Olarte <fola...@peoplecall.com> writes:
> > On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera <alvhe...@2ndquadrant.com> 
> > wrote:
> >> I've wished sometimes for a "\set READLINE off" psql metacommand for
> >> this kind of thing.  It's pretty annoying when the text being pasted
> >> contains tabs and readline uses to do completion.
> 
> > Doesn't 'cat | psql ' disable it?
> 
> Sure, but you could as well use 'psql -n'.  I think the point is to be
> able to turn it on and off without starting a fresh session.  (Admittedly,
> maybe there's not a lot of usability gain there.)

If your command line already connected to the correct server/database,
with the correct login role, then yeah you can do that.  If you have to
switch role (say the role that runs the commands is not a login role),
it's not so convenient to disconnect and launch a new psql.

Now of course this not a huge new feature, but a usability improvement
only -- but psql is full of small usability features and they make it a
pleasure to use.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] pasting a lot of commands to psql

[Alvaro Herrera]

Merlin Moncure wrote:

> Might be a 'xterm vs Mate Terminal' problem.  Using raw xterm
> performance is great.  I like some of the creature comforts of the
> mate terminal though.

Heh.  I've been using lxterminal for a couple of weeks now and I find
some of these comfort features rather uncomfortable.  Haven't tried
megabyte pastes.  But yeah, it might be the terminal.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] pasting a lot of commands to psql

[Alvaro Herrera]

Merlin Moncure wrote:
> On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera <alvhe...@2ndquadrant.com> 
> wrote:
> > Tom Lane wrote:
> >
> >> You might have better luck with "psql -n", or maybe not.
> >
> > I've wished sometimes for a "\set READLINE off" psql metacommand for
> > this kind of thing.  It's pretty annoying when the text being pasted
> > contains tabs and readline uses to do completion.
> 
> Agreed.  I've looked at this problem extensively and concur that
> readline is the culprit; I don't think there's any solution on our end
> besides filing a bug with the readline.  I also agree with the
> upthread suggestion that the best workaround today is to \e into a
> non-readline based editor (vim qualifies).  Having said that, at least
> on linux/gnome, very long pastes can cause severe performance issues
> as well.  So for large pastes I go with psql -f.

Hmm, I was doing megabyte-long pastes (longest one over 5 MB) just a few
weeks ago and was pleasantly surprised to discover that they worked just
fine with no noticeable performance problem.  I was pasting skype logs
directly from the Linux skype client window into an xterm running cat,
with obviously no readline involved.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] pasting a lot of commands to psql

[Alvaro Herrera]

Tom Lane wrote:

> You might have better luck with "psql -n", or maybe not.

I've wished sometimes for a "\set READLINE off" psql metacommand for
this kind of thing.  It's pretty annoying when the text being pasted
contains tabs and readline uses to do completion.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Fastest memmove in C

[Alvaro Herrera]

FarjadFarid(ChkNet) wrote:

> Excellent research and could be well worth checking out. As it could
> improve the performance of postgresql engine.

0) We certainly do a lot of memory copying.

1) this work is under the "Code Project Open License" which doesn't look
compatible with our Postgres license on first blush.  Maybe T Herselman
would agree to share under the Postgres licence, which would make things
easier.

2) from the description, the code is probably tailored to specific
compilers.  Even if it's faster now in some mainstream compilers, it
won't be in yesterday or tomorrow's ones.  Also, there are probably
going to be variations depending on CPU features.

3) How much faster does Postgres get when you replace memcpy/memmove
with these Apex versions?  If the percentage to be gained is small, then
there's probably no point in spending a lot of effort into providing
platform-specific implementations of these things anyway.

If you have enough interest in this topic, you could try using these
Apex versions in Postgres and measuring an improvement, to get a
discussion going.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Is it possible to use an EVENT TRIGGER to validate a TRIGGER?

[Alvaro Herrera]

Luís Eduardo Oliveira Lizardo wrote:
> Hi,
> 
> Is it possible to use an EVENT TRIGGER to validate a TRIGGER definition?
> 
> What I want is to guarantee that the trigger is fired AFTER a STATEMENT, on
> INSERT or UPDATE but not on DELETE, like the following example:

What you can do with a C language function in 9.5 is to examine the
CreateTrigger struct and verify that it matches the conditions you want.
So, yes, it's possible, but not in plpgsql.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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_dump fundenental question

[Alvaro Herrera]

J. Cassidy wrote:

> As stated in the original email, I want to know whether compression
> (whatever level) is on by default (or not) -  if I supply NO extra
> switches/options.  I have read the documentation and it is unclear in
> this respect. I am a Mainframer and perhaps have a different world
> view on how to explain things...

I disagree on it being unclear.  It says plain and simple "the default
is not to compress" when using the text output which it also says to be
the default output format.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Postgres Dropped DB have recovered files how to restore

[Alvaro Herrera]

John R Pierce wrote:
> On 6/6/2016 4:09 PM, Alvaro Herrera wrote:
> >I have no idea about Windows filesystems but you may be able to
> >"undelete" the files, as long as you don't touch the partition for
> >anything else; search the web for "undelete ntfs".  Once you undelete
> >you will need to put them back in the right places.  I suggest you get a
> >disk with twice as much space as the original; make an image copy of the
> >original disk and don't touch the original anymore.  Then try to
> >undelete the files from the image.  Use the list Adrian provided as a
> >guide for what you're missing.
> >
> >You*may*  be able to recover the data, if you're lucky and careful.
> 
> that won't work if he did a DROP DATABASE as all the metadata is gone.

It's only very difficult -- not impossible.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Postgres Dropped DB have recovered files how to restore

[Alvaro Herrera]

lifetronics wrote:
> This morning I accidently deleted my database for my OpenERP accounting. I
> did not have a good backup system setup so I was unable to do a system
> restore. i did manage to recover the files the drop command removed but I
> dont know how to get the DB back into postgres? Can anyone help me with
> this. I realy need this db to be restored otherwise I am screwed.

I have no idea about Windows filesystems but you may be able to
"undelete" the files, as long as you don't touch the partition for
anything else; search the web for "undelete ntfs".  Once you undelete
you will need to put them back in the right places.  I suggest you get a
disk with twice as much space as the original; make an image copy of the
original disk and don't touch the original anymore.  Then try to
undelete the files from the image.  Use the list Adrian provided as a
guide for what you're missing.

You *may* be able to recover the data, if you're lucky and careful.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[Alvaro Herrera]

Joseph Kregloh wrote:
> It is my understanding that if PostgeSQL has log shipping enabled, if for
> whatever reason it cannot ship the file the master server will hold it. But
> for how long?

Forever (which means it dies because of running out of space in the
partition containing pg_xlog).

> Secondly, I have 2 servers I ship log files to using the following script:
> 
> #!/usr/local/bin/bash
> 
> # Slave 1
> rsync -a $1 pgi@192.168.1.105:archive/$2 < /dev/null;
> 
> # Slave 2
> rsync -a $1 pg@192.168.1.93:archive/$2 < /dev/null;
> 
> In this case if Slave 1 is up but Slave 2 is down. It will ship the log
> file to Slave 1 but not Slave 2 and move one. Thereby Slave 2 will now be
> out of sync, correct?

You could cause the script to return failure if either of these copies
fail, and return success if once both replicas have the file
(considering that one replica might already have the file from a
previous run of your script); that way, the master will retain the file
until both replicas have it, and remove the file once both replicas have
it.  Of course, you want to avoid copying the file again to the replica
that already had the file, without getting confused by a partially
written file.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] ERROR: MultiXactId xxx has not been created yet

[Alvaro Herrera]

Christophe Pettus wrote:
> We have a database (PostgreSQL 9.3.10) which is reporting this error on a 
> TOAST table on a VACUUM.  Is there a canonical way of repairing this?  The 
> table is *huge*, so a VACUUM FULL or pg_dump / pg_restore is probably not 
> going to work.

I don't think toast tables can have multixacts at all, so my guess is
that the tuple header is corrupted.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] PostgreSQL with BDR - PANIC: could not create replication identifier checkpoint

[Alvaro Herrera]

Cameron Smith wrote:

> t:2016-05-19 01:14:51.668 UTC d= p=144 a=PANIC:  could not create replication 
> identifier checkpoint "pg_logical/checkpoints/8-F3923F98.ckpt.tmp": Invalid 
> argument

This line corresponds to the following code in BDR's 9.4.4
src/backend/replication/logical/replication_identifier.c:

/*
 * no other backend can perform this at the same time, we're protected by
 * CheckpointLock.
 */
tmpfd = OpenTransientFile(tmppath,
  O_CREAT | O_EXCL | O_WRONLY | PG_BINARY,
  S_IRUSR | S_IWUSR);
if (tmpfd < 0)
ereport(PANIC,
(errcode_for_file_access(),
 errmsg("could not create replication identifier checkpoint 
\"%s\": %m",
tmppath)));

This file does not exist in 9.5, but instead we have
src/backend/replication/logical/origin.c which has identical code.

OpenTransientFile calls BasicOpenFile, which in turn calls open() and
propagates the errno.  My manpage doesn't list any possible reasons for
open() to return EINVAL, so I'm at a loss about what is happening here.
Maybe this is a filesystem problem?

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] preventing ERROR: multixact "members" limit exceeded

[Alvaro Herrera]

Steve Kehlet wrote:
> On Mon, May 16, 2016 at 6:18 PM Alvaro Herrera <alvhe...@2ndquadrant.com>
> wrote:
> 
> > Not really.  Your best bet is to reduce the
> > autovacuum_multixact_freeze_min_age limit, so that vacuums are able to
> > get rid of multixacts sooner (and/or reduce
> > autovacuum_multixact_freeze_table_age, so that whole-table vacuuming
> > takes place earlier).
> 
> Thank you very much. I will adjust those settings. Is there a way,
> something similar to keeping an eye on `age(relfrozenxid)`, that I can
> watch this and keep an eye on it before it becomes a problem?

In 9.4, not really. In 9.5 there's a function mxid_age() that gives you
the age of a multixact, so you'd grab the oldest from
pg_database.datminmxid and compute the age of that one.  Going from the
oldest multi to the oldest offset cannot be done without an additional
function, however.  It's much easier to keep track of the oldest file in
$PGDATA/pg_multixact/members/; what you really need to care about is the
size of the "hole" between the newest and the oldest files there.  Once
newest starts to stomp on oldest, you're screwed.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] preventing ERROR: multixact "members" limit exceeded

[Alvaro Herrera]

Steve Kehlet wrote:

> Now it's just about preventing this. Our best guess at this point is the
> autovacuums aren't working fast enough. Sure enough this instance has our
> old values for:
> autovacuum_vacuum_cost_delay: 20ms
> autovacuum_vacuum_cost_limit: 200
> 
> We've since started using:
> autovacuum_vacuum_cost_delay: 10ms
> autovacuum_vacuum_cost_limit: 2000
> 
> We'll be updating those settings as soon as possible.
> 
> Just looking for some expert eyes on this problem. Are we on the track
> track? I.e. is making the autovacuumer run more aggressively our best bet
> to avoid this issue?

Not really.  Your best bet is to reduce the
autovacuum_multixact_freeze_min_age limit, so that vacuums are able to
get rid of multixacts sooner (and/or reduce
autovacuum_multixact_freeze_table_age, so that whole-table vacuuming
takes place earlier).  You may need to decrease the cost_delay too
(and/or increase the cost_limit) in order for autovac to be able to keep
up, but really that's more a side effect because of a possible need for
autovac to do more in the same period of time.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] WAL files not being recycled

[Alvaro Herrera]

Scott Moynes wrote:
> I have a PostgreSQL server that is not recycling WAL files. Log files are
> continually created and no old log files are ever removed.
> 
> Running PostgreSQL v 9.4.8 with archive settings:
> 
> archive_mode = on
> archive_command = /bin/true
> 
> Checkpoint logging is enabled and does not record any logs being recycled:

See the wal_keep_segments option, as well as existance of any
replication slot (select * from pg_replication_slots).

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] EINTR causes panic (data dir on btrfs)

[Alvaro Herrera]

Gustavo Lopes wrote:
> Every few weeks, I'm getting a error like this:
> 
> > 2015-02-11 15:31:00 CET PANIC: could not write to log file 
> > 00010007007D at offset 1335296, length 8192: Interrupted system 
> > call
> > 2015-02-11 15:31:00 CET STATEMENT: COMMIT
> > 2015-02-11 15:31:17 CET LOG: server process (PID 8390) was terminated by 
> > signal 6: Aborted
> > 2015-02-11 15:31:17 CET DETAIL: Failed process was running: COMMIT
> > 2015-02-11 15:31:17 CET LOG: terminating any other active server processes
> > 2015-02-11 15:31:17 CET WARNING: terminating connection because of crash of 
> > another server proces
> 
> I'm running the Ubuntu 9.3.4-1 package on a 3.2.13 kernel.
> 
> Is there any solution for this? The code generating the error seems to
> be this:
> 
> > if (write(openLogFile, from, nbytes) != nbytes)
> > {
> > /* if write didn't set errno, assume no disk space */
> > if (errno == 0)
> > errno = ENOSPC;
> > ereport(PANIC,
> > (errcode_for_file_access(),
> >  errmsg("could not write to log file %s "
> > "at offset %u, length %lu: %m",
> > XLogFileNameP(ThisTimeLineID, openLogSegNo),
> > openLogOff, (unsigned long) nbytes)));
> > }
> 
> which strikes me as a bit strange (but there may be data consistency
> issues I'm not aware of). Why wouldn't postgres retry on EINTR or even
> allow return values of write() lower than nbytes (and then continue in a
> loop).

I happened to notice this report from 15 months ago, which didn't get
any response.  Did you find a solution to this problem?  I would first
blame btrfs, mostly because I've never heard of anyone with this problem
on more mainstream filesystems.  As I recall, we use SA_RESTART almost
everywhere so we don't expect EINTR anywhere.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Possible causes for "tuple concurrently updated" error

[Alvaro Herrera]

Jacob Scott wrote:
> Arg, should have included this in my initial email :-(
> 
> 9.3.11

OK.  So what are the updates doing?  Are there any FKs involved?

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Possible causes for "tuple concurrently updated" error

[Alvaro Herrera]

On Wed, May 04, 2016 at 11:52:47PM -0700, Jacob Scott wrote:
> Hi,
> 
> I'm seeing a "tuple concurrently updated" error thrown while executing
> UPDATE statements. I've attempted to diligently review previous threads on
> this error (e.g.,
> https://www.google.com/webhp?ie=UTF-8#q=tuple+concurrently+updated+update+site:postgresql.org)
> but am confused about what classes of queries can conflict to cause this
> error.

What Postgres version are you running?


-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Vacuum of large tables causing replication delays to hot standby

[Alvaro Herrera]

Tom Lane wrote:
> Jeff Mcdowell  writes:
> > 95% of the time, the delay is only microseconds. But we have discovered 
> > that whenever the master does an auto vacuum of a large table, the 
> > transaction replay delay can climb is high as 1 hour. These delays don�t 
> > seem to correlate with any particular queries that are running against the 
> > master or the standby, and the delay only subsides when the vacuum 
> > completes.
> 
> What PG version might this be?

This sounds related to 

commit 3e4b7d87988f0835f137f15f5c1a40598dd21f3d
Author: Simon Riggs 
AuthorDate: Sun Apr 3 17:46:09 2016 +0100
CommitDate: Sun Apr 3 17:46:09 2016 +0100

Avoid pin scan for replay of XLOG_BTREE_VACUUM in all cases


-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

[Alvaro Herrera]

Tom Lane wrote:
> Melvin Davidson  writes:

> > However, Customer Feedback (
> > https://postgresql.uservoice.com/forums/21853-general
> >  ) does seem to
> > indicate it and give positive results.
> 
> I had never heard of postgresql.uservoice.com before this thread, and
> I daresay most other community members had not either.  It has NO
> standing or influence on our development work.

Actually, to be fair, Peter Eisentraut set it up back in 2009 and
continues to keep it updated.  I thought I had seen it announced, but
now that I look, it seems he only mailed sysadm...@postgresql.org and
never any public list.  From the comments there, it's pretty obvious
that the list is helpful; the number of things marked "done" in recent
times is not small.

I'm not saying that *anything* listed there is useful.  (In particular I
don't think the "relcreated" column provides a lot of value.)

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Columnar store as default for PostgreSQL 10?

[Alvaro Herrera]

Bráulio Bhavamitra wrote:
> Hi all,
> 
> I'm finally having performance issues with PostgreSQL when doing big
> analytics queries over almost the entire database of more than 100gb of
> data.
> 
> And what I keep reading all over the web is many databases switching to
> columnar store (RedShift, Cassandra, cstore_fdw, etc) and having great
> performance on queries in general and giant boosts with big analytics
> queries.
> 
> I wonder if there is any plans to move postgresql entirely to a columnar
> store (or at least make it an option), maybe for version 10?

This is a pretty interesting question.  I wrote an answer, then thought
it would make a good blog post, so it's at
http://blog.2ndquadrant.com/column-store-plans/
I reproduce it below.

Completely replacing the current row-based store wouldn't be a good
idea: it has served us extremely well and I’m pretty sure that replacing
it entirely with a columnar store would be disastrous performance-wise
for OLTP use cases.

That doesn't mean columnar stores are a bad idea in general -- because
they aren't. They just have a more limited use case than "the whole
database". For analytical queries on append-mostly data, a columnar
store is a much more appropriate representation than the regular
row-based store, but not all databases are analytical.

However, in order to attain interesting performance gains you need to do
a lot more than just change the underlying storage: you need to ensure
that the rest of the system can take advantage of the changed
representation, so that it can execute queries optimally; for instance,
you may want aggregates that operate in a SIMD mode rather than
one-value-at-a-time as it is today. This, in itself, is a large
undertaking, and there are other challenges too.

As it turns out, there's a team at 2ndQuadrant working precisely on
these matters. We posted a patch last year, but it wasn’t terribly
interesting -— it only made a single-digit percentage improvement in
TPC-H scores; not enough to bother the development community with (it
was a fairly invasive patch). We want more than that.

In our design, columnar or not is going to be an option: you're going to
be able to say "Dear server, for this table kindly set up columnar
storage for me, would you? Thank you very much." And then you’re going
to get a table which may be slower for regular usage but which will rock
for analytics. For most of your tables the current row-based store will
still likely be the best option, because row-based storage is much
better suited to the more general cases.

We don’t have a timescale yet. Stay tuned. 

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Initdb --data-checksums by default

[Alvaro Herrera]

Alex Ignatov wrote:
> Hello everyone!
> Today in Big Data epoch silent data corruption becoming more and more issue
> to afraid of. With uncorrectable read error rate ~ 10^-15  on multiterabyte
> disk bit rot is the real issue.
> I think that today checksumming data  must be mandatory  set by default.
> Only if someone doesn't care about his data he can manually turn this option
> off.

In principle I support the idea of turning data checksums by default,
but can you provide some numbers on how it affects performance on
various workloads?  That's a critical point in the discussion.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] 9.5 new features

[Alvaro Herrera]

I just noticed this old thread.

Thomas Kellerer wrote:

> > 62.1. Introduction
> > ...
> > "A block range is a group of pages that are physically adjacent in the 
> > table; for each block range, some summary info is stored by the index."
> > 
> > From the above, may I presume that it is best to cluster (or sort),
> > the table based on the intended BRIN column(s) before actually
> > creating the index to insure the pages are adjacent? If so, should
> > that not be included in the documentation, instead of implied?
> 
> That is something I am also curious about. If that was true, it would
> mean that BRIN indexes couldn't be used on tables that are not
> clustered along the index

There's no hard requirement that values must be clustered.  If the
values are clustered, that's the best case scenario for BRIN and things
will be very quick.  However, clustering a table is a slow operation and
requires locking the table, so I don't recommend that.

But strict correlation isn't really necessary either -- you just need
the values to be grouped together.  To illustrate, consider this
simplistic case: table has four pages, all the values in the first page
have col1=999, page 2 has all col1=1, page 3 has all col1=1500, page 4
has col1=-1000.  There's little correlation there, but a BRIN index with
pages_per_range=1 can still help a query that looks for col1 > 500
execute optimally.

> it wouldn't make sense to have more than one BRIN index.

Well, you can put all the columns in a single index, and it works just
like if you had one index for each column.  However, if you want a BRIN
index that's more detailed for certain columns than others, you can use
different pages_per_range settings on multiple indexes.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Multixacts wraparound monitoring

[Alvaro Herrera]

Pavlov, Vladimir wrote:
> Hello,
> If I get you right:
> Latest checkpoint's NextMultiXactId:  2075246000
> Latest checkpoint's oldestMultiXid:   2019511697
> Number of members files:  10820
> Size pg_multixact/members/ (bytes) (2.7Gb):   2887696384
> Pages in file:32
> Members on page:  2045
> Number of members (32*2045*10820):708060800
> Members per multixact (2075246000 - 2019511697)/708060800:12,70421916
> Multixact size (bytes) (2887696384/708060800):4,078316981 - It's a 
> lot?

Yeah, 12.7 members per multixact on average is a lot, unless you have 12
processes concurrently locking the same tuples, all the time (although
that is possible).   My guess is that this is related to subtransactions
(either explicit SAVEPOINTs in your SQL code, or EXCEPTION blocks in
plpgsql functions).

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Multixacts wraparound monitoring

[Alvaro Herrera]

Pavlov, Vladimir wrote:
> Yes, VACUUM helps to solve the problem and the WARNING gone away.
> But, the problem is that the VACUUM for the entire database (2.4T) takes over 
> 7 hours, and it has to run every 15-20 hours (about 300 millions 
> transactions), otherwise:
> ERROR:  multixact "members" limit exceeded - and server stops working.
> The question is how to start the VACUUM at least once in three days.

You should have *started* the thread with this information.

My bet is that your multixacts are overly large and that's causing
excessive vacuuming work; this is likely due to bug #8470 (which is
fixed in 9.5 and master but not 9.3 and 9.4) and my bet is that you
would very much benefit from the patch I posted in
https://www.postgresql.org/message-id/20150620043650.GX133018%40postgresql.org
I didn't actually verify this; you could with some arithmetic on the
deltas in multixact counters in pg_controldata output that you could
take periodically.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Multixacts wraparound monitoring

[Alvaro Herrera]

Pavlov, Vladimir wrote:
> There is nothing:
> select * from pg_prepared_xacts;
>  transaction | gid | prepared | owner | database
> -+-+--+---+--
> (0 rows)
> It is also noticed that a lot of files in a directory 
> main/pg_multixact/members/, now - 69640.

Can you attach pg_controldata output?

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Multixacts wraparound monitoring

[Alvaro Herrera]

Pavlov, Vladimir wrote:
> Thanks for your reply.
> Yes, the first thing I looked at the statistics from pg_stat_activity.
> But I have a transaction is not more than 60 seconds and the condition 'idle 
> in transaction' lasts only a few seconds.

Maybe you have a prepared transaction?  See
select * from pg_prepared_xacts;


-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

[Alvaro Herrera]

Tom Lane wrote:
> "David G. Johnston"  writes:
> > On Monday, March 21, 2016, Tom Lane  wrote:
> >> What about just discarding the old format entirely, and printing one of
> >> these two things:
> >> 
> >> Timestamp (every Ns)
> >> 
> >> User Given Title  Timestamp (every Ns)
> 
> > This works for me.
> 
> If I don't hear objections PDQ, I'm going to update the docs and commit
> it like that.

It works for me too.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: [HACKERS] Request - repeat value of \pset title during \watch interations

[Alvaro Herrera]

David G. Johnston wrote:

> Tom doesn't care enough to veto and you don't really care...
> 
> I'll admit it's awkward because it's abbreviated but if someone enters
> \watch 5 and then sees (5s) in the title I think they can put two and two
> together.
> 
> If the watched query takes a long to run, or there is a disruption, knowing
> when the last one ran and how often it is supposed to run is useful info to
> have at ones fingertips.  I have done this myself occasionally so I'm not
> speaking from theory.  But I won't complain if its removed.

I like David's UI better FWIW.

(I'll also use this opportunity to complain again about not being able
to use floating point sleep time.)

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[Alvaro Herrera]

Joshua D. Drake wrote:
> On 03/14/2016 08:48 AM, Alvaro Herrera wrote:

> >(*) Yes, I'm being a bit sarcastic here, sorry about that.  I actually
> >learned quite a bit of database design and related topics by translating
> >the "General Bits" column she used to write, many years ago.
> 
> Your answer presumes some personal issue with Elein. I don't know why that
> is.

Quite the contrary.  I hold the utmost respect for her and her work.  I
think she has something useful to say on this topic (and many others)
and the community is being myopic by saying that "enums are useless
anyway, so why improve on them, use lookup tables" which is essentially
what is being repeated over and over.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[Alvaro Herrera]

Elein wrote:

Hi Elein,

>* When an insert into an enum column fails give the person a hint as to 
> valid values

> -- Lousy message.  Show enum list.
> insert into badinfo values ('green');
> ERROR:  invalid input value for enum rainbow: "green"
> LINE 1: insert into badinfo values ('green');

True, we could improve that, though it could easily get messy with large
enums.


> >* Make enum_range to not be dependent on values in the target table.
> >  Remove/Obsolete enum_range( enum_column ) and replace with enum_range( 
> > typein regtype )
> > 
> >Workaround: define the enum_range( typein regtyp ) yourself.

Hmm, this is pretty clunky.  I don't think passing the OID of the enum
itself is a lot better, but if you use a regtype cast then perhaps it's
not that bad.  Perhaps we could have both functions.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[Alvaro Herrera]

Melvin Davidson wrote:

> Enums are evil!
> http://www.lornajane.net/posts/2010/is-enum-evil

???

This post is about MySQL's enums, which aren't really related to
Postgres enums:

"In order to change the allowed values of an enum column, we
need to issue an alter table statement [...]   Alter table
actually creates a new table matching the new structure, copies
all the data across, and then renames the new table to the right
name."

This is not at all how things happen in Postgres' enums.


> enums are from before there were foreign keys

In Postgres, ENUMs are pretty recent actually -- a lot newer than FKs:

commit 57690c6803525f879fe96920a05e979ece073e71
Author: Tom Lane 
AuthorDate: Mon Apr 2 03:49:42 2007 +
CommitDate: Mon Apr 2 03:49:42 2007 +

Support enum data types.  Along the way, use macros for the values of
pg_type.typtype whereever practical.  Tom Dunstan, with some kibitzing
from Tom Lane.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Email address VERP problems (was RE: [GENERAL] Does a call to a language handler provide a context/session, and somewhere to keep session data?

[Alvaro Herrera]

David Bennett wrote:
> > ow...@postgresql.org] On Behalf Of Alvaro Herrera
> 
> > On that subject.  I noticed that Outlook seems to add the "return
> > path"
> > addresses (sometimes called bounce address or envelope sender) to the
> > CC header, which sets a new record in the stupidity scale.  Since we
> > use VERP, each message gets a different return path address, so with
> > each reply you make, Outlook adds a new address to the CC.
> 
> Interesting. I use a lot of mailing lists and I've not run across one
> actually using VERP before. Is it becoming more frequent?

Not sure if it's becoming more frequent -- I only manage *this* list
server and we enabled VERP several years ago.  I thought it was common
practice ... the idea of manually managing addresses that bounce seems
completely outdated now.

> I checked the headers. It seems this list is using a VERP address for both
> the Return-path and the Reply-To, and only the Sender identifies the list
> directly.

I'm pretty sure our list server is not setting the VERP address in
Reply-To.  That would be insane, wouldn't it.  We don't touch the
Reply-To header at all.  Maybe some other program along the way modifies
the email before Outlook gets it?

> I rather think the problem is that the list software is not pruning VERP
> addresses from the emails it sends out. I don't know exactly what is causing
> them, but it certainly seems to me something that the list software could
> handle effortlessly.

Well, since the VERP addresses are not supposed to appear in those
headers, the list software doesn't try to prune -- that would be useless
99.95% of the time.  You see, this is the first time that I have seen
any mail chain do this (I've been here for several years.)

> Sorry, but there is nothing to tweak. It all seems to be working just fine
> at this end. But I will keep any eye out for extra VERPs and delete them (as
> I have on this message).

Thanks.  I think it will be better for your continued mental health to
ensure that nothing adds such addresses to Reply-To, if that is indeed
what is happening.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Does a call to a language handler provide a context/session, and somewhere to keep session data?

[Alvaro Herrera]

da...@andl.org wrote:
> > ow...@postgresql.org] On Behalf Of Adrian Klaver
> 
> > On that subject. I have followed this thread, but only sort of as the
> > quoting your email client is doing tends to obscure the flow. I know
> > for me that is making it hard to follow your ideas.
> > 
> > Is that something that can be changed?
> 
> I use Outlook, and while it has lots of options, I can only pick one. I've
> now switched to what seems to work best for plain text mailing lists, but it
> looks terrible on anything else. Very annoying.

On that subject.  I noticed that Outlook seems to add the "return path"
addresses (sometimes called bounce address or envelope sender) to the CC
header, which sets a new record in the stupidity scale.  Since we use
VERP, each message gets a different return path address, so with each
reply you make, Outlook adds a new address to the CC.

Maybe this is tweakable.  If so, please turn it off.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] query reboot pgsql 9.5.1

[Alvaro Herrera]

David G. Johnston wrote:

> ​To be more clear, you need to run a query that will complete in our
> lifetime (and without an OOM error) with all three of ANALYZE, BUFFERS, and
> TIMING specified for the EXPLAIN.

I think the problem is pretty clear.  The plan is sensible yet the
result doesn't seem to be.  Why do you think using up all the memory is
a sensible result here?

Jaime Casanova suggested that maybe the @> operator have memory leaks.
Or perhaps the GIN index machinery that's using them.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] query reboot pgsql 9.5.1

[Alvaro Herrera]

Felipe de Jesús Molina Bravo wrote:

> pba=# \i tablas.sql
> DROP TABLE
> SELECT 120130
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> DROP TABLE
> SELECT 91932
> CREATE INDEX
> CREATE INDEX
> pba=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb
> a LEFT  join _gc_cat b on ( b.arama <@ a.arama  and a.arama <@ b.arama )
> ;
> Terminado (killed)

It used up so much memory that the OOM-killer terminated it.  That
wouldn't happen with the plan you previously showed, so please do the
same without the ANALYZE option to see what plan is it trying to
execute.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Issue enabling track_counts to launch autovacuum in 9.4.5

[Alvaro Herrera]

Derek Elder wrote:

> From what I had read, this setting should be on by default. When I checked
> our other servers I see that track_counts is on and the autovacuum process
> is working correctly on them. Indeed we don't even have the setting
> explicitly listed in our postgresql.conf on these servers.
> 
> We first tried a simple restart, but that didn't work.

Try
  ALTER SYSTEM RESET track_counts;

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] commit time in logical decoding

[Alvaro Herrera]

Weiping Qu wrote:
> Hello Artur,
> 
> Thank you for your reply.
> Should it work in a stable version like Postgresql 9.4, since it's enough
> for me and I don't care whether it's 9.6 or 9.5.
> Nevertheless I will try it using 9.4.

Yes, it was introduced by a commit that's in 9.5 and up only, so 9.4
should behave as you expect.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] bloated postgres data folder, clean up

[Alvaro Herrera]

Rémi Cura wrote:
> Hey dear list,
> after a fex years of experiments and crash,
> I ended up with a grossly bloated postgres folder.
> I had about 8 Go of useless files.

Would you add a new page to the wiki with this?

https://wiki.postgresql.org/wiki/Category:Administrative_Snippets

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Only owners can ANALYZE tables...seems overly restrictive

[Alvaro Herrera]

Joshua D. Drake wrote:
> On 02/29/2016 05:31 AM, Stephen Frost wrote:

> >Realistically, ANALYZE is a background/maintenance task that autovacuum
> >should be handling for you.
> 
> Incorrect. That would be autoanalyze and although they are similar they are
> not the same. ANALYZE is used for a number of things, not the least is query
> profiling.

I think you are confusing ANALYZE with EXPLAIN ANALYZE.

I am not aware of any way in which ANALYZE is different from what you
call "autoanalyze" (which is really just an autovacuum-invoked ANALYZE
and for which we don't have any specific term).

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] FreeBSD x86 and x86_64

[Alvaro Herrera]

MEERA wrote:
> Hi all,
> 
> Any information regarding PostgreSQL support on FreeBSD platform?

You can see the list of supported platforms here:
http://buildfarm.postgresql.org/cgi-bin/show_status.pl
We seem to have FreeBSD 9.0 on gcc 4.2 and FreeBSD 10 on clang, both on
x86_64.  If you want to see it tested on 32bit x86, I suggest you put up
a permanent buildfarm machine to that effect; see 
http://wiki.postgresql.org/wiki/PostgreSQL_Buildfarm_Howto

Cheers

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Transaction ID not logged if no explicit transaction used

[Alvaro Herrera]

Tom Lane wrote:
> Alvaro Herrera <alvhe...@2ndquadrant.com> writes:
> > Mart�n Marqu�s wrote:
> >> This really gives little use for recovery_target_xid. :(
> 
> > Hmm, you can still use pg_xlogdump to figure it out from the actual WAL,
> > which has the correct XIDs.  It's obviously a worse solution though from
> > the user's POV, because it's hard to figure out what WAL record
> > corresponds to the change you care about ...
> 
> To what extent does the commit_ts infrastructure fix this?

I don't think it does at all.  You could try to find out the XID using a
timestamp you obtain from the log file (knowing that the lookups are the
opposite way, i.e. you give it an XID and it returns a timestamp); but
if that's the scenario, I think it's simpler to use the timestamp in
recovery_target_time directly.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Test CMake build

[Alvaro Herrera]

Teodor Sigaev wrote:

> Cmake 2 times faster, that is good, but I don't understand why. Which
> optimization level does cmake buld use by default? Which compiler does it
> take? It's not obvious, because cmake build hides actual compiler command
> line.

Hm, I don't think having the compile/link lines be hidden up is
acceptable.  Many times we need to debug some compile problem, and the
output is mandatory.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Test CMake build

[Alvaro Herrera]

Teodor Sigaev wrote:
> >Hm, I don't think having the compile/link lines be hidden up is
> >acceptable.  Many times we need to debug some compile problem, and the
> >output is mandatory.
> 
> +1
> 
> Although it could be fixed by
> VERBOSE=1 make

Verbose needs to be the default.   Having a QUIET mode would be nice
sometime in the future, but if I were you, I wouldn't propose that for
the first cut of this patch -- I think it's easier to sell if you keep
the current behavior unchanged.  We can discuss further improvements
later.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Transaction ID not logged if no explicit transaction used

[Alvaro Herrera]

Martín Marqués wrote:
> El 10/02/16 a las 21:46, Tom Lane escribió:

> > We could maybe fix this by redefining %x as "the current or most recent
> > xid", so that it'd still be valid for messages issued post-commit.
> > But I'm afraid that would add about as many bad behaviors as it would
> > remove.  In your example above, that would result in a pretty misleading
> > xid attached to the "begin" statement, since at that point we have
> > started a new transaction but not assigned it any xid.
> 
> This really gives little use for recovery_target_xid. :(

Hmm, you can still use pg_xlogdump to figure it out from the actual WAL,
which has the correct XIDs.  It's obviously a worse solution though from
the user's POV, because it's hard to figure out what WAL record
corresponds to the change you care about ...

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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_multixact issues

[Alvaro Herrera]

Thomas Munro wrote:

> 4.  You could look into whether all those multixacts with many member
> are really expected.  (Large numbers of concurrent FK checks or
> explicit share locks on the same rows perhaps?  A good proportion of
> this happened on one day this week I see.)

I think maybe this patch
https://www.postgresql.org/message-id/20150620043650.GX133018%40postgresql.org
should help with this.  I expect to come back to this and get it pushed
to 9.3 and 9.4 sometime ...

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general