Saturday, May 31, 2008

Re: [HACKERS] [GENERAL] Fragments in tsearch2 headline

Index: src/backend/tsearch/dict.c
===================================================================
RCS file: /home/sushant/devel/pgsql-cvs/pgsql/src/backend/tsearch/dict.c,v
retrieving revision 1.5
diff -u -r1.5 dict.c
--- src/backend/tsearch/dict.c 25 Mar 2008 22:42:43 -0000 1.5
+++ src/backend/tsearch/dict.c 30 May 2008 23:20:57 -0000
@@ -16,6 +16,7 @@
#include "catalog/pg_type.h"
#include "tsearch/ts_cache.h"
#include "tsearch/ts_utils.h"
+#include "tsearch/ts_public.h"
#include "utils/builtins.h"


Index: src/backend/tsearch/to_tsany.c
===================================================================
RCS file: /home/sushant/devel/pgsql-cvs/pgsql/src/backend/tsearch/to_tsany.c,v
retrieving revision 1.12
diff -u -r1.12 to_tsany.c
--- src/backend/tsearch/to_tsany.c 16 May 2008 16:31:01 -0000 1.12
+++ src/backend/tsearch/to_tsany.c 31 May 2008 08:43:27 -0000
@@ -15,6 +15,7 @@

#include "catalog/namespace.h"
#include "tsearch/ts_cache.h"
+#include "tsearch/ts_public.h"
#include "tsearch/ts_utils.h"
#include "utils/builtins.h"
#include "utils/syscache.h"
Index: src/backend/tsearch/ts_parse.c
===================================================================
RCS file: /home/sushant/devel/pgsql-cvs/pgsql/src/backend/tsearch/ts_parse.c,v
retrieving revision 1.8
diff -u -r1.8 ts_parse.c
--- src/backend/tsearch/ts_parse.c 16 May 2008 16:31:01 -0000 1.8
+++ src/backend/tsearch/ts_parse.c 31 May 2008 08:09:32 -0000
@@ -485,7 +485,11 @@
addHLParsedLex(HeadlineParsedText *prs, TSQuery query, ParsedLex *lexs, TSLexeme *norms)
{
ParsedLex *tmplexs;
+ ParsedText *prstxt;
TSLexeme *ptr;
+ int startpos, endpos, pos;
+
+ startpos = prs->curwords;

while (lexs)
{
@@ -505,6 +509,29 @@
lexs = tmplexs;
}

+ /* update the corresponding ParsedText */
+ pos = startpos;
+ endpos = prs->curwords;
+ prstxt = &(prs->prstxt);
+ for (ptr = norms; ptr && ptr->lexeme; ptr++)
+ {
+ while (prstxt->curwords >= prstxt->lenwords)
+ {
+ prstxt->lenwords *= 2;
+ prstxt->words = (ParsedWord *) repalloc((void *) prstxt->words, prstxt->lenwords * sizeof(ParsedWord));
+ }
+ if (ptr->flags & TSL_ADDPOS)
+ pos++;
+
+ prstxt->words[prstxt->curwords].len = strlen(ptr->lexeme);
+ prstxt->words[prstxt->curwords].word = palloc( prstxt->words[prstxt->curwords].len);
+ memcpy(prstxt->words[prstxt->curwords].word, ptr->lexeme, prstxt->words[prstxt->curwords].len);
+ prstxt->words[prstxt->curwords].nvariant = ptr->nvariant;
+ prstxt->words[prstxt->curwords].flags = ptr->flags & TSL_PREFIX;
+ prstxt->words[prstxt->curwords].alen = 0;
+ prstxt->words[prstxt->curwords].pos.pos = pos;
+ prstxt->curwords++;
+ }
if (norms)
{
ptr = norms;
Index: src/backend/tsearch/wparser.c
===================================================================
RCS file: /home/sushant/devel/pgsql-cvs/pgsql/src/backend/tsearch/wparser.c,v
retrieving revision 1.9
diff -u -r1.9 wparser.c
--- src/backend/tsearch/wparser.c 12 May 2008 00:00:50 -0000 1.9
+++ src/backend/tsearch/wparser.c 31 May 2008 08:44:01 -0000
@@ -317,6 +317,9 @@
prs.lenwords = 32;
prs.words = (HeadlineWordEntry *) palloc(sizeof(HeadlineWordEntry) * prs.lenwords);

+ prs.prstxt.lenwords = 32;
+ prs.prstxt.words = (ParsedWord *) palloc(sizeof(ParsedWord) * prs.prstxt.lenwords);
+
hlparsetext(cfg->cfgId, &prs, query, VARDATA(in), VARSIZE(in) - VARHDRSZ);

if (opt)
@@ -335,6 +338,11 @@
PG_FREE_IF_COPY(query, 2);
if (opt)
PG_FREE_IF_COPY(opt, 3);
+
+
+ /* prs.prstxt.words are all freed up by make_tsvector itself
+ * so don't need to free it now */
+
pfree(prs.words);
pfree(prs.startsel);
pfree(prs.stopsel);
Index: src/backend/tsearch/wparser_def.c
===================================================================
RCS file: /home/sushant/devel/pgsql-cvs/pgsql/src/backend/tsearch/wparser_def.c,v
retrieving revision 1.14
diff -u -r1.14 wparser_def.c
--- src/backend/tsearch/wparser_def.c 1 Jan 2008 19:45:52 -0000 1.14
+++ src/backend/tsearch/wparser_def.c 31 May 2008 15:14:16 -0000
@@ -1684,18 +1684,176 @@
return false;
}

-Datum
-prsd_headline(PG_FUNCTION_ARGS)
+static void
+mark_fragment(HeadlineParsedText *prs, int highlight, int startpos, int endpos)
{
- HeadlineParsedText *prs = (HeadlineParsedText *) PG_GETARG_POINTER(0);
- List *prsoptions = (List *) PG_GETARG_POINTER(1);
- TSQuery query = PG_GETARG_TSQUERY(2);
+ int i;
+ char *coversep = "...";
+ int coverlen = strlen(coversep);

- /* from opt + start and and tag */
- int min_words = 15;
- int max_words = 35;
- int shortword = 3;
+ for (i = startpos; i <= endpos; i++)
+ {
+ if (prs->words[i].item)
+ prs->words[i].selected = 1;
+ if (highlight == 0)
+ {
+ if (HLIDIGNORE(prs->words[i].type))
+ prs->words[i].replace = 1;
+ }
+ else
+ {
+ if (XMLHLIDIGNORE(prs->words[i].type))
+ prs->words[i].replace = 1;
+ }

+ prs->words[i].in = (prs->words[i].repeated) ? 0 : 1;
+ }
+ /* add cover separators if needed */
+ if (startpos > 0 && strncmp(prs->words[startpos-1].word, coversep,
+ prs->words[startpos-1].len) != 0)
+ {
+
+ prs->words[startpos-1].word = repalloc(prs->words[startpos-1].word, sizeof(char) * coverlen);
+ prs->words[startpos-1].in = 1;
+ prs->words[startpos-1].len = coverlen;
+ memcpy(prs->words[startpos-1].word, coversep, coverlen);
+ }
+ if (endpos-1 < prs->curwords && strncmp(prs->words[startpos-1].word, coversep,
+ prs->words[startpos-1].len) != 0)
+ {
+ prs->words[endpos+1].word = repalloc(prs->words[endpos+1].word, sizeof(char) * coverlen);
+ prs->words[endpos+1].in = 1;
+ memcpy(prs->words[endpos+1].word, coversep, coverlen);
+ }
+}
+
+static void
+mark_hl_fragments(HeadlineParsedText *prs, TSQuery query,int highlight,
+ int num_fragments, int maxcoversize)
+{
+ DocRepresentation* doc;
+ Extention ext;
+ int4 coverlen, doclen;
+ int4 startpos = 0, endpos = 0;
+ QueryRepresentation qr;
+ int4 i, f, numcovers = 0, maxcovers = 32, maxstretch;
+ int4 min, minI = 0;
+ CoverPos *covers = palloc(maxcovers * sizeof(CoverPos));
+ TSVector t = make_tsvector(&(prs->prstxt));
+
+ qr.query = query;
+ qr.operandexist = (bool*) palloc0(sizeof(bool) * query->size);
+
+ /* start generating covers for the query */
+ doc = get_docrep(t, &qr, &doclen);
+ if (!doc)
+ {
+ pfree(qr.operandexist);
+ pfree(covers);
+ /* cannot do anything */
+ return;
+ }
+
+ /* get all covers */
+ MemSet(&ext, 0, sizeof(Extention));
+ while (Cover(doc, doclen, &qr, &ext))
+ {
+ if (numcovers >= maxcovers)
+ {
+ maxcovers *= 2;
+ covers = repalloc(covers, sizeof(CoverPos) * maxcovers);
+ }
+ covers[numcovers].startpos = ext.p;
+ covers[numcovers].endpos = ext.q;
+
+ covers[numcovers].in = 0;
+ covers[numcovers].excluded = 0;
+ numcovers ++;
+ }
+ /* we do not need tsvector any more, free it */
+ if (t)
+ pfree(t);
+
+ /* choose best covers */
+ for (f = 0; f < num_fragments; f++)
+ {
+ min = 9999999;/* XXX - will not display headlines that exceed 9999999 */
+ for (i = 0; i < numcovers; i ++)
+ {
+ coverlen = covers[i].endpos - covers[i].startpos + 1;
+ if (!covers[i].in && !covers[i].excluded && min > coverlen)
+ {
+ min = coverlen;
+ minI = i;
+ }
+ }
+ if (min < 9999999)
+ {
+ covers[minI].in = 1;
+ /* adjust the size of cover
+ * if maxcoversize >= len
+ * then headline from ext.p - (maxcoversize-len)/2 to ext.q + (maxcoverSize-len) /2
+ * if maxcoverSize < len
+ * then headline from ext.p to ext.p + maxcoverSize
+ * (ensures starting lexeme is in the headline)
+ */
+ /* cut down endpos if it crosses maxWords */
+ startpos = covers[minI].startpos;
+ endpos = covers[minI].endpos;
+ coverlen = endpos - startpos + 1;
+
+ if (maxcoversize > coverlen)
+ {
+ /* stretch it to maxwords */
+ maxstretch = maxcoversize;
+
+ /* divide the stretch on both sides of cover */
+ startpos -= (maxstretch - coverlen)/2;
+ endpos += (maxstretch - coverlen)/2;
+ if (startpos < 0)
+ startpos = 0;
+ /* XXX - do we need to check whether endpos crosses the document
+ * the other function would return if the document ends or the
+ * endpos is reached.
+ * Dropping this check for time being
+ */
+ }
+ else if (maxcoversize < coverlen)
+ endpos = startpos + maxcoversize;
+ covers[minI].startpos = startpos;
+ covers[minI].endpos = endpos;
+
+ /* exclude overlapping covers */
+ for (i = 0; i < numcovers; i ++)
+ {
+ if (i != minI &&
+ (covers[i].startpos >= covers[minI].startpos &&
+ covers[i].startpos <= covers[minI].endpos))
+ covers[i].excluded = 1;
+ }
+ }
+ else
+ break;
+ }
+
+ /* Mark the chosen fragments (covers) */
+
+ for (i = 0; i < numcovers; i++)
+ {
+ if (!covers[i].in)
+ continue;
+
+ startpos = covers[i].startpos;
+ endpos = covers[i].endpos;
+
+ mark_fragment(prs, highlight, covers[i].startpos, covers[i].endpos);
+ }
+ pfree(qr.operandexist);
+ pfree(covers);
+}
+static void
+mark_hl_words(HeadlineParsedText *prs, TSQuery query, int highlight, int shortword, int min_words, int max_words)
+{
int p = 0,
q = 0;
int bestb = -1,
@@ -1707,56 +1865,9 @@
curlen;

int i;
- int highlight = 0;
- ListCell *l;
-
- /* config */
- prs->startsel = NULL;
- prs->stopsel = NULL;
- foreach(l, prsoptions)
- {
- DefElem *defel = (DefElem *) lfirst(l);
- char *val = defGetString(defel);
-
- if (pg_strcasecmp(defel->defname, "MaxWords") == 0)
- max_words = pg_atoi(val, sizeof(int32), 0);
- else if (pg_strcasecmp(defel->defname, "MinWords") == 0)
- min_words = pg_atoi(val, sizeof(int32), 0);
- else if (pg_strcasecmp(defel->defname, "ShortWord") == 0)
- shortword = pg_atoi(val, sizeof(int32), 0);
- else if (pg_strcasecmp(defel->defname, "StartSel") == 0)
- prs->startsel = pstrdup(val);
- else if (pg_strcasecmp(defel->defname, "StopSel") == 0)
- prs->stopsel = pstrdup(val);
- else if (pg_strcasecmp(defel->defname, "HighlightAll") == 0)
- highlight = (pg_strcasecmp(val, "1") == 0 ||
- pg_strcasecmp(val, "on") == 0 ||
- pg_strcasecmp(val, "true") == 0 ||
- pg_strcasecmp(val, "t") == 0 ||
- pg_strcasecmp(val, "y") == 0 ||
- pg_strcasecmp(val, "yes") == 0);
- else
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("unrecognized headline parameter: \"%s\"",
- defel->defname)));
- }

if (highlight == 0)
{
- if (min_words >= max_words)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("MinWords should be less than MaxWords")));
- if (min_words <= 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("MinWords should be positive")));
- if (shortword < 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("ShortWord should be >= 0")));
-
while (hlCover(prs, query, &p, &q))
{
/* find cover len in words */
@@ -1877,6 +1988,82 @@
prs->words[i].in = (prs->words[i].repeated) ? 0 : 1;
}

+}
+
+Datum
+prsd_headline(PG_FUNCTION_ARGS)
+{
+ HeadlineParsedText *prs = (HeadlineParsedText *) PG_GETARG_POINTER(0);
+ List *prsoptions = (List *) PG_GETARG_POINTER(1);
+ TSQuery query = PG_GETARG_TSQUERY(2);
+
+ /* from opt + start and and tag */
+ int min_words = 15;
+ int max_words = 35;
+ int shortword = 3;
+ int num_fragments = 0;
+ int highlight = 0;
+ ListCell *l;
+
+ /* config */
+ prs->startsel = NULL;
+ prs->stopsel = NULL;
+ foreach(l, prsoptions)
+ {
+ DefElem *defel = (DefElem *) lfirst(l);
+ char *val = defGetString(defel);
+
+ if (pg_strcasecmp(defel->defname, "MaxWords") == 0)
+ max_words = pg_atoi(val, sizeof(int32), 0);
+ else if (pg_strcasecmp(defel->defname, "MinWords") == 0)
+ min_words = pg_atoi(val, sizeof(int32), 0);
+ else if (pg_strcasecmp(defel->defname, "ShortWord") == 0)
+ shortword = pg_atoi(val, sizeof(int32), 0);
+ else if (pg_strcasecmp(defel->defname, "NumFragments") == 0)
+ num_fragments = pg_atoi(val, sizeof(int32), 0);
+ else if (pg_strcasecmp(defel->defname, "StartSel") == 0)
+ prs->startsel = pstrdup(val);
+ else if (pg_strcasecmp(defel->defname, "StopSel") == 0)
+ prs->stopsel = pstrdup(val);
+ else if (pg_strcasecmp(defel->defname, "HighlightAll") == 0)
+ highlight = (pg_strcasecmp(val, "1") == 0 ||
+ pg_strcasecmp(val, "on") == 0 ||
+ pg_strcasecmp(val, "true") == 0 ||
+ pg_strcasecmp(val, "t") == 0 ||
+ pg_strcasecmp(val, "y") == 0 ||
+ pg_strcasecmp(val, "yes") == 0);
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unrecognized headline parameter: \"%s\"",
+ defel->defname)));
+ }
+
+ if (highlight == 0)
+ {
+ if (min_words >= max_words)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("MinWords should be less than MaxWords")));
+ if (min_words <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("MinWords should be positive")));
+ if (shortword < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("ShortWord should be >= 0")));
+ if (num_fragments < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("NumFragments should be >= 0")));
+
+ if (num_fragments == 0)
+ /* call the default headline generator */
+ mark_hl_words(prs, query, highlight, shortword, min_words, max_words);
+ else
+ mark_hl_fragments(prs, query, highlight, num_fragments, max_words);
+ }
if (!prs->startsel)
prs->startsel = pstrdup("<b>");
if (!prs->stopsel)
@@ -1886,3 +2073,4 @@

PG_RETURN_POINTER(prs);
}
+
Index: src/backend/utils/adt/tsrank.c
===================================================================
RCS file: /home/sushant/devel/pgsql-cvs/pgsql/src/backend/utils/adt/tsrank.c,v
retrieving revision 1.13
diff -u -r1.13 tsrank.c
--- src/backend/utils/adt/tsrank.c 16 May 2008 16:31:01 -0000 1.13
+++ src/backend/utils/adt/tsrank.c 30 May 2008 17:44:01 -0000
@@ -17,6 +17,7 @@

#include "tsearch/ts_type.h"
#include "tsearch/ts_utils.h"
+#include "tsearch/ts_rank.h"
#include "utils/array.h"
#include "miscadmin.h"

@@ -488,14 +489,6 @@
PG_RETURN_FLOAT4(res);
}

-typedef struct
-{
- QueryItem **item;
- int16 nitem;
- uint8 wclass;
- int32 pos;
-} DocRepresentation;
-
static int
compareDocR(const void *va, const void *vb)
{
@@ -507,12 +500,6 @@
return (a->pos > b->pos) ? 1 : -1;
}

-typedef struct
-{
- TSQuery query;
- bool *operandexist;
-} QueryRepresentation;
-
#define QR_GET_OPERAND_EXISTS(q, v) ( (q)->operandexist[ ((QueryItem*)(v)) - GETQUERY((q)->query) ] )
#define QR_SET_OPERAND_EXISTS(q, v) QR_GET_OPERAND_EXISTS(q,v) = true

@@ -524,17 +511,7 @@
return QR_GET_OPERAND_EXISTS(qr, val);
}

-typedef struct
-{
- int pos;
- int p;
- int q;
- DocRepresentation *begin;
- DocRepresentation *end;
-} Extention;
-
-
-static bool
+bool
Cover(DocRepresentation *doc, int len, QueryRepresentation *qr, Extention *ext)
{
DocRepresentation *ptr;
@@ -615,7 +592,7 @@
return Cover(doc, len, qr, ext);
}

