gmane.comp.db.postgresql.devel.general

Re: [PATCH]Tablesample Submission

Simon Riggs — 2013-05-22T08:03:21


This was added to CF1 of 9.4. The patch is nowhere near committable
and hasn't been worked on at all since last time it was submitted.

It's important that we have an efficient implementation of TABLESAMPLE
in Postgres.

I'm going to remove it from CF, for now, but I'll also take
responsibility for this for 9.4, barring objections.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

MVCC catalog access

Robert Haas — 2013-05-22T02:18:21

We've had a number of discussions about the evils of SnapshotNow.  As
far as I can tell, nobody likes it and everybody wants it gone, but
there is concern about the performance impact.  I decided to do some
testing to measure the impact.  I was pleasantly surprised by the
results.

The attached patch is a quick hack to provide for MVCC catalog access.
 It adds a GUC called "mvcc_catalog_access".  When this GUC is set to
true, and heap_beginscan() or index_beginscan() is called with
SnapshotNow, they call GetLatestSnapshot() and use the resulting
snapshot in lieu of SnapshotNow.  As a debugging double-check, I
modified HeapTupleSatisfiesNow to elog(FATAL) if called with
mvcc_catalog_access is true.  Of course, both of these are dirty
hacks.  If we were actually to implement MVCC catalog access, I think
we'd probably just go through and start replacing instances of
SnapshotNow with GetLatestSnapshot(), but I wanted to make it easy to
do perf testing.

When I first made this change, I couldn't detect any real c

Re: plperl segfault in plperl_trusted_init() on kfreebsd

Christoph Berg — 2013-05-22T00:33:44

Re: Andrew Dunstan 2013-05-17 <51964770.6070405< at >dunslane.net>

I've just tried to look into this but got lost in chasing about 5
nested layers of ERRSV #defines. :-/

The crash also happens with libperl5.18 (5.18.0-1) on unstable/kfreebsd-amd64.

./configure --with-perl; make; cd src/pl/perl; make clean

postgresql-9.3/src/pl/plperl $ make PROFILE="-g -O0"
'/usr/bin/perl' ./text2macro.pl --strip='^(\#.*|\s*)$' plc_perlboot.pl plc_trusted.pl > perlchunks.h
'/usr/bin/perl' plperl_opmask.pl plperl_opmask.h
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -fpic -I. -I. -I../../../src/include -D_GNU_SOURCE  -I/usr/lib/perl/5.18/CORE  -c -o plperl.o plperl.c
'/usr/bin/perl' /usr/share/perl/5.18/ExtUtils/xsubpp -typemap /usr/share/perl/5.18/ExtUtils/typemap SPI.xs >SPI.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-lab

Re: SET work_mem = '1TB';

Gavin Flower — 2013-05-21T21:41:17

I suspect it should be fixed before it starts being a problem, for 2 
reasons:

 1. best to panic early while we have time
    (or more prosaically: doing it soon gives us more time to get it
    right without undue pressure)

 2. not able to cope with 2TB and above might put off companies with
    seriously massive databases from moving to Postgres

Probably an idea to check what other values should be increased as well.


Cheers,
Gavin

SET work_mem = '1TB';

Simon Riggs — 2013-05-21T21:13:52

I worked up a small patch to support Terabyte setting for memory.
Which is OK, but it only works for 1TB, not for 2TB or above.

Which highlights that since we measure things in kB, we have an
inherent limit of 2047GB for our memory settings. It isn't beyond
belief we'll want to go that high, or at least won't be by end 2014
and will be annoying sometime before 2020.

Solution seems to be to support something potentially bigger than INT
for GUCs. So we can reclassify GUC_UNIT_MEMORY according to the
platform we're on.

Opinions?

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Fast promotion failure

Simon Riggs — 2013-05-21T20:33:17


Well, after fighting some more with that, I've gone with the, er,
principle of slightly less ugliness.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

pg_export_snapshot on standby side

Fujii Masao — 2013-05-21T18:16:53

Hi,

We cannot run parallel pg_dump on the standby server because
pg_export_snapshot() always fails on the standby. Is this the oversight
of parallel pg_dump or pg_export_snapshot()?

pg_export_snapshot() fails in the standby because it always assigns
new XID and which is not allowed in the standby. Do we really need
to assign new XID even in the standby for the exportable snapshot?

Regards,

Re: streaming replication, "frozen snapshot backup on it" and missing relfile (postgres 9.2.3 on xfs + LVM)

Benedikt Grundmann — 2013-05-21T15:59:26

We are seeing these errors on a regular basis on the testing box now.  We
have even changed the backup script to
shutdown the hot standby, take lvm snapshot, restart the hot standby, rsync
the lvm snapshot.  It still happens.

We have never seen this before we introduced the hot standby.  So we will
now revert to taking the backups from lvm snapshots on the production
database.  If you have ideas of what else we should try / what information
we can give you to debug this let us know and we will try to so.

Until then we will sadly operate on the assumption that the combination of
hot standby and "frozen snapshot" backup of it is not production ready.

Thanks,

Bene




On Thu, May 16, 2013 at 8:10 AM, David Powers <dpowers< at >janestreet.com>wrote:

Re: fast promotion and log_checkpoints

Simon Riggs — 2013-05-21T15:02:51


This would mean we can't use the secondary checkpoint record, but we
already gave that up so should be OK.

