Re: [HACKERS] Patch: add conversion from pg_wchar to multibyte

[Tatsuo Ishii]

> Well, when the preceding comment block contains five references to
> xemacs and the link for more information leads to www.xemacs.org,
> I don't think it's real helpful to add one sentence saying "oh
> by the way we're not actually following xemacs".
> 
> I continue to think that we'd be better off to follow the xemacs
> spec, as the subdivisions the emacs spec is insisting on seem like
> entirely useless complication.  The only possible reason for doing
> it the emacs way is that it would provide room for twice as many
> charset IDs ... but the present design for wchar conversion destroys
> that advantage, because it requires the charset ID spaces to be
> nonoverlapping anyhow.  Moreover, it's not apparent to me that
> charset standards are still proliferating, so I doubt that we need
> any more ID space.

Well, we have been following emacs spec, not xemacs spec from the day
0. I don't see any value to switch to xemacs way at this moment,
because I think the reason why we support particular encoding is, to
keep on supporting existing user data, not "enhance" our internal
architecture.

If you like xeamcs's spec, I think you'd better add new encoding,
rather than break data compatibility.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

-- 
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] Patch: add conversion from pg_wchar to multibyte

[Tom Lane]

Tatsuo Ishii  writes:
> Done along with comment that we follow emacs's implementation, not
> xemacs's.

Well, when the preceding comment block contains five references to
xemacs and the link for more information leads to www.xemacs.org,
I don't think it's real helpful to add one sentence saying "oh
by the way we're not actually following xemacs".

I continue to think that we'd be better off to follow the xemacs
spec, as the subdivisions the emacs spec is insisting on seem like
entirely useless complication.  The only possible reason for doing
it the emacs way is that it would provide room for twice as many
charset IDs ... but the present design for wchar conversion destroys
that advantage, because it requires the charset ID spaces to be
nonoverlapping anyhow.  Moreover, it's not apparent to me that
charset standards are still proliferating, so I doubt that we need
any more ID space.

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] BlockNumber initialized to InvalidBuffer?

[Tom Lane]

Markus Wanner  writes:
> I stumbled across an initialization of a BlockNumber with InvalidBuffer,
> which seems strange to me, as the values for "invalid" of the two types
> are different, see attached patch.

That's certainly bogus ...

> In case the 'stack' argument passed to that function is not NULL, the
> variable in question gets overridden immediately, in which case it
> certainly doesn't matter. I don't know nor did I check whether or not it
> can ever be NULL. So this might not be a real issue at all.

... but AFAICS, ginInsertValue cannot be called with stack == NULL at
any of the existing call sites.  Moreover, if stack were NULL, the
function would do nothing, which seems to me to violate its API contract
to insert the given value into the index.

So I think a better fix is to Assert that the passed stack isn't
NULL, along the lines of

GinBtreeStack *parent;
BlockNumber rootBlkno;
Pagepage,
rpage,
lpage;

/* extract root BlockNumber from stack */
Assert(stack != NULL);
parent = stack;
do
{
rootBlkno = parent->blkno;
parent = parent->parent;
} while (parent);

I'm also inclined to think that the "while (stack)" coding of the rest
of it is wrong, misleading, or both, on precisely the same grounds: if
that loop ever did fall out at the test, the function would have failed
to honor its contract.  The only correct exit points are the "return"s
in the middle.

Comments?

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] Using pg_upgrade on log-shipping standby servers

[Bruce Momjian]

On Tue, Jul 10, 2012 at 09:10:25PM +0200, Magnus Hagander wrote:
> On Tue, Jul 10, 2012 at 7:27 PM, Bruce Momjian  wrote:
> > On Tue, Jul 10, 2012 at 07:06:39PM +0200, Magnus Hagander wrote:
> >> >> >> rsync where and how? What are you actually trying to suggest people
> >> >> >> do?
> >> >> >
> >> >> > Updated docs attached.
> >> >>
> >> >> I suggest just removing the rsync part completely. You're basically
> >> >> saying "you ca nset up a new standby after you're done", which is kind
> >> >> of obvious anyway. And if you're going to use rsync fromthe master to
> >> >> make a new standby, there's no point in running pg_upgrade on the new
> >> >> standby in the first place.
> >> >
> >> > I went the other direction and just said you can't upgrade a standby (as
> >> > a standby), and to just use rsync --- patch attached.
> >>
> >> Reads much better now. I'd say "use rsync to rebuild the standbys",
> >> but that's more nitpicking :) (And maybe "the simplest way" rather
> >> than "the simplest case"? But i'll leave that to someone who has
> >> english as their first language)
> >
> > Both change made;  updated patch attached.
> 
> Looks good to me.

OK, applied and backpatched to 9.2.

-- 
  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] Patch: add conversion from pg_wchar to multibyte

[Tatsuo Ishii]

 Tatsuo Ishii  writes:
>> So far as I can see, the only LCPRVn marker code that is actually in
>> use right now is 0x9d --- there are no instances of 9a, 9b, or 9c
>> that I can find.
>> 
>> I also read in the xemacs internals doc, at
>> http://www.xemacs.org/Documentation/21.5/html/internals_26.html#SEC145
>> that XEmacs thinks the marker code for private single-byte charsets
>> is 0x9e (only) and that for private multi-byte charsets is 0x9f (only);
>> moreover they think 0x9a-0x9d are potential future official multibyte
>> charset codes.  I don't know how we got to the current state of using
>> 0x9a-0x9d as private charset markers, but it seems pretty inconsistent
>> with XEmacs.
 
> At the time when mule internal code was introduced to PostgreSQL,
> xemacs did not have multi encoding capabilty and mule (a patch to
> emacs) was the only implementation allowed to use multi encoding. So I
> used the specification of mule documented in the URL I wrote.
 
 I see.  Given that upstream has decided that a simpler definition is
 more appropriate, is there any reason not to follow their lead, to the
 extent that we can do so without breaking existing on-disk data?
>>> 
>>> Please let me spend week end to understand the their latest spec.
>> 
>> This is an intermediate report on the internal multi-byte charset
>> implementation of emacen. I have read the link Tom showed. Also I made
>> a quick scan on xemacs-21.4.0 source code, especially
>> xemacs-21.4.0/src/mule-charset.h. It seems the web document is
>> essentially a copy of the comments in the file. Also I looked into
>> other place of xemacs code and I think I can conclude that xeamcs
>> 21.4's multi-byte implementation is based on the doc on the web.
>> 
>> Next I looked into emacs 24.1 source code because I could not find any
>> doc regarding emacs's(not xemacs's) implementation of internal
>> multi-byte charset. I found followings in src/charset.h:
>> 
>> /* Leading-code followed by extended leading-code.DIMENSION/COLUMN */
>> #define EMACS_MULE_LEADING_CODE_PRIVATE_11   0x9A /* 1/1 */
>> #define EMACS_MULE_LEADING_CODE_PRIVATE_12   0x9B /* 1/2 */
>> #define EMACS_MULE_LEADING_CODE_PRIVATE_21   0x9C /* 2/2 */
>> #define EMACS_MULE_LEADING_CODE_PRIVATE_22   0x9D /* 2/2 */
>> 
>> And these are used like this:
>> 
>> /* Read one non-ASCII character from INSTREAM.  The character is
>>encoded in `emacs-mule' and the first byte is already read in
>>C.  */
>> 
>> static int
>> read_emacs_mule_char (int c, int (*readbyte) (int, Lisp_Object), Lisp_Object 
>> readcharfun)
>> {
>> :
>> :
>>   else if (len == 3)
>> {
>>   if (buf[0] == EMACS_MULE_LEADING_CODE_PRIVATE_11
>>|| buf[0] == EMACS_MULE_LEADING_CODE_PRIVATE_12)
>>  {
>>charset = CHARSET_FROM_ID (emacs_mule_charset[buf[1]]);
>>code = buf[2] & 0x7F;
>>  }
>> 
>> As far as I can tell, this is exactly the same way how PostgreSQL
>> handles single private character sets: they consist of 3 bytes, and
>> leading byte is either 0x9a or 0x9b. Other examples regarding single
>> byte/multi-byte private charsets can be seen in coding.c.
>> 
>> As far as I can tell, it seems emacs and xemacs employes different
>> implementations of multi-byte charaset regarding "private"
>> charsets. Emacs's is same as PostgreSQL, while xemacs is not.  I am
>> contacting to the original Mule author if he knows anything about
>> this.
> 
> I got reply from the Mule author, Kenichi Handa (the mail is in
> Japanese. So I do not quote his mail here. If somebody wants to read
> the original mail please let me know). First of all my understanding
> with emacs's implementaion is correct according to him. He did not
> know about xemacs's implementation. Apparently the implementation of
> xemacs was not lead by the original mule author.
> 
> So which one of emacs/xemacs should we follow? My suggestion is, not
> to follow xemacs, and to leave the current treatment of private
> leading byte as it is because emacs seems to be more "right" upstream
> comparing with xemacs.
> 
>> BTW, while looking into emacs's source code, I found their charset
>> definitions are in lisp/international/mule-conf.el. According to the
>> file several new charsets has been added. Included is the patch to
>> follow their changes. This makes no changes to current behavior, since
>> the patch just changes some comments and non supported charsets.
> 
> If there's no objection, I would like to commit this. Objection?

Done along with comment that we follow emacs's implementation, not
xemacs's.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

-- 
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] Synchronous Standalone Master Redoux

[Daniel Farina]

On Tue, Jul 10, 2012 at 2:42 PM, Dimitri Fontaine
 wrote:>
> What you explain you want reads to me "Async replication + Archiving".

Notable caveat: one can't very easily measure or bound the amount of
transaction loss in any graceful way  as-is.  We only have "unlimited
lag" and "2-safe or bust".

Presumably the DRBD setup run by the original poster can do this:

* run without a partner in a degraded mode (to use common RAID terminology)

* asynchronous rebuild and catch-up of a new remote RAID partner

* switch to synchronous RAID-1, which attenuates the source of block
device changes to get 2-safe reliability (i.e. blocking on
confirmations from two block devices)

However, the tricky part is what is DRBD's heuristic when suffering
degraded but non-zero performance of the network or block device will
drop attempts to replicate to its partner.  Postgres's interpretation
is "halt, because 2-safe is currently impossible."  DRBD seems to be
"continue" (but hopefully record a statistic, because who knows how
often you are actually 2-safe, then).

For example, what if DRBD can only complete one page per second for
some reason?  Does it it simply have the primary wait at this glacial
pace, or drop synchronous replication and go degraded?  Or does it do
something more clever than just a timeout?

These may seem like theoretical concerns, but 'slow, but non-zero'
progress has been an actual thorn in my side many times.

Regardless of what DRBD does, I think the problem with the async/sync
duality as-is is there is no nice way to manage exposure to
transaction loss under various situations and requirements.  I'm not
really sure what a solution might look like; I was going to do
something grotesque and conjure carefully orchestrated standby status
packets to accomplish this.

-- 
fdr

-- 
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] enhanced error fields

[Tom Lane]

Alvaro Herrera  writes:
> FWIW about the new include:  I feel a strong dislike about the forward
> declaration you suggest.  Defining Relation in elog.h seems completely
> out of place.

Agreed.  Maybe a reasonable solution is to allow some ereport helper
functions (or, really, wrappers for the helper functions) to be declared
someplace else than elog.h.  They'd likely need to be implemented
someplace else than elog.c, too, so this doesn't seem unreasonable.

The generic helper function approach doesn't seem too unreasonable for
this: elog.h/.c would provide something like

err_generic_string(int fieldid, const char *str)

and then someplace else could provide functions built on this that
insert table/schema/column/constraint/etc names into suitable fields.

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] Testing of various opclasses for ranges

[Alexander Korotkov]

On Tue, Jul 10, 2012 at 1:38 PM, Heikki Linnakangas <
heikki.linnakan...@enterprisedb.com> wrote:

> I think the ultimate question is, which ones of these should we include in
> core? We cannot drop the existing range_ops opclass, if only because that
> would break pg_upgrade. However, range_ops2 seems superior to it, so I
> think we should make that the default for new indexes.
>

Actually, I'm not fully satisfied with range_ops2. I expect it could be
recommend for all cases, but actually it builds significantly slower and
sometimes requires more pages for search. Likely, we have to write some
recommedation in docs about which opclass to use in particular.
Additionally, somebody could think GiST range indexing becoming tangled.

For SP-GiST, I don't think we need to include both quad and k-d tree
> implementations. They have quite similar characteristics, so IMHO we should
> just pick one. Which one would you prefer? Is there any difference in terms
> of code complexity between them? Looking at the performance test results,
> quad tree seems to be somewhat slower to build, but is faster to query.
> Based on that, I think we should pick the quad tree, query performance
> seems more important.


Agree, I think we should stay at quad tree implemetation.

--
With best regards,
Alexander Korotkov.

Re: [HACKERS] has_language_privilege returns incorrect answer for non-superuser

[Joe Conway]

On 07/10/2012 12:50 AM, Magnus Hagander wrote:
> On Tue, Jul 10, 2012 at 3:23 AM, Joe Conway  wrote:
>> I verified this behavior on head as well as 9.1 (didn't bother looking
>> any further back). Looks like the reason is that CreateFunction()
>> correctly checks lanpltrusted, whereas pg_language_aclmask() does not.
>>
>> Seems like a bug to me -- opinions?
> 
> Definitely seems like a bug to me, yes.
> 
> And while I haven't verified that the suggested fix actually fixes it
> for me, it sounds reasonable :)

I realized there is a somewhat analogous situation with schema objects
and schema USAGE permission. I.e. I find this understandable but surprising:

8<--
test1=> \c - postgres
You are now connected to database "test1" as user "postgres".
test1=# select has_table_privilege('nobody','sf.foo','select');
 has_table_privilege
-
 f
(1 row)

test1=# grant select on table sf.foo to nobody;
GRANT
test1=# select has_table_privilege('nobody','sf.foo','select');
 has_table_privilege
-
 t
(1 row)

test1=# \c - nobody
You are now connected to database "test1" as user "nobody".
test1=> select * from sf.foo;
ERROR:  permission denied for schema sf
LINE 1: select * from sf.foo;
8<--

So I think this boils down to what we think the output of the various
has_*_privilege() functions *should* tell you:

1) privileges possessed even though they may not
   be usable
 -or-
2) privileges possessed and usable

Personally I'm interested in answering the latter question -- what are
all the things role X can do and see.

But historically (and perhaps correctly) these functions have always
done the former -- so maybe all we need are some words of warning in the
documentation of these functions?

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 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] Schema version management

[Merlin Moncure]

On Sun, May 20, 2012 at 2:41 PM, Joel Jacobson  wrote:
> Hi,
>
> I just read a very interesting post about "schema version management".
>
> Quote: "You could set it up so that every developer gets their own
> test database, sets up the schema there, takes a dump, and checks that
> in. There are going to be problems with that, including that dumps
> produced by pg_dump are ugly and optimized for restoring, not for
> developing with, and they don't have a deterministic output order." (
> http://petereisentraut.blogspot.com/2012/05/my-anti-take-on-database-schema-version.html
> )
>
> Back in December 2010, I suggested a new option to pg_dump, --split,
> which would write the schema definition of each object in separate
> files:
>
> http://archives.postgresql.org/pgsql-hackers/2010-12/msg02285.php
>
> Instead of a huge plain text schema file, impossible to version
> control, all tables/sequences/views/functions are written to separate
> files, allowing the use of a version control software system, such as
> git, to do proper version controlling.
>
> The "deterministic output order" problem mentioned in the post above,
> is not a problem if each object (table/sequence/view/function/etc) is
> written to the same filename everytime.
> No matter the order, the tree of files and their content will be
> identical, no matter the order in which they are dumped.
>
> I remember a lot of hackers were very positive about this option, but
> we somehow failed to agree on the naming of files in the tree
> structure. I'm sure we can work that out though.
>
> I use this feature in production, I have a cronjob which does a dump
> of the schema every hour, committing any eventual changes to a
> separate git branch for each database installation, such as
> production, development and test.
> If no changes to the schema have been made, nothing will be committed
> to git since none of the files have changed.
>
> It is then drop-dead simple to diff two different branches of the
> database schema, such as development or production, or diffing
> different revisions allowing point-in-time comparison of the schema.
>
> This is an example of the otuput of a git log --summary for one of the
> automatic commits to our production database's git-repo:
>
> --
> commit 18c31f8162d851b0dac3bad7e80529ef2ed18be3
> Author: Production Database 
> Date:   Fri May 4 15:00:04 2012 +0200
>
> Update of database schema Linux DB0 2.6.26-2-amd64 #1 SMP Wed Aug
> 19 22:33:18 UTC 2009 x86_64 GNU/Linux Fri, 04 May 2012 15:00:04 +0200
>
>  create mode 100644
> gluepay-split/public/CONSTRAINT/openingclosingbalances_pkey.sql
>  create mode 100644
> gluepay-split/public/CONSTRAINT/openingclosingbalances_source_key.sql
>  create mode 100644 gluepay-split/public/SEQUENCE/seqopeningclosingbalance.sql
>  create mode 100644 gluepay-split/public/TABLE/openingclosingbalances.sql
> --
>
> Here we can see we apparently deployed a new table,
> "openingclosingbalances" around Fri May 4 15:00:04.
>
> Without any manual work, I'm able to follow all changes actually
> _deployed_ in each database.
>
> At my company, a highly database-centric stored-procedure intensive
> business dealing with mission-critical monetary transactions, we've
> been using this technique to successfully do schema version management
> without any hassle for the last two years.
>
> Hopefully this can add to the list of various possible _useful_ schema
> version management methods.

What does your patch do that you can't already do with pg_restore?

create function foo(a int, b int, c text) returns int as $$ select 0;
$$ language sql;
CREATE FUNCTION

pg_dump -Fc postgres -s > postgres.dump
pg_restore -l postgres.dump  | grep FUNCTION
196; 1255 32939 FUNCTION public foo(integer, integer, text) merlin

pg_restore -P "foo(integer, integer, text)" postgres.dump


it's fairly easy to wrap pg_restore with a smalls script that extracts
function bodies and writes them out to file names.  this is a great
and underused feature, so I'd argue that if you wanted to formalize
per object file extraction you should be looking at expanding
pg_restore, not pg_dump.

merlin

-- 
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] Schema version management

[Magnus Hagander]

On Tue, Jul 10, 2012 at 11:39 PM, Peter Eisentraut  wrote:
> On sön, 2012-07-08 at 18:52 -0400, Tom Lane wrote:
>> Peter Eisentraut  writes:
>> > On lör, 2012-07-07 at 17:18 -0400, Tom Lane wrote:
>> >> Sure.  You need not look further than "/" to find an operator name
>> that
>> >> absolutely *will* cause trouble if it's dumped into a filename
>> >> literally.
>>
>> > But that problem applies to all object names.
>>
>> In principle, yes, but in practice it's far more likely that operators
>> will have names requiring some sort of encoding than that objects with
>> SQL-identifier names will.
>
> I'm not sure.  The only character that's certainly an issue is "/".  Are
> there any others on file systems that we want to support?

\ and : if we care at all about windows

-- 
 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] Schema version management

[Andrew Dunstan]

On 07/10/2012 05:39 PM, Peter Eisentraut wrote:

On sön, 2012-07-08 at 18:52 -0400, Tom Lane wrote:

Peter Eisentraut  writes:

On lör, 2012-07-07 at 17:18 -0400, Tom Lane wrote:

Sure.  You need not look further than "/" to find an operator name

that

absolutely *will* cause trouble if it's dumped into a filename
literally.

But that problem applies to all object names.

In principle, yes, but in practice it's far more likely that operators
will have names requiring some sort of encoding than that objects with
SQL-identifier names will.

I'm not sure.  The only character that's certainly an issue is "/".  Are
there any others on file systems that we want to support?




In general, NTFS forbids the use of these printable ASCII chars in 
filenames (see 
:



" * : < > ? \ / |


Many of these could be used in operators.

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] Synchronous Standalone Master Redoux

[Dimitri Fontaine]

Shaun Thomas  writes:
> When you re-connect a secondary device, it catches up as fast as possible by
> replaying waiting transactions, and then re-attaching to the cluster. Until
> it's fully caught-up, it doesn't exist. DRBD acknowledges the secondary is
> there and attempting to catch up, but does not leave "degraded" mode until
> the secondary reaches "UpToDate" status.

That's exactly what happens with PostgreSQL when using asynchronous
replication and archiving. When joining the cluster, the standby will
feed from the archives and then there's nothing recent enough left over
there, and only at this time it will contact the master.

For a real graceful setup you need both archiving and replication.

Then, synchronous replication means that no transaction can make it to
the master alone. The use case is not being allowed to tell the client
it's ok when you're at risk of losing the transaction by crashing the
master when it's the only one knowing about it.

What you explain you want reads to me "Async replication + Archiving".

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] Schema version management

[Peter Eisentraut]

On sön, 2012-07-08 at 18:52 -0400, Tom Lane wrote:
> Peter Eisentraut  writes:
> > On lör, 2012-07-07 at 17:18 -0400, Tom Lane wrote:
> >> Sure.  You need not look further than "/" to find an operator name
> that
> >> absolutely *will* cause trouble if it's dumped into a filename
> >> literally.
> 
> > But that problem applies to all object names.
> 
> In principle, yes, but in practice it's far more likely that operators
> will have names requiring some sort of encoding than that objects with
> SQL-identifier names will.

I'm not sure.  The only character that's certainly an issue is "/".  Are
there any others on file systems that we want to 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] enhanced error fields

[Peter Geoghegan]

On 10 July 2012 21:26, Alvaro Herrera  wrote:
>> I haven't defined Relation in elog.h; I have pre-declared it there.
>> Maybe that isn't to your taste, but there is surely something to be
>> said for adding exactly one line of code in preference to adding an
>> entire new header file, and having a bunch of existing files include
>> that new header.
>
> That is true.  I'd like to hear others' opinions.

It seems that the code, exactly as written, relies upon a GNU
extension that didn't make it into the standard until C11 - the
redefinition of a typedef. Clang warns about this. Still, it ought to
be possible, if not entirely straightforward, to use a pre-declaration
all the same.

-- 
Peter Geoghegan   http://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

Re: [HACKERS] enhanced error fields

[Alvaro Herrera]

Excerpts from Peter Geoghegan's message of mar jul 10 15:54:59 -0400 2012:
> On 10 July 2012 20:28, Alvaro Herrera  wrote:

> > FWIW about the new include:  I feel a strong dislike about the forward
> > declaration you suggest.  Defining Relation in elog.h seems completely
> > out of place.  The one you suggested as precedent (BufFile) is
> > completely unlike it, in that the declaration is clearly placed in the
> > header (buffile.h) of the module that works with the struct in question.
> 
> I haven't defined Relation in elog.h; I have pre-declared it there.
> Maybe that isn't to your taste, but there is surely something to be
> said for adding exactly one line of code in preference to adding an
> entire new header file, and having a bunch of existing files include
> that new header.

That is true.  I'd like to hear others' opinions.

-- 
Á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: [SPAM] [MessageLimit][lowlimit] Re: [HACKERS] pl/perl and utf-8 in sql_ascii databases

[Alvaro Herrera]

Excerpts from Kyotaro HORIGUCHI's message of mar jul 03 04:59:38 -0400 2012:
> Hello, Here is regression test runs on pg's also built with
> cygwin-gcc and VC++.
> 
> The patches attached following,
> 
> - plperl_sql_ascii-4.patch : fix for pl/perl utf8 vs sql_ascii
> - plperl_sql_ascii_regress-1.patch : regression test for this patch.
>  I added some tests on encoding to this.
> 
> I will mark this patch as 'ready for committer' after this.

I have pushed these changes to HEAD, 9.2 and 9.1.  Instead of the games
with plperl_lc_*.out being copied around, I just used the ASCII version
as plperl_lc_1.out and the UTF8 one as plperl_lc.out.

I chose to backpatch the whole thing instead of cherry-picking parts of
it; that was turning into a tedious and pointless exercise.  We'll see
how does the buildfarm like the whole thing -- including on MSVC, which
I did not test at all.

-- 
Á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] enhanced error fields

[Peter Geoghegan]

On 10 July 2012 20:28, Alvaro Herrera  wrote:
> I think we should just define constants for the set of fields the patch
> currently uses.  When and if we later add new fields to other callsites,
> we can define more constants.

Fair enough. Let's do that.

> FWIW about the new include:  I feel a strong dislike about the forward
> declaration you suggest.  Defining Relation in elog.h seems completely
> out of place.  The one you suggested as precedent (BufFile) is
> completely unlike it, in that the declaration is clearly placed in the
> header (buffile.h) of the module that works with the struct in question.

I haven't defined Relation in elog.h; I have pre-declared it there.
Maybe that isn't to your taste, but there is surely something to be
said for adding exactly one line of code in preference to adding an
entire new header file, and having a bunch of existing files include
that new header. That said, it's not as if I feel strongly about it.

-- 
Peter Geoghegan   http://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

Re: [HACKERS] enhanced error fields

[Alvaro Herrera]

Excerpts from Peter Geoghegan's message of mar jul 10 14:56:40 -0400 2012:

> On 7 July 2012 13:57, Pavel Stehule  wrote:

> >> +#define PG_DIAG_TRIGGER_SCHEMA 'h'
> >>
> >> Not all appear to have a way of setting the value within the ereport
> >> interface. For example, there is nothing like "errrelation_column()"
> >> (or "errrelcol()", as I call it) to set PG_DIAG_ROUTINE_NAME. This is
> >> something I haven't touched.
> >
> > When I sent this patch first time, then one issue was new functions
> > for these fields. Tom proposal was using a generic function for these
> > new fields. These fields holds separated values, but in almost all
> > cases some combinations are used - "ROUTINE_NAME, ROUTINE_SCHEMA",
> > "TABLE_NAME, TABLE_SCHEMA" - so these fields are not independent -
> > this is difference from original ErrorData fields - so axillary
> > functions doesn't follow these fields - because it is not practical.
> 
> Maybe it isn't practical to do it that way, but I think that we need
> to have a way of setting the fields from an ereport callsite. I am
> willing to accept that it may make sense to add existing ereport sites
> by piecemeal, in later patches, but I think you should figure out how
> regular ereport sites are supposed to do this before anything is
> committed. We need to nail down the interface first.

I think we should just define constants for the set of fields the patch
currently uses.  When and if we later add new fields to other callsites,
we can define more constants.


FWIW about the new include:  I feel a strong dislike about the forward
declaration you suggest.  Defining Relation in elog.h seems completely
out of place.  The one you suggested as precedent (BufFile) is
completely unlike it, in that the declaration is clearly placed in the
header (buffile.h) of the module that works with the struct in question.

-- 
Á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] Using pg_upgrade on log-shipping standby servers

[Magnus Hagander]

On Tue, Jul 10, 2012 at 7:27 PM, Bruce Momjian  wrote:
> On Tue, Jul 10, 2012 at 07:06:39PM +0200, Magnus Hagander wrote:
>> >> >> rsync where and how? What are you actually trying to suggest people
>> >> >> do?
>> >> >
>> >> > Updated docs attached.
>> >>
>> >> I suggest just removing the rsync part completely. You're basically
>> >> saying "you ca nset up a new standby after you're done", which is kind
>> >> of obvious anyway. And if you're going to use rsync fromthe master to
>> >> make a new standby, there's no point in running pg_upgrade on the new
>> >> standby in the first place.
>> >
>> > I went the other direction and just said you can't upgrade a standby (as
>> > a standby), and to just use rsync --- patch attached.
>>
>> Reads much better now. I'd say "use rsync to rebuild the standbys",
>> but that's more nitpicking :) (And maybe "the simplest way" rather
>> than "the simplest case"? But i'll leave that to someone who has
>> english as their first language)
>
> Both change made;  updated patch attached.

Looks good to me.

-- 
 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] enhanced error fields

[Peter Geoghegan]

On 7 July 2012 13:57, Pavel Stehule  wrote:
>> In my revision, I've just added a pre-declaration and removed the
>> dedicated header, which didn't make too much sense to me:
>>
>> + /* Pre-declare Relation, in order to avoid a build dependency on rel.h. */
>> + typedef struct RelationData *Relation;

>> Opaque pointers are ordinarily used to encapsulate things in C, rather
>> than to prevent build dependencies, but I believe that's only because
>> in general that's something that C programmers are more likely to
>> want.
>>
>
> It is question for Alvaro or Tom. I have not strong opinion on it.

Fair enough.

>> You always log all of these new fields within write_csvlog(), even if 
>> (Log_error_verbosity <
>> PGERROR_VERBOSE). Why?

> it is bug - these new fields should be used only when verbosity is >= VERBOSE

Please fix it.

>> +#define PG_DIAG_TRIGGER_SCHEMA 'h'
>>
>> Not all appear to have a way of setting the value within the ereport
>> interface. For example, there is nothing like "errrelation_column()"
>> (or "errrelcol()", as I call it) to set PG_DIAG_ROUTINE_NAME. This is
>> something I haven't touched.
>
> When I sent this patch first time, then one issue was new functions
> for these fields. Tom proposal was using a generic function for these
> new fields. These fields holds separated values, but in almost all
> cases some combinations are used - "ROUTINE_NAME, ROUTINE_SCHEMA",
> "TABLE_NAME, TABLE_SCHEMA" - so these fields are not independent -
> this is difference from original ErrorData fields - so axillary
> functions doesn't follow these fields - because it is not practical.

Maybe it isn't practical to do it that way, but I think that we need
to have a way of setting the fields from an ereport callsite. I am
willing to accept that it may make sense to add existing ereport sites
by piecemeal, in later patches, but I think you should figure out how
regular ereport sites are supposed to do this before anything is
committed. We need to nail down the interface first.

> I understand, but fixing any ereport in core is difficult for
> processing. So coverage only some subset is practical (first stage) -
> with some basic infrastructure in core all other patches with better
> covering will be simpler for review and for commit too. RI and
> constraints is more often use cases where you would to parse error
> messages - these will be covered in first stage.

Okay. What subset? I would hope that it was a well-defined subset.

-- 
Peter Geoghegan   http://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

Re: [HACKERS] [PATCH] psql \n shortcut for set search_path =

[Björn Häuser]

Am 10.07.2012 18:44, schrieb Tom Lane:

David Fetter  writes:

On Tue, Jul 10, 2012 at 12:00:06PM -0400, Tom Lane wrote:

ISTM there was some discussion awhile back about user-definable
typing shortcuts in psql.



In some sense, we already have them:


Good point:

regression=# show search_path ;
   search_path

  "$user",public
(1 row)

regression=# \set n 'set search_path ='
regression=# :n foo;
SET
regression=# show search_path ;
  search_path
-
  foo
(1 row)


Well, a separate command would be mandatory to have tab-completion? 
Maybe not a single-letter one, but I really would appreciate such an 
command.

Setting the search_path is a thing I do several times a day.

Björn



So maybe what's needed here is a documentation example showing how you
can use a \set in ~/.psqlrc to provide this sort of functionality.

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] Btree or not btree? That is the question

[Greg Sabino Mullane]

On Mon, Jul 09, 2012 at 04:02:13PM -0400, Tom Lane wrote:
> ... Could you crank up the log verbosity so we can get 
> file and line number, at least?

Here is what the increased verbosity reveals in aggregate. This 
is about an 18-hour span, covering 12.5M transactions, on 
version 8.3.18:

