gmane.comp.db.postgresql.devel.general

Re: psql bug

Tom Lane — 2012-05-16T23:58:52

FWIW, I failed to reproduce that on any of my machines.  Maybe
your editor is leaving the tty in a funny state?

regards, tom lane

Re: Strange issues with 9.2 pg_basebackup & replication

Fujii Masao — 2012-05-16T23:29:00

What did the replica-replica do after you got such an error? Repeated
such an error? Emit PANIC error and exited? Got stuck? Successfully
reconnected to the master-replica? ....

In theory, the gap of timeline should be resolved as follows:

1. promote master-replica, which terminates cascade replication.
2. while replica-replica is repeating to reconnect to master-replica,
    if it finds new timeline history file in the archive, it adjusts
its timeline
    to new one.
3. as the result of promotion, master-replica increments its timeline,
    creates the timeline history file and archives it.
4. finally replica-replica finds new timeline history file in the archive,
    adjusts its timeline to new one, and successfully reconnects to the
    master-replica.

Note that you might see the timeline mismatch error some times
before replication is successfully restarted because of the timing
problem.


+1, at least for the case where -P option is specified in pg_basebackup.

Regards,

Re: Draft release notes complete

Bruce Momjian — 2012-05-16T21:30:27

I will make the adjustments outlined below as soon as I can.

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

On Sun, May 13, 2012 at 12:37:52AM -0400, Robert Haas wrote:

temporal support patch

Miroslav Šimulčík — 2012-05-16T21:14:53

Hi all,

as a part of my master's thesis I have created temporal support patch for
PostgreSQL. It enables the creation of special temporal tables with entries
versioning. Modifying operations (UPDATE, DELETE, TRUNCATE) on these tables
don't cause permanent changes to entries, but create new versions of them.
Thus user can easily get to the past states of the table.

Basic information on temporal databases can be found on
http://en.wikipedia.org/wiki/Temporal_database

In field of temporal databases, there are only proprietary solution
available. During the analysis I found these:
    - IBM DB2 10 for z/OS
    - Oracle 11g Workspace Manager
    - Teradata Database 13.10

Primary goal of my work was the creation of opensource solution, that is
easy to use and is backward compatible with existing applications, so that
the change of the original tables to temporal ones, does not require
changes to applications that work with them. This patch is built on
standard SQL/Temporal with some minor modifications inspire

Re: Pre-alloc ListCell's optimization

Stephen Frost — 2012-05-16T20:53:51

All,

  Here's an updated version of the patch which cleans up a couple of the
  previous issues, including addressing some of the free'ing issues.

  Looking forward to comments.

  Thanks,

Stephen

Re: Draft release notes complete

Heikki Linnakangas — 2012-05-16T19:49:25

No, it applies to indexed tables too. However, if there are indexes on 
the table, the overhead of updating the indexes will probably make any 
speedup in the heap insertions look tiny in comparison.

The optimization doesn't apply if the table has BEFORE or INSTEAD OF 
triggers, or volatile DEFAULT expressions need to be evaluated.

Re: Draft release notes complete

Jeff Janes — 2012-05-16T19:38:15

For item:
Improve COPY performance by adding tuples to the heap in batches
(Heikki Linnakangas)

I think we should point out that the batching only applies for COPY
into unindexed tables.  Nice as the feature is, that is pretty big
limitation not to mention.

Also, I wouldn't use "to the heap", as I think the main improvement is
from the batching of the wal records, not the heap, and also because
the basic improvement (get data into TABLES faster) can be understood
by users who don't know what a heap is, so we should avoid referring
to extraneous implementation details when they are not critical to
understanding the feature.

Thanks,

Jeff

Re: Pre-alloc ListCell's optimization

Stephen Frost — 2012-05-16T18:50:44

Having thought through this a bit more, with the new list
implementation, it's possible to just replace the pfree(oldhdr); with
list_free(oldhdr); and we may want to document or perhaps encourage that
users of list_concat() consider list_free()'ing the 2nd list if memory
is a concern under the new implementation.

The list_copy() will allocate a new list into subargs.  list_concat()
with the new list implementation will just append unprocessed_args on to
the end of subargs, and with unprocessed_args being replaced and that
list only being referenced by oldhdr, we can go ahead and do a shallow
free of that list.

We may want to go back and consider other uses of list_concat() under
the new list implementation, since the amount of memory leak'd now is
larger than just the list header, it's the list header and the array of
8 initial list element slots.  Still, these were the only places where
we were worried about free'ing the list header, so perhaps the other
list_concat() uses aren't in highly called areas o

psql bug

Teodor Sigaev — 2012-05-16T18:35:48

After editing query with external editor psql exits on Ctrl-C:

% psql postgres
SET
Timing is on.
psql (9.2beta1)
Type "help" for help.

postgres=#   --- Ctrl-C
postgres=#   --- Ctrl-C
postgres=#   --- Ctrl-C
postgres=# \e
...
postgres=#   --- Ctrl-C
%

Some details:
external editor - nvi/vim, OS FreeBSD 9.0 64-bit,
% ldd /usr/local/pgsql/bin/psql
/usr/local/pgsql/bin/psql:
         libpq.so.5 => /usr/local/pgsql/lib/libpq.so.5 (0x8008b4000)
         libreadline.so.8 => /lib/libreadline.so.8 (0x800ae5000)
         libc.so.7 => /lib/libc.so.7 (0x800d24000)
         libthr.so.3 => /lib/libthr.so.3 (0x80106a000)
         libncurses.so.8 => /lib/libncurses.so.8 (0x80128d000)

Re: Pre-alloc ListCell's optimization

Stephen Frost — 2012-05-16T18:05:15

Tom,

* Stephen Frost (sfrost< at >snowman.net) wrote:

A couple of these pfree's were added to simplify_or_arguments() in
commit 56c88772911b4e4c8fbb86d8687d95c3acd38a4f and the other was added
to transformFromClauseItem() in a4996a895399a4b0363c7dace71fc6ce8acbc196.

The two cases in clauses.c are pretty specific and documented:

      List *subargs = list_copy(((BoolExpr *) arg)->args);

      /* overly tense code to avoid leaking unused list header */
      if (!unprocessed_args)
          unprocessed_args = subargs;
      else
      {
          List *oldhdr = unprocessed_args;

          unprocessed_args = list_concat(subargs, unprocessed_args);
          pfree(oldhdr);
      }

which really makes me wonder if it's a pretty important cleanup due to
how this call can end up getting nested pretty deeply and the number of
potential loops.  On the surface, it looks like this whole chunk could
be reduced to a single list_concat() call.  With the new list
implementation, we'll probably want to review this area and

Re: Strange issues with 9.2 pg_basebackup & replication

Jim Nasby — 2012-05-16T18:04:15

Only *after* it was correctly setup.

Josh's point is that if you flub the configuration, you should get an error, which is not what's happening now. Right now it just comes up and acts as if nothing's wrong.

Re: Strange issues with 9.2 pg_basebackup & replication

Jim Nasby — 2012-05-16T18:02:28

Well, that is a form of testing. :)

My point was that we need some kind of regression tests around all the new replication stuff, and if you had some scripts that would be a useful starting point. But it sounds like you haven't gotten that far with it, so...

On 5/15/12 10:12 AM, Joshua Berkus wrote:

Re: [BUG] Checkpointer on hot standby runs without looking checkpoint_segments

Kyotaro HORIGUCHI — 2012-05-16T17:07:05

Hello,

On Sun, May 13, 2012 at 10:38 PM, Kyotaro HORIGUCHI
<horiguchi.kyotaro< at >lab.ntt.co.jp> wrote:

I forcused on WalRcvInProgress again to achieve this. The problem on
using it was its intermittent nature. But using IsStandbyMode to avoid
that turned out to lead another problem.

Now, the introduced function WalRcvStarted() uses WalRcvInProgress to
inform the caller if wal receiver has been started regardless of
current status.

We can avoid accelarated checkpoints before WAL receiver starts, but
checkpoints on WAL streaming will be governed with
checkpoint_segments.

I have not certain answer for the criticism that checkpoint_semgents
should be ignored even when WAL streaming.. Allowing
checkpoint_segments be null to signal no check aganst it? Introduce
another guc variable in bool to instruct to ignore checkpoint_semgnts
on WAL streaming? Or something other?

regards,

Re: Pre-alloc ListCell's optimization

Stephen Frost — 2012-05-16T17:03:57

Apologies, that wasn't quite right- it'd reduce it to 1 palloc call for
each of the 893,206 lists.  In terms of actual comparison of palloc
calls, we have to look at how many are done today for those lists:

palloc calls for:

1-node lists: 1143794
2-node lists: 673071
3-node lists: 205364
4-node lists: 133650
5-node lists: 60552
6-node lists: 28896
7-node lists: 13248
8-node lists: 27045

Total: 2,285,620

Instead, we'd have 1 palloc call for each list, or 893,206, so we'd be
removing ~1.4M calls across the regression suite.