Three people, three suggestions; so I will agree to this suggestion so
we can get on with it.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: fast promotion and log_checkpoints

Fujii Masao — 2013-05-21T14:29:15

Or, what about using CHECKPOINT_FORCE and just printing "force"?
Currently that checkpoint always starts because of existence of the
end-of-recovery record, but I think we should ensure that the checkpoint
always starts by using that flag.

Regards,

Re: pgbench vs. SERIALIZABLE

Josh Berkus — 2013-05-21T13:44:40


See, this is why I had no intention of retrying.  Since people can run
custom workloads, there's no good way for us to handle the different
permutations of what kinds of scripts people might write.

Re: Move unused buffers to freelist

Amit Kapila — 2013-05-21T10:23:56

On looking more closely at data posted by you, I believe that there is some
problem with data (8
clients < at > scale factor 1000 on master) as in all other cases, the data for
scale factor 1000 is better than 3000 except for this case.
So I think no need to run again.

Re: Fast promotion failure

Simon Riggs — 2013-05-21T08:26:01

I'm OK with that principle, as long as we don't touch ThisTimeLineID,
which has been the source of multiple bugs.

So we should set the TLI explicitly before installing, like attached patch.

Otherwise we'd need multiple permanent TLIs which would be overkill.

I feel there are problems because we set the newly selected TLI from
startup process into shared memory, then some time later we set
SharedRecoveryInProgress = false. That timing window isn't good, but I
don't see a different way.


Agreed, but looks like too much code to touch that lightly.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Move unused buffers to freelist

Amit Kapila — 2013-05-21T07:06:17

Thanks for running detailed tests



Is the above test for tpc-b?
In the above tests, there is performance increase from 1~8% and decrease
from 0.2~1.5%


  Do you want to say the reading of 1000 scale factor?
  

I have not generated numbers for read-write tests, I will check that once.
For read-only tests, the performance increase is minor and different from
what I saw. 
Few points which I could think of for difference in data:

1. In my test's I always observed best data when number of clients/threads
are equal to number of cores which in your case should be at 16.
2. I think for scale factor 100 and 300, there should not be much
performance increase, as for them they should mostly get buffer from
freelist inspite of even bgwriter adds to freelist or not. 
3. In my tests variance is for shared buffers, database size is always less
than RAM (Scale Factor -1200, approx db size 16~17GB, RAM -24 GB), but due
to variance in shared buffers, it can lead to I/O. 
4. Each run is of 20 minutes, not sure if this ha

Re: Fast promotion failure

Heikki Linnakangas — 2013-05-21T06:46:31

Hmm. lastReplayedTLI is supposed to be the timeline of the last record 
that was replayed, ie. the timeline corresponding lastReplayedEndRecPtr. 
I think it would work, but it feels like it could be an easy source of 
bugs in the future.

It might be a good idea to change walsender to not store that in 
ThisTimeLineID, though. It used to make sense for ThisTimeLineID to 
track the current recovery timeline in 9.2 and below, but now that 
walsender can be sending any older timeline, using ThisTimeLineID to 
store the latest one seems confusing.

- Heikki

Re: [PATCH] Correct release notes about DROP TABLE IF EXISTS and add, link.

Bruce Momjian — 2013-05-21T03:02:08

Yeah.  :-)  The patch added markup, and fixed the command text.

Re: [PATCH] Correct release notes about DROP TABLE IF EXISTS and add, link.

Andrew Dunstan — 2013-05-21T02:55:19

On 05/20/2013 10:38 PM, Bruce Momjian wrote:

*snort*

This would be a rather pointless command!

cheers

andrew

Re: [PATCH] Correct release notes about DROP TABLE IF EXISTS and add, link.

Bruce Momjian — 2013-05-21T02:38:50

Thanks, applied.

---------------------------------------------------------------------------

On Fri, May 17, 2013 at 03:12:21PM -0400, Joe Abbate wrote:

Re: Proposal to add --single-row to psql

Darren Duncan — 2013-05-21T02:18:16

I have actually been working on the task discussed in this thread, most relevant 
parts quoted below, for awhile now, and hope to have something concrete that you 
can use by the end of this summer.

My in-development Muldis D language is homoiconic as a core feature, its source 
code is data to it, and the native syntax of the language resembles an abstract 
syntax tree.  This tree of nodes primarily defines behavior of the code, but it 
also supports arbitrary metadata per node, which for example can be used to 
preserve concrete syntax for any programming language that can be parsed into 
Muldis D nodes or conversely generated from said.

For example, you can have one type of node defining a function, and its details 
are defined by its attributes or child nodes, such as its result type, its 
parameters, whether it is declared associative/commutative/etc or not, and the 
expression(s) defining its body.  Another type of node defines a call to a 
function, another type defines a text literal, another a rel

Removal of pageinspect--1.0.sql

Michael Paquier — 2013-05-20T23:50:25

Hi all,

The contrib module pageinspect has been upgraded to 1.1, but
pageinspect--1.0.sql is still present in source code. Shouldn't it be
removed? Please find patch attached.

Thanks

Re: Add more regression tests for dbcommands

Robins Tharakan — 2013-05-20T21:28:25

Hi,

Attached is an updated patch that does only 1 CREATE DATABASE and reuses
that for all other tests.
The code-coverage with this patch goes up from 36% to 70%.

--
Robins Tharakan


On 13 May 2013 21:04, Robins Tharakan <tharakan< at >gmail.com> wrote: