Re: [HACKERS] functional call named notation clashes with SQL feature

[Heikki Linnakangas]

On 27/05/10 22:55, Tom Lane wrote:

Peter Eisentraut  writes:

How about
select myfunc(a := 7, b := 6);
?


Hey, that's a thought.  We couldn't have used that notation before
because we didn't have := as a separate token, but since I hacked that
in for plpgsql's benefit, I think it might be an easy fix.  It'd be
nice that it puts the argument name first like the spec syntax, too.


If we go with that, should we make some preparations to allow => in the 
future? Like provide an alternative operator name for hstore's =>, and 
add a note somewhere in the docs to discourage other modules from using =>.


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

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

[HACKERS] Patch submission deadline for CommitFest 2010-07

[Fujii Masao]

Hi,

When is the patch submission deadline for CommitFest 2010-07?
July 14? or June 14 (before review fest)? Sorry, I'm not sure
what is actually different between CF and RF.

Regards,

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

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

Re: [HACKERS] pg_trgm

[Peter Eisentraut]

On fre, 2010-05-28 at 10:04 +0900, Tatsuo Ishii wrote:
> > I think the problem at hand has nothing at all to do with agglutination
> > or CJK-specific issues.  You will get the same problem with other
> > languages *if* you set a locale that does not adequately support the
> > characters in use.  E.g., Russian with locale C and encoding UTF8:
> > 
> > select similarity(E'\u0441\u043B\u043E\u043D', E'\u0441\u043B\u043E
> > \u043D\u044B');
> >  similarity
> > 
> > NaN
> > (1 row)
> 
> Wait. This works fine for me with stock pg_trgm. local is C and
> encoding is UTF8. What version of PostgreSQL are you using? Mine is
> 8.4.4.

This is in 9.0, because 8.4 doesn't recognize the \u escape syntax.  If
you run this in 8.4, you're just comparing a sequence of ASCII letters
and digits.


-- 
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] Streaming Replication: Checkpoint_segment and wal_keep_segments on standby

[Sander, Ingo (NSN - DE/Munich)]

Both nodes (active and standby) have the same configuration parameters. 
The observed effect happens too if the checkpoint timeout is decreaased.

The problem seems to be that on standby no checkpoints are written and 
only the chekpoint_timeout mechanism is active

Regards
Ingo

-Original Message-
From: ext Fujii Masao [mailto:masao.fu...@gmail.com] 
Sent: Thursday, May 27, 2010 4:10 PM
To: Sander, Ingo (NSN - DE/Munich)
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Streaming Replication: Checkpoint_segment and
wal_keep_segments on standby

On Thu, May 27, 2010 at 10:13 PM, Sander, Ingo (NSN - DE/Munich)
 wrote:
>
> With the parameter checkpoint_segment and wal_keep_segments the max.
number
> of wal segments are set. If now the max number is reached,
>
> (1) the segments are deleted/recycled
> or (2) if the time set by the checkpoint_timeout is over, a checkpoint
is
> set and if possible a deletion/recycling is done.
>
> This is the mechanism on the active side of a db server. On the
standby side
> however only unused tranferred segments will be deleted if the
> checkpoint_timeout mechanism (2) is executed.
>
> Is this a correct behaviour or it is an error?
>
> I have observed (checkpoint_segment set to 3; wal_keep_segments set to
10
> and checkpoint_timeout set to 30min) that in my stress test the disk
usage
> on standby side is increased up to 2GB with xlog segments whereby on
the
> active side only ~60MB xlog files are available (we have patched the
xlog
> file size to 4MB). To prevent this one possibility is to decreace the
> checkpoint_timeout to a low value (30sec), however this had the
disadvantage
> that a checkpoint is often executed on active side which can influence
the
> performance. Another possibility is to have different postgresql.conf
on
> active and on standby side, but this is not our preferred solution.

I guess this happens because the frequency of checkpoint on the standby
is
too lower than that on the master. In the master, checkpoint occurs for
every
consumption of three segments because of "checkpoint_segments = 3". On
the
other hand, in the standby, only checkpoint_timeout has effect, so
checkpoint
occurs for every 30 minutes because of "checkpoint_timeout = 30min".

The walreceiver should signal the bgwriter to start checkpoint if it has
received more than checkpoint_segments WAL files, like normal
processing?

Regards,

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

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

[HACKERS] Working with PostgreSQL enums in C code

[Joseph Adams]

I encountered a situation while implementing JSON support where I
needed to return an enum value from a C function.  To clarify, here's
the SQL:

CREATE TYPE json_type_t AS ENUM ('null', 'string', 'number', 'bool',
'object', 'array');

CREATE OR REPLACE FUNCTION json_type(json)
RETURNS json_type_t
AS 'MODULE_PATHNAME','json_get_type'
LANGUAGE C STRICT IMMUTABLE;

I initially tried looking for another function returning an enum in
the PostgreSQL source tree, but I couldn't find any.  I guess this is
because enums are a relatively new feature in PostgreSQL.

I learned that to return an enum value from C, one needs to return the
OID of the right row of the pg_enum table.  I eventually managed to
write the code below, which is mostly based on the enum_in function in
src/backend/utils/adt/enum.c .

#define PG_RETURN_ENUM(typname, label) return enumLabelToOid(typname, label)

static Oid enumLabelToOid(const char *typname, const char *label)
{
Oid enumtypoid;
HeapTuple   tup;
Oid ret;

enumtypoid = TypenameGetTypid(typname);
Assert(OidIsValid(enumtypoid));

tup = SearchSysCache2(ENUMTYPOIDNAME,
  ObjectIdGetDatum(enumtypoid),
  CStringGetDatum(label));
Assert(HeapTupleIsValid(tup));

ret = HeapTupleGetOid(tup);

ReleaseSysCache(tup);

return ret;
}

Feel free to nitpick the code above, as I'm still learning.  Note that
I replaced the more robust validity checks of enum_in with (quicker?)
asserts, with the assumption that correct programs would only pass
valid values to PG_RETURN_ENUM .

The code using the method above can be found here:
http://git.postgresql.org/gitweb?p=json-datatype.git;a=tree;f=contrib/json;h=1dd813da4016b31f35cb39b01c6d5f0999da672e;hb=092fa046f95580dd7906a07370ca401692a1f818
.  My testcases passed, so everything seems to work.

I suppose my PG_RETURN_ENUM macro is nice and simple, except for the
fact that the coder has to keep an enum names table in sync with the
SQL code and the C code.  However, going the other way around
(PG_GETARG_ENUM) would need access to that enum names table.  Hence,
it'd make sense to have macros for defining this table so both
PG_RETURN_ENUM and PG_GETARG_ENUM can reference it.

I believe that these macros would be a useful addition to the
PostgreSQL function manager API, as they would provide a decent way to
receive and return custom enums from C code.  Anyone agree/disagree?


Joey Adams

-- 
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] functional call named notation clashes with SQL feature

[Tom Lane]

Andrew Dunstan  writes:
> Bruce Momjian wrote:
>> One concern I have is that in PL/pgSQL, := and = behave the same, while
>> in SQL, they would not.  That might cause confusion.

> I doubt there will be much confusion.

I agree.  Bruce is ignoring the fact that they are *not* interchangeable
even in plpgsql, except in the one context of the assignment operator.
If you try to use := in a SQL construct it won't work, eg
if (a := b) then ...
if (a = b) then ...
have never been equivalent.

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] functional call named notation clashes with SQL feature

[Andrew Dunstan]

Bruce Momjian wrote:

Peter Eisentraut wrote:
  

On tor, 2010-05-27 at 12:59 -0400, Tom Lane wrote:


I think we should fix it now.  Quick thought: maybe we could use

FOR 
  

instead of AS: select myfunc(7 for a, 6 for b);


I'm afraid FOR doesn't work either; it'll create a conflict with the
spec-defined SUBSTRING(x FOR y) syntax.
  

How about

select myfunc(a := 7, b := 6);



One concern I have is that in PL/pgSQL, := and = behave the same, while
in SQL, they would not.  That might cause confusion.

  


That is a sad wart that we should never have done, IMNSHO (it was before 
my time or I would have objected ;-) ). But beyond that, = is an 
operator in SQL and := is never an operator, IIRC.


cheers

andrew

I doubt there will be much confusion.

--
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] mingw initdb failure on HEAD

[Takahiro Itagaki]

Andrew Dunstan  wrote:

> Several buildfarm mingw members are getting failures like this, when 
> running initdb:
> 
> 
> Could it have been caused by the PGDLLIMPORT/PGDLLEXPORT changes?

Probably, but it's curious because MSVC members are OK.
Do we have special treatments for exported functions in mingw?
It might export 'dllimport' funtions/variables, but not 'dllexport' ones.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center



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

Re: [HACKERS] functional call named notation clashes with SQL feature

[Bruce Momjian]

Peter Eisentraut wrote:
> On tor, 2010-05-27 at 12:59 -0400, Tom Lane wrote:
> > > I think we should fix it now.  Quick thought: maybe we could use
> > FOR 
> > > instead of AS: select myfunc(7 for a, 6 for b);
> > 
> > I'm afraid FOR doesn't work either; it'll create a conflict with the
> > spec-defined SUBSTRING(x FOR y) syntax.
> 
> How about
> 
> select myfunc(a := 7, b := 6);

One concern I have is that in PL/pgSQL, := and = behave the same, while
in SQL, they would not.  That might cause confusion.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://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] Why my manualy constructed raw parser tree produce failed to execute?

[Mohammad Heykal Abdillah]

On Jum, 2010-05-28 at 08:55 +0900, Tatsuo Ishii wrote: 
> > > Now to the question, why my manualy constructed list was failed to
> > > execute? I was pretty sure that my list node was identical with yacc.
> > 
> > Because you have a bug in your code.
> 
> You can debug your code by comparing your hand made tree with the
> original tree by using equal(). Search #ifdef COPY_PARSE_PLAN_TREES in
> postgres.c to see how to use it. Good luck.

Thank you Mr. Tatsuo and Mr. Robert for the hint, i think i'll have to
debug my code more thoroughly but at least now i have some hint :)

Once again thank you.

> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp


-- 
Mohammad Heykal Abdillah 


-- 
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] Specification for Trusted PLs?

[Tom Lane]

David Fetter  writes:
> I don't know about a *good* idea, but here's the one I've got.

> 1.  Make a whitelist.  This is what needs to work in order for a
> language to be a fully functional trusted PL.

Well, I pretty much lose interest right here, because this is already
assuming that every potentially trusted PL is isomorphic in its
capabilities.  If that were so, there'd not be very much point in
supporting multiple PLs.  A good example here is R.  I have no idea
whether PL/R is trusted or trustworthy, but in any case the main point
of supporting that PL is to allow access to the R statistical library.
How does that fit into a whitelist designed for some other language?
It doesn't.

> 3.  (the un-fun part) Write tests which attempt to do things not in
> the whitelist.  We can start from the vulnerabilities so far
> discovered.

And here is the *other* fatal problem: a whitelist does not in fact give
any leverage at all for testing whether there is access to functionality
outside the whitelist.  (It might be useful if you could enforce the
whitelist at some sufficiently low level of the language implementation,
but as a matter of testing, it does nothing for you.)  What you're
suggesting isn't so much un-fun as un-possible.  Given a maze of twisty
little subroutines all different, how will you find out if any of them
contain calls of unwanted functionality?

If you think you can do something with this, go for it, but don't
expect me to spend any of my time on it.

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] tsvector pg_stats seems quite a bit off.

[Tom Lane]

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?=  writes:
> On 19/05/10 21:01, Jesper Krogh wrote:
>> In practice, just cranking the statistics estimate up high enough seems
>> to solve the problem, but doesn't
>> there seem to be something wrong in how the statistics are collected?

> The algorithm to determine most common vals does not do it accurately.
> That would require keeping all lexemes from the analysed tsvectors in
> memory, which would be impractical. If you want to learn more about the
> algorithm being used, try reading
> http://www.vldb.org/conf/2002/S10P03.pdf and corresponding comments in
> ts_typanalyze.c

I re-scanned that paper and realized that there is indeed something
wrong with the way we are doing it.  The paper says (last sentence in
the definition of the algorithm, section 4.2):

When a user requests a list of items with threshold s, we output
those entries in D where f >= (s-e)N.

What we are actually doing is emitting every entry with f >= 2.  Since
e is fixed at 1/w, this effectively means that we are setting s to be
only fractionally greater than e, which means very large relative errors
in the estimates.

Or, if you want it explained another way: we are emitting words whose f
is very small and whose delta is very large, representing items that got
added to the scan very late.  These really shouldn't be there because
their true frequency is probably a lot less than the intended threshold.

The net effect of this is first that there are a lot of rather useless
entries in the MCV list whose claimed frequency is quite small, like as
little as two occurrences.  Their true frequency could be quite a bit
more.  What's even worse is that we believe that the minimum of these
claimed frequencies is a reliable upper bound for the frequencies of
items not listed in the MCV list.  Both of these errors are manifest
in Jesper's description of his case, and they're also easy to reproduce
if you just take stats on any reasonable-size collection of documents.
Cranking up the stats target actually makes it worse not better, since
low-frequency items are then more likely to get into the MCV list.

So I think we have to fix this.  The right thing is to select s and e
values that are actually meaningful in the terms of the paper, then
compute w from that, and be sure to enforce the minimum f value
specified in the algorithm ... ie, don't be afraid to throw away values
in the final D table.

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] quoting and recovery.conf

[Fujii Masao]

On Fri, May 28, 2010 at 12:20 AM, Greg Stark  wrote:
> My suggestion is we should fold all the parameters into
> postgresql.conf and treat recovery.conf as an additional
> postgresql.conf to read. It would allow any GUC. The only difference
> is that it would be moved out of the way automatically when the target
> is reached.

I agree to move all the parameter to postgresql.conf. If we do so,
ISTM that it's not useful to leave recovery.conf as a configuration
file. Instead, how about treating recovery.conf as just a trigger
file for recovery? That is, only when recovery.conf is found, the
recovery parameters in postgresql.conf are used and an archive
recovery or standby server start.

If we should avoid a hard-code of a trigger file name, we can
introduce new GUC paramter specifying the path of that. Then a user
can focus on postgresql.conf about configuration. Thought?

Regards,

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

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

Re: [HACKERS] [RFC] Security label support

[KaiGai Kohei]

(2010/05/28 5:25), Stephen Frost wrote:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> Stephen Frost  writes:
>>> Err, your question comes across to me like "if you added comments to
>>> pg_depend, you'd only be able to use a given comment X for one object?".
>>> Doesn't make alot of sense. :)
>>
>> Well, one of us is confused.  I thought the idea was that the same
>> security label would apply to many different objects.  If each object
>> has its own label, you're going to need an awfully large label cache
>> for performance to be sane.
> 
> It's certainly true that many objects could, and probably would in most
> situations, have the same label.  I feel like that could be true of
> comments as well.  We were just thinking about keeping it simple in the
> first go-round.  Going back to what I think was KaiGai's earlier
> question about which way to go:
> 
> #1: only have pg_description-like pg_seclabel
>  (objoid oid, classoid oid, objsubid integer, label text)
> 
> #2: have long-lived labels in pg_seclabels with (secoid oid, label text)
>  have label_oid in various system catalogs (pg_class, etc)
> 
> #3: have long-lived labels in pg_seclabels with (secoid oid, label text)
>  have mapping from each object to OID of label associated with it
>   (objoid oid, classoid oid, objsubid integer, secoid oid)
> 
> My inclination would generally be towards #2, to be honest, but I
> thought we wanted to avoid changing pg_namespace, pg_class and
> pg_attribute for this.
> 
Are you talking about a future development, aren't you?

