Re: [HACKERS] Hot Standby btree delete records and vacuum_defer_cleanup_age

[Fujii Masao]

On Fri, Mar 11, 2011 at 4:46 PM, Heikki Linnakangas
 wrote:
>> Is this an open item for 9.1?
>
> Simon fixed it, commit b9075a6d2f9b07a00262a670dd60272904c79dce.

Oh, thanks!

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

[HACKERS] KEEPONLYALNUM for pg_trgm is not documented

[Itagaki Takahiro]

contrib/pg_trgm in 9.1 becomes more attractive feature by index supports
for LIKE operators, but only alphabet and numeric characters are indexed
by default. But, we can modify KEEPONLYALNUM in the source code to
keep all characters in n-gram words.

However, the limitation and KEEPONLYALNUM are not documented in the page:
  http://developer.postgresql.org/pgdocs/postgres/pgtrgm.html

An additonal documentation patches acceptable? The issues would be a FAQ for
non-English users. I heard that pg_trgm will be one of the *killer features*
of 9.1 in Japan, where N-gram based text search is preferred.

-- 
Itagaki Takahiro

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

Re: [HACKERS] KEEPONLYALNUM for pg_trgm is not documented

[Fujii Masao]

On Fri, Mar 11, 2011 at 5:52 PM, Itagaki Takahiro
 wrote:
> contrib/pg_trgm in 9.1 becomes more attractive feature by index supports
> for LIKE operators, but only alphabet and numeric characters are indexed
> by default. But, we can modify KEEPONLYALNUM in the source code to
> keep all characters in n-gram words.
>
> However, the limitation and KEEPONLYALNUM are not documented in the page:
>  http://developer.postgresql.org/pgdocs/postgres/pgtrgm.html
>
> An additonal documentation patches acceptable? The issues would be a FAQ for
> non-English users. I heard that pg_trgm will be one of the *killer features*
> of 9.1 in Japan, where N-gram based text search is preferred.

+10

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Basic Recovery Control functions for use in Hot Standby. Pause,

[Fujii Masao]

On Fri, Mar 11, 2011 at 4:36 AM, Robert Haas  wrote:
> On Tue, Mar 8, 2011 at 5:48 AM, Fujii Masao  wrote:
>> On Tue, Mar 8, 2011 at 12:04 PM, Fujii Masao  wrote:
>>> On Wed, Feb 9, 2011 at 5:12 PM, Magnus Hagander  wrote:
 I was also worried about the non-hot-standby case, but I see that the
 patch makes sure you can't enable pause when not in hot standby mode.
 Which in itself might be surprising - perhaps we need a NOTICE for
 when that happens as well?
>>>
>>> I didn't include this fix in the patch because I prefer FATAL to
>>> NOTICE for that.
>>> NOTICE doesn't stop recovery. So we might be unable to notice such a NOTICE
>>> message and stop the recovery before it's too late, i.e., the recovery has
>>> completed at the undesirable point. So I think that emitting FATAL is safer.
>>
>> I included this fix in the patch, which emits FATAL if 
>> pause_at_recovery_target
>> is enabled while hot standby is disabled and the recovery target is set.
>
> Eh, this is problematic, because you can't claim in the documentation
> (and the comments in recovery.conf.sample) that the parameter has no
> effect when Hot Standby is not enabled, and then at the same time make
> that combination a FATAL error.  I don't have a strong opinion on
> whether to change the docs or make it not FATAL, but the two have to
> match.

Yeah, since I like the former, I changed the wordings in the doc and
recovery.conf.sample. What about the attached patch?

> Committing the rest.

Thanks!

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


change_recovery_conf_sample_v4.patch
Description: Binary data

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

Re: [HACKERS] Database file copy

[Bruce Momjian]

Kevin Grittner wrote:
> Tom Lane  wrote: 
> > "Kevin Grittner"  writes:
>  
> >> shouldn't we be getting support for the new syntax added, so
> >> there can be a release or two supporting both?
> > 
> > You mean like 9.0?
>  
> Yeah, just like that.
>  
> If we're going to be supporting that long term, we should probably
> change the note about FREEZE being deprecated, though.
>  
> So, still +1 on removing the wording about FREEZE being deprecated,
> but instead we should mention what actually *is* deprecated (the
> omission of the parentheses).

Done with the attached, applied patch.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index dee1cc3..5b5b161 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -70,9 +70,9 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ 
When the option list is surrounded by parentheses, the options can be
written in any order.  Without parentheses, options must be specified
in exactly the order shown above.
-   Prior to PostgreSQL 9.0, the unparenthesized
-   syntax was the only one supported.  It is expected that all new options
-   will be supported only in the parenthesized syntax.
+   The unparenthesized syntax was added in
+   PostgreSQL 9.0;  the unparenthesized
+   syntax is deprecated.
   
  
 
@@ -102,8 +102,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ 
   Specifying FREEZE is equivalent to performing
   VACUUM with the
parameter
-  set to zero.  The FREEZE option is deprecated and
-  will be removed in a future release; set the parameter instead.
+  set to zero.
  
 


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

Re: [HACKERS] [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.

[Fujii Masao]

On Fri, Mar 11, 2011 at 5:04 AM, Robert Haas  wrote:
>>        if ((wrote_xlog && XactSyncCommit) || forceSyncCommit || nrels > 0 ||
>> SyncRepRequested())
>>
>> Whenever synchronous_replication is TRUE, we disable synchronous_commit.
>> But, before disabling that, we should check also max_wal_senders and
>> synchronous_standby_names? Otherwise, synchronous_commit can
>> be disabled unexpectedly even in non replication case.
>
> Yeah, that's bad.  At the risk of repeating myself, I don't think this
> code should be checking SyncRepRequested() in the first place.  If the
> user has turned off synchronous_commit, then we should just commit
> asynchronously, even if sync rep is otherwise in force.  Otherwise,
> this if statement is going to get really complicated.   The logic is
> already at least mildly wrong here anyway: clearly we do NOT need to
> commit synchronously if the transaction has not written xlog, even if
> sync rep is enabled.

Yeah, not to wait for replication when synchronous_commit is disabled
seems to be more reasonable.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

[HACKERS] Re: [BUGS] BUG #5842: Memory leak in PL/Python when taking slices of results

[Bruce Momjian]

What has been done with this report/fix?

---

Daniel Popowich wrote:
> 
> The following bug has been logged online:
> 
> Bug reference:  5842
> Logged by:  Daniel Popowich
> Email address:  danielpopow...@gmail.com
> PostgreSQL version: 8.4.6
> Operating system:   x86_64-pc-linux-gnu (Ubuntu 10.04.1)
> Description:Memory leak in PL/Python when taking slices of results
> Details: 
> 
> There is a memory leak in PL/Python when taking slices of results.  This was
> first discussed in pgsql-general:
> 
>   http://archives.postgresql.org/pgsql-general/2011-01/msg00367.php
> 
> Thanks to Alex Hunsaker for pinpointing the problem to slices.  The
> following code (a modification of Alex's) demonstrates the problem well...in
> a database with plpythonu installed:
> 
> -- leaks big time
> CREATE  or replace FUNCTION py_leak() RETURNS void
>LANGUAGE plpythonu
>AS $$
> results = plpy.execute("""select generate_series(0, 100)""")
> slice_creates_leak = results[:]
> for r in slice_creates_leak:
> pass
> return
> $$;
> 
> -- does not leak
> CREATE  or replace FUNCTION py_no_leak() RETURNS void
>LANGUAGE plpythonu
>AS $$
> results = plpy.execute("""select generate_series(0, 100)""")
> for noleak in results:
> pass
> return
> $$;
> 
> 
> I traced the bug to PLy_result_slice() in src/pl/plpython/plpython.c.  That
> function calls the python API function PyList_GetSlice() and erroneously
> increments the reference count before returning the result to the caller. 
> PyList_GetSlice returns a *new* reference, not a borrowed one, so it should
> just return the object as-is.
> 
> A patch is attached below.
> 
> Cheers,
> 
> Dan Popowich
> 
> 
> --
> 
> 
> *** src/pl/plpython/plpython.c~   2010-12-13 21:59:19.0 -0500
> --- src/pl/plpython/plpython.c2011-01-18 11:18:28.857831733 -0500
> ***
> *** 2328,2341 
>   static PyObject *
>   PLy_result_slice(PyObject *arg, Py_ssize_t lidx, Py_ssize_t hidx)
>   {
> - PyObject   *rv;
>   PLyResultObject *ob = (PLyResultObject *) arg;
>   
> ! rv = PyList_GetSlice(ob->rows, lidx, hidx);
> ! if (rv == NULL)
> ! return NULL;
> ! Py_INCREF(rv);
> ! return rv;
>   }
>   
>   static int
> --- 2328,2336 
>   static PyObject *
>   PLy_result_slice(PyObject *arg, Py_ssize_t lidx, Py_ssize_t hidx)
>   {
>   PLyResultObject *ob = (PLyResultObject *) arg;
>   
> ! return PyList_GetSlice(ob->rows, lidx, hidx);
>   }
>   
>   static int
> 
> -- 
> Sent via pgsql-bugs mailing list (pgsql-b...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Couple document fixes

[Bruce Momjian]

Kevin Grittner wrote:
> Thom Brown  wrote:
>  
> > I've attached a couple minor fixes to the docs.  One relating to
> > SECURITY LABEL and the other for pg_class.relpersistence
>  
> relpersistence should be "char", not char.
> Oddly enough, there is a difference.

I am unsure on that one.  We have many 'char' mentions in catalog.sgml,
and I don't see any of them shown as '"char"'.  (Wow, we should have
just called this type char1, but I think that name came from Berkeley!) 
The big problem is that the pg_type name is really "char" _without_
quotes.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Fwd: index corruption in PG 8.3.13

[Nikhil Sontakke]

Hi,

>>> 1. Somebody inserts a bunch of new tuples into the relation, causing
>>> growth in the index.
>>
>> In case it's not obvious VACUUM FULL would do precisely that.
>
> Oh, I didn't even think about that.  Yeah, that could be it, too.

Thanks a lot Greg and Robert. This theory seems very plausible. VF
must have carried out a rearrangement of heap tuples for compaction
and that might have caused new index entries which might explain the
extension of that many blocks.

> maybe VACUUM FULL - crash before checkpoint - problem with recovery?

Did I mention that an immediate shutdown was thrown into the mix just
after the VF? That is almost close to a crash and that means that the
shared buffers were not written back to the index data file. So that
should also account for all these pages still being zeroed out. So
change the above to:

> VACUUM FULL - immediate shutdown - problem with recovery?

But WAL replay should still have handled this. I would presume even an
immediate shutdown ensures that WAL is flushed to disk properly?

So that means that either there is a corner case bug in VF which adds
incorrect WAL logging in some specific btree layout scenarios or there
was indeed some bit flipping in the WAL, which caused the recovery to
prematurely end during WAL replay. What are the scenarios that you
would think can cause WAL bit flipping?

I was trying to repro this on the setup by repeatedly creating a table
with large inserts, doing lotta deletes, running VF and then issuing
immediate shutdown. However if I try to inspect the index data file at
this point in the test case, it is inconsequential as the file is
largely out of sync since its dirty shared buffers have not been
flushed. That leaves me with the option to restart and check the index
data file again for problems. If we see problems after the restart it
should generally mean WAL logging errors (but we still cannot discount
the bit flipping case I guess).

I guess a perusal of the WAL activity done by VF btree index activity
is the need of the hour..

Regards,
Nikhils

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

Re: [HACKERS] B-tree parent pointer and checkpoints

[Heikki Linnakangas]

On 10.03.2011 22:50, Bruce Momjian wrote:

Bruce Momjian wrote:


Has this been addressed?


I see we have with this commit:

9de3aa65f01fb51cbc725e8508ea233e4e92c46c


We fixed GiST. B-tree still has the issue that if you have a checkpoint 
in the middle of an insert, and crash, you might be left with a leaf 
node without a downlink in the parent.


That's relatively harmless, index searches and insertions work without 
the downlink. However, there's code in page deletion that ERRORs if the 
parent can't be found. That should be fixed.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

[HACKERS] maximum digits for NUMERIC

[Gianni Ciolli]

Hi,

maybe we should change the "1000 digits" here:

  
http://developer.postgresql.org/pgdocs/postgres/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

because ISTM that up to 2^17 digits are supported (which makes more
sense than 1000).

Best regards,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni.cio...@2ndquadrant.it | www.2ndquadrant.it

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

Re: [HACKERS] Use of O_DIRECT only for open_* sync options

[Bruce Momjian]

Greg Smith wrote:
> Bruce Momjian wrote:
> > xlogdefs.h says:
> >
> > /*
> >  *  Because O_DIRECT bypasses the kernel buffers, and because we never
> >  *  read those buffers except during crash recovery, it is a win to use
> >  *  it in all cases where we sync on each write().  We could allow O_DIRECT
> >  *  with fsync(), but because skipping the kernel buffer forces writes out
> >  *  quickly, it seems best just to use it for O_SYNC.  It is hard to imagine
> >  *  how fsync() could be a win for O_DIRECT compared to O_SYNC and O_DIRECT.
> >  *  Also, O_DIRECT is never enough to force data to the drives, it merely
> >  *  tries to bypass the kernel cache, so we still need O_SYNC or fsync().
> >  */
> >
> > This seems wrong because fsync() can win if there are two writes before
> > the sync call.  Can kernels not issue fsync() if the write was O_DIRECT?
> > If that is the cause, we should document it.
> >   
> 
> The comment does look busted, because you did imagine exactly a case 
> where they might be combined.  The only incompatibility that I'm aware 
> of is that O_DIRECT requires reads and writes to be aligned properly, so 
> you can't use it in random application code unless it's aware of that.  
> O_DIRECT and fsync are compatible; for example, MySQL allows combining 
> the two:  http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html

C comment updated in git head:

 *  Because O_DIRECT bypasses the kernel buffers, and because we never
 *  read those buffers except during crash recovery or if wal_level != minimal,
 *  it is a win to use it in all cases where we sync on each write().  We could
 *  allow O_DIRECT with fsync(), but it is unclear if fsync() could process
 *  writes not buffered in the kernel.  Also, O_DIRECT is never enough to force
 *  data to the drives, it merely tries to bypass the kernel cache, so we still
 *  need O_SYNC/O_DSYNC.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Sync Rep v19

[Fujii Masao]

On Fri, Mar 11, 2011 at 5:50 AM, Robert Haas  wrote:
> On Thu, Mar 10, 2011 at 3:29 PM, Dimitri Fontaine
>  wrote:
>> Robert Haas  writes:
>>> they are, but there's no easy way to figure out what that means in
>>> terms of wall-clock time, which I think would be useful.
>>
>> Jan Wieck had a detailed proposal to make that happen at last developper
>> meeting, but then ran out of time to implement it for 9.1 it seems.  The
>> idea was basically to have a ticker in core, an SRF that would associate
>> txid_snapshot with wall clock time.  Lots of good things would come from
>> that.
>>
>>  http://archives.postgresql.org/pgsql-hackers/2010-05/msg01209.php
>>
>> Of course if you think that's important enough for you to implement it
>> between now and beta, that would be great :)
>
> I think that's actually something a little different, and more
> complicated, but I do think it'd be useful.  I was hoping there was a
> simple way to get some kind of time-based information into
> pg_stat_replication, but if there isn't, there isn't.

How about sending the timestamp of last applied transaction
(i.e., this is the return value of pg_last_xact_replay_timestamp)
from the standby to the master, and reporting it in
pg_stat_replication? Then you can see the lag by comparing
it with current_timestamp.

But since the last replay timestamp doesn't advance (but
current timestamp advances) if there is no work on the master,
the calculated lag might be unexpectedly too large. So, to
calculate the exact lag, I'm thinking that we should introduce
new function which returns the timestamp of the last transaction
written in the master.