I'll work on cleaning up the patch to be committable early in the 9.3
dev cycle, so it can get a lot of testing prior to the next release.

Thanks,

Stephen

Re: Interrupting long external library calls

Heikki Linnakangas — 2012-05-16T16:30:03

Hmm, no. CHECK_FOR_INTERRUPTS() checks the InterruptPending variable, 
but on Windows it also checks for UNBLOCKED_SIGNAL_QUEUE(). And even if 
InterruptPending is set, it's not totally certain that 
CHECK_FOR_INTERRUPTS() won't return. I think InterruptPending can be set 
spuriously (even if that's not possible today, I wouldn't rely on it), 
and if you're in a HOLD/RESUME_INTERRUPTS block, CHECK_FOR_INTERRUPTS() 
will do nothing even if InterruptPending is true.

The only sane way to make 3rd party code interruptible is to add 
CHECK_FOR_INTERRUPTS() to it, in safe places.

Re: Pre-alloc ListCell's optimization

Stephen Frost — 2012-05-16T16:24:39

Ok, it took me, uh, a little while to get around to this, but:

http://tamriel.snowman.net/~sfrost/list_histgram.svg

Is what our list lengths look like for the regression tests.  We could
do a pg_bench run, but it looks like Tom's right here- the vast majority
of our lists are small.  Highlights:

63% are 1-element
25% are 2-element

Lists of 4 or fewer elements are 97%
Lists of 8 or fewer elements are 99%

So, when it comes to palloc() reduction, this patch would eliminate 99%
of palloc's due to lists.  For the regression tests, we're talking about
reducing 893,206 palloc calls to only 1.

Thanks,

Stephen

Re: Strange issues with 9.2 pg_basebackup & replication

Thom Brown — 2012-05-16T16:07:16

I didn't shut down the master-master, but I didn't expect to need to.

I also had recovery_target_timeline set to latest.  I also tried
explicitly setting it to the new timeline, and got an error saying
there was no such timeline.


Also could we provide some feedback when using the -c spread option,
when there isn't progress within a short period of time?  Something
like "Waiting for checkpoint.  This can take up to
%checkpoint_timeout%", or something similar, rather than seeing
nothing happening and wondering if something has gone wrong.  And also
a note in the documentation saying that, on "quiet" clusters, it may
take some time before the base backup commences.  In fact, since
pg_start_backup will exhibit the same behaviour (i.e. no feedback when
waiting for a checkpoint), maybe that should return a notice (if there
are dirty pages) stating that it will complete when the next
checkpoint occurs.

Re: Strange issues with 9.2 pg_basebackup & replication

Fujii Masao — 2012-05-16T15:53:11


According to your first report, ISTM you got error messages.

Regards,

Re: Strange issues with 9.2 pg_basebackup & replication

Fujii Masao — 2012-05-16T15:49:37

What's the "remaster"?


At least cascading replication (1) allows you to adopt more flexible
configuration of servers,
(2) reduces the number of standby servers which directly connect to
the master, which
reduces the overhead of the master, and (3) provides the
infrastructure of standby-only
base backup feature.

Regards,

Re: read() returns ERANGE in Mac OS X

Alvaro Herrera — 2012-05-16T15:38:16

Excerpts from Tom Lane's message of mié may 16 09:51:26 -0400 2012:

Understood.  This is a bit at odds with what the docs say about about
the feature though:

  Detection of a damaged page header normally causes PostgreSQL to report an
error, aborting the current transaction. Setting zero_damaged_pages to on
causes the system to instead report a warning, zero out the damaged page in
memory, and continue processing. This behavior will destroy data, namely all
the rows on the damaged page. However, it does allow you to get past the error
and retrieve rows from any undamaged pages that might be present in the table.
It is useful for recovering data if corruption has occurred due to a hardware
or software error. You should generally not set this on until you have given up
hope of recovering data from the damaged pages of a table. Zeroed-out pages are
not forced to disk so it is recommended to recreate the table or the index
before turning this parameter off again. The default setting is off, and it can
only b

Re: Strange issues with 9.2 pg_basebackup & replication

Fujii Masao — 2012-05-16T15:36:26

Hmm.. when doing the same, the replica-replica successfully reconnected
to the master-replica after I shutdown the master-master and promoted the
master-replica. archive_command is the same in three servers,
restore_command is the same in two standby servers (i.e., master-replica
and replica-replica), and recovery_target_timeline is set to 'latest' in two
standby servers.


Yes.


+1 To do this, we would need to define SIGINT signal handler and make it
send QueryCancel packet when Ctrl-C is typed.

Regards,