I comment it for just a future development, not current efforts.

I plan the security-Id which points to an entry of pg_seclabel shall
be stored within padding area of HeapTupleHeader like what we are doing
to store object-Id.
The object-Id is stored only when HEAP_HASOID is set on t_infomask.
It does not damage to the existing schema of system catalog, and
allows to turn on/off the table's capability to store the security-Id.
Maybe, if we set up SELinux support, we need to run a special program
to initialize the database cluster just after initdb. It will turn on
the capability to store security-Id of the pg_class and so on.
Of course, we can apply same way between system catalogs and users'
tables.

>> OK, but the notion that you would try to remove "orphan" pg_labels
>> entries seems entirely wrongheaded to me.  The labels would be
>> long-lived objects.
> 
> OK, then we should really go with either #2 or #3 from above, and make
> sure that we add appropriate grammar to allow adding and removing of
> security labels.  Do we then throw an error if someone tries to put a
> label on an object that we don't already know of?  We certainly need to
> complain if someone tries to remove a label that is used by an object
> somewhere.  Typically, I'd expect the set of labels to be pretty well
> defined, but there will still be some amount of churn, and we'll need a
> way for people to determine what objects are still using certain labels.
> Not unlike how we deal with roles today already though.
> 
As Bruce suggested before, pg_seclabel should have relid field which
ensures the relation which references the security label entry.
It allows us to reclaim orphan security label with minimum locks.

In SE-PostgreSQL of Fedora, I provide a method to reclaim orphan labels:

  LOCK  IN SHARE MODE;

  DELETE FROM pg_seclabel WHERE relid =  AND
  secid NOT IN (SELECT tuple_to_secid() FROM ONLY );

During the reclaims, the  is locked for writable accesses,
but we don't need to lock out whole of the database to detect orphan labels.

Thanks,
-- 
KaiGai Kohei 

-- 
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] Streaming Replication: Checkpoint_segment and wal_keep_segments on standby

[Fujii Masao]

On Thu, May 27, 2010 at 11:13 PM, Robert Haas  wrote:
>> I guess this happens because the frequency of checkpoint on the standby is
>> too lower than that on the master. In the master, checkpoint occurs for every
>> consumption of three segments because of "checkpoint_segments = 3". On the
>> other hand, in the standby, only checkpoint_timeout has effect, so checkpoint
>> occurs for every 30 minutes because of "checkpoint_timeout = 30min".
>>
>> The walreceiver should signal the bgwriter to start checkpoint if it has
>> received more than checkpoint_segments WAL files, like normal processing?
>
> Is this also an issue when using log shipping, or just with SR?

When using log shipping, checkpoint_segments always doesn't trigger a
checkpoint. So recovery after the standby crashes might take unexpectedly
long since redo starting point might be old.

But in file-based log shipping, since WAL files don't accumulate in
pg_xlog directory on the standby, even if the frequency of checkpoint
is very low, pg_xlog will not be filled with many WAL files. That
accumulation occurs only when using SR.

If we should avoid low frequency of checkpoint itself rather than
accumulation of WAL files, the bgwriter instead of the walreceiver
should check if we've consumed too much WAL, I think. Thought?

Regards,

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

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

Re: [HACKERS] Specification for Trusted PLs?

[David Fetter]

On Thu, May 27, 2010 at 09:51:30PM -0400, Robert Haas wrote:
> On Thu, May 27, 2010 at 7:10 PM, David Fetter  wrote:
> > On Fri, May 28, 2010 at 01:03:15AM +0300, Peter Eisentraut wrote:
> >> On fre, 2010-05-21 at 14:22 -0400, Robert Haas wrote:
> >> > On Fri, May 21, 2010 at 2:21 PM, Tom Lane  wrote:
> >> > > Robert Haas  writes:
> >> > >> So... can we get back to coming up with a reasonable
> >> > >> definition,
> >> > >
> >> > > (1) no access to system calls (including file and network
> >> > > I/O)
> >> > >
> >> > > (2) no access to process memory, other than variables defined
> >> > > within the PL.
> >> > >
> >> > > What else?
> >> >
> >> > Doesn't subvert the general PostgreSQL security mechanisms?
> >> >  Not sure how to formulate that.
> >>
> >> Succinctly: A trusted language does not grant access to data that
> >> the user would otherwise not have.
> >
> > That's a great definition from a point of view of understanding by
> > human beings.  A whitelist system will work better from the point
> > of automating tests which, while they couldn't conclusively prove
> > that something was actually this way, could go a long way toward
> > making sure that PLs didn't regress into untrusted territory.
> 
> You haven't presented any sort of plan for how such automated
> testing would actually work.  Perhaps if you presented the plan
> first we could think about how to provide for its needs.  I'm
> generally of the opinion that it's not possible to do automated
> testing for security vulnerabilities (beyond crash testing, perhaps)
> but if you have a good idea let's talk about it.

I don't know about a *good* idea, but here's the one I've got.

1.  Make a whitelist.  This is what needs to work in order for a
language to be a fully functional trusted PL.

2.  Write tests that check that each thing on the whitelist works as
advertised.  These are language specific.

3.  (the un-fun part) Write tests which attempt to do things not in
the whitelist.  We can start from the vulnerabilities so far
discovered.

4.  Each time a vulnerability is discovered in one language, write
something that tests for it in the other languages.

I get that this isn't going to ensure that the access control is
perfect.  It's more a backstop against regressions of previously
function access controls.

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] [RFC] Security label support

[KaiGai Kohei]

(2010/05/28 5:11), Robert Haas wrote:
> On Thu, May 27, 2010 at 4:01 PM, Tom Lane  wrote:
>> Stephen Frost  writes:
>>> * Tom Lane (t...@sss.pgh.pa.us) wrote:
 I'm not real sure that you want a dependency for a security label anyway
 --- wouldn't that mean each label could only be used for one object?
>>
>>> Err, your question comes across to me like "if you added comments to
>>> pg_depend, you'd only be able to use a given comment X for one object?".
>>> Doesn't make alot of sense. :)
>>
>> Well, one of us is confused.  I thought the idea was that the same
>> security label would apply to many different objects.  If each object
>> has its own label, you're going to need an awfully large label cache
>> for performance to be sane.
> 
> I think this only makes sense when and if we implement row-level
> security.  The labels for SELinux are, say, 64 byte strings.  That's
> really not that big, if these are only being applied to objects like
> tables, and even columns.

Yes, as I noted on the idea [3], RLS with label requires a facility to
share a limited number of security labels, instead of flat text for
each user tuples. But it will need more code than the idea [2].

>  More to the point, ISTM a cache would be
> fairly useless anyway, because you have to pass the labels themselves
> to the OS to get an access control decision, which is also based on
> the type of object that you're doing something to and the operation
> you're doing to it.  It probably make sense to cache the results of
> the access-control lookup within a query - for example, if all the
> labels of a table you're accessing have the same label, just ask once
> for all of them, instead of individually for each one - but I can't
> see how you could usefully do much more than that.
> 
Right, as long as security policy is identical, it returns an identical
access control decision for the given pair of security label.
I plan to support access control decision cache, but it should be
implemented within ESP module, because it is SELinux specific.

BTW, SELinux also provide an interface to inform userspace applications
an invalidation message when security policy is reloaded, using netlink
socket. We entirely need a background worker process to monitor the socket,
but it should be in the future development.

> Now, if we were talking about row-level security, well, that's a whole
> different situation.  Now the space to store the individual labels
> might become burdensome.  But that's a problem for another day,
> hopefully a day when I'm out of town.
> 
Yes, let's tackle it in another day.

>>> The structure for pg_seclabel we were talking about would be very
>>> similar to pg_description, eg:
>>
>>> CREATE TABLE pg_seclabel (
>>>objoid oid not null,
>>>classoid oid not null,
>>>objsubid integer not null,
>>>label text
>>> );
>>
>>> We could move label into another table (eg: pg_labels) and then give an
>>> OID to each label and then store the label's OID in pg_seclabel.
>>
>> OK, but the notion that you would try to remove "orphan" pg_labels
>> entries seems entirely wrongheaded to me.  The labels would be
>> long-lived objects.
> 
> Now I'm confused.  Previously you complained about not having a
> garbage collection mechanism for labels - now you seem to be saying
> that we should never garbage collect.
> 
I'm also confused. What is the orphan label in the current pg_description
like design?
It has 1:1 map with a certain database object, so whenever we drop
the database object, its label entry shall be also dropped.
As long as database is not corrupt, no orphan labels will appear.

Thanks,
-- 
KaiGai Kohei 

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

Re: [HACKERS] Why my manualy constructed raw parser tree produce failed to execute?

[Robert Haas]

On Thu, May 27, 2010 at 6:56 PM, Mohammad Heykal Abdillah
 wrote:
> On Kam, 2010-05-27 at 15:02 -0400, Robert Haas wrote:
>> On Thu, May 27, 2010 at 1:58 PM, Mohammad Heykal Abdillah
>>  wrote:
>> > Now to the question, why my manualy constructed list was failed to
>> > execute? I was pretty sure that my list node was identical with yacc.
>>
>> Because you have a bug in your code.
>>
> Yes, that i know.
>
> Anyway, this is my manualy generate list node i hope you can point me
> where it went wrong :
> In function "pg_parse_query(const char *query_string)"
>
>  List    *my_parsetree_list;
>  my_parsetree_list = NIL;
>
>  ColumnRef *o = makeNode(ColumnRef);
>                  o->type = T_ColumnRef;
>                  o->fields = list_make1(makeString("*"));
>                  o->location = 16;
>
>  ResTarget *m = makeNode(ResTarget);
>                    m->type = T_ResTarget;
>                    m->name = NIL;
>                    m->indirection = NIL;
>                    m->val = o;
>                    m->location = 16;
>
>  RangeVar *p = makeNode(RangeVar);
>                p->schemaname = NIL;
>                p->relname = "customer";
>      p->inhOpt = 2;
>      p->istemp = false ;
>      p->alias = NIL;
>
>  SelectStmt *n         = makeNode(SelectStmt);
>      n->type           = T_SelectStmt;
>      n->distinctClause = list_make1(NIL);;
>      n->intoClause     = NULL;
>      n->targetList     = list_make1(m);
>      n->fromClause     = list_make1(p);
>      n->whereClause    = NULL;
>      n->groupClause    = NIL;
>      n->havingClause   = NULL;
>      n->valuesLists    = NIL;
>      n->sortClause     = NIL;
>      n->limitOffset    = NULL;
>      n->limitCount     = NULL;
>      n->lockingClause  = NIL;
>      n->op             = 0;
>      n->all            = false;
>      n->larg           = NULL;
>      n->rarg           = NULL;
>
>   my_parsetree_list = list_make1(n);
>
> raw_parsetree_list = my_parsetree_list;
> if (log_parser_stats)
> ShowUsage("PARSER STATISTICS"); ...
> ... and rest its same with original code.

In order to debug this, I (or someone else) would need an actual patch
that could be applied to the source tree with a specific series of
steps that would reproduce the bug.  But I'm not really that
interested in that myself, since this isn't something that is going to
be integrated into the Postgres source code.  If you want to try to
debug it yourself, I suggest using equal() [as Tatsuo-san already
mentioned] or else stepping through the code line by line with a
debugger until you find where it's going wrong, or else adding some
printf() statements to print out key variables in the functions that
are being called and try to find out where the difference is between
the original code and your modified code.

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

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

Re: [HACKERS] Specification for Trusted PLs?

[Robert Haas]

On Thu, May 27, 2010 at 7:10 PM, David Fetter  wrote:
> On Fri, May 28, 2010 at 01:03:15AM +0300, Peter Eisentraut wrote:
>> On fre, 2010-05-21 at 14:22 -0400, Robert Haas wrote:
>> > On Fri, May 21, 2010 at 2:21 PM, Tom Lane  wrote:
>> > > Robert Haas  writes:
>> > >> So... can we get back to coming up with a reasonable
>> > >> definition,
>> > >
>> > > (1) no access to system calls (including file and network I/O)
>> > >
>> > > (2) no access to process memory, other than variables defined
>> > > within the PL.
>> > >
>> > > What else?
>> >
>> > Doesn't subvert the general PostgreSQL security mechanisms?  Not
>> > sure how to formulate that.
>>
>> Succinctly: A trusted language does not grant access to data that
>> the user would otherwise not have.
>
> That's a great definition from a point of view of understanding by
> human beings.  A whitelist system will work better from the point of
> automating tests which, while they couldn't conclusively prove that
> something was actually this way, could go a long way toward making
> sure that PLs didn't regress into untrusted territory.

You haven't presented any sort of plan for how such automated testing
would actually work.  Perhaps if you presented the plan first we could
think about how to provide for its needs.  I'm generally of the
opinion that it's not possible to do automated testing for security
vulnerabilities (beyond crash testing, perhaps) but if you have a good
idea let's talk about it.

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

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

Re: [HACKERS] [RFC] Security label support

[KaiGai Kohei]

(2010/05/28 4:12), Stephen Frost wrote:
> KaiGai,
> 
> * KaiGai Kohei (kai...@ak.jp.nec.com) wrote:
>> As we talked at the developer meeting on Ottawa, it needs to provide
>> a capability to assign a short text identifier on database objects
>> to support label based ESP (such as SELinux).
>> So, I'd like to propose a few approaches to support security label
>> as a draft of discussion.
> [...]
>> [2] Using OID as a key of text representation in separated catalog
>> --
>>
>> This idea is similar to pg_description/pg_shdescription.
>> A new system catalog pg_seclabel and pg_shseclabel stores text form
>> of labels for pair of the relation-Id, object-Oid and object-Subid.
>> It does not damage to the schema of existing system catalog,
> 
> Right, this is the approach that was agreed to during various
> discussions and is, I believe, what Robert is currently working on.
> 
Good.

I've been allowed to work it with high priority.
If your hands are not free, should I work on it?

>> It adds two new system catalogs; pg_seclabel (local) and pg_shseclabel 
>> (shared).
> 
> Do we really need a shared catalog initially?  Right now, we're just
> talking about labels for tables and columns.  Let's not overcomplicate
> this.  We can always add it later.
> 
Perhaps, I can agree to add it later. I believe the pg_seclabel and 
pg_shseclabel
shall have identical schema, so we can easily add it.

>> We also add a dependency between the labeled object and the security
>> label itself. It also enables to clean up orphan labels automatically,
>> without any new invention.
> 
> I agree that we need to address this.  I am kind of curious how this is
> handled for comments?  It appears to be, but I don't see an entry in
> pg_depend when a comment is added to an object, yet the entry in
> pg_description disappears when a table is dropped.
> 

Sorry, I missed the special handling of pg_description.
However, what we should do here is same as pg_description without
any new invention.

>> However, it also has a limitation from the viewpoint of long-term.
>> > From the definition, OID of database objects are unique. So, we cannot
>> share text form of labels even if massive number of database objects
>> have an identical security label; it can lead waste of storage consumption
>> because of the duplicated security labels. So, this idea shall be switched
>> to the [3] when we support row-level security with ESP.
>> But I think the idea [2] is reasonable in short-term development.
> 
> Row level security might not even use this catalog directly but perhaps
> another one.  That's a discussion for a much later time though.
> 

Yep, we can design it later.

