Monday, June 2, 2008

[GENERAL] join ... using ... and - is this expected behaviour?

Just wondering if this is expected behaviour. When executing a query in
the form of:

select column from table join table using (column) and column = clause

pgsql (8.2) returns the following: syntax error at or near "and"

Obviously, you can get around this by using "where" instead of "and",
but shouldn't the format as used above be valid? The following is...

select column from table join table on (column = column) and column = clause

The documentation indicates that the two formats of the query are
equivalent
(http://www.postgresql.org/docs/8.2/interactive/queries-table-expressions.html#QUERIES-JOIN)

The following test case illustrates the issue:

CREATE TABLE table1
(
columnone integer,
columntwo integer
);

CREATE TABLE table2
(
columntwo integer,
columnthree integer
);

insert into table1 values (1, 1), (2, 1);

insert into table2 values (1, 3);

This query results in a syntax error:

select t1.columnone, t1.columntwo, t2.columnthree
from table1 t1 join table2 t2
using (columntwo) and columnone = 1

This query executes as expected:

select t1.columnone, t1.columntwo, t2.columnthree
from table1 t1 join table2 t2
on (t1.columntwo = t2.columntwo) and columnone = 1

Rob Johnston

--
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-es-ayuda] caracteres

José Fermín Francisco Ferreras wrote:
> Hola lista!!
>
> Tengo una duda, cuando inserto una columna con un valor d acento o Ñ
> (ejemplo: España, México, República Dominicana) en PgAdmin III, y
> luego lo visualizo y todo está bien, pero en psql no lo presenta asi.
>
> Pór que será eso??
>
> PD: uso windows xp y postgresql 8.3
Se debe al client_encoding y a la codificacion que tienes en tu base de
datos.

--
Saludos,

Julio Cesar Sánchez González.

--
Ahora me he convertido en la muerte, destructora de mundos.
Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.

www.sistemasyconectividad.com.mx

http://darkavngr.blogspot.com/

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

Re: [GENERAL] FW: make rows unique across db's without UUIP on windows?

Hi.

Ahh,yes.
Patch is inspected for me now.
Please wait for a while.

Regards,
Hiroshi Saito

>From: Kimball Johnson [mailto:kimballjohnson@sbcglobal.net]
>Sent: Monday, June 02, 2008 7:42 PM
>To: 'pgsql-general@postgresql.org'
>Subject: make rows unique across db's without UUIP on windows?
>
>
>
>Considering the discussions on the pgsql-bugs user list regarding ossp-uuip:
>Re: BUG #4167: When generating UUID using UUID-OSSP module, UUIDs are not
>unique on Windows,
>
>
>
>What is the normal solution in pgsql-land for making a serious number of
>rows unique across multiple databases?
>
>
>
>I mean particularly databases of different types (every type) used at
>various places (everywhere) on all platforms (even MS[TM])? You know. a
>UNIVERSAL id?
>
>
>
>I gotta have it! I'm doing distributed list management.
>
>
>
>Anyone got a real good, dependable, easy, and universally manageable
>solution?
>
>
>
>Thanks,
>
>
>
>Kimball Johnson
>

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

Re: [GENERAL] turning fsync off for WAL


Are you sure this will work correctly for database use at all?  The known issue listed at http://www.persistentfs.com/documentation/Release_Notes sounded like a much bigger consistancy concern than the fsync trivia you're bringing up:

"In the current Technology Preview release, any changes to an open file's meta data are not saved to S3 until the file is closed. As a result, if PersistentFS or the system crashes while writing a file, it is possible for the file size in the file's directory entry to be greater than the actual number of file blocks written to S3..."

This sounds like you'll face potential file corruption every time the database goes down for some reason, on whatever database files happen to be open at the time.

Given the current state of EC2, I don't know why you'd take this approach instead of just creating an AMI to install the database into.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD


The problem that I am facing is that EC2 has no persistent storage (at least currently). So, if the server restarts for some reason, all data on the local disks are gone. The idea was to store the tables on the non-persistent local disk, and do the WAL on to an S3 mounted drive. If the server goes down for some reason, I was hoping to recover by replaying the WAL. I was hoping that by faking the fsyncs, I would not incur the actual charges from Amazon until the file system writes into S3.
Also, since WAL is on a separate FS, it will not affect my disk-write rates. 

Ram





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 2 Jun 2008 20:10:14 -0000
@@ -446,6 +446,27 @@
memcpy(prs->words[prs->curwords].word, buf, buflen);
prs->curwords++;
}
+static void
+hladdnorm(HeadlineParsedText *prs, char *lexeme, int lexemelen)
+{
+ ParsedText *prstxt = &(prs->prstxt);
+ while (prstxt->curwords >= prstxt->lenwords)
+ {
+ prstxt->lenwords *= 2;
+ prstxt->words = (ParsedWord *) repalloc((void *) prstxt->words, prstxt->lenwords * sizeof(ParsedWord));
+ }
+
+ prstxt->words[prstxt->curwords].len = lexemelen;
+ prstxt->words[prstxt->curwords].word = palloc(lexemelen * sizeof(char));
+ memcpy(prstxt->words[prstxt->curwords].word, lexeme, lexemelen);
+ /*
+ 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 = prs->curwords - 1;
+ prstxt->curwords++;
+}

static void
hlfinditem(HeadlineParsedText *prs, TSQuery query, char *buf, int buflen)
@@ -476,6 +497,9 @@
}
else
word->item = &item->operand;
+ /* update the corresponding ParsedText */
+ hladdnorm(prs, buf, buflen);
+
}
item++;
}
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 2 Jun 2008 20:47:17 -0000
@@ -1684,18 +1684,186 @@
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;
+ TSVector t;
+
+ if (prs->prstxt.curwords == 0)
+ {
+ /* no query words found in the document */
+ pfree(prs->prstxt.words);
+ return;
+ }
+ t = make_tsvector(&(prs->prstxt));
+ covers = palloc(maxcovers * sizeof(CoverPos));
+
+ 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 */
+ min = 0x7fffffff;
+ 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 +1875,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 +1998,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 +2083,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 2 Jun 2008 20:04:25 -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 2 Jun 2008 20:04:25 -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
Efficiency: I realized that we do not need to store all norms. We need
to only store store norms that are in the query. So I moved the addition
of norms from addHLParsedLex to hlfinditem. This should add very little
memory overhead to existing headline generation.

If this is still not acceptable for default headline generation, then I
can push it into mark_hl_fragments. But I think any headline marking
function will benefit by having the norms corresponding to the query.

Why we need norms?

hlCover does the exact thing that Cover in tsrank does which is to find
the cover that contains the query. However hlcover has to go through
words that do not match the query. Cover on the other hand operates on
position indexes for just the query words and so it should be faster.