Thought?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

Re: [HACKERS] SAVEPOINTs and COMMIT performance

[Bruce Momjian]

What happened to this patch?

---

Simon Riggs wrote:
> On Sun, 2011-02-06 at 12:11 -0500, Bruce Momjian wrote:
> > Did this ever get addressed?
> 
> Patch attached.
> 
> Seems like the easiest fix I can come up with.
> 
> > Simon Riggs wrote:
> > > 
> > > As part of a performance investigation for a customer I've noticed an
> > > O(N^2) performance issue on COMMITs of transactions that contain many
> > > SAVEPOINTs. I've consistently measured COMMIT times of around 9 seconds,
> > > with 49% CPU, mostly in LockReassignCurrentOwner().
> > > 
> > > BEGIN;
> > > INSERT...
> > > SAVEPOINT ...
> > > INSERT...
> > > SAVEPOINT ...
> > > ... (repeat 10,000 times)
> > > COMMIT;
> > > 
> > > The way SAVEPOINTs work is that each is nested within the previous one,
> > > so that at COMMIT time we must recursively commit all the
> > > subtransactions before we issue final commit.
> > > 
> > > That's a shame because ResourceOwnerReleaseInternal() contains an
> > > optimisation to speed up final commit, by calling ProcReleaseLocks().
> > > 
> > > What we actually do is recursively call LockReassignCurrentOwner() which
> > > sequentially scans LockMethodLocalHash at each level of transaction. The
> > > comments refer to this as "retail" rather than the wholesale method,
> > > which never gets to execute anything worthwhile in this case.
> > > 
> > > This issue does NOT occur in PLpgSQL functions that contain many
> > > EXCEPTION clauses in a loop, since in that case the subtransactions are
> > > started and committed from the top level so that the subxact nesting
> > > never goes too deep.
> > > 
> > > Fix looks like we need special handling for the depth-first case, rather
> > > than just a recursion loop in CommitTransactionCommand().
> > > 
> > > Issues looks like it goes all the way back, no fix for 9.0.
> > > 
> > > I notice also that the nesting model of SAVEPOINTs also means that
> > > read-only subtransactions will still generate an xid when followed by a
> > > DML statement. That's unnecessary, but required given current design.
> > > 
> > > -- 
> > >  Simon Riggs   www.2ndQuadrant.com
> > >  PostgreSQL Development, 24x7 Support, Training and Services
> > > 
> > > 
> > > -- 
> > > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> > > To make changes to your subscription:
> > > http://www.postgresql.org/mailpref/pgsql-hackers
> > 
> 
> -- 
>  Simon Riggs   http://www.2ndQuadrant.com/books/
>  PostgreSQL Development, 24x7 Support, Training and Services
>  

[ Attachment, skipping... ]

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] multiple -f support

[Bruce Momjian]

Robert Haas wrote:
> On Sun, Feb 6, 2011 at 11:16 AM, Bruce Momjian  wrote:
> > I assume having psql support multiple -f files is not a high priority or
> > something we don't want.
> 
> IIRC, nobody objected to the basic concept, and it seems useful.  I
> thought we were pretty close to committing something along those lines
> at one point, actually.  I don't remember exactly where the wheels
> came off.
> 
> Maybe a TODO?

Added to the psql section:

|Allow processing of multiple -f (file) options

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] SAVEPOINTs and COMMIT performance

[Robert Haas]

On Fri, Mar 11, 2011 at 7:18 AM, Bruce Momjian  wrote:
> What happened to this patch?

I added it to the next CommitFest.  It would be reasonably to apply it
sooner, perhaps, but nobody's reviewed it.  Want to volunteer?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

[HACKERS] Re: [COMMITTERS] pgsql: Document that the parenthesized VACUUM syntax is deprecated, not

[Robert Haas]

On Fri, Mar 11, 2011 at 5:34 AM, Bruce Momjian  wrote:
> Document that the parenthesized VACUUM syntax is deprecated, not the
> FREEZE functionality.

The text you added here is flat-out wrong (you used "unparenthesized"
in both halves of the sentence), and it's also not as clear as the
text it replaced.  I'd suggest reverting this, and instead adding a
sentence that says simply:

The unparenthesized syntax is deprecated.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Sync Rep v19

[Robert Haas]

On Fri, Mar 11, 2011 at 7:08 AM, Fujii Masao  wrote:
> On Fri, Mar 11, 2011 at 5:50 AM, Robert Haas  wrote:
>> On Thu, Mar 10, 2011 at 3:29 PM, Dimitri Fontaine
>>  wrote:
>>> Robert Haas  writes:
 they are, but there's no easy way to figure out what that means in
 terms of wall-clock time, which I think would be useful.
>>>
>>> Jan Wieck had a detailed proposal to make that happen at last developper
>>> meeting, but then ran out of time to implement it for 9.1 it seems.  The
>>> idea was basically to have a ticker in core, an SRF that would associate
>>> txid_snapshot with wall clock time.  Lots of good things would come from
>>> that.
>>>
>>>  http://archives.postgresql.org/pgsql-hackers/2010-05/msg01209.php
>>>
>>> Of course if you think that's important enough for you to implement it
>>> between now and beta, that would be great :)
>>
>> I think that's actually something a little different, and more
>> complicated, but I do think it'd be useful.  I was hoping there was a
>> simple way to get some kind of time-based information into
>> pg_stat_replication, but if there isn't, there isn't.
>
> How about sending the timestamp of last applied transaction
> (i.e., this is the return value of pg_last_xact_replay_timestamp)
> from the standby to the master, and reporting it in
> pg_stat_replication? Then you can see the lag by comparing
> it with current_timestamp.
>
> But since the last replay timestamp doesn't advance (but
> current timestamp advances) if there is no work on the master,
> the calculated lag might be unexpectedly too large. So, to
> calculate the exact lag, I'm thinking that we should introduce
> new function which returns the timestamp of the last transaction
> written in the master.
>
> Thought?

Hmm... where would we get that value from?  And what if no
transactions are running on the master?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

[HACKERS] Re: [COMMITTERS] pgsql: Document that the parenthesized VACUUM syntax is deprecated, not

[Robert Haas]

On Fri, Mar 11, 2011 at 7:58 AM, Bruce Momjian  wrote:
> Robert Haas wrote:
>> On Fri, Mar 11, 2011 at 5:34 AM, Bruce Momjian  wrote:
>> > Document that the parenthesized VACUUM syntax is deprecated, not the
>> > FREEZE functionality.
>>
>> The text you added here is flat-out wrong (you used "unparenthesized"
>> in both halves of the sentence), and it's also not as clear as the
>> text it replaced.  I'd suggest reverting this, and instead adding a
>> sentence that says simply:
>>
>> The unparenthesized syntax is deprecated.
>
> Fixed.  Other changes?  I didn't like the original sentence because it
> started by mentioning 9.0 rather than the syntax itself.

Hmm, well I don't know.  I thought the original phrasing was clearer.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Fwd: index corruption in PG 8.3.13

[Robert Haas]

On Fri, Mar 11, 2011 at 6:17 AM, Nikhil Sontakke
 wrote:
>> VACUUM FULL - immediate shutdown - problem with recovery?

An immediate shutdown == an intentional crash.  OK, so you have the
VACUUM FULL and the immediate shutdown just afterward.  So we just
need to figure out what happened during recovery.

> But WAL replay should still have handled this. I would presume even an
> immediate shutdown ensures that WAL is flushed to disk properly?

I'm not sure, but I doubt it.  If the VACUUM FULL committed, then the
WAL records should be on disk, but if the immediate shutdown happened
while it was still running, then the WAL records might still be in
wal_buffers, in which case I don't think they'll get written out and
thus zero pages in the index are to be expected.  Now that doesn't
explain any other corruption in the file, but I believe all-zeroes
pages in a relation are an expected consequence of an unclean
shutdown.  But assuming the VF actually committed before the immediate
shutdown, there must be something else going on, since by that point
XLOG should have been flushed.

> So that means that either there is a corner case bug in VF which adds
> incorrect WAL logging in some specific btree layout scenarios or there
> was indeed some bit flipping in the WAL, which caused the recovery to
> prematurely end during WAL replay. What are the scenarios that you
> would think can cause WAL bit flipping?

Some kind of fluke hard drive malfunction, maybe?  I know that the
incidence of a hard drive being told to write A and actually writing B
is very low, but it's probably not exactly zero.  Do you have the logs
from the recovery following the immediate shutdown?  Anything
interesting there?

Or, as you say, there could be a corner-case VF bug.

> I was trying to repro this on the setup by repeatedly creating a table
> with large inserts, doing lotta deletes, running VF and then issuing
> immediate shutdown. However if I try to inspect the index data file at
> this point in the test case, it is inconsequential as the file is
> largely out of sync since its dirty shared buffers have not been
> flushed. That leaves me with the option to restart and check the index
> data file again for problems. If we see problems after the restart it
> should generally mean WAL logging errors (but we still cannot discount
> the bit flipping case I guess).

contrib/pageinspect might help.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Replication server timeout patch

[Fujii Masao]

On Mon, Mar 7, 2011 at 8:47 PM, Fujii Masao  wrote:
> On Sun, Mar 6, 2011 at 11:10 PM, Fujii Masao  wrote:
>> On Sun, Mar 6, 2011 at 5:03 PM, Fujii Masao  wrote:
 Why does internal_flush_if_writable compute bufptr differently from
 internal_flush?  And shouldn't it be static?

 It seems to me that this ought to be refactored so that you don't
 duplicate so much code.  Maybe static int internal_flush(bool
 nonblocking).

 I don't think that the while (bufptr < bufend) loop needs to contain
 the code to set and clear the nonblocking state.  You could do the
 whole loop with nonblocking mode turned on and then reenable it just
 once at the end.  Besides possibly being clearer, that would be more
 efficient and leave less room for unexpected failures.
>>>
>>> All these comments seem to make sense. Will fix. Thanks!
>>
>> Done. I attached the updated patch.
>
> I rebased the patch against current git master.

I added this replication timeout patch into next CF.

I explain why this feature is required for the future review;

Without this feature, walsender might unexpectedly remain for a while when
the standby crashes or the network outage happens. TCP keepalive can
improve this situation to　a certain extent, but it's not perfect. Remaining
walsender can cause some problems.

For example, when hot_standby_feedback is enabled, such a remaining
walsender would prevent oldest xmin from advancing and interfere with
vacuuming on the master. For example, when you use synchronous
replication and walsender in SYNC mode gets stuck, any synchronous
standby candidate cannot switch to SYNC mode until that walsender exits,
and all the transactions would pause.

This feature causes walsender to exit when there is no reply from the
standby before the replication timeout expires. Then we can avoid the
above problems.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

Re: [HACKERS] Replication server timeout patch

[Robert Haas]

On Fri, Mar 11, 2011 at 8:14 AM, Fujii Masao  wrote:
> On Mon, Mar 7, 2011 at 8:47 PM, Fujii Masao  wrote:
>> On Sun, Mar 6, 2011 at 11:10 PM, Fujii Masao  wrote:
>>> On Sun, Mar 6, 2011 at 5:03 PM, Fujii Masao  wrote:
> Why does internal_flush_if_writable compute bufptr differently from
> internal_flush?  And shouldn't it be static?
>
> It seems to me that this ought to be refactored so that you don't
> duplicate so much code.  Maybe static int internal_flush(bool
> nonblocking).
>
> I don't think that the while (bufptr < bufend) loop needs to contain
> the code to set and clear the nonblocking state.  You could do the
> whole loop with nonblocking mode turned on and then reenable it just
> once at the end.  Besides possibly being clearer, that would be more
> efficient and leave less room for unexpected failures.

 All these comments seem to make sense. Will fix. Thanks!
>>>
>>> Done. I attached the updated patch.
>>
>> I rebased the patch against current git master.
>
> I added this replication timeout patch into next CF.
>
> I explain why this feature is required for the future review;
>
> Without this feature, walsender might unexpectedly remain for a while when
> the standby crashes or the network outage happens. TCP keepalive can
> improve this situation to　a certain extent, but it's not perfect. Remaining
> walsender can cause some problems.
>
> For example, when hot_standby_feedback is enabled, such a remaining
> walsender would prevent oldest xmin from advancing and interfere with
> vacuuming on the master. For example, when you use synchronous
> replication and walsender in SYNC mode gets stuck, any synchronous
> standby candidate cannot switch to SYNC mode until that walsender exits,
> and all the transactions would pause.
>
> This feature causes walsender to exit when there is no reply from the
> standby before the replication timeout expires. Then we can avoid the
> above problems.

I think we should consider making this change for 9.1.  This is a real
wart, and it's going to become even more of a problem with sync rep, I
think.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Sync Rep v19

[Fujii Masao]