>> * SQL Statement
>> ---
>>
>> It also need to provide SQL statement to manage security label of the 
>> database
>> object. I plan the following statement to change the security label.
>>
>>ALTER xxx  SECURITY LABEL TO 'label';
> 
> Rather than adding more cruft around ALTER, I believe the plan is to add
> a new top-level command (eg: 'SECURITY LABEL ON'), just like the COMMENT
> ON syntax.
> 

It seems to me the syntax like COMMENT ON gives us an incorrect impression.
The role of ALTER is (basically) to alter an existing property of the object,
such as owner, name, schema and so on. Meanwhile, COMMENT ON is used for
both assignment of new description and update of existing description.

The security label is a part of the properties of object to be assigned on
the creation time, such as owner-id. (Even if ESP module is not loaded on
the creation time, it assumes someone is labeled on the unlabeled object.)

The ALTER SECURITY LABEL TO shall be implemented as an individual code path,
like ALTER xxx RENAME TO or ALTER xxx SCHEMA TO, so the patch shall not be
invasive to existing ALTER implementation.

I don't think a new top level 'SECURITY LABEL ON' is not a good naming,
although its internal catalog layout is similar to pg_description.

>> When the ALTER command is executed, ESP module validate the given label,
>> in addition to permission checks to relabel it.
> 
>> If no ESP module is available, the ALTER always raises a 
>> feature-not-supported
>> error.
> 
> Right.  We do need to identify what the hook needs to look like.  I
> would think just passing a pg_seclabel (or whatever) structure which
> would represent the new row in the table (possibly replacing an existing
> row, if one exists, but the hook can figure that out).  The hook can
> then figure out the user and any other information it needs to know
> based on that and either allow or not allow the change.
> 

I think ESP should not need to handle whether the target object is already
labeled, or not. It can be push down into a common function.
For example, we can easily provide seclabel_insert_or_update() that allows
to insert a new label into pg_seclabel (if not exist) or update an old label
of pg_seclabel (if exist).

I plan the following des

Re: [HACKERS] pg_trgm

[Tatsuo Ishii]

> It's not a practical solution for people working with prebuilt Postgres
> versions, which is most people.  I don't object to finding a way to
> provide a "not-space" behavior instead of an "is-alnum" behavior,
> but as noted upthread a GUC isn't the right way.  How do you feel
> about a new set of functions with an additional flag argument of
> some sort?

Let me see how many functions we need to create...
--
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 XMLEXISTS function from the SQL/XML standard

[Bruce Momjian]

Mike Fowler wrote:
> Tom Lane wrote:
> > Robert Haas  writes:
> >   
> >> On Tue, May 25, 2010 at 1:09 PM, Mike Fowler  wrote:
> >> 
>  We're unlikely to accept this patch if it changes the minimum version
>  of libxml2 required to compile PostgreSQL
>  
> >>> Why? 2.6.27 is almost 4 years old.
> >> At a minimum, I think it's fair to say that the burden is on you to
> >> justify what it's worth bumping the version number.
> >> 
> >
> > Yes.  Increasing the minimum required version of some library is a Big
> > Deal, we don't do it on a whim.  And we definitely don't do it just
> > because it's old.
> >
> > regards, tom lane
> >
> >   
> 
> OK, I consider myself suitably educated/chastised. I now understand why 
> a version bump is such a big deal. Your objections are all reasonable, I 
> suppose I'm just used to living on the bleeding edge of everything. 
> Consequently I have changed the code to produce the same result in a 
> different way without using the new function. I've down-graded my 
> version to 2.6.26 and it all compiles cleanly. Please find attached my 
> revised patch, and thanks all for your advise.

FYI, it is often good to add a comment in the C code about why you
didn't use the new XML function so if the issue comes up again, we know
why, and in 10 years, we can use it.  ;-)

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://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] pg_trgm

[Tatsuo Ishii]

> I think the problem at hand has nothing at all to do with agglutination
> or CJK-specific issues.  You will get the same problem with other
> languages *if* you set a locale that does not adequately support the
> characters in use.  E.g., Russian with locale C and encoding UTF8:
> 
> select similarity(E'\u0441\u043B\u043E\u043D', E'\u0441\u043B\u043E
> \u043D\u044B');
>  similarity
> 
> NaN
> (1 row)

Wait. This works fine for me with stock pg_trgm. local is C and
encoding is UTF8. What version of PostgreSQL are you using? Mine is
8.4.4.
--
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] pg_trgm

[Tatsuo Ishii]

> Tatsuo Ishii  writes:
> > similarity -> generate_trgm -> find_word -> iswordchr -> t_isalpha -> 
> > isalpha
> 
> > if locale is C and USE_WIDE_UPPER_LOWER defined which is the case in
> > most modern OSs.
> 
> Quite.  And *if locale is C then only standard ASCII letters are letters*.
> You may not like that but it's not wrong; in fact any other behavior
> would be wrong.

> *if locale is C then only standard ASCII letters are letters*.

That's just the definition/implementaion of isalpha. My point is,
using isalpha is quite correct for pg_trgm or not. Text search, oracle
compat functions and any other string handling functions in PostgreSQL
behave quite different from what pg_trgm does.

The essential question is, are there any reason for pg_trgm to limit
to only ASCII range characters?
--
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] pg_trgm

[Tom Lane]

Tatsuo Ishii  writes:
> Or you could just #undef KEEPONLYALNUM in trgm.h. But I'm not sure
> this is the right thing for you.

It's not a practical solution for people working with prebuilt Postgres
versions, which is most people.  I don't object to finding a way to
provide a "not-space" behavior instead of an "is-alnum" behavior,
but as noted upthread a GUC isn't the right way.  How do you feel
about a new set of functions with an additional flag argument of
some sort?

regards, tom lane

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

Re: [HACKERS] pg_trgm

[Tom Lane]

Tatsuo Ishii  writes:
> similarity -> generate_trgm -> find_word -> iswordchr -> t_isalpha -> isalpha

> if locale is C and USE_WIDE_UPPER_LOWER defined which is the case in
> most modern OSs.

Quite.  And *if locale is C then only standard ASCII letters are letters*.
You may not like that but it's not wrong; in fact any other behavior
would be wrong.

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] List traffic

[Tom Lane]

Josh Berkus  writes:
> We do not have a problem.   The lists are fine the way they are.

+1 ... wasn't the point I thought you were trying to make, but I'm
good with not changing things.

regards, tom lane

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

Re: [HACKERS] pg_trgm

[Tatsuo Ishii]

> > Problem with pg_trgm is, it uses isascii() etc. to recognize a letter,
> > which will skip any non ASCII range character in C locale.
> 
> The only place I see that is in those ISPRINTABLE macros, which are only
> used in show_trgm(), which is just a debugging function.  It could stand
> to be improved but it doesn't seem exactly critical.

Really?

similarity -> generate_trgm -> find_word -> iswordchr -> t_isalpha -> isalpha

if locale is C and USE_WIDE_UPPER_LOWER defined which is the case in
most modern OSs.
--
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] List traffic

[Josh Berkus]

Well, there's no free lunch.  If we have a whole lot of "small" lists
there are going to be two big downsides: fewer people reading each list
(hence fewer answers), and many more arguably-misclassified postings,
thus diluting the theoretical targetedness of the lists.


You're missing my point.  I'm saying that people *are* gettings answers 
on the -sql and -performance lists, that those lists are very busy, and 
that consolidating them with other lists would just drive people away 
due to traffic volume.  And that nobody who is suggesting list 
consolidation has presented any evidence to the contrary other than a 
*single* missed bug report.  Data is not the plural of anecdote.


I'm *not* suggesting that we create more lists just because, either.

Again, this whole discussion is a solution in search of a problem. 
Someone wants to mess with our list organization just because they are 
bored.  If they're that bored, I understand that the web team could use 
some help.  Or they could review patches.


We do not have a problem.   The lists are fine the way they are.

--
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.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] pg_trgm

[Tom Lane]

Tatsuo Ishii  writes:
> Problem with pg_trgm is, it uses isascii() etc. to recognize a letter,
> which will skip any non ASCII range character in C locale.

The only place I see that is in those ISPRINTABLE macros, which are only
used in show_trgm(), which is just a debugging function.  It could stand
to be improved but it doesn't seem exactly critical.

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] Why my manualy constructed raw parser tree produce failed to execute?

[Tatsuo Ishii]

> > Now to the question, why my manualy constructed list was failed to
> > execute? I was pretty sure that my list node was identical with yacc.
> 
> Because you have a bug in your code.

You can debug your code by comparing your hand made tree with the
original tree by using equal(). Search #ifdef COPY_PARSE_PLAN_TREES in
postgres.c to see how to use it. Good luck.
--
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] pg_trgm

[Tatsuo Ishii]

> What I can't help wondering as I'm reading this discussion is -
> Tatsuo-san said upthread that he has a problem with pg_trgm that he
> does not have with full text search.  So what is full text search
> doing differently than pg_trgm?

Problem with pg_trgm is, it uses isascii() etc. to recognize a letter,
which will skip any non ASCII range character in C locale.
--
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] pg_trgm

[Tatsuo Ishii]

> I think the problem at hand has nothing at all to do with agglutination
> or CJK-specific issues.  You will get the same problem with other
> languages *if* you set a locale that does not adequately support the
> characters in use.  E.g., Russian with locale C and encoding UTF8:
> 
> select similarity(E'\u0441\u043B\u043E\u043D', E'\u0441\u043B\u043E
> \u043D\u044B');
>  similarity
> 
> NaN
> (1 row)

Of course. That's why I started this thread.

With my patch:

test=# select similarity(E'\u0441\u043B\u043E\u043D', 
E'\u0441\u043B\u043E\u043D\u044B');
 similarity 

   0.75
(1 row)

Or you could just #undef KEEPONLYALNUM in trgm.h. But I'm not sure
this is the right thing for you.
--
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

[HACKERS] VPATH docs

[David Fetter]

Folks,

Andrew Dunstan posted some instructions on his blog, and I'm thinking
they clarify things a great deal for people who want to learn how to
do VPATH builds.

Attached patch adds the description along with an index term.  What
say?

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
diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml
index 5da97c5..cd3755b 100644
--- a/doc/src/sgml/installation.sgml
+++ b/doc/src/sgml/installation.sgml
@@ -542,9 +542,18 @@ su - postgres
 This script will run a number of tests to determine values for various
 system dependent variables and detect any quirks of your
 operating system, and finally will create several files in the
-build tree to record what it found.  (You can also run
+build tree to record what it found.  You can also run
 configure in a directory outside the source
-tree if you want to keep the build directory separate.)
+tree, if you want to keep the build directory separate.  This
+procedure is also called a
+VPATHVPATH
+build.  Here's how:
+
+mkdir build_dir
+cd build_dir
+/path/to/source/tree/configure [options go here]
+gmake
+

 


-- 
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] Specification for Trusted PLs?

[David Fetter]

On Fri, May 28, 2010 at 01:03:15AM +0300, Peter Eisentraut wrote:
> On fre, 2010-05-21 at 14:22 -0400, Robert Haas wrote:
> > On Fri, May 21, 2010 at 2:21 PM, Tom Lane  wrote:
> > > Robert Haas  writes:
> > >> So... can we get back to coming up with a reasonable
> > >> definition,
> > >
> > > (1) no access to system calls (including file and network I/O)
> > >
> > > (2) no access to process memory, other than variables defined
> > > within the PL.
> > >
> > > What else?
> > 
> > Doesn't subvert the general PostgreSQL security mechanisms?  Not
> > sure how to formulate that.
> 
> Succinctly: A trusted language does not grant access to data that
> the user would otherwise not have.

That's a great definition from a point of view of understanding by
human beings.  A whitelist system will work better from the point of
automating tests which, while they couldn't conclusively prove that
something was actually this way, could go a long way toward making
sure that PLs didn't regress into untrusted territory.

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] Why my manualy constructed raw parser tree produce failed to execute?

[Mohammad Heykal Abdillah]

On Kam, 2010-05-27 at 15:02 -0400, Robert Haas wrote: 
> On Thu, May 27, 2010 at 1:58 PM, Mohammad Heykal Abdillah
>  wrote:
> > Now to the question, why my manualy constructed list was failed to
> > execute? I was pretty sure that my list node was identical with yacc.
> 
> Because you have a bug in your code.
> 
Yes, that i know.

Anyway, this is my manualy generate list node i hope you can point me
where it went wrong :
In function "pg_parse_query(const char *query_string)"

  List*my_parsetree_list;
  my_parsetree_list = NIL;

  ColumnRef *o = makeNode(ColumnRef);
  o->type = T_ColumnRef;
  o->fields = list_make1(makeString("*"));
  o->location = 16;

  ResTarget *m = makeNode(ResTarget);
m->type = T_ResTarget;
m->name = NIL;
m->indirection = NIL;
m->val = o;
m->location = 16;

  RangeVar *p = makeNode(RangeVar);  
p->schemaname = NIL;
p->relname = "customer";
  p->inhOpt = 2; 
  p->istemp = false ;
  p->alias = NIL;

  SelectStmt *n = makeNode(SelectStmt);
  n->type   = T_SelectStmt;
  n->distinctClause = list_make1(NIL);;
  n->intoClause = NULL;  
  n->targetList = list_make1(m);
  n->fromClause = list_make1(p);  
  n->whereClause= NULL;  
  n->groupClause= NIL; 
  n->havingClause   = NULL; 
  n->valuesLists= NIL; 
  n->sortClause = NIL; 
  n->limitOffset= NULL;  
  n->limitCount = NULL; 
  n->lockingClause  = NIL; 
  n->op = 0;
  n->all= false;
  n->larg   = NULL;
  n->rarg   = NULL;

   my_parsetree_list = list_make1(n);

raw_parsetree_list = my_parsetree_list;
if (log_parser_stats)
ShowUsage("PARSER STATISTICS"); ...
... and rest its same with original code.

Thank You
-- 
Mohammad Heykal Abdillah 


-- 
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] Synchronization levels in SR

[Robert Haas]

On May 27, 2010, at 4:31 PM, Bruce Momjian  wrote:

Heikki Linnakangas wrote:
BTW, I think we're going to need a separate config file for listing  
the
standbys anyway. There you can write per-server rules and options,  
but

explicitly knowing about all the standbys also allows the master to
recycle WAL as soon as it has been streamed to all the registered
standbys. Currently we just keep wal_keep_segments files around,  
just in

case there's a standby out there that needs them.


Ideally we could set 'slave_sync_count' and  
'slave_commit_continue_mode'

on the master, and allow the sync/async mode to be set on each slave,
e.g. if slave_sync_count = 2 and slave_commit_continue_mode = #2, then
two slaves with sync mode of #2 or stricter have to complete before  
the

master can continue.

Naming the slaves on the master seems very confusing because I am
unclear how we would identify named slaves, and the names have to  
match,

etc.


The names could be configured with a GUC on the slaves, or we could  
base it on the login role.


...Robert

--
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] functional call named notation clashes with SQL feature

[David Fetter]

On Thu, May 27, 2010 at 02:55:54PM -0400, Robert Haas wrote:
> On Thu, May 27, 2010 at 1:27 PM, David E. Wheeler  
> wrote:
> > On May 27, 2010, at 9:59 AM, Tom Lane wrote:
> >
> >>> I think we should fix it now.  Quick thought: maybe we could use FOR
> >>> instead of AS: select myfunc(7 for a, 6 for b);
> >>
> >> I'm afraid FOR doesn't work either; it'll create a conflict with the
> >> spec-defined SUBSTRING(x FOR y) syntax.
> >
> > How about "ISPARAMVALUEFOR"? That shouldn't conflict with anything.
> 
> Or we could use the Finnish word
> epäjärjestelmällistyttämättömyydellänsäkäänköhän, which I'm pretty
> sure is not currently used in our grammar.

We could use the Turkish
muvaffakiyetsizleştiricileştiriveremeyebileceklerimizdenmişsinizcesine,
which I'm pretty sure isn't either :)

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] Specification for Trusted PLs?

[Peter Eisentraut]

On fre, 2010-05-21 at 14:22 -0400, Robert Haas wrote:
> On Fri, May 21, 2010 at 2:21 PM, Tom Lane  wrote:
> > Robert Haas  writes:
> >> So... can we get back to coming up with a reasonable
> >> definition,
> >
> > (1) no access to system calls (including file and network I/O)
> >
> > (2) no access to process memory, other than variables defined within the
> > PL.
> >
> > What else?
> 
> Doesn't subvert the general PostgreSQL security mechanisms?  Not sure
> how to formulate that.

Succinctly: A trusted language does not grant access to data that the
user would otherwise not have.

I wouldn't go any further than that.  File and network I/O, for example,
are implementation details.  A trusted language might do some kind of
RPC, for example.  The PL/J project once wanted to do something like
that.


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

[HACKERS] mingw initdb failure on HEAD

[Andrew Dunstan]

Several buildfarm mingw members are getting failures like this, when 
running initdb:


   creating conversions ... FATAL:  could not load library 
"C:/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.3584/src/test/regress/./tmp_check/install/home/pgrunner/bf/root/HEAD/inst/lib/postgresql/utf8_and_johab.dll":
 Invalid access to memory location.

   STATEMENT:  CREATE OR REPLACE FUNCTION johab_to_utf8 (INTEGER, INTEGER, 
CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$libdir/utf8_and_johab', 
'johab_to_utf8' LANGUAGE C STRICT;

   child process exited with exit code 1
 

See for example 



Could it have been caused by the PGDLLIMPORT/PGDLLEXPORT changes?

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] Exposing the Xact commit order to the user

[Jan Wieck]

On 5/27/2010 12:01 PM, Jan Wieck wrote:

On 5/27/2010 9:59 AM, Greg Stark wrote:

This thread has been hard to follow for me. Were any of these
questions answered?



Yes.


The thing missing is any sort of answer to that problem description.


Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin


--
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] Idea for getting rid of VACUUM FREEZE on cold pages

[Kevin Grittner]

>>>Joe Conway  wrote:
 
> (a) can work if it is all in one command, CREATE TABLE AS
> SELECT...
 
> Additionally we were discussing COPY in the FROM clause, which
> means you could CREATE TABLE AS SELECT ... FROM (COPY ...). That
> would allow bulk loading with hint bits already set (and tuples
> frozen?).
 
As long as it's hinted and frozen after a pg_dump -1 | psql I'll be
happy.
 
-Kevin

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

Re: [HACKERS] Synchronization levels in SR

[Bruce Momjian]

Heikki Linnakangas wrote:
> BTW, I think we're going to need a separate config file for listing the 
> standbys anyway. There you can write per-server rules and options, but 
> explicitly knowing about all the standbys also allows the master to 
> recycle WAL as soon as it has been streamed to all the registered 
> standbys. Currently we just keep wal_keep_segments files around, just in 
> case there's a standby out there that needs them.

Ideally we could set 'slave_sync_count' and 'slave_commit_continue_mode'
on the master, and allow the sync/async mode to be set on each slave,
e.g. if slave_sync_count = 2 and slave_commit_continue_mode = #2, then
two slaves with sync mode of #2 or stricter have to complete before the
master can continue.

Naming the slaves on the master seems very confusing because I am
unclear how we would identify named slaves, and the names have to match,
etc.  

Also, what would be cool would be if you could run a query on the master
to view the SR commit mode of each slave.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://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] [RFC] Security label support

[Stephen Frost]

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost  writes:
> > Err, your question comes across to me like "if you added comments to
> > pg_depend, you'd only be able to use a given comment X for one object?".
> > Doesn't make alot of sense. :)
> 
> Well, one of us is confused.  I thought the idea was that the same
> security label would apply to many different objects.  If each object
> has its own label, you're going to need an awfully large label cache
> for performance to be sane.

It's certainly true that many objects could, and probably would in most
situations, have the same label.  I feel like that could be true of
comments as well.  We were just thinking about keeping it simple in the
first go-round.  Going back to what I think was KaiGai's earlier
question about which way to go:

#1: only have pg_description-like pg_seclabel
(objoid oid, classoid oid, objsubid integer, label text)

#2: have long-lived labels in pg_seclabels with (secoid oid, label text)
have label_oid in various system catalogs (pg_class, etc)

#3: have long-lived labels in pg_seclabels with (secoid oid, label text)
have mapping from each object to OID of label associated with it
(objoid oid, classoid oid, objsubid integer, secoid oid)

My inclination would generally be towards #2, to be honest, but I
thought we wanted to avoid changing pg_namespace, pg_class and
pg_attribute for this.

> OK, but the notion that you would try to remove "orphan" pg_labels
> entries seems entirely wrongheaded to me.  The labels would be
> long-lived objects.

OK, then we should really go with either #2 or #3 from above, and make
sure that we add appropriate grammar to allow adding and removing of
security labels.  Do we then throw an error if someone tries to put a
label on an object that we don't already know of?  We certainly need to
complain if someone tries to remove a label that is used by an object
somewhere.  Typically, I'd expect the set of labels to be pretty well
defined, but there will still be some amount of churn, and we'll need a
way for people to determine what objects are still using certain labels.
Not unlike how we deal with roles today already though.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Joe Conway]

On 05/27/2010 12:39 PM, Robert Haas wrote:
> On Thu, May 27, 2010 at 3:15 PM, Kevin Grittner
>  wrote:
>> Jesper Krogh  wrote:
>>
>>> Couldn't pages that are totally filled by the same transaction, be
>>
>>> frozen on the initial write?
>>
>> As far as I'm aware, that can only be done if:
>>
>> (a)  The tuples were written within the same transaction which
>> created or truncated the table.
>>
>> *or*
>>
>> (b)  The writing transaction and all transactions concurrent to it
>> have completed by the time the page is about to be written.
> 
> Actually, I think this is true only in case (b).  In case (a), you
> mess up visibility with respect to other command-IDs within the
> transaction.
> 

(a) can work if it is all in one command, CREATE TABLE AS SELECT...

Additionally we were discussing COPY in the FROM clause, which means you
could CREATE TABLE AS SELECT ... FROM (COPY ...). That would allow bulk
loading with hint bits already set (and tuples frozen?).

Joe



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] List traffic

[alvherre]

Excerpts from Josh Berkus's message of jue may 27 14:11:51 -0400 2010:

> Only someone who is a postgresql developer would consider 15-30
> posts/day "small".  For most of our user base, the level of traffic on
> -performance, -sql, and -general is already too high and many people
> don't subscribe to these lists because it is too high.  I get complaints
> -- and people personal-sending me questions because they don't want to
> subscribe -- all the time.

People can post without being subscribed, and most people around here
will CC them when they reply.  That's supposed to be a feature of our
lists.  Maybe when you receive such a question you can forward it to a
list CCing the person.

Not that I disagree with your opinion that a smaller list is desirable.
I think collapsing lists into -general or whatever would be a terrible idea.

-- 
Á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] [RFC] Security label support

[Robert Haas]

On Thu, May 27, 2010 at 4:01 PM, Tom Lane  wrote:
> Stephen Frost  writes:
>> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>>> I'm not real sure that you want a dependency for a security label anyway
>>> --- wouldn't that mean each label could only be used for one object?
>
>> Err, your question comes across to me like "if you added comments to
>> pg_depend, you'd only be able to use a given comment X for one object?".
>> Doesn't make alot of sense. :)
>
> Well, one of us is confused.  I thought the idea was that the same
> security label would apply to many different objects.  If each object
> has its own label, you're going to need an awfully large label cache
> for performance to be sane.

I think this only makes sense when and if we implement row-level
security.  The labels for SELinux are, say, 64 byte strings.  That's
really not that big, if these are only being applied to objects like
tables, and even columns.   More to the point, ISTM a cache would be
fairly useless anyway, because you have to pass the labels themselves
to the OS to get an access control decision, which is also based on
the type of object that you're doing something to and the operation
you're doing to it.  It probably make sense to cache the results of
the access-control lookup within a query - for example, if all the
labels of a table you're accessing have the same label, just ask once
for all of them, instead of individually for each one - but I can't
see how you could usefully do much more than that.

Now, if we were talking about row-level security, well, that's a whole
different situation.  Now the space to store the individual labels
might become burdensome.  But that's a problem for another day,
hopefully a day when I'm out of town.

>> The structure for pg_seclabel we were talking about would be very
>> similar to pg_description, eg:
>
>> CREATE TABLE pg_seclabel (
>>   objoid oid not null,
>>   classoid oid not null,
>>   objsubid integer not null,
>>   label text
>> );
>
>> We could move label into another table (eg: pg_labels) and then give an
>> OID to each label and then store the label's OID in pg_seclabel.
>
> OK, but the notion that you would try to remove "orphan" pg_labels
> entries seems entirely wrongheaded to me.  The labels would be
> long-lived objects.

Now I'm confused.  Previously you complained about not having a
garbage collection mechanism for labels - now you seem to be saying
that we should never garbage collect.

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

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

Re: [HACKERS] List traffic

[Tom Lane]

Josh Berkus  writes:
> On 5/27/10 8:38 AM, Greg Stark wrote:
>> Mot administration
>> questions are originally posed as general help questions. If you're
>> subscribed to these lists you get a random, fairly small, subset of
>> discussion related these topics.

> Only someone who is a postgresql developer would consider 15-30
> posts/day "small".  For most of our user base, the level of traffic on
> -performance, -sql, and -general is already too high and many people
> don't subscribe to these lists because it is too high.  I get complaints
> -- and people personal-sending me questions because they don't want to
> subscribe -- all the time.

> Having fewer posts on any particular list is *desireable*.  It's a good
> thing.  It's *only* a problem when a bug report or user question goes
> unanswered because the list is unattended.  And so far, I've only seen
> one report of that.

Well, there's no free lunch.  If we have a whole lot of "small" lists
there are going to be two big downsides: fewer people reading each list
(hence fewer answers), and many more arguably-misclassified postings,
thus diluting the theoretical targetedness of the lists.

If you want good answers to questions you need to post them in a forum
where there are enough people to ensure someone will know the answer
(and have the time/interest to respond).  People who want answers and
don't want to have to read other discussions should consider obtaining
commercial support.

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] functional call named notation clashes with SQL feature

[Pavel Stehule]

2010/5/27 Tom Lane :
> Peter Eisentraut  writes:
>> On tor, 2010-05-27 at 12:59 -0400, Tom Lane wrote:
>>> I'm afraid FOR doesn't work either; it'll create a conflict with the
>>> spec-defined SUBSTRING(x FOR y) syntax.
>
>> How about
>> select myfunc(a := 7, b := 6);
>> ?
>
> Hey, that's a thought.  We couldn't have used that notation before
> because we didn't have := as a separate token, but since I hacked that
> in for plpgsql's benefit, I think it might be an easy fix.  It'd be
> nice that it puts the argument name first like the spec syntax, too.

I can live with it.

Regards

Pavel

>
> Question #1: is the SQL committee likely to standardize that out
> from under us, too?
>
> Question #2: will ecpg have a problem with this?  Or psql for that
> matter (can you have a psql variable named '=')?
>
>                        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
>

-- 
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] [RFC] Security label support

[Tom Lane]

Stephen Frost  writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> I'm not real sure that you want a dependency for a security label anyway
>> --- wouldn't that mean each label could only be used for one object?

> Err, your question comes across to me like "if you added comments to
> pg_depend, you'd only be able to use a given comment X for one object?".
> Doesn't make alot of sense. :)

Well, one of us is confused.  I thought the idea was that the same
security label would apply to many different objects.  If each object
has its own label, you're going to need an awfully large label cache
for performance to be sane.

> The structure for pg_seclabel we were talking about would be very
> similar to pg_description, eg:

> CREATE TABLE pg_seclabel (
>   objoid oid not null,
>   classoid oid not null,
>   objsubid integer not null,
>   label text 
> );

> We could move label into another table (eg: pg_labels) and then give an
> OID to each label and then store the label's OID in pg_seclabel.

OK, but the notion that you would try to remove "orphan" pg_labels
entries seems entirely wrongheaded to me.  The labels would be
long-lived objects.

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] Idea for getting rid of VACUUM FREEZE on cold pages

[Kevin Grittner]

Robert Haas  wrote:
 
> I proposed an idea at PGCon, but I believe Tom and Heikki thought
> it was far too grotty to consider.
 
Well, as an alternative -- don't we have some information about the
relation pinned which could hold the xid of its creator?  If the
tuple is frozen check to see if your transaction is the creator and
behave like you created the tuple (which, in fact, you did)?
 
-Kevin

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

Re: [HACKERS] functional call named notation clashes with SQL feature

[Stephen Frost]

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Peter Eisentraut  writes:
> > select myfunc(a := 7, b := 6);

Kinda like it myself.

> Question #1: is the SQL committee likely to standardize that out
> from under us, too?

Couldn't say on that one.

> Question #2: will ecpg have a problem with this?  Or psql for that
> matter (can you have a psql variable named '=')?

psql doesn't like it, for what it's worth:

postgres=# \set = hi
\set: error

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] [RFC] Security label support

[Stephen Frost]

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost  writes:
> > I agree that we need to address this.  I am kind of curious how this is
> > handled for comments?  It appears to be, but I don't see an entry in
> > pg_depend when a comment is added to an object, yet the entry in
> > pg_description disappears when a table is dropped.  
> 
> IIRC, dropping comments is hard-wired into the object drop mechanism ---
> this seemed more efficient than having to add a pg_depend entry for each
> one.  You could argue that either way of course depending on how many
> comments you expect there to be in the system.

ok.  We would need to add similar mechanics for labels (removing the
entries when the table is dropped).  Strikes me as slightly odd that we
have an infrastructure in place to handle exactly that but we're not
using it. :)

> I'm not real sure that you want a dependency for a security label anyway
> --- wouldn't that mean each label could only be used for one object?

Err, your question comes across to me like "if you added comments to
pg_depend, you'd only be able to use a given comment X for one object?".
Doesn't make alot of sense. :)  Perhaps I misunderstood, but my
assumption would be that, as with comments, there would be an additional
identifier in pg_seclabel (eg: oid) to then use in pg_depend to track that
a given *entry* in pg_seclabel depends on a table.  You wouldn't (erm,
and couldn't) put the actual text of the label into pg_depend.

The structure for pg_seclabel we were talking about would be very
similar to pg_description, eg:

CREATE TABLE pg_seclabel (
  objoid oid not null,
  classoid oid not null,
  objsubid integer not null,
  label text 
);

We could move label into another table (eg: pg_labels) and then give an
OID to each label and then store the label's OID in pg_seclabel.  That
would then have the problem you describe, but we could just have a
'mapping OID' from the table to the label and then have *that* depend on
the table (erm, and the label).  I'm not sure I see the need for that
right now.  We may want that when we add row-level security support, so
perhaps we should consider doing that now, but I don't expect RLS
anytime real soon.