(13 times) Various OIDs that do exist cannot be found:
ERROR:  XX000: could not open relation with OID 1554847444
LOCATION:  relation_open, heapam.c:879

(21 times) Relations that do exist cannot be found:
ERROR:  42P01: relation "foobar" does not exist
LOCATION:  RangeVarGetRelid, namespace.c:273

(1 time) Qualified relation that exists cannot be found:
ERROR:  42P01: relation "public.foobar" does not exist
LOCATION:  RangeVarGetRelid, namespace.c:268

(5 times) Failure to read a block:
XX001: could not read block 3 of relation 1663/1554846571/4184054438: read only 
0 of 8192 bytes
LOCATION:  mdread, md.c:631

(5 times) Cache lookup failure:
XX000: cache lookup failed for relation 1554847255
LOCATION:  has_subclass, plancat.c:921

-- 
Greg Sabino Mullane g...@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8


pgpBH51gd5sgI.pgp
Description: PGP signature

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

[Bruce Momjian]

On Tue, Jul 10, 2012 at 07:06:39PM +0200, Magnus Hagander wrote:
> >> >> rsync where and how? What are you actually trying to suggest people
> >> >> do?
> >> >
> >> > Updated docs attached.
> >>
> >> I suggest just removing the rsync part completely. You're basically
> >> saying "you ca nset up a new standby after you're done", which is kind
> >> of obvious anyway. And if you're going to use rsync fromthe master to
> >> make a new standby, there's no point in running pg_upgrade on the new
> >> standby in the first place.
> >
> > I went the other direction and just said you can't upgrade a standby (as
> > a standby), and to just use rsync --- patch attached.
> 
> Reads much better now. I'd say "use rsync to rebuild the standbys",
> but that's more nitpicking :) (And maybe "the simplest way" rather
> than "the simplest case"? But i'll leave that to someone who has
> english as their first language)

Both change made;  updated patch attached.

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

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index 30f4544..cffcebb
*** a/doc/src/sgml/pgupgrade.sgml
--- b/doc/src/sgml/pgupgrade.sgml
*** psql --username postgres --file script.s
*** 518,523 
--- 518,529 

  

+A Log-Shipping Standby Server () cannot
+be upgraded because the server must allow writes.  The simplest way
+is to upgrade the primary and use rsync to rebuild the standbys.
+   
+ 
+   
 If you want to use link mode and you do not want your old cluster
 to be modified when the new cluster is started, make a copy of the
 old cluster and upgrade that in link mode. To make a valid copy

-- 
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] Using pg_upgrade on log-shipping standby servers

[Magnus Hagander]

On Tue, Jul 10, 2012 at 6:59 PM, Bruce Momjian  wrote:
> On Tue, Jul 10, 2012 at 06:29:24PM +0200, Magnus Hagander wrote:
>> > Testing maybe?  I feel we have just avoided saying what you can and
>> > can't do with the standbys and pg_upgrade, so I think we have to state
>> > something.  If we just want to say "recreate", let's say that.
>>
>> Well, the bottom line is we can'd do *anything* with a standby with 
>> pg_upgrade.
>>
>> Once you've promoted it, it is no longer a standby, and now you can
>> use pg_upgrade.
>>
>> >> And I think the sentence about running rsync is extremely vague - run
>> >> rsync where and how? What are you actually trying to suggest people
>> >> do?
>> >
>> > Updated docs attached.
>>
>> I suggest just removing the rsync part completely. You're basically
>> saying "you ca nset up a new standby after you're done", which is kind
>> of obvious anyway. And if you're going to use rsync fromthe master to
>> make a new standby, there's no point in running pg_upgrade on the new
>> standby in the first place.
>
> I went the other direction and just said you can't upgrade a standby (as
> a standby), and to just use rsync --- patch attached.

Reads much better now. I'd say "use rsync to rebuild the standbys",
but that's more nitpicking :) (And maybe "the simplest way" rather
than "the simplest case"? But i'll leave that to someone who has
english as their first language)

-- 
 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] several problems in pg_receivexlog

[Fujii Masao]

On Tue, Jul 10, 2012 at 3:23 AM, Fujii Masao  wrote:
> Hi,
>
> I found several problems in pg_receivexlog, e.g., memory leaks,
> file-descripter leaks, ..etc. The attached patch fixes these problems.
>
> ISTM there are still some other problems in pg_receivexlog, so I'll
> read it deeply later.

While pg_basebackup background process is streaming WAL records,
if its replication connection is terminated (e.g., walsender in the server
is accidentally terminated by SIGTERM signal), pg_basebackup ends
up failing to include all required WAL files in the backup. The problem
is that, in this case, pg_basebackup doesn't emit any error message at all.
So an user might misunderstand that a base backup has been successfully
taken even though it doesn't include all required WAL files.

To fix this problem, I think that, when the replication connection is
terminated, ReceiveXlogStream() should check whether we've already
reached the stop point by calling stream_stop() before returning TRUE.
If we've not yet (this means that we've not received all required WAL
files yet), ReceiveXlogStream() should return FALSE and
pg_basebackup should emit an error message.  Comments?

Regards,

-- 
Fujii Masao

-- 
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_prewarm

[Josh Berkus]

On 7/10/12 5:22 AM, Dimitri Fontaine wrote:
> Jeff Janes  writes:
>> I think we want this.  There is some discussion about how much overlap
>> it has with pgfincore, but I don't think there is an active proposal
>> to put that into contrib, so don't see that as blocking this.
> 
> It is my understanding that Cédric wants to propose a patch for
> pgfincore as a contrib module in next Commit Fest, and has already been
> working on some necessary cleaning to see that happen.

Still means "not a blocker" in my book.

pgFincore, great as it is:

a) might not be ready for contrib in 9.2
b) isn't supported on all platforms
c) isn't necessarily safe in production (I've crashed Linux with Fincore
in the recent past).

As such, I see no reason why pgprewarm and pgfincore in contrib should
block each other, either way.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] Using pg_upgrade on log-shipping standby servers

[Bruce Momjian]

On Tue, Jul 10, 2012 at 06:29:24PM +0200, Magnus Hagander wrote:
> > Testing maybe?  I feel we have just avoided saying what you can and
> > can't do with the standbys and pg_upgrade, so I think we have to state
> > something.  If we just want to say "recreate", let's say that.
> 
> Well, the bottom line is we can'd do *anything* with a standby with 
> pg_upgrade.
> 
> Once you've promoted it, it is no longer a standby, and now you can
> use pg_upgrade.
> 
> >> And I think the sentence about running rsync is extremely vague - run
> >> rsync where and how? What are you actually trying to suggest people
> >> do?
> >
> > Updated docs attached.
> 
> I suggest just removing the rsync part completely. You're basically
> saying "you ca nset up a new standby after you're done", which is kind
> of obvious anyway. And if you're going to use rsync fromthe master to
> make a new standby, there's no point in running pg_upgrade on the new
> standby in the first place.

I went the other direction and just said you can't upgrade a standby (as
a standby), and to just use rsync --- patch attached.

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

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index 30f4544..4a646bd
*** a/doc/src/sgml/pgupgrade.sgml
--- b/doc/src/sgml/pgupgrade.sgml
*** psql --username postgres --file script.s
*** 518,523 
--- 518,529 

  

+A Log-Shipping Standby Server () cannot
+be upgraded because the server must allow writes.  The simplest case
+is to upgrade the primary and use rsync to upgrade the standbys.
+   
+ 
+   
 If you want to use link mode and you do not want your old cluster
 to be modified when the new cluster is started, make a copy of the
 old cluster and upgrade that in link mode. To make a valid copy

-- 
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] Synchronous Standalone Master Redoux

[Josh Berkus]

Shaun,

> Too many mental gymnastics. I get that async is "faster" than sync, but
> the inconsistent transactional state makes it *look* slower. If a
> customer makes an order, but just happens to check that order state on
> the secondary before it can catch up, that's a net loss. Like I said,
> that's fine for our DR system, or a reporting mirror, or any one of
> several use-case scenarios, but it's not good enough for a failover when
> better alternatives exist. In this case, better alternatives are
> anything that can guarantee transaction durability: DRBD / PG sync.

Per your exchange with Heikki, that's not actually how SyncRep works in
9.1.  So it's not giving you what you want anyway.

This is why we felt that the "sync rep if you can" mode was useless and
didn't accept it into 9.1.  The *only* difference between sync rep and
async rep is whether or not the master waits for ack that the standby
has written to log.

I think one of the new modes in 9.2 forces synch-to-DB before ack.  No?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] several problems in pg_receivexlog

[Fujii Masao]

On Tue, Jul 10, 2012 at 6:27 AM, Magnus Hagander  wrote:
> On Mon, Jul 9, 2012 at 8:23 PM, Fujii Masao  wrote:
>> Hi,
>>
>> I found several problems in pg_receivexlog, e.g., memory leaks,
>> file-descripter leaks, ..etc. The attached patch fixes these problems.
>
> While I don't doubt that what you've found are real problems, would
> you mind explaining exactly what they are, so we don't have to
> reverse-engineer the patch to figure that out?

Yep.

When an error happens after replication connection has been established,
pg_receivexlog doesn't close an open file descriptor and release an allocated
memory area. This was harmless before 16282ae688de2b320cf176e9be8a89e4dfc60698
because pg_receivexlog exits immediately when an error happens. But
currently in an error case, pg_receivexlog tries reconnecting to the server
infinitely, so file descriptors and memory would leak. I think this is problem
and should be fixed. The patch which I submitted yesterday changes
pg_receivexlog so that it closes the open file and frees the memory area
before reconnecting to the server.

Regards,

-- 
Fujii Masao

-- 
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] [PATCH] psql \n shortcut for set search_path =

[Tom Lane]

David Fetter  writes:
> On Tue, Jul 10, 2012 at 12:00:06PM -0400, Tom Lane wrote:
>> ISTM there was some discussion awhile back about user-definable
>> typing shortcuts in psql.

> In some sense, we already have them:

Good point:

regression=# show search_path ;
  search_path   

 "$user",public
(1 row)

regression=# \set n 'set search_path ='
regression=# :n foo;
SET
regression=# show search_path ;
 search_path 
-
 foo
(1 row)

So maybe what's needed here is a documentation example showing how you
can use a \set in ~/.psqlrc to provide this sort of functionality.

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] [PATCH] psql \n shortcut for set search_path =

[David Fetter]

