Re: [GENERAL] quote in string
On May 21, 2008, at 7:36 PM, finecur wrote:
select * from my_flexible_sql_function('select * from employee where
dep ='Eng'')
You need to escape that string like 'select * from employee where dep
=''Eng'' '
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,483665eb927662006790369!
--
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] Fragments in tsearch2 headline
Thanks Pierre for porting this! I just tested this for my application
and it works. There was a small bug in that startHL has to be
initialized to 0 for each chosen cover. I fixed that and attached the
new patch.
Teodor did not want a separate function. He wanted it as an extension to
ts_headline. One way to do this will be to invoke it only when options
like MaxCoverSize is used. It will be slightly ugly though.
It still seems to have bugs. I will try to clean that up.
-Sushant.
On Thu, 2008-05-22 at 13:31 +0200, Pierre-Yves Strub wrote:
> Hi,
>
> I've ported the patch of Sushant Sinha for fragmented headlines to pg8.3.1
> (http://archives.postgresql.org/pgsql-general/2007-11/msg00508.php)
>
> W.r.t, http://archives.postgresql.org/pgsql-general/2008-03/msg00806.php
> I can continue the work until this becomes an acceptable patch for pg.
>
> Pierre-yves.
diff -Nurb postgresql-8.3.1/contrib/tsearch2/tsearch2.c postgresql-8.3.1-orig/contrib/tsearch2/tsearch2.c
--- postgresql-8.3.1/contrib/tsearch2/tsearch2.c 2008-01-01 14:45:45.0 -0500
+++ postgresql-8.3.1-orig/contrib/tsearch2/tsearch2.c 2008-05-22 18:44:16.0 -0400
@@ -82,6 +82,7 @@
Datum tsa_to_tsquery_name(PG_FUNCTION_ARGS);
Datum tsa_plainto_tsquery_name(PG_FUNCTION_ARGS);
Datum tsa_headline_byname(PG_FUNCTION_ARGS);
+Datum tsa_headline_with_fragments(PG_FUNCTION_ARGS);
Datum tsa_ts_stat(PG_FUNCTION_ARGS);
Datum tsa_tsearch2(PG_FUNCTION_ARGS);
Datum tsa_rewrite_accum(PG_FUNCTION_ARGS);
@@ -101,6 +102,7 @@
PG_FUNCTION_INFO_V1(tsa_to_tsquery_name);
PG_FUNCTION_INFO_V1(tsa_plainto_tsquery_name);
PG_FUNCTION_INFO_V1(tsa_headline_byname);
+PG_FUNCTION_INFO_V1(tsa_headline_with_fragments);
PG_FUNCTION_INFO_V1(tsa_ts_stat);
PG_FUNCTION_INFO_V1(tsa_tsearch2);
PG_FUNCTION_INFO_V1(tsa_rewrite_accum);
@@ -358,6 +360,24 @@
return result;
}
+/* tsa_headline_with_fragments(text, tsvector, text, tsquery, text) */
+Datum
+tsa_headline_with_fragments(PG_FUNCTION_ARGS)
+{
+ text *cfgname = PG_GETARG_TEXT_P(0);
+ Datum arg1 = PG_GETARG_DATUM(1);
+ Datum arg2 = PG_GETARG_DATUM(2);
+ Datum arg3 = PG_GETARG_DATUM(3);
+ Datum arg4 = PG_GETARG_DATUM(4);
+ Oid config_oid;
+
+ config_oid = TextGetObjectId(regconfigin, cfgname);
+
+ return DirectFunctionCall5(ts_headline_with_fragments,
+ ObjectIdGetDatum(config_oid),
+ arg1, arg2, arg3, arg4);
+}
+
/*
* tsearch2 version of update trigger
*
diff -Nurb postgresql-8.3.1/contrib/tsearch2/tsearch2.sql.in postgresql-8.3.1-orig/contrib/tsearch2/tsearch2.sql.in
--- postgresql-8.3.1/contrib/tsearch2/tsearch2.sql.in 2007-11-28 14:33:04.0 -0500
+++ postgresql-8.3.1-orig/contrib/tsearch2/tsearch2.sql.in 2008-05-22 18:44:16.0 -0400
@@ -384,6 +384,11 @@
LANGUAGE INTERNAL
RETURNS NULL ON NULL INPUT IMMUTABLE;
+CREATE FUNCTION headline_with_fragments(text, text, tsquery, text)
+RETURNS text
+AS 'MODULE_PATHNAME', 'tsa_headline_with_fragments'
+LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;
+
-- CREATE the OPERATOR class
CREATE OPERATOR CLASS gist_tsvector_ops
FOR TYPE tsvector USING gist
diff -Nurb postgresql-8.3.1/src/backend/tsearch/ts_parse.c postgresql-8.3.1-orig/src/backend/tsearch/ts_parse.c
--- postgresql-8.3.1/src/backend/tsearch/ts_parse.c 2008-01-01 14:45:52.0 -0500
+++ postgresql-8.3.1-orig/src/backend/tsearch/ts_parse.c 2008-05-22 18:49:53.0 -0400
@@ -578,6 +578,112 @@
FunctionCall1(&(prsobj->prsend), PointerGetDatum(prsdata));
}
+#define COVER_SEP "..."
+#define COVER_SEP_LEN (sizeof(COVER_SEP)-1)
+
+void
+hlparsetext_with_covers(Oid cfgId,
+HeadlineParsedText *prs,
+TSQuery query,
+text *in,
+struct coverpos*covers,
+int4numcovers)
+{
+ TSParserCacheEntry *prsobj;
+ TSConfigCacheEntry *cfg;
+void *prsdata;
+ LexizeData ldata;
+ int4 icover, startpos, endpos, currentpos = 0;
+
+ char *lemm = NULL;
+ int4lenlemm = 0;
+ ParsedLex *lexs;
+ int4type, startHL = 0;
+ TSLexeme *norms;
+ int4oldnumwords, newnumwords, i;
+
+ cfg = lookup_ts_config_cache(cfgId);
+ prsobj = lookup_ts_parser_cache(cfg->prsId);
+
+prsdata = (void*) DatumGetPointer(FunctionCall2(&(prsobj->prsstart),
+PointerGetDatum(VARDATA(in)),
+Int32GetDatum(VARSIZE(in) - VARHDRSZ)));
+
+ LexizeInit(&ldata, cfg);
+
+ for (icover = 0; icover < numcovers; icover++)
+{
+if (!covers[icover].in)
+continue;
+
+startpos = covers[icover].startpos;
+endpos = covers[icover].endpos;
+
+ if (curre
Re: [GENERAL] Extracting \ Generate DDL for existing object permissions
Raymond O'Donnell wrote: On 22/05/2008 22:33, smiley2211 wrote: 1) dump database A - 2) Extract permissions from database B - (HOW) How about dumping B using the plain-text format, then running the output through grep, looking for lines starting with "GRANT..."? Hint: sometimes easier is to run pg_dump -fc and then pg_restore -l to create an object list and operate your filters on this list, then use the resulting list with pg_restore -L With GRANTS, which are one-liners, it works either way. But the general approach works very well with any statement, think of multiline create table, create function ... Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Is this correct usage of generate_series?
On Thu, May 22, 2008 at 10:41 PM, Hiroaki Nakamura <[EMAIL PROTECTED]> wrote: > Hi, there. > > These three queries below works as I expect, which is wonderful, but are > these correct usages? > If these are intended features, I would like them to be documented at > http://www.postgresql.org/docs/8.3/interactive/functions-srf.html > > => select generate_series(1, 3) as i; > i > --- > 1 > 2 > 3 > (3 rows) > > => select 'a' as a, generate_series(1, 3) as i; > a | i > ---+--- > a | 1 > a | 2 > a | 3 > (3 rows) > > => select 'a' as a, i from generate_series(1, 3) i; > a | i > ---+--- > a | 1 > a | 2 > a | 3 > (3 rows) > > Here is an example using this technique, which shows column positions and > names in > the specified index. > > select attnum, attname > from pg_catalog.pg_attribute a > join ( > select > indrelid, indkey[i] as pos > from ( > select > indrelid, indkey, generate_series(lb, ub) as i > from ( > select indrelid, indkey, array_lower(indkey, 1) as lb, > array_upper(indkey, 1) as ub > from pg_catalog.pg_index > where indexrelid = ( > select oid > from pg_catalog.pg_class > where relnamespace = (select oid from > pg_catalog.pg_namespace where nspname = 'public') > and relkind = 'i' > and relname = '' > ) > ) x > ) y > ) z on a.attrelid = z.indrelid and a.attnum = z.pos; > > > All three of these are correct usages. This is an SRF (Set Returning Function), and Postgres allows you to use an SRF in the SELECT list. People have been using tricks similar to what you showed above, and it works great. I have seen discussions in the past on the -hackers mailing list about deprecating the usage of SRFs in select list, but honestly, I don't see this usage being deprecated anytime soon; it's pretty useful, and I would call it an extension to the SQL language. And as long as it's nit buggy, and ALA it doesn't surprise people in a wrong way, I think such usage will remain. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
Re: [GENERAL] Results of stored procedures in WHERE clause
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of Gordon > Sent: Tuesday, May 20, 2008 11:03 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Results of stored procedures in WHERE clause > > I have a table representing tree structures of pages on a website. > they have an itm_id column (integer key) and an itm_parent column > (pointer to item's parent node). Any item with an itm_parent of 0 is > a root node, representing a website. Anything with a non-zero parent > is a non-root node representing a folder or document in a website. > > I need to be able to do queries that restrict my result set to items > belonging to a specified site and ignore all nodes that belong to > different sites. To determine the ID of the site an item belongs to I > wrote a stored procedure: ... I would highly recommend checking out the ltree contrib module. It will make this task much easier, as long as you are not locked into the current database design. Manually performing multi-level parent/child relationships on a table can become quite painful. You will probably get faster results using ltree also due to the fact that you can perform what you want with one query instead of looping through multiple queries (very important if your tree gets big). http://www.sai.msu.su/~megera/postgres/gist/ltree/ Justin Pasher -- 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] Error: Could not open relation...
On Fri, 2008-05-23 at 00:08 +0100, Howard Cole wrote: > Can anyone give me a hint how to trace the cause of this error message > in the error log: > > ERROR could not open relation 1663/20146/128342: Permission Denied > > Running 8.2.7 on W2K3. > Looks like someone or something changed the permissions on the postgresql folders or files. Sincerely, Joshua D. Drake > Thanks. > > Howard Cole. > www.selestial.com > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error: Could not open relation...
Can anyone give me a hint how to trace the cause of this error message in the error log: ERROR could not open relation 1663/20146/128342: Permission Denied Running 8.2.7 on W2K3. Thanks. Howard Cole. www.selestial.com -- 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] Results of stored procedures in WHERE clause
> > I need to be able to do queries that restrict my result set to items > belonging to a specified site and ignore all nodes that belong to > different sites. To determine the ID of the site an item belongs to I > wrote a stored procedure: > > This returns the ID of the root node for non-root nodes, the node's > own ID for root-nodes and NULL for invalid IDs. > > I'm writing a query to do document searching (the version given is > simplified to the problem in hand). > > SELECT cms_v_items.* , > getroot (cms_v_items.itm_id) AS itm_root > FROM cms_v_items > WHERE itm_root = ?; > > I was hoping this query would return a set of items that had the same > root node. Instead it throws an error, column itm_root does not > exist. > > I'm obviously doing something wrong here, but what? > I don't think you can reference an alias in the where clause. You'll have to repeat it, like this: SELECT cms_v_items.* , getroot (cms_v_items.itm_id) AS itm_root FROM cms_v_items WHERE getroot (cms_v_items.itm_id) = ?; Don't worry, I think with the function marked STABLE, postgresql is smart enough not to call it twice. I think you could further optimize your function doing something like this: SELECT cms_v_items.* , getroot (cms_v_items.itm_parent) AS itm_root FROM cms_v_items WHERE (itm_parent = ? OR getroot (cms_v_items.itm_parent) = ?; This will save one loop. Keep in mind, both queries will perform the getroot() function call for every single row in cms_v_items. You may want to experiment with a function that takes the root ID as a parameter and returns an array or a rowset, of just the items beneath that root. Then you'd use that function in your query by joining to the results or using "= ANY". This might be faster: SELECT * from FROM cms_v_items WHERE itm_id = ANY(item_in_root(?)); -- 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] quote in string
On Wed, May 21, 2008 at 11:36 AM, finecur <[EMAIL PROTECTED]> wrote:
> Hi, I have a function and it's interface is :
>
>my_flexible_sql_function(cmd)
>
> where cmd is another sql command. I call this function like this:
>
> select * from my_flexible_sql_function('select * from employee where
> id < 100')
>
> Inside this functioin, I will first retrieve all data by calling
> 'select * from employee where id < 100' and do some calculation about
> the result and then return.
>
> No I run into a problem, I need to call it like this:
>
> select * from my_flexible_sql_function('select * from employee where
> dep ='Eng'')
>
> But looks like there is a problem to include a string inside the cmd.
> The way I write 'Eng' is not right. Is there a way to include quote in
> string?
Assuming you're running a fairly recent pgsql version (8.0 and up) you
can use $$ quoting:
select * from my_flexible_sql_function($$select * from employee where
dep ='Eng' $$)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Results of stored procedures in WHERE clause
I have a table representing tree structures of pages on a website. they have an itm_id column (integer key) and an itm_parent column (pointer to item's parent node). Any item with an itm_parent of 0 is a root node, representing a website. Anything with a non-zero parent is a non-root node representing a folder or document in a website. I need to be able to do queries that restrict my result set to items belonging to a specified site and ignore all nodes that belong to different sites. To determine the ID of the site an item belongs to I wrote a stored procedure: CREATE OR REPLACE FUNCTION cms.getroot(node integer) RETURNS integer AS $BODY$DECLARE thisnodeinteger := node; thisparent integer := node; BEGIN WHILE thisparent != 0 LOOP SELECT itm_id, itm_parent INTO thisnode, thisparent FROM cms.cms_items WHERE itm_id = thisparent; END LOOP; RETURN thisnode; END; $BODY$ LANGUAGE 'plpgsql' STABLE COST 100; This returns the ID of the root node for non-root nodes, the node's own ID for root-nodes and NULL for invalid IDs. I'm writing a query to do document searching (the version given is simplified to the problem in hand). SELECT cms_v_items.* , getroot (cms_v_items.itm_id) AS itm_root FROM cms_v_items WHERE itm_root = ?; I was hoping this query would return a set of items that had the same root node. Instead it throws an error, column itm_root does not exist. I'm obviously doing something wrong here, but what? -- 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] Results of stored procedures in WHERE clause
On May 20, 5:02 pm, Gordon <[EMAIL PROTECTED]> wrote: > I have a table representing tree structures of pages on a website. > they have an itm_id column (integer key) and an itm_parent column > (pointer to item's parent node). Any item with an itm_parent of 0 is > a root node, representing a website. Anything with a non-zero parent > is a non-root node representing a folder or document in a website. > > I need to be able to do queries that restrict my result set to items > belonging to a specified site and ignore all nodes that belong to > different sites. To determine the ID of the site an item belongs to I > wrote a stored procedure: > > CREATE OR REPLACE FUNCTION cms.getroot(node integer) > RETURNS integer AS > $BODY$DECLARE > thisnodeinteger := node; > thisparent integer := node; > BEGIN > WHILE thisparent != 0 LOOP > SELECT itm_id, itm_parent > INTO thisnode, thisparent > FROM cms.cms_items > WHERE itm_id = thisparent; > END LOOP; > RETURN thisnode; > END; > $BODY$ > LANGUAGE 'plpgsql' STABLE > COST 100; > > This returns the ID of the root node for non-root nodes, the node's > own ID for root-nodes and NULL for invalid IDs. > > I'm writing a query to do document searching (the version given is > simplified to the problem in hand). > > SELECT cms_v_items.* , > getroot (cms_v_items.itm_id) AS itm_root > FROM cms_v_items > WHERE itm_root = ?; > > I was hoping this query would return a set of items that had the same > root node. Instead it throws an error, column itm_root does not > exist. > > I'm obviously doing something wrong here, but what? Is what I'm trying to do even possible? I'm really struggling to find much help with Google on this topic. -- 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] tsearch2 on-demand dictionary loading & using functions in tsearch2
Craig Ringer wrote: This is probably a stupid question, but: with PostgreSQL's use of shared memory, is it possible to load dictionaries into a small reserved shm area when the first backend starts, then use the preloaded copy in subsequent backends? That way the postmaster doesn't have to do any risky work. Anything that reduces backend startup costs and per-backend unshared memory would have to be a good thing. I've found it useful in the past to share resources with an mmap()ped file, too, especially if I want write protection from some or all processes. If the postmaster forked a process to generate the mmap()able compiled dictionary files on startup then it'd be pretty safe from any misbehaviour of the dictionary compiling process. Then again, I can't say I've personally noticed the cost of loading tsearch2 dictionaries. So the dictionary will be parsed on the first usage by the given backend, and from that moment on, all running backends and all backends that will be spawned afterwards will have access to the parsed dictionary structures thanks to the shm? That seems to solve all issues - speed, memory and updating. Would this be a way to go? Obviously, it might boil down to "write a patch", but if someone actually wrote a patch, would this approach be acceptable? Thanks, Steve PS: Please, CC me, as I am off the list. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] quote in string
Hi, I have a function and it's interface is :
my_flexible_sql_function(cmd)
where cmd is another sql command. I call this function like this:
select * from my_flexible_sql_function('select * from employee where
id < 100')
Inside this functioin, I will first retrieve all data by calling
'select * from employee where id < 100' and do some calculation about
the result and then return.
No I run into a problem, I need to call it like this:
select * from my_flexible_sql_function('select * from employee where
dep ='Eng'')
But looks like there is a problem to include a string inside the cmd.
The way I write 'Eng' is not right. Is there a way to include quote in
string?
Thanks,
ff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] deadlock debug methodology
Hi All, I have a deadlock situation, two transactions waiting on each other to complete. Based on the details below, does anyone have recommendations for me. Thanks. Regards, A. I am using: rpm -qa|grep postgres compat-postgresql-libs-4-2PGDG.rhel5_x86_64 postgresql-server-8.2.6-1PGDG.rhel5 postgresql-8.2.6-1PGDG.rhel5 postgresql-devel-8.2.6-1PGDG.rhel5 postgresql-libs-8.2.6-1PGDG.rhel5 I set 'deadlock_timeout = 1h' in order to have time to inspect pg_locks. The locks are: db0=# select * from pg_locks where not granted; locktype| database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode| granted ---+--+--+--+---+---+-+---+--+-+--+---+- transactionid | | | | | 1407 | | | |1404 | 8303 | ShareLock | f transactionid | | | | | 1404 | | | |1407 | 8277 | ShareLock | f (2 rows) Each transaction seems to be waiting on a row-level lock the other has acquired. The tuples are: db0=# select * from pg_locks where locktype='tuple'; locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted --+--+--+--+---+---+-+---+--+-+--+---+- tuple|16384 |16576 | 38 | 6 | | | | |1407 | 8277 | ExclusiveLock | t tuple|16384 |16576 | 38 | 5 | | | | |1404 | 8303 | ShareLock | t (2 rows) The corresponding rows are: db0=# select id from tt where ctid = '(38,6)'; id - 600 (1 row) db0=# select id from tt where ctid = '(38,5)'; id - 611 (1 row) Note that the id column is defined as 'id serial primary key'. The two queries in effect in each transaction are found using: select current_query from pg_stat_activity where procpid = 8303; select current_query from pg_stat_activity where procpid = 8277; Careful inspection of these (unfortunately complex) queries seems to indicate row-level locks are acquired in consistent order, assuming that any command of the type update tt where will always lock rows in a consistent order (can someone confirm that it is necessarily the case). Therefore, it is not clear to me how this deadlock situation arises. Does anyone have a recommendation?
[GENERAL] Is this correct usage of generate_series?
Hi, there. These three queries below works as I expect, which is wonderful, but are these correct usages? If these are intended features, I would like them to be documented at http://www.postgresql.org/docs/8.3/interactive/functions-srf.html => select generate_series(1, 3) as i; i --- 1 2 3 (3 rows) => select 'a' as a, generate_series(1, 3) as i; a | i ---+--- a | 1 a | 2 a | 3 (3 rows) => select 'a' as a, i from generate_series(1, 3) i; a | i ---+--- a | 1 a | 2 a | 3 (3 rows) Here is an example using this technique, which shows column positions and names in the specified index. select attnum, attname from pg_catalog.pg_attribute a join ( select indrelid, indkey[i] as pos from ( select indrelid, indkey, generate_series(lb, ub) as i from ( select indrelid, indkey, array_lower(indkey, 1) as lb, array_upper(indkey, 1) as ub from pg_catalog.pg_index where indexrelid = ( select oid from pg_catalog.pg_class where relnamespace = (select oid from pg_catalog.pg_namespace where nspname = 'public') and relkind = 'i' and relname = '' ) ) x ) y ) z on a.attrelid = z.indrelid and a.attnum = z.pos; Thanks in advance, Hiroaki Nakamura
Re: [GENERAL] Extracting \ Generate DDL for existing object permissions
On 22/05/2008 22:33, smiley2211 wrote: 1) dump database A - 2) Extract permissions from database B - (HOW) How about dumping B using the plain-text format, then running the output through grep, looking for lines starting with "GRANT..."? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Extracting \ Generate DDL for existing object permissions
Hello all, How do I capture the EXISTING permissions for a database? I know you can see them via \z or \dp however I need ALL permissions to be captured so I can reapply after performing a database restore. ex: 1) dump database A - 2) Extract permissions from database B - (HOW) 3) load database B from A - 4) execute sql to put permissions back to database B from step 2 Thanks...Michelle -- View this message in context: http://www.nabble.com/Extracting-%5C-Generate-DDL-for-existing-object-permissions-tp17413948p17413948.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] deadlock debug methodology question
On Thu, May 22, 2008 at 4:20 PM, Bill Moran <[EMAIL PROTECTED]> wrote: > > Please don't top-post. I've attempted to reconstruct the conversation > flow. > > In response to "antiochus antiochus" <[EMAIL PROTECTED]>: > > > > On Thu, May 22, 2008 at 2:57 PM, Bill Moran < > [EMAIL PROTECTED]> > > wrote: > > > > > In response to "antiochus antiochus" <[EMAIL PROTECTED]>: > > > > > > > > I have a deadlock situation, two transactions waiting on each other > to > > > > complete. Based on the details below, would anyone have > recommendations > > > for > > > > me, please? > > > > > > I have a theory on deadlocks, and that theory is that it's damn near > > > impossible to track them all down, so your best bet is to wrap all > > > SQL calls in a function that detects deadlock and sleep/retries. > > > > One possibility might then seem to do something like: > > > > update table tt where ID in (select ID from tt where ... order by ID > asc > > for update); > > > > but unfortunately 'for update' is not allows in subqueries. Therefore, > one > > could do: > > > > select ID from tt where ... order by ID asc for update; > > update table tt where ...; > > > > However, in read committed mode, it is not guaranteed that the subset of > rows > > selected with the two 'where' tests will be the same... > > I can see two solutions: > > BEGIN; > SET TRANSACTION SERIALIZABLE > select ID from tt where ... order by ID asc for update; > update table tt where ...; > COMMIT; > > or > > BEGIN; > LOCK TABLE tt IN SHARE MODE; > select ID from tt where ... order by ID asc for update; > update table tt where ...; > COMMIT; > > Depending on exactly what you need to accomplish. > > > > [snip] > > > > > > > Careful inspection of these (unfortunately complex) queries seems to > > > > indicate row-level locks are acquired in consistent order, assuming > that > > > any > > > > command of the type > > > > > > > > update tt where > > > > > > > > will always lock rows in a consistent order (can someone confirm that > it > > > is > > > > necessarily the case). > > > > > > I believe that assertion is incorrect. Without seeing your entire > > > query, I can only speculate, but unless you have an explicit ordering > > > clause, there's no guarantee what order rows will be accessed in. > > > > > > Try putting an explicit ORDER BY in the queries and see if the problem > > > goes away. > > -- > Bill Moran > Collaborative Fusion Inc. > http://people.collaborativefusion.com/~wmoran/ > > [EMAIL PROTECTED] > Phone: 412-422-3463x4023 > Thanks, Bill. For the past fews days, I've been making the application work using your second suggestion (what I meant by "some form of explicit locking"). It does works. However, it is the first explicit lock used in this growing application and I was trying to not have any. The rows of this table are, from a business logic perspective, partitioned into distinct sets (think one set per customer) and a transaction would never involve rows across these sets. So in the original design, concurrency is great across customer requests. By using the table-level lock, writing transactions targeting these non-overlapping per-customer sets end up waiting on each other... If that is the best we can do, that is it. However, I do not understand why acquiring row-level locks in consistent order does not seem to be enough, or if that is so, why my methodology to enforce this ordering is flawed. Note that I also use foreign keys and I am aware of the fact that constraints are verified (and lock acquired) after row insertion and this is taken into account as well. I could have a "per-customer" lock, to improve concurrency across customers while avoiding deadlocks, or use seralizable transactions, but I was wondering if a methodology to acomplish what I was originally trying to do has been documented anywhere. Thanks a lot for your suggestions. Regards, A.
Re: [GENERAL] deadlock debug methodology question
Please don't top-post. I've attempted to reconstruct the conversation flow. In response to "antiochus antiochus" <[EMAIL PROTECTED]>: > > On Thu, May 22, 2008 at 2:57 PM, Bill Moran <[EMAIL PROTECTED]> > wrote: > > > In response to "antiochus antiochus" <[EMAIL PROTECTED]>: > > > > > > I have a deadlock situation, two transactions waiting on each other to > > > complete. Based on the details below, would anyone have recommendations > > for > > > me, please? > > > > I have a theory on deadlocks, and that theory is that it's damn near > > impossible to track them all down, so your best bet is to wrap all > > SQL calls in a function that detects deadlock and sleep/retries. > > One possibility might then seem to do something like: > > update table tt where ID in (select ID from tt where ... order by ID asc > for update); > > but unfortunately 'for update' is not allows in subqueries. Therefore, one > could do: > > select ID from tt where ... order by ID asc for update; > update table tt where ...; > > However, in read committed mode, it is not guaranteed that the subset of rows > selected with the two 'where' tests will be the same... I can see two solutions: BEGIN; SET TRANSACTION SERIALIZABLE select ID from tt where ... order by ID asc for update; update table tt where ...; COMMIT; or BEGIN; LOCK TABLE tt IN SHARE MODE; select ID from tt where ... order by ID asc for update; update table tt where ...; COMMIT; Depending on exactly what you need to accomplish. > > [snip] > > > > > Careful inspection of these (unfortunately complex) queries seems to > > > indicate row-level locks are acquired in consistent order, assuming that > > any > > > command of the type > > > > > > update tt where > > > > > > will always lock rows in a consistent order (can someone confirm that it > > is > > > necessarily the case). > > > > I believe that assertion is incorrect. Without seeing your entire > > query, I can only speculate, but unless you have an explicit ordering > > clause, there's no guarantee what order rows will be accessed in. > > > > Try putting an explicit ORDER BY in the queries and see if the problem > > goes away. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] intermittent problems with ident authentication
Anybody? On Tue, 20 May 2008, Ben wrote: I have some scripts that connect to my 8.2 postgres server using ident authentication, and maybe ~10% of the time, authentication fails like so: 2008-05-20 00:22:54 UTC LOG: invalidly formatted response from Ident server: "49205 , 5432 : ERROR :dba " xinetd's logs show normal auth activity at these times. This is on CentOS 5.1. Has anybody else seen anything like this? Or even better, figured out what was going on? -- 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] deadlock debug methodology question
Thanks for your responses, I appreciate the help. I gather from this that 2 transactions concurrently running the exact same: update table tt where ... could end up in deadlock because it is not garanteed row-level locks are taken in a consistent order in an update. One possibility might then seem to do something like: update table tt where ID in (select ID from tt where ... order by ID asc for update); but unfortunately 'for update' is not allows in subqueries. Therefore, one could do: select ID from tt where ... order by ID asc for update; update table tt where ...; However, in read committed mode, it is not garanteed that the subset of rows selected with the two 'where' tests will be the same... Therefore, it seems impossible to solve this issue without using some form of explicit locking. Does this make sense to you? On Thu, May 22, 2008 at 2:57 PM, Bill Moran <[EMAIL PROTECTED]> wrote: > In response to "antiochus antiochus" <[EMAIL PROTECTED]>: > > > > I have a deadlock situation, two transactions waiting on each other to > > complete. Based on the details below, would anyone have recommendations > for > > me, please? > > I have a theory on deadlocks, and that theory is that it's damn near > impossible to track them all down, so your best bet is to wrap all > SQL calls in a function that detects deadlock and sleep/retries. > > [snip] > > > Careful inspection of these (unfortunately complex) queries seems to > > indicate row-level locks are acquired in consistent order, assuming that > any > > command of the type > > > > update tt where > > > > will always lock rows in a consistent order (can someone confirm that it > is > > necessarily the case). > > I believe that assertion is incorrect. Without seeing your entire > query, I can only speculate, but unless you have an explicit ordering > clause, there's no guarantee what order rows will be accessed in. > > Try putting an explicit ORDER BY in the queries and see if the problem > goes away. > > -- > Bill Moran > Collaborative Fusion Inc. > http://people.collaborativefusion.com/~wmoran/ > > [EMAIL PROTECTED] > Phone: 412-422-3463x4023 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] XML Support related questions
Hello PostgreSQL XML Experts, I was exploring xml support in postgreSQL and ran into few questions answer to which I couldn't find easily in the documentation. I would appreciate responses to following queries: 1) Can xml column be constrained to be DOCUMENT or CONTENT type? XMLPARSE/XMLSERIALIZE functions allows to set XML option DOCUMENT or CONTENT type. Can we do the same when defining an xml column level? 2) Is there plan in near future to support XML schema validation i.e to ensure inserted xml document conforms to a preregistered set of XML schemas. 3) Support for XQuery - I know it is asking for too much but when do you see it happening in the future. 4) Support for xml indexes - Is it something that is being worked upon for next release? Our application support many underlying databases and I would hate to list out the stuff users cannot do when using xml datatype in postgresql. So far for non-xml functionality we haven't had to this and I hope I don't need to do it for xml as well. Thanks, Brijesh
Re: [GENERAL] deadlock debug methodology question
In response to "antiochus antiochus" <[EMAIL PROTECTED]>: > > I have a deadlock situation, two transactions waiting on each other to > complete. Based on the details below, would anyone have recommendations for > me, please? I have a theory on deadlocks, and that theory is that it's damn near impossible to track them all down, so your best bet is to wrap all SQL calls in a function that detects deadlock and sleep/retries. [snip] > Careful inspection of these (unfortunately complex) queries seems to > indicate row-level locks are acquired in consistent order, assuming that any > command of the type > > update tt where > > will always lock rows in a consistent order (can someone confirm that it is > necessarily the case). I believe that assertion is incorrect. Without seeing your entire query, I can only speculate, but unless you have an explicit ordering clause, there's no guarantee what order rows will be accessed in. Try putting an explicit ORDER BY in the queries and see if the problem goes away. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] Short-circuiting FK check for a newly-added field
On Tue, May 20, 2008 at 02:25:15PM -0400, Decibel! wrote: > I need to add a field to a fairly large table. In the same alter statement > I'd like to add a FK constraint on that new field. Is there any way to > avoid the check of the table that the database is doing right now? The > check is pointless because the newly added field is nothing but NULLs. > I don't see the problem. FK constraints don't fire on NULL values. I think you might be imagining that a problem exists when it doesn't. If the FK column is created as NOT NULL, there is a problem. The best way to handle this case is to add the column (allowing nulls), populate the columns, then alter the column to make it NOT NULL. You can wrap that all in a transaction if you like. > This is version 8.1.mumble. > You can get the version using select version(). -Reece -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is this correct usage of generate_series?
Hi, there. These three queries below works as I expect, which is wonderful, but are these correct usages? If these are intended features, I would like them to be documented at http://www.postgresql.org/docs/8.3/interactive/functions-srf.html => select generate_series(1, 3) as i; i --- 1 2 3 (3 rows) => select 'a' as a, generate_series(1, 3) as i; a | i ---+--- a | 1 a | 2 a | 3 (3 rows) => select 'a' as a, i from generate_series(1, 3) i; a | i ---+--- a | 1 a | 2 a | 3 (3 rows) Here is an example using this technique, which shows column positions and names in the specified index. select attnum, attname from pg_catalog.pg_attribute a join ( select indrelid, indkey[i] as pos from ( select indrelid, indkey, generate_series(lb, ub) as i from ( select indrelid, indkey, array_lower(indkey, 1) as lb, array_upper(indkey, 1) as ub from pg_catalog.pg_index where indexrelid = ( select oid from pg_catalog.pg_class where relnamespace = (select oid from pg_catalog.pg_namespace where nspname = 'public') and relkind = 'i' and relname = '' ) ) x ) y ) z on a.attrelid = z.indrelid and a.attnum = z.pos; Thanks in advance, Hiroaki Nakamura
Re: [GENERAL] Finding records that are not there
You have commcost in the correlated subquery which shouldn't be there. Here are three ways to achieve the results you want: select commcost.maplot, commcost.unitno from commcost where not exists(select null from bldg where commcost.maplot = bldg.maplot and commcost.unitno = bldg.unitno) order by commcost.maplot; select commcost.maplot, commcost.unitno from commcost left join bldg on commcost.maplot = bldg.maplot and commcost.unitno = bldg.unitno where bldg.maplot is null order by commcost.maplot; select commcost.maplot, commcost.unitno from commcost except select bldg.maplot, bldg.unitno from bldg order by maplot; Jon > -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of Owen Hartnett > Sent: Thursday, May 22, 2008 11:22 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Finding records that are not there > > > Hi: > > This is gotta be elementary SQL 101, but I'm having a mental block as > to why this doesn't work. > > I have two tables that have identical index fields, maplot and > unitno, (both indexes span two columns) and I want to find all the > records in the commcost table that don't have a corresponding record > in the bldg file. > > The SQL I've tried is: > > select commcost.maplot, commcost.unitno from commcost > where not exists(select 1 from commcost, bldg > where commcost.maplot = bldg.maplot and > commcost.unitno = bldg.unitno) > order by commcost.maplot > > It returns no records although I know that there are records in > commcost which do not match keys with records from bldg. > > Help! What am I doing wrong? > > -Owen > > -- > 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: [GENERAL] Finding records that are not there
> I have two tables that have identical index fields, maplot and > unitno, (both indexes span two columns) and I want to find all the > records in the commcost table that don't have a corresponding record > in the bldg file. > > The SQL I've tried is: > > select commcost.maplot, commcost.unitno from commcost > where not exists(select 1 from commcost, bldg > where commcost.maplot = bldg.maplot and > commcost.unitno = bldg.unitno) > order by commcost.maplot > > It returns no records although I know that there are records in > commcost which do not match keys with records from bldg. > You shouldn't put "commcost" in your inner select, since it's already in your outer select. Or try this, it's probably faster: Select commcost.maplot, commcost.unitno from commcost c left join bldg b on c.maplot = b.maplot and c.unitno = b.unitno where b.unitno is null -- 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] Finding records that are not there
At 6:47 PM +0200 5/22/08, hubert depesz lubaczewski wrote: On Thu, May 22, 2008 at 12:21:35PM -0400, Owen Hartnett wrote: The SQL I've tried is: select commcost.maplot, commcost.unitno from commcost where not exists(select 1 from commcost, bldg where commcost.maplot = bldg.maplot and commcost.unitno = bldg.unitno) order by commcost.maplot change it to: select commcost.maplot, commcost.unitno from commcost where not exists(select 1 from bldg where commcost.maplot = bldg.maplot and commcost.unitno = bldg.unitno) order by commcost.maplot or simply write: select * from commcost except select * from bldg; depesz Thank you very much for your quick response! -Owen -- 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] Finding records that are not there
On Thu, May 22, 2008 at 12:21:35PM -0400, Owen Hartnett wrote: > The SQL I've tried is: > select commcost.maplot, commcost.unitno from commcost > where not exists(select 1 from commcost, bldg > where commcost.maplot = bldg.maplot and > commcost.unitno = bldg.unitno) > order by commcost.maplot change it to: > select commcost.maplot, commcost.unitno from commcost > where not exists(select 1 from bldg > where commcost.maplot = bldg.maplot and > commcost.unitno = bldg.unitno) > order by commcost.maplot or simply write: select * from commcost except select * from bldg; depesz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Finding records that are not there
Hi: This is gotta be elementary SQL 101, but I'm having a mental block as to why this doesn't work. I have two tables that have identical index fields, maplot and unitno, (both indexes span two columns) and I want to find all the records in the commcost table that don't have a corresponding record in the bldg file. The SQL I've tried is: select commcost.maplot, commcost.unitno from commcost where not exists(select 1 from commcost, bldg where commcost.maplot = bldg.maplot and commcost.unitno = bldg.unitno) order by commcost.maplot It returns no records although I know that there are records in commcost which do not match keys with records from bldg. Help! What am I doing wrong? -Owen -- 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] Installed pgadmin3-1.4.3 with 8.3.1 database..errors
On May 22, 2008, at 6:38 AM, Barbara Stephenson wrote: Hello, We have recently upgraded from 7.4.19 to 8.3.1. I am running Red Hat Enterprise Linux WS release 4 (Nahant Update 6) on my laptop and I finally was able to install pgadmin3-1.4.3. However, when I expand my databases, I get two popups. Does anyone know what that means? I'd guess it's that you're using an obsolete version of pgadmin that doesn't know about system changes. 1.8.2 is the current version, I think. Cheers, Steve FIRST POPUP: An error has occured: ERROR: column op.oprlsortop does not exist LINE 12: LEFT OUTER JOIN pg_operator lso ON lso.oid=op.oprlsortop SECOND POPUP: An error has occured: ERROR: column "opcamid" does not exist LINE 3: JOIN pg_am am ON am.oid=opcamid -- Regards, Barbara Stephenson EDI Specialist/Programmer Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30507 tel: (678)989-3020 fax: (404)935-6171 [EMAIL PROTECTED] www.ohlogistics.com -- 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
[GENERAL] deadlock debug methodology question
Hi All, I have a deadlock situation, two transactions waiting on each other to complete. Based on the details below, would anyone have recommendations for me, please? Regards, A. I am using: rpm -qa|grep postgres compat-postgresql-libs-4-2PGDG.rhel5_x86_64 postgresql-server-8.2.6-1PGDG.rhel5 postgresql-8.2.6-1PGDG.rhel5 postgresql-devel-8.2.6-1PGDG.rhel5 postgresql-libs-8.2.6-1PGDG.rhel5 I set 'deadlock_timeout = 1h' in order to have time to inspect pg_locks. The locks are: db0=# select * from pg_locks where not granted; locktype| database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode| granted ---+--+--+--+---+---+-+---+--+-+--+---+- transactionid | | | | | 1407 | | | |1404 | 8303 | ShareLock | f transactionid | | | | | 1404 | | | |1407 | 8277 | ShareLock | f (2 rows) Each transaction seems to be waiting on a row-level lock the other has acquired. The tuples are: db0=# select * from pg_locks where locktype='tuple'; locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted --+--+--+--+---+---+-+---+--+-+--+---+- tuple|16384 |16576 | 38 | 6 | | | | |1407 | 8277 | ExclusiveLock | t tuple|16384 |16576 | 38 | 5 | | | | |1404 | 8303 | ShareLock | t (2 rows) The corresponding rows are: db0=# select id from tt where ctid = '(38,6)'; id - 600 (1 row) db0=# select id from tt where ctid = '(38,5)'; id - 611 (1 row) Note that the id column is defined as 'id serial primary key'. The two queries in effect in each transaction are found using: select current_query from pg_stat_activity where procpid = 8303; select current_query from pg_stat_activity where procpid = 8277; Careful inspection of these (unfortunately complex) queries seems to indicate row-level locks are acquired in consistent order, assuming that any command of the type update tt where will always lock rows in a consistent order (can someone confirm that it is necessarily the case). Therefore, it is not clear to me how this deadlock situation arises. Does anyone have a recommendation?
[GENERAL] Installed pgadmin3-1.4.3 with 8.3.1 database..errors
Hello, We have recently upgraded from 7.4.19 to 8.3.1. I am running Red Hat Enterprise Linux WS release 4 (Nahant Update 6) on my laptop and I finally was able to install pgadmin3-1.4.3. However, when I expand my databases, I get two popups. Does anyone know what that means? FIRST POPUP: An error has occured: ERROR: column op.oprlsortop does not exist LINE 12: LEFT OUTER JOIN pg_operator lso ON lso.oid=op.oprlsortop SECOND POPUP: An error has occured: ERROR: column "opcamid" does not exist LINE 3: JOIN pg_am am ON am.oid=opcamid -- Regards, Barbara Stephenson EDI Specialist/Programmer Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30507 tel: (678)989-3020 fax: (404)935-6171 [EMAIL PROTECTED] www.ohlogistics.com -- 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] Fragments in tsearch2 headline
Hi,
I've ported the patch of Sushant Sinha for fragmented headlines to pg8.3.1
(http://archives.postgresql.org/pgsql-general/2007-11/msg00508.php)
W.r.t, http://archives.postgresql.org/pgsql-general/2008-03/msg00806.php
I can continue the work until this becomes an acceptable patch for pg.
Pierre-yves.
diff -Nrub postgresql-8.3.1-orig/contrib/tsearch2/tsearch2.c postgresql-8.3.1/contrib/tsearch2/tsearch2.c
--- postgresql-8.3.1-orig/contrib/tsearch2/tsearch2.c 2008-01-01 20:45:45.0 +0100
+++ postgresql-8.3.1/contrib/tsearch2/tsearch2.c 2008-05-22 11:35:07.0 +0200
@@ -82,6 +82,7 @@
Datum tsa_to_tsquery_name(PG_FUNCTION_ARGS);
Datum tsa_plainto_tsquery_name(PG_FUNCTION_ARGS);
Datum tsa_headline_byname(PG_FUNCTION_ARGS);
+Datum tsa_headline_with_fragments(PG_FUNCTION_ARGS);
Datum tsa_ts_stat(PG_FUNCTION_ARGS);
Datum tsa_tsearch2(PG_FUNCTION_ARGS);
Datum tsa_rewrite_accum(PG_FUNCTION_ARGS);
@@ -101,6 +102,7 @@
PG_FUNCTION_INFO_V1(tsa_to_tsquery_name);
PG_FUNCTION_INFO_V1(tsa_plainto_tsquery_name);
PG_FUNCTION_INFO_V1(tsa_headline_byname);
+PG_FUNCTION_INFO_V1(tsa_headline_with_fragments);
PG_FUNCTION_INFO_V1(tsa_ts_stat);
PG_FUNCTION_INFO_V1(tsa_tsearch2);
PG_FUNCTION_INFO_V1(tsa_rewrite_accum);
@@ -358,6 +360,24 @@
return result;
}
+/* tsa_headline_with_fragments(text, tsvector, text, tsquery, text) */
+Datum
+tsa_headline_with_fragments(PG_FUNCTION_ARGS)
+{
+ text *cfgname = PG_GETARG_TEXT_P(0);
+ Datum arg1 = PG_GETARG_DATUM(1);
+ Datum arg2 = PG_GETARG_DATUM(2);
+ Datum arg3 = PG_GETARG_DATUM(3);
+ Datum arg4 = PG_GETARG_DATUM(4);
+ Oid config_oid;
+
+ config_oid = TextGetObjectId(regconfigin, cfgname);
+
+ return DirectFunctionCall5(ts_headline_with_fragments,
+ ObjectIdGetDatum(config_oid),
+ arg1, arg2, arg3, arg4);
+}
+
/*
* tsearch2 version of update trigger
*
diff -Nrub postgresql-8.3.1-orig/contrib/tsearch2/tsearch2.sql.in postgresql-8.3.1/contrib/tsearch2/tsearch2.sql.in
--- postgresql-8.3.1-orig/contrib/tsearch2/tsearch2.sql.in 2007-11-28 20:33:04.0 +0100
+++ postgresql-8.3.1/contrib/tsearch2/tsearch2.sql.in 2008-05-22 11:55:51.0 +0200
@@ -384,6 +384,11 @@
LANGUAGE INTERNAL
RETURNS NULL ON NULL INPUT IMMUTABLE;
+CREATE FUNCTION headline_with_fragments(text, text, tsquery, text)
+RETURNS text
+AS 'MODULE_PATHNAME', 'tsa_headline_with_fragments'
+LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;
+
-- CREATE the OPERATOR class
CREATE OPERATOR CLASS gist_tsvector_ops
FOR TYPE tsvector USING gist
diff -Nrub postgresql-8.3.1-orig/src/backend/tsearch/ts_parse.c postgresql-8.3.1/src/backend/tsearch/ts_parse.c
--- postgresql-8.3.1-orig/src/backend/tsearch/ts_parse.c 2008-01-01 20:45:52.0 +0100
+++ postgresql-8.3.1/src/backend/tsearch/ts_parse.c 2008-05-22 13:02:39.0 +0200
@@ -578,6 +578,111 @@
FunctionCall1(&(prsobj->prsend), PointerGetDatum(prsdata));
}
+#define COVER_SEP "..."
+#define COVER_SEP_LEN (sizeof(COVER_SEP)-1)
+
+void
+hlparsetext_with_covers(Oid cfgId,
+HeadlineParsedText *prs,
+TSQuery query,
+text *in,
+struct coverpos*covers,
+int4numcovers)
+{
+ TSParserCacheEntry *prsobj;
+ TSConfigCacheEntry *cfg;
+void *prsdata;
+ LexizeData ldata;
+ int4 icover, startpos, endpos, currentpos = 0;
+
+ char *lemm = NULL;
+ int4lenlemm = 0;
+ ParsedLex *lexs;
+ int4type, startHL = 0;
+ TSLexeme *norms;
+ int4oldnumwords, newnumwords, i;
+
+ cfg = lookup_ts_config_cache(cfgId);
+ prsobj = lookup_ts_parser_cache(cfg->prsId);
+
+prsdata = (void*) DatumGetPointer(FunctionCall2(&(prsobj->prsstart),
+PointerGetDatum(VARDATA(in)),
+Int32GetDatum(VARSIZE(in) - VARHDRSZ)));
+
+ LexizeInit(&ldata, cfg);
+
+ for (icover = 0; icover < numcovers; icover++)
+{
+if (!covers[icover].in)
+continue;
+
+startpos = covers[icover].startpos;
+endpos = covers[icover].endpos;
+
+ if (currentpos > endpos)
+ {
+/* XXX - something wrong ... we have gone past the cover */
+continue;
+ }
+
+ /* see if we need to add a cover seperator */
+ if (currentpos < startpos && startpos > 0)
+ {
+ hladdword(prs, COVER_SEP, COVER_SEP_LEN, 3);
+prs->words[prs->curwords - 1].in = 1;
+ }
+
+ do
+ {
+ type = DatumGetInt32(FunctionCall3(&(prsobj->prstoken),
+
[GENERAL] Index cleanup
Hi, recently I went through some dev dbs just to clean up some unused tables. What I found out afterwards is that even if you run a vacuum full analyze it doesn't removed the index data. So I was left with table size of a view bytes and gigabyte large indexes. Of course a re-index cleaned that up. My question now is, is this intended? Should vacuum full not clean out the indexes if the table for this index is emptied or a lot of data is removed. Should that happen after some time? I doubt, because on one of my life servers a test db had a size of 33MB overall, but an index size of 1.5GB ... Whats the best way to handles this on a production system. I doubt its wise to recreate indexes every month or so ... -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
