Re: [GENERAL] int8 primary keys still not using index without manual

I'm using EJB CMP (Enterprise Java Beans, Container Managed Persistence), so the SQL is generated. I would think this is a common usage of PostgreSQL, as a database for a modern EJB container. There are options for fixing this (not including fixing postgres itself), IMHO the best is patching

[GENERAL]

Hello, Can you please help me in understanding how constants are stored in Postgresql. How is the Datum data structure used for the same. Thanks Shalu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Optimiser desicion bringing system to its knees?

Ahh, this makes sense. I've recently changed this into a partial index because I was getting awful performance on it. Thanks Tom, this gives me a stopgap fix until I can get 7.4 into production. Craig On Thu, 6 Nov 2003, Tom Lane wrote: > "Craig O'Shannessy" <[EMAIL PROTECTED]> writes: > > T

Re: [GENERAL] create function for trigger question

Barbera What you have written is a stored procedure not a trigger function. Trigger functions until 7.2 return generally OPAQUE and after 7.2 they return TRIGGER. See the example of a trigger below for your function CREATE OR REPLACE FUNCTION customer_bak_proc() RETURNS TRIGGER AS ' BEGIN IF T

Re: [GENERAL] Cartesian product bug?

Greg Stark wrote: > > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > > On Fri, 31 Oct 2003, Tom Lane wrote: > > > > > (Personally I think NATURAL JOIN is an evil, bug-prone construct, > > > precisely because coincidental matches of column names will mess up your > > > results.) > > > > Me too.

Re: [GENERAL] Sequences without blank holes

> On Thu, Nov 06, 2003 at 05:01:54 -0300, > MaRcElO PeReIrA <[EMAIL PROTECTED]> wrote: >> >> $ select * from products; >> prod_id | description >> +- >> 1 | S470DXBLM >> 12 | S470DXABM >> 33 | RG250DX >> +- >> (3 rows) >

Re: [GENERAL] PostgreSQL License Question

Paul Thomas wrote: > > but I was unable to find them in the archive. Also, please reply to me > > directly, I am not subscribed. > > > > Thank you, Ryan Mack > > My understanding is that PostgreSQL's BSD-style license leaves you free to > do whatever want with the it and that includes creating

[GENERAL] int8 primary keys still not using index without manual JDBC driver patch (7.4RC1)

Hi all, Just thought I'd mention that I really think this problem needs to be fixed. I I'm patching the 7.4RC1 JDBC drivers as we speak due to this optimiser bug, and it's the third time I've had to do this. I would think this bug causes quite a lot of people to evaluate postgres and decide

Re: [GENERAL] on connect trigger?

Quoting [EMAIL PROTECTED]: > Is there any kind of mechanism in pg 7.3 for doing something like what I > would describe as a "login trigger" procedure or maybe "on connect" > trigger, i.e., a way to specify a stored procedure to run when a user > connects to the database? > > What I'm thinking is

[GENERAL] on connect trigger

Is there any kind of mechanism in pg 7.3 for doing something like what I would describe as a "login trigger" procedure or maybe "on connect" trigger, i.e., a way to specify a stored procedure to run when a user connects to the database? What I'm thinking is this. Right now, my end-user GUI applica

[GENERAL] Help with "locking" please

Hello, Here's a scenario I am trying to find a solution for. I have a contacts table individual rows of which can be edited in our app. I'd like to be able to mark a row as 'opened by someone else' when editor is opened and un-mark it when window is closed. No problems there, I could add a procpid

[GENERAL] create function for trigger question

I am a postgres newbie. I am trying to create a trigger that will put a copy of a record into a backup table before update or delete. As I understand it, in order to do this I must have a function created to do this task. The function I am trying to create is as follows: CREATE FUNCTION custome

[GENERAL] on connect trigger?

Is there any kind of mechanism in pg 7.3 for doing something like what I would describe as a "login trigger" procedure or maybe "on connect" trigger, i.e., a way to specify a stored procedure to run when a user connects to the database? What I'm thinking is this. Right now, my end-user GUI applica

[GENERAL] pg_stat

Just wondering how often the stats collector resets it self. Is this a parameter i can change? ---(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] Optimiser desicion bringing system to its knees?

"Craig O'Shannessy" <[EMAIL PROTECTED]> writes: > The explains below make it clear I think. If I just change the table > declaration order, I get MASSIVELY better performance. Hm. Is the lineitem_sku_reservation_idx index a partial index, by any chance? I see this bug fix in 7.2.2: 2002-08-22

Re: [GENERAL] int8 primary keys still not using index without manual JDBC driver patch (7.4RC1)

Any particular reason you can't just put the value in quotes and let postgres determine the type? On Fri, Nov 07, 2003 at 10:43:05AM +1100, Craig O'Shannessy wrote: > Hi all, > > Just thought I'd mention that I really think this problem needs to be > fixed. I > > I'm patching the 7.4RC1 JDBC

[GENERAL] pid of current session?