On Tue, Jul 10, 2012 at 06:26:22PM +0200, Colin 't Hart wrote:
> On 10 July 2012 18:24, David Fetter  wrote:
> 
> > On Tue, Jul 10, 2012 at 12:00:06PM -0400, Tom Lane wrote:
> > > Josh Kupershmidt  writes:
> > > > On Tue, Jul 10, 2012 at 2:09 AM, Colin 't Hart 
> > wrote:
> > > >> Attached please find a trivial patch for psql which adds a \n
> > > >> meta command as a shortcut for typing set search_path =.
> > >
> > > > I think the use-case is a bit narrow: saving a few characters
> > > > typing on a command not everyone uses very often (I don't), at the
> > > > expense of adding yet another command to remember.
> > >
> > > Another point here is that we are running low on single-letter
> > > backslash command names in psql.  I'm not sure that "SET
> > > SEARCH_PATH" is so useful as to justify using up one of the ones
> > > that are left.
> > >
> > > ISTM there was some discussion awhile back about user-definable
> > > typing shortcuts in psql.
> >
> > In some sense, we already have them:
> >
> > \set FOO 'SELECT * FROM pg_stat_activity;'
> > ...
> > :FOO
> >
> > Was there more to it?
> 
> Can I pass a parameter to ":FOO" ?

That'd be the "more," I suppose.

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

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

[Magnus Hagander]

On Tue, Jul 10, 2012 at 6:26 PM, Bruce Momjian  wrote:
> On Tue, Jul 10, 2012 at 06:21:35PM +0200, Magnus Hagander wrote:
>> On Tue, Jul 10, 2012 at 6:17 PM, Bruce Momjian  wrote:
>> > On Tue, Jul 10, 2012 at 12:04:50PM -0400, Tom Lane wrote:
>> >> Bruce Momjian  writes:
>> >> > +While a Log-Shipping Standby Server (> >> > linkend="warm-standby">) can
>> >> > +be upgraded, the server must be in changed to a primary server to 
>> >> > allow
>> >> > +writes, and after the upgrade it cannot be reused as a standby 
>> >> > server.
>> >> > +(Running rsync after the upgrade allows reuse.)
>> >>
>> >> "in changed"?  This sentence makes no sense at all to me.
>> >
>> > Oops.  New wording attached with "in" removed:
>> >
>> > the server must be changed to a primary server
>>
>> Don't we normally talk about "must be promoted to a primary server",
>> not changed?
>
> OK, sure, updated patch attached.
>
>> And wouldn't it be good if it also mentions that another good option
>> is to just pg_upgrade the master and rebuild the standby? (Unless
>> that's already mentioned somewhere else).
>
> I assume they already realize they re-create the standbys.
>
>> What's the actual usecase for promoting the slave, upgrading it and
>> then *not* using it, which is what I think this paragraph suggests?
>
> Testing maybe?  I feel we have just avoided saying what you can and
> can't do with the standbys and pg_upgrade, so I think we have to state
> something.  If we just want to say "recreate", let's say that.

Well, the bottom line is we can'd do *anything* with a standby with pg_upgrade.

Once you've promoted it, it is no longer a standby, and now you can
use pg_upgrade.

>> And I think the sentence about running rsync is extremely vague - run
>> rsync where and how? What are you actually trying to suggest people
>> do?
>
> Updated docs attached.

I suggest just removing the rsync part completely. You're basically
saying "you ca nset up a new standby after you're done", which is kind
of obvious anyway. And if you're going to use rsync fromthe master to
make a new standby, there's no point in running pg_upgrade on the new
standby in the first place.

-- 
 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] Using pg_upgrade on log-shipping standby servers

[Bruce Momjian]

On Tue, Jul 10, 2012 at 06:21:35PM +0200, Magnus Hagander wrote:
> On Tue, Jul 10, 2012 at 6:17 PM, Bruce Momjian  wrote:
> > On Tue, Jul 10, 2012 at 12:04:50PM -0400, Tom Lane wrote:
> >> Bruce Momjian  writes:
> >> > +While a Log-Shipping Standby Server () 
> >> > can
> >> > +be upgraded, the server must be in changed to a primary server to 
> >> > allow
> >> > +writes, and after the upgrade it cannot be reused as a standby 
> >> > server.
> >> > +(Running rsync after the upgrade allows reuse.)
> >>
> >> "in changed"?  This sentence makes no sense at all to me.
> >
> > Oops.  New wording attached with "in" removed:
> >
> > the server must be changed to a primary server
> 
> Don't we normally talk about "must be promoted to a primary server",
> not changed?

OK, sure, updated patch attached.

> And wouldn't it be good if it also mentions that another good option
> is to just pg_upgrade the master and rebuild the standby? (Unless
> that's already mentioned somewhere else).

I assume they already realize they re-create the standbys.

> What's the actual usecase for promoting the slave, upgrading it and
> then *not* using it, which is what I think this paragraph suggests?

Testing maybe?  I feel we have just avoided saying what you can and
can't do with the standbys and pg_upgrade, so I think we have to state
something.  If we just want to say "recreate", let's say that.

> And I think the sentence about running rsync is extremely vague - run
> rsync where and how? What are you actually trying to suggest people
> do?

Updated docs attached.

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

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index 30f4544..623276a
*** a/doc/src/sgml/pgupgrade.sgml
--- b/doc/src/sgml/pgupgrade.sgml
*** psql --username postgres --file script.s
*** 518,523 
--- 518,531 

  

+While a Log-Shipping Standby Server () can
+be upgraded, the server must be promoted to a primary server to allow
+writes, and after the upgrade it cannot be reused as a standby server.
+You can run rsync after the upgrade to guarantee all the
+files are identical, and then the former standby can be reused.
+   
+ 
+   
 If you want to use link mode and you do not want your old cluster
 to be modified when the new cluster is started, make a copy of the
 old cluster and upgrade that in link mode. To make a valid copy

-- 
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] [PATCH] psql \n shortcut for set search_path =

[David Fetter]

On Tue, Jul 10, 2012 at 12:00:06PM -0400, Tom Lane wrote:
> Josh Kupershmidt  writes:
> > On Tue, Jul 10, 2012 at 2:09 AM, Colin 't Hart  wrote:
> >> Attached please find a trivial patch for psql which adds a \n
> >> meta command as a shortcut for typing set search_path =.
> 
> > I think the use-case is a bit narrow: saving a few characters
> > typing on a command not everyone uses very often (I don't), at the
> > expense of adding yet another command to remember.
> 
> Another point here is that we are running low on single-letter
> backslash command names in psql.  I'm not sure that "SET
> SEARCH_PATH" is so useful as to justify using up one of the ones
> that are left.
> 
> ISTM there was some discussion awhile back about user-definable
> typing shortcuts in psql.

In some sense, we already have them:

\set FOO 'SELECT * FROM pg_stat_activity;'
...
:FOO

Was there more to it?

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

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

[Magnus Hagander]

On Tue, Jul 10, 2012 at 6:17 PM, Bruce Momjian  wrote:
> On Tue, Jul 10, 2012 at 12:04:50PM -0400, Tom Lane wrote:
>> Bruce Momjian  writes:
>> > +While a Log-Shipping Standby Server () 
>> > can
>> > +be upgraded, the server must be in changed to a primary server to 
>> > allow
>> > +writes, and after the upgrade it cannot be reused as a standby server.
>> > +(Running rsync after the upgrade allows reuse.)
>>
>> "in changed"?  This sentence makes no sense at all to me.
>
> Oops.  New wording attached with "in" removed:
>
> the server must be changed to a primary server

Don't we normally talk about "must be promoted to a primary server",
not changed?

And wouldn't it be good if it also mentions that another good option
is to just pg_upgrade the master and rebuild the standby? (Unless
that's already mentioned somewhere else).

What's the actual usecase for promoting the slave, upgrading it and
then *not* using it, which is what I think this paragraph suggests?
And I think the sentence about running rsync is extremely vague - run
rsync where and how? What are you actually trying to suggest people
do?

-- 
 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] Using pg_upgrade on log-shipping standby servers

[Bruce Momjian]

On Tue, Jul 10, 2012 at 12:04:50PM -0400, Tom Lane wrote:
> Bruce Momjian  writes:
> > +While a Log-Shipping Standby Server () can
> > +be upgraded, the server must be in changed to a primary server to allow
> > +writes, and after the upgrade it cannot be reused as a standby server.
> > +(Running rsync after the upgrade allows reuse.)
> 
> "in changed"?  This sentence makes no sense at all to me.

Oops.  New wording attached with "in" removed:

the server must be changed to a primary server

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

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index 30f4544..04dffbf
*** a/doc/src/sgml/pgupgrade.sgml
--- b/doc/src/sgml/pgupgrade.sgml
*** psql --username postgres --file script.s
*** 518,523 
--- 518,530 

  

+While a Log-Shipping Standby Server () can
+be upgraded, the server must be changed to a primary server to allow
+writes, and after the upgrade it cannot be reused as a standby server.
+(Running rsync after the upgrade allows reuse.)
+   
+ 
+   
 If you want to use link mode and you do not want your old cluster
 to be modified when the new cluster is started, make a copy of the
 old cluster and upgrade that in link mode. To make a valid copy

-- 
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] Using pg_upgrade on log-shipping standby servers

[Tom Lane]

Bruce Momjian  writes:
> +While a Log-Shipping Standby Server () can
> +be upgraded, the server must be in changed to a primary server to allow
> +writes, and after the upgrade it cannot be reused as a standby server.
> +(Running rsync after the upgrade allows reuse.)

"in changed"?  This sentence makes no sense at all to me.

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: Allow replacement of bloated primary key indexes without foreign key rebuilds

[Tom Lane]

Alvaro Herrera  writes:
> Excerpts from Tom Lane's message of mar jul 10 10:44:03 -0400 2012:
>> What we really want is REINDEX CONCURRENTLY.

> http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.47.9961 ?

Hm ... that paper looks like something we might want to incorporate into
btree's VACUUM processing, but it's not very on-point if someone really
wants to rebuild the index totally.

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] [PATCH] psql \n shortcut for set search_path =

[Tom Lane]

Josh Kupershmidt  writes:
> On Tue, Jul 10, 2012 at 2:09 AM, Colin 't Hart  wrote:
>> Attached please find a trivial patch for psql which adds a \n meta command
>> as a shortcut for typing set search_path =.

> I think the use-case is a bit narrow: saving a few characters typing
> on a command not everyone uses very often (I don't), at the expense of
> adding yet another command to remember.

Another point here is that we are running low on single-letter backslash
command names in psql.  I'm not sure that "SET SEARCH_PATH" is so useful
as to justify using up one of the ones that are left.

ISTM there was some discussion awhile back about user-definable typing
shortcuts in psql.  I don't recall any details, but being able to set
up "SET SEARCH_PATH" as a user-definable shortcut if it's useful to you
would eliminate the question about whether it's useful to everyone.

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] Using pg_upgrade on log-shipping standby servers

