Re: [GENERAL] sort array optimisation in pl/perl

Title: RE: [GENERAL] sort array optimisation in pl/perl Hi, thanks for this help. The method functions great but the profit of time is good just if I have a lot of elements to deplace.     COFRAMI     Nicolas Giroire     on behalf of AIRBUS France     for In Flight & G

[GENERAL] pl/perl problem of memory

Title: pl/perl problem of memory Hi, I load data in a varible of type text, and i arrive to out of memory message. I want to know if it exists a function which allows to empty a variable and the place uses in memory without destroy it (because I use the variable again). In fact, now i put

[GENERAL] Stuck with references

Hello all, this is my first post to the list, so please be gentle :-) I created the following tables (full file can be found here: http://www.larik.nl/sql/payway.sql ): CREATE TABLE location ( location_id integer DEFAULT nextval('"location_id_seq"'::text) NOT NULL, name text NOT NULL, CONST

Re: [GENERAL] pl/perl problem of memory

"GIROIRE Nicolas (COFRAMI)" <[EMAIL PROTECTED]> writes: > In fact, now i put the variable to '' ( $myvar = '' ) but it seems that Perl > don't empty totally memory. See the nearby thread "plperl doesn't release memory". There's some evidence that this may be due to a Perl configuration issue.

Re: [GENERAL] Stuck with references

try the following (untested) query: SELECT la.name,lb.name pp.distance FROM payway_profile AS pp JOIN location AS la ON ( pp.location_a = l.location_id ) join location AS lb ON ( pp.location_b = l.location_id ); -- Original Message --- From: Frodo Larik <[EMAIL PROTECTED]> To: pg

Re: [GENERAL] Stuck with references

On Fri, Mar 25, 2005 at 10:37:31AM -0500, Jim Buttafuoco wrote: > > try the following (untested) query: > > SELECT la.name,lb.name pp.distance FROM payway_profile AS pp > JOIN location AS la ON ( pp.location_a = l.location_id ) > join location AS lb ON ( pp.location_b = l.location_id ); This que

[GENERAL] Persistent data per connection

We've been struggling for several days now to come up with a mechanism that allows us to establish a mechanism to store data that remains persistent for the life of the connection. Essentially we have a web based application that utilizes a connection pool (using one single 'super' postgresql d

[GENERAL] Referential integrity using constant in foreign key

I need to create referential integrity constraints: CREATE TABLE classifier ( category CHAR(1), code CHAR(10), PRIMARY KEY (category,code) ); -- code1 references to category 1, -- code2 references to category 2 from classifier table. CREATE TABLE info ( code1 CHAR(10), code2 CHAR(10), FOREIGN KE

Re: [GENERAL] Delay INSERT

On Wed, 23 Mar 2005 12:33:00 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Dawid Kuroczko <[EMAIL PROTECTED]> writes: > > PostgreSQL doesn't have such issues with blocking, so only difference > > between INSERT and INSERT DELAYED from PostgreSQL's standpoint > > would be waiting and not for the resu

[GENERAL] [ANNOUNCE] Manitou-Mail

Hello, I'd like to announce a PostgreSQL-powered mail app I've been working on for some time and that I think is now candidate for public use. It comes up as three parts: 1) a PostgreSQL database for the storage of decoded mail contents. 2) a pure-SQL desktop app for the mail user interface.

Re: [GENERAL] Referential integrity using constant in foreign key

It's somewhat unclear what you're attempting to do, here, but I'll give a shot at interpreting. Referential integrity lets you guarantee that values in a column or columns exist in a column or columns in another table. With classifier as you've defined it, if you want referential integrity in

Re: [GENERAL] Persistent data per connection

Jeff Amiel <[EMAIL PROTECTED]> writes: > We've been struggling for several days now to come up with a mechanism > that allows us to establish a mechanism to store data that remains > persistent for the life of the connection. Why don't you just store it in a temporary table? Goes away at connec

Re: [GENERAL] Persistent data per connection

because the connection is never really dropped... using a connection poolso it's just reclaimed by the pool on a connection.close() or after a timeout period Tom Lane wrote: Jeff Amiel <[EMAIL PROTECTED]> writes: We've been struggling for several days now to come up with a mechanism tha

Re: [GENERAL] Persistent data per connection

> Our latest scheme involves giving each web system user a postgresql user > account and when we grab a connection from the connection pool, we SET > SESSION AUTHORIZATION for that user. We can then access the user info > from the trigger. > > But is there a better/different way to persist da

Re: [GENERAL] Persistent data per connection

On Fri, Mar 25, 2005 at 10:56:50AM -0600, Jeff Amiel wrote: > because the connection is never really dropped... > using a connection poolso it's just reclaimed by the pool on a > connection.close() or after a timeout period Then you don't really want per-connection state, you want per-clien

Re: [GENERAL] Persistent data per connection

Steve Atkins wrote: On Fri, Mar 25, 2005 at 10:56:50AM -0600, Jeff Amiel wrote: because the connection is never really dropped... using a connection poolso it's just reclaimed by the pool on a connection.close() or after a timeout period Then you don't really want per-connection stat

Re: [GENERAL] Persistent data per connection

On Fri, Mar 25, 2005 at 11:46:03AM -0600, Jeff Amiel wrote: > Steve Atkins wrote: > > >On Fri, Mar 25, 2005 at 10:56:50AM -0600, Jeff Amiel wrote: > > Yesbut inside a trigger function, how do I know which 'row' to look > at in the table that matches up with the session/connection I am > curr

Re: [GENERAL] Persistent data per connection

Steve Atkins wrote: Using a temporary table to store that unique token is how I'd do it. Rather than grovel through the system tables during the trigger I'd do all the work at the client session setup. As the first thing, write the token into the temporary table. If that fails, create the temporary

Re: [GENERAL] Persistent data per connection

On Fri, Mar 25, 2005 at 12:21:28PM -0600, Jeff Amiel wrote: > Steve Atkins wrote: > > >Rather than grovel through the system tables during the trigger I'd do > >all the work at the client session setup. As the first thing, write > >the token into the temporary table. If that fails, create the > >t

Re: [GENERAL] plperl doesn't release memory

At 6:58 PM -0500 3/24/05, Greg Stark wrote: Dan Sugalski <[EMAIL PROTECTED]> writes: Anyway, if perl's using its own memory allocator you'll want to rebuild it to not do that. You would need to do that if you wanted to use a debugging malloc. But there's no particular reason to think that you sh

Re: [GENERAL] Stuck with references

I did say untested :) -- Original Message --- From: Michael Fuhr <[EMAIL PROTECTED]> To: Jim Buttafuoco <[EMAIL PROTECTED]> Cc: Frodo Larik <[EMAIL PROTECTED]>, pgsql-general@postgresql.org Sent: Fri, 25 Mar 2005 09:05:50 -0700 Subject: Re: [GENERAL] Stuck with references > On Fr

[GENERAL] syntax issue with custom aggregator

When I run a query using a custom aggregator I wrote to find the average of only non-negative values: CREATE AGGREGATE "property"."pos_avg" ( BASETYPE = "int2", SFUNC = "property"."ag_pos_avg_accum", STYPE = "_int4", FINALFUNC = "property"."ag_pos_avg_final", INITCOND = "'{0,0}'"); CREATE OR REPL

Re: [GENERAL] syntax issue with custom aggregator

"Lucas F." <[EMAIL PROTECTED]> writes: > CREATE AGGREGATE "property"."pos_avg" ( BASETYPE = "int2", > SFUNC = "property"."ag_pos_avg_accum", STYPE = "_int4", > FINALFUNC = "property"."ag_pos_avg_final", INITCOND = "'{0,0}'"); Too many quotes ... try INITCOND = '{0,0}' rega

Re: [GENERAL] syntax issue with custom aggregator

On Fri, Mar 25, 2005 at 02:39:11PM -0500, Tom Lane wrote: > "Lucas F." <[EMAIL PROTECTED]> writes: > > CREATE AGGREGATE "property"."pos_avg" ( BASETYPE = "int2", > > SFUNC = "property"."ag_pos_avg_accum", STYPE = "_int4", > > FINALFUNC = "property"."ag_pos_avg_final", INITCOND = "'{0,0}'"); > > To

Re: [GENERAL] sort array optimisation in pl/perl

On Fri, 2005-03-25 at 15:29 +0100, GIROIRE Nicolas (COFRAMI) wrote: [re-arranged] > [mailto:[EMAIL PROTECTED] la part de Ragnar Hafstað > > On Thu, 2005-03-24 at 15:49 +0100, GIROIRE Nicolas (COFRAMI) wrote: > > > > I create an array which is result of query on postgresql database > and > > >

[GENERAL] pg_index question

Hi, If I were to set the value of pg_class.indisunique on a unique index to False inside a transaction so I could juggle sequence numbers around on a table with a unique two element index, and then set it back again to its proper value, all in the same transaction, would that allow me to temora

Re: [GENERAL] pg_index question

To answer my own question, "No it won't work." I still get a unique constraint error. On Friday 25 March 2005 04:10 pm, Terry Lee Tucker saith: > Hi, > > If I were to set the value of pg_class.indisunique on a unique index to > False inside a transaction so I could juggle sequence numbers around

Re: [GENERAL] pg_index question

On Fri, 2005-03-25 at 15:10, Terry Lee Tucker wrote: > Hi, > > If I were to set the value of pg_class.indisunique on a unique index to False > inside a transaction so I could juggle sequence numbers around on a table > with a unique two element index, and then set it back again to its proper >

Re: [GENERAL] pg_index question

Umm, I tried it and it worked. Sure you got the right relid? On Fri, 2005-03-25 at 15:30, Terry Lee Tucker wrote: > To answer my own question, "No it won't work." I still get a unique > constraint > error. > > On Friday 25 March 2005 04:10 pm, Terry Lee Tucker saith: > > Hi, > > > > If I were

Re: [GENERAL] pg_index question

Well, I think I had the right oid, but I agree with you. I think I have a better solution than this. I think I real try it once more though. I must have done something wrong. Thanks for the reply :o) On Friday 25 March 2005 04:53 pm, Scott Marlowe saith: > Umm, I tried it and it worked. Sure y

[GENERAL] help w/ a little naive brainstorming ...

hi all, i've two random, not-completely-disjointed questions that have arisen in a recent, off-the-cuff discussion. any/all initial pointers/comments would be much appreciated ... while i slog through Google, etc. trying to learn more ... (1) does pgsql have/plan an equivalent of Oracle's Exter

Re: [GENERAL] help w/ a little naive brainstorming ...

On Fri, Mar 25, 2005 at 03:16:12PM -0800, OpenMacNews wrote: > (1) does pgsql have/plan an equivalent of Oracle's External Tables? > "The External Table feature allows for flat files, which reside outside the database, to b

Re: [GENERAL] pg_index question

Scott Marlowe <[EMAIL PROTECTED]> writes: > Umm, I tried it and it worked. Sure you got the right relid? I think the relation cache will probably not notice a manual update on pg_index. So whether it "works" or not would depend on a lot of extraneous factors like whether the relation cache entry

Re: [GENERAL] help w/ a little naive brainstorming ...

hi michael, (1) does pgsql have/plan an equivalent of Oracle's External Tables? "The External Table feature allows for flat files, which reside outside the database, to be accessed just like relational tables within the datab

Re: [GENERAL] pg_index question

Thanks Tom. One never knows until he asks... On Friday 25 March 2005 06:36 pm, Tom Lane saith: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > Umm, I tried it and it worked. Sure you got the right relid? > > I think the relation cache will probably not notice a manual update on > pg_index. So w

[GENERAL] TSearch2 FreeBSD

Anyone with any TSearch2 experience on FreeBSD? Any known Gotchas? Postgres 7.3 Postgres is working fine and the Port for contrib seemed to install okay. tsearch2.so is indeed in the below directory and permissions look right. $ psql database < tsearch2.sql SET BEGIN NOTICE: CREATE TABLE / PRIM

Re: [GENERAL] TSearch2 FreeBSD

Matthew Terenzio <[EMAIL PROTECTED]> writes: > Anyone with any TSearch2 experience on FreeBSD? Any known Gotchas? > Postgres 7.3 > ERROR: Load of file /usr/local/lib/postgresql/tsearch2.so failed: > dlopen '/usr/local/lib/postgresql/tsearch2.so' failed. > (/usr/local/lib/postgresql/tsearch2.so:

Re: [GENERAL] TSearch2 FreeBSD

On Mar 25, 2005, at 9:28 PM, Tom Lane wrote: Matthew Terenzio <[EMAIL PROTECTED]> writes: Anyone with any TSearch2 experience on FreeBSD? Any known Gotchas? Postgres 7.3 ERROR: Load of file /usr/local/lib/postgresql/tsearch2.so failed: dlopen '/usr/local/lib/postgresql/tsearch2.so' failed. (/usr/

Re: [GENERAL] TSearch2 FreeBSD

On Fri, Mar 25, 2005 at 09:18:27PM -0500, Matthew Terenzio wrote: > > Anyone with any TSearch2 experience on FreeBSD? Any known Gotchas? > Postgres 7.3 I use tsearch2 with PostgreSQL 8.0.1 (built from CVS source) on FreeBSD 4.11-STABLE. No problems. > ERROR: Load of file /usr/local/lib/postgre

Re: [GENERAL] Persistent data per connection

Jeff Amiel wrote: > (heck, all cases) , the audit triggers themselves (when an > update/insert/delete takes place) have to look to see if a temp table > exists to pull the user data from. It wont exist in tese cases, but > the triggers dont know this. Can't you catch the undefined_table exce