A thought that did occur to me is that we could forgo actually
identifying in pg_depend the *specific* entry in pg_description or
pg_seclabel that depends on the table and, perhaps, just have an
entry that says "something with this classid depends on it, so delete
anything in that table which has a matching objoid and classoid of
what's being removed".

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] JSON manipulation functions

[Tom Lane]

Robert Haas  writes:
> On Thu, May 27, 2010 at 3:50 PM, Tom Lane  wrote:
>> It certainly is --- he was actually declaring a cast with it in his
>> example, no?

> That was an attempt at a workaround to get it to do what he wanted.

Oh.  If you don't want to think of it as being a cast-like operation,
then naming it after the result type is probably the wrong thing anyway.

regards, tom lane

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Robert Haas]

On Thu, May 27, 2010 at 3:52 PM, Kevin Grittner
 wrote:
> Robert Haas  wrote:
>> On Thu, May 27, 2010 at 3:15 PM, Kevin Grittner
>
>>> (a)  The tuples were written within the same transaction which
>>> created or truncated the table.
>
>> In case (a), you mess up visibility with respect to other
>> command-IDs within the transaction.
>
> Surely that problem is surmountable?

I proposed an idea at PGCon, but I believe Tom and Heikki thought it
was far too grotty to consider.

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

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

Re: [HACKERS] functional call named notation clashes with SQL feature

[Tom Lane]

Peter Eisentraut  writes:
> On tor, 2010-05-27 at 12:59 -0400, Tom Lane wrote:
>> I'm afraid FOR doesn't work either; it'll create a conflict with the
>> spec-defined SUBSTRING(x FOR y) syntax.

> How about
> select myfunc(a := 7, b := 6);
> ?

Hey, that's a thought.  We couldn't have used that notation before
because we didn't have := as a separate token, but since I hacked that
in for plpgsql's benefit, I think it might be an easy fix.  It'd be
nice that it puts the argument name first like the spec syntax, too.

Question #1: is the SQL committee likely to standardize that out
from under us, too?

Question #2: will ecpg have a problem with this?  Or psql for that
matter (can you have a psql variable named '=')?

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] JSON manipulation functions

[Robert Haas]

On Thu, May 27, 2010 at 3:50 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Thu, May 27, 2010 at 3:35 PM, Tom Lane  wrote:
>>> If the function is a cast function (which it is),
>
>> I don't think it is.
>
> It certainly is --- he was actually declaring a cast with it in his
> example, no?

That was an attempt at a workaround to get it to do what he wanted.

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

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

Re: [HACKERS] Synchronization levels in SR

[Bruce Momjian]

Simon Riggs wrote:
> On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote:
> 
> > I guess that dropping the support of #3 doesn't reduce complexity
> > since the code of #3 is almost the same as that of #2. Like
> > walreceiver sends the ACK after receiving the WAL in #2 case, it has
> > only to do the same thing after the WAL flush.
> 
> Hmm, well the code for #3 is similar also to the code for #4. So if you
> do #2, its easy to do #2, #3 and #4 together.
> 
> The comment is about whether having #3 makes sense from a user interface
> perspective. It's easy to add options, but they must have useful
> meaning.

If the slave is runing read-only queries, #3 is the most reliable option
withouth delaying the slave, so there is a usecase for #3.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://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] Idea for getting rid of VACUUM FREEZE on cold pages

[Kevin Grittner]

Robert Haas  wrote:
> On Thu, May 27, 2010 at 3:15 PM, Kevin Grittner
 
>> (a)  The tuples were written within the same transaction which
>> created or truncated the table.
 
> In case (a), you mess up visibility with respect to other
> command-IDs within the transaction.
 
Surely that problem is surmountable?
 
-Kevin

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

Re: [HACKERS] JSON manipulation functions

[Tom Lane]

Robert Haas  writes:
> On Thu, May 27, 2010 at 3:35 PM, Tom Lane  wrote:
>> If the function is a cast function (which it is),

> I don't think it is.

It certainly is --- he was actually declaring a cast with it in his
example, no?

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] [9.1] pg_stat_get_backend_server_addr

[Tom Lane]

Peter Eisentraut  writes:
> There are functions pg_stat_get_backend_client_addr and
> pg_stat_get_backend_client_port, which are exposed through the
> pg_stat_activity view, but there is no straightforward way to get the
> server-side address and port of a connection.  This is obviously much
> less commonly needed than the client information,

... indeed.  Is it worth burdening the pg_stats mechanism with this?
The use case seems vanishingly thin.

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] Idea for getting rid of VACUUM FREEZE on cold pages

[Robert Haas]

On Thu, May 27, 2010 at 3:15 PM, Kevin Grittner
 wrote:
> Jesper Krogh  wrote:
>
>> Couldn't pages that are totally filled by the same transaction, be
>
>> frozen on the initial write?
>
> As far as I'm aware, that can only be done if:
>
> (a)  The tuples were written within the same transaction which
> created or truncated the table.
>
> *or*
>
> (b)  The writing transaction and all transactions concurrent to it
> have completed by the time the page is about to be written.

Actually, I think this is true only in case (b).  In case (a), you
mess up visibility with respect to other command-IDs within the
transaction.

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

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

Re: [HACKERS] [RFC] Security label support

[Tom Lane]

Stephen Frost  writes:
>> We also add a dependency between the labeled object and the security
>> label itself. It also enables to clean up orphan labels automatically,
>> without any new invention.

> I agree that we need to address this.  I am kind of curious how this is
> handled for comments?  It appears to be, but I don't see an entry in
> pg_depend when a comment is added to an object, yet the entry in
> pg_description disappears when a table is dropped.  

IIRC, dropping comments is hard-wired into the object drop mechanism ---
this seemed more efficient than having to add a pg_depend entry for each
one.  You could argue that either way of course depending on how many
comments you expect there to be in the system.

I'm not real sure that you want a dependency for a security label anyway
--- wouldn't that mean each label could only be used for one object?

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] functional call named notation clashes with SQL feature

[Peter Eisentraut]

On tor, 2010-05-27 at 12:59 -0400, Tom Lane wrote:
> > I think we should fix it now.  Quick thought: maybe we could use
> FOR 
> > instead of AS: select myfunc(7 for a, 6 for b);
> 
> I'm afraid FOR doesn't work either; it'll create a conflict with the
> spec-defined SUBSTRING(x FOR y) syntax.

How about

select myfunc(a := 7, b := 6);

?


-- 
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] JSON manipulation functions

[Robert Haas]

On Thu, May 27, 2010 at 3:35 PM, Tom Lane  wrote:
> If the function is a cast function (which it is),

I don't think it is.

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

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

Re: [HACKERS] JSON manipulation functions

[Tom Lane]

Joseph Adams  writes:
> I tried making a function named json_type that has the same name as
> the type json_type.  However, this doesn't work as expected:

> SELECT json_type('[1,2,3]');

> Instead of calling json_type with '[1,2,3]' casted to JSON, it's
> trying to cast '[1,2,3]' to json_type.  Is there a way to override
> this behavior, or would I be better off renaming the function?

Well, that might not be the behavior you expected, but that doesn't
make it wrong.  The above is, by convention, equivalent to
'[1,2,3]'::json_type, so it's acting as per convention.

If the function is a cast function (which it is), it *should* be named
after the destination type.  Doing anything else will violate numerous
longstanding expectations.  You might want to read the comments about
function-calls-as-casts in func_get_detail().

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] [9.1] pg_stat_get_backend_server_addr

[Peter Eisentraut]

There are functions pg_stat_get_backend_client_addr and
pg_stat_get_backend_client_port, which are exposed through the
pg_stat_activity view, but there is no straightforward way to get the
server-side address and port of a connection.  This is obviously much
less commonly needed than the client information, but it's still
sometimes useful on hosts with many IP addresses.

I suggest that we add the functions pg_stat_get_backend_server_addr and
pg_stat_get_backend_server_port, but don't expose them in
pg_stat_activity.  (_server_port is really mostly for symmetry, because
you can't currently bind to multiple ports.)

Patch attached.  Comments?

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index d197731..b1f7645 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -838,6 +838,28 @@ postgres: user database host 
 
  
+  pg_stat_get_backend_server_addr(integer)
+  inet
+  
+   The IP address on the given server process that the client is
+   connected to. Null if the connection is over a Unix domain
+   socket. Also null if the current user is not a superuser nor
+   the same user as that of the session being queried
+  
+ 
+
+ 
+  pg_stat_get_backend_server_port(integer)
+  integer
+  
+   The TCP port number on the given server process that the client
+   is connected to.  -1 if the connection is over a Unix domain
+   socket. Null if the current user is not a superuser nor the
+   same user as that of the session being queried
+  
+ 
+
+ 
   pg_stat_get_bgwriter_timed_checkpoints()
bigint

diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index edb5c80..b3a7d82 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -2278,6 +2278,7 @@ pgstat_bestart(void)
 	TimestampTz proc_start_timestamp;
 	Oid			userid;
 	SockAddr	clientaddr;
+	SockAddr	serveraddr;
 	volatile PgBackendStatus *beentry;
 
 	/*
@@ -2296,12 +2297,18 @@ pgstat_bestart(void)
 	/*
 	 * We may not have a MyProcPort (eg, if this is the autovacuum process).
 	 * If so, use all-zeroes client address, which is dealt with specially in
-	 * pg_stat_get_backend_client_addr and pg_stat_get_backend_client_port.
+	 * pg_stat_get_backend_(client|server)_(addr|port).
 	 */
 	if (MyProcPort)
+	{
 		memcpy(&clientaddr, &MyProcPort->raddr, sizeof(clientaddr));
+		memcpy(&serveraddr, &MyProcPort->laddr, sizeof(serveraddr));
+	}
 	else
+	{
 		MemSet(&clientaddr, 0, sizeof(clientaddr));
+		MemSet(&serveraddr, 0, sizeof(serveraddr));
+	}
 
 	/*
 	 * Initialize my status entry, following the protocol of bumping
@@ -2322,6 +2329,7 @@ pgstat_bestart(void)
 	beentry->st_databaseid = MyDatabaseId;
 	beentry->st_userid = userid;
 	beentry->st_clientaddr = clientaddr;
+	beentry->st_serveraddr = serveraddr;
 	beentry->st_waiting = false;
 	beentry->st_appname[0] = '\0';
 	beentry->st_activity[0] = '\0';
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 8379407..9af2b5a 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -56,6 +56,8 @@ extern Datum pg_stat_get_backend_xact_start(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_start(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_client_port(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_backend_server_addr(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_backend_server_port(PG_FUNCTION_ARGS);
 
 extern Datum pg_stat_get_db_numbackends(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS);
@@ -887,6 +889,101 @@ pg_stat_get_backend_client_port(PG_FUNCTION_ARGS)
 
 
 Datum
+pg_stat_get_backend_server_addr(PG_FUNCTION_ARGS)
+{
+	int32		beid = PG_GETARG_INT32(0);
+	PgBackendStatus *beentry;
+	SockAddr	zero_serveraddr;
+	char		local_host[NI_MAXHOST];
+	int			ret;
+
+	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
+		PG_RETURN_NULL();
+
+	if (!superuser() && beentry->st_userid != GetUserId())
+		PG_RETURN_NULL();
+
+	/* A zeroed server addr means we don't know */
+	memset(&zero_serveraddr, 0, sizeof(zero_serveraddr));
+	if (memcmp(&(beentry->st_serveraddr), &zero_serveraddr,
+			   sizeof(zero_serveraddr) == 0))
+		PG_RETURN_NULL();
+
+	switch (beentry->st_serveraddr.addr.ss_family)
+	{
+		case AF_INET:
+#ifdef HAVE_IPV6
+		case AF_INET6:
+#endif
+			break;
+		default:
+			PG_RETURN_NULL();
+	}
+
+	local_host[0] = '\0';
+	ret = pg_getnameinfo_all(&beentry->st_serveraddr.addr,
+			 beentry->st_serveraddr.salen,
+			 local_host, sizeof(local_host),
+			 NULL, 0,
+			 NI_NUMERICHOST | NI_NUMERICSERV);
+	if (ret)
+		PG_RETURN_NULL();
+
+	clean_ipv6_addr(beentry->st_serveraddr.addr.ss_family, local_host);
+
+	PG_RETURN_INET_P(DirectFunctionCall1(inet_in,
+		 CStringGetDatum(local_host)));
+}
+
+Datum
+pg_stat_get_

Re: [HACKERS] Specification for Trusted PLs?

[David Fetter]

On Thu, May 27, 2010 at 11:23:44AM -0400, Bruce Momjian wrote:
> Tom Lane wrote:
> > Joshua Tolley  writes:
> > > Agreed. As long as a trusted language can do things outside the
> > > database only by going through a database and calling some
> > > function to which the user has rights, in an untrusted language,
> > > that seems decent to me. A user with permissions to
> > > launch_missiles() would have a function in an untrusted language
> > > to do it, but there's no reason an untrusted language shouldn't
> > > be able to say "SELECT
> > 
> > s/untrusted/trusted/ here, right?
> 
> One thing that has always bugged me is that the use of
> "trusted/untrusted" for languages is confusing, because it is
> "trusted" users who can run untrusted languages.  I think "trust" is
> more associated with users than with software features.  I have no
> idea how this confusion could  be clarified.

Sadly, I don't think it could short of a time machine.  We're stuck
with an backward convention. :(

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] Idea for getting rid of VACUUM FREEZE on cold pages

[Kevin Grittner]

Jesper Krogh  wrote:
 
> Couldn't pages that are totally filled by the same transaction, be
 
> frozen on the initial write?
 
As far as I'm aware, that can only be done if:
 
(a)  The tuples were written within the same transaction which
created or truncated the table.
 
*or*
 
(b)  The writing transaction and all transactions concurrent to it
have completed by the time the page is about to be written.
 
-Kevin

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

Re: [HACKERS] [RFC] Security label support

[Stephen Frost]

KaiGai,

* KaiGai Kohei (kai...@ak.jp.nec.com) wrote:
> As we talked at the developer meeting on Ottawa, it needs to provide
> a capability to assign a short text identifier on database objects
> to support label based ESP (such as SELinux).
> So, I'd like to propose a few approaches to support security label
> as a draft of discussion.
[...]
> [2] Using OID as a key of text representation in separated catalog
> --
> 
> This idea is similar to pg_description/pg_shdescription.
> A new system catalog pg_seclabel and pg_shseclabel stores text form
> of labels for pair of the relation-Id, object-Oid and object-Subid.
> It does not damage to the schema of existing system catalog,

Right, this is the approach that was agreed to during various
discussions and is, I believe, what Robert is currently working on.

> It adds two new system catalogs; pg_seclabel (local) and pg_shseclabel 
> (shared).

Do we really need a shared catalog initially?  Right now, we're just
talking about labels for tables and columns.  Let's not overcomplicate
this.  We can always add it later.

> We also add a dependency between the labeled object and the security
> label itself. It also enables to clean up orphan labels automatically,
> without any new invention.

I agree that we need to address this.  I am kind of curious how this is
handled for comments?  It appears to be, but I don't see an entry in
pg_depend when a comment is added to an object, yet the entry in
pg_description disappears when a table is dropped.  

> However, it also has a limitation from the viewpoint of long-term.
> >From the definition, OID of database objects are unique. So, we cannot
> share text form of labels even if massive number of database objects
> have an identical security label; it can lead waste of storage consumption
> because of the duplicated security labels. So, this idea shall be switched
> to the [3] when we support row-level security with ESP.
> But I think the idea [2] is reasonable in short-term development.

Row level security might not even use this catalog directly but perhaps
another one.  That's a discussion for a much later time though.

> * SQL Statement
> ---
> 
> It also need to provide SQL statement to manage security label of the database
> object. I plan the following statement to change the security label.
> 
>   ALTER xxx  SECURITY LABEL TO 'label';

Rather than adding more cruft around ALTER, I believe the plan is to add
a new top-level command (eg: 'SECURITY LABEL ON'), just like the COMMENT
ON syntax.

> When the ALTER command is executed, ESP module validate the given label,
> in addition to permission checks to relabel it.

> If no ESP module is available, the ALTER always raises a feature-not-supported
> error.

Right.  We do need to identify what the hook needs to look like.  I
would think just passing a pg_seclabel (or whatever) structure which
would represent the new row in the table (possibly replacing an existing
row, if one exists, but the hook can figure that out).  The hook can
then figure out the user and any other information it needs to know
based on that and either allow or not allow the change.

> In my original SE-PostgreSQL design, it provided an option to specify
> an explicit security label in CREATE xxx statement, but I discarded
> the idea, because the implementation of CREATE statement has much
> variations for each object class (so the patch will be invasive),
> and it is a fungible functionality using ALTER.

As with other things, this could be done in a transaction rather than
cluttering up CREATE, etc, statements.  Supporting a default label for
objects might be something which could be added later.

> * Pg_dump/Pg_restore support
> 

We do need to include some kind of pg_dump/pg_restore support for this,
of course.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Jesper Krogh]

On 27/05/2010, at 20.00, Josh Berkus  wrote:




Well, maybe I'm confused here, but arranging things so that we NEVER
have to visit the page after initially writing it seems like it's
setting the bar almost impossibly high.


That is the use case, though.  What I've encountered so far at 3  
client

sites is tables which are largely append-only, with a few selects and
very few updates (< 2%) on recent data.   In general, once data gets
flushed out of memory, it goes to disk and never gets recalled, and
certainly not written.  Thinks are hunky-dory until we reach
max_freeze_age, at which point the server has to chew through hundreds
of gigabytes of old data just to freeze them, sometimes bringing the
application to a halt in the process.


The data doesn't get in there in " no time" if autovacuum was aware of  
inserts too it would incrementally freeze the table as it grows.


It would still cause it to be read in again but not in a big chunck.

Couldn't pages that are totally filled by the same transaction, be  
frozen on the initial write?


Jesper - given my limited knowledge about how it works.


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

Re: [HACKERS] Why my manualy constructed raw parser tree produce failed to execute?

[Robert Haas]

On Thu, May 27, 2010 at 1:58 PM, Mohammad Heykal Abdillah
 wrote:
> Now to the question, why my manualy constructed list was failed to
> execute? I was pretty sure that my list node was identical with yacc.

Because you have a bug in your code.

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

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

Re: [HACKERS] functional call named notation clashes with SQL feature

[Robert Haas]

On Thu, May 27, 2010 at 2:59 PM, David E. Wheeler  wrote:
> On May 27, 2010, at 11:55 AM, Robert Haas wrote:
>> Or we could use the Finnish word
>> epäjärjestelmällistyttämättömyydellänsäkäänköhän, which I'm pretty
>> sure is not currently used in our grammar.
>
> I thought that was an Icelandic volcano.

No, that's Eyjafjallajökull.

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

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

Re: [HACKERS] pg_trgm

[Robert Haas]

On Thu, May 27, 2010 at 2:01 PM, Peter Eisentraut  wrote:
> On fre, 2010-05-28 at 00:46 +0900, Tatsuo Ishii wrote:
>> > I don't know about Japanese, but the locale approach works just fine for
>> > other agglutinative languages.  I would rather suspect that it is the
>> > trigram approach that might be rather useless for such languages,
>> > because you are going to get a lot of similarity hits for the affixes.
>>
>> I'm not sure what you mean by "affixes".  But I will explain...
>>
>> A Japanese sentence consists of words. Problem is, each word is not
>> separated by space (agglutinative). So most text tools such as text
>> search need preprocess which finds word boundaries by looking up
>> dictionaries (and smart grammer analysis routine). In the process
>> "affixes" can be determined and perhaps removed from the target word
>> group to be used for text search (note that removing affixes is no
>> relevant to locale). Once we get space separated sentence, it can be
>> processed by text search or by pg_trgm just same as Engligh. (Note
>> that these preprocessing are done outside PostgreSQL world). The
>> difference is just the "word" can be consists of non ASCII letters.
>
> I think the problem at hand has nothing at all to do with agglutination
> or CJK-specific issues.  You will get the same problem with other
> languages *if* you set a locale that does not adequately support the
> characters in use.  E.g., Russian with locale C and encoding UTF8:
>
> select similarity(E'\u0441\u043B\u043E\u043D', E'\u0441\u043B\u043E
> \u043D\u044B');
>  similarity
> 
>        NaN
> (1 row)

What I can't help wondering as I'm reading this discussion is -
Tatsuo-san said upthread that he has a problem with pg_trgm that he
does not have with full text search.  So what is full text search
doing differently than pg_trgm?

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

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

Re: [HACKERS] functional call named notation clashes with SQL feature

[David E. Wheeler]

On May 27, 2010, at 11:55 AM, Robert Haas wrote:

> Or we could use the Finnish word
> epäjärjestelmällistyttämättömyydellänsäkäänköhän, which I'm pretty
> sure is not currently used in our grammar.

I thought that was an Icelandic volcano.

Best,

David


-- 
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] functional call named notation clashes with SQL feature

[Robert Haas]

On Thu, May 27, 2010 at 1:27 PM, David E. Wheeler  wrote:
> On May 27, 2010, at 9:59 AM, Tom Lane wrote:
>
>>> I think we should fix it now.  Quick thought: maybe we could use FOR
>>> instead of AS: select myfunc(7 for a, 6 for b);
>>
>> I'm afraid FOR doesn't work either; it'll create a conflict with the
>> spec-defined SUBSTRING(x FOR y) syntax.
>
> How about "ISPARAMVALUEFOR"? That shouldn't conflict with anything.

Or we could use the Finnish word
epäjärjestelmällistyttämättömyydellänsäkäänköhän, which I'm pretty
sure is not currently used in our grammar.

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

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Robert Haas]

On Thu, May 27, 2010 at 2:00 PM, Josh Berkus  wrote:
>> Well, maybe I'm confused here, but arranging things so that we NEVER
>> have to visit the page after initially writing it seems like it's
>> setting the bar almost impossibly high.
>
> That is the use case, though.  What I've encountered so far at 3 client
> sites is tables which are largely append-only, with a few selects and
> very few updates (< 2%) on recent data.   In general, once data gets
> flushed out of memory, it goes to disk and never gets recalled, and
> certainly not written.

We might be able to optimize this case if the transactions are small,
such that they commit before dirtying too large a fraction of
shared_buffers.  We could - at least in theory - teach the bgwriter or
some other process to freeze them before writing them to disk the
first time.  But if the blocks have to be written to disk before
transaction commit it seems to me we're DOA, unless we're willing to
retain arbitrarily large amounts of CLOG.

What might be more practical is to try to find ways to spread out the
I/O so that it doesn't happen all at once in a huge ornery spike.

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

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Robert Haas]

On Thu, May 27, 2010 at 2:17 PM, Josh Berkus  wrote:
> On 5/26/10 6:32 PM, Robert Haas wrote:
>> Hmm, yeah.  Maybe we should freeze when we set PD_ALL_VISIBLE; that
>> might be just as good, and simpler.  Assuming the visibility map is
>> sufficiently crash-safe/non-buggy, we could then teach VACUUM that
>> it's OK to advance relfrozenxid even when doing just a partial vacuum
>> - because any pages that were skipped must contain only frozen tuples.
>>  Previously you've objected to proposals in this direction because
>> they might destroy forensic information, but maybe we should do it
>> anyway.
>
> It would be an improvement, and easier than the various ways of never
> having to visit the pages, which are all fairly intensive.  Given the
> destruction of rollback information, though, we'd probably want a way to
> switch this behaviour on and off as an autovacuum setting.

It's not going to destroy anything that is needed for rollback unless
there's a bug - PD_ALL_VISIBLE only gets set when all tuples on the
page are visible to all backends.  That can't happen until all
transactions that wrote the page, and all others that have a lower
xmin, have committed.  That having been said, if making it a GUC makes
people less nervous about doing it, then +1 from me.

> Does this send us down the wrong path, though?  I thought we wanted to
> think about removing hint bits so that we could implement things like
> CRCs.  No?

PD_ALL_VISIBLE is a page-level bit, not a tuple-level bit, and I
strongly suspect it's not going anywhere.  It's critical
infrastructure for index-only scans, among other things.

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

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

Re: [HACKERS] JSON manipulation functions

[Robert Haas]

On Thu, May 27, 2010 at 2:39 PM, Joseph Adams
 wrote:
> I've started implementing the JSON datatype; the repo is at
> http://git.postgresql.org/gitweb?p=json-datatype.git .
>
> On Fri, May 14, 2010 at 1:15 PM, Robert Haas  wrote:
>> On Thu, May 13, 2010 at 9:47 PM, Joseph Adams
>>  wrote:
>>> Would it be a bad idea to give an enum and a function the same name
>>> (which appears to be allowed by PostgreSQL) ?  If so, json_type(json)
>>> could be json_typeof(json) or something instead.
>>
>> No, I think that's a fine idea.
>
> I tried making a function named json_type that has the same name as
> the type json_type.  However, this doesn't work as expected:
>
> SELECT json_type('[1,2,3]');
>
> Instead of calling json_type with '[1,2,3]' casted to JSON, it's
> trying to cast '[1,2,3]' to json_type.  Is there a way to override
> this behavior, or would I be better off renaming the function?

Well, you should rename either the function or the type, I guess.  Not
sure which.  Calling it json_typeof would be reasonable...

> Note that if the function were renamed, the literal would implicitly be json:
>
> SELECT json_typeof('[1,2,3]'); -- works
>
> I tried this:
>
> CREATE OR REPLACE FUNCTION json_type(json)
> RETURNS json_type
> AS 'MODULE_PATHNAME','json_get_type'
> LANGUAGE C STRICT IMMUTABLE;
>
> CREATE CAST (json AS json_type) WITH FUNCTION json_type(json);
>
> However, json_type('[1,2,3]') still doesn't work (it doesn't infer
> that '[1,2,3]' should be casted to json first).  I also tried each of
> AS ASSIGNMENT and AS IMPLICIT as well.

Yeah, I don't think you want to go that way.

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

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

Re: [HACKERS] JSON manipulation functions

[Joseph Adams]

I've started implementing the JSON datatype; the repo is at
http://git.postgresql.org/gitweb?p=json-datatype.git .

On Fri, May 14, 2010 at 1:15 PM, Robert Haas  wrote:
> On Thu, May 13, 2010 at 9:47 PM, Joseph Adams
>  wrote:
>> Would it be a bad idea to give an enum and a function the same name
>> (which appears to be allowed by PostgreSQL) ?  If so, json_type(json)
>> could be json_typeof(json) or something instead.
>
> No, I think that's a fine idea.

I tried making a function named json_type that has the same name as
the type json_type.  However, this doesn't work as expected:

SELECT json_type('[1,2,3]');

Instead of calling json_type with '[1,2,3]' casted to JSON, it's
trying to cast '[1,2,3]' to json_type.  Is there a way to override
this behavior, or would I be better off renaming the function?

Note that if the function were renamed, the literal would implicitly be json:

SELECT json_typeof('[1,2,3]'); -- works

I tried this:

CREATE OR REPLACE FUNCTION json_type(json)
RETURNS json_type
AS 'MODULE_PATHNAME','json_get_type'
LANGUAGE C STRICT IMMUTABLE;

CREATE CAST (json AS json_type) WITH FUNCTION json_type(json);

However, json_type('[1,2,3]') still doesn't work (it doesn't infer
that '[1,2,3]' should be casted to json first).  I also tried each of
AS ASSIGNMENT and AS IMPLICIT as well.

-- 
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] [ADMIN] command tag logging

[Tom Lane]

Ray Stell  writes:
> On Thu, May 27, 2010 at 12:49:49PM -0400, Tom Lane wrote:
>> That is in the right place, isn't it.  That would suggest that
>> get_ps_display() is returning a wrong length on Ray's machine.
>> It works okay here, but since that's platform-specific code that
>> hardly proves much.  Ray, what platform is this exactly?

> [postgres ~]$ cat /etc/issue
> Red Hat Enterprise Linux Server release 5.5 (Tikanga)

OK, I can reproduce it when I try on my Fedora box.  The problem is that
log_connections emits a log message before init_ps_display() has been
called, and the ps_status.c logic isn't careful to ensure that it
returns something sane in that case.  It accidentally failed to fail
too badly before I changed the elog.c logic, because there'd be an
embedded null after the program name in most cases.  I suppose people
might even have thought that printing "postmaster" or "postgres" for
%i was intended behavior there.

I think the most useful fix is to create a static variable to hold the
notional strlen(ps_buffer), which will initialize to zero, and then
we can use that instead of groveling over the string in get_ps_display.
Should improve performance a tad as well as fixing this problem.
Will work on that.

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] Idea for getting rid of VACUUM FREEZE on cold pages

[Josh Berkus]

On 5/26/10 6:32 PM, Robert Haas wrote:
> Hmm, yeah.  Maybe we should freeze when we set PD_ALL_VISIBLE; that
> might be just as good, and simpler.  Assuming the visibility map is
> sufficiently crash-safe/non-buggy, we could then teach VACUUM that
> it's OK to advance relfrozenxid even when doing just a partial vacuum
> - because any pages that were skipped must contain only frozen tuples.
>  Previously you've objected to proposals in this direction because
> they might destroy forensic information, but maybe we should do it
> anyway.

It would be an improvement, and easier than the various ways of never
having to visit the pages, which are all fairly intensive.  Given the
destruction of rollback information, though, we'd probably want a way to
switch this behaviour on and off as an autovacuum setting.

Does this send us down the wrong path, though?  I thought we wanted to
think about removing hint bits so that we could implement things like
CRCs.  No?

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.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] 9.0beta2 release plans

[Simon Riggs]

On Thu, 2010-05-27 at 11:24 -0400, Tom Lane wrote:

> Current thought among core is to wrap beta2 Thursday June 3 (a week
> from today) for public release on Monday the 7th.  Get those fixes in.

I believe my issues are all sorted, apart from one outstanding patch to
attempt to address the max_standby_delay discussions.

If anybody knows different please ping me directly. I'm going to be in
powersave mode for a few weeks.

-- 
 Simon Riggs   www.2ndQuadrant.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] List traffic

[Josh Berkus]

On 5/27/10 8:38 AM, Greg Stark wrote:
> Lists like -ecpg or -odbc
> would work fine if the traffic warranted them.

A low-traffic list is a feature, not a bug.  Most people don't *like*
subscribing to lists which have 80posts/day.

> But some of the lists we have now are 99% overlap with each other -- I
> claim because the definitions are meaningless. What part of postgres
> discussion (aside from this thread) *don't* relate in some way to SQL?
> Or administration? Or performance? Most performance problems end up
> being solved by adjusting SQL or changing GUCs. 