On Fri, Mar 11, 2011 at 10:02 PM, Robert Haas  wrote:
>> How about sending the timestamp of last applied transaction
>> (i.e., this is the return value of pg_last_xact_replay_timestamp)
>> from the standby to the master, and reporting it in
>> pg_stat_replication? Then you can see the lag by comparing
>> it with current_timestamp.
>>
>> But since the last replay timestamp doesn't advance (but
>> current timestamp advances) if there is no work on the master,
>> the calculated lag might be unexpectedly too large. So, to
>> calculate the exact lag, I'm thinking that we should introduce
>> new function which returns the timestamp of the last transaction
>> written in the master.
>>
>> Thought?
>
> Hmm... where would we get that value from?

xl_xact_commit->xact_time (which is set in RecordTransactionCommit)
and xl_xact_abort->xact_time (which is set in RecordTransactionAbort).

> And what if no
> transactions are running on the master?

In that case, the last write WAL timestamp would become equal to the
last replay WAL timestamp. So we can see that there is no lag.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

Re: [HACKERS] Typed-tables patch broke pg_upgrade

[Bruce Momjian]

Is this still an open bug?

---

Tom Lane wrote:
> I find that pg_upgrade fails in HEAD when asked to do a 9.1-to-9.1
> upgrade of the regression database.  It gets to this bit of the
> restore script:
> 
> CREATE TABLE test_tbl2 OF public.test_type2;
> 
> -- For binary upgrade, recreate dropped column.
> UPDATE pg_catalog.pg_attribute
> SET attlen = -1, attalign = 'i', attbyval = false
> WHERE attname = 'pg.dropped.2'
>   AND attrelid = 'test_tbl2'::pg_catalog.regclass;
> ALTER TABLE ONLY test_tbl2 DROP COLUMN "pg.dropped.2";
> 
> and fails with 
> 
> ERROR:  cannot drop column from typed table
> 
> which probably is because test_type2 has a dropped column.
> 
> Somebody has failed to think through something, because if this state of
> affairs was allowed to be created during the regression tests, why
> should we not be able to restore it?
> 
> (pg_upgrade's ENUM support is broken too, but at least that one is a
> one-line fix.)
> 
>   regards, tom lane

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Replication server timeout patch

[Fujii Masao]

On Fri, Mar 11, 2011 at 10:18 PM, Robert Haas  wrote:
>> I added this replication timeout patch into next CF.
>>
>> I explain why this feature is required for the future review;
>>
>> Without this feature, walsender might unexpectedly remain for a while when
>> the standby crashes or the network outage happens. TCP keepalive can
>> improve this situation to　a certain extent, but it's not perfect. Remaining
>> walsender can cause some problems.
>>
>> For example, when hot_standby_feedback is enabled, such a remaining
>> walsender would prevent oldest xmin from advancing and interfere with
>> vacuuming on the master. For example, when you use synchronous
>> replication and walsender in SYNC mode gets stuck, any synchronous
>> standby candidate cannot switch to SYNC mode until that walsender exits,
>> and all the transactions would pause.
>>
>> This feature causes walsender to exit when there is no reply from the
>> standby before the replication timeout expires. Then we can avoid the
>> above problems.
>
> I think we should consider making this change for 9.1.  This is a real
> wart, and it's going to become even more of a problem with sync rep, I
> think.

Yeah, that's a welcome! Please feel free to review the patch.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

Re: [HACKERS] Range Types: << >> -|- ops vs empty range

[Bruce Momjian]

Where are we on this?

---

Erik Rijkers wrote:
> On Wed, February 9, 2011 09:35, Jeff Davis wrote:
> > Updated patch.
> >
> 
> The operators  <<  >>  and -|-  have the following behavior with empty ranges:
> 
> testdb=# select '-'::int4range << range(200,300);
> ERROR:  empty range
> testdb=# select '-'::int4range >> range(200,300);
> ERROR:  empty range
> testdb=# select '-'::int4range -|- range(200,300);
> ERROR:  empty range
> 
> I'm not sure if that is deliberate behavior, but they seem
> almost bugs to me.
> 
> Wouldn't it be better (and more practical) if these would
> return false (or perhaps NULL, for 'unknown') ?
> 
> (the same goes for all the other range types, btw.)
> 
> 
> Erik Rijkers
> 
> 
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] pg_terminate_backend and pg_cancel_backend by not administrator user

[Bruce Momjian]

Kevin Grittner wrote:
> Torello Querci  wrote:
>  
> > I attach a path for this
>  
> It's too late in the release cycle to consider this for version 9.1.
> Please add it to the open CommitFest for consideration for 9.2:
>  
> https://commitfest.postgresql.org/action/commitfest_view/open

I have added it to the next commit fest.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Add support for logging the current role

[Bruce Momjian]

Is this something for the next commit-fest?

---

Stephen Frost wrote:
-- Start of PGP signed section.
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > Robert Haas  writes:
> > > It seems there's at least one more thing to worry about here, which is
> > > the overhead of this computation when CSV logging is in use.  If no
> > > SET ROLE or SET SESSION AUTHORIZATION commands are in use, the code
> > > will call show_role(), which will return "none".  We'll then strcmp()
> > > that against "none" and decide to call show_session_authorization(),
> > > which will call strtoul() to find the comma separator and then return
> > > a pointer to the string that follows it.  Now, none of that is
> > > enormously expensive, so maybe it's not worth worrying about, but
> > > since logging can be a hotspot, I thought I'd mention it and solicit
> > > an opinion on whether that's likely to be a problem in practice.
> > 
> > Well, in the first place, going through two not-very-related APIs in
> > order to reverse-engineer what miscinit.c already knows is pretty silly
> > (not to mention full of possible bugs).  We ought to be looking at the
> > GetUserId state directly.
> 
> GetUserId can end up being set in a number of places though, often in
> places where we can't fail (SetUserIdAndSecContext has some nice
> comments on this).
> 
> > Now you will complain that elog.c mustn't try to map that OID back to
> > string form, which is true.  But IIRC, we used to keep the current
> > userid stored in both OID and string form.  The string form was removed
> > as unnecessary overhead, but maybe it'd be a good idea to put that back.
> 
> The OID and the string are kept in the role_string and
> session_authorization_string GUCs respectively.  They're just not in a
> terribly useful format, and because SetUserId() can change things w/o
> the GUCs getting updated, there's a risk that they're wrong, which is
> why show_role() does the stroul() dance to check if GetCurrentRoleId()
> matches to what it stuffed into role_string.
> 
> > In short, add a bit of overhead at SetUserId time in order to make this
> > cheap (and accurate) in elog.c.
> 
> We can't do the lookup in SetUserIDAndSecContext(), and I'm not
> convinced we actually want to anyway, since that would end up returning
> what the role is inside of security definer functions and the like.
> We're already setting a variable in assign_session_authorization and
> assign_role that has the information we need.  We could inspect
> role_string ourselves (including the strcmp() and strtoul()) instead
> of asking show_role() to do it for us but that doesn't strike me as all
> *that* much of an improvement and goes around the API that at least
> exists.
> 
> We could certainly have a second set of variables which are set by
> assign_role/assign_session_authorization that are in a format we can
> more easily use but what would that mean for the GUC variables..?  I
> don't know that we'd want to keep them duplicating the data..  Would it
> be possible to actually use a struct instead of a straight-up string
> there?  Is there any particular reason we keep monkeying around with
> storing the OID, superuser bit, role name, etc, as a string anyway..?
> 
>   Thanks,
> 
>   Stephen
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Prefered Types

[Robert Haas]

On Thu, Mar 10, 2011 at 8:12 AM, Zotov  wrote:
> Hello, i  have an old system where used implicit casting
> float<->integer
> numeric<->float
> numeric<->integer
>
> I want define implicit casts, but postgresql don`t know cast priority
> now postgresql have PREFERRED flag, but only flag
> I can`t define prefer level like
> Integer=0
> Numeric=1
> Float=2
> Maybe
> text = 2 or 3
> and other to define My prefer cast more detail than just flag
> i understand what it more dificult tuning, but more flexible
> now i can only create duplicate operators like
> numeric+integer, integer+numeric, integer>numeric and many other
> What can i do? Can i wait for prefer flag changed to prefer level?

Interestingly, I've also had the thought that it might make sense to
change typispreferred to an integer typpreference.  But I'm not sure
we actually have any consensus on that point, and it probably wouldn't
happen until 9.2 at the earliest, so you're probably best off finding
some other way to attack the problem.  It's not going to help with
text vs. integer/float/numeric anyway, I think, because they have
different typcategory values.  The whole typcategory system seems a
little wonky to me, actually...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Sync Rep v19

[Robert Haas]

On Fri, Mar 11, 2011 at 8:21 AM, Fujii Masao  wrote:
> On Fri, Mar 11, 2011 at 10:02 PM, Robert Haas  wrote:
>>> How about sending the timestamp of last applied transaction
>>> (i.e., this is the return value of pg_last_xact_replay_timestamp)
>>> from the standby to the master, and reporting it in
>>> pg_stat_replication? Then you can see the lag by comparing
>>> it with current_timestamp.
>>>
>>> But since the last replay timestamp doesn't advance (but
>>> current timestamp advances) if there is no work on the master,
>>> the calculated lag might be unexpectedly too large. So, to
>>> calculate the exact lag, I'm thinking that we should introduce
>>> new function which returns the timestamp of the last transaction
>>> written in the master.
>>>
>>> Thought?
>>
>> Hmm... where would we get that value from?
>
> xl_xact_commit->xact_time (which is set in RecordTransactionCommit)
> and xl_xact_abort->xact_time (which is set in RecordTransactionAbort).
>
>> And what if no
>> transactions are running on the master?
>
> In that case, the last write WAL timestamp would become equal to the
> last replay WAL timestamp. So we can see that there is no lag.

Oh, I see (I think).  You're talking about write/replay lag, but I was
thinking of master/slave transmission lag.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] KEEPONLYALNUM for pg_trgm is not documented

[Robert Haas]

On Fri, Mar 11, 2011 at 3:59 AM, Fujii Masao  wrote:
> On Fri, Mar 11, 2011 at 5:52 PM, Itagaki Takahiro
>  wrote:
>> contrib/pg_trgm in 9.1 becomes more attractive feature by index supports
>> for LIKE operators, but only alphabet and numeric characters are indexed
>> by default. But, we can modify KEEPONLYALNUM in the source code to
>> keep all characters in n-gram words.
>>
>> However, the limitation and KEEPONLYALNUM are not documented in the page:
>>  http://developer.postgresql.org/pgdocs/postgres/pgtrgm.html
>>
>> An additonal documentation patches acceptable? The issues would be a FAQ for
>> non-English users. I heard that pg_trgm will be one of the *killer features*
>> of 9.1 in Japan, where N-gram based text search is preferred.
>
> +10

It's certainly not too late for doc patches.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Add support for logging the current role

[Stephen Frost]

* Bruce Momjian (br...@momjian.us) wrote:
> Is this something for the next commit-fest?

I already moved it there..

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Replication server timeout patch

[Bruce Momjian]

Fujii Masao wrote:
> On Fri, Mar 11, 2011 at 10:18 PM, Robert Haas  wrote:
> >> I added this replication timeout patch into next CF.
> >>
> >> I explain why this feature is required for the future review;
> >>
> >> Without this feature, walsender might unexpectedly remain for a while when
> >> the standby crashes or the network outage happens. TCP keepalive can
> >> improve this situation to?a certain extent, but it's not perfect. Remaining
> >> walsender can cause some problems.
> >>
> >> For example, when hot_standby_feedback is enabled, such a remaining
> >> walsender would prevent oldest xmin from advancing and interfere with
> >> vacuuming on the master. For example, when you use synchronous
> >> replication and walsender in SYNC mode gets stuck, any synchronous
> >> standby candidate cannot switch to SYNC mode until that walsender exits,
> >> and all the transactions would pause.
> >>
> >> This feature causes walsender to exit when there is no reply from the
> >> standby before the replication timeout expires. Then we can avoid the
> >> above problems.
> >
> > I think we should consider making this change for 9.1. ?This is a real
> > wart, and it's going to become even more of a problem with sync rep, I
> > think.
> 
> Yeah, that's a welcome! Please feel free to review the patch.

It is already in the next commitfest, so if someone wants to add it as
an open 9.1 item, go ahead.  I am unclear of this so I am not adding it.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Fwd: index corruption in PG 8.3.13

[Nikhil Sontakke]

>>> VACUUM FULL - immediate shutdown - problem with recovery?
>
> An immediate shutdown == an intentional crash.  OK, so you have the
> VACUUM FULL and the immediate shutdown just afterward.  So we just
> need to figure out what happened during recovery.
>

Right.

>> But WAL replay should still have handled this. I would presume even an
>> immediate shutdown ensures that WAL is flushed to disk properly?
>
> I'm not sure, but I doubt it.  If the VACUUM FULL committed, then the
> WAL records should be on disk, but if the immediate shutdown happened
> while it was still running, then the WAL records might still be in
> wal_buffers, in which case I don't think they'll get written out and
> thus zero pages in the index are to be expected.  Now that doesn't
> explain any other corruption in the file, but I believe all-zeroes
> pages in a relation are an expected consequence of an unclean
> shutdown.  But assuming the VF actually committed before the immediate
> shutdown, there must be something else going on, since by that point
> XLOG should have been flushed.
>

Oh yeah, so if VF committed, the xlog should have been ok too, but
can't say the same about the shared buffers.

>> So that means that either there is a corner case bug in VF which adds
>> incorrect WAL logging in some specific btree layout scenarios or there
>> was indeed some bit flipping in the WAL, which caused the recovery to
>> prematurely end during WAL replay. What are the scenarios that you
>> would think can cause WAL bit flipping?
>
> Some kind of fluke hard drive malfunction, maybe?  I know that the
> incidence of a hard drive being told to write A and actually writing B
> is very low, but it's probably not exactly zero.  Do you have the logs
> from the recovery following the immediate shutdown?  Anything
> interesting there?
>

Unfortunately we do not have the recovery logs. Would have loved to
see some signs about some issues in the WAL replay to confirm the
theory about bit flipping..

> Or, as you say, there could be a corner-case VF bug.
>

Yeah, much harder to find by just eyeballing the code I guess :)

Regards,
Nikhils

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Basic Recovery Control functions for use in Hot Standby. Pause,

[Tom Lane]