-static DocRepresentation *
+DocRepresentation *
get_docrep(TSVector txt, QueryRepresentation *qr, int *doclen)
{
QueryItem *item = GETQUERY(qr->query);
Index: src/include/tsearch/ts_public.h
===================================================================
RCS file: /home/sushant/devel/pgsql-cvs/pgsql/src/include/tsearch/ts_public.h,v
retrieving revision 1.9
diff -u -r1.9 ts_public.h
--- src/include/tsearch/ts_public.h 16 May 2008 16:31:02 -0000 1.9
+++ src/include/tsearch/ts_public.h 31 May 2008 15:10:24 -0000
@@ -14,6 +14,7 @@
#define _PG_TS_PUBLIC_H_

#include "tsearch/ts_type.h"
+#include "tsearch/ts_utils.h"

/*
* Parser's framework
@@ -47,6 +48,7 @@

typedef struct
{
+ ParsedText prstxt;
HeadlineWordEntry *words;
int4 lenwords;
int4 curwords;
@@ -55,6 +57,24 @@
int2 startsellen;
int2 stopsellen;
} HeadlineParsedText;
+/*
+ * headline framework, flow in common to generate:
+ * 1 parse text with hlparsetext
+ * 2 parser-specific function to find part
+ * 3 generateHeadline to generate result text
+ */
+
+typedef struct
+{
+ int4 startpos;
+ int4 endpos;
+ int2 in;
+ int2 excluded;
+} CoverPos;
+
+extern void hlparsetext(Oid cfgId, HeadlineParsedText *prs, TSQuery query,
+ char *buf, int4 buflen);
+extern text *generateHeadline(HeadlineParsedText *prs);

/*
* Common useful things for tsearch subsystem
Index: src/include/tsearch/ts_rank.h
===================================================================
RCS file: src/include/tsearch/ts_rank.h
diff -N src/include/tsearch/ts_rank.h
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ src/include/tsearch/ts_rank.h 30 May 2008 17:44:01 -0000
@@ -0,0 +1,36 @@
+#ifndef __TSRANK_H__
+#define __TSRANK_H__
+
+#include "ts_type.h"
+#include "ts_cache.h"
+
+typedef struct
+{
+ QueryItem **item;
+ int16 nitem;
+ uint8 wclass;
+ int32 pos;
+} DocRepresentation;
+
+typedef struct
+{
+ TSQuery query;
+ bool *operandexist;
+} QueryRepresentation;
+
+typedef struct
+{
+ int pos;
+ int p;
+ int q;
+ DocRepresentation *begin;
+ DocRepresentation *end;
+} Extention;
+
+bool
+Cover(DocRepresentation *doc, int len, QueryRepresentation *qr, Extention *ext);
+
+DocRepresentation *
+get_docrep(TSVector txt, QueryRepresentation *qr, int *doclen);
+
+#endif /* __TSRANK_H__ */
Index: src/include/tsearch/ts_utils.h
===================================================================
RCS file: /home/sushant/devel/pgsql-cvs/pgsql/src/include/tsearch/ts_utils.h,v
retrieving revision 1.15
diff -u -r1.15 ts_utils.h
--- src/include/tsearch/ts_utils.h 16 May 2008 16:31:02 -0000 1.15
+++ src/include/tsearch/ts_utils.h 30 May 2008 23:18:08 -0000
@@ -13,7 +13,7 @@
#define _PG_TS_UTILS_H_

#include "tsearch/ts_type.h"
-#include "tsearch/ts_public.h"
+#include "tsearch/ts_rank.h"
#include "nodes/pg_list.h"

/*
@@ -90,16 +90,6 @@

extern void parsetext(Oid cfgId, ParsedText *prs, char *buf, int4 buflen);

-/*
- * headline framework, flow in common to generate:
- * 1 parse text with hlparsetext
- * 2 parser-specific function to find part
- * 3 generateHeadline to generate result text
- */
-
-extern void hlparsetext(Oid cfgId, HeadlineParsedText *prs, TSQuery query,
- char *buf, int4 buflen);
-extern text *generateHeadline(HeadlineParsedText *prs);

/*
* Common check function for tsvector @@ tsquery
I have attached a new patch with respect to the current cvs head. This
produces headline in a document for a given query. Basically it
identifies fragments of text that contain the query and displays them.

DESCRIPTION

HeadlineParsedText contains an array of actual words but not
information about the norms. We need an indexed position vector for each
norm so that we can quickly evaluate a number of possible fragments.
Something that tsvector provides.

So this patch changes HeadlineParsedText to contain the norms
(ParsedText). This field is updated while parsing in hlparsetext. The
position information of the norms corresponds to the position of words
in HeadlineParsedText (not to the norms positions as is the case in
tsvector). This works correctly with the current parser. If you think
there may be issues with other parsers please let me know.

This approach does not change any other interface and fits nicely with
the overall framework.

The norms are converted into tsvector and a number of covers are
generated. The best covers are then chosen to be in the headline. The
covers are separated using a hardcoded coversep. Let me know if you want
to expose this as an option.

Covers that overlap with already chosen covers are excluded.

Some options like ShortWord and MinWords are not taken care of right
now. MaxWords are used as maxcoversize. Let me know if you would like to
see other options for fragment generation as well.

Let me know any more changes you would like to see.

-Sushant.

On Tue, 2008-05-27 at 13:30 +0400, Teodor Sigaev wrote:
> Hi!
>
> > 1. Why is hlparsetext used to parse the document rather than the
> > parsetext function? Since words to be included in the headline will be
> > marked afterwords, it seems more reasonable to just use the parsetext
> > function.
> > The main difference I see is the use of hlfinditem and marking whether
> > some word is repeated.
> hlparsetext preserves any kind of lexeme - not indexed, spaces etc. parsetext
> doesn't.
> hlparsetext preserves original form of lexemes. parsetext doesn't.
>
> >
> > The reason this is important is that hlparsetext does not seem to be
> > storing word positions which parsetext does. The word positions are
> > important for generating headline with fragments.
> Doesn't needed - hlparsetext preserves the whole text, so, position is a number
> of array.
>
> >
> > 2.
> >> I would prefer the signature ts_headline( [regconfig,] text, tsquery
> >> [,text] )and function should accept 'NumFragments=>N' for default
> >> parser. Another parsers may use another options.
> >
> > Does this mean we want a unified function ts_headline and we trigger the
> > fragments if NumFragments is specified?
>
> Trigger should be inside parser-specific function (pg_ts_parser.prsheadline).
> Another parsers might not recognize that option.
>
> > It seems that introducing a new
> > function which can take configuration OID, or name is complex as there
> > are so many functions handling these issues in wparser.c.
> No, of course - ts_headline takes care about finding configuration and calling
> correct parser.
>
> >
> > If this is true then we need to just add marking of headline words in
> > prsd_headline. Otherwise we will need another prsd_headline_with_covers
> > function.
> Yeah, pg_ts_parser.prsheadline should mark the lexemes to. It even can change
> an array of HeadlineParsedText.
>
> >
> > 3. In many cases people may already have TSVector for a given document
> > (for search operation). Would it be faster to pass TSVector to headline
> > function when compared to computing TSVector each time? If that is the
> > case then should we have an option to pass TSVector to headline
> > function?
> As I mentioned above, tsvector doesn;t contain whole information about text.
>

Re: [HACKERS] Overhauling GUCS

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


In the interest of constructive criticism, here's some
ways I think postgresql.conf could be improved.

* A weak +1 to the #include idea. I'm much more inclined to simply
add a new section to the bottom of the file and use version
control, but I could see ways in which include would be useful.


* Rearrange the settings to put the more "common" ones at
the top, even if it means messing up the categories a bit.
Having 'bonjour_name' at the top, and 'default_statistics_target'
buried way down below is crazy. The sections should be
looked at from a clean perspective: do we need them at all? Are their
better ways to arrange things? What order should they be in?


* Much more verbose comments. The abovementioned default_statistics_target
is a very important settings, but there is zero explanation in the file
of what it is. The only thing we're told is that it ranges from 10 - 1000.
We can do better than that. Users would absolutely love it if each item
had a clear explanation, and it would be well worth a slightly increased
file size. See the postfix main.cf file for a good example of such.


* Remove the confusing "commented out is default" bit entirely. Simply
set each value explicitly. Why should new users have to confront this?:

# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.


* Lose the tabbed indenting, which looks bad, especially for multi-line
comments. Just use spaces.


* Create a tool, or at least a best practices, for controlling and tracking
changes to the file.


* Put some doc URLs in the file, at the minimum per major section. At the
very bare minimum, a real URL at the top.


* Indicate which values can be changed per-session or per-role.


* Fix the disparity between the syntax in the file and the SET interface.
For example, work_mem = 16MB works in the conf file, but you have to write
SET work_mem = '16MB'. Easiest is probably just to quote everything in the conf.


* Lose the post-value, end-of-line comments, they just get in the way when making
changes and make the file harder to read by contributing to the wrap problem.


* I'm tempted by the argument of creating a separate file for the obscure
settings, but I think it would be too much pain, and nobody would ever agree on
which settings are 'important' and which are 'obscure'.


* It might be nice to mention other ways to reload the file, such as
'service postgresql reload', or whatever Windows uses.


* The word 'paramters' is still misspelled. :)


* That whole sentence about changing the parameters as command-line
options needs to go away.


* Since the executable is now named "postgres" (thank goodness we got
rid of "postmaster"), the file should be named 'postgres.conf'. This would
also be a way to quickly distinguish 'old' vs 'new' style conf files if
we end up making major changes to it.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200805311911
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkhB39sACgkQvJuQZxSWSshahQCg4V5QsO34HOhUDoPzT7STcR45
V5UAoPQxkmuk/oCYirTKxMAhV+Kh8Ytz
=7Lgk
-----END PGP SIGNATURE-----

--
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] Overhauling GUCS

On May 31, 2008, at 15:32, Peter Eisentraut wrote:

>> 1. Most people have no idea how to set these.
>
> Could you clarify this? I can't really believe that people are
> incapable of
> editing a configuration file.

I've been using PostgreSQL on and off, mostly on, for almost 10 years.
I still have no idea what 75% of those settings in postgresql.conf
mean or are for. There are an overwhelming number of them. I know that
5-8 of them I always touch, thanks largely to assistance now and then
from Josh Berkus, but the rest are just complexity to me. I don't
doubt that the vast majority of them are useful in one situation or
another, but unless I'm in one of those situations, I really don't
need to see them there and be confused by them.

Does that help?

>> 2. The current postgresql.conf file is a huge mess of 194 options,
>> the
>> vast majority of which most users will never touch.
>
> My opinion has always been that we should provide a default file
> with only the
> essential options instead of all of them. I see this as a the major
> problem,
> because people are overwhelmed and consequently don't set anything.

+1

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] Overhauling GUCS

Josh Berkus wrote:
> Currently, PostgreSQL,conf and our set of GUC configurations suffer from
> 4 large problems:

As we have talked about it before, you know that I agree that the GUC system
could use some improvements. But I'm a bit surprised about some of your
assessments.

> 1. Most people have no idea how to set these.

Could you clarify this? I can't really believe that people are incapable of
editing a configuration file.

> 2. The current postgresql.conf file is a huge mess of 194 options, the
> vast majority of which most users will never touch.

My opinion has always been that we should provide a default file with only the
essential options instead of all of them. I see this as a the major problem,
because people are overwhelmed and consequently don't set anything.

> 3. GUCS lists are kept in 3 different places (guc.c, postgresql.conf,
> and the settings.sgml), which are only synched with each other manually.

While this is not ideal, I can't really see who this is a major problem, at
least from the perspective of the user.

> 4. We don't seem to be getting any closer to autotuning.

True. But how does your proposal address this?

--
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] Overhauling GUCS

On May 31, 2008, at 12:36, Gregory Stark wrote:

> What this sounds like is a sly way to try to get rid of
> postgresql.conf
> entirely and replace it with parameters stored in the database so
> admins would
> adjust the parameters using an SQL syntax rather than a text file.
>
> There are pros and cons of such a system but I think for newbie
> admins that
> would be a thousand times *more* baffling. You would have to learn new
> commands and have no holistic view of what parameters had been set,
> what
> related parameters might exist. You also have no way to keep the
> file in a
> version control system or sync across servers etc.

FWIW, this has not been a barrier to MySQL adoption.

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: [PERFORM] 2GB or not 2GB

"Josh Berkus" <josh@agliodbs.com> writes:

> Simon,
>
>> There is an optimum for each specific sort.
>
> Well, if the optimum is something other than "as much as we can get", then we
> still have a pretty serious issue with work_mem, no?

With the sort algorithm. The problem is that the database can't predict the
future and doesn't know how many more records will be arriving and how out of
order they will be.

What appears to be happening is that if you give the tape sort a large amount
of memory it keeps a large heap filling that memory. If that large heap
doesn't actually save any passes and doesn't reduce the number of output tapes
then it's just wasted cpu time to maintain such a large heap. If you have any
clever ideas on how to auto-size the heap based on how many output tapes it
will create or avoid then by all means speak up.

--
Gregory Stark
EnterpriseDB

http://www.enterprisedb.com

Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

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

Re: [HACKERS] Overhauling GUCS

"Josh Berkus" <josh@agliodbs.com> writes:

> It's my viewpoint based on a lot of user feedback that the current
> postgresql.conf is fundamentally broken and a major roadblock to PostgreSQL
> adoption. This was a point with which there was pretty much universal
> agreement when I talked with people at pgCon.

Actually as a new DBA when I was first starting out with Postgres I found it
very convenient to have all the common parameters in one place where I could
just uncomment and adjust them. Instead of having to search through
documentation and find the default value from which

> 1 & 2) by not having the settings be defined in a 500-line file, new users
> would no longer be baffled by scores of settings which probably don't concern
> them, trying to find the handful of settings which do.

I'm not sure how an empty file is any less "baffling" than one listing the
default value for parameters they don't need yet.

> 3) We'd consolidate the GUC lists down from 3 places to 2, which is one less
> area to synchronize. Magnus and I looked to see if it might be possible to
> generate the docs from the same list, but it's not practical.

This seems like a trivial gain and one which is unlikely to outweigh the pain
of having to massage the info into C data structures.

> 4) By shifting from a model where postgresql.conf is document-formatted and
> hand-edited to one where it's machine generated, it becomes vastly easier to
> write simple utilities to manage these settings. Right now, the big
> "obstacle" to things like SET PERSISTENT is "how to we preseve the
> hand-edited comments in the file" -- and the answer is we *don't.*

What this sounds like is a sly way to try to get rid of postgresql.conf
entirely and replace it with parameters stored in the database so admins would
adjust the parameters using an SQL syntax rather than a text file.

There are pros and cons of such a system but I think for newbie admins that
would be a thousand times *more* baffling. You would have to learn new
commands and have no holistic view of what parameters had been set, what
related parameters might exist. You also have no way to keep the file in a
version control system or sync across servers etc.

> Have you *looked* at postgresql.conf.sample lately, Tom? It's a disaster.
> Maintenance is already difficult, and becoming more so as we add settings.

What difficulties?

--
Gregory Stark
EnterpriseDB

http://www.enterprisedb.com

Ask me about EnterpriseDB's 24x7 Postgres 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] Overhauling GUCS

"David E. Wheeler" <david@kineticode.com> writes:

> I'd love to see these issues resolved. The current postgresql.conf is way over
> the top. Might you have a better idea?

I don't think fiddling with surface issues like the formatting of the
postgresql.conf is productive. Hiding parameters because you don't think
beginners need them is only going to frustrate those people who do need to
adjust them.

What might be productive is picking up a group of parameters and thinking hard
about what they mean in terms of user-visible real-world effects. If they can
be recast in terms of behaviour the user wants instead of internal
implementation details then that would translate into a massive simplification
as well as being easier to explain to users.

I think we do a pretty good job of this already. Witness things like
effective_cache_size -- imagine if this were "nested_loop_cache_hit_rate" for
example, good luck figuring out what to set it to.

The vacuum cost delay factors are probably ripe for such a recast though. I
think we need just one parameter "vacuum_io_bandwidth" or something like that.
The bgwriter parameters might also be a candidate but I'm less certain.

--
Gregory Stark
EnterpriseDB

http://www.enterprisedb.com

Ask me about EnterpriseDB's PostGIS 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: [GENERAL] Converting empty input strings to Nulls

On Sat, 2008-05-31 at 13:40 -0400, Ken Winter wrote:
> I'm looking for a way to trap this bad input at the database level, quietly
> convert the input empty strings to Null, and store the Null in the date
> column. I tried a BEFORE INSERT OR UPDATE trigger evoking this function ...

You can replace your table with a view and use rules to transform the
updates and inserts.

Regards,
Jeff Davis


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

Re: [PERFORM] 2GB or not 2GB

Simon,

> There is an optimum for each specific sort.

Well, if the optimum is something other than "as much as we can get", then we
still have a pretty serious issue with work_mem, no?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

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

Re: [GENERAL] Converting empty input strings to Nulls

Pavel Stehule wrote:
> Hello
>
> 2008/5/31 Ken Winter <ken@sunward.org>:
>> ... but an empty string still evokes the error even before this function is
>> triggered.
>>
>> Is there a way to convert empty strings to Nulls before the error is evoked?
>>
> no - it's not possible. And some "magic" fix in triggers is bad style.

The rule system may be able to handle this transformation (using insert
rules or an updateable view). Getting it to work could be a bit arcane,
though, and having never really delved into the rule system I can't be
of much help.

See:

http://www.postgresql.com.cn/docs/8.3/static/rules.html

http://www.postgresql.com.cn/docs/8.3/static/sql-createrule.html

http://wiki.postgresql.org/wiki/Updatable_views

http://wiki.postgresql.org/wiki/Introduction_to_PostgreSQL_Rules_-_Making_entries_which_can't_be_altered

Personally, though, if at all possible I'd fix the broken client
application. "" is NOT NULL . If you don't fix it, consider at least
clearly documenting the wacky behaviour and if possible applying it only
to an updatable view rather than the base table.

--
Craig Ringer

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

Re: [pgsql-advocacy] Feedback on blog post about Replication Feature decision and its impact

Dirk,

> <a href="http://www.enterprisedb.com/">EnterpriseDB</a> is a well-funded
> database startup whose product builds on PostgreSQL. EnterpriseDB adds
> many "enterprise-readiness" features to the basic PostgreSQL product,
> including database replication, and much more.

The replication-in-core vs. not-in-core has absolutely nothing to do with
EnterpriseDB either way. I think you'd be doing a disservice to your readers
by implying that it does. Or with the GPL. If you want to blog about these
things, maybe break them up into seperate posts?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

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

Re: [HACKERS] Overhauling GUCS

Simon, Tom, Greg,

> Now, that will get a lot easier just by
> virtue of having a smaller config file, but I think that adding something
> into pg_settings that allows saving user-added commentary would be a nice
> step toward some useful standardization on that side of things. It would
> make future automated tools aimed at parsing and generating new files, as
> part of things like version upgrades, a lot easier if there was a standard
> way such comments were handled in addition to the raw data itself.

Hmmm. What about a COMMENT ON SETTING? That seems like it would serve the
purpose ... and make preserving user comments easier than the current
file-conversion approach.

> The other thing I'd like to see make its way into pg_settings, so that
> tools can operate on it just by querying the database, is noting what file
> the setting came from so that you can track things like include file
> usage. I think with those two additions (comments and source file
> tracking) it would even be concievable to clone a working facsimile of
> even a complicated postgresql.conf file set remotely just by reading
> pg_settings.

Hmmm. Any idea how to do this? It sounds like a good idea to me.

> So my viewpoint is that we should be aggressively adding new features,
> yet be conservative in changing existing behaviour: provide options for
> behaves-like-previous-release and keep the administrative interfaces as
> similar as possible between releases.

It's my viewpoint based on a lot of user feedback that the current
postgresql.conf is fundamentally broken and a major roadblock to PostgreSQL
adoption. This was a point with which there was pretty much universal
agreement when I talked with people at pgCon. That is, I beleive that you're
arguing for keeping .conf stable for the 5% of users who understand it and
ignoring the 95% of users who are baffled by it.

At this point, I think we are the only major SQL database without some form of
basic autotuning for the most significant settings (certainly MySQL, Oracle,
and SQL Server have it); we've been able to coast through that because
autotuning features are new in other DBs, but it's going to start to hurt us
pretty soon.

Now, I do believe that we should plan any GUCS overhaul to happen in one
postgresql version rather than phasing it in over multiple versions so that
administrators only need to get used to a new way once.

> > Currently, PostgreSQL,conf and our set of GUC configurations suffer from
> > 4 large problems:
> >
> > 1. Most people have no idea how to set these.
> > 2. The current postgresql.conf file is a huge mess of 194 options, the
> > vast majority of which most users will never touch.
> > 3. GUCS lists are kept in 3 different places (guc.c, postgresql.conf,
> > and the settings.sgml), which are only synched with each other manually.
> > 4. We don't seem to be getting any closer to autotuning.
>
> The proposal doesn't actually solve any of those problems.

OK, let me draw some lines:
1 & 2) by not having the settings be defined in a 500-line file, new users
would no longer be baffled by scores of settings which probably don't concern
them, trying to find the handful of settings which do.

3) We'd consolidate the GUC lists down from 3 places to 2, which is one less
area to synchronize. Magnus and I looked to see if it might be possible to
generate the docs from the same list, but it's not practical.

4) By shifting from a model where postgresql.conf is document-formatted and
hand-edited to one where it's machine generated, it becomes vastly easier to
write simple utilities to manage these settings. Right now, the big
"obstacle" to things like SET PERSISTENT is "how to we preseve the
hand-edited comments in the file" -- and the answer is we *don't.*

> I disagree with doing any of this. It doesn't result in any useful
> reduction in maintenance effort, and what it does do is make it
> impossible to keep control over the detailed layout, formatting,
> commenting etc in a sample postgresql.conf.

Have you *looked* at postgresql.conf.sample lately, Tom? It's a disaster.
Maintenance is already difficult, and becoming more so as we add settings.

Further, you and Simon seem to think that the current "narrative docs inside
the conf file" format has some kind of value which makes things easier for
DBAs. I don't believe it does, and I have yet to meet a *single* new
PostgreSQL user who wasn't confused and intimidated by the file.

> Nor do I think that
> "generate a whole config file from scratch" is going to be a useful
> behavior for tuning problems --- how will you merge it with what
> you had before?

Who's merging? I don't think you get the proposal. The whole "hand-edited"
approach to postgresql.conf should go away. It's not useful, it's not
educational, and it doesn't make PostgreSQL easy to manage.

Further, the lack of an easy way to manage settings via port access to
PostgreSQL is a significant inhibitor to adopting PostgreSQL in environments
with large numbers of servers. See prior arguments by the CPANEL folks about
why they don't support PostgreSQL, which is in turn a major reason why
PostgreSQL web hosting is hard to find.

I agree that editing the data about settings in the guc.c file is not ideal;
Magnus and I discussed that mainly because we wanted to preserve the
translation framework with gettext strings. If someone can think of a better
way to do this part, I'm all ears.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

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

Re: [GENERAL] Converting empty input strings to Nulls

Hello

2008/5/31 Ken Winter <ken@sunward.org>:
> Applications accessing my PostgreSQL 8.0 database like to submit no-value
> date column values as empty strings rather than as Nulls. This, of course,
> causes this PG error:
>
> SQL State: 22007
> ERROR: invalid input syntax for type date: ""
>
> I'm looking for a way to trap this bad input at the database level, quietly
> convert the input empty strings to Null, and store the Null in the date
> column. I tried a BEFORE INSERT OR UPDATE trigger evoking this function ...
>
> CREATE OR REPLACE FUNCTION "public"."empty_string_to_null"()
> RETURNS trigger AS
> $BODY$
> BEGIN
> IF CAST(NEW.birth_date AS text) = '' THEN
> NEW.birth_date = Null;
> END IF;
> RETURN NEW;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
> ... but an empty string still evokes the error even before this function is
> triggered.
>
> Is there a way to convert empty strings to Nulls before the error is evoked?
>
no - it's not possible. And some "magic" fix in triggers is bad style.

you can write own custom type (not in plpgsql) that allows this
behave. You can copy and modify postgre's DateADT implementation.
pgsql/src/backend/utils/adt/date.c

Datum
date_in(PG_FUNCTION_ARGS)
{
char *str = PG_GETARG_CSTRING(0);
DateADT date;
fsec_t fsec;
struct pg_tm tt,
*tm = &tt;
int tzp;
int dtype;
int nf;
int dterr;
char *field[MAXDATEFIELDS];
int ftype[MAXDATEFIELDS];
char workbuf[MAXDATELEN + 1];

// your hack
if (strlen(str) == 0)
PG_RETURN_NULL();

dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype,
MAXDATEFIELDS, &n
if (dterr == 0)
dterr = DecodeDateTime(field, ftype, nf, &dtype, tm,
&fsec, &tzp)
if (dterr != 0)
DateTimeParseError(dterr, str, "date");

switch (dtype)


more: http://www.postgresql.org/docs/8.3/interactive/xtypes.html

I am not sure if in function can return NULL.You should to test it

regards
Pavel Stehule

> ~ TIA
> ~ Ken
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

Re: [HACKERS] Overhauling GUCS

On May 31, 2008, at 09:23, Tom Lane wrote:

>> 1. Most people have no idea how to set these.
>> 2. The current postgresql.conf file is a huge mess of 194 options,
>> the
>> vast majority of which most users will never touch.
>> 3. GUCS lists are kept in 3 different places (guc.c, postgresql.conf,
>> and the settings.sgml), which are only synched with each other
>> manually.
>> 4. We don't seem to be getting any closer to autotuning.
>
> The proposal doesn't actually solve any of those problems.

It solves #2 at least.

> I disagree with doing any of this. It doesn't result in any useful
> reduction in maintenance effort, and what it does do is make it
> impossible to keep control over the detailed layout, formatting,
> commenting etc in a sample postgresql.conf. Nor do I think that
> "generate a whole config file from scratch" is going to be a useful
> behavior for tuning problems --- how will you merge it with what
> you had before?

I'd love to see these issues resolved. The current postgresql.conf is
way over the top. Might you have a better idea?

Thanks,

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] Proposal - Collation at database level

Radek Strnad napsal(a):
> Zdenek Kotala wrote:
>> Radek Strnad napsal(a):
>>
>> <snip>
>>
>>>
>>> I'm thinking of dividing the problem into two parts - in beginning
>>> pg_collation will contain two functions. One will have hard-coded rules
>>> for these basic collations (SQL_CHARACTER, GRAPHIC_IRV, LATIN1, ISO8BIT,
>>> UCS_BASIC). It will compare each string character bitwise and guarantee
>>> that the implementation will meet the SQL standard implemented in
>>> PostgreSQL.
>>> Second one will allow the user to use installed system locales. The set
>>> of these collations will obviously vary between systems. Catalogs will
>>> contain encoding and collation for calling the system locale function.
>>> This will allow us to use collations such as en_US.utf8, cs_CZ.iso88592
>>> etc. if they will be availible.
>>>
>>> We will also need to change the way how strings are compared. Regarding
>>> the set database collation the right function will be used.
>>> http://doxygen.postgresql.org/varlena_8c.html#4c7af81f110f9be0bd8eb2bd99525675

>>>
>>>
>>> This design will make possible switch to ICU or any other implementation
>>> quite simple and will not cause any major rewriting of what I'm coding
>>> right now.
>>
>>
>> Collation function is main point here. How you mentioned one will be
>> only wrapper about strcmp and second one about strcoll. (maybe you
>> need four - char/wchar) Which function will be used it is defined in
>> pg_collation catalog by CREATE COLLATION command. But you need specify
>> name of locale for system locales. It means you need attribute for
>> storing locale name.
>>
> You're right. I've extended pg_collation for system locale columns. In
> the first stage we actually don't need any other catalogs such as
> encoding, etc. and we can build this functionality only on following
> pg_collation catalog. Used collation function (system or built-in) will
> be decided on existing collation name.
>
> CATALOG(pg_collations, ###)
> {
> NameData colname; /* collation name */
> Oid colschema; /* collation schema */
> NameData colcharset; /* character set specification */
> Oid colexistingcollation; /* existing collation */
> bool colpadattribute; /* pad attribute */
> bool colcasesensitive; /* case sensitive */
> bool colaccent; /* accent sensitive */
> NameData colsyslccollate; /* lc_collate */
> NameData colsyslcctype; /* lc_ctype */
> regproc colfunc; /* used collation function */
> } FormData_pg_collations;
>
>
>>> FormData_pg_collations;
>> It would be good to send list of new and modified SQL commands (like
>> CREATE COLLATION) for wide discussion.
>>
> CREATE COLLATION <collation name> FOR <character set specification> FROM
> <existing collation name> [ <pad characteristic> ] [ <case sensitive> ]
> [ <accent sensitive> ] [ LC_COLLATE <lc_collate> ] [ LC_CTYPE <lc_ctype> ]
>
> <pad characteristic> := NO PAD | PAD SPACE
> <case sensitive> := CASE SENSITIVE | CASE INSENSITIVE
> <accent sensitive> := ACCENT SENSITIVE | ACCENT INSENSITIVE
>
> Since you can specify order by in select clause there's no need for
> adding ascending and descending type of collation. They will allways be
> ascending.
>
> DROP COLLATION <collation name>
>
> CREATE DATABASE ... [ COLLATE <collation name> ] ...
>
> ALTER DATABASE ... [ COLLATE <collation name> ] ...
>
>

I think catalog is good. Maybe attributes names colsyslccollate and
colsyslcctype should be more generic because they could be shared with ICU.

But collation function should be specified in CREATE COLLATION command.
Maybe CREATE COLLATION .... [STRCOL <fn name>]


Zdenek

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

[GENERAL] Converting empty input strings to Nulls

Applications accessing my PostgreSQL 8.0 database like to submit no-value
date column values as empty strings rather than as Nulls. This, of course,
causes this PG error:

SQL State: 22007
ERROR: invalid input syntax for type date: ""

I'm looking for a way to trap this bad input at the database level, quietly
convert the input empty strings to Null, and store the Null in the date
column. I tried a BEFORE INSERT OR UPDATE trigger evoking this function ...

CREATE OR REPLACE FUNCTION "public"."empty_string_to_null"()
RETURNS trigger AS
$BODY$
BEGIN
IF CAST(NEW.birth_date AS text) = '' THEN
NEW.birth_date = Null;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

... but an empty string still evokes the error even before this function is
triggered.

Is there a way to convert empty strings to Nulls before the error is evoked?

~ TIA
~ Ken


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

Re: [HACKERS] Packages in oracle Style

Hello

2008/5/31 <coutinho@mondriantecnologia.com>:
>
> I have implemented a solution for global variables implemented in plpython and I need implement this in c :)
>
> My implementation:
>
> select set_session('USER','coutinho');
> select set_session('EMAIL','coutinho@mondriantecnologia.com');
>
> select get_session('USER');
>> coutinho
>
> select get_session('EMAIL');
>> coutinho@mondriantecnologia.com
>
>