The main reason why I would I like it to be fast is that I want to
generate all covers for a given query. Then choose covers with smallest
length as they will be the one that will best explain relation of a
query to a document. Finally stretch those covers to the specified size.

In my understanding, the current headline generation tries to find the
biggest cover for display in the headline. I personally think that such
a cover does not explain the context of a query in a document. We may
differ on this and thats why we may need both options.

Let me know what you think on this patch and I will update the patch to
respect other options like MinWords and ShortWord.

NumFragments < 2:
I wanted people to use the new headline marker if they specify
NumFragments >= 1. If they do not specify the NumFragments or put it to
0 then the default marker will be used. This becomes a bit of tricky
parameter so please put in any idea on how to trigger the new marker.

On an another note I found that make_tsvector crashes if it receives a
ParsedText with curwords = 0. Specifically uniqueWORD returns curwords
as 1 even when it gets 0 words. I am not sure if this is the desired
behavior.

-Sushant.


On Mon, 2008-06-02 at 18:10 +0400, Teodor Sigaev wrote:
> > 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.
> New variant is much better, but...
>
> > 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.
>
> Why do you need to store norms? The single purpose of norms is identifying words
> from query - but it's already done by hlfinditem. It sets
> HeadlineWordEntry->item to corresponding QueryOperand in tsquery.
> Look, headline function is rather expensive and your patch adds a lot of extra
> work - at least in memory usage. And if user calls with NumFragments=0 the that
> work is unneeded.
>
> > This approach does not change any other interface and fits nicely with
> > the overall framework.
> Yeah, it's a really big step forward. Thank you. You are very close to
> committing except: Did you find a hlCover() function which produce a cover from
> original HeadlineParsedText representation? Is any reason to do not use it?
>
> >
> > 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.
> ShortWord, MinWords and MaxWords should store their meaning, but for each
> fragment, not for the whole headline.
>
>
> >
> > Let me know any more changes you would like to see.
>
> 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);
>
>
> Suppose, num_fragments < 2?
>

[GENERAL] FW: make rows unique across db's without UUIP on windows?

 

 

From: Kimball Johnson [mailto:kimballjohnson@sbcglobal.net]
Sent: Monday, June 02, 2008 7:42 PM
To: 'pgsql-general@postgresql.org'
Subject: make rows unique across db's without UUIP on windows?

 

Considering the discussions on the pgsql-bugs user list regarding ossp-uuip: Re: BUG #4167: When generating UUID using UUID-OSSP module, UUIDs are not unique on Windows,

 

What is the normal solution in pgsql-land for making a serious number of rows unique across multiple databases?

 

I mean particularly databases of different types (every type) used at various places (everywhere) on all platforms (even MS[TM])? You know… a UNIVERSAL id?

 

I gotta have it! I’m doing distributed list management.

 

Anyone got a real good, dependable, easy, and universally manageable solution?

 

Thanks,

 

Kimball Johnson

Re: [GENERAL] turning fsync off for WAL

On Mon, 2 Jun 2008, Ram Ravichandran wrote:

> My current plan is to mount an Amazon S3 bucket as a drive using
> PersistentFS which is a POSIX-compliant file system.

Are you sure this will work correctly for database use at all? The known
issue listed at http://www.persistentfs.com/documentation/Release_Notes

sounded like a much bigger consistancy concern than the fsync trivia
you're bringing up:

"In the current Technology Preview release, any changes to an open file's
meta data are not saved to S3 until the file is closed. As a result, if
PersistentFS or the system crashes while writing a file, it is possible
for the file size in the file's directory entry to be greater than the
actual number of file blocks written to S3..."

This sounds like you'll face potential file corruption every time the
database goes down for some reason, on whatever database files happen to
be open at the time.

Given the current state of EC2, I don't know why you'd take this approach
instead of just creating an AMI to install the database into.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

[GENERAL] make rows unique across db's without UUIP on windows?

Considering the discussions on the pgsql-bugs user list regarding ossp-uuip: Re: BUG #4167: When generating UUID using UUID-OSSP module, UUIDs are not unique on Windows,

 

What is the normal solution in pgsql-land for making a serious number of rows unique across multiple databases?

 

I mean particularly databases of different types (every type) used at various places (everywhere) on all platforms (even MS[TM])? You know… a UNIVERSAL id?

 

I gotta have it! I’m doing distributed list management.

 

Anyone got a real good, dependable, easy, and universally manageable solution?

 

Thanks,

 

Kimball Johnson

Re: [HACKERS] Case-Insensitve Text Comparison

On Mon, Jun 02, 2008 at 10:29:30AM -0700, Jeff Davis wrote:

> What if you had a CHECK constraint that was locale-sensitive? Would the
> constraint only be non-false (true or null) for records inserted under
> the same locale? That's not very useful.

It would seem that this is one of the important cases that needs to be
worked out. I wasn't suggesting that per-session locale (or whatever
we want to call it) is _easy_ or, for that matter, even possible; just
that it would solve a large number of the problems that people
complain about.

In fact, I suspect that what we really need is something a little more
like "in-database locale" or something.

> I think if you want some special treatment of text for some users, it
> should be explicit.

Yes. Also, not just text. Think of currency, numeric separators, &c.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.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] Proposal: new function array_init

2008/6/2 Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>> There was more time questions about array's initialisation. I propose
>> function array_init.
>
>> CREATE OR REPLACE FUNCTION array_init(sizes int[], v anyelement)
>> RETURNS anyarray;
>
> I think this is basically a good idea, but maybe the API needs a bit of
> adjustment --- providing the sizes as an array doesn't seem especially
> convenient. Since we only allow up to 6 dimensions (IIRC), what about
> six functions with different numbers of parameters:
>
> array_int(int, anyelement)
> array_int(int, int, anyelement)
> ...
> array_int(int, int, int, int, int, int, anyelement)
>
> I don't object to having the array-input version too, but seems like in
> most cases this way would be easier to use. It wouldn't work well
> for providing lower bounds too, but maybe the array-input case is
> sufficient for that.

Your proposal is maybe little bit readable with lower bounds, and when
initial value is integer. But it's easy do wrap SQL functions .

>
> Other thoughts:
>
> * Should the fill value be the first parameter instead of the last?
> I'm not sure either way.

I am not sure too. I have not any original - the nearest function is memset?

>
> * I have a mild preference for "array_fill" instead of "array_init".

maybe, maybe array_set. Any ideas are welcome

>
> * We can handle a null fill value now, but what about nulls in the
> dimensions? The alternatives seem to be to return a null array
> (not an array of nulls) or throw error.

I am afraid so null array can be changed with null value - so I prefer
in this case raise exception.

Regards
Pavel Stehule
>
> 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] Case-Insensitve Text Comparison

