Re: [GENERAL] Calculating a moving average (Coding style)

[EMAIL PROTECTED] wrote: CREATE OR REPLACE FUNCTION get_bar_avg() RETURNS TRIGGER AS ' DECLARE bar_record RECORD; x INTEGER; y DOUBLE PRECISION := 0; BEGIN IF TG_OP = ''INSERT'' THEN y := y + NEW.bar; ... RETURN NEW; ELSIF TG_OP = ''DELETE'' THEN x := 0; ...

Re: [GENERAL] Calculating a moving average (Coding style)

On Mon, 24 Jan 2005 08:32 pm, Alban Hertroys wrote: > [EMAIL PROTECTED] wrote: > > CREATE OR REPLACE FUNCTION get_bar_avg() RETURNS TRIGGER AS ' > > DECLARE > > bar_record RECORD; > > x INTEGER; > > y DOUBLE PRECISION := 0; > > BEGIN > > IF TG_OP = ''INSERT'' THEN > > y := y + NEW.ba

[GENERAL] Savepoint Problem

Hello, I have a function, which stores some Data. Sometimes this function is called within a transaction an sometimes not. But I need to set a savepoint in this function. But this is my problem, when I'm not in a transaction I get this error: ERROR: SAVEPOINT may only be used in transaction bl

Re: [GENERAL] serialization errors when inserting new records

Ralph van Etten wrote: Ok, but then I have the same problem. The "display" field has to be unique and it needs to use a SELECT MAX in an INSERT query and this gives the same problem. Only not with the primary key but with possible duplicate values in the "display" field. If you absolutely need the

Re: [GENERAL] serialization errors when inserting new records

On Mon, 24 Jan 2005, William Yu wrote: > If you absolutely need the display field to be unique and it has to be a > sequence starting from 1 -- it's simplicity itself. Use the > serialization errors to your advantage. If you try to insert a record > and you get a dupe key violation, just increment

[GENERAL] disable trigger from transaction

hello, I am interested in disabling a trigger from a transaction. I am not want to disable the trigger globally but only for the current transaction. Can I do it somehow ? thanks, Razvan Radu ---(end of broadcast)--- TIP 5: Have you checked our exte

Re: [GENERAL] disable trigger from transaction

Razvan, I don't believe there is a way of doing this from by way of some postgreSQL command. We accomplish this by creating a table called "override". It is defined as: recid | integer| not null default nextval('public.override_recid_seq'::text) trig_name | chara

[GENERAL] on update / on delete performance of foreign keys

Hi I ran into some performance problems regarding foreign keys lately. My schema has about 20 tables, which each contain from 10 to 100.000 records. They have quite complicated interdependencies, modeled using foregin keys set to "on update cascade, on delete cascade". The schema stores data for m

Re: [GENERAL] on update / on delete performance of foreign keys

Florian G. Pflug wrote: Hi I ran into some performance problems regarding foreign keys lately. My schema has about 20 tables, which each contain from 10 to 100.000 records. They have quite complicated interdependencies, modeled using foregin keys set to "on update cascade, on delete cascade". The

Re: [GENERAL] How are foreign key constraints built?

"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Well, every other database I've used can do index covering, which means > index scans *are* faster. Still not necessarily true. In a case like this it would still be random access which would be slower than sequential access. Though Oracle is capable

Re: [GENERAL] on update / on delete performance of foreign keys

"Florian G. Pflug" <[EMAIL PROTECTED]> writes: > when deleting a lot of rows from a large table - and each time it has to find > referencing tuples by doing an index scan Are you sure it was even an index scan? And not doing a sequential table scan for every deletion? In order to do an index sca

Re: [GENERAL] on update / on delete performance of foreign keys

Richard Huxton wrote: Florian G. Pflug wrote: I ran into some performance problems regarding foreign keys lately. My schema has about 20 tables, which each contain from 10 to 100.000 records. They have quite complicated interdependencies, modeled using foregin keys set to "on update cascade, on de

Re: [GENERAL] on update / on delete performance of foreign keys

On Mon, 24 Jan 2005, Florian G. Pflug wrote: > Since postgres already incoporates code to check foreign keys more > efficiently (when doing alter table ... add constraint .. foreign key, > postgres seems to use a merge or a hash join, instead of a nested loop), > I wondered how hard it would be t

[GENERAL] querying two connections in one query

Hi, Is it possible, perhaps through some extension, to query two database through one SQL select statement where the two databases have different connections? Something similar to how you can link databases in Oracle. Regards, Abdul-Wahid ---(end of broadcast)---

Re: [GENERAL] Data entry - forms design or other APIs etc. - what is there?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm using Runtime Revolution (www.runrev.com; note this is a commercial product, but there is a free demo) -- not database specific, but it does have built-in support for PostgreSQL. Note that while it *should* be able to build apps for Linux, acces

Re: [GENERAL] Weird problem in 8.0.0

Devrim GUNDUZ <[EMAIL PROTECTED]> writes: > I've changed statement_timeout value from 5 to 0; restarted PostgreSQL and > all commands are running now... So, what could be the problem with > statement_timeout? Apparently you need a timeout longer than 5 milliseconds. Note that the first few quer

Re: [GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird

If you explicitly name your schemas (including public) in all of your SQL it will port easily to Oracle. Chris

[GENERAL] Postgres 8.0 on RHEL

I hope this is a valid question for this list.   I want to install PG 8.0 on a RHEL system. I have been told our security policy says that only packages provided by red hat can be installed.   Does anyone know how long until PG8.0 would be available as a RHEL rpm?

Re: [GENERAL] querying two connections in one query

On Mon, Jan 24, 2005 at 02:48:21PM +, Abdul-Wahid Paterson wrote: > Is it possible, perhaps through some extension, to query two database > through one SQL select statement where the two databases have > different connections? contrib/dblink might provide what you're looking for. -- Michael

Re: [GENERAL] querying two connections in one query

On Mon, 2005-01-24 at 14:48 +, Abdul-Wahid Paterson wrote: > Hi, > > Is it possible, perhaps through some extension, to query two database > through one SQL select statement where the two databases have > different connections? Something similar to how you can link databases > in Oracle. > I

[GENERAL] Ident authentication

Hi. I'm trying to connect to my test DB in localhost with pgAdmin3 but I got an error message Error connecting to the server: FATAL: Ident authentication failed for user "jarkko" What should I do? I'm reading documents about this and there was something about pg_hba.conf and pg_ident.conf. I d

Re: [GENERAL] Postgres 8.0 on RHEL

On Mon, 24 Jan 2005 10:25:09 -0500, Rick Schumeyer <[EMAIL PROTECTED]> wrote: > > > > I hope this is a valid question for this list. > > > > I want to install PG 8.0 on a RHEL system. > > I have been told our security policy says > > that only packages provided by red hat can be inst

Re: [GENERAL] on update / on delete performance of foreign keys

Stephan Szabo wrote: On Mon, 24 Jan 2005, Florian G. Pflug wrote: Since postgres already incoporates code to check foreign keys more efficiently (when doing alter table ... add constraint .. foreign key, postgres seems to use a merge or a hash join, instead of a nested loop), I wondered how hard it

Re: [GENERAL] Postgres 8.0 on RHEL

"Rick Schumeyer" <[EMAIL PROTECTED]> writes: > Does anyone know how long until PG8.0 would > be available as a RHEL rpm? Whenever RHEL5 is released. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postm

Re: [GENERAL] Ident authentication

On Mon, Jan 24, 2005 at 05:55:29PM +0200, Jarkko Elfving wrote: > I'm trying to connect to my test DB in localhost with pgAdmin3 but I got > an error message > > Error connecting to the server: FATAL: Ident authentication failed for > user "jarkko" Are you using a TCP connection or a Unix-domai

Re: [GENERAL] Ident authentication

Jarkko Elfving <[EMAIL PROTECTED]> writes: > I'm trying to connect to my test DB in localhost with pgAdmin3 but I got > an error message > Error connecting to the server: FATAL: Ident authentication failed for > user "jarkko" > What should I do? Either run an identd daemon, or use a different aut

Re: [GENERAL] Ident authentication

On Mon, 2005-01-24 at 09:21 -0700, Michael Fuhr wrote: > Are you using a TCP connection or a Unix-domain socket connection? > If the former, do you have an IDENT server running? If the latter, > what platform are you using? Not all operating systems can pass > credentials over Unix-domain sockets

Re: [GENERAL] Ident authentication SOLVED

Hi... I did get it work, thanks for help. -- Jarkko Elfving <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [GENERAL] Ident authentication SOLVED

On Mon, Jan 24, 2005 at 06:35:36PM +0200, Jarkko Elfving wrote: > I did get it work, thanks for help. What was the solution? It might be informative to people searching the list archives because they're having the same problem. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ -

Re: [GENERAL] Postgres 8.0 on RHEL

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Mon, 24 Jan 2005, Lonni J Friedman wrote: If you want PG8 then your best hope is FC4 this May. I believe some guys must have built PostgreSQL 8.0 RPMS for RHEL 3.0 ;) Here it is: http://www.postgresql.org/ftp/binary/v8.0/linux/rpms/redhat/rhel-e

Re: [GENERAL] Weird problem in 8.0.0

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Mon, 24 Jan 2005, Tom Lane wrote: I've changed statement_timeout value from 5 to 0; restarted PostgreSQL and all commands are running now... So, what could be the problem with statement_timeout? Apparently you need a timeout longer than 5 millise

Re: [GENERAL] Ident authentication SOLVED

On Mon, 2005-01-24 at 09:39 -0700, Michael Fuhr wrote: > On Mon, Jan 24, 2005 at 06:35:36PM +0200, Jarkko Elfving wrote: > > > I did get it work, thanks for help. > > What was the solution? It might be informative to people searching > the list archives because they're having the same problem. >

Re: [GENERAL] on update / on delete performance of foreign keys

On Mon, 24 Jan 2005, Florian G. Pflug wrote: > Stephan Szabo wrote: > > On Mon, 24 Jan 2005, Florian G. Pflug wrote: > > > >>Since postgres already incoporates code to check foreign keys more > >>efficiently (when doing alter table ... add constraint .. foreign key, > >>postgres seems to use a me

Re: [GENERAL] number of rown in a cursor.

> Not without actually scanning the result, if that's what you meant. so basically i have to do a move to the end ? ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [GENERAL] number of rown in a cursor.

On Mon, 2005-01-24 at 10:58, Christoffer Gurell wrote: > > Not without actually scanning the result, if that's what you meant. > > so basically i have to do a move to the end ? yep. This is because one of the advantages of a cursor is that it only runs partially and returns the first X rows for

Re: [GENERAL] Problem getting sql statement logging to work

Michael Many thanks. Configuring the syslog.conf and using logger to check it was working did the trick. Previously I had no local0 entries in the syslog.conf which was my problem. David From: Michael Fuhr <[EMAIL PROTECTED]> To: David Klugmann <[EMAIL PROTECTED]> CC: pgsql-general@postgresql.or

Re: [GENERAL] Ident authentication SOLVED

On Mon, Jan 24, 2005 at 06:46:25PM +0200, Jarkko Elfving wrote: > On Mon, 2005-01-24 at 09:39 -0700, Michael Fuhr wrote: > > > > What was the solution? It might be informative to people searching > > the list archives because they're having the same problem. > > I just modify the pg_hba.conf fil

Re: [GENERAL] on update / on delete performance of foreign keys

In article <[EMAIL PROTECTED]>, "Florian G. Pflug" <[EMAIL PROTECTED]> writes: >> PostgreSQL doesn't automatically add indexes to foreign-key >> columns. That sounds like the issue to me. > Oh... *feeling a bit stupid*... Seems that I got confused, because it > requires an index to exist on the r

[GENERAL] [7.4.1] signal 11 while accesing DB

Hi, Yesterday morning one of our PostgreSQL engines stopped unexpectedly, with the following log: postgres[4192]: [5105-1] LOG: server process (PID 26678) was terminated by signal 11 postgres[4192]: [5106-1] LOG: terminating any other active server processes postgres[4192]: [5107-1] LOG: all s

Re: [GENERAL] [7.4.1] signal 11 while accesing DB

On Mon, 2005-01-24 at 11:45, Marcin wrote: > Hi, > Yesterday morning one of our PostgreSQL engines stopped unexpectedly, > with the following log: > > postgres[4192]: [5105-1] LOG: server process (PID 26678) was terminated by > signal 11 Have you tried backing up your data directory (as a preca

Re: [GENERAL] Ident authentication

On 24/01/2005 16:28 Jarkko Elfving wrote: On Mon, 2005-01-24 at 09:21 -0700, Michael Fuhr wrote: > Are you using a TCP connection or a Unix-domain socket connection? > If the former, do you have an IDENT server running? If the latter, > what platform are you using? Not all operating systems can p

Re: [GENERAL] [7.4.1] signal 11 while accesing DB

Marcin <[EMAIL PROTECTED]> writes: > Yesterday morning one of our PostgreSQL engines stopped unexpectedly, > [ with SIGSEGV at ] > (gdb) bt > #0 0x081b4e79 in GetRawDatabaseInfo () > #1 0x081b4865 in InitPostgres () > #2 0x08154741 in PostgresMain () This suggests corrupted data in pg_database,

Re: [GENERAL] disable trigger from transaction

Would it work to just do a DROP TRIGGER at the begining of the transaction and a CREATE TRIGGER at the end? Regards, Jeff Davis On Mon, 2005-01-24 at 06:50 -0500, Terry Lee Tucker wrote: > Razvan, > > I don't believe there is a way of doing this from by way of some postgreSQL > command.

Re: [GENERAL] number of rown in a cursor.

> yep. This is because one of the advantages of a cursor is that it only > runs partially and returns the first X rows for the fetch. This keeps > load down so that many cursors hitting the machine at once don't all > materialize all their rows and chew up all that I/O, cpu, and memory. > Unfort

Re: [GENERAL] on update / on delete performance of foreign keys

Harald Fuchs <[EMAIL PROTECTED]> writes: > "Florian G. Pflug" <[EMAIL PROTECTED]> writes: >>> PostgreSQL doesn't automatically add indexes to foreign-key >>> columns. That sounds like the issue to me. >> Oh... *feeling a bit stupid*... Seems that I got confused, because it >> requires an index to

Re: [GENERAL] disable trigger from transaction

I don't know if droping a trigger inside a transaction will work. Besides that, we want the trigger to do its work in all other circumstances. With a hundred connections on the database, I don't know what kind of issues that would cause if the trigger were there, and suddenly, not there. We figu

[GENERAL] Bug report in 7.4

Is this known? If you create a table with a SERIAL and then rename the table you can't dump/restore the database. The create table creates a sequence based on the new table name but the setval() call still refers to the old sequence name. I'm not really sure which sequence name ought to be used.

[GENERAL] Validating user-input to be inserted in regular expressions

Hi all, I would like to take user input, which should be interpreted literally, and put it inside a regular expression, something like select * from files where path ~ (USER_INPUT || '.*') How should I escape the user input? I mean: I know that I must insert double backslashes before special cha

Re: [GENERAL] number of rown in a cursor.

On Jan 24, 2005, at 1:38 PM, Christoffer Gurell wrote: the thing is that i want to create a gui-widget that has the possibility to show a large amount of data over a slow connection. My idea was that i create a cursor and create a srollbar with the number of rows in the cursor so the user can scro

Re: [GENERAL] [7.4.1] signal 11 while accesing DB

Marcin <[EMAIL PROTECTED]> writes: > Okay, then pg_filedump 1262: > and suddenly: > Block4 > - > Block Offset: 0x8000 Offsets: Lower 0 (0x) > Block: Size0 Version0Upper 0 (0x) >

Re: [GENERAL] Bug report in 7.4

Gregory Stark <[EMAIL PROTECTED]> writes: > Is this known? Yes, it's what we invented pg_get_serial_sequence() for. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http:/

[GENERAL] What is the format of 'binary' data in the postgresql client/server protocol version 3

Guys, I am in the middle of implementing postgresql's client server protocol and I have run into a little issue. The spec says that in general data can be of two types, 'text' and 'binary'. The text one is generally pretty simple to figure out. However, there doesn't seem to be any area of the do

[GENERAL] PGresult pointer and memory usage

When you do a PQexec call in libpq, does PostgreSQL produce the entire result in memory on the server side, and then does libpq hold the entire result in memory after it retrieves it? The only time that the server sends parts of the result at a time is with a cursor, right? And libpq always holds

Re: [GENERAL] PGresult pointer and memory usage

From what I understand: On Mon, Jan 24, 2005 at 12:14:09PM -0800, Jeff Davis wrote: > When you do a PQexec call in libpq, does PostgreSQL produce the entire > result in memory on the server side, and then does libpq hold the entire > result in memory after it retrieves it? The server sends the da

Re: [GENERAL] Recursive queries

Quoth tmp <[EMAIL PROTECTED]>: > Are there any plans on implementing support for recursive queries in > postgresql in the near future? If so: When? > > I can see there has been some discussion on the subject in the > developer-group for quite some time ago, but aparently all thoughts > of recursive

Re: [GENERAL] What is the format of 'binary' data in the postgresql client/server protocol version 3

Eric Merritt <[EMAIL PROTECTED]> writes: > I am in the middle of implementing postgresql's client server > protocol and I have run into a little issue. The spec says that in > general data can be of two types, 'text' and 'binary'. The text one is > generally pretty simple to figure out. However, t

Re: [GENERAL] PGresult pointer and memory usage

Jeff Davis <[EMAIL PROTECTED]> writes: > When you do a PQexec call in libpq, does PostgreSQL produce the entire > result in memory on the server side, and then does libpq hold the entire > result in memory after it retrieves it? No, and yes. regards, tom lane

Re: [GENERAL] Calculating a moving average (Coding style)

I personally use 3 seperate triggers on most occasions, depending on how different the action for each seperate action is, it's just easier for me and my people to logically distinguish the functions that way, but the example in the 7.4 documentation for triggers is given using the form that i wrot

[GENERAL] Object Relational, Foreign Keys and Triggers

I am facing the classic pgsql ORDBMS problem: create sequence entity_id_seq; create table entity ( entity_id int not null default nextval('entity_id_seq'), primary key (entity_id) ); create table person ( first_name varchar(32) not null, last_name varchar(32) not null, primary key (enti

Re: [GENERAL] What is the format of 'binary' data in the postgresql client/server protocol version 3

On Mon, 24 Jan 2005 18:54:01 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Eric Merritt <[EMAIL PROTECTED]> writes: > > I am in the middle of implementing postgresql's client server > > protocol and I have run into a little issue. The spec says that in > > general data can be of two types, 'text' a

[GENERAL] xpath_list() question for contrib/xml2

Short summary: I want something like xpath_list() that returns an array (instead of a delimited, concatenated string) when multiple nodes exist in an XML file. It feels to me like that'd be a better (cleaner) API. Before I write one, does anyone already have such a patch? If not, woul

[GENERAL] PostgreSQL General Bits Issue # 94

General Bits has returned from its sabbatical. The newest issue is now available! Issue #94 of PostgreSQL General Bits, the weekly column of bits, tidbits and factoids for PostgreSQL is now available at: http://www.varlena.com/GeneralBits/ Cheers, elein

Re: [GENERAL] How are foreign key constraints built?

On Mon, Jan 24, 2005 at 09:24:49AM -0500, Greg Stark wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > Well, every other database I've used can do index covering, which means > > index scans *are* faster. > > Still not necessarily true. In a case like this it would still be random > acces

Re: [GENERAL] Recursive queries

On Mon, Jan 24, 2005 at 06:30:52PM -0500, Christopher Browne wrote: > Quoth tmp <[EMAIL PROTECTED]>: > > Are there any plans on implementing support for recursive queries in > > postgresql in the near future? If so: When? > > > > I can see there has been some discussion on the subject in the > > de

Re: [GENERAL] on update / on delete performance of foreign keys

Stephan Szabo wrote: The second is that these triggers will want to know which rows are deleted, but AFAIK statement-level triggers don't currently give you that information and deleting/changing any rows that aren't satisfied does not give the correct behavior. This I do not understand. Isn't it s

Re: [GENERAL] What is the format of 'binary' data in the postgresql client/server protocol version 3

Eric Merritt <[EMAIL PROTECTED]> writes: > On Mon, 24 Jan 2005 18:54:01 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: >> Generally you have to look at the send/recv routines for the specific >> data type that you are wondering about. > Ik, ok I can do that. Any pointers to the particular sourcefil

[GENERAL] Tablespaces and primary keys

Implicit indexes created by a constraint do not appear to honor the default tablespace. The index gets created in the "null" tablespace. I took pg_dumpall output and modified the schema to place everything in specific table spaces. When the statement: ALTER TABLE ONLY addresses ADD CONST

[GENERAL] Search for restricting foreign keys

Is there a way in PG 7.3, given a field, to find out what other tables & records are linked to it via a foreign key? EG: create table cities (id serial primary key, title varchar not null); insert into cities(title) values ('San Fransisco'); insert into cities(title) values ('Los Angeles');

Re: [GENERAL] Tablespaces and primary keys

On Mon, 2005-01-24 at 21:03 -0600, Wes wrote: > Implicit indexes created by a constraint do not appear to honor the default > tablespace. The index gets created in the "null" tablespace. > > I took pg_dumpall output and modified the schema to place everything in > specific table spaces. When th

Re: [GENERAL] disable trigger from transaction

It got me curious enough that I tested it, and apparently droping a trigger locks the table. Any actions on that table must wait until the transaction that drops the trigger finishes. So, technically my system works, but requires a rather nasty lock while the transaction (the one that doesn't want

Re: [GENERAL] Search for restricting foreign keys

On Mon, Jan 24, 2005 at 08:35:45PM -0800, Benjamin Smith wrote: > Is there a way in PG 7.3, given a field, to find out what other tables & > records are linked to it via a foreign key? See the "System Catalogs" chapter in the documentation: http://www.postgresql.org/docs/7.3/static/catalogs.htm

[GENERAL] How to lock or disable a trigger

Hi I am new to SQL. Is there a way to lock or disable temporarily a trigger? Since under load conditions while updating the database if the triggers are down then it might help updating the db faster. I am using postgre 7.2 and have to update only with INSERT commands. since with copy comman

[GENERAL] How do I connect to Postgresql on Windows 98?

Hello all, I just want to know how I should connect to Postgresql while running Windows 98 (no networking)? Dan _ Don’t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01

Re: [GENERAL] How to lock or disable a trigger

There was just that question posed earlier today. I'll refer you to the archives, where Terry Lee Tucker weighed in with a good response: I hope that helps, Jeff Davis On Mon, 2005-01-24 at 22:48 -0800, sid tow wrote: >

Re: [GENERAL] on update / on delete performance of foreign keys

On Tue, 25 Jan 2005, Florian G. Pflug wrote: > Stephan Szabo wrote: > > > It's not sufficient to do the delete for non existant pk rows in the > > deferred case. I also think we'd need to decide on the behavior for the > > PostgreSQL case where a user trigger runs in between the delete and the >