this is too simple :( data are stored in text format, not in native Datum format

Regards
Pavel Stehule

> On Wed, 28 May 2008 22:13:31 +0200, "Pavel Stehule" <pavel.stehule@gmail.com> wrote:
>> 2008/5/27 Zdenek Kotala <Zdenek.Kotala@sun.com>:
>>> Coutinho napsal(a):
>>>>
>>>> this is listed on TODO:
>>>> http://www.postgresql.org/docs/faqs.TODO.html
>>>>
>>>> Add features of Oracle-style packages (Pavel)
>>>>
>>
>> My last idea was only global variables for plpgsql. It needs hack of
>> plpgsql :(. But it's can be simple work.
>>
>> Pavel
>>
>>>
>>> I see. Sorry I overlooked it. I think Pavel Stehule will help you. He
>> has
>>> idea how to do it.
>>>
>>> Zdenek
>>>
>>> --
>>> 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: [BUGS] what are the ways to avoid --- "ERROR: EXECUTE of SELECT ... INTO is not implemented yet"


Here is my real use case

1) We have nornal SELECT ...INTO calls in the procedure calls
2) However we landed ourselves with the following problem
http://www.postgresql.org/docs/faqs.FAQ.html#item4.19

<snip>

4.19) Why do I get "relation with OID ##### does not exist" errors when accessing temporary tables in PL/PgSQL functions?

In PostgreSQL versions < 8.3, PL/PgSQL caches function scripts, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that table is later dropped and recreated, and the function called again, the function will fail because the cached function contents still point to the old temporary table. The solution is to use EXECUTE for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time.

This problem does not occur in PostgreSQL 8.3 and later.

</snip>

3) So the solution we are trying to implement is the EXECUTE command one.

This is causing the SELECT ...INTO problem


Upgrade is not an option :(

Currently the only available solution is
Use a temporary table where we write the local variable and make it read from the table .

Any alternatives ?

~
Shantanu

On Sat, May 31, 2008 at 10:13 AM, Craig Ringer <craig@postnewspapers.com.au> wrote:
Shantanu wrote:

ERROR:  EXECUTE of SELECT ... INTO is not implemented yet

PostgreSQL 8.3 includes support for EXECUTE ... INTO, so you may want to look at an upgrade.

Personally I wouldn't call this a bug at all. It's just that PostgreSQL knew how to parse that query before the support for actually executing it was written.

--
Craig Ringer

Re: [BUGS] what are the ways to avoid --- "ERROR: EXECUTE of SELECT ... INTO is not implemented yet"

Here is my real use case

1) We have nornal SELECT ...INTO calls in the procedure calls
2) However we landed ourselves with the following problem
http://www.postgresql.org/docs/faqs.FAQ.html#item4.19

<snip>

4.19) Why do I get "relation with OID ##### does not exist" errors when accessing temporary tables in PL/PgSQL functions?

In PostgreSQL versions < 8.3, PL/PgSQL caches function scripts, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that table is later dropped and recreated, and the function called again, the function will fail because the cached function contents still point to the old temporary table. The solution is to use EXECUTE for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time.

This problem does not occur in PostgreSQL 8.3 and later.

</snip>

3) So the solution we are trying to implement is the EXECUTE command one.

This is causing the SELECT ...INTO problem


Upgrade is not an option :(

Currently the only available solution is
Use a temporary table where we write the local variable and make it read from the table .

Any alternatives ?

~
Shantanu


On Sat, May 31, 2008 at 8:38 AM, <tomas@tuxteam.de> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, May 30, 2008 at 09:57:49PM +0530, Shantanu wrote:
> Hello experts,
>
>
> I am facing this error.
>
> mydb=> select version();
>                                             version
>
> --------------------------------------------------------------------------------
>
>  PostgreSQL 8.1.9
>
> (1 row)
>
> mydb=> \i /tmp/test.sql
> CREATE FUNCTION
> mydb=> select sp_test();
> ERROR:  EXECUTE of SELECT ... INTO is not implemented yet
> CONTEXT:  PL/pgSQL function "sp_test" line 4 at execute statement
>
>
> mydb=>
> [1]+  Stopped                 su - dbu
> mymachine<root># cat /tmp/test.sql
> create or replace function sp_test() RETURNS void as $$
> declare
>         l_var timestamp;
> begin
> execute 'SELECT NOW() INTO l_var';
 ^^^^^^^^^^^^^^^^^^

Why do you need execute '...' at all?  Why not directly do instead

 SELECT NOW() into l_var;

What is your "real" use case?

Note that if you really need the dynamic command, you might put its
result int a variable, like so:

 EXECUTE 'SELECT NOW()' INTO l_var;

...but if we don't know what you are trying to achieve, it's difficult
to provide meaningful recommendations.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIQMEjBcgs9XrR2kYRAnaWAJ9Mpu/qvqzi911yiftbe3lLYZiyLgCfcWIl
TKs78mgZiKFRZcHnmHMQnOw=
=I2iD
-----END PGP SIGNATURE-----

Re: [pgsql-es-ayuda]

El día 31 de mayo de 2008 11:50, kervin licett <kerljose@gmail.com> escribió:
> Necesito ayuda porque mi tesis voy a trabajar en postgres y quiero trabajar
> con phppgadmin y de verdad cuando me conecto al servidor no reconoce el
> usuario y password que tiene portgres cuando lo instalo desde synaptic por
> defecto cual es la clave
>

por defecto no tiene ninguna password, asumo que usas una distro con
sabor a Debian, si es ubuntu en una consola digita
#sudo su - postgres
#psql

con esto entraras al interprete psql de postgresql asi sabras que tu
instalación de postgresql esta correcta y solo te faltara editar el
archivo pg_hba.conf para poder usar phppgadmin.

saludos.-
--
TIP 7: no olvides aumentar la configuración del "free space map"

Re: [HACKERS] Overhauling GUCS

Josh Berkus <josh@agliodbs.com> writes:
> Currently, PostgreSQL,conf and our set of GUC configurations suffer from
> 4 large problems:

> 1. Most people have no idea how to set these.
> 2. The current postgresql.conf file is a huge mess of 194 options, the
> vast majority of which most users will never touch.
> 3. GUCS lists are kept in 3 different places (guc.c, postgresql.conf,
> and the settings.sgml), which are only synched with each other manually.
> 4. We don't seem to be getting any closer to autotuning.

The proposal doesn't actually solve any of those problems.

> Here's a list of the things we want to change. It's all a package and
> should make sense if you take all the changes as a whole.

> 1) Add several pieces of extra information to guc.c in the form of extra
> "gettext" commands: default value, subcategory, long description,
> recommendations, enum lists.
> 2) Incorporate this data into pg_settings
> 3) Delete postgresql.conf.sample
> 4) Add a script called pg_generate_conf to generate a postgresql.conf
> based on guc.c and command-line switches (rather than
> postgresql.conf.sample), which would be called automatically by initdb.

I disagree with doing any of this. It doesn't result in any useful
reduction in maintenance effort, and what it does do is make it
impossible to keep control over the detailed layout, formatting,
commenting etc in a sample postgresql.conf. Nor do I think that
"generate a whole config file from scratch" is going to be a useful
behavior for tuning problems --- how will you merge it with what
you had before?

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: [BUGS] BUG #4216: Index scan goes wrong with crosstype comparison and between in one statement

"Sokolov Yura aka "funny_falcon"" <funny.falcon@gmail.com> writes:
> I've created a new type - time_interval, define operators on it and
> timestamp, add those operators into OPERATOR FAMILY datetime_ops USING
> btree;

> When I query a table using BETWEEN and equality timestamp = time_interval
> (which means timestamp included in time_interval) then statement gives
> strange results.

I think your operator class is broken. You can't just invent some
randomly-defined operators that work sort of like a normal scalar
ordering and then expect btree indexes to work. In particular,
this set of operators violates the transitive law when taken together
with regular timestamp equality: for two timestamps T1 and T2 and
some interval TI, we can have T1 = TI and T2 = TI from which it
should follow that T1 = T2, yet that does not follow. But by putting
this notion of "equality" into the same opfamily as timestamp equality,
you are promising the system that it *does* follow. I didn't bother
digging through the code to see exactly where that assumption comes into
play, but that's basically what's biting you.

regards, tom lane

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

Re: [HACKERS] proposal: Preference SQL

Jan,

;-)  ... agree with your points.  Note I wasn't trying to duplicate the full functionality of the preferences clause.  ...just pointing out that the example presented could be duplicated with standard SQL with the result being both ugly and confusing. 

My intent was show that the syntax you proposed was easier to read and more flexible.

Kevin




> Date: Sat, 31 May 2008 16:53:54 +0200
> From: j.urbanski@students.mimuw.edu.pl
> To: skywalkereast@hotmail.com
> CC: srb@cuci.nl; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] proposal: Preference SQL
>
> Kevin Walker wrote:
> > Yes, the preference clause can be rewritten using standard SQL. The syntax to duplicate the example result set is listed below. The syntax is not very flexible or easy to read.
> >
> > se lect id
> > from computer
> > where (main_memory = (select max(main_memory)
> > from computer)
> > and cpu_speed = (select max(cpu_speed)
> > from computer
> > where cpu_speed < (select max(cpu_speed) from computer)))
> > or (cpu_speed = (select max(cpu_speed)
> > from computer)
> > and main_memory = (select max(main_memory)
> > from computer
> > where main_memory < (select max(main_memory) from computer)))
>
> Well, that's not 100% correct, but the idea is something like this. In
> particular, if you'd have only one entry in the table, then this query
> would not return any rows, which would be wrong. Also, if you had a
> computer that has larger main memory and a faster CP U than any other
> copmuter, it should be returned as the result, but the above query would
> fail to do that.
>
> The point is not rewriting that particular preference query into a
> standard query. The point is whether it's worth having an automated
> mechanism for executing arbitrary preference queries with complex
> preferences (again: the syntax is richer, I didn't want to go into any
> detail about it before getting some feedback).
>
> Let me give you a more sophisticated example.
> You have a webpage that sells used cars. You have your typical search
> form with car make, colour, engine power and so on. Normally, you would
> make the search form input fields correspond to SQL WHERE clauses. So,
> if I want a white Honda with a 180 hp engine and about 40k kilometers of
> mileage I enter these parameters and hit the submit button. Now imagine
> I don't get any re sults for my search. That could mean that you have no
> Honda cars in stock, but it can also mean that you have my perfect Honda
> at a bargain price, it's only that it's black. Or maybe you have a
> Honda, but it has a 160 hp engine? Or is it just that the one perfect
> Honda you have has a mileage of just over 41k km, and that's why I
> didnt' get it in my result set? People seldom want a perfect match when
> they are searching for something. They want the best match, they can
> get. So, if I wanted to get a Honda with a decent engine and my
> favourite color is white, I'd say:
>
> SELECT * FROM cars WHERE make = 'Honda' PREFERRING (power = '180' AND
> mileage AROUND 40000) CASCADE color = 'white';
>
> Remember, that an AND in a preference clause constructs a partial order.
> The query says: I equally prefer having a 180 hp engine and having a car
> that has a mileage of 40k km. Tha CASCADE clause intrudoces a less
> important preference. It means that the color is not as important to me
> as power and mileage, but if I had a choice I'd take the white one.
>
> I'd strongly recommend skimming through the paper I mentioned in my
> first email, it explains stuff much better than I do.
>
> Preference SQL in Postgres could, as I see it, become one of the
> distinct features that no other widespread database system has and that
> could potentially be massively useful for online shops, social networks,
> etc. - you name it.
> After hearing Bruce's keynote at PGCon and how Postgres now should be
> aiming at more that just catching up with the big guys I just thought:
> well, that's one neat feature that none of them has, that's useful,
> that's kind of sexy *and* I could get my degree out of it.
>
> Cheers,
> Jan
>
> --
> Jan Urbanski
> GPG key ID: E583D7D2
>
> ouden estin


E-mail for the greater good. Join the i'm Initiative from Microsoft.

Re: [PERFORM] Statistics issue

Vlad Arkhipov <arhipov@dc.baikal.ru> writes:
> EXPLAIN ANALYZE
> SELECT *
> FROM i
> WHERE d BETWEEN '2007-05-12' AND '2007-05-12'

> Index Scan using i_d on i (cost=0.00..2.39 rows=1 width=402) (actual
> time=0.053..4.284 rows=1721 loops=1)
> Index Cond: ((d >= '2007-05-12'::date) AND (d <= '2007-05-12'::date))
> Total runtime: 6.645 ms

> EXPLAIN ANALYZE
> SELECT *
> FROM i
> WHERE d = '2007-05-12'

> Index Scan using i_d on i (cost=0.00..38.97 rows=1572 width=402)
> (actual time=0.044..4.250 rows=1721 loops=1)
> Index Cond: (d = '2007-05-12'::date)
> Total runtime: 6.619 ms

Hmm, I wonder whether we shouldn't do something like this
http://archives.postgresql.org/pgsql-committers/2008-03/msg00128.php
for all range conditions, not just those made up by
prefix_selectivity().

regards, tom lane

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

[pgsql-es-ayuda]

Necesito ayuda porque mi tesis voy a trabajar en postgres y quiero trabajar con phppgadmin y de verdad cuando me conecto al servidor no reconoce el usuario y password que tiene portgres cuando lo instalo desde synaptic por defecto cual es la clave
 

[HACKERS] Where can I find the doxyfile?

Hi,
I don't know where I can find the doxyfile which generate
"doxygen.postgresql.org" web site. I found that when reading code the
doxygen source code is quite helpful. However, I want to generate an
off-line copy of doxygen docs myself, but I can't find the doxyfile in
the lastest source release.
Thanks!

--
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] Packages in oracle Style

coutinho@mondriantecnologia.com wrote:
> I have implemented a solution for global variables implemented in plpython and I need implement this in c :)

The below listed tarball is out of date at this point, but I have
updated code laying around if someone really wanted it:

http://www.joeconway.com/sessfunc.tar.gz
I've used variations of this over the years on several projects.

I've also (mis)used custom configs, e.g. in a plpgsql function:

8<----------------------------
EXECUTE 'set myvars.var1 to ''' || p_var1 || '''';
8<----------------------------

and in your "get session var" C function:

8<----------------------------
#define GET_SESSVAR_BY_NAME(SESS_VAR_NAME) \
do { \
SESS_VAR_NAME = GetConfigOptionByName("myvars." #SESS_VAR_NAME, \
NULL); \
if (!SESS_VAR_NAME) \
elog(ERROR, "Missing session variable: " #SESS_VAR_NAME); \
} while (0)

char *var1 = GET_SESSVAR_BY_NAME(var1);
8<----------------------------

Joe

--
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] proposal: Preference SQL

Heikki Linnakangas wrote:
> Jan Urbański wrote:
>> SELECT * FROM computers
>> PREFERRING HIGHEST(main_memory) AND HIGHEST(cpu_speed);
>
> This seems similar to the SKYLINE OF patch that was discussed a year (?)
> ago. Are you familiar with that project? Can you summarize the differences?

Oh, I wasn't familiar with it. After reading through the archives - it
it very similar. I guess what some people are calling PREFERRING, others
are calling SKYLINE OF. There may be subtle differences though, I'll
post a comparision after getting some more info on it.

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin


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

Re: [BUGS] problems compiling in 64 bits

"Gerardo Antonio" <oyogimalandro@gmail.com> writes:
> I am trying to compile Postgres 8.1.9 in the following Linux version:

> Linux version 2.6.9-34.EL (bhcompile@altix2.build.redhat.com) (gcc version
> 3.4.5 20051201 (Red Hat 3.4.5-2))

> but I get a bunch of errors saying "

> i386 architecture of input file
> `../../src/port/libpgport_srv.a(pgstrcasecmp_srv.o)' is incompatible with
> ia64-elf64 output"

There's something screwed up about your build environment. You might
want to check that you do not have values for CFLAGS, LDFLAGS, CPPFLAGS
etc in your environment variables (if you do, get rid of them and
restart from the configure step). The only other cause I can think of
offhand is having the wrong compile/link toolchain installed.

It might be helpful for you to compare your build logs with those
from a similar machine in our buildfarm, eg

http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=dungbeetle&br=REL8_1_STABLE
Look for differences in the flags that the compiler/linker get
invoked with.

regards, tom lane

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

Re: [HACKERS] Overhauling GUCS

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Many are dismayed that every time they do we've fiddled with the
> knobs so some don't work anymore, some are missing or renamed and
> there's a few new ones.
...
> * We should rename effective_cache_size

*raises eyebrow*

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200805311114
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkhBa3UACgkQvJuQZxSWSsg6pACgu2EJOyzj+ik79QwzdTTfi8Qj
aW0AoNayz4sniFVFxnQFgnxh7dPB6QOD
=Uyap
-----END PGP SIGNATURE-----


--
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] Overhauling GUCS

Simon Riggs wrote:
> On Fri, 2008-05-30 at 17:34 -0700, Josh Berkus wrote:

> We have many supporters, though 90% of them seldom touch the database
> from one release to the next. Many are dismayed that every time they do
> we've fiddled with the knobs so some don't work anymore, some are
> missing or renamed and there's a few new ones.

I certainly agree we don't want to make it more difficult.

>
> So if they don't know
> what they're doing it is frequently because we keep moving the
> goalposts.

No, its because they don't read the docs. We are not talking about
dumping SQL here :) I don't feel it is correct to not perform such a
needed cleanup for fear that some user can't be bothered to read the
documentation.

> We should also consider that most people with multiple databases are
> running multiple versions of PostgreSQL. The main reason for that is
> that we keep changing the behaviour of SQL between releases, so even if
> you had a magic superfast upgrade utility, in perhaps 50% of cases they
> won't use it because they have to do a full application re-test, which
> costs time and money.

This could certainly be a problem.

>
> Trying to be a Postgres DBA is hard when each new release is configured
> differently to the last one and we have a major release about 3-5 more
> frequently than Oracle/SQLServer. That is probably largest source of
> questions and annoyance from the students on the courses I run.

That is the nature of the beast though. We are still learning,
improving, engineering. It's part of progress of the database. I would
suggest that your students (which is what I tell mine) not upgrade every
release but instead try hanging out for 3 years per release.

>
> So my viewpoint is that we should be aggressively adding new features,
> yet be conservative in changing existing behaviour: provide options for
> behaves-like-previous-release and keep the administrative interfaces as
> similar as possible between releases.
>

I agree with this in principle but not on this argument.

> If you wish to make changes, I would suggest that you simply reorder
> some of the parameters in the .conf file, so that the ones you think are
> important are grouped at the top.

IMO, the only settings that should be in the postgresql.conf are the
ones that require a restart. The rest should be gathered from pg_settings.

>
> Another suggestion would be to allow a #include style interface within
> postgresql.conf. We can then do this:
>
> #include standard_postgresql.conf
>
> # now we put Josh's favourite GUCs as overrides on the above
> ...

I think that could get very messy but Apache allows this. It isn't
exactly a new idea and I wouldn't fight against it.

> Some other problems I see with GUCs
>
> * It's not possible to set one parameter depending upon the setting of
> another.

That is getting a bit more complicated than I think we need. Unless I am
misunderstanding what you are saying.

>
> * It's always unclear which GUCs can be changed, and when. That is much
> more infrequently understood than the meaning of them.
>

This is a definite problem. Even the docs are a bit difficult on this
one. It should be a nice simple grid :) or like I said above, only goes
in the conf if requires a restart.

Hmm,

SET effective_cache_size = '5000MB';
WARNING: You must issue a reload to the database for this to take effect.

> * We should rename effective_cache_size to something that doesn't sound
> like it does what shared_buffers does

Hmmm, I wonder if it is not the other way around. Although I know that
one thing I run into is that a lot of people think effective_cache is an
allocation.

shared_buffer_alloc?
shared_mem?

>
> * There is no config verification utility, so if you make a change and
> then try to restart and it won't, you are in trouble.
>

+1 +1

Sincerely,

Joshua D. Drake


--
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] Core team statement on replication in PostgreSQL

Merlin Moncure wrote:
> On Sat, May 31, 2008 at 2:18 AM, Mike Rylander <mrylander@gmail.com> wrote:
>> On Fri, May 30, 2008 at 6:47 PM, Andreas 'ads' Scherbaum

> Compression especially is going to negate one of the big advantages of
> wal shipping, namely that it is cheap investment in terms of load to
> the main. A gigabit link can ship a lot of log files, you can always

Who has a gigabit link between Dallas and Atlanta? That is the actual
problem here. Switch to Switch compression is a waste of time (if you
aren't running GiGE, what are you doing???).

Sincerely,

Joshua D. Drake

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

Re: [GENERAL] Mediawiki 1.10 and PG 8.3 upgrade

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> If upgrading MediaWiki to a version that supports postgres 8.3,
> another option could be to export from the old db as XML (using the
> Special:Export page)

You can also use the dumpBackup.php tool in the maintenance directory.
The XML this dumped does not include the mwusers table, so it's
not entirely helpful.

I spoke too soon when I said earlier all one needs is php update.php.
There are some serious tsearch2 issues that prevent you from dumping
the old database and importing into the new version, even with the
tsearch2 compatibility stuff. I'm creating some workarounds, and
will report back. Doing a --data-only dump and creating the schema
anew from maintenance/postgres/tables.sql should work, although
not if you've made any schema changes, obviously.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200805311103
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkhBaX4ACgkQvJuQZxSWSsg2lgCfWn5bPrgZFfp+Tf/rZFlqSEIm
AB8AoKfl/ooepXLbjzh3ql4GGuk86MJT
=yQ9w
-----END PGP SIGNATURE-----

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

[COMMITTERS] pgsql: Done: -Remove psql newline patch when we find out why mingw

Log Message:
-----------
Done: -Remove psql newline patch when we find out why mingw outputs an extra newline

Modified Files:
--------------
pgsql/doc:
TODO (r1.2462 -> r1.2463)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/TODO?r1=1.2462&r2=1.2463)
pgsql/doc/src/FAQ:
TODO.html (r1.968 -> r1.969)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/FAQ/TODO.html?r1=1.968&r2=1.969)

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

Re: [pgsql-es-ayuda] Ayuda al crear Rol

Usa EXECUTE para ese tipo de comandos, pues por la forma en que se especifica el nombre del rol a crear, me parece imposible de plasmar como una sentencia estática, lo mismo para los GRANT/REVOKE.

El día 30 de mayo de 2008 22:21, Brando Torres <btorres@cajanorperu.com.pe> escribió:

Hola amigos, una consulta. Necesito saber de qué manera podría crear roles cogiendo parámetros en mi función. Por ejemplo estoy haciendo lo siguiente:

 

CREATE ROLE "$1" LOGIN PASSWORD "$4" NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

 

Donde $1 es el usuario y $4 es el password; y bueno me está mostrando un error de sintaxis. Como podría hacer esto, con los parámetros indicados?

 

Gracias.

 

..

 

Brando.


Re: [HACKERS] proposal: Preference SQL

Kevin Walker wrote:
> Yes, the preference clause can be rewritten using standard SQL. The syntax to duplicate the example result set is listed below. The syntax is not very flexible or easy to read.
>
> select id
> from computer
> where (main_memory = (select max(main_memory)
> from computer)
> and cpu_speed = (select max(cpu_speed)
> from computer
> where cpu_speed < (select max(cpu_speed) from computer)))
> or (cpu_speed = (select max(cpu_speed)
> from computer)
> and main_memory = (select max(main_memory)
> from computer
> where main_memory < (select max(main_memory) from computer)))

Well, that's not 100% correct, but the idea is something like this. In
particular, if you'd have only one entry in the table, then this query
would not return any rows, which would be wrong. Also, if you had a
computer that has larger main memory and a faster CPU than any other
copmuter, it should be returned as the result, but the above query would
fail to do that.

The point is not rewriting that particular preference query into a
standard query. The point is whether it's worth having an automated
mechanism for executing arbitrary preference queries with complex
preferences (again: the syntax is richer, I didn't want to go into any
detail about it before getting some feedback).

Let me give you a more sophisticated example.
You have a webpage that sells used cars. You have your typical search
form with car make, colour, engine power and so on. Normally, you would
make the search form input fields correspond to SQL WHERE clauses. So,
if I want a white Honda with a 180 hp engine and about 40k kilometers of
mileage I enter these parameters and hit the submit button. Now imagine
I don't get any results for my search. That could mean that you have no
Honda cars in stock, but it can also mean that you have my perfect Honda
at a bargain price, it's only that it's black. Or maybe you have a
Honda, but it has a 160 hp engine? Or is it just that the one perfect
Honda you have has a mileage of just over 41k km, and that's why I
didnt' get it in my result set? People seldom want a perfect match when
they are searching for something. They want the best match, they can
get. So, if I wanted to get a Honda with a decent engine and my
favourite color is white, I'd say:

SELECT * FROM cars WHERE make = 'Honda' PREFERRING (power = '180' AND
mileage AROUND 40000) CASCADE color = 'white';

Remember, that an AND in a preference clause constructs a partial order.
The query says: I equally prefer having a 180 hp engine and having a car
that has a mileage of 40k km. Tha CASCADE clause intrudoces a less
important preference. It means that the color is not as important to me
as power and mileage, but if I had a choice I'd take the white one.

I'd strongly recommend skimming through the paper I mentioned in my
first email, it explains stuff much better than I do.

Preference SQL in Postgres could, as I see it, become one of the
distinct features that no other widespread database system has and that
could potentially be massively useful for online shops, social networks,
etc. - you name it.
After hearing Bruce's keynote at PGCon and how Postgres now should be
aiming at more that just catching up with the big guys I just thought:
well, that's one neat feature that none of them has, that's useful,
that's kind of sexy *and* I could get my degree out of it.

Cheers,
Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

--
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] proposal: Preference SQL

Jan Urbański wrote:
> SELECT * FROM computers
> PREFERRING HIGHEST(main_memory) AND HIGHEST(cpu_speed);

This seems similar to the SKYLINE OF patch that was discussed a year (?)
ago. Are you familiar with that project? Can you summarize the differences?

--
Heikki Linnakangas
EnterpriseDB

http://www.enterprisedb.com

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

Re: [pgsql-es-ayuda] PROBLEMA EN WINDOWS VISTA

2008/5/31 Noel Martínez Juárez <noelius79@hotmail.com>:
> Soy nuevo en esto de PostgreSQL, bajé la versión 8.3 para windows,la instalé
> sobre Vista Ultimate, básicamente para probar un sistema que trabaja en
> access con formatos de fecha dd/mm/yyyy, pero a la hora de querer configurar
> el DATESTYLE(SET DATESTYLE TO "SQL"), desde el PGADMIN III y/o directamente
> desde el archivo postgresql.conf, no me resulta, es decir no me realiza los
> cambios, que puedo hacer, será problema del sistema operativo?, o tendré que
> probar desde XP?, gracias.

Si deseas que el cambio sea permanente debes editar postgresql.conf y
hacer un reload o restart del servicio postgresql si haces un SET
DATESTYLE TO "SQL" esto funcionara solo mientras estas conectado.

saludos.-
--
TIP 5: ¿Has leído nuestro extenso FAQ?

http://www.postgresql.org/docs/faqs.FAQ.html