On Mon, 2008-06-02 at 19:55 +0200, Martijn van Oosterhout wrote:
> The SQL COLLATE syntax handles this just fine. Either the original
> COLLATE patch or the new one will let people tags strings with any
> collation they like.

http://wiki.postgresql.org/wiki/Todo:Collate

The last reference I see on that page is from 2005. Is there any updated
information? Are there any major obstacles holding this up aside from
the platform issues mentioned on that page?

Regards,
Jeff Davis


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

Re: [HACKERS] Overhauling GUCS

Greg,

> Like some of the other GUC simplification ideas that show up sometimes
> (unifying all I/O and limiting background processes based on that total
> is another), this is hard to do internally.  Josh's proposal has a fair
> amount of work involved, but the code itself doesn't need to be clever
> or too intrusive.  Unifying all the memory settings would require being
> both clever and intrusive, and I doubt you'll find anybody who could
> pull it off who isn't already overtasked with more important
> improvements for the 8.4 timeframe.

Plus, I'm a big fan of "enable an API" rather than "write a feature". I
think that there are people & companies out there who can write better
autotuning tools than I can, and I'd rather give them a place to plug
those tools in than trying to write autotuning into the postmaster.

--
--Josh

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: [HACKERS] Case-Insensitve Text Comparison

On Sun, 2008-06-01 at 22:13 -0700, David E. Wheeler wrote:
> What locale is right? If I have a Web app, there could be data in many
> different languages in a single table/column.

I think the values should be explicitly treated differently.

It would be nice if you could just typecast, like:
"lower(somevalue::text(fr_CA))"

which would then lowercase according to the fr_CA locale, regardless of
the locale of "somevalue".

Using typmod for localization was brought up here:
http://archives.postgresql.org/pgsql-hackers/2007-06/msg00635.php

Has it been discussed further? I happen to like the idea of the TEXT
type taking a locale as a typmod. No typmod would, of course, fall back
to the cluster setting. And it would throw an exception if the encoding
couldn't represent that locale.

Regards,
Jeff Davis


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

Re: [HACKERS] Case-Insensitve Text Comparison

On Mon, Jun 02, 2008 at 10:29:30AM -0700, Jeff Davis wrote:
> I think if you want some special treatment of text for some users, it
> should be explicit. Text in one locale is really a different type from
> text in another locale, and so changing the locale of some text variable
> is really a typecast. I don't think GUCs are the correct mechanism for
> this.

The SQL COLLATE syntax handles this just fine. Either the original
COLLATE patch or the new one will let people tags strings with any
collation they like.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org>

http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Re: [HACKERS] Add dblink function to check if a named connection exists

Joe Conway wrote:

>
> If you really want the notational simplicity, you could use an SQL
> function to wrap it:
>
> CREATE OR REPLACE FUNCTION dblink_exists(text)
> RETURNS bool AS $$
> SELECT $1 = ANY (dblink_get_connections())
> $$ LANGUAGE sql;


Thanks, that seems like a reasonable way to solve this.


--
Tommy Gildseth

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

On Thu, 2008-05-29 at 23:37 +0200, Mathias Brossard wrote:

> I pointed out that the NTT solution is synchronous because Tom said in
> the first part of his email that:
>
> > In practice, simple asynchronous single-master-multiple-slave
> > replication covers a respectable fraction of use cases, so we have
> > concluded that we should allow such a feature to be included in the
> > core project.
>
> ... and yet "the most appropriate base technology for this" is
> synchronous and maybe I should have also pointed out in my previous mail
> is that it doesn't support multiple slaves.

I don't think that you need too many slaves in sync mode.

Probably 1-st slave sync and others async from there on will be good
enough.

> Also, as other have pointed out there are different interpretations of
> "synchronous" depending on wether the WAL data has reached the other end
> of the network connection, a safe disk checkpoint or the slave DB itself.

Probably all DRBD-s levels ( A) data sent to network, B) data received,
C) data written to disk) should be supported + C1) data replayed in
slave DB. C1 meaning that it can be done in parallel with C)

Then each DBA can set it up depending on what he trusts - network,
slave's power supply or slaves' disk.

Also, the case of slave failure should be addressed. I don't think that
the best solution is halting all ops on master if slave/network fails.

Maybe we should allow also a setup with 2-3 slaves, where operations can
continue when at least 1 slave is "syncing" ?

--------------
Hannu


--
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] Case-Insensitve Text Comparison

On Mon, 2008-06-02 at 09:51 -0400, Andrew Sullivan wrote:
> On Sun, Jun 01, 2008 at 10:13:07PM -0700, David E. Wheeler wrote:
>
> > What locale is right? If I have a Web app, there could be data in many
> > different languages in a single table/column.
>
> I think the above amounts to a need for per-session locale settings or
> something, no?
>

What if you had a CHECK constraint that was locale-sensitive? Would the
constraint only be non-false (true or null) for records inserted under
the same locale? That's not very useful.

I think if you want some special treatment of text for some users, it
should be explicit. Text in one locale is really a different type from
text in another locale, and so changing the locale of some text variable
is really a typecast. I don't think GUCs are the correct mechanism for
this.

Regards,
Jeff Davis


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

Re: [HACKERS] Proposal: new function array_init

On Mon, Jun 2, 2008 at 9:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>> There was more time questions about array's initialisation. I propose
>> function array_init.

As one of the questioners, I will give some short thoughts below.

>> CREATE OR REPLACE FUNCTION array_init(sizes int[], v anyelement)
>> RETURNS anyarray;

+1. +0 for Pavel's proposed syntax, because it feels better, but also
it scales to N dimensions (we should throw an error obviously if the
input is too big, but we can probably source that size through an
include), I hate functions with more than four arguments, and having
six slightly overloaded functions in the system catalogs seems
annoying.

> * We can handle a null fill value now, but what about nulls in the
> dimensions? The alternatives seem to be to return a null array
> (not an array of nulls) or throw error.

I would throw an error, unless there is something that one can do with
a null array (perhaps there is?).

We also might want to consider a resize function, and some other
utilities as long as we are bothering with this.

I am sorry that I can't offer to write these, but I don't have the
time to learn the Postgresql infrastructure to do it.

Thanks for the attention Pavel!

--
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] No parar aplicación en vacuum full

OK.
Voy a hacer lo que me sugieres no utilizando el vacuum full y configurando el autovacuum para
esta tabla.

Ya os contaré el resultado.

Gracias y saludos,

Anna Pisa

________________________________________
De: Jaime Casanova [systemguards@gmail.com]
Enviat el: dilluns, 2 / juny / 2008 17:29
Per a: Pisa Borràs, Anna
A/c: pgsql-es-ayuda@postgresql.org
Tema: Re: [pgsql-es-ayuda] No parar aplicación en vacuum full

2008/6/2 Pisa Borràs, Anna <anna.pisa@tecsidel.es>:
> Documentándome entré en el maravilloso mundo del vacuum. Logré estabilizar
> el sistema haciendo un autovacuum cada hora, un vacuum full cada día ( a
> las 3 de la madrugada, que el sistema va mas descargado, se ejecuta un
> script con el vacuum full) y aumentar el valor de parámetros de memoria o
> free space map . Con esta configuración todo marcha sobre ruedas.
>

vacuum full no lo necesitas, solo haz vacuum normal...

> Ahora los detalles: la aplicación modifica una tabla, que puede llegar a
> tener unos 70 registros, cada uno recibe un update cada 15 segundos, con lo
> cual os podéis imaginar como crece esta tabla cada día.
>

es posible configurar autovacuum para que afecte a esta tabla de forma
mas frecuente creando un registro que haga referencia a esta tabla en
el catalogo pg_autovacuum

> Para que el vacuum full haga limpieza debo parar la aplicación, y la vuelvo
> a arrancar una vez ha acabado el vacuum full.

por eso no es conveniente el VACUUM FULL, y lo que es mas actualmente
ya ni siquiera se recomienda su uso...

> Cuando hice pruebas con la
> aplicación en funcionamiento encontraba cantidad de filas removibles pero
> luego no se podían borrar.
>
> INFO: "tabla": found nnnnnn removable, 70 non removable rows versions in
> xxxx pages
> DETAIL: nnnnnn dead row versions cannot be removed yet
>
> Es como si el vacuum full no pudiera bloquear la tabla para hacer la
> limpieza. (Puede ser esto realmente?)
>

Lo que esta pasando es que aun hay transacciones que pueden ver las
versiones viejas de esas tuplas. Mientras esas transacciones esten
corriendo VACUUM no se va a arriesgar a eliminarlas

> Mi pregunta es si hay algún mecanismo o parámetro que permita que el vacuum
> full haga la limpieza de dicha tabla sin parar la aplicación.
>

si. no lo uses :)

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 087171157
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

Re: [PERFORM] Outer joins and equivalence

On Tue, 2008-05-27 at 17:43 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > I have a complex query where making a small change to the SQL increases
> > run-time by > 1000 times.
>
> > The first SQL statement is of the form
>
> > A JOIN B ON (a.id = b.id) LEFT JOIN C ON (a.id = c.id)
>
> > and the second is like this
>
> > A JOIN B ON (a.id = b.id) LEFT JOIN C ON (b.id = c.id)
>
> > the only difference is the substitution of a -> b
>
> Please provide an actual test case.

Getting closer, but still not able to produce a moveable test case.

Symptoms are

* using partitioning
* when none of the partitions are excluded
* when equivalence classes ought to be able to reconcile join

Still working on it

--
Simon Riggs

www.2ndQuadrant.com

PostgreSQL Training, Services and Support


--
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] dblink() cursor error/issue (TopMemoryContext)

On Mon, June 2, 2008 6:53 pm, Tom Lane wrote:
> "Henry" <henry@zen.co.za> writes:
>> I'm trying to code a function to copy rows from one machine to another
>> using dblink and cursors:
>
> What PG version is this, exactly?

Arg, dammit. Sorry, it's version 8.2.6 (where the function is running),
talking to a remote machine running 8.3.1.

> I don't think your problem has anything to do with dblink per se.
> The repeated begin/exception blocks are apparently managing to leak
> some memory per iteration. I can't tell whether this represents
> a known (and perhaps already fixed) bug; it very likely depends on
> details you haven't shown us. Do you want to try to put together a
> self-contained test case? (Again, you likely don't need dblink to
> exhibit the issue.)

OK, will give this a swing; but I'll first upgrade to 8.3.1 and see what
that does.

> If you just want something that works now, try probing for an existing
> entry before inserting, instead of relying on catching an exception.

ok, but that's going to be slow considering I'm inserting almost a hundred
million rows.

My previous post wasn't entirely accurate: the dblink_exec() call in a
loop is also enclosed in a begin/exception/end block, yet it doesn't fail.
The only difference besides the dblink_exec call itself, is that I'm
using 'others' to catch any error from dblink_exec (using 'others' on the
first insert results in the same issue).


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

[lapug] Call for meeting Locations and Speakers

We still need a meeting location for June, If anyone would like to
volunteer one please feel free to do so.

Also we have slots open for talks for the months of July, August, and
September. So if there is a PostgreSQL feature, relational schema
design, or any thing else that you would like to demonstrate, here is
your chance!

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

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

[lapug] PL-Perl presentation for June 27

Juan,

I wanted to check with you to see if you were still availaible for a
presentation of PL-PERL?

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

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

Re: [GENERAL] dblink() cursor error/issue (TopMemoryContext)

"Henry" <henry@zen.co.za> writes:
> I'm trying to code a function to copy rows from one machine to another
> using dblink and cursors:

What PG version is this, exactly?

> perform dblink_connect ('dbname=db1...host=othermachine.com');
> perform dblink_open ('cur_other1', 'SELECT col1 FROM tab1');

> loop
> fnd := 0;
> for rec in
> -- grab a 1000 rows at a time
> SELECT col1 FROM dblink_fetch ('cur_other1', 1000)
> AS tab1 (col1 text)
> loop
> begin
> INSERT INTO tab1 (col1) VALUES (rec.col1);
> ...
> exception when unique_violation then
> -- ignore dups
> end;
> fnd := 1
> end loop;
> if fnd = 0 then
> exit;
> end if;
> end loop;

> perform dblink_close ('cur_other1');
> perform dblink_disconnect();

I don't think your problem has anything to do with dblink per se.
The repeated begin/exception blocks are apparently managing to leak
some memory per iteration. I can't tell whether this represents
a known (and perhaps already fixed) bug; it very likely depends on
details you haven't shown us. Do you want to try to put together a
self-contained test case? (Again, you likely don't need dblink to
exhibit the issue.)

If you just want something that works now, try probing for an existing
entry before inserting, instead of relying on catching an exception.

regards, tom lane

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

[pgsql-es-ayuda] cuestión d Licencias

Estaba leyendo unas discusiones en esta página:http://codigolibre.org/modules.php?name=Forums&file=viewtopic&t=911#1733

Me gustaría ver las opiniones d ustedes sobre este asunto.



<html><div>ing. José Fermín Francisco Ferreras <BR>San Francisco de Macorís, Rep. Dom. <BR></div></html>


Send funny voice messages packed with tidbits from MSN. Everyone wants to be ready.

Re: [HACKERS] Overhauling GUCS

On Mon, 2 Jun 2008, Jignesh K. Shah wrote:

> Most people I have seen will increase one or few but not all parameters
> related to memory which can result in loss of performance and
> productivity in figuring out.

If it becomes easier to build a simple tool available to help people tune
their configurations, that should help here without having to do anything
more complicated than that.

> What happened to AvailRAM setting and base all memory gucs on that.

Like some of the other GUC simplification ideas that show up sometimes
(unifying all I/O and limiting background processes based on that total is
another), this is hard to do internally. Josh's proposal has a fair
amount of work involved, but the code itself doesn't need to be clever or
too intrusive. Unifying all the memory settings would require being both
clever and intrusive, and I doubt you'll find anybody who could pull it
off who isn't already overtasked with more important improvements for the
8.4 timeframe.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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: new function array_init

"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> There was more time questions about array's initialisation. I propose
> function array_init.

> CREATE OR REPLACE FUNCTION array_init(sizes int[], v anyelement)
> RETURNS anyarray;

I think this is basically a good idea, but maybe the API needs a bit of
adjustment --- providing the sizes as an array doesn't seem especially
convenient. Since we only allow up to 6 dimensions (IIRC), what about
six functions with different numbers of parameters:

array_int(int, anyelement)
array_int(int, int, anyelement)
...
array_int(int, int, int, int, int, int, anyelement)

I don't object to having the array-input version too, but seems like in
most cases this way would be easier to use. It wouldn't work well
for providing lower bounds too, but maybe the array-input case is
sufficient for that.

Other thoughts:

* Should the fill value be the first parameter instead of the last?
I'm not sure either way.

* I have a mild preference for "array_fill" instead of "array_init".

* We can handle a null fill value now, but what about nulls in the
dimensions? The alternatives seem to be to return a null array
(not an array of nulls) or throw error.

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] Case-Insensitve Text Comparison

On Jun 2, 2008, at 09:33, Tom Lane wrote:

>> Would the use of str_tolower() in formatting.c fix that?
>
> Yeah, you need something equivalent to that. I think that whole area
> is due for refactoring, though --- we've got kind of a weird
> collection
> of upper/lower/initcap APIs spread through a couple of different
> files.

And I just ran into this on 8.3 when trying to install citext:

psql:citext.sql:350: ERROR: there is no built-in function named
"oid_text"

I'm assuming that this is because a lot of automatic casts were
removed in 8.3 or 8.2; There are a bunch of these:

CREATE FUNCTION citext(oid) RETURNS citext AS 'oid_text' LANGUAGE
'internal' IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION citext(oid) TO PUBLIC;
COMMENT ON FUNCTION citext(oid) IS 'convert oid to citext';
CREATE FUNCTION oid(citext) RETURNS oid AS 'text_oid' LANGUAGE
'internal' IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION oid(citext) TO PUBLIC;
COMMENT ON FUNCTION oid(citext) IS 'convert citext to oid';
CREATE CAST (citext AS oid) WITH FUNCTION oid(citext);
CREATE CAST (oid AS citext) WITH FUNCTION citext(oid);

CREATE FUNCTION citext(int2) RETURNS citext AS 'int2_text' LANGUAGE
'internal' IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION citext(int2) TO PUBLIC;
COMMENT ON FUNCTION citext(int2) IS 'convert int2 to citext';
CREATE FUNCTION int2(citext) RETURNS int2 AS 'text_int2' LANGUAGE
'internal' IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION int2(citext) TO PUBLIC;
COMMENT ON FUNCTION int2(citext) IS 'convert citext to int2';
CREATE CAST (citext AS int2) WITH FUNCTION int2(citext);
CREATE CAST (int2 AS citext) WITH FUNCTION citext(int2);

And on and on. Clearly this module needs updating for newer
PostgreSQLs. I tried removing them all in order to get the data type
and tried it out with this script:

my $dbh = DBI->connect('dbi:Pg:dbname=try', 'postgres', '',
{ pg_enable_utf8 => 1 });
for my $char qw( À Á Â Ã Ä Å Ç Ć Č Ĉ Ċ Ď Đ A B C D ) {
print "$char: ", $dbh->selectrow_array('SELECT LOWER(?::citext)',
undef, $char ), $/;
}

Naturally it didn't work:

À: Ã
Á: á
Â: â
Ã: ã
Ä: ä
Å: Ã¥
Ç: ç
Ć: ć
Č: č
Ĉ: ĉ
Ċ: Ä&lsqauo;
Ď: ď
Đ: Ä'
A: a
B: b
C: c
D: d

BTW, I rebuilt my cluster with --locale en_US.UTF-8 and the script
works on a text type, so having a locale is key.

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] Case-Insensitve Text Comparison

"David E. Wheeler" <david@kineticode.com> writes:
> On Jun 1, 2008, at 21:08, Tom Lane wrote:
>> [ broken record... ] Kinda depends on your locale. However,
>> tolower()
>> is 100% guaranteed not to work for multibyte encodings, so citext is
>> quite useless if you're using UTF8. This is fixable, no doubt, but
>> it's not fixed in the project as it stands.

> Would the use of str_tolower() in formatting.c fix that?

Yeah, you need something equivalent to that. I think that whole area
is due for refactoring, though --- we've got kind of a weird collection
of upper/lower/initcap APIs spread through a couple of different files.

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

On Mon, 2008-06-02 at 11:59 -0400, Jignesh K. Shah wrote:
>
> Simon Riggs wrote:
> >
> > Some other problems I see with GUCs
> >
> > * It's not possible to set one parameter depending upon the setting of
> > another.
> >
>
> To me this is more critical.. Most people I have seen will increase one
> or few but not all parameters related to memory which can result in loss
> of performance and productivity in figuring out.
>
> What happened to AvailRAM setting and base all memory gucs on that.
> Ideally PostgreSQL should only create one big memory pool and allow all
> other variables to change runtime via dba or some tuner process or
> customized application as long as total is less than the allocated
> shared_memory and local_memory settings. (This will also reduce the need
> of restarting Postgres if a value needs to be changed)

Agreed.

Right now, we can't even do that in code, let alone in config file.

If we had a smart_memory_config = on then we'd be able to say in the
backend:
if (smart_memory_config)
{
other_thing = 0.1 * Nbuffers;
}

but the GUCs are evaluated in alphabetical order, without any way of
putting dependencies between them. So they are notionally orthogonal.

--
Simon Riggs

www.2ndQuadrant.com

PostgreSQL Training, Services and 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: [pgsql-www] RFC: Product directory

On Mon, 2008-06-02 at 17:02 +0100, Dave Page wrote:
> On Mon, Jun 2, 2008 at 4:56 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> >
> > And who is going to make sure the pricing is up to date? (hint: no one
> > will)
>
> I'm guessing you didn't read all of the thread?

Dave made a good point to me on Jabber about the pricing issue.
Basically, I don't buy software so this issue doesn't quite sit right
with me. He is right, I license very little commercial software for
business purposes. In fact the *only* commercial software CMD has
purchased is QuickBooks. So I am going to drop my argument against this.

Sincerely,

Joshua D. Drake

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

Re: [HACKERS] Overhauling GUCS

On Mon, 2 Jun 2008, Tom Lane wrote:

> Greg Smith <gsmith@gregsmith.com> writes:
>> Joshua has been banging a drum for a while now that all this data needs to
>> get pushing into the database itself.
>
> This is, very simply, not going to happen.

Right, there are also technical challenges in the way of that ideal. I
was only mentioning the reasons why it might not be the best idea even if
it were feasible. However, I do not see why the limitations you bring up
must get in the way of thinking about how to interact and manage the
configuration data in a database context, even though it ultimately must
be imported and exported to a flat file.

The concerns you bring up again about leaving the database in an
unstartable state are a particularly real danger in the "only has access
to 5432" hosted provider case that this redesign is trying to satisfy. I
added a "Gotchas" section to the wiki page so that this issue doesn't get
forgotten about. The standard way to handle this situation is to have a
known good backup configuration floating around. Adding something in that
area may end up being a hard requirement before remote editing makes
sense.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] Case-Insensitve Text Comparison

On Jun 1, 2008, at 21:08, Tom Lane wrote:

>> 1. Does the use of the tolower() C function in the citext data type
>> on
>> pgfoundry basically give me the same results as using lower() in my
>> SQL has for all these years?
>
> [ broken record... ] Kinda depends on your locale. However,
> tolower()
> is 100% guaranteed not to work for multibyte encodings, so citext is
> quite useless if you're using UTF8. This is fixable, no doubt, but
> it's not fixed in the project as it stands.

Would the use of str_tolower() in formatting.c fix that?

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: [pgsql-www] RFC: Product directory

On Mon, 2008-06-02 at 17:02 +0100, Dave Page wrote:
> On Mon, Jun 2, 2008 at 4:56 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> >
> > And who is going to make sure the pricing is up to date? (hint: no one
> > will)
>
> I'm guessing you didn't read all of the thread?
>

I may have missed that. Let me review again.


> > But I am sure you have time to create a secondary table and the code for
> > it. I am not asking you to create all the code for sponsors, news,
> > events. I am asking you to use a reusable design so others can create
> > code for those services.
>
> That I can do. It sounded like you wanted me to get rid of all the
> redundant info at the same time.

:)

Sincerely,

Joshua D. Drake

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

Re: [austinpug] June meeting

<aol>ME TOO</aol>

--
Larry Rosenman

http://www.lerctr.org/~ler
Phone: +1 512-248-2683 E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893


-----Original Message-----
From: austinpug-owner@postgresql.org [mailto:austinpug-owner@postgresql.org]
On Behalf Of David McNett
Sent: Monday, June 02, 2008 10:59 AM
To: Robert Lor
Cc: austinpug@postgresql.org
Subject: Re: [austinpug] June meeting

Count me in. I'm looking forward to it!

On May 30, 2008, at 5:59 PM, Robert Lor wrote:
> Please RSVP if you plan to attend so I can pre-register folks a
> head of time.
>
> Decibel! wrote:
>> The June meeting will be at Sun, Tuesday June 3rd at 7PM.
>>
>> Food: do we want to do Mangia's again? Other suggestions?
> Mangia's works for me. We can split the bill this time!
>>
>> Presentation: we identified 3 possibilities; GUCs / tuning, dtrace,
>> and internals. I'll also throw out doing an overview of PGCon,
>> since there were 3 of us there. What would folks like to see?
> I think most people were interested in tuning, so I'd pick GUCs/
> tuning.
>>
>> (Robert, please get me that internals presentation :) )
> http://neilconway.org/talks/hacking/
>
>
> -Robert
>
> --
> Sent via austinpug mailing list (austinpug@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/austinpug
>


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


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

[HACKERS] Proposal: new function array_init

Hello

There was more time questions about array's initialisation. I propose
function array_init.

CREATE OR REPLACE FUNCTION array_init(sizes int[], v anyelement)
RETURNS anyarray;

First parameter is array of dimension's sizes. Second argument is
value that will be used for initialisation.

Because pg array's indexes must not start from 1 we can allow specify it.

CREATE OR REPLACE FUNCTION array_init(sizes int[], lowers int[], v
anyelement) RETURNS anyarray;

select array_init(array[2],0);
array
---------------
{0,0}
(1 row)


select array_init(array[1,2], 0);
array
---------------
{{0,0},{0,0}}
(1 row)

Any comments?

Regards
Pavel Stehule

--
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] sequences and currval()

On Mon, Jun 02, 2008 at 11:55:14AM -0400, Michael P. Soulier wrote:
> Hello,
>
> I'm migrating a db schema in an automated fashion, using this
>
> UPDATE clients_client
> SET icp_id = null
> WHERE icp_id = 1;
> UPDATE icps_icp
> SET id = nextval('public.icps_icp_id_seq')
> WHERE id = 1;
> UPDATE clients_client
> SET icp_id = currval('public.icps_icp_id_seq')
> WHERE icp_id = null;

Your problem is that this should be: WHERE icp_id IS NULL.

> I've noticed this on a fresh pgsql session.
>
> tugdb=# select currval('icps_icp_id_seq');
> ERROR: currval of sequence "icps_icp_id_seq" is not yet defined in this
> session
>
> I don't understand this, as I only want the current value of the
> sequence. I suppose I can get it this way

currval() returns the value last returned by nextval() *in this
session*. If you havn't called it in this session ofcourse it won't
work. This is for transaction safety.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org>

http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Re: [pgsql-www] RFC: Product directory

On Mon, Jun 2, 2008 at 4:56 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>
> And who is going to make sure the pricing is up to date? (hint: no one
> will)

I'm guessing you didn't read all of the thread?

> But I am sure you have time to create a secondary table and the code for
> it. I am not asking you to create all the code for sponsors, news,
> events. I am asking you to use a reusable design so others can create
> code for those services.

That I can do. It sounded like you wanted me to get rid of all the
redundant info at the same time.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Re: [HACKERS] Overhauling GUCS

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

To me this is more critical.. Most people I have seen will increase one
or few but not all parameters related to memory which can result in loss
of performance and productivity in figuring out.

What happened to AvailRAM setting and base all memory gucs on that.
Ideally PostgreSQL should only create one big memory pool and allow all
other variables to change runtime via dba or some tuner process or
customized application as long as total is less than the allocated
shared_memory and local_memory settings. (This will also reduce the need
of restarting Postgres if a value needs to be changed)

-Jignesh

> * It's always unclear which GUCs can be changed, and when. That is much
> more infrequently understood than the meaning of them.
>
> * We should rename effective_cache_size to something that doesn't sound
> like it does what shared_buffers does
>
> * 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.
>
>

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

Re: [PATCHES] extend VacAttrStats to allow stavalues of different types

Tom Lane wrote:
> I think the correct solution is to initialize the fields to match the
> column type before calling the typanalyze function. Then you don't
> break compatibility for existing typanalyze functions. It's also less
> code, since the standard typanalyze functions can rely on those preset
> values.

Right. Updated patch attached.

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

Re: [austinpug] June meeting

Count me in. I'm looking forward to it!

On May 30, 2008, at 5:59 PM, Robert Lor wrote:
> Please RSVP if you plan to attend so I can pre-register folks a
> head of time.
>
> Decibel! wrote:
>> The June meeting will be at Sun, Tuesday June 3rd at 7PM.
>>
>> Food: do we want to do Mangia's again? Other suggestions?
> Mangia's works for me. We can split the bill this time!
>>
>> Presentation: we identified 3 possibilities; GUCs / tuning, dtrace,
>> and internals. I'll also throw out doing an overview of PGCon,
>> since there were 3 of us there. What would folks like to see?
> I think most people were interested in tuning, so I'd pick GUCs/
> tuning.
>>
>> (Robert, please get me that internals presentation :) )
> http://neilconway.org/talks/hacking/
>
>
> -Robert
>
> --
> Sent via austinpug mailing list (austinpug@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/austinpug
>


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

Re: [pgsql-www] RFC: Product directory

On Mon, 2008-06-02 at 16:46 +0100, Dave Page wrote:

> > I have to go with fetter here. Pricing is not our concern.
>
> I actually feel quite strongly about that one - it's not *our*
> concern, but it is the concern of the users. There's nothing worse
> than researching a product, and eventually finding out after trawling
> the website for half an hour that it's way too expensive. As an
> end-user I always wanted to see a ballpark figure up front.

And who is going to make sure the pricing is up to date? (hint: no one
will)


> > Yeah I mentioned this in my previous post. Publisher really needs to be
> > pushed out. There is entirely too much redundant information that can be
> > accumulated.
>
> OK so the difficulty here/previously is that you are essentially
> saying I need to write a publisher management system and a product
> management system, and update sponsors, news, events, services etc to
> use that data as well. Nice in theory, but not something I have time
> to do.

But I am sure you have time to create a secondary table and the code for
it. I am not asking you to create all the code for sponsors, news,
events. I am asking you to use a reusable design so others can create
code for those services.

Sincerely,

Joshua D. Drake

>


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

[GENERAL] sequences and currval()

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIRBfmKGqCc1vIvggRAhoTAJ9vwQ0rWP/zF8POuO/7LehDzdDYTACghR4I
9wDKOzHrkGA+dX7Mke4I7OM=
=3eUa
-----END PGP SIGNATURE-----
Hello,

I'm migrating a db schema in an automated fashion, using this

UPDATE clients_client
SET icp_id = null
WHERE icp_id = 1;
UPDATE icps_icp
SET id = nextval('public.icps_icp_id_seq')
WHERE id = 1;
UPDATE clients_client
SET icp_id = currval('public.icps_icp_id_seq')
WHERE icp_id = null;

So essentially, clients have an icp_id that is set to null if it was 1,
and then the icps_icp table is updated to move icp 1 to whatever is next
in the sequence.

I then want to adjust the clients such that they reference where the icp
was moved to using currval() on the sequence. But, this part doesn't
seem to be working. The clients continue to have an icp_id of null.

I've noticed this on a fresh pgsql session.

tugdb=# select currval('icps_icp_id_seq');
ERROR: currval of sequence "icps_icp_id_seq" is not yet defined in this
session

I don't understand this, as I only want the current value of the
sequence. I suppose I can get it this way

tugdb=# select last_value from icps_icp_id_seq;
last_value
------------
2
(1 row)

but I'd like to understand why currval() doesn't work.

Thanks,
Mike
--
Michael P. Soulier <michael_soulier@mitel.com>, 613-592-2122 x2522
"Any intelligent fool can make things bigger and more complex... It
takes a touch of genius - and a lot of courage to move in the opposite
direction." --Albert Einstein

Re: [HACKERS] Core team statement on replication in PostgreSQL

adsmail@wars-nicht.de ("Andreas 'ads' Scherbaum") writes:
> On Thu, 29 May 2008 23:02:56 -0400 Andrew Dunstan wrote:
>
>> Well, yes, but you do know about archive_timeout, right? No need to wait
>> 2 hours.
>
> Then you ship 16 MB binary stuff every 30 second or every minute but
> you only have some kbyte real data in the logfile. This must be taken
> into account, especially if you ship the logfile over the internet
> (means: no high-speed connection, maybe even pay-per-traffic) to the
> slave.

If you have that kind of scenario, then you have painted yourself into
a corner, and there isn't anything that can be done to extract you
from it.

Consider: If you have so much update traffic that it is too much to
replicate via WAL-copying, why should we expect that other mechanisms
*wouldn't* also overflow the connection?

If you haven't got enough network bandwidth to use this feature, then
nobody is requiring that you use it. It seems like a perfectly
reasonable prerequisite to say "this requires that you have enough
bandwidth."
--
(reverse (concatenate 'string "ofni.secnanifxunil" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/
"There's nothing worse than having only one drunk head."
-- Zaphod Beeblebrox

--
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-www] RFC: Product directory

On Mon, Jun 02, 2008 at 12:58:49PM +0100, Dave Page wrote:
>
> Again, not sure I see a need. Either it's commercial, OSS, or freeware
> - I don't think there's much scope to have more than one (the obvious
> exception is something like "$99.99, or free to educational users" but
> I'd just class that as commercial).

Seems like you need a "multiple" class, then. (Dual licenses are
pretty common.)

A
--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

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

[pgadmin-hackers] column reordering

Hi,

As pgAdmin advances, is it feasible to include a column reordering
feature as outlined here?

http://www.mail-archive.com/pgsql-general@postgresql.org/msg73113.html

Thanks,
Kev

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

Re: [pgsql-www] RFC: Product directory

On Mon, Jun 2, 2008 at 4:28 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>> Agreed but a) we already display that data and b) it's useful to give
>> users a ballpark figure, even if something like "$99.99US as at
>> 15/12/2007"
>
> I have to go with fetter here. Pricing is not our concern.

I actually feel quite strongly about that one - it's not *our*
concern, but it is the concern of the users. There's nothing worse
than researching a product, and eventually finding out after trawling
the website for half an hour that it's way too expensive. As an
end-user I always wanted to see a ballpark figure up front.

>> Possibly. Makes the coding & management a little more tricky though.
>> If we were to do that perhaps it should be part of a larger project to
>> have a directory of vendors/publishers etc for news, events, services
>> and products.
>
> Yeah I mentioned this in my previous post. Publisher really needs to be
> pushed out. There is entirely too much redundant information that can be
> accumulated.

OK so the difficulty here/previously is that you are essentially
saying I need to write a publisher management system and a product
management system, and update sponsors, news, events, services etc to
use that data as well. Nice in theory, but not something I have time
to do.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Re: [HACKERS] Case-Insensitve Text Comparison

On Jun 2, 2008, at 06:51, Andrew Sullivan wrote:

> On Sun, Jun 01, 2008 at 10:13:07PM -0700, David E. Wheeler wrote:
>
>> What locale is right? If I have a Web app, there could be data in
>> many
>> different languages in a single table/column.
>
> I think the above amounts to a need for per-session locale settings or
> something, no?

Yes, that's what I was getting at.

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] phrase search

> I have attached a patch for phrase search with respect to the cvs head.
> Basically it takes a a phrase (text) and a TSVector. It checks if the
> relative positions of lexeme in the phrase are same as in their
> positions in TSVector.

Ideally, phrase search should be implemented as new operator in tsquery, say #
with optional distance. So, tsquery 'foo #2 bar' means: find all texts where
'bar' is place no far than two word from 'foo'. The complexity is about complex
boolean expressions ( 'foo #1 ( bar1 & bar2 )' ) and about several languages as
norwegian or german. German language has combining words, like a footboolbar -
and they have several variants of splitting, so result of to_tsquery('foo #
footboolbar') will be a 'foo # ( ( football & bar ) | ( foot & ball & bar ) )'
where variants are connected with OR operation.

Of course, phrase search should be able to use indexes.
>
> If the configuration for text search is "simple", then this will produce
> exact phrase search. Otherwise the stopwords in a phrase will be ignored
> and the words in a phrase will only be matched with the stemmed lexeme.

Your solution can't be used as is, because user should use tsquery too to use an
index:

column @@ to_tsquery('phrase search') AND is_phrase_present('phrase search',
column)

First clause will be used for index scan and it will fast search a candidates.

> For my application I am using this as a separate shared object. I do not
> know how to expose this function from the core. Can someone explain how
> to do this?

Look at contrib/ directory in pgsql's source code - make a contrib module from
your patch. As an example, look at adminpack module - it's rather simple.

Comments of your code:
1)
+#ifdef PG_MODULE_MAGIC
+PG_MODULE_MAGIC;
+

[GENERAL] dblink() cursor error/issue (TopMemoryContext)

Hello all,

I'm trying to code a function to copy rows from one machine to another
using dblink and cursors:

...
perform dblink_connect ('dbname=db1...host=othermachine.com');
perform dblink_open ('cur_other1', 'SELECT col1 FROM tab1');

loop
fnd := 0;
for rec in
-- grab a 1000 rows at a time
SELECT col1 FROM dblink_fetch ('cur_other1', 1000)
AS tab1 (col1 text)
loop
begin
INSERT INTO tab1 (col1) VALUES (rec.col1);
...
exception when unique_violation then
-- ignore dups
end;
fnd := 1
end loop;
if fnd = 0 then
exit;
end if;
end loop;

perform dblink_close ('cur_other1');
perform dblink_disconnect();


This runs fine for a while, then starts vomiting:

TopMemoryContext: 44175408 total in 5388 blocks; 94224 free (5394 chunks);
44081184 used
SPI Plan: 3072 total in 2 blocks; 2000 free (0 chunks); 1072 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
SPI Plan: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used
SPI Plan: 3072 total in 2 blocks; 1328 free (0 chunks); 1744 used
SPI Plan: 7168 total in 3 blocks; 3896 free (0 chunks); 3272 used
SPI Plan: 7168 total in 3 blocks; 3896 free (0 chunks); 3272 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 7168 total in 3 blocks; 1504 free (0 chunks); 5664 used
PL/PgSQL function context: 8192 total in 1 blocks; 6928 free (5 chunks);
1264 used
SPI Plan: 3072 total in 2 blocks; 1808 free (0 chunks); 1264 used
SPI Plan: 1024 total in 1 blocks; 96 free (0 chunks); 928 used
SPI Plan: 3072 total in 2 blocks; 1664 free (0 chunks); 1408 used
SPI Plan: 3072 total in 2 blocks; 1312 free (0 chunks); 1760 used
PL/PgSQL function context: 24576 total in 2 blocks; 12112 free (10
chunks); 12464 used
SPI Plan: 15360 total in 4 blocks; 7640 free (0 chunks); 7720 used
Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks); 3320 used
Record information cache: 8192 total in 1 blocks; 1800 free (0 chunks);
6392 used
SPI Plan: 3072 total in 2 blocks; 1576 free (0 chunks); 1496 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
SPI Plan: 3072 total in 2 blocks; 1760 free (0 chunks); 1312 used
SPI Plan: 3072 total in 2 blocks; 1856 free (0 chunks); 1216 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
PL/PgSQL function context: 24576 total in 2 blocks; 7784 free (16 chunks);
16792 used
CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
Rendezvous variable hash: 8192 total in 1 blocks; 3848 free (0 chunks);
4344 used
PLpgSQL function cache: 24596 total in 2 blocks; 5904 free (0 chunks);
18692 used
TopTransactionContext: 8380416 total in 10 blocks; 3213936 free (0
chunks); 5166480 used
SPI Exec: 8192 total in 1 blocks; 7992 free (0 chunks); 200 used
ExecutorState: 8192 total in 1 blocks; 3080 free (0 chunks); 5112 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
SPI Proc: 8192 total in 1 blocks; 6520 free (5 chunks); 1672 used
CurTransactionContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AfterTriggerEvents: 8192 total in 1 blocks; 8136 free (0 chunks); 56 used
ExecutorState: 24576 total in 2 blocks; 4472 free (4 chunks); 20104 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used

...

repeat above until 150GB+ logfile, then ctrl-c

On the tty where I've called the function, after hitting ctrl-c, I get:

...
ERROR: out of memory
DETAIL: Failed on request of size 1291220.
...
PANIC: ERRORDATA_STACK_SIZE exceeded
...

I'm trying to use cursors so that I don't run out of memory - yet I seem
to be running out of memory anyway.

Doing this the other way round works OK:

perform dblink_connect('dbname=db1...host=othermachine.com');
for rec in
SELECT col1 FROM tab1 -- this uses cursors in function auto'ally
loop
perform dblink_exec ('INSERT INTO tab1 ..'||rec.col1||'...');
...
end loop;
perform dblink_disconnect();
...


I must be doing something stupid here.

Any comments are welcome.

Regards
Henry


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