Fujii Masao  writes:
> Yeah, since I like the former, I changed the wordings in the doc and
> recovery.conf.sample. What about the attached patch?

Please stop plastering the code with elog(FATAL) calls.  Those are
hardly ever appropriate.  In contexts where it might be reasonable
to do that, the error handler will treat ERROR like FATAL anyway.

regards, tom lane

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

Re: [HACKERS] Indent authentication overloading

[Peter Eisentraut]

On tor, 2011-03-10 at 22:45 +0100, Magnus Hagander wrote:
> On Thu, Mar 10, 2011 at 22:22, Bruce Momjian  wrote:
> >
> > Added to TODO:
> >
> >Rename unix domain socket 'ident' connections to 'peer', to avoid
> >confusion with TCP 'ident'
> 
> Should we consider adding "peer" as an alias for "ident" already in
> 9.1 (and change the default pg_hba.conf template), and then deprecate
> ident for 9.2 and remove it in 9.3 or something? By adding the alias
> now (yes, I know it's not in the last CF :P), we can move what's going
> to be a long process up one release...

Might as well, if you can get it done soon.  The documentation might
need more extensive adjustments.


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

Re: [HACKERS] maximum digits for NUMERIC

[Tom Lane]

Gianni Ciolli  writes:
> maybe we should change the "1000 digits" here:

>   
> http://developer.postgresql.org/pgdocs/postgres/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

> because ISTM that up to 2^17 digits are supported

This is incorrect.  (You're confusing the number of stored digits
with the location of the decimal point.)

regards, tom lane

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

Re: [HACKERS] maximum digits for NUMERIC

[Gianni Ciolli]

On Fri, Mar 11, 2011 at 09:38:03AM -0500, Tom Lane wrote:
> Gianni Ciolli  writes:
> > maybe we should change the "1000 digits" here:
> 
> >   
> > http://developer.postgresql.org/pgdocs/postgres/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
> 
> > because ISTM that up to 2^17 digits are supported
> 
> This is incorrect.  (You're confusing the number of stored digits
> with the location of the decimal point.)

My understanding of the documentation is that precision is defined as
"the total count of significant digits in the whole number" while the
location of the decimal point can be determined by the scale, which is
defined as "the count of decimal digits in the fractional part, to the
right of the decimal point".

The documentation I mentioned previously starts with

  "The type numeric can store numbers with up to 1000 digits of
  precision and perform calculations exactly."

and I was able to store a base 10 integers with up to 2^17 digits in a
NUMERIC; so I still believe that the documentation is incorrect in
saying that (by my understanding of the definition of significant
digits in an exact integer).

If there is a limit of 1000 on the number of fractional digits to the
right of the decimal points, then we should change that wording
(unfortunately I won't be able to run this test before UTC+0 evening).

Best regards,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni.cio...@2ndquadrant.it | www.2ndquadrant.it

p.s. my small investigation started from having read the
 documentation, having incorrectly believed that NUMERIC would
 have rejected integers greater than 10^1000, and finding
 experimentally that the threshold is about 10^(2^17) (all with
 9.0).


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

Re: [HACKERS] why is max standby delay only 35 minutes?

[Bruce Momjian]

FYI, this is now on the TODO list:

Increase maximum values for max_standby_streaming_delay and
log_min_duration_statement

* http://archives.postgresql.org/pgsql-hackers/2010-12/msg01517.php 

---

Magnus Hagander wrote:
> On Fri, Mar 4, 2011 at 18:19, Robert Treat  wrote:
> > On Fri, Mar 4, 2011 at 2:03 AM, Magnus Hagander  wrote:
> >> On Fri, Mar 4, 2011 at 04:00, Robert Treat  wrote:
> >>> I have a server where I wanted to do some reporting on a standby, and
> >>> wanted to set the max standby delay to 1 hour. upon doing that, i get
> >>> this in the logs:
> >>>
> >>> 2011-03-03 21:20:08 EST () [2656]: [2-1] user=,db=LOG: ?received
> >>> SIGHUP, reloading configuration files
> >>> 2011-03-03 21:20:08 EST () [2656]: [3-1] user=,db=LOG: ?360 is
> >>> outside the valid range for parameter "max_standby_archive_delay" (-1
> >>> .. 2147483)
> >>>
> >>> The error is clear enough, but is there some reason that the parameter
> >>> is coded this way? istm people are much more likely to want to be able
> >>> to set the precision in hours than in microseconds.
> >>>
> >>> OTOH, maybe it's a bug? The default resolution is in milliseconds, and
> >>> you can't set it to anything less than that (afaict). I asked on irc
> >>> and the consensus seemed to be that the internal representation is
> >>> off, are we missing something?
> >>
> >> See this thread here:
> >> http://archives.postgresql.org/pgsql-hackers/2010-12/msg01517.php
> >>
> >> Summary: should be fixed, but it needs to be verified that it works
> >> across all possible codepaths. It's not an issue with just
> >> max_standby_delay.
> >>
> >
> > Thanks for the pointer! ?I guess the next question is if anyone is
> > working on that, and/or what would need to be done to know we've done
> > a satisfactory job of verifying nothing breaks across all codepaths
> > were someone to take on the job?
> 
> I have it sitting on my list somewhere, but I haven't actually started
> doing anything...
> 
> A good start is to just change the code (likely quite easy) and then
> test all the different ways that you can set and reset and read the
> values of a guc (set/show/pg_settings/anythingelseyoucanthinkof), that
> it's passed properly across exec_backend etc - and testing tihs on
> multiple platforms I guess, in particular both 32 and 64-bit...
> 
> At least that's my understanding of what needs to be done :-)
> 
> -- 
> ?Magnus Hagander
> ?Me: http://www.hagander.net/
> ?Work: http://www.redpill-linpro.com/
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] pg_basebackup and wal streaming

[Bruce Momjian]

Magnus Hagander wrote:
> On Fri, Mar 4, 2011 at 15:23, Yeb Havinga  wrote:
> > On 2011-02-18 11:02, Magnus Hagander wrote:
> >>
> >> Better late than never (or?), here's the final cleanup of
> >> pg_streamrecv for moving into the main distribution, per discussion
> >> back in late dec or early jan. It also includes the "stream logs in
> >> parallel to backup" part that was not completed on pg_basebackup.
> >
> > Is it a welcome idea to add a -X argument to specify a seperate xlog
> > directory like initdb -X ?
> 
> Probably not a bad idea - for a future enhancement ;)

A TODO?  Wording?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Couple document fixes

[Alvaro Herrera]

Removing CC to pg-docs so that Robert reads it.

Excerpts from Bruce Momjian's message of vie mar 11 08:13:20 -0300 2011:
> Kevin Grittner wrote:

> > relpersistence should be "char", not char.
> > Oddly enough, there is a difference.
> 
> I am unsure on that one.  We have many 'char' mentions in catalog.sgml,
> and I don't see any of them shown as '"char"'.  (Wow, we should have
> just called this type char1, but I think that name came from Berkeley!) 
> The big problem is that the pg_type name is really "char" _without_
> quotes.

One idea is to rename the type to something else.  We could keep "char"
as an alias for backwards compatibility, but use the new name in system
catalogs, and document it as the main name of the type.

Discussed the idea a bit on IM with Bruce, but couldn't find any really
good alternative.  Idea floated so far:

* byte (seems pretty decent to me)
* octet (though maybe people would expect it'd output as a number)
* char1 (looks ugly, but then we have int4 and so on)
* achar (this one is just plain weird)

None seems great.  Thoughts?

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Couple document fixes

[Tom Lane]

Alvaro Herrera  writes:
> One idea is to rename the type to something else.  We could keep "char"
> as an alias for backwards compatibility, but use the new name in system
> catalogs, and document it as the main name of the type.

We don't have type aliases...

regards, tom lane

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

[HACKERS] Flex output missing from 9.1a4 tarballs?

[Dave Page]

I'm seeing this failure on a build machine with an old (and therefore
unusable) version of flex:

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wformat-security
-fno-strict-aliasing -fwrapv -I../../../src/include -D_GNU_SOURCE
-I/usr/local/include/libxml2  -I/usr/local/include  -c -o basebackup.o
basebackup.c
/usr/bin/bison -d  -o repl_gram.c repl_gram.y
***
ERROR: `flex' is missing on your system. It is needed to create the
file `repl_scanner.c'. You can either get flex from a GNU mirror site
or download an official distribution of PostgreSQL, which contains
pre-packaged flex output.
***
make[3]: *** [repl_scanner.c] Error 1
make[3]: Leaving directory
`/mnt/buildfarm/pginstaller/server/source/postgres.linux/src/backend/replication'
make[2]: Leaving directory
`/mnt/buildfarm/pginstaller/server/source/postgres.linux/src/backend'
make[2]: *** [replication-recursive] Error 2
make[1]: Leaving directory
`/mnt/buildfarm/pginstaller/server/source/postgres.linux/src'
make[1]: *** [all-backend-recurse] Error 2
make: *** [all-src-recurse] Error 2

Looks like we're missing the pre-build output from the tarball.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Flex output missing from 9.1a4 tarballs?

[Heikki Linnakangas]

On 11.03.2011 17:41, Dave Page wrote:

Looks like we're missing the pre-build output from the tarball.


Yes. Tom spotted and fixed this yesterday:

commit 174f65ab00bb8de0f119a6a60d562b516ba71bba
Author: Tom Lane 
Date:   Thu Mar 10 00:03:26 2011 -0500

Fix some oversights in distprep and maintainer-clean targets.

At least two recent commits have apparently imagined that a comment in
a Makefile stating that something would be included in the distribution
tarball was sufficient to make it so.  They hadn't bothered to hook
into the upper maintainer-clean targets either.  Per bug #5923 from
Charles Johnson, in which it emerged that the 9.1alpha4 tarballs are
short a few files that should be there.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] KEEPONLYALNUM for pg_trgm is not documented

[Tom Lane]

Itagaki Takahiro  writes:
> contrib/pg_trgm in 9.1 becomes more attractive feature by index supports
> for LIKE operators, but only alphabet and numeric characters are indexed
> by default. But, we can modify KEEPONLYALNUM in the source code to
> keep all characters in n-gram words.

> However, the limitation and KEEPONLYALNUM are not documented in the page:
>   http://developer.postgresql.org/pgdocs/postgres/pgtrgm.html

> An additonal documentation patches acceptable? The issues would be a FAQ for
> non-English users. I heard that pg_trgm will be one of the *killer features*
> of 9.1 in Japan, where N-gram based text search is preferred.

I'm not sure it's really a great idea to encourage people to use custom
builds with modified versions of that symbol.  And those not using
custom builds will just be frustrated.  If we think this is an important
feature then we ought to work out a better way to expose the
functionality.

(Personally I wonder how useful pg_trgm is at all in multibyte
encodings.  Its idea of a trigram is 3 bytes, not 3 characters...)

regards, tom lane

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

Re: [HACKERS] Flex output missing from 9.1a4 tarballs?

[Dave Page]

On Fri, Mar 11, 2011 at 9:15 PM, Heikki Linnakangas
 wrote:
> On 11.03.2011 17:41, Dave Page wrote:
>>
>> Looks like we're missing the pre-build output from the tarball.
>
> Yes. Tom spotted and fixed this yesterday:

I really should pay more attention to the committers list. Thanks!



-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] FOR KEY LOCK foreign keys

[Noah Misch]

On Fri, Feb 11, 2011 at 02:13:22AM -0500, Noah Misch wrote:
> Automated tests would go a long way toward building confidence that this patch
> does the right thing.  Thanks to the SSI patch, we now have an in-tree test
> framework for testing interleaved transactions.  The only thing it needs to be
> suitable for this work is a way to handle blocked commands.  If you like, I 
> can
> try to whip something up for that.
[off-list ACK followed]

Here's a patch implementing that.  It applies to master, with or without your
KEY LOCK patch also applied, though the expected outputs reflect the
improvements from your patch.  I add three isolation test specs:

  fk-contention: blocking-only test case from your blog post
  fk-deadlock: the deadlocking test case I used during patch review
  fk-deadlock2: Joel Jacobson's deadlocking test case

When a spec permutation would have us run a command in a currently-blocked
session, we cannot implement that permutation.  Such permutations represent
impossible real-world scenarios, anyway.  For now, I just explicitly name the
valid permutations in each spec file.  If the test harness detects this problem,
we abort the current test spec.  It might be nicer to instead cancel all
outstanding queries, issue rollbacks in all sessions, and continue with other
permutations.  I hesitated to do that, because we currently leave all
transaction control in the hands of the test spec.

I only support one waiting command at a time.  As long as one commands continues
to wait, I run other commands to completion synchronously.  This decision has no
impact on the current test specs, which all have two sessions.  It avoided a
touchy policy decision concerning deadlock detection.  If two commands have
blocked, it may be that a third command needs to run before they will unblock,
or it may be that the two commands have formed a deadlock.  We won't know for
sure until deadlock_timeout elapses.  If it's possible to run the next step in
the permutation (i.e., it uses a different session from any blocked command), we
can either do so immediately or wait out the deadlock_timeout first.  The latter
slows the test suite, but it makes the output more natural -- more like what one
would typically after running the commands by hand.  If anyone can think of a
sound general policy, that would be helpful.  For now, I've punted.

With a default postgresql.conf, deadlock_timeout constitutes most of the run
time.  Reduce it to 20ms to accelerate things when running the tests repeatedly.

Since timing dictates which query participating in a deadlock will be chosen for
cancellation, the expected outputs bearing deadlock errors are unstable.  I'm
not sure how much it will come up in practice, so I have not included expected
output variations to address this.

I think this will work on Windows as well as pgbench does, but I haven't
verified that.

Sorry for the delay on this.

nm
*** /dev/null
--- b/src/test/isolation/expected/fk-contention.out
***
*** 0 
--- 1,16 
+ Parsed test spec with 2 sessions
+ 
+ starting permutation: ins com upd
+ step ins:  INSERT INTO bar VALUES (42); 
+ step com:  COMMIT; 
+ step upd:  UPDATE foo SET b = 'Hello World'; 
+ 
+ starting permutation: ins upd com
+ step ins:  INSERT INTO bar VALUES (42); 
+ step upd:  UPDATE foo SET b = 'Hello World'; 
+ step com:  COMMIT; 
+ 
+ starting permutation: upd ins com
+ step upd:  UPDATE foo SET b = 'Hello World'; 
+ step ins:  INSERT INTO bar VALUES (42); 
+ step com:  COMMIT; 
*** /dev/null
--- b/src/test/isolation/expected/fk-deadlock.out
***
*** 0 
--- 1,63 
+ Parsed test spec with 2 sessions
+ 
+ starting permutation: s1i s1u s1c s2i s2u s2c
+ step s1i:  INSERT INTO child VALUES (1, 1); 
+ step s1u:  UPDATE parent SET aux = 'bar'; 
+ step s1c:  COMMIT; 
+ step s2i:  INSERT INTO child VALUES (2, 1); 
+ step s2u:  UPDATE parent SET aux = 'baz'; 
+ step s2c:  COMMIT; 
+ 
+ starting permutation: s1i s1u s2i s1c s2u s2c
+ step s1i:  INSERT INTO child VALUES (1, 1); 
+ step s1u:  UPDATE parent SET aux = 'bar'; 
+ step s2i:  INSERT INTO child VALUES (2, 1);  
+ step s1c:  COMMIT; 
+ step s2i: <... completed>
+ step s2u:  UPDATE parent SET aux = 'baz'; 
+ step s2c:  COMMIT; 
+ 
+ starting permutation: s1i s2i s1u s2u s1c s2c
+ step s1i:  INSERT INTO child VALUES (1, 1); 
+ step s2i:  INSERT INTO child VALUES (2, 1); 
+ step s1u:  UPDATE parent SET aux = 'bar'; 
+ step s2u:  UPDATE parent SET aux = 'baz';  
+ step s1c:  COMMIT; 
+ step s2u: <... completed>
+ step s2c:  COMMIT; 
+ 
+ starting permutation: s1i s2i s2u s1u s2c s1c
+ step s1i:  INSERT INTO child VALUES (1, 1); 
+ step s2i:  INSERT INTO child VALUES (2, 1); 
+ step s2u:  UPDATE parent SET aux = 'baz'; 
+ step s1u:  UPDATE parent SET aux = 'bar';  
+ step s2c:  COMMIT; 
+ step s1u: <... completed>
+ step s1c:  COMMIT; 
+ 
+ starting permutation: s2i s1i s1u s2u s1c s2c
+ step s2i:  INSERT INTO child VALUES (2, 1); 
+ step s1i:  INSERT INTO child VALUES

Re: [HACKERS] Couple document fixes

[Alvaro Herrera]

Excerpts from Tom Lane's message of vie mar 11 12:40:50 -0300 2011:
> Alvaro Herrera  writes:
> > One idea is to rename the type to something else.  We could keep "char"
> > as an alias for backwards compatibility, but use the new name in system
> > catalogs, and document it as the main name of the type.
> 
> We don't have type aliases...

I meant the conversion we do from a certain name (say because it's the
SQL-mandated name for the type) to the internal name, such as mapping
integer to int4.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Flex output missing from 9.1a4 tarballs?

[Devrim GÜNDÜZ]

On Fri, 2011-03-11 at 17:45 +0200, Heikki Linnakangas wrote:
> 
> On 11.03.2011 17:41, Dave Page wrote:
> > Looks like we're missing the pre-build output from the tarball.
> 
> Yes. Tom spotted and fixed this yesterday: 

I believe we need an alpha5 for post-alpha-4 fixes, including syncrep
ones.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] B-tree parent pointer and checkpoints

[Tom Lane]

Heikki Linnakangas  writes:
> On 10.03.2011 22:50, Bruce Momjian wrote:
>> Bruce Momjian wrote:
> 
> Has this been addressed?
>> 
>> I see we have with this commit:
>> 
>> 9de3aa65f01fb51cbc725e8508ea233e4e92c46c

> We fixed GiST. B-tree still has the issue that if you have a checkpoint 
> in the middle of an insert, and crash, you might be left with a leaf 
> node without a downlink in the parent.

But that will be fixed during WAL replay.

> That's relatively harmless, index searches and insertions work without 
> the downlink. However, there's code in page deletion that ERRORs if the 
> parent can't be found. That should be fixed.

I don't like the idea of removing that consistency check, and I don't
think it's necessary to do so.

regards, tom lane

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

Re: [HACKERS] Couple document fixes

[Tom Lane]

Alvaro Herrera  writes:
> Excerpts from Tom Lane's message of vie mar 11 12:40:50 -0300 2011:
>> Alvaro Herrera  writes:
>>> One idea is to rename the type to something else.  We could keep "char"
>>> as an alias for backwards compatibility, but use the new name in system
>>> catalogs, and document it as the main name of the type.

>> We don't have type aliases...

> I meant the conversion we do from a certain name (say because it's the
> SQL-mandated name for the type) to the internal name, such as mapping
> integer to int4.

That works for keywords.  "char" is, by definition, not a keyword.

regards, tom lane

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

Re: [HACKERS] Flex output missing from 9.1a4 tarballs?

[Dave Page]

2011/3/11 Devrim GÜNDÜZ :
> On Fri, 2011-03-11 at 17:45 +0200, Heikki Linnakangas wrote:
>>
>> On 11.03.2011 17:41, Dave Page wrote:
>> > Looks like we're missing the pre-build output from the tarball.
>>
>> Yes. Tom spotted and fixed this yesterday:
>
> I believe we need an alpha5 for post-alpha-4 fixes, including syncrep
> ones.

Might be useful. My build scripts fell over because of the parallel
build issues too.


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Couple document fixes

[Alvaro Herrera]

Excerpts from Tom Lane's message of vie mar 11 13:01:06 -0300 2011:
> Alvaro Herrera  writes:
> > Excerpts from Tom Lane's message of vie mar 11 12:40:50 -0300 2011:
> >> Alvaro Herrera  writes:
> >>> One idea is to rename the type to something else.  We could keep "char"
> >>> as an alias for backwards compatibility, but use the new name in system
> >>> catalogs, and document it as the main name of the type.
> 
> >> We don't have type aliases...
> 
> > I meant the conversion we do from a certain name (say because it's the
> > SQL-mandated name for the type) to the internal name, such as mapping
> > integer to int4.
> 
> That works for keywords.  "char" is, by definition, not a keyword.

Oh.  Right, of course.

Seems the only option is to continue living with it.

(Well actually the other option would be to rename it and break
backwards compatibility.  I'm not sure anyone is going to be happy with
that though.)

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

[HACKERS] Re: [COMMITTERS] pgsql: Document that the parenthesized VACUUM syntax is deprecated, not

[Bruce Momjian]

Robert Haas wrote:
> On Fri, Mar 11, 2011 at 5:34 AM, Bruce Momjian  wrote:
> > Document that the parenthesized VACUUM syntax is deprecated, not the
> > FREEZE functionality.
> 
> The text you added here is flat-out wrong (you used "unparenthesized"
> in both halves of the sentence), and it's also not as clear as the
> text it replaced.  I'd suggest reverting this, and instead adding a
> sentence that says simply:
> 
> The unparenthesized syntax is deprecated.

Fixed.  Other changes?  I didn't like the original sentence because it
started by mentioning 9.0 rather than the syntax itself.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
new file mode 100644
index 5b5b161..87283bc
*** a/doc/src/sgml/ref/vacuum.sgml
--- b/doc/src/sgml/ref/vacuum.sgml
*** VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] A
*** 70,76 
 When the option list is surrounded by parentheses, the options can be
 written in any order.  Without parentheses, options must be specified
 in exactly the order shown above.
!The unparenthesized syntax was added in
 PostgreSQL 9.0;  the unparenthesized
 syntax is deprecated.

--- 70,76 
 When the option list is surrounded by parentheses, the options can be
 written in any order.  Without parentheses, options must be specified
 in exactly the order shown above.
!The parenthesized syntax was added in
 PostgreSQL 9.0;  the unparenthesized
 syntax is deprecated.


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

Re: [HACKERS] multiple -f support

[David Christensen]

On Mar 11, 2011, at 6:17 AM, Bruce Momjian wrote:

> Robert Haas wrote:
>> On Sun, Feb 6, 2011 at 11:16 AM, Bruce Momjian  wrote:
>>> I assume having psql support multiple -f files is not a high priority or
>>> something we don't want.
>> 
>> IIRC, nobody objected to the basic concept, and it seems useful.  I
>> thought we were pretty close to committing something along those lines
>> at one point, actually.  I don't remember exactly where the wheels
>> came off.
>> 
>> Maybe a TODO?
> 
> Added to the psql section:
> 
>   |Allow processing of multiple -f (file) options


The original patch was a fairly trivial WIP one, which I started working on to 
add support for multiple -c flags interspersed as well.  I haven't looked at it 
in quite some time, though; there had been some concerns about how it worked in 
single-transaction mode and some other issues I don't recall off the top of my 
head.

On this topic, I was thinking that it may be useful to provide an alternate 
multi-file syntax, a la git, with any argument following '--' in the argument 
list being interpreted as a file to process; i.e.,:

$ psql -U user [option] database -- file1.sql file2.sql file3.sql

This would allow things like shell expansion to work as expected:

$ ls
01-schema.sql02-data1.sql03-fixups.sql

$ psql database -- *.sql

etc.

Regards,

David
--
David Christensen
End Point Corporation
da...@endpoint.com





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

Re: [HACKERS] Flex output missing from 9.1a4 tarballs?

[Robert Haas]

2011/3/11 Dave Page :
> 2011/3/11 Devrim GÜNDÜZ :
>> On Fri, 2011-03-11 at 17:45 +0200, Heikki Linnakangas wrote:
>>>
>>> On 11.03.2011 17:41, Dave Page wrote:
>>> > Looks like we're missing the pre-build output from the tarball.
>>>
>>> Yes. Tom spotted and fixed this yesterday:
>>
>> I believe we need an alpha5 for post-alpha-4 fixes, including syncrep
>> ones.
>
> Might be useful. My build scripts fell over because of the parallel
> build issues too.

Have those been fixed?

My vote would be forget about building installers for alpha4 and
instead wrap an alpha5 next week.  By that time Tom will have
hopefully also finished hacking on the collation stuff.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] B-tree parent pointer and checkpoints

[Heikki Linnakangas]

On 11.03.2011 17:59, Tom Lane wrote:

Heikki Linnakangas  writes:

On 10.03.2011 22:50, Bruce Momjian wrote:

Bruce Momjian wrote:


Has this been addressed?


I see we have with this commit:

9de3aa65f01fb51cbc725e8508ea233e4e92c46c



We fixed GiST. B-tree still has the issue that if you have a checkpoint
in the middle of an insert, and crash, you might be left with a leaf
node without a downlink in the parent.


But that will be fixed during WAL replay.


Not under the circumstances that started the original thread:

1. Backend splits a page
2. Checkpoint starts
3. Checkpoint runs to completion
4. Crash
(5. Backend never got to insert the parent pointer)

WAL replay starts at the checkpoint redo pointer, which is after the 
page split record, so WAL replay won't insert the parent pointer. That's 
an incredibly tight window to hit in practice, but it's possible in theory.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] multiple -f support

[Robert Haas]

On Fri, Mar 11, 2011 at 11:30 AM, David Christensen  wrote:
>
> On Mar 11, 2011, at 6:17 AM, Bruce Momjian wrote:
>
>> Robert Haas wrote:
>>> On Sun, Feb 6, 2011 at 11:16 AM, Bruce Momjian  wrote:
 I assume having psql support multiple -f files is not a high priority or
 something we don't want.
>>>
>>> IIRC, nobody objected to the basic concept, and it seems useful.  I
>>> thought we were pretty close to committing something along those lines
>>> at one point, actually.  I don't remember exactly where the wheels
>>> came off.
>>>
>>> Maybe a TODO?
>>
>> Added to the psql section:
>>
>>       |Allow processing of multiple -f (file) options
>
>
> The original patch was a fairly trivial WIP one, which I started working on 
> to add support for multiple -c flags interspersed as well.  I haven't looked 
> at it in quite some time, though; there had been some concerns about how it 
> worked in single-transaction mode and some other issues I don't recall off 
> the top of my head.
>
> On this topic, I was thinking that it may be useful to provide an alternate 
> multi-file syntax, a la git, with any argument following '--' in the argument 
> list being interpreted as a file to process; i.e.,:
>
> $ psql -U user [option] database -- file1.sql file2.sql file3.sql
>
> This would allow things like shell expansion to work as expected:
>
> $ ls
> 01-schema.sql    02-data1.sql    03-fixups.sql
>
> $ psql database -- *.sql
>
> etc.

+1.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

[HACKERS] Non-simultaneous file system snapshots as backups

[Bruce Momjian]

I know we allow people to use file system snapshots as backups, but what
happens if they are using tablespaces and they can't do the snapshots
simultaneously?  If there is only one check point happening between the
first and last snapshot, would the WAL logs clean up that inconsistency
like they do for crashes?  I assume the pg_xlog directory would have to
be the last file system snapshotted.  If so, is this something we should
document?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Flex output missing from 9.1a4 tarballs?

[Dave Page]

2011/3/11 Robert Haas :
> 2011/3/11 Dave Page :
>> 2011/3/11 Devrim GÜNDÜZ :
>>> On Fri, 2011-03-11 at 17:45 +0200, Heikki Linnakangas wrote:

 On 11.03.2011 17:41, Dave Page wrote:
 > Looks like we're missing the pre-build output from the tarball.

 Yes. Tom spotted and fixed this yesterday:
>>>
>>> I believe we need an alpha5 for post-alpha-4 fixes, including syncrep
>>> ones.
>>
>> Might be useful. My build scripts fell over because of the parallel
>> build issues too.
>
> Have those been fixed?

Dunno - I removed the -j option from my build scripts as a workaround.
I have seen discussion of that issue though, so I assumed it was in
hand and didn't bother reporting it.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

[HACKERS] Add unistd.h to c.h

[Bruce Momjian]

Twenty percent of our C files include unistd.h.  Should we include
unistd.h in c.h and remove mentions of unistd.h in files that include
c.h?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Add unistd.h to c.h

[Heikki Linnakangas]

On 11.03.2011 18:50, Bruce Momjian wrote:

Twenty percent of our C files include unistd.h.  Should we include
unistd.h in c.h and remove mentions of unistd.h in files that include
c.h?


Why?

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Add unistd.h to c.h

[Bruce Momjian]

Heikki Linnakangas wrote:
> On 11.03.2011 18:50, Bruce Momjian wrote:
> > Twenty percent of our C files include unistd.h.  Should we include
> > unistd.h in c.h and remove mentions of unistd.h in files that include
> > c.h?
> 
> Why?

Well, that is one less C include file in 151 C files, and just one
additional line in c.h.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Add unistd.h to c.h

[Robert Haas]

On Fri, Mar 11, 2011 at 11:50 AM, Bruce Momjian  wrote:
> Twenty percent of our C files include unistd.h.  Should we include
> unistd.h in c.h and remove mentions of unistd.h in files that include
> c.h?

Why?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Add unistd.h to c.h

[Heikki Linnakangas]

On 11.03.2011 18:53, Bruce Momjian wrote:

Heikki Linnakangas wrote:

On 11.03.2011 18:50, Bruce Momjian wrote:

Twenty percent of our C files include unistd.h.  Should we include
unistd.h in c.h and remove mentions of unistd.h in files that include
c.h?


Why?


Well, that is one less C include file in 151 C files, and just one
additional line in c.h.


It would make 80% of C files include unistd.h unnecessarily, slowing 
down compilation by some small margin.


-1

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Add unistd.h to c.h

[Bruce Momjian]

Heikki Linnakangas wrote:
> On 11.03.2011 18:53, Bruce Momjian wrote:
> > Heikki Linnakangas wrote:
> >> On 11.03.2011 18:50, Bruce Momjian wrote:
> >>> Twenty percent of our C files include unistd.h.  Should we include
> >>> unistd.h in c.h and remove mentions of unistd.h in files that include
> >>> c.h?
> >>
> >> Why?
> >
> > Well, that is one less C include file in 151 C files, and just one
> > additional line in c.h.
> 
> It would make 80% of C files include unistd.h unnecessarily, slowing 
> down compilation by some small margin.
> 
> -1

OK, I am just asking.  FYI, we already include a boatload of includes in
c.h:

#include 
#include 
#include 
#include 
#include 
#ifdef HAVE_STRINGS_H
#include 
#endif
#ifdef HAVE_STDINT_H
#include 
#endif
#include 

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Add unistd.h to c.h

[Robert Haas]

On Fri, Mar 11, 2011 at 11:53 AM, Bruce Momjian  wrote:
> Heikki Linnakangas wrote:
>> On 11.03.2011 18:50, Bruce Momjian wrote:
>> > Twenty percent of our C files include unistd.h.  Should we include
>> > unistd.h in c.h and remove mentions of unistd.h in files that include
>> > c.h?
>>
>> Why?
>
> Well, that is one less C include file in 151 C files, and just one
> additional line in c.h.

We could take it a little further and just make c.h include everything
that any file needs anywhere in the system.  That would save even more
lines of code, but it has nothing else to recommend it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Basic Recovery Control functions for use in Hot Standby. Pause,

[Robert Haas]

On Fri, Mar 11, 2011 at 9:31 AM, Tom Lane  wrote:
> Fujii Masao  writes:
>> Yeah, since I like the former, I changed the wordings in the doc and
>> recovery.conf.sample. What about the attached patch?
>
> Please stop plastering the code with elog(FATAL) calls.  Those are
> hardly ever appropriate.  In contexts where it might be reasonable
> to do that, the error handler will treat ERROR like FATAL anyway.

Another problem here is that we are defaulting to hot_standby=off and
pause_at_recovery_target=on.  So AIUI, with this patch, if someone
sets a recovery target without making any other changes to the
configuration, their database won't start up.  That seems poor.

Even without the FATAL error, this whole pause_at_recovery_target
thing is a little weird.  If someone sets a recovery target without
making any other configuration changes, and Hot Standby is not
enabled, then we will enter normal running, but if Hot Standby *is*
enabled, then we'll replay to that point and pause recovery.  That
seems a bit confusing.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Add unistd.h to c.h

[Heikki Linnakangas]

On 11.03.2011 18:55, Bruce Momjian wrote:

OK, I am just asking.  FYI, we already include a boatload of includes in
c.h:

#include
#include
#include
#include
#include
#ifdef HAVE_STRINGS_H
#include
#endif
#ifdef HAVE_STDINT_H
#include
#endif
#include


Presumably all of these are used by something in c.h itself. At least 
strings.h is needed by memset, and stddef.h and/or stdlib.h is needed 
for size_t. I'm too lazy to check the rest, but if there are any header 
files there that are not in fact used by anything in c.h itself, they 
should be removed from c.h, rather than going further into that direction.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Add unistd.h to c.h

[Alvaro Herrera]

Excerpts from Heikki Linnakangas's message of vie mar 11 13:59:59 -0300 2011:

> Presumably all of these are used by something in c.h itself. At least 
> strings.h is needed by memset, and stddef.h and/or stdlib.h is needed 
> for size_t. I'm too lazy to check the rest, but if there are any header 
> files there that are not in fact used by anything in c.h itself, they 
> should be removed from c.h, rather than going further into that direction.

Yeah.  FWIW I sometimes make efforts to reduce the reach of headers, for
example by removing them from other headers that don't need them.  It is
quite the opposite to what you propose here.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Non-simultaneous file system snapshots as backups

[Magnus Hagander]

On Fri, Mar 11, 2011 at 17:46, Bruce Momjian  wrote:
> I know we allow people to use file system snapshots as backups, but what
> happens if they are using tablespaces and they can't do the snapshots
> simultaneously?  If there is only one check point happening between the
> first and last snapshot, would the WAL logs clean up that inconsistency
> like they do for crashes?  I assume the pg_xlog directory would have to
> be the last file system snapshotted.  If so, is this something we should
> document?

If you can't take an atomic snapshot, you have to use
pg_start_backup/pg_stop_backup. But as long as you do that, it works
fine with any kind of snapshots. I don't think it's doable any other
way.

That said, there are systems that let you snapshot atomically across
multiple tablespaces. But they tend to not be cheap.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

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

Re: [HACKERS] Non-simultaneous file system snapshots as backups

[Bruce Momjian]

Magnus Hagander wrote:
> On Fri, Mar 11, 2011 at 17:46, Bruce Momjian  wrote:
> > I know we allow people to use file system snapshots as backups, but what
> > happens if they are using tablespaces and they can't do the snapshots
> > simultaneously? ?If there is only one check point happening between the
> > first and last snapshot, would the WAL logs clean up that inconsistency
> > like they do for crashes? ?I assume the pg_xlog directory would have to
> > be the last file system snapshotted. ?If so, is this something we should
> > document?
> 
> If you can't take an atomic snapshot, you have to use
> pg_start_backup/pg_stop_backup. But as long as you do that, it works
> fine with any kind of snapshots. I don't think it's doable any other
> way.
> 
> That said, there are systems that let you snapshot atomically across
> multiple tablespaces. But they tend to not be cheap.

Agreed, thanks.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Range Types: << >> -|- ops vs empty range

[Jeff Davis]

On Fri, 2011-03-11 at 08:37 -0500, Bruce Momjian wrote:
> Where are we on this?

The options are:

1. Rip out empty ranges. Several people have been skeptical of their
usefulness, but I don't recall anyone directly saying that they should
be removed. Robert Haas made the point that range types aren't closed
under UNION:

http://archives.postgresql.org/pgsql-hackers/2011-02/msg01045.php

So the additional nice mathematical properties provided by empty ranges
are not as important (because it wouldn't be perfect anyway).


2. Change the semantics. Erik Rijkers suggested that we define all
operators for empty ranges, perhaps using NULL semantics:

http://archives.postgresql.org/pgsql-hackers/2011-02/msg00942.php

And Kevin Grittner suggested that there could be discrete ranges of zero
length yet a defined starting point:

http://archives.postgresql.org/pgsql-hackers/2011-02/msg01042.php


3. Leave empty ranges with the existing "empty set" semantics. Nathan
Boley made a good point here:

http://archives.postgresql.org/pgsql-hackers/2011-02/msg01108.php


Right now it's #3, and I lean pretty strongly toward keeping it. Without
#3, people will get confused when fairly simple operations fail in a
data-dependent way (at runtime). With #3, people will run into problems
only in situations where it is fairly dubious to have an empty range
anyway (and therefore likely a real error), such as finding ranges "left
of" an empty range.

Otherwise, I'd prefer #1 to #2. I think #2 is a bad path to take, and
we'll end up with a lot of unintuitive and error-prone operators.

Regards,
Jeff Davis


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

Re: [HACKERS] B-tree parent pointer and checkpoints

[Tom Lane]

Heikki Linnakangas  writes:
> On 11.03.2011 17:59, Tom Lane wrote:
>> But that will be fixed during WAL replay.

> Not under the circumstances that started the original thread:

> 1. Backend splits a page
> 2. Checkpoint starts
> 3. Checkpoint runs to completion
> 4. Crash
> (5. Backend never got to insert the parent pointer)

> WAL replay starts at the checkpoint redo pointer, which is after the 
> page split record, so WAL replay won't insert the parent pointer. That's 
> an incredibly tight window to hit in practice, but it's possible in theory.

Hmm.  It's not so improbable that checkpoint would start inside that
window, but that the parent insertion is still pending by the time the
checkpoint finishes is pretty improbable.

How about just reducing the deletion-time ERROR for missing downlink to a LOG?

regards, tom lane

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

Re: [HACKERS] Range Types: << >> -|- ops vs empty range

[Christopher Browne]

On Fri, Mar 11, 2011 at 12:37 PM, Jeff Davis  wrote:
> Right now it's #3, and I lean pretty strongly toward keeping it. Without
> #3, people will get confused when fairly simple operations fail in a
> data-dependent way (at runtime). With #3, people will run into problems
> only in situations where it is fairly dubious to have an empty range
> anyway (and therefore likely a real error), such as finding ranges "left
> of" an empty range.

That seems pretty apropos to me.

> Otherwise, I'd prefer #1 to #2. I think #2 is a bad path to take, and
> we'll end up with a lot of unintuitive and error-prone operators.

I think back to your essay on the nonintuitiveness of NULL
(),
and suggest the thought that picking #2 would add to the already
existent confusion.
-- 
http://linuxfinances.info/info/linuxdistributions.html

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

Re: [HACKERS] B-tree parent pointer and checkpoints

[Heikki Linnakangas]

On 11.03.2011 19:41, Tom Lane wrote:

Heikki Linnakangas  writes:

On 11.03.2011 17:59, Tom Lane wrote:

But that will be fixed during WAL replay.



Not under the circumstances that started the original thread:



1. Backend splits a page
2. Checkpoint starts
3. Checkpoint runs to completion
4. Crash
(5. Backend never got to insert the parent pointer)



WAL replay starts at the checkpoint redo pointer, which is after the
page split record, so WAL replay won't insert the parent pointer. That's
an incredibly tight window to hit in practice, but it's possible in theory.


Hmm.  It's not so improbable that checkpoint would start inside that
window, but that the parent insertion is still pending by the time the
checkpoint finishes is pretty improbable.

How about just reducing the deletion-time ERROR for missing downlink to a LOG?


Well, the code that follows expects to have a valid parent page locked, 
so you can't literally do just that. But yeah, LOG and aborting the page 
deletion seems fine to me.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

[HACKERS] Macros for time magic values

[Bruce Momjian]

It has bothered me that many of our time routines use special magic
constants for time values, e.g. 24, 12, 60, etc.

The attached patch changes these magic constants to macros to clarify
the code.  I would like to apply this for 9.1 as a cleanup.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 80dd10b..f96fa6c 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -2612,7 +2612,7 @@ timetz_zone(PG_FUNCTION_ARGS)
 	type = DecodeSpecial(0, lowzone, &val);
 
 	if (type == TZ || type == DTZ)
-		tz = val * 60;
+		tz = val * MINS_PER_HOUR;
 	else
 	{
 		tzp = pg_tzset(tzname);
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 85f0206..f0fe2e3 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -342,7 +342,7 @@ j2date(int jd, int *year, int *month, int *day)
 	*year = y - 4800;
 	quad = julian * 2141 / 65536;
 	*day = julian - 7834 * quad / 256;
-	*month = (quad + 10) % 12 + 1;
+	*month = (quad + 10) % MONTHS_PER_YEAR + 1;
 
 	return;
 }	/* j2date() */
@@ -952,8 +952,8 @@ DecodeDateTime(char **field, int *ftype, int nf,
  * DecodeTime()
  */
 /* test for > 24:00:00 */
-if (tm->tm_hour > 24 ||
-	(tm->tm_hour == 24 &&
+if (tm->tm_hour > HOURS_PER_DAY ||
+	(tm->tm_hour == HOURS_PER_DAY &&
 	 (tm->tm_min > 0 || tm->tm_sec > 0 || *fsec > 0)))
 	return DTERR_FIELD_OVERFLOW;
 break;
@@ -1371,12 +1371,12 @@ DecodeDateTime(char **field, int *ftype, int nf,
 		return dterr;
 
 	/* handle AM/PM */
-	if (mer != HR24 && tm->tm_hour > 12)
+	if (mer != HR24 && tm->tm_hour > HOURS_PER_DAY / 2)
 		return DTERR_FIELD_OVERFLOW;
-	if (mer == AM && tm->tm_hour == 12)
+	if (mer == AM && tm->tm_hour == HOURS_PER_DAY / 2)
 		tm->tm_hour = 0;
-	else if (mer == PM && tm->tm_hour != 12)
-		tm->tm_hour += 12;
+	else if (mer == PM && tm->tm_hour != HOURS_PER_DAY / 2)
+		tm->tm_hour += HOURS_PER_DAY / 2;
 
 	/* do additional checking for full date specs... */
 	if (*dtype == DTK_DATE)
@@ -2058,17 +2058,18 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
 		return dterr;
 
 	/* handle AM/PM */
-	if (mer != HR24 && tm->tm_hour > 12)
+	if (mer != HR24 && tm->tm_hour > HOURS_PER_DAY / 2)
 		return DTERR_FIELD_OVERFLOW;
-	if (mer == AM && tm->tm_hour == 12)
+	if (mer == AM && tm->tm_hour == HOURS_PER_DAY / 2)
 		tm->tm_hour = 0;
-	else if (mer == PM && tm->tm_hour != 12)
-		tm->tm_hour += 12;
+	else if (mer == PM && tm->tm_hour != HOURS_PER_DAY / 2)
+		tm->tm_hour += HOURS_PER_DAY / 2;
 
-	if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > 59 ||
-		tm->tm_sec < 0 || tm->tm_sec > 60 || tm->tm_hour > 24 ||
+	if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > MINS_PER_HOUR - 1 ||
+		tm->tm_sec < 0 || tm->tm_sec > SECS_PER_MINUTE ||
+		tm->tm_hour > HOURS_PER_DAY ||
 	/* test for > 24:00:00 */
-		(tm->tm_hour == 24 &&
+		(tm->tm_hour == HOURS_PER_DAY &&
 		 (tm->tm_min > 0 || tm->tm_sec > 0 || *fsec > 0)) ||
 #ifdef HAVE_INT64_TIMESTAMP
 		*fsec < INT64CONST(0) || *fsec > USECS_PER_SEC
@@ -2396,13 +2397,15 @@ DecodeTime(char *str, int fmask, int range,
 
 	/* do a sanity check */
 #ifdef HAVE_INT64_TIMESTAMP
-	if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > 59 ||
-		tm->tm_sec < 0 || tm->tm_sec > 60 || *fsec < INT64CONST(0) ||
+	if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > MINS_PER_HOUR -1 ||
+		tm->tm_sec < 0 || tm->tm_sec > SECS_PER_MINUTE ||
+		*fsec < INT64CONST(0) ||
 		*fsec > USECS_PER_SEC)
 		return DTERR_FIELD_OVERFLOW;
 #else
-	if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > 59 ||
-		tm->tm_sec < 0 || tm->tm_sec > 60 || *fsec < 0 || *fsec > 1)
+	if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > MINS_PER_HOUR - 1 ||
+		tm->tm_sec < 0 || tm->tm_sec > SECS_PER_MINUTE ||
+		*fsec < 0 || *fsec > 1)
 		return DTERR_FIELD_OVERFLOW;
 #endif
 
@@ -2748,9 +2751,9 @@ DecodeTimezone(char *str, int *tzp)
 
 	if (hr < 0 || hr > 14)
 		return DTERR_TZDISP_OVERFLOW;
-	if (min < 0 || min >= 60)
+	if (min < 0 || min >= MINS_PER_HOUR)
 		return DTERR_TZDISP_OVERFLOW;
-	if (sec < 0 || sec >= 60)
+	if (sec < 0 || sec >= SECS_PER_MINUTE)
 		return DTERR_TZDISP_OVERFLOW;
 
 	tz = (hr * MINS_PER_HOUR + min) * SECS_PER_MINUTE + sec;
@@ -3324,7 +3327,7 @@ DecodeISO8601Interval(char *str,
 			{
 case 'Y':
 	tm->tm_year += val;
-	tm->tm_mon += (fval * 12);
+	tm->tm_mon += (fval * MONTHS_PER_YEAR);
 	break;
 case 'M':
 	tm->tm_mon += val;
@@ -3359,7 +3362,7 @@ DecodeISO8601Interval(char *str,
 		return DTERR_BAD_FORMAT;
 
 	tm->tm_year += val;
-	tm->tm_mon += (fval * 12);
+	tm->tm_mon += (fval * MONTHS_PER_YEAR);
 	if (unit == '\0')
 		return 0;
 	if (unit == 'T')
@@ -4155,7 +4158,7 @@ InstallTimeZoneAbbrevs(tzEntry *abbrevs, int n)
 	{
 		strncpy(newtb

Re: [HACKERS] Macros for time magic values

[Christopher Browne]

On Fri, Mar 11, 2011 at 12:50 PM, Bruce Momjian  wrote:
> It has bothered me that many of our time routines use special magic
> constants for time values, e.g. 24, 12, 60, etc.
>
> The attached patch changes these magic constants to macros to clarify
> the code.  I would like to apply this for 9.1 as a cleanup.

The context diffs show off some references to 1901 and 2038...

Here's a *possible* extension to this...
-- 
http://linuxfinances.info/info/linuxdistributions.html
diff --git a/src/backend/utils/adt/nabstime.c b/src/backend/utils/adt/nabstime.c
index 0e25c5f..3cf4166 100644
--- a/src/backend/utils/adt/nabstime.c
+++ b/src/backend/utils/adt/nabstime.c
@@ -178,7 +178,7 @@ tm2abstime(struct pg_tm * tm, int tz)
 	AbsoluteTime sec;
 
 	/* validate, before going out of range on some members */
-	if (tm->tm_year < 1901 || tm->tm_year > 2038 ||
+	if (tm->tm_year < UTIME_MINYEAR || tm->tm_year > UTIME_MAXYEAR ||
 		tm->tm_mon < 1 || tm->tm_mon > 12 ||
 		tm->tm_mday < 1 || tm->tm_mday > 31 ||
 		tm->tm_hour < 0 ||

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

Re: [HACKERS] Macros for time magic values

[Bruce Momjian]

Christopher Browne wrote:
> On Fri, Mar 11, 2011 at 12:50 PM, Bruce Momjian  wrote:
> > It has bothered me that many of our time routines use special magic
> > constants for time values, e.g. 24, 12, 60, etc.
> >
> > The attached patch changes these magic constants to macros to clarify
> > the code. ?I would like to apply this for 9.1 as a cleanup.
> 
> The context diffs show off some references to 1901 and 2038...
> 
> Here's a *possible* extension to this...


Interesting idea, but UTIME_MINYEAR/UTIME_MAXYEAR is only defined in
src/interfaces/ecpg/pgtypeslib/dt.h, and it seems odd to duplicate or
move them for just one use site.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] How should the waiting backends behave in sync rep?

[Robert Haas]

On Wed, Mar 9, 2011 at 9:58 PM, Fujii Masao  wrote:
> On Thu, Mar 10, 2011 at 2:06 AM, Robert Haas  wrote:
>> On Tue, Mar 8, 2011 at 10:06 AM, Fujii Masao  wrote:
>>> How should the backends waiting for replication behave when
>>> synchrnous_standby_names
>>> is set to '' and the configuration file is reloaded? Now they keep
>>> waiting for the ACK from the
>>> standby. But I think that it's more natural for them to get out of the
>>> wait state and complete
>>> the transaction in that case. If we'll change them in that way, we
>>> would no longer need
>>> something like "pg_ctl standalone" which I mentioned in another thread. 
>>> Thought?
>>
>> I think so.  Looking at assign_synchronous_standby_names, the
>> following code just looks wrong:
>>
>>        if (doit && list_length(elemlist) > 0)
>>                sync_standbys_defined = true;
>>
>> Once sync_standbys_defined becomes true, there's no way for it to ever
>> become false again.  That can't be right.  That means that if you
>> disable sync rep by zeroing out synchronous_standby_names, backends
>> that already existed at the time you made the change will continue to
>> act as though sync rep is enabled until they exit.
>
> Yes, that's a bug. Yeah, sync rep currently seems to have many TODO items.
> I added some of them in wiki.
> http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items

There's a comment that looks related to this issue in syncrep.c.  It reads:

/*
 * We don't receive SIGHUPs at this point, so resetting
 * synchronous_standby_names has no effect on waiters.
 */

It's unclear to me what this actually means.  Is there some reason we
CAN'T receive SIGHUPs at that point, or have we just chosen not to
(for unexplained reasons)?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Macros for time magic values

[Bruce Momjian]

Bruce Momjian wrote:
> Christopher Browne wrote:
> > On Fri, Mar 11, 2011 at 12:50 PM, Bruce Momjian  wrote:
> > > It has bothered me that many of our time routines use special magic
> > > constants for time values, e.g. 24, 12, 60, etc.
> > >
> > > The attached patch changes these magic constants to macros to clarify
> > > the code. ?I would like to apply this for 9.1 as a cleanup.
> > 
> > The context diffs show off some references to 1901 and 2038...
> > 
> > Here's a *possible* extension to this...
> 
> 
> Interesting idea, but UTIME_MINYEAR/UTIME_MAXYEAR is only defined in
> src/interfaces/ecpg/pgtypeslib/dt.h, and it seems odd to duplicate or
> move them for just one use site.

We could move UTIME_MINYEAR/UTIME_MAXYEAR, but I don't see a common file
they both currently include.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Fwd: index corruption in PG 8.3.13

[Greg Stark]

On Fri, Mar 11, 2011 at 2:28 PM, Nikhil Sontakke
 wrote:
>> I'm not sure, but I doubt it.  If the VACUUM FULL committed, then the
>> WAL records should be on disk, but if the immediate shutdown happened
>> while it was still running, then the WAL records might still be in
>> wal_buffers, in which case I don't think they'll get written out and
>> thus zero pages in the index are to be expected.
>>...
>
> Oh yeah, so if VF committed, the xlog should have been ok too, but
> can't say the same about the shared buffers.

But there was a later block that *was* written out. What was the LSN
on that block? everything in the WAL log should have been fsynced up
to that point when that buffer was flushed.

Was there a machine restart in the picture as well?



-- 
greg

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

[HACKERS] Re: [COMMITTERS] pgsql: Add missing keywords to gram.y's unreserved_keywords list.

[Robert Haas]

On Tue, Mar 8, 2011 at 4:44 PM, Tom Lane  wrote:
> Add missing keywords to gram.y's unreserved_keywords list.
>
> We really need an automated check for this ... and did VALIDATE really
> need to become a keyword at all, rather than picking some other syntax
> using existing keywords?

I think we ought to try to do something about this, so that VALIDATE
doesn't need to become a keyword.

How about instead of VALIDATE CONSTRAINT we simply write ALTER
CONSTRAINT ... VALID?  (Patch attached, passes make check.)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


alter-constraint-valid.patch
Description: Binary data

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Add missing keywords to gram.y's unreserved_keywords list.

[Alvaro Herrera]

Excerpts from Robert Haas's message of vie mar 11 15:59:40 -0300 2011:
> On Tue, Mar 8, 2011 at 4:44 PM, Tom Lane  wrote:
> > Add missing keywords to gram.y's unreserved_keywords list.
> >
> > We really need an automated check for this ... and did VALIDATE really
> > need to become a keyword at all, rather than picking some other syntax
> > using existing keywords?
> 
> I think we ought to try to do something about this, so that VALIDATE
> doesn't need to become a keyword.
> 
> How about instead of VALIDATE CONSTRAINT we simply write ALTER
> CONSTRAINT ... VALID?  (Patch attached, passes make check.)

Please make-check the docs too.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Replication server timeout patch

[Robert Haas]

On Fri, Mar 11, 2011 at 8:29 AM, Fujii Masao  wrote:
>> I think we should consider making this change for 9.1.  This is a real
>> wart, and it's going to become even more of a problem with sync rep, I
>> think.
>
> Yeah, that's a welcome! Please feel free to review the patch.

I discussed this with Heikki on IM.

I think we should rip all the GUC change stuff out of this patch and
just decree that if you set a timeout, you get a timeout.  If you set
this inconsistently with wal_receiver_status_interval, then you'll get
lots of disconnects.  But that's your problem.  This may seem a little
unfriendly, but the logic in here is quite complex and still isn't
going to really provide that much protection against bad
configurations.  The only realistic alternative I see is to define
replication_timeout as a multiple of the client's
wal_receiver_status_interval, but that seems quite annoyingly
unfriendly.  A single replication_timeout that applies to all slaves
doesn't cover every configuration someone might want, but it's simple
and easy to understand and should cover 95% of cases.  If we find that
it's really necessary to be able to customize it further, then we
might go the route of adding the much-discussed standby registration
stuff, where there's a separate config file or system table where you
can stipulate that when a walsender with application_name=foo
connects, you want it to get wal_receiver_status_interval=$FOO.  But I
think that complexity can certainly wait until 9.2 or later.

I also think that the default for replication_timeout should not be 0.
 Something like 60s seems about right.  That way, if you just use the
default settings, you'll get pretty sane behavior - a connectivity
hiccup that lasts more than a minute will bounce the client.  We've
already gotten reports of people who thought they were replicating
when they really weren't, and had to fiddle with settings and struggle
to try to make it robust.  This should make things a lot nicer for
people out of the box, but it won't if it's disabled out of the box.

On another note, there doesn't appear to be any need to change the
return value of WaitLatchOrSocket().

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Add missing keywords to gram.y's unreserved_keywords list.

[Heikki Linnakangas]

On 11.03.2011 20:59, Robert Haas wrote:

On Tue, Mar 8, 2011 at 4:44 PM, Tom Lane  wrote:

Add missing keywords to gram.y's unreserved_keywords list.

We really need an automated check for this ... and did VALIDATE really
need to become a keyword at all, rather than picking some other syntax
using existing keywords?


I think we ought to try to do something about this, so that VALIDATE
doesn't need to become a keyword.

How about instead of VALIDATE CONSTRAINT we simply write ALTER
CONSTRAINT ... VALID?  (Patch attached, passes make check.)


ALTER CONSTRAINT ... VALID sounds like it just marks the constraint as 
valid. "VALIDATE CONSTRAINT" sounds like it scans and checks that the 
constraint is valid.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Add missing keywords to gram.y's unreserved_keywords list.

[Robert Haas]

On Fri, Mar 11, 2011 at 2:39 PM, Heikki Linnakangas
 wrote:
> On 11.03.2011 20:59, Robert Haas wrote:
>>
>> On Tue, Mar 8, 2011 at 4:44 PM, Tom Lane  wrote:
>>>
>>> Add missing keywords to gram.y's unreserved_keywords list.
>>>
>>> We really need an automated check for this ... and did VALIDATE really
>>> need to become a keyword at all, rather than picking some other syntax
>>> using existing keywords?
>>
>> I think we ought to try to do something about this, so that VALIDATE
>> doesn't need to become a keyword.
>>
>> How about instead of VALIDATE CONSTRAINT we simply write ALTER
>> CONSTRAINT ... VALID?  (Patch attached, passes make check.)
>
> ALTER CONSTRAINT ... VALID sounds like it just marks the constraint as
> valid. "VALIDATE CONSTRAINT" sounds like it scans and checks that the
> constraint is valid.

Yeah, it's a little awkward, but I think it's still better than adding
another keyword.  Any other ideas for wording?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Sync Rep v19

[Ross J. Reedstrom]

On Fri, Mar 11, 2011 at 09:03:33AM -0500, Robert Haas wrote:
> On Fri, Mar 11, 2011 at 8:21 AM, Fujii Masao  wrote:
> >
> > In that case, the last write WAL timestamp would become equal to the
> > last replay WAL timestamp. So we can see that there is no lag.
> 
> Oh, I see (I think).  You're talking about write/replay lag, but I was
> thinking of master/slave transmission lag.
> 

Which are both useful numbers to know: the first tells you how "stale"
queries from a Hot Standby will be, the second tells you the maximum
data loss from a "meteor hits the master" scenario where that slave is
promoted, if I understand all the interactions correctly.

Ross
-- 
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
Connexions  http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Add missing keywords to gram.y's unreserved_keywords list.

[David Christensen]

On Mar 11, 2011, at 1:40 PM, Robert Haas wrote:

> On Fri, Mar 11, 2011 at 2:39 PM, Heikki Linnakangas
>  wrote:
>> On 11.03.2011 20:59, Robert Haas wrote:
>>> 
>>> On Tue, Mar 8, 2011 at 4:44 PM, Tom Lane  wrote:
 
 Add missing keywords to gram.y's unreserved_keywords list.
 
 We really need an automated check for this ... and did VALIDATE really
 need to become a keyword at all, rather than picking some other syntax
 using existing keywords?
>>> 
>>> I think we ought to try to do something about this, so that VALIDATE
>>> doesn't need to become a keyword.
>>> 
>>> How about instead of VALIDATE CONSTRAINT we simply write ALTER
>>> CONSTRAINT ... VALID?  (Patch attached, passes make check.)
>> 
>> ALTER CONSTRAINT ... VALID sounds like it just marks the constraint as
>> valid. "VALIDATE CONSTRAINT" sounds like it scans and checks that the
>> constraint is valid.
> 
> Yeah, it's a little awkward, but I think it's still better than adding
> another keyword.  Any other ideas for wording?


CHECK VALID?

Regards,

David
--
David Christensen
End Point Corporation
da...@endpoint.com





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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Add missing keywords to gram.y's unreserved_keywords list.

[Andrew Dunstan]

On 03/11/2011 02:50 PM, David Christensen wrote:

On Mar 11, 2011, at 1:40 PM, Robert Haas wrote:


On Fri, Mar 11, 2011 at 2:39 PM, Heikki Linnakangas
  wrote:

On 11.03.2011 20:59, Robert Haas wrote:

On Tue, Mar 8, 2011 at 4:44 PM, Tom Lane   wrote:

Add missing keywords to gram.y's unreserved_keywords list.

We really need an automated check for this ... and did VALIDATE really
need to become a keyword at all, rather than picking some other syntax
using existing keywords?

I think we ought to try to do something about this, so that VALIDATE
doesn't need to become a keyword.

How about instead of VALIDATE CONSTRAINT we simply write ALTER
CONSTRAINT ... VALID?  (Patch attached, passes make check.)

ALTER CONSTRAINT ... VALID sounds like it just marks the constraint as
valid. "VALIDATE CONSTRAINT" sounds like it scans and checks that the
constraint is valid.

Yeah, it's a little awkward, but I think it's still better than adding
another keyword.  Any other ideas for wording?


CHECK VALID?





SET VALID? (c.f. SET NULL).

cheers

andrew

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Add missing keywords to gram.y's unreserved_keywords list.

[Tom Lane]

Andrew Dunstan  writes:
> On 03/11/2011 02:50 PM, David Christensen wrote:
>> On Mar 11, 2011, at 1:40 PM, Robert Haas wrote:
>>> ALTER CONSTRAINT ... VALID sounds like it just marks the constraint as
>>> valid. "VALIDATE CONSTRAINT" sounds like it scans and checks that the
>>> constraint is valid.

> SET VALID? (c.f. SET NULL).

That sounds the best so far, but maybe we should think about other
phrases altogether (ie, not arising from the word "valid")?  I don't
have any great ideas offhand, just trying to think outside the box.

regards, tom lane

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Add missing keywords to gram.y's unreserved_keywords list.

[Robert Haas]

On Fri, Mar 11, 2011 at 3:00 PM, Andrew Dunstan  wrote:
> On 03/11/2011 02:56 PM, Andrew Dunstan wrote:
>>
>>
>>
>> SET VALID? (c.f. SET NULL).
>
> Of course I mean SET NOT NULL.
>
>
> Anyway, the full thing would be something like
>
>
> ALTER TABLE foo SET VALID CONSTRAINT bar;

Or ALTER TABLE foo SET CONSTRAINT bar VALID
Or ALTER TABLE foo ALTER CONSTRAINT bar SET VALID

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Add missing keywords to gram.y's unreserved_keywords list.

[Kevin Grittner]

Tom Lane  wrote:
> Andrew Dunstan  writes:
 
>> SET VALID? (c.f. SET NULL).
> 
> That sounds the best so far, but maybe we should think about other
> phrases altogether (ie, not arising from the word "valid")?  I
> don't have any great ideas offhand, just trying to think outside
> the box.
 
At the risk of adding yet another meaning to an
already-heavily-worked word, ANALYZE?
 
-Kevin

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Add missing keywords to gram.y's unreserved_keywords list.

[Andrew Dunstan]

On 03/11/2011 02:56 PM, Andrew Dunstan wrote:




SET VALID? (c.f. SET NULL).


Of course I mean SET NOT NULL.


Anyway, the full thing would be something like


ALTER TABLE foo SET VALID CONSTRAINT bar;


cheers

andrew

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

Re: [HACKERS] FuncExpr.collid/OpExpr.collid unworkably serving double duty

[Martijn van Oosterhout]

On Thu, Mar 10, 2011 at 05:51:31PM -0500, Tom Lane wrote:
> No, that's not what I'm on about.  Consider
> 
>   (((A COLLATE X) || B) || (C COLLATE Y)) < (D COLLATE Z)
> 
> (I've spelled out the parenthesization in full for clarity, but most
> of these parens could be omitted.)  Is this expression legal, or
> should the "<" operator be throwing an error for conflicting
> explicitly-derived collations?  Our code as it stands will take it,
> because no individual operator sees more than one COLLATE among its
> arguments.  But I'm not sure this is right.  The only text I can find
> in SQL2008 that seems to bear on the point is in 4.2.2:

The rules are essentially as described here:

http://msdn.microsoft.com/en-us/library/ms179886.aspx

So:

(A COLLATE X) => collation X
((A COLLATE X) || B)   => collation X
(((A COLLATE X) || B) || (C COLLATE Y))  => error

If we aren't erroring on this then we're doing it wrong. The whole
point of going through the parse tree and assigning a collation to each
node is to catch these things.

> As I read this, the collation attached to any Var clause is implicit
> (because it came from the Var's data type), and the collation attached
> to a CollateClause is presumably explicit, but where does it say what
> happens at higher levels in the expression tree?  It's at least arguable
> that the result collation of an expression is explicit if its input
> collation was explicit.  The fact that the default in case of doubt
> apparently is supposed to be "explicit" doesn't give any aid or comfort
> to your position either.  If explicitness comes only from the immediate
> use of COLLATE, why don't they say that?  This is worded to make one
> think that most cases will have explicit derivation, not only COLLATE.

See 9.3 "Data types of results of aggregations" clause (ii). It
contains essentially the rules outlined by the Transact-SQL page above.

The collation derivation and declared type collation of the result are
determined as follows.
Case:
1) If some data type in DTS has an explicit collation derivation and
declared type collation
EC1, then every data type in DTS that has an explicit collation
derivation shall have a declared
type collation that is EC1. The collation derivation is explicit and
the collation is EC1.
2) If every data type in DTS has an implicit collation derivation, then
Case:
A) If every data type in DTS has the same declared type collation IC1,
then the collation
derivation is implicit and the declared type collation is IC1.
B) Otherwise, the collation derivation is none.
3) Otherwise, the collation derivation is none.

In my implementation I needed to expand this to the general set of
operators postgresql supported and relaxed this to only consider
arguments to the function/operator that had the same type as the
resulting type of the function/operator, since that's the only thing
that makes sense.

A concatination then requires its arguments to be compatible. A substr
has the collation of its sole string argument.

I hope this helps,

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first. 
>   - Charles de Gaulle


signature.asc
Description: Digital signature

Re: [HACKERS] FuncExpr.collid/OpExpr.collid unworkably serving double duty

[Tom Lane]

Martijn van Oosterhout  writes:
> On Thu, Mar 10, 2011 at 05:51:31PM -0500, Tom Lane wrote:
>> No, that's not what I'm on about.  Consider
>> 
>> (((A COLLATE X) || B) || (C COLLATE Y)) < (D COLLATE Z)

> The rules are essentially as described here:
> http://msdn.microsoft.com/en-us/library/ms179886.aspx

Hmm ... that's an interesting document, but I'm not at all sure that
it intends to describe the same rules that are in the SQL standard.
In particular I don't believe that their notion of coercible-default
matches the standard.

> In my implementation I needed to expand this to the general set of
> operators postgresql supported and relaxed this to only consider
> arguments to the function/operator that had the same type as the
> resulting type of the function/operator, since that's the only thing
> that makes sense.

Yeah, that corresponds to Transact-SQL's distinction between functions
that take a string and produce a string, versus those that produce a
string without any string inputs.  But I don't see anything justifying
such a distinction in the text of the standard.

Also note that the TSQL doc explicitly points out that collation labels
can be carried up through changes of character string types, so I think
you're wrong to say that collation is only carried through functions that
produce exactly the same type as their input.  I'd say collation labels
propagate through any function that has both collatable input(s) and a
collatable result type.

In any case, I am sure that that what this describes is not what our
current code does :-(, and that we can't get anywhere close to this with
the existing infrastructure.  So at this point I'm thinking that the
safest approach is to rip out the result-collation caching fields and
perform collation assignment in a parsing post-pass.  That will allow us
to revise the collation assignment algorithm without further catversion
bumps.

regards, tom lane

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

Re: [HACKERS] Add unistd.h to c.h

[Tom Lane]

Heikki Linnakangas  writes:
> On 11.03.2011 18:55, Bruce Momjian wrote:
>> OK, I am just asking.  FYI, we already include a boatload of includes in
>> c.h:
>> 
>> #include
>> #include
>> #include
>> #include
>> #include
>> #ifdef HAVE_STRINGS_H
>> #include
>> #endif
>> #ifdef HAVE_STDINT_H
>> #include
>> #endif
>> #include

> Presumably all of these are used by something in c.h itself. At least 
> strings.h is needed by memset, and stddef.h and/or stdlib.h is needed 
> for size_t. I'm too lazy to check the rest, but if there are any header 
> files there that are not in fact used by anything in c.h itself, they 
> should be removed from c.h, rather than going further into that direction.

I would argue that most of those includes (in particular all the stdXXX
ones) are needed to establish a minimum sane C programming environment.
Removing them would not be productive, regardless of whether c.h itself
requires them.

There are a bunch of *other* includes in c.h and the OS-specific port
files, which I'd like to see minimized, but not those.  It's this kind
of thing that we should be trying to get rid of:

#if defined(WIN32) || defined(__CYGWIN__)
#include   /* ensure O_BINARY is available */
#endif

because it greatly increases the probability that a patch developed on
one platform will not port to others where c.h doesn't pull in the
same header.

regards, tom lane

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

Re: [HACKERS] pg_dump -X

[David Fetter]

On Thu, Mar 10, 2011 at 10:46:47PM -0500, Robert Haas wrote:
> On Thu, Mar 10, 2011 at 10:36 PM, Bruce Momjian  wrote:
> > Robert Haas wrote:
> >> Back in 2006, we have this commit:
> >>
> >> commit 2b25e1169f44368c120931787628d51731b5cc8c
> >> Author: Peter Eisentraut 
> >> Date:   Sat Oct 7 20:59:05 2006 +
> >>
> >>     The -X option in pg_dump was supposed to be a workaround for the lack 
> >> of
> >>     portable long options.  But we have had portable long options for a 
> >> long
> >>     time now, so this is obsolete.  Now people have added options which 
> >> *only*
> >>     work with -X but not as regular long option, so I'm putting a stop to 
> >> this:
> >>     -X is deprecated; it still works, but it has been removed from the
> >>     documentation, and please don't add more of them.
> >>
> >> Since then, two additional -X options have crept in, doubtless due to
> >> mimicry of the existing options without examination of the commit
> >> logs.  I think we should either (a) remove the -X option altogether or
> >> (b) change the comment so that it clearly states the same message that
> >> appears here in the commit log, namely, that no new -X options are to
> >> be created.  The existing comment says that -X is deprecated, but that
> >> doesn't make it entirely 100% clear that the code isn't intended to be
> >> further updated, at least judging by the results.
> >
> > Code comment added with attached, applied patch.
> 
> At a minimum, we should probably also remove -X no-security-label and
> -X no-unlogged-table-data, which don't exist in any released versions
> (unless you want to count alphas).  But considering that this has been
> deprecated and undocumented since 8.2, I think it might be time to
> pull the plug on -X altogether.

+1 for pulling this plug :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

[HACKERS] Avoiding repeated ON COMMIT truncation for temporary tables

[Bruce Momjian]

Currently, if you create a temporary table with the ON COMMIT action of
DELETE ROWS, the table will truncated for every commit, whether there is
any data in the table or not.

I measured the overhead using this test:

$ (echo 'CREATE TEMPORARY TABLE TEST2 (x int);'; jot -b 'SELECT 1;'
1) | time psql  test > /dev/null
6.93 real 0.93 user 0.78 sys
$ (echo 'CREATE TEMPORARY TABLE TEST2 (x int) ON COMMIT DELETE ROWS;';
jot -b 'SELECT 1;' 1) | time psql  test > /dev/null
7.93 real 1.02 user 0.72 sys

The overhead measures 14%.  Is there a simple way to avoid the repeated
truncation overhead of such cases?  Is this a TODO?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] pg_dump -X

[Alvaro Herrera]

Excerpts from Bruce Momjian's message of vie mar 11 00:59:03 -0300 2011:

> > At a minimum, we should probably also remove -X no-security-label and
> > -X no-unlogged-table-data, which don't exist in any released versions
> > (unless you want to count alphas).  But considering that this has been
> > deprecated and undocumented since 8.2, I think it might be time to
> > pull the plug on -X altogether.
> 
> I remove the new -X options with the attached, applied patch.  The
> existing options are not really costing us anything except a few lines
> of code.

Given that, it seems pretty pointless to remove support for -X options
that have existed for years.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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