Hello pgsql-general, Is there a way to tell which session from 'select * from pg_stat_activity' is a current session? Obviously if there's way to find out pid of a current session by some other means I could then look the rest in pg_stat_activity. Thanks! -- -Boris ---

Re: [GENERAL] Optimiser desicion bringing system to its knees?

Hi, I'm not onsite, but I just heard back from a colleage that rushed in to try exactly that. It does indeed seem to be fixed in 7.4RC1. I assume this will be pretty stable, and there seems to be heaps of enhancements, so I think I'll just skip 7.3.x. I'm not doing anything funky with postg

Re: [GENERAL] Optimiser desicion bringing system to its knees?

Hello, Well the first thing I would ask is what does the optimizer do if you are running a current version of PostgreSQL? Specifically either 7.3.4 or even 7.4RC1. Sincerely, Joshua Drake Craig O'Shannessy wrote: Hi everyone, My performance on a big mission critical system has recently collap

[GENERAL] pg_stat_activity

Hi all, in the pg_stat_activity catalog there is a field called current_query but it is in blank when i modify the show statement stat in postgresql.conf i win nothing. what am i doing wrong? thanks in advance, Jaime Casanova (el_vigia) ___

Re: [GENERAL] Help on update that subselects other records in table, uses joins

On Thu, 6 Nov 2003 00:50:06 +0900, Michael Glaesemann <[EMAIL PROTECTED]> wrote: >>> What I came up with was deleting and reinserting the relevant >>> ordercharges rows >> >> This might have unwanted side effects (think ON DELETE CASCADE). > >Good point. At this stage in my PostgreSQL progress, I h

[GENERAL] Optimiser desicion bringing system to its knees?

Hi everyone, My performance on a big mission critical system has recently collapsed, and I've finally traced it down to the postgresql optimiser I think. I'm running postgresql-7.2.1-2PGDG The explains below make it clear I think. If I just change the table declaration order, I get MASSIVELY bett

Re: [GENERAL] Insert and obtain the pk

On Thu, Nov 06, 2003 at 12:26:48PM -0600, Eliu Montoya wrote: > Does anyone know an easy way to insert a record (without a value for the > primary key because it is a sequence) and at the same time obtain the value > of primary key that was assign to this new record. Given that you are already

Re: [GENERAL] pid of current session?

Hello, Thursday, November 6, 2003, 1:48:25 PM, I wrote: BP> Is there a way to tell which session from 'select * from BP> pg_stat_activity' is a current session? Obviously if there's way BP> to find out pid of a current session by some other means I could BP> then look the rest in pg_stat_activity

Re: [GENERAL] Default user after upgrade to 7.3?

On Thu, 2003-11-06 at 10:59, [EMAIL PROTECTED] wrote: > I think you should look at your pg_hba.com file: > /var/lib/pgsql/pg_hba.conf > > That is the file of "what users can logon from where and how" > > Perhaps it got clobbered or something. Thanks! I had rebuilt it from the old one, which did

Re: [GENERAL] Question on database structure

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Gaetano Mendola wrote: > > You are going to have nightmares for your queries then. Well, it's ok. As far as I know the zip code I can modify my query. But it would be better, if I don't have to think about that modification. > How much rows you w

[GENERAL] Install files for version 7.4

Title: Install files for version 7.4 Hello Forum, Could someone please send me the link and the names of the files to install the version 7.4 in Windows 2000 server and XP Pro?  I cannot discern the files that one is supposed to download. Thanks in advance for your response. Carlos Oliva

Re: [GENERAL] Insert and obtain the pk

Actually, it's tablename_columname_seq , unless that exceeds namedatalen. It shouldn't. Mine never do... But if it does, there is an algorithm for truncating the table and/or field name to fit. >>> Adam Ruth <[EMAIL PROTECTED]> 11/06/03 11:46AM >>> You can call currval on the sequence that pro

Re: [GENERAL] Insert and obtain the pk

You can call currval on the sequence that provided the value for the primary key. It's guaranteed to be the last value retrieved from that sequence *for your session*. insert into table1 (f1, f2) values ('a', 'b'); select currval('table1_seq'); Use the name of your sequence. If the primary key

Re: [GENERAL] tsearch2 and gist index bloat

--- Oleg Bartunov <[EMAIL PROTECTED]> wrote: > On Thu, 6 Nov 2003, George Essig wrote: > > > Thanks for the reply. For this project, I can update the data and reindex during > > off-peak > hours. > > I was just surprised to see the size of the index double after heavy write > > activity. > > >

Re: [GENERAL] tsearch2 and gist index bloat

On Thu, 6 Nov 2003, George Essig wrote: > Thanks for the reply. For this project, I can update the data and reindex during > off-peak hours. > I was just surprised to see the size of the index double after heavy write activity. > This is not tsearch specific problem. It was discussed several ti

Re: [GENERAL] Install files for version 7.4

Carlos Oliva writes: > Not even for the beta version? In that case you want the file postgresql-7.4RC1.tar.gz. (or .bz2). -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster