[GENERAL] tsearch2 problems / limitations
Hi,
I've successfully added tsearch2 to an existing database and the speedup
of searches is brilliant. I'm now trying to extend this to other parts
of our system.
One of the tables holds reasonable amounts of text, some fields hold up
to 2Mb. When I try and run
UPDATE table SET idxfti=to_tsvector('default', field);
it runs for a while then aborts with the following message
ERROR: value is too big
Does anyone know what the problem might be here? Does tsearch2 have
configurable (or compilable) settings that could get around this?
Many Thanks,
James
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] CREATE TABLE problem in plpgsql trigger
James Croft wrote: The problems seems to be with the table_name arg being a variable and not a literal but can't see how to fix this. If anyone knows what's going on here or has any pointers it would be appreciated. Thanks, James Sorry, I'm running PgSQL 7.4.7 on RedHat Enterprise Linux 3. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] CREATE TABLE problem in plpgsql trigger
Hi all, I'm trying to create a trigger function for a few tables that will store old versions of rows prior to any update on them. Part of the function needs to creates other tables (the table to store these snapshots in). When this trigger runs I get the and error of 'syntax error at or near "$1" at character 15' which is the CREATE TABLE line. DECLARE rec RECORD; snapshottable TEXT; originaltable TEXT; BEGIN SELECT INTO rec count(*) AS num FROM pg_tables WHERE schemaname = ''table_snapshots'' AND tablename = TG_RELNAME; IF rec.num < 1 THEN snapshottable := ''table_snapshots.'' || TG_RELNAME; originaltable := TG_RELNAME; CREATE TABLE snapshottable (LIKE originaltable); ALTER TABLE snapshottable ADD COLUMN snapshottime date; ALTER TABLE snapshottable ALTER COLUMN snapshottime SET DEFAULT CURRENT_TIMESTAMP; END IF; The problems seems to be with the table_name arg being a variable and not a literal but can't see how to fix this. If anyone knows what's going on here or has any pointers it would be appreciated. Thanks, James ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] CREATE TABLE problem in plpgsql trigger
Stephan Szabo wrote: On Thu, 19 May 2005, James Croft wrote: Hi all, I'm trying to create a trigger function for a few tables that will store old versions of rows prior to any update on them. Part of the function needs to creates other tables (the table to store these snapshots in). When this trigger runs I get the and error of 'syntax error at or near "$1" at character 15' which is the CREATE TABLE line. Yes, I don't think support statements like CREATE TABLE currently work with variables directly. You probably can use EXECUTE however by generating a string containing the command you want to run first. Something like: EXECUTE ''CREATE TABLE '' || snapshottable || '' (LIKE '' || originaltable || '')''; excepting that you'd need to be more careful with quoting. Thanks Stephanm, that fixed it. -- James Croft Lumison t: 0845 1199 911 f: 0845 1199 901 d: 0131 5144 022 begin:vcard fn:James Croft n:Croft;James org:Lumision Ltd adr:;;12 Dock Place;Edinburgh;;EH6 6LU;UK email;internet:[EMAIL PROTECTED] title:Systems Developer tel;work:0131 514 4022 tel;fax:0845 1199 900 x-mozilla-html:TRUE url:http://www.lumison.net version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Temp tables as session var containers
Hi, I've seen the session variable question pop up a fair bit on this list. The temporary table solution seems good but I've got a question before using it... - My app creates a temp table for session vars - UPDATE, INSERT and DELETE triggers on tables use this data My question is: If I run a query directly through the psql command line tool (or another app that doesn't setup this temp table) that temp table wont exist. How can I write the trigger function to detect the absence of the temp table and deal with it gracefully? I think I need some SQL to determine which pg_temp_N schema belongs to my session. Is this possible? TIA, James ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] PostgreSQL release schedule
Hi, Does anyone know if there is a publicly available release schedule for upcoming versions of PostgreSQL along with proposed new functionality? In a month or so I'm going to start on updating an app from Pg7.1 and would like to know if should start writing for Pg8.0 or if 8.1 is around the corner and if it has any features that would benefit the app. Thanks, James ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL release schedule
Scott Marlowe wrote: I'd go for 8.0. It's quite stable now, and 8.1 will take as long as it needs to take to come out, which could be anything, but will likely be no earlier than the end of the year. I believe there was talk about an 8.1 coming out to replace the caching algorithm, but I believe the change was implemented in 8.0.3 or something like that, so there's no need to push out an 8.1 release. Thanks Scott. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL release schedule
Marc G. Fournier wrote: I'd almost think taht this shuld be much more prominently put in a section on the main page of the web site, actually ... make it nice and visible instead of buried on a sub page ... I agree it would be good to have a link on the main page. Possibly near "What's new in current_version" to have a link to 'coming up in our next release' or something similar. Just my $0.02, I know where the todo page is now. :) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Temporary table visibility
Hi all, I've had a look at through the list archives but haven't found an answer to this one. Any suggestions appreciated (aside from ones suggesting that I should not need to do this ;-)... - A normal table foo is created in a database. - Clients connect to the database, some create a temp table foo some don't. (only one postgresql user is being used to connect to the database if that matters) How does a client determine if table foo is temporary or not? Or put another way... How can I determine what temporary tables exist in my session, bearing in mind that other sessions contain temp tables using the same names? Many thanks, James -- This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. Any offers or quotation of service are subject to formal specification. Errors and omissions excepted. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Lumison, nplusone or lightershade ltd. Finally, the recipient should check this email and any attachments for the presence of viruses. Lumison, nplusone and lightershade ltd accepts no liability for any damage caused by any virus transmitted by this email. -- -- Virus scanned by Lumison. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Temporary table visibility
On 25 Jan 2006, at 14:17, Jaime Casanova wrote: How can I determine what temporary tables exist in my session, bearing in mind that other sessions contain temp tables using the same names? just the ones you have created in your session, temporary tables in other sessions are invisible to you... Thanks Jaime but that's not really what I meant. I know that if a session creates a temporary table it is only visible to that session. I'm not doing a good job of explaining this but basically given the following results... test=> select relname, relnamespace, reltype from pg_class where relname = 'session_data'; relname| relnamespace | reltype --+--+-- session_data | 2200 | 16114367 session_data | 16120903 | 16314010 session_data | 16120709 | 16314030 session_data | 16122659 | 16314133 session_data | 16123201 | 16314285 session_data | 16124398 | 16315049 session_data |16767 | 16315527 session_data | 16120382 | 16315818 session_data | 16125558 | 16315816 session_data | 16114413 | 16316810 session_data | 16127654 | 16317471 session_data | 16114683 | 16317551 session_data | 16118447 | 16317563 session_data | 15035529 | 16317579 (14 rows) How can I determine if one of the above relations is a temporary table in the current session (one of them, the first in ns 2200, is a normal permanent table)? Thanks, James -- This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. Any offers or quotation of service are subject to formal specification. Errors and omissions excepted. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Lumison, nplusone or lightershade ltd. Finally, the recipient should check this email and any attachments for the presence of viruses. Lumison, nplusone and lightershade ltd accepts no liability for any damage caused by any virus transmitted by this email. -- -- Virus scanned by Lumison. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