This is a set theory fallacy.  While most performance issues are
administration issues as well, it is NOT therefore true that most
administration issues are also performance issues.  In fact, I'd say
that the -performance list does an excellent job of sticking to
troubleshooting performance issues only.  And for someone who has a
performance issue, and does not want to field 100 emails about "can't
install Postgre", that's a feature.

> Mot administration
> questions are originally posed as general help questions. If you're
> subscribed to these lists you get a random, fairly small, subset of
> discussion related these topics.

Only someone who is a postgresql developer would consider 15-30
posts/day "small".  For most of our user base, the level of traffic on
-performance, -sql, and -general is already too high and many people
don't subscribe to these lists because it is too high.  I get complaints
-- and people personal-sending me questions because they don't want to
subscribe -- all the time.

Having fewer posts on any particular list is *desireable*.  It's a good
thing.  It's *only* a problem when a bug report or user question goes
unanswered because the list is unattended.  And so far, I've only seen
one report of that.

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.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] pg_trgm

[Peter Eisentraut]

On fre, 2010-05-28 at 00:46 +0900, Tatsuo Ishii wrote:
> > I don't know about Japanese, but the locale approach works just fine for
> > other agglutinative languages.  I would rather suspect that it is the
> > trigram approach that might be rather useless for such languages,
> > because you are going to get a lot of similarity hits for the affixes.
> 
> I'm not sure what you mean by "affixes".  But I will explain...
> 
> A Japanese sentence consists of words. Problem is, each word is not
> separated by space (agglutinative). So most text tools such as text
> search need preprocess which finds word boundaries by looking up
> dictionaries (and smart grammer analysis routine). In the process
> "affixes" can be determined and perhaps removed from the target word
> group to be used for text search (note that removing affixes is no
> relevant to locale). Once we get space separated sentence, it can be
> processed by text search or by pg_trgm just same as Engligh. (Note
> that these preprocessing are done outside PostgreSQL world). The
> difference is just the "word" can be consists of non ASCII letters.

I think the problem at hand has nothing at all to do with agglutination
or CJK-specific issues.  You will get the same problem with other
languages *if* you set a locale that does not adequately support the
characters in use.  E.g., Russian with locale C and encoding UTF8:

select similarity(E'\u0441\u043B\u043E\u043D', E'\u0441\u043B\u043E
\u043D\u044B');
 similarity

NaN
(1 row)



-- 
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] Idea for getting rid of VACUUM FREEZE on cold pages

[Josh Berkus]

> Well, maybe I'm confused here, but arranging things so that we NEVER
> have to visit the page after initially writing it seems like it's
> setting the bar almost impossibly high. 

That is the use case, though.  What I've encountered so far at 3 client
sites is tables which are largely append-only, with a few selects and
very few updates (< 2%) on recent data.   In general, once data gets
flushed out of memory, it goes to disk and never gets recalled, and
certainly not written.  Thinks are hunky-dory until we reach
max_freeze_age, at which point the server has to chew through hundreds
of gigabytes of old data just to freeze them, sometimes bringing the
application to a halt in the process.

The user's perspective on this is quite reasonable: if I haven't
selected these pages, and I haven't written to them, why does autovacuum
need to visit them and screw up my server performance?

>  Consider a table that is
> regularly written but append-only.  Every time autovacuum kicks in,
> we'll go and remove any dead tuples and then mark the pages
> PD_ALL_VISIBLE and set the visibility map bits, which will cause
> subsequent vacuums to ignore the all-visible portions of the table...
> until anti-wraparound kicks in, at which point we'll vacuum the entire
> table and freeze everything.
>
> If, however, we decree that you can't write a new tuple into a
> PD_ALL_VISIBLE page without freezing the existing tuples, then you'll
> still have the small, incremental vacuums but those are pretty cheap,

That only works if those pages were going to be autovacuumed anyway.  In
the case outlined above (which I've seen at 3 different production sites
this year), they wouldn't be; a table with less than 2% updates and
deletes does not get vacuumed until max_freeze_age for any reason.  For
that matter, pages which are getting autovacuumed are not a problem,
period; they're being read and written and freezing them is not an issue.

I'm not seeing a way of fixing this common issue short of overhauling
CLOG, or of creating a freeze_map.  Darn.

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.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

[HACKERS] Why my manualy constructed raw parser tree produce failed to execute?

[Mohammad Heykal Abdillah]

Hi all,

I have try to understand how parser work. So far the raw parser in
PostgreSQL will produce that called "raw parser tree" and the "raw
parser tree" will be passed to "analyzer", right?

I have modified PostgreSQL, so the program wont call function
"raw_parser" -a function container that make by yacc and lex-. The part
that i try to modified was at "src/backend/tcop/postgres.c" function
"pg_parse_query". I have using GDB to see list and node structure (a
parse tree structure that made using yacc) of my working query.

I have constructed same list and node structure as my working query,
using manual code (lit_make1, lappend, makeNode, etc). GDB pprint show
that my manualy constructed list are identically with my working query,
at least when i try to compare it by my eye 1 on 1. But when my manualy
constructed query tree is execute, it produce "error: unrecognized node
type".

Now to the question, why my manualy constructed list was failed to
execute? I was pretty sure that my list node was identical with yacc.

Is there something that i miss when i consctructed my list (perhaps some
list structure part that not printed by GDB)? By the way in GBD i using
"call pprint(node/list name)" to display my list.

Thank You.
-- 
Mohammad Heykal Abdillah 


-- 
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] functional call named notation clashes with SQL feature

[David E. Wheeler]

On May 27, 2010, at 9:59 AM, Tom Lane wrote:

>> I think we should fix it now.  Quick thought: maybe we could use FOR 
>> instead of AS: select myfunc(7 for a, 6 for b);
> 
> I'm afraid FOR doesn't work either; it'll create a conflict with the
> spec-defined SUBSTRING(x FOR y) syntax.

How about "ISPARAMVALUEFOR"? That shouldn't conflict with anything.

Best,

David


-- 
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] Keepalive for max_standby_delay

[Simon Riggs]

On Wed, 2010-05-26 at 16:22 -0700, Josh Berkus wrote:
> > Just this second posted about that, as it turns out.
> > 
> > I have a v3 *almost* ready of the keepalive patch. It still makes sense
> > to me after a few days reflection, so is worth discussion and review. In
> > or out, I want this settled within a week. Definitely need some R&R
> > here.
> 
> Does the keepalive fix all the issues with max_standby_delay?  Tom?

OK, here's v4.

Summary

* WALSender adds a timestamp onto the header of every WAL chunk sent.

* Each WAL record now has a conceptual "send timestamp" that remains
constant while that record is replayed. This is used as the basis from
which max_standby_delay is calculated when required during replay.

* Send timestamp is calculated as the later of the timestamp of chunk in
which WAL record was sent and the latest XLog time.

* WALSender sends an empty message as a keepalive when nothing else to
send. (No longer a special message type for the keepalive).

I think its close, but if there's a gaping hole here somewhere then I'll
punt for this release.

-- 
 Simon Riggs   www.2ndQuadrant.com
*** a/doc/src/sgml/protocol.sgml
--- b/doc/src/sgml/protocol.sgml
***
*** 4222,4247  The commands accepted in walsender mode are:



!   Byten



!   Data that forms part of WAL data stream.



!   




!  
!  
 A single WAL record is never split across two CopyData messages. When
 a WAL record crosses a WAL page boundary, however, and is therefore
 already split using continuation records, it can be split at the page
 boundary. In other words, the first main WAL record and its
 continuation records can be split across different CopyData messages.
   
  

--- 4222,4257 



!   Byte8



!   Message timestamp.



!   
!   
!   Byten
!   
!   
!   
!   Data that forms part of WAL data stream. (May be zero length).




!   
!   
 A single WAL record is never split across two CopyData messages. When
 a WAL record crosses a WAL page boundary, however, and is therefore
 already split using continuation records, it can be split at the page
 boundary. In other words, the first main WAL record and its
 continuation records can be split across different CopyData messages.
+   
+   
+   
+   
   
  

*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***
*** 1938,1944  UpdateMinRecoveryPoint(XLogRecPtr lsn, bool force)
  			UpdateControlFile();
  			minRecoveryPoint = newMinRecoveryPoint;
  
! 			ereport(DEBUG2,
  	(errmsg("updated min recovery point to %X/%X",
  		minRecoveryPoint.xlogid, minRecoveryPoint.xrecoff)));
  		}
--- 1938,1944 
  			UpdateControlFile();
  			minRecoveryPoint = newMinRecoveryPoint;
  
! 			ereport(DEBUG3,
  	(errmsg("updated min recovery point to %X/%X",
  		minRecoveryPoint.xlogid, minRecoveryPoint.xrecoff)));
  		}
***
*** 9210,9218  retry:
  {
  	/*
  	 * While walreceiver is active, wait for new WAL to arrive
! 	 * from primary.
  	 */
! 	receivedUpto = GetWalRcvWriteRecPtr();
  	if (XLByteLT(*RecPtr, receivedUpto))
  	{
  		/*
--- 9210,9218 
  {
  	/*
  	 * While walreceiver is active, wait for new WAL to arrive
! 	 * from primary. Get next applychunk and do other bookkeeping.
  	 */
! 	receivedUpto = GetWalRcvNextApplyChunk();
  	if (XLByteLT(*RecPtr, receivedUpto))
  	{
  		/*
*** a/src/backend/replication/walreceiver.c
--- b/src/backend/replication/walreceiver.c
***
*** 394,410  XLogWalRcvProcessMsg(unsigned char type, char *buf, Size len)
  		case 'w':/* WAL records */
  			{
  XLogRecPtr	recptr;
  
! if (len < sizeof(XLogRecPtr))
  	ereport(ERROR,
  			(errcode(ERRCODE_PROTOCOL_VIOLATION),
  			 errmsg_internal("invalid WAL message received from primary")));
  
  memcpy(&recptr, buf, sizeof(XLogRecPtr));
  buf += sizeof(XLogRecPtr);
  len -= sizeof(XLogRecPtr);
  
! XLogWalRcvWrite(buf, len, recptr);
  break;
  			}
  		default:
--- 394,427 
  		case 'w':/* WAL records */
  			{
  XLogRecPtr	recptr;
+ TimestampTz chunk_timestamp;
  
! if (len < (sizeof(XLogRecPtr) + sizeof(TimestampTz)))
  	ereport(ERROR,
  			(errcode(ERRCODE_PROTOCOL_VIOLATION),
  			 errmsg_internal("invalid WAL message received from primary")));
  
+ /*
+  * Extract starting XLogRecPtr of message header
+  */
  memcpy(&recptr, buf, sizeof(XLogRecPtr));
  buf += sizeof(XLogRecPtr);
  len -= sizeof(XLogRecPtr

Re: [HACKERS] [ADMIN] command tag logging

[Ray Stell]

On Thu, May 27, 2010 at 12:49:49PM -0400, Tom Lane wrote:
> alvherre  writes:
> > Excerpts from Ray Stell's message of mi?? may 26 17:08:33 -0400 2010:
> >> I just installed a compiled from src 8.3.11.  I usually include %i, 
> >> command tag,
> >> in the log_line_prefix setting.  This causes some spewage I'd not seen 
> >> before
> >> on connection received lines as if it is dumping the environment:
> 
> > Hmm, I bet it's the recent %.*s patch.
> 
> That is in the right place, isn't it.  That would suggest that
> get_ps_display() is returning a wrong length on Ray's machine.
> It works okay here, but since that's platform-specific code that
> hardly proves much.  Ray, what platform is this exactly?

I should have included this:

   version  
 
-
 PostgreSQL 8.3.11 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 
20080704 (Red Hat 4.1.2-46)
(1 row)

[postgres ~]$ uname -a
Linux horntail.cns.vt.edu 2.6.18-194.3.1.el5PAE #1 SMP Sun May 2 04:42:25 EDT 
2010 i686 i686 i386 GNU/Linux

[postgres ~]$ cat /etc/issue
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
Kernel \r on an \m

-- 
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] [ADMIN] command tag logging

[Tom Lane]

alvherre  writes:
> Excerpts from Tom Lane's message of jue may 27 12:49:49 -0400 2010:
>> That is in the right place, isn't it.  That would suggest that
>> get_ps_display() is returning a wrong length on Ray's machine.
>> It works okay here, but since that's platform-specific code that
>> hardly proves much.  Ray, what platform is this exactly?

> FWIW it fails for me too (Debian running Linux 2.6.32).

Hmm.  It seems like the %.*s change could only have affected things if
the PS display area contains \0 characters before the theoretical end
of the string.  Which it shouldn't, once we've set the display, but
Ray is only reporting this for log_connection output which might come
out before that.

In any case it strikes me that get_ps_display() is designed on the
assumption that it needn't be particularly fast, but using its result
in log_line_prefix is a place in which performance could indeed matter.
Maybe we should go to some effort to track the intended display string
length explicitly so we could avoid the mucking about in
get_ps_display().

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] functional call named notation clashes with SQL feature

[Tom Lane]

Andrew Dunstan  writes:
> Peter Eisentraut wrote:
>> In systems that have inheritance of composite types, this is used to
>> specify which type the value is supposed to be interpreted as (for
>> example, to treat the value as a supertype).

Why don't they just use CAST() syntax for that, instead of adding this
unnecessary syntax wart?

If their complaint is that CAST() is too much typing, perhaps they
could adopt :: cast notation ;-)

> I think we should fix it now.  Quick thought: maybe we could use FOR 
> instead of AS: select myfunc(7 for a, 6 for b);

I'm afraid FOR doesn't work either; it'll create a conflict with the
spec-defined SUBSTRING(x FOR y) syntax.

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] Exposing the Xact commit order to the user

[Jan Wieck]

On 5/27/2010 9:59 AM, Greg Stark wrote:

On Wed, May 26, 2010 at 5:38 PM, Greg Stark  wrote:

How about just API generalities? Like, where do you need this data, on
the master or on the slave? Would PGXC like it on the transaction
coordinator?

What question do you need to answer, do you need to pull out sets of
commits in certain ranges or look up specific transaction ids and find
out when they committed? Or do you only need to answer which of two
transaction ids committed first?



This thread has been hard to follow for me. Were any of these
questions answered?



Yes.

On 5/26/2010 4:49 PM, Jan Wieck wrote:

On 5/26/2010 12:38 PM, Greg Stark wrote:

> On Wed, May 26, 2010 at 5:10 PM, Jan Wieck  wrote:

>> ... but to answer that request, actually I don't even think we should be
>> discussing API specifics.
>>
> 
> How about just API generalities? Like, where do you need this data, on

> the master or on the slave? Would PGXC like it on the transaction
> coordinator?
> 
> What question do you need to answer, do you need to pull out sets of

> commits in certain ranges or look up specific transaction ids and find
> out when they committed? Or do you only need to answer which of two
> transaction ids committed first?


The question I want answered is

   "what was the order and xid of the next 0..n transactions, that
   committed after transaction X?"

Preferably I would avoid scanning the entire available WAL just to get 
the next n xid's to process.


The proposal assigned a unique serial number (file segment and position 
driven) to each xid and used that for the ordering as well as 
identification of the last known transaction. That is certainly a 
premature implementation detail.


In this implementation it wouldn't even matter if a transaction that was 
recorded actually never made it because it crashed before the WAL flush. 
It would be reported by this "commit order" feature, but there would be 
no traces of whatever it did to be found inside the DB, so that anomaly 
is harmless.



Jan

-- Anyone who trades liberty for security deserves neither liberty nor 
security. -- Benjamin Franklin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers 




--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

--
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] [ADMIN] command tag logging

[alvherre]

Excerpts from Tom Lane's message of jue may 27 12:49:49 -0400 2010:
> alvherre  writes:
> > Excerpts from Ray Stell's message of mié may 26 17:08:33 -0400 2010:
> >> I just installed a compiled from src 8.3.11.  I usually include %i, 
> >> command tag,
> >> in the log_line_prefix setting.  This causes some spewage I'd not seen 
> >> before
> >> on connection received lines as if it is dumping the environment:
> 
> > Hmm, I bet it's the recent %.*s patch.
> 
> That is in the right place, isn't it.  That would suggest that
> get_ps_display() is returning a wrong length on Ray's machine.
> It works okay here, but since that's platform-specific code that
> hardly proves much.  Ray, what platform is this exactly?

FWIW it fails for me too (Debian running Linux 2.6.32).  Adding some
logging to stderr results in this:

psdisp (len 2130) is: “““/pgsql/install/83_rel/bin/postmaster”””
/pgsql/install/83_rel/bin/postmasterPGDATA=/pgsql/install/83_rel/dataORBIT_SOCKETDIR=/home/alvherre/tmp/orbit-alvherreSSH_AGENT_PID=2739GPG_AGENT_INFO=/tmp/gpg-aXAHSs/S.gpg-agent:2704:1SHELL=/bin/bashTERM=xtermXDG_SESSION_COOKIE=e50959452240490c59b0366b96665400-1274967349.87074-853952583HISTSIZE=1TMPDIR=/home/alvherre/tmpGTK_RC_FILES=/etc/gtk/gtkrc:/home/alvherre/.gtkrc-1.2-gnome2WINDOWID=29360152GNOME_KEYRING_CONTROL=/home/alvherre/tmp/keyring-EUoSfgGTK_MODULES=canberra-gtk-moduleUSER=alvherrehttp_proxy=http://localhost:8118XTERM_SHELL=/bin/bashHISTFILESIZE=1LD_LIBRARY_PATH=/pgsql/install/83_rel/libLS_COLORS=no=00:fi=00:di=01;35:ln=01;36:pi=40;33:so=01;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=01;32:*.cmd=01;32:*.exe=01;32:*.com=01;32:*.btm=01;32:*.bat=01;32:*.tar=01;31:*.tgz=01;31:*.tbz2=01;31:*.arc=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lha=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.gz=01;31:*.bz2=01;31:*.bz=01;31:*.tz=01;31:*.rpm=01;31:*.jpg=01
 
;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.xbm=01;35:*.xpm=01;35:*.png=01;35:*.tif=01;35:*.tiff=01;35:SSH_AUTH_SOCK=/home/alvherre/tmp/keyring-EUoSfg/sshTMOUT=0USERNAME=alvherreSESSION_MANAGER=local/perhan:@/tmp/.ICE-unix/2689,unix/perhan:/tmp/.ICE-unix/2689PAGER=lessDESKTOP_SESSION=gnomePATH=/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games:/home/alvherre/bin:/sbin:/usr/sbinGDM_XSERVER_LOCATION=localPX_CONFIG_ORDER=envvarPWD=/home/alvherreEDITOR=vimLANG=es_CL.UTF-8GDM_LANG=es_CL.UTF-8TZ=America/SantiagoGDMSESSION=gnomeHISTIGNORE=ls:bg:fg:cd:exit:XTERM_VERSION=XTerm(256)XTERM_LOCALE=es_CL.UTF-8HISTCONTROL=ignorespace:erasedupsHOME=/home/alvherreSHLVL=1GNOME_DESKTOP_SESSION_ID=this-is-deprecatedno_proxy=localhost,127.0.0.0/8BASH_ENV=/home/alvherre/.bashrcLOGNAME=alvherreLESS=-XRM
 
-x4VISUAL=vimXDG_DATA_DIRS=/usr/share/gnome:/usr/local/share/:/usr/share/:/usr/share/gdm/DBUS_SESSION_BUS_ADDRESS=unix:abstract=/tmp/dbus-TnbbC5PUiR,guid=833f76565b26a89543f6aa42004f_PX_CONFIG
 
_ORDER=WINDOWPATH=7DISPLAY=:0.0HOSTFILE=/home/alvherre/.hostsXAUTHORITY=/home/alvherre/.Xauthority
 LOG:  connection received: host=[local]


Maybe the problem is the PS_PADDING setting?

I patched as below -- obviously the \0 didn't make any difference (it
was the first thing I tried), because the length, as you say, is wrong.

*** log_line_prefix(StringInfo buf)
*** 1615,1621 
--- 1615,1623 
int displen;
  
psdisp = get_ps_display(&displen);
+   fprintf(stderr, "psdisp (len %d) is: “““%s”””\n", displen, 
psdisp);
appendBinaryStringInfo(buf, psdisp, displen);
+   appendStringInfoChar(buf, '\0');
}
break;
case 'r':


-- 
Á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] [ADMIN] command tag logging

[Tom Lane]

alvherre  writes:
> Excerpts from Ray Stell's message of mié may 26 17:08:33 -0400 2010:
>> I just installed a compiled from src 8.3.11.  I usually include %i, command 
>> tag,
>> in the log_line_prefix setting.  This causes some spewage I'd not seen before
>> on connection received lines as if it is dumping the environment:

> Hmm, I bet it's the recent %.*s patch.

That is in the right place, isn't it.  That would suggest that
get_ps_display() is returning a wrong length on Ray's machine.
It works okay here, but since that's platform-specific code that
hardly proves much.  Ray, what platform is this exactly?

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] Straightforward Synchronous Replication

[Robert Haas]

On Thu, May 27, 2010 at 11:50 AM, Simon Riggs  wrote:
> On Thu, 2010-05-27 at 10:11 -0400, Robert Haas wrote:
>> On Thu, May 27, 2010 at 9:08 AM, Simon Riggs  wrote:
>> > * New process: WALAck (on standby)
>> > Reads shared memory to get last received and last applied xlog location
>> > and sends message to WALSync on primary. Loop/Sleep forever.
>>
>> So would WALAck be polling shared memory?  That would increase latency
>> significantly, I think, though perhaps you have a plan for avoiding
>> that?
>
> The backends are going to be released in batches anyway, so I can't see
> how polling makes a difference.
>
> Polling means no waiting, so asynchronous action and higher throughput,
> and with sufficiently high polling rate no significant loss of latency.

I guess what I'm trying to figure out is the part that says
"loop/sleep forever".  That sounds like you wait 50 ms (or some other
interval), then check shared memory to see if anything has changed, if
not you do it again.  That means that up to 49.9 ms (or whatever
interval you picked) could be spent waiting before you realize that
new WAL has been applied, which I suspect will not work out very well.
 On the other hand checking it in a TIGHT loop would mean using up a
whole CPU on an idle system, so that's not practical either.  ISTM
you'd need some kind of signalling system between the startup process
and the WALAck process, so that the startup process can wake WALAck
after applying each bit of WAL (or maybe the startup process knows
about the lowest LSN that WALAck cares about, and wakes it only upon
reaching that point).

> The other plan requires WALReceiver to wait for fsync and apply, which
> seems very likely to suck badly from a latency perspective. While its
> waiting it is also reducing throughout of incoming WAL. It's hard to see
> how that would work well.
>
> You could also do this by avoiding the wait in WALReceiver, but then
> that becomes more like polling anyway.

I'm not sure if I understand this part, so let me try to say it
another way and you can tell me if I've got it right.  I think your
concern is that, during the time that WALReceiver is waiting for one
chunk of WAL to get fsynced, the startup process might finish applying
an earlier chunk of WAL that is of interest to the master.  The ACK
will therefore be delayed until the fsync completes and WALReceiver
can again do other things, like check whether there are any ACKs that
must be sent.  Is that it, or have I missed the boat completely?

>> > The above needs just two parameters at user level
>> > synch_rep = none | recv | apply
>> > synch_rep_timeout = Ns
>> > and an additional parameter in recovery.conf to say whether a standby is
>> > providing the facility for sync replication (as requested by Yeb etc)
>> > (default = yes).
>> >
>> > So this is the same as having quorum = 0 or 1 (boring but simple) and
>> > having sync_rep_timeout_action = commit in all cases (clear behaviour in
>> > failure modes, without need for per-standby parameters).
>>
>> This seems good, but I think we need a little more definition about
>> what happens with sync_rep_timeout expires.
>
> It commits... that is very clear: "sync_rep_timeout_action = commit in
> all cases". Commit is the only viable option, since abort and
> wait-forever both have disadvantages pointed out for them.

So, do we declare the sync server offline at that point and stop
waiting for it, or do we continue waiting for it on every transaction?
 If we declare it dead, what are the criteria for subsequently making
it alive again?

>> > Yes, this is a 3rd design for sync rep, though I think it improves upon
>> > the things I've heard so far from other authors and also includes
>> > feedback from Dimitri, Heikki, Yeb, Alastair. I'm happy to code this as
>> > well, when 9.1 dev starts and a benchmark should be interesting also.
>>
>> It's great that we have so many people who want to implement this
>> feature, or in one case already have.  I'm not sure whose design is
>> best, but I do hope that we can avoid dueling patches.  There are
>> plenty of other good features to work on also.
>
> There is already a patch on SR, yet Masao is discussing another that
> contains what looks to me like very close to nothing of Zoltan's work,
> not even similar ideas. The dueling patches situation looks like it
> already exists to me, though not of my making or encouragement. Even if
> I agreed with everything one of those authors say, there would still be
> two patches.

Oh, I wasn't aware that Fujii Masao's work had progressed as far as an
actual patch yet.

> Considering a variety of design approaches seems like a good idea for an
> important feature, especially when the information is thin and opinions
> run high. It's unlikely that anyone is right about everything, which is
> why I've amalgamated this simple proposal from everything said so far.

Agreed.

> It's easy to add some things if we add them at the start, much harder to
> retrofit the

Re: [HACKERS] List traffic

[Marc G. Fournier]

On Thu, 27 May 2010, Greg Stark wrote:


Sure, if we have distinctions which make sense then having separate
lists makes sense. Linux has separate lists for different drivers,
different parts of the kernel, projects to improve the kernel in
various specific ways (latency, etc). I'm all for having a list
dedicated to infrastructure (oddly named -www here)


Actually, infrastructure is appropriately discussed on -sysadmins ... web 
is on -www ... tends to be a bit of overlap since -sysadmins was added 
later, and prior to that we did discuss on -www ...



since those topics are usually well defined. Lists like -ecpg or -odbc
would work fine if the traffic warranted them.


I don't agree with the comment about 'if traffic warranted them' though 
... the fact that there is very little traffic should be what makes them 
attractive / useful ... you don't have to weed through alot of posts to 
find the odbc/ecpg related ones ...


Perhaps what I'm looking for is a more sensible division that allows 
most of the traffic related to the subtopics to actually go there. It 
would have to be a division so clearcut that anyone who doesn't follow 
could reasonably be blamed for not following etiquette. That's simply 
not true with the current divisions.


how about something -sql vs -tuning ... ?  -tuning replacing -performance, 
which I do agree could be sql *or* server ... where -tuning would be more 
obviously server related ...



Marc G. FournierHub.Org Hosting Solutions S.A.
scra...@hub.org http://www.hub.org

Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org

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

[Tatsuo Ishii]

> So I think a GUC is broken because pg_tgrm has a index opclasses and
> any indexes built using one setting will be broken if the GUC is
> changed.
> 
> Perhaps we need two sets of functions (which presumably call the same
> implementation with a flag to indicate which definition to use). Then
> you can define an index using one or the other and the meaning would
> be stable.

It's worse. pg_trgm has another compile option "IGNORECASE" which
might affect index opclasses.
--
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] Straightforward Synchronous Replication

[Simon Riggs]

On Thu, 2010-05-27 at 10:11 -0400, Robert Haas wrote:
> On Thu, May 27, 2010 at 9:08 AM, Simon Riggs  wrote:
> > * New process: WALAck (on standby)
> > Reads shared memory to get last received and last applied xlog location
> > and sends message to WALSync on primary. Loop/Sleep forever.
> 
> So would WALAck be polling shared memory?  That would increase latency
> significantly, I think, though perhaps you have a plan for avoiding
> that?

The backends are going to be released in batches anyway, so I can't see
how polling makes a difference.

Polling means no waiting, so asynchronous action and higher throughput,
and with sufficiently high polling rate no significant loss of latency.

The other plan requires WALReceiver to wait for fsync and apply, which
seems very likely to suck badly from a latency perspective. While its
waiting it is also reducing throughout of incoming WAL. It's hard to see
how that would work well.

You could also do this by avoiding the wait in WALReceiver, but then
that becomes more like polling anyway.

> > The above needs just two parameters at user level
> > synch_rep = none | recv | apply
> > synch_rep_timeout = Ns
> > and an additional parameter in recovery.conf to say whether a standby is
> > providing the facility for sync replication (as requested by Yeb etc)
> > (default = yes).
> >
> > So this is the same as having quorum = 0 or 1 (boring but simple) and
> > having sync_rep_timeout_action = commit in all cases (clear behaviour in
> > failure modes, without need for per-standby parameters).
> 
> This seems good, but I think we need a little more definition about
> what happens with sync_rep_timeout expires.

It commits... that is very clear: "sync_rep_timeout_action = commit in
all cases". Commit is the only viable option, since abort and
wait-forever both have disadvantages pointed out for them.

> > Yes, this is a 3rd design for sync rep, though I think it improves upon
> > the things I've heard so far from other authors and also includes
> > feedback from Dimitri, Heikki, Yeb, Alastair. I'm happy to code this as
> > well, when 9.1 dev starts and a benchmark should be interesting also.
> 
> It's great that we have so many people who want to implement this
> feature, or in one case already have.  I'm not sure whose design is
> best, but I do hope that we can avoid dueling patches.  There are
> plenty of other good features to work on also.

There is already a patch on SR, yet Masao is discussing another that
contains what looks to me like very close to nothing of Zoltan's work,
not even similar ideas. The dueling patches situation looks like it
already exists to me, though not of my making or encouragement. Even if
I agreed with everything one of those authors say, there would still be
two patches.

Considering a variety of design approaches seems like a good idea for an
important feature, especially when the information is thin and opinions
run high. It's unlikely that anyone is right about everything, which is
why I've amalgamated this simple proposal from everything said so far.

It's easy to add some things if we add them at the start, much harder to
retrofit them. I've shown that some things are easier than has been
said, with fewer parameters and a good case for better performance also.

-- 
 Simon Riggs   www.2ndQuadrant.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] pg_trgm

[Tatsuo Ishii]

> I don't know about Japanese, but the locale approach works just fine for
> other agglutinative languages.  I would rather suspect that it is the
> trigram approach that might be rather useless for such languages,
> because you are going to get a lot of similarity hits for the affixes.

I'm not sure what you mean by "affixes".  But I will explain...

A Japanese sentence consists of words. Problem is, each word is not
separated by space (agglutinative). So most text tools such as text
search need preprocess which finds word boundaries by looking up
dictionaries (and smart grammer analysis routine). In the process
"affixes" can be determined and perhaps removed from the target word
group to be used for text search (note that removing affixes is no
relevant to locale). Once we get space separated sentence, it can be
processed by text search or by pg_trgm just same as Engligh. (Note
that these preprocessing are done outside PostgreSQL world). The
difference is just the "word" can be consists of non ASCII letters.
--
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