[Bruce Momjian]

I occasionally get questions about how to run pg_upgrade on log-shipping
standby servers.  The attached documentation patch outlines how to do
it.

I don't think we can assume that because pg_upgrade was run on the
master and standby that they are binary identical, can we?  Technically
the user file are identical, but the system catalogs and WAL might be
different, hence my suggestion to run rsync before allowing the standby
to rejoin the primary.

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

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index 30f4544..3743599
*** a/doc/src/sgml/pgupgrade.sgml
--- b/doc/src/sgml/pgupgrade.sgml
*** psql --username postgres --file script.s
*** 518,523 
--- 518,530 

  

+While a Log-Shipping Standby Server () can
+be upgraded, the server must be in changed to a primary server to allow
+writes, and after the upgrade it cannot be reused as a standby server.
+(Running rsync after the upgrade allows reuse.)
+   
+ 
+   
 If you want to use link mode and you do not want your old cluster
 to be modified when the new cluster is started, make a copy of the
 old cluster and upgrade that in link mode. To make a valid copy

-- 
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] expression evaluation with expected datatypes

[Tom Lane]

Alvaro Herrera  writes:
> Excerpts from Tom Lane's message of mar jul 10 10:56:50 -0400 2012:
>> What's to solve?  Presumably the WITH function name would take
>> precedence over anything in the catalogs, the same as WITH query names
>> take precedence over actual tables.

> Hm, would the newly defined function mask all regular functions with
> that name?

Only the ones with the same parameter types ...

> If not, a seemingly innocuous change in a query could mean
> calling not the function defined in the WITH FUNCTION clause but another
> one with the same name but different parameter count/types.

I would see this working as if the WITH function appeared in a schema
earlier in the search path than any regular functions.  So the risk is
not greater, nor indeed different, than from any other overloaded
function name.

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] Synchronous Standalone Master Redoux

[Daniel Farina]

On Tue, Jul 10, 2012 at 6:28 AM, Shaun Thomas  wrote:
> On 07/10/2012 01:11 AM, Daniel Farina wrote:
>
>> So if I get this straight, what you are saying is "be asynchronous
>> replication unless someone is around, in which case be synchronous"
>> is the mode you want.
>
>
> Er, no. I think I see where you might have gotten that, but no.

>From your other communications, this sounds like exactly what you
want, because RAID-1 is rather like this: on writes, a degraded RAID-1
needs not wait on its (non-existent) mirror, and can be faster, but
once it has caught up it is not allowed to leave synchronization,
which is slower than writing to one disk alone, since it is the
maximum of the time taken to write to two disks.  While in the
degraded state there is effectively only one copy of the data, and
while a mirror rebuild is occurring the replication is effectively
asynchronous to bring it up to date.

-- 
fdr

-- 
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: Allow replacement of bloated primary key indexes without foreign key rebuilds

[Alvaro Herrera]

Excerpts from Tom Lane's message of mar jul 10 10:44:03 -0400 2012:

> All of these things seem like ugly, hard-to-use kluges anyway (the
> make-sure-the-indexes-match business is just as much of a PITA for the
> DBA as it is for the system).  What we really want is REINDEX
> CONCURRENTLY.

http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.47.9961 ?

-- 
Á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] Synchronous Standalone Master Redoux

[Shaun Thomas]

On 07/10/2012 09:40 AM, Heikki Linnakangas wrote:


You are mistaken. It only guarantees that it's been sync'd to disk in
the standby, but if there are open snapshots or the system is simply
busy, it might takes minutes or more until the effects of that
transaction become visible.


Well, crap. It's subtle distinctions like this I wish I'd noticed 
before. Doesn't really affect our plans, it just makes sync rep even 
less viable for our use case. Thanks for the correction! :)


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
stho...@optionshouse.com



__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email

--
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: Allow replacement of bloated primary key indexes without foreign key rebuilds

[Greg Stark]

On Tue, Jul 10, 2012 at 3:44 PM, Tom Lane  wrote:
>> The problem you describe is one of constraints and dependencies and
>> not one of indexes. It seems what you really want is a way to alter
>> foreign key dependencies to depend on a new index. Either an explicit
>> command that lets you set the new dependency or what seems even better
>> would be to have DROP INDEX check any dependent objects to see if
>> there's another index that can satisfy them and change their
>> dependency.
>
> Either of these have exactly the same issue, namely their correctness
> depends on determining if two indexes have identical properties.

This doesn't sound right to me. In these cases all it would have to
know about is the same set of properties that CREATE CONSTRAINT looks
for to find a satisfactory index to depend on.

-- 
greg

-- 
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] expression evaluation with expected datatypes

[Alvaro Herrera]

Excerpts from Tom Lane's message of mar jul 10 10:56:50 -0400 2012:
> Pavel Stehule  writes:
> > 2012/7/10 Dimitri Fontaine :
> >> I'm not sure I can understand the difference between that and the use
> >> case for which you want to implement DO blocks with parameters.
> 
> > this is similar to temporary functions - you need some temporary name
> > - it is insert to pg_proc, and you have to solve possible conflicts.
> 
> What's to solve?  Presumably the WITH function name would take
> precedence over anything in the catalogs, the same as WITH query names
> take precedence over actual tables.

Hm, would the newly defined function mask all regular functions with
that name?  If not, a seemingly innocuous change in a query could mean
calling not the function defined in the WITH FUNCTION clause but another
one with the same name but different parameter count/types.

-- 
Á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] [PATCH] psql \n shortcut for set search_path =

[Josh Kupershmidt]

On Tue, Jul 10, 2012 at 2:09 AM, Colin 't Hart  wrote:
> Attached please find a trivial patch for psql which adds a \n meta command
> as a shortcut for typing set search_path =.

I think the use-case is a bit narrow: saving a few characters typing
on a command not everyone uses very often (I don't), at the expense of
adding yet another command to remember. Plus it opens the floodgates
to people wanting yet more separate commands for other possibly
commonly-used SET commands. There are a lot of GUCs, after all, even
counting only those changeable at runtime.

Josh

-- 
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] expression evaluation with expected datatypes

[Tom Lane]

Pavel Stehule  writes:
> 2012/7/10 Dimitri Fontaine :
>> I'm not sure I can understand the difference between that and the use
>> case for which you want to implement DO blocks with parameters.

> this is similar to temporary functions - you need some temporary name
> - it is insert to pg_proc, and you have to solve possible conflicts.

What's to solve?  Presumably the WITH function name would take
precedence over anything in the catalogs, the same as WITH query names
take precedence over actual tables.

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] expression evaluation with expected datatypes

[Pavel Stehule]

2012/7/10 Dimitri Fontaine :
> Pavel Stehule  writes:
   WITH FUNCTION foo(param list) returns rettype language foo AS (
 definition here
   )
   ;
>>>
>>> I like this idea.  This gets rid of both the "how to pass parameters"
>>> and the "how to return results" issues that exist with DO, as well as
>>> assorted implementation problems that you hinted at by asking whether
>>> DO would still be a utility command.
>>
>> what is use case for this statement?
>
> It's the DO block idea turned into a query rather than a utility
> command: you can now run a function that does not exists in the catalogs
> *and* feed it parameters (either from the client, as literals in the
> main query, or from the query itself) *and* you get a query result our
> of it.
>
> I'm not sure I can understand the difference between that and the use
> case for which you want to implement DO blocks with parameters.

this is similar to temporary functions - you need some temporary name
- it is insert to pg_proc, and you have to solve possible conflicts.



>
> Regards,
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] expression evaluation with expected datatypes

[Dimitri Fontaine]

Pavel Stehule  writes:
>>>   WITH FUNCTION foo(param list) returns rettype language foo AS (
>>> definition here
>>>   )
>>>   ;
>>
>> I like this idea.  This gets rid of both the "how to pass parameters"
>> and the "how to return results" issues that exist with DO, as well as
>> assorted implementation problems that you hinted at by asking whether
>> DO would still be a utility command.
>
> what is use case for this statement?

It's the DO block idea turned into a query rather than a utility
command: you can now run a function that does not exists in the catalogs
*and* feed it parameters (either from the client, as literals in the
main query, or from the query itself) *and* you get a query result our
of it.

I'm not sure I can understand the difference between that and the use
case for which you want to implement DO blocks with parameters.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] Re: Allow replacement of bloated primary key indexes without foreign key rebuilds

[Tom Lane]

Greg Stark  writes:
> On Sat, Jul 7, 2012 at 4:53 AM, Gurjeet Singh  wrote:
>> All we need to do is allow swapping of pg_class.relfilenode of two indexes.

> Fwiw I don't like swapping relfilenodes on indexes the user created.
> REINDEX currently does this but it's a bit of a hack and only works
> because reindex carefully builds the new index with exactly the same
> definition as the old one.

Yes.  The swap-relfilenodes operation would have to carefully check that
the index definitions were exactly equivalent, and there would be a
constant risk for bugs of omission if that code weren't taught about
any new index properties we invent.

> The problem you describe is one of constraints and dependencies and
> not one of indexes. It seems what you really want is a way to alter
> foreign key dependencies to depend on a new index. Either an explicit
> command that lets you set the new dependency or what seems even better
> would be to have DROP INDEX check any dependent objects to see if
> there's another index that can satisfy them and change their
> dependency.

Either of these have exactly the same issue, namely their correctness
depends on determining if two indexes have identical properties.

All of these things seem like ugly, hard-to-use kluges anyway (the
make-sure-the-indexes-match business is just as much of a PITA for the
DBA as it is for the system).  What we really want is REINDEX
CONCURRENTLY.

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] Synchronous Standalone Master Redoux

[Heikki Linnakangas]

On 10.07.2012 17:31, Shaun Thomas wrote:

On 07/09/2012 05:15 PM, Josh Berkus wrote:

So I'm unclear on why sync rep would be faster than async rep given
that they use exactly the same mechanism. Explain?


Too many mental gymnastics. I get that async is "faster" than sync, but
the inconsistent transactional state makes it *look* slower. If a
customer makes an order, but just happens to check that order state on
the secondary before it can catch up, that's a net loss. Like I said,
that's fine for our DR system, or a reporting mirror, or any one of
several use-case scenarios, but it's not good enough for a failover when
better alternatives exist. In this case, better alternatives are
anything that can guarantee transaction durability: DRBD / PG sync.

PG sync mode does what I want in that regard, it just has no graceful
failure state without relatively invasive intervention.


You are mistaken. PostgreSQL's synchronous replication does not 
guarantee that the transaction is immediately replayed in the standby. 
It only guarantees that it's been sync'd to disk in the standby, but if 
there are open snapshots or the system is simply busy, it might takes 
minutes or more until the effects of that transaction become visible.


I agree that such a mode would be highly useful, where a transaction is 
not acknowledged to the client as committed until it's been replicated 
*and* replayed in the standby. And in that mode, a timeout after which 
the master just goes ahead without the standby would be useful. You 
could then configure your middleware and/or standby to not use the 
standby server for queries after that timeout.


--
  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] expression evaluation with expected datatypes

[Pavel Stehule]

2012/7/10 Tom Lane :
> Dimitri Fontaine  writes:
>> We could imagine several syntax to show up the idea, common keywords
>> here include LAMBDA, FLET or LABELS, but I think that expanding WITH
>> would be preferable for us.
>
>>   WITH FUNCTION foo(param list) returns rettype language foo AS (
>> definition here
>>   )
>>   ;
>
> I like this idea.  This gets rid of both the "how to pass parameters"
> and the "how to return results" issues that exist with DO, as well as
> assorted implementation problems that you hinted at by asking whether
> DO would still be a utility command.

what is use case for this statement?

Regards

Pavel

>
> In the syntax-bikeshedding department, we'd still need the function body
> to be a string literal, and I think we'd want the ability to add
> options such as IMMUTABLE/VOLATILE.  So I'd be inclined to move all
> these options inside the parentheses that the WITH syntax dictates.
> Perhaps
>
> WITH FUNCTION foo(paramlist) AS (
> returns int
> as $$ ... $$
> language plpgsql
> ... other CREATE FUNCTION options as needed ...
> )
> query here ...
>
> 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] Synchronous Standalone Master Redoux

[Shaun Thomas]

On 07/09/2012 05:15 PM, Josh Berkus wrote:



"Total-consistency" replication is what I think you want, that is, to
guarantee that at any given time a read query on the master will return
the same results as a read query on the standby.  Heck, *most* people
would like to have that.  You would also be advancing database science
in general if you could come up with a way to implement it.


Doesn't having consistent transactional state across the systems imply that?


So I'm unclear on why sync rep would be faster than async rep given
that they use exactly the same mechanism.  Explain?


Too many mental gymnastics. I get that async is "faster" than sync, but 
the inconsistent transactional state makes it *look* slower. If a 
customer makes an order, but just happens to check that order state on 
the secondary before it can catch up, that's a net loss. Like I said, 
that's fine for our DR system, or a reporting mirror, or any one of 
several use-case scenarios, but it's not good enough for a failover when 
better alternatives exist. In this case, better alternatives are 
anything that can guarantee transaction durability: DRBD / PG sync.


PG sync mode does what I want in that regard, it just has no graceful 
failure state without relatively invasive intervention. Theoretically we 
could write a Pacemaker agent, or some other simple harness, that just 
monitors both servers and performs an LSB HUP after modifying the 
primary node to disable synchronous_standby_names if the secondary dies, 
or promotes the secondary if the primary dies. But after being spoiled 
by DRBD knowing the instant the secondary disconnects, but still being 
available until the secondary is restored, we can't justifiably switch 
to something that will have the primary hang for ten seconds between 
monitor checks and service reloads.


I'm just saying I considered it briefly during testing the last few 
days, but there's no way I can make a business case for it. PG sync rep 
is a great step forward, but it's not for us. Yet.


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
stho...@optionshouse.com



__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email

--
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] expression evaluation with expected datatypes

[Tom Lane]

Dimitri Fontaine  writes:
> We could imagine several syntax to show up the idea, common keywords
> here include LAMBDA, FLET or LABELS, but I think that expanding WITH
> would be preferable for us.

>   WITH FUNCTION foo(param list) returns rettype language foo AS (
> definition here
>   )
>   ;

I like this idea.  This gets rid of both the "how to pass parameters"
and the "how to return results" issues that exist with DO, as well as
assorted implementation problems that you hinted at by asking whether
DO would still be a utility command.

In the syntax-bikeshedding department, we'd still need the function body
to be a string literal, and I think we'd want the ability to add
options such as IMMUTABLE/VOLATILE.  So I'd be inclined to move all
these options inside the parentheses that the WITH syntax dictates.
Perhaps

WITH FUNCTION foo(paramlist) AS (
returns int
as $$ ... $$
language plpgsql
... other CREATE FUNCTION options as needed ...
)
query here ...

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] Re: Allow replacement of bloated primary key indexes without foreign key rebuilds

[Greg Stark]

On Sat, Jul 7, 2012 at 4:53 AM, Gurjeet Singh  wrote:
> All we need to do is allow swapping of pg_class.relfilenode of two indexes.
> This will let the dependency entries stand as they are and allow us to drop
> the bloated primary key index structure without having to rebuild the
> foreign key constraints.

Fwiw I don't like swapping relfilenodes on indexes the user created.
REINDEX currently does this but it's a bit of a hack and only works
because reindex carefully builds the new index with exactly the same
definition as the old one.

The problem you describe is one of constraints and dependencies and
not one of indexes. It seems what you really want is a way to alter
foreign key dependencies to depend on a new index. Either an explicit
command that lets you set the new dependency or what seems even better
would be to have DROP INDEX check any dependent objects to see if
there's another index that can satisfy them and change their
dependency.

These might suffer from deadlock problems but hopefully they could be
manageable since it's not a frequent operation and there aren't any
other operations that rejigger dependencies.

-- 
greg

-- 
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] autocomplete - SELECT fx

[Josh Kupershmidt]

On Sat, Jul 7, 2012 at 5:43 PM, Noah Misch  wrote:
> I like the patch, as far as it goes.  It's the natural addition to the
> completions we already offer; compare the simplistic completion after WHERE.
> Like Pavel and Robert, I think a delightful implementation of tab completion
> for SELECT statements would require radical change.

Thanks for the feedback, Noah. Peter, are you interested in posting an
updated version of your patch? (The only problems I remember are
checking attisdropped and function visibility.)

Josh

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

[HACKERS] [PATCH] psql \n shortcut for set search_path =

[Colin 't Hart]

Hi,

Attached please find a trivial patch for psql which adds a \n meta command
as a shortcut for typing set search_path =.

This allows you to use psql as follows:

\dn

\n my_schema

\d

\d my_table

etc.


Not yet done: updating documentation (psql internal help, psql man page,
main documentation).

If this is something that is desired (I hope so as this is something I now
use a lot), I will update the documentation and resubmit.


Cheers,

Colin


psql_slash_n.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] Event Triggers reduced, v1

[Dimitri Fontaine]

Thom Brown  writes:
> I also attach various typo/grammar fixes.

In fact Robert's cleanup of the docs make that patch of yours not apply
anymore, and I think a part of it is maybe already fixed. Do you have
time to look at this with the new v1.8 patch that you will receive in a
minute, or with the github branch if you're tracking that?

Sorry about that.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] Synchronous Standalone Master Redoux

[Aidan Van Dyk]

On Tue, Jul 10, 2012 at 9:28 AM, Shaun Thomas  wrote:

> Async is simply too slow for our OLTP system except for the disaster
> recovery node, which isn't expected to carry on within seconds of the
> primary's failure. I briefly considered sync mode when it appeared as a
> feature, but I see it's still too early in its development cycle, because
> there are no degraded operation modes. That's fine, I'm willing to wait.

But this is where some of us are confused with what your asking for.
async is actually *FASTER* than sync.  It's got less over head.
Synchrounous replication is basicaly async replication, with an extra
overhead, and an artificial delay on the master for the commit to
*RETURN* to the client.  The data is still committed and view able to
new queries on the master, and the slave at the same rate as with
async replication.  Just that the commit status returned to the client
is delayed.

So the "async is too slow" is what we don't understand.

> I just don't understand the push-back, I guess. RAID-1 is the poster child
> for synchronous writes for fault tolerance. It will whine constantly to
> anyone who will listen when operating only on one device, but at least it
> still works. I'm pretty sure nobody would use RAID-1 if its failure mode
> was: block writes until someone installs a replacement disk.

I think most of us in the "synchronous replication must be syncronous
replication" camp are there because the guarantees of a simple RAID 1
just isn't good enough for us ;-)

a.

-- 
Aidan Van Dyk Create like a god,
ai...@highrise.ca   command like a king,
http://www.highrise.ca/   work like a slave.

-- 
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] Synchronous Standalone Master Redoux

[Shaun Thomas]

On 07/10/2012 01:11 AM, Daniel Farina wrote:


So if I get this straight, what you are saying is "be asynchronous
replication unless someone is around, in which case be synchronous"
is the mode you want.


Er, no. I think I see where you might have gotten that, but no.


This is a pretty tricky definition: consider if you bring a standby
on-line from archive replay and it shows up in streaming with pretty
high lag, and stops all commit traffic while it reaches the bounded
window of what "acceptable" lag is. That sounds pretty terrible, too.
How does DBRD handle this? It seems like the catchup phase might be
interesting prior art.


Well, DRBD actually has a very definitive sync mode, and no 
"attenuation" is involved at all. Here's what a fully working cluster 
looks like, according to /proc/drbd:


cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate

Here's what happens when I disconnect the secondary:

cs:WFConnection ro:Primary/Unknown ds:UpToDate/DUnknown

So there's a few things here:

1. Primary is waiting for the secondary to reconnect.
2. It knows its own data is still up to date.
3. It's waiting to assess the secondary when it re-appears
4. It's still capable of writing to the device.

This is more akin to degraded RAID-1. Writes are synchronous as long as 
two devices exist, but if one vanishes, you can still use the disk at 
your own risk. Checking the status of DRBD will show this readily. I 
also want to point out it is *fully* synchronous when both nodes are 
available. I.e., you can't even call a filesystem sync without the sync 
succeeding on both nodes.


When you re-connect a secondary device, it catches up as fast as 
possible by replaying waiting transactions, and then re-attaching to the 
cluster. Until it's fully caught-up, it doesn't exist. DRBD acknowledges 
the secondary is there and attempting to catch up, but does not leave 
"degraded" mode until the secondary reaches "UpToDate" status.


This is a much more graceful failure scenario than is currently possible 
with PostgreSQL. With DRBD, you'd still need a tool to notice the master 
node is in an invalid state and perform a failover, but the secondary 
going belly-up will not suddenly halt the master.


But I'm not even hoping for *that* level of functionality. I just want 
to be able to tell PostgreSQL to notice when the secondary becomes 
unavailable *on its own*, and then perform in "degraded non-sync mode" 
because it's much faster than any monitor I can possibly attach to 
perform the same function. I plan on using DRBD until either PG can do 
that, or a better alternative presents itself.


Async is simply too slow for our OLTP system except for the disaster 
recovery node, which isn't expected to carry on within seconds of the 
primary's failure. I briefly considered sync mode when it appeared as a 
feature, but I see it's still too early in its development cycle, 
because there are no degraded operation modes. That's fine, I'm willing 
to wait.


I just don't understand the push-back, I guess. RAID-1 is the poster 
child for synchronous writes for fault tolerance. It will whine 
constantly to anyone who will listen when operating only on one device, 
but at least it still works. I'm pretty sure nobody would use RAID-1 if 
its failure mode was: block writes until someone installs a replacement 
disk.


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
stho...@optionshouse.com



__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email

--
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 rsync for data directory copying

[Bruce Momjian]

On Mon, Jul  9, 2012 at 08:00:48PM -0700, David Kerr wrote:
> 
> On Jul 9, 2012, at 7:48 PM, Bruce Momjian wrote:
> 
> > Rsync is popular with Postgres users, but I don't understand how they
> > are using the default check mode (file size, modification time) to
> > synchronize shut-down data directories?  It seems they would have to use
> > --checksum because it is too easy for files to change in the same
> > second, and for a backend to write into the middle of a file.
> > 
> > Is everyone who is using rsync with Postgres also using --checksum mode?
> 
> 
> I must be missing something, if they're shut down you can't write to them. =)
> 
> I do use rsync though for resyncing my mirror's, it's been working great so 
> far. I assume
> that the WAL fixes anything that gets goofed up in the copy. (hopefully I've 
> been assuming correctly.)

If two writes happens in the middle of a file in the same second, it
seems one might be missed.  Yes, I suppose the WAL does fix that during
replay, though if both servers were shut down cleanly, WAL would not be
replayed.

If you using it for a hot backup, and WAL would clean that up.

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

[HACKERS] BlockNumber initialized to InvalidBuffer?

[Markus Wanner]

Hackers,

I stumbled across an initialization of a BlockNumber with InvalidBuffer,
which seems strange to me, as the values for "invalid" of the two types
are different, see attached patch.

In case the 'stack' argument passed to that function is not NULL, the
variable in question gets overridden immediately, in which case it
certainly doesn't matter. I don't know nor did I check whether or not it
can ever be NULL. So this might not be a real issue at all.

Regards

Markus Wanner
# InvalidBlockNumber is -1 (or rather 0x), while
# the currently used InvalidBuffer is 0, which is a valid
# BlockNumber.

*** src/backend/access/gin/ginbtree.c	2d3e63387737b4034fc25ca3cb128d9ac57f4f01
--- src/backend/access/gin/ginbtree.c	67351e1b6541b25ab3c8e8dc7a57487c2422e124
*** ginInsertValue(GinBtree btree, GinBtreeS
*** 276,282 
  ginInsertValue(GinBtree btree, GinBtreeStack *stack, GinStatsData *buildStats)
  {
  	GinBtreeStack *parent = stack;
! 	BlockNumber rootBlkno = InvalidBuffer;
  	Page		page,
  rpage,
  lpage;
--- 276,282 
  ginInsertValue(GinBtree btree, GinBtreeStack *stack, GinStatsData *buildStats)
  {
  	GinBtreeStack *parent = stack;
! 	BlockNumber rootBlkno = InvalidBlockNumber;
  	Page		page,
  rpage,
  lpage;


-- 
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] expression evaluation with expected datatypes

[Pavel Stehule]

2012/7/10 Dimitri Fontaine :
> Pavel Stehule  writes:
>> When I worked on parametrised DO statement, I had to solve following issue:
>
> DO currently is a utility command, not a query. Do you mean to change
> that?
>
> Also, did you think about a lambda construct, which is basically
> allowing functions to be defined inline in a query?
>
> We could imagine several syntax to show up the idea, common keywords
> here include LAMBDA, FLET or LABELS, but I think that expanding WITH
> would be preferable for us.
>
>   WITH FUNCTION foo(param list) returns rettype language foo AS (
> definition here
>   )
>   ;
>
> Other WITH extensions we can think about include support for DCL as
> asked by David Fetter in the past already, and support for variables too
> (a kind of per-query SET LOCAL).
>
> I don't see how adding parameters and return values to utility commands
> is going to be easier than adding a "lambda" facility.

I don't think so we need true LAMBDA - we don't need support for
recursion and we don't need to modify system tables.

I don't see any advantage and usage of this complex syntax

Regards

Pavel

>
> Regards,
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] pg_prewarm

[Dimitri Fontaine]

Jeff Janes  writes:
> I think we want this.  There is some discussion about how much overlap
> it has with pgfincore, but I don't think there is an active proposal
> to put that into contrib, so don't see that as blocking this.

It is my understanding that Cédric wants to propose a patch for
pgfincore as a contrib module in next Commit Fest, and has already been
working on some necessary cleaning to see that happen.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] expression evaluation with expected datatypes

[Dimitri Fontaine]

Pavel Stehule  writes:
> When I worked on parametrised DO statement, I had to solve following issue:

DO currently is a utility command, not a query. Do you mean to change
that?

Also, did you think about a lambda construct, which is basically
allowing functions to be defined inline in a query?

We could imagine several syntax to show up the idea, common keywords
here include LAMBDA, FLET or LABELS, but I think that expanding WITH
would be preferable for us.

  WITH FUNCTION foo(param list) returns rettype language foo AS (
definition here
  )
  ;

Other WITH extensions we can think about include support for DCL as
asked by David Fetter in the past already, and support for variables too
(a kind of per-query SET LOCAL).

I don't see how adding parameters and return values to utility commands
is going to be easier than adding a "lambda" facility.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] [PATCH] psql \n shortcut for set search_path =

[Colin 't Hart]

Hi,

Attached please find a trivial patch for psql which adds a \n meta command
as a shortcut for typing set search_path =.

This allows you to navigate a database very quickly in psql as follows:

\dn

\n my_schema

\d

\d my_table

etc.


Not yet done: updating documentation (psql internal help, psql man page,
main documentation).

If this is something that is desired (I hope so as this is something I now
use a lot), I will update the documentation and resubmit.


Cheers,

Colin


psql_slash_n.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] Testing of various opclasses for ranges

[Heikki Linnakangas]

On 10.07.2012 02:33, Alexander Korotkov wrote:

Hackers,

I've tested various opclasses for ranges (including currently in-core one
and my patches). I've looked into scholar papers for which datasets they
are using for testing. The lists below show kinds of datasets used in
papers.


Great! That's a pretty comprehensive suite of datasets.


I've merged all 3 patches into 1 (see 2d_map_range_indexing.patch). In this
patch following opclasses are available for ranges:
1) range_ops - currently in-core GiST opclass
2) range_ops2 - GiST opclass based on 2d-mapping
3) range_ops_quad - SP-GiST quad tree based opclass
4) range_ops_kd - SP-GiST k-d tree based opclass


I think the ultimate question is, which ones of these should we include 
in core? We cannot drop the existing range_ops opclass, if only because 
that would break pg_upgrade. However, range_ops2 seems superior to it, 
so I think we should make that the default for new indexes.


For SP-GiST, I don't think we need to include both quad and k-d tree 
implementations. They have quite similar characteristics, so IMHO we 
should just pick one. Which one would you prefer? Is there any 
difference in terms of code complexity between them? Looking at the 
performance test results, quad tree seems to be somewhat slower to 
build, but is faster to query. Based on that, I think we should pick the 
quad tree, query performance seems more important.


--
  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] has_language_privilege returns incorrect answer for non-superuser

[Magnus Hagander]

On Tue, Jul 10, 2012 at 3:23 AM, Joe Conway  wrote:
> I noticed today that has_language_privilege() returns incorrect answer
> for non-superuser, e.g.:
>
> 8<---
> select has_language_privilege('nobody',
>   'plperlu',
>   'usage');
>  has_language_privilege
> 
>  t
> (1 row)
>
> test1=# \c - nobody
> You are now connected to database "test1" as user "nobody".
>
> create function f() returns text as $$ $$ language plperlu;
> ERROR:  permission denied for language plperlu
> 8<---
>
> I verified this behavior on head as well as 9.1 (didn't bother looking
> any further back). Looks like the reason is that CreateFunction()
> correctly checks lanpltrusted, whereas pg_language_aclmask() does not.
>
> Seems like a bug to me -- opinions?


Definitely seems like a bug to me, yes.

And while I haven't verified that the suggested fix actually fixes it
for me, it sounds reasonable :)

-- 
 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] Synchronous Standalone Master Redoux

[Magnus Hagander]

On Tue, Jul 10, 2012 at 8:42 AM, Amit Kapila  wrote:
>> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Daniel Farina
>> Sent: Tuesday, July 10, 2012 11:42 AM
>>>On Mon, Jul 9, 2012 at 1:30 PM, Shaun Thomas 
> wrote:
>>>
>>> 1. Slave wants to be synchronous with master. Master wants replication on
> at least one slave. They have this, and are happy.
>>> 2. For whatever reason, slave crashes or becomes unavailable.
>>> 3. Master notices no more slaves are available, and operates in
> standalone mode, accumulating WAL files until a suitable slave appears.
>>> 4. Slave finishes rebooting/rebuilding/upgrading/whatever, and
> re-subscribes to the feed.
>>> 5. Slave stays in degraded sync (asynchronous) mode until it is caught
> up, and then switches to synchronous. This makes both master and slave
> happy, because *intent* of synchronous replication is fulfilled.
>>>
>
>> So if I get this straight, what you are saying is "be asynchronous
>> replication unless someone is around, in which case be synchronous" is
>> the mode you want.  I think if your goal is zero-transaction loss then
>> you would want to rethink this, and that was the goal of SR: two
>> copies, no matter what, before COMMIT returns from the primary.
>
> For such cases, can there be a way with which an option can be provided to
> user if he wants to change mode to async?

You can already change synchronous_standby_names, and do so without a
restart. That will change between sync and async just fine on a live
system. And you can control that from some external monitor to define
your own rules for exactly when it should drop to async mode.

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

[HACKERS] HTTP API experimental implementation

[Björn Harrtell]

Hey all,

I've begun an implementation of the proposed HTTP API [1] (with some
changes) using node.js

The project lives at
https://github.com/bjornharrtell/postgresql-http-server and
basic functionality is in place.

Feedback appriciated!

[1] http://wiki.postgresql.org/wiki/HTTP_API

Regards

/Björn Harrtell