Re: [GENERAL] tsearch2: more than one index per table?

On Wed, 23 Nov 2005, Teodor Sigaev wrote: ERROR: could not find tsearch config by locale UPDATE t SET idxA=to_tsvector('default', a); Is it working select to_tsvector('foo bar')? I suppose, no. In that case tsearch can't find configuration for current database locale, update pg_ts_cfg.loca

[GENERAL] Strugging with NEW and OLD records.

I am a newbie to the stored proc. game and this is eating my brain. > CREATE TABLE name(first VARCHAR(32) NULL,last VARCHAR(32) NULL, extra VARCHAR(32) NULL ); > CREATE OR REPLACE FUNCTION update_name() RETURNS opaque AS '     DECLARE     BEGIN             NEW.extra:=NEW.first;         RETURN NE

Re: [GENERAL] tsearch2: more than one index per table?

ERROR: could not find tsearch config by locale UPDATE t SET idxA=to_tsvector('default', a); Is it working select to_tsvector('foo bar')? I suppose, no. In that case tsearch can't find configuration for current database locale, update pg_ts_cfg.locale in wished row to correct value. -- Teo

Re: [GENERAL] Index Administration: pg_index vs. pg_get_indexdef()

On Nov 22, 2005, at 10:56 PM, Tom Lane wrote: "Thomas F. O'Connell" <[EMAIL PROTECTED]> writes: In an old thread , Tom Lane suggested that it would be "unreasonable" to use pg_index to reconstruct (expressional) indexes (in 7.4)

Re: [GENERAL] Index Administration: pg_index vs. pg_get_indexdef()

"Thomas F. O'Connell" <[EMAIL PROTECTED]> writes: > In an old thread msg00271.php>, Tom Lane suggested that it would be "unreasonable" to > use pg_index to reconstruct (expressional) indexes (in 7.4). The > suggested alternative was to use

[GENERAL] tsearch2: more than one index per table?

Is there something in tsearch2 that prevents more than one index per table? I would like an index on field A, and a separate index on field B. The index builds fine for A, but gives an error for B.  The error text is   ERROR:  could not find tsearch config by locale   The code below

Re: [GENERAL] Why pgAdmin III guru suggests VACUUM in 8.1

"Matthew T. O'Connor" writes: >> LOG: autovacuum: processing database "foo" > Also this creates a lot of noise in the log files. I think it would be > better to downgrade this message to a NOTICE or even a DEBUG, and > replace it with a LOG level message that states when action has taken > p

Re: [GENERAL] Set Returning Function (Pipelining)

tschak wrote: I have a question on set returning functions. In one of the TechDocs on the postgres website it says: "Currently SRF returning PL/pgSQL functions must generate the entire set before the result is returned" It also says that this might be changed in future releases (later than 7.3).

[GENERAL] Set Returning Function (Pipelining)

Hi everyone, I have a question on set returning functions. In one of the TechDocs on the postgres website it says: "Currently SRF returning PL/pgSQL functions must generate the entire set before the result is returned" It also says that this might be changed in future releases (later than 7.3). M

[GENERAL] Slow pgdump

OS - RH3 Pg - 7.4.9 Ram - 8G Disk-709G Raid 0+1 We are having a pgdump issue that we can't seem to find an answer for Background: Production server contains 11 databases of which 1 database comprises 85% of the 194G used on the drive. This one large db contains 12 schemas. Within the schemas o

[GENERAL] Index Administration: pg_index vs. pg_get_indexdef()

In an old thread , Tom Lane suggested that it would be "unreasonable" to use pg_index to reconstruct (expressional) indexes (in 7.4). The suggested alternative was to use pg_get_indexdef(). I administer a postgres 8.0.x databa

Re: [GENERAL] Why pgAdmin III guru suggests VACUUM in 8.1

Andrus wrote: Jim, Keep in mind that if analyze has never been run on a table the database will assume 1000 rows, which is definately off from 122 rows. autovacuum processes this tabele regularly. I believed that autovacuum can update the row count to be real. I think this is a poor

Re: [GENERAL] Why pgAdmin III guru suggests VACUUM in 8.1

On Tue, Nov 22, 2005 at 09:33:34PM +0200, Andrus wrote: > Jim, > > > Upsizes? Are you adding more data? If so then yes, analyze would be > > good, though autovacuum should handle it for you. > > I create new Postgres database, upsize a lot of data into it. After that FWIW, people generally refe

Re: [GENERAL] Why pgAdmin III guru suggests VACUUM in 8.1

Jim, > Upsizes? Are you adding more data? If so then yes, analyze would be > good, though autovacuum should handle it for you. I create new Postgres database, upsize a lot of data into it. After that this database goes online and will receive a lot of transactions daily. I'm using PG 8.1 default

Re: [GENERAL] Why pgAdmin III guru suggests VACUUM in 8.1

On Tue, Nov 22, 2005 at 09:01:25PM +0200, Andrus wrote: > Jim, > > > Keep in mind that if analyze has never been run on a table the database > > will assume 1000 rows, which is definately off from 122 rows. > > autovacuum processes this tabele regularly. > I believed that autovacuum can update th

Re: [GENERAL] Why pgAdmin III guru suggests VACUUM in 8.1

Jim, > Keep in mind that if analyze has never been run on a table the database > will assume 1000 rows, which is definately off from 122 rows. autovacuum processes this tabele regularly. I believed that autovacuum can update the row count to be real. > You might want to ask on the pgAdmin list.

Re: [GENERAL] Why pgAdmin III guru suggests VACUUM in 8.1

Keep in mind that if analyze has never been run on a table the database will assume 1000 rows, which is definately off from 122 rows. You might want to ask on the pgAdmin list. Though I'd recommend against calling the guru 'stupid' over there. :) On Sun, Nov 20, 2005 at 09:13:36PM +0200, Andrus M

Re: [GENERAL] Best way to represent values.

On Tuesday 22 November 2005 11:40, Lincoln Yeoh wrote: > At 01:19 PM 11/21/2005 -0500, Dennis Veatch wrote: > >I had thought just adding some fields called topsoil_start/topsoil_end, > >gravel_start/gravel_end, etc. But them I'm left with how to take those > > values and give to total depth for eac

Re: [GENERAL] Partial foreign keys, check constraints and inheritance

On Thu, Nov 17, 2005 at 02:21:33PM -0500, Eric E wrote: > >maybe you can solve it adding a new col and allow both to contain null > >values. > > > >if these are not mutually exclusive you can avoid a check if they are > >check that if one has a non-null value other has null... > > > I did think

Re: [GENERAL] Group By?

Bruno The table I previously sent came through distorted and probabley caused misunderstanding. The table control and auto_control are both permanent table. I want to reshuffle how the information is associated from one table to another with the link between table by way of the device_id.

Re: [GENERAL] How to trim Bytea fields

Joe Conway wrote trim() will remove '\000' bytes from both ends -- would that work for you? Thanks Joe, just what I was looking for. Howard ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] Createlang plpgsql

Well they are probably busy people and I have not got a reply from them as yet. The reason I post the problem here is in the hops that someone has come across this problem before and has some experience that might help. For-instance they might have found an economical shared hosting site wi

Re: [GENERAL] Best way to represent values.

At 01:19 PM 11/21/2005 -0500, Dennis Veatch wrote: I had thought just adding some fields called topsoil_start/topsoil_end, gravel_start/gravel_end, etc. But them I'm left with how to take those values and give to total depth for each layer and total depth of the well. But I'm not sure that is t

Re: [GENERAL] problem with GRANT postgres 8.0.4

Richard Huxton napisał(a): Don't forget to cc: the list when replying Jacek Balcerski wrote: Richard Huxton napisał(a): Jacek Balcerski wrote: ERROR: permission denied for relation reviewers KONTEKST: SQL statement "SELECT 1 FROM ONLY "public"."reviewers" x WHERE "person_id" = $1 FOR

Re: [GENERAL] problem with GRANT postgres 8.0.4

Don't forget to cc: the list when replying Jacek Balcerski wrote: Richard Huxton napisał(a): Jacek Balcerski wrote: ERROR: permission denied for relation reviewers KONTEKST: SQL statement "SELECT 1 FROM ONLY "public"."reviewers" x WHERE "person_id" = $1 FOR UPDATE OF x" In person table

Re: [GENERAL] How to trim Bytea fields

Howard Cole wrote: Hi, I have an bytea field that contains data with a lot of trailing blank space composed of multiple '\000' zero bytes. Does anyone know of a quick SQL fix to trim these bytes from the data? trim() will remove '\000' bytes from both ends -- would that work for you? select

Re: [GENERAL] problem with GRANT postgres 8.0.4

Jacek Balcerski wrote: ERROR: permission denied for relation reviewers KONTEKST: SQL statement "SELECT 1 FROM ONLY "public"."reviewers" x WHERE "person_id" = $1 FOR UPDATE OF x" In person table there is ofcourse person with id=569. User is super user and I did GRANT ALL on ALL TABLES : publi

Re: [GENERAL] Java, postgres and jasper help

[EMAIL PROTECTED] wrote: hi all, i am trying to fix this bug within my program. its a java, postgres and jasper based program which generates charts. now i am generating a chart which does not show any 0 data points if they exist, only non-zero ones. obviously i am trying to do everything in jus

[GENERAL] How to trim Bytea fields

Hi, I have an bytea field that contains data with a lot of trailing blank space composed of multiple '\000' zero bytes. Does anyone know of a quick SQL fix to trim these bytes from the data? Thanks Howard Cole http://www.selestial.com ---(end of broadcast)---

[GENERAL] problem with GRANT postgres 8.0.4

There are two tables article_reviewers Column| Type | Modifiers -+-+--- article_id | integer | not null reviewer_id | integer | not null Foreign-key constraints: "$1" FOREIGN KEY (reviewer_id) REFERENCES reviewers(person_id) reviewers Column | Type

Re: [GENERAL] Best way to represent values.

The problem is that each well can have a different number of and types of layers. Trying to pre-plan all the combinations could be a big headache. My first thought is the following layout- well_number layer_number bottom_depth layer_type 1 1 10

Re: [GENERAL] Rule appears not to fire on insert w/ "except"

Dear Chris, Sorry, I forgot the (): insert into test1 select id, data from test2 where (id, data) not in (select id, data from test1); With the story of Tom Lane, your solution would be a before trigger I guess: create or replace function trg_test() returns "trigger" as ' begin insert into

Re: [GENERAL] Rule appears not to fire on insert w/ "except"

On Tuesday 22 November 2005 08:34 am, A.j. Langereis wrote: > Dear Chris, > > What about this: > > insert into test1 >     select id, data from test2 >     where id, data not in (select id, data from test1); > > of which one would expect the same results... > > Yours, > > Aarjan > > Ps. notice that

Re: [GENERAL] Rule appears not to fire on insert w/ "except"

Dear Chris, What about this: insert into test1 select id, data from test2 where id, data not in (select id, data from test1); of which one would expect the same results... Yours, Aarjan Ps. notice that you are inserting data into a serial column (in your examples as well), as far as I

[GENERAL] Java, postgres and jasper help

hi all, i am trying to fix this bug within my program. its a java, postgres and jasper based program which generates charts. now i am generating a chart which does not show any 0 data points if they exist, only non-zero ones. obviously i am trying to do everything in just one sql query, but i am n

[GENERAL] settings for multi-language unicode DB

Please could somebody give me advice on settings for PGSQL with a database with UTF-8 strings in a large number of languages? I've had no problems so far in storing/retrieving UTF-8 strings but can't find clear answers to other issues. What locale would be best for sorting this dataset? Does it h

Re: [GENERAL] TSearch2 Questions

On 21.11.2005 18:24, Bruno Wolff III wrote: On Mon, Nov 21, 2005 at 16:50:00 +0300, Oleg Bartunov wrote: On Mon, 21 Nov 2005, Hannes Dorbath wrote: I'm playing a bit with it ATM. Indexing one Gigabyte of plain text worked well, with 10 GB I yet have some performance problems. I read the TSear

Re: [GENERAL] Rule appears not to fire on insert w/ "except"

On Monday 21 November 2005 08:16 pm, David Fetter wrote: > On Mon, Nov 21, 2005 at 08:05:19PM -0500, Jerry Sievers wrote: > > Chris Kratz <[EMAIL PROTECTED]> writes: > > > Hello All, > > > > > > We have finally tracked down a bug in our application to a rewrite rule > > > on a table. In essence, t

Re: [GENERAL] Rule appears not to fire on insert w/ "except"

On Monday 21 November 2005 08:05 pm, Jerry Sievers wrote: > Chris Kratz <[EMAIL PROTECTED]> writes: > > Hello All, > > > > We have finally tracked down a bug in our application to a rewrite rule > > on a table. In essence, the rewrite rule in question logs any inserts to > > another table. This w

Re: [GENERAL] Timestamp with Timezone

Matthew Terenzio wrote: reading the docs . . . let's see if I've got it. 1. Timestamp with timezone accepts a timestamp with the additional timezone, converts it and stores it as GMT 2. It returns the value as the timestamp converted to the timezone of the local machine? that's what it says

Re: [GENERAL] not null error in trigger on unrelated column

I removed the not null contraint on members.admin and "update items set active = false where member_id=38" results in the count columns in members getting updated AND members.admin getting set to NULL. Really bizarre. I dropped the trigger function and the trigger, then recreated both, and now

Re: [GENERAL] Difference in indexes

Ok, I didn't look at it from that point of view. It makes it all clear! Thanks for the explanation! Yours, Aarjan Langereis - Original Message - From: "Qingqing Zhou" <[EMAIL PROTECTED]> To: Sent: Tuesday, November 22, 2005 2:23 AM Subject: Re: [GENERAL] Difference in indexes > > ""A