Re: [GENERAL] planer picks a bad plan (seq-scan instead of index)

Thomas H. wrote: hi list. as soon as i left-join an additional table, the query takes 24sec instead of 0.2sec, although the added fields have no impact on the resultset: SELECT * FROM shop.dvds LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id WHERE (lower(mov_name) LIKE

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index)

Thomas H. wrote: SELECT * FROM shop.dvds LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id LEFT JOIN shop.data_soundmedia ON sm_info_ean = dvd_ean WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like '%superman re%' OR lower(dvd_edition) LIKE '%superman re%')

Re: [GENERAL] RULE - special variables?

Matthew Peter wrote: Do rules get special variables like triggers? Can I set variables in them like triggers? You get NEW/OLD but they mean something subtly different. Rules act on a query-tree so they are more like placeholders. You can't set variables in triggers. You do so in a function.

Re: [GENERAL] TRIGGERS - access sql query_string that called it?

Matthew Peter wrote: Is it possible to access the entire sql query_string that called the trigger? No. It doesn't necessarily exist in any useful form (think about what happens with rules on a view, or triggers making updates which fire other triggers - what is the "original" query?). --

Re: [GENERAL] dblink / plpgsql - definition list cheats for record

Matthew Peter wrote: Is there any way to use CREATE TYPE or an VIEW/TABLE defintion list instead of manually defining it? Yes, just define your function as returning that type rather than RECORD. That way the calling context will know what to expect. -- Richard Huxton Archonet Ltd -

[GENERAL] Table design - unknown number of column

Hello, For a project I have a table containing products and flags. The columns of this table are of 2 kinds : - Not null data (id, column1, column2) - Flags (100 different flags set to 1 or 0) Over the time the number of flag will increase from 100 to 200 or more. So I have 2 choices : - Use a

Re: [GENERAL] RULE - special variables?

> Do rules get special variables like triggers? Can I set > variables in them like > triggers? A rule is one or more SQL-Statements - how do you set variables in SQL? Don't think of a rule as a program! In a statement in a rule, you have the pseudotables 'OLD' and 'NEW' which refer to the affe

Re: [GENERAL] Table design - unknown number of column

Richard Ollier wrote: Hello, For a project I have a table containing products and flags. The columns of this table are of 2 kinds : - Not null data (id, column1, column2) - Flags (100 different flags set to 1 or 0) Over the time the number of flag will increase from 100 to 200 or more. Having

Re: [GENERAL] Table design - unknown number of column

On fim, 2006-11-09 at 10:56 +0100, Alban Hertroys wrote: > Richard Ollier wrote: > > For a project I have a table containing products and flags. > > The columns of this table are of 2 kinds : > > - Not null data (id, column1, column2) > > - Flags (100 different flags set to 1 or 0) > > > > Over th

Re: [GENERAL] Can PostgreSQL notify a client that a trigger has fired?

Bill wrote: Jorge Godoy wrote: I have the impression that you're missing a lot of sections in the manual... How about some time to re-read it? I don't know about you but for me a 1500 page manual is at least two weeks of full time reading. I have read several sections of it but I am trying t

Re: [GENERAL] [SQL] [ADMIN] Is there anyway to...

[EMAIL PROTECTED] wrote: i'm wanting to learn something here so i'm going to chime in. the way i read what you are saying is that you'd have start_date and number_days columns in your table. each day a query would run and pull the start_date and numbers_days columns. the application (or postgr

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index)

I am admittedly speaking up somewhat late here, and may be completely off base, but it seems to me that the "LIKE" operation is almost always going to be a loser, performance-wise, when there is an initial wildcard, e.g. "%superman re%" will require a sequential scan, while "superman re%" would

Re: [GENERAL] RULE - special variables?

Albe Laurenz wrote: Do rules get special variables like triggers? Can I set variables in them like triggers? A rule is one or more SQL-Statements - how do you set variables in SQL? SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT } http://www.postgresql.org/docs/8.1/inte

[GENERAL] autovacuum blues

Hi, I just can't understand why autovacuum is not working. I have a test db/table which I insert values into (by the thousands) and can't work out why my stats don't get updated. Could someone have a quick look at my attached .conf and tell me what I am doing? I am running it on FC5 8.1.4.fc5.1. C

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index)

Thomas H. wrote: hi list. as soon as i left-join an additional table, the query takes 24sec instead of 0.2sec, although the added fields have no impact on the resultset: SELECT * FROM shop.dvds LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id WHERE (lower(mov_name) LIKE

Re: [GENERAL] RULE - special variables?

Shane Ambler wrote: Albe Laurenz wrote: Do rules get special variables like triggers? Can I set variables in them like triggers? A rule is one or more SQL-Statements - how do you set variables in SQL? SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT } http://www.postgre

Re: [GENERAL] autovacuum blues

Anton Melser wrote: Hi, I just can't understand why autovacuum is not working. I have a test db/table which I insert values into (by the thousands) and can't work out why my stats don't get updated. Could someone have a quick look at my attached .conf and tell me what I am doing? I am running it

Re: [GENERAL] Can PostgreSQL notify a client that a trigger has fired?

"Bill" <[EMAIL PROTECTED]> writes: > Jorge Godoy wrote: > >> I have the impression that you're missing a lot of sections in the >> manual... How about some time to re-read it? > > I don't know about you but for me a 1500 page manual is at least two > weeks of full time reading. I have read severa

[GENERAL] Changing the number and type of columns in a view

Hi General, Is there a reason why it is not possible to redefine a view with a different number of columns or a different column type? It's been possible to change the datatypes of a table, and the column numbers for a long time. What are the restrictions on making this possible for views.

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index) when adding an additional join

1. You MUST sequence scan dvds, as there is no way to do an index search on a like with % at the beginning. 2. You are asking for a left join on dvds, which means you want all records, so you must sequence scan dvds. The filters are all OR, so you can't say that a records is excluded until AFTE

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index)

Try putting your conditions as part of the join: SELECT * FROM shop.dvds LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id AND ( lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like '%superman re%' OR lower(dvd_edition) LIKE '%superman re%' ) LEFT JOIN shop.data_soundme

Re: [GENERAL] Changing the number and type of columns in a view

Russell Smith wrote: Hi General, Is there a reason why it is not possible to redefine a view with a different number of columns or a different column type? It's been possible to change the datatypes of a table, and the column numbers for a long time. What are the restrictions on making this

[GENERAL] Too many open cursors

  Hi All ..   I have used Oracle 9i in the past ...currently working on postgres ...   Oracle throws this SQLException ... Too many open cursors when there are too many connections to the database.   I have found that  ..but postgres ..locks ..the DB ..and doesnt throw this exception   I am tr

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index)

Thomas H. wrote: Try putting your conditions as part of the join: SELECT * FROM shop.dvds LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id AND ( lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like '%superman re%' OR lower(dvd_edition) LIKE '%superman re%' ) LEFT JOIN

Re: [GENERAL] autovacuum blues

On 09/11/06, Richard Huxton wrote: Anton Melser wrote: > Hi, > I just can't understand why autovacuum is not working. I have a test > db/table which I insert values into (by the thousands) and can't work > out why my stats don't get updated. Could someone have a quick look at > my attached .conf

[GENERAL] [PL/pgSQL] How should I use FOUND special variable. Documentation is little unclear for me

Hello.I'm porting some procedures from PL/SQL  and I encountered following problem:In PL/SQL I'm using this statement related to cursor:OPEN crs_cnt(start_millis, end_millis);LOOP FETCH crs_cnt into row_cnt;     EXIT WHEN crs_cnt%NOTFOUND;    insert into spm_audit_stats values(SEQ_SPM_ID_AUTO_INC.n

Re: [GENERAL] reproducing this issue on PG 8.0.0 ERROR: index "patient_pkey" is not a btree

surabhi.ahuja wrote: > hi > I am using Postgres 8.0.0 and we found this issue "ERROR: index > "patient_pkey" is not a btree" I have been informed that we should > shift to Postgres 8.0.9 > > I discussed this with my team member and they are asking if we can > upgrade to the latest Postgres vers

Re: [GENERAL] [PL/pgSQL] How should I use FOUND special variable.

Title: Nachricht Hi!   Just use this:   FETCH crs_cnt into row_cnt; EXIT WHEN NOT FOUND;   Greetings, Matthias -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeremiasz MiedzinskiSent: Thursday, November 09, 2006 1:15 PMTo: pgsql-general@p

Re: [GENERAL] autovacuum blues

Hi Anton! I'm not sure how this is with 8.1 but on 7.4.14 we have to enable row level statistics collection for autovacuum: stats_row_level = true Greetings, Matthias > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Anton Melser > Sent: Thursday, N

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index)

OK - in that case try explicit subqueries: SELECT ... FROM (SELECT * FROM shop.dvds LEFT JOIN shop.oldtables.movies WHERE lower(mov_name) LIKE ... ) AS bar LEFT JOIN shop.data_soundmedia same result, have tried this as well (22sec). it's the LEFT JOIN shop.data_soundmedia for which the plan

Re: [GENERAL] [PL/pgSQL] How should I use FOUND special variable.

Jeremiasz Miedzinski wrote: Hello. I'm porting some procedures from PL/SQL and I encountered following problem: In PL/SQL I'm using this statement related to cursor: OPEN crs_cnt(start_millis, end_millis); LOOP FETCH crs_cnt into row_cnt; EXIT WHEN crs_cnt%NOTFOUND; insert into spm_aud

Re: [GENERAL] Table design - unknown number of column

Alban Hertroys wrote: Richard Ollier wrote: Hello, For a project I have a table containing products and flags. The columns of this table are of 2 kinds : - Not null data (id, column1, column2) - Flags (100 different flags set to 1 or 0) Over the time the number of flag will increase from 100 t

Re: [GENERAL] converting Informix outer to Postgres

I am not familiar with Informix but: - is OUTER() a LEFT or FULL outer join? - it is important where you put your join condition in Postgres wrt NULL insertions of OUTER joins E.g. Tables A(k,a) with (k1,a1), (k2, a2) records and table B(k,b) with (k1, b1) will result in: A LEFT OUTER JOIN B

Re: [GENERAL] Table design - unknown number of column

I think the typical way of attacking a problem would be a second and third table. The second table would look like: flat_type table flag_type_id flag_type (like the column name in your original table) flag_type_description (BONUS: you can describe each flag) product_flag table product_

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index)

Thomas H. wrote: OK - in that case try explicit subqueries: SELECT ... FROM (SELECT * FROM shop.dvds LEFT JOIN shop.oldtables.movies WHERE lower(mov_name) LIKE ... ) AS bar LEFT JOIN shop.data_soundmedia same result, have tried this as well (22sec). it's the LEFT JOIN shop.data_soundmedia

Re: [GENERAL] autovacuum blues

Anton Melser wrote: > Hi, > I just can't understand why autovacuum is not working. I have a test > db/table which I insert values into (by the thousands) and can't work > out why my stats don't get updated. Could someone have a quick look at > my attached .conf and tell me what I am doing? > I am r

Re: [GENERAL] Table design - unknown number of column

On Thursday 9. November 2006 09:34, Richard Ollier wrote: >Hello, > >For a project I have a table containing products and flags. >The columns of this table are of 2 kinds : >- Not null data (id, column1, column2) >- Flags (100 different flags set to 1 or 0) > >Over the time the number of flag will

[GENERAL] Why overlaps is not working

set datestyle to iso,iso; select 1 where ('2006-10-31'::date, '-12-31'::date) OVERLAPS ('2006-10-16'::DATE, '2006-10-31':: DATE) does not return any rows. Why ? How to make overlaps to return correct result? Andrus. ---(end of broadcast)---

Re: [GENERAL] Why overlaps is not working

Hm, why not this one: select ('2006-10-31'::date, '-12-31'::date) OVERLAPS ('2006-10-16'::DATE, '2006-10-31':: DATE); overlaps -- f (1 row) Greetings, Matthias > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Andrus > Sent: Thursday,

[GENERAL] database dump then restore on another system?

To date I have always used pg on a system where I had pg superuser status. I'm trying to move a database from such a system to one where I am just a user, and I'm having a couple of problems. The first is, the output of pg_dump has a lot of lines like: ALTER FUNCTION some_function OWNER TO ric

Re: [GENERAL] Why overlaps is not working

Andrus wrote: set datestyle to iso,iso; select 1 where ('2006-10-31'::date, '-12-31'::date) OVERLAPS ('2006-10-16'::DATE, '2006-10-31':: DATE) does not return any rows. Why ? They're adjacent, they don't overlap. Check the documentation on OVERLAPS, I'm sure it's explicit about whet

Re: [GENERAL] Why overlaps is not working

am Thu, dem 09.11.2006, um 15:46:50 +0200 mailte Andrus folgendes: > set datestyle to iso,iso; > > select 1 where ('2006-10-31'::date, '-12-31'::date) OVERLAPS >('2006-10-16'::DATE, '2006-10-31':: DATE) > > > does not return any rows. > > Why ? > How to make overlaps to return correct

Re: [GENERAL] Why overlaps is not working

I have a number of select statements (in 8.1 and 8.2beta) which assume that overlaps returns true for those cases. Which the best way to fix them ? Should I use AND, OR and date comparison operators instead of OVERLAPS ? Andrus. ---(end of broadcast)-

Re: [GENERAL] Why overlaps is not working

> They're adjacent, they don't overlap. Check the documentation on OVERLAPS, > I'm sure it's explicit about whether it is inclusive or exclusive (the > latter apparently). 8.2 doc does not explain term overlap. It only says: "This expression yields true when two time periods (defined by their

Re: [GENERAL] Why overlaps is not working

2006/11/9, Andrus <[EMAIL PROTECTED]>: > They're adjacent, they don't overlap. Check the documentation on OVERLAPS,> I'm sure it's explicit about whether it is inclusive or exclusive (the> latter apparently).8.2 doc does not explain term overlap. It only says: "This _expression_ yields true when tw

[GENERAL] cannot connect anymore from a remote host

Hi all, after a crash of my machine I restarted the pgsql as usual, and I can connect from the machine itself, but no more from a remote host. I checked the pg_hba.conf file and it's ok, but either from psql or pgadmin I cannot connect to the host. Nmapping my host I cannot see the daemon listen

[GENERAL] posgres headers

Hi all. Sorry to bother. Does anyone know where the postgres headers are stored? Which files constitute postgres headers? kind regards, Antonios Access over 1 million songs - Yahoo! Music Unlimited.

Re: [GENERAL] Why overlaps is not working

Alban Hertroys <[EMAIL PROTECTED]> writes: > They're adjacent, they don't overlap. Check the documentation on > OVERLAPS, I'm sure it's explicit about whether it is inclusive or > exclusive (the latter apparently). It's not very clear, but the spec defines (S1,T1) OVERLAPS (S2,T2) as

Re: [GENERAL] posgres headers

On Thursday 09 November 2006 06:33 am, Antonios Katsikadamos wrote: > Hi all. Sorry to bother. Does anyone know where the postgres headers are > stored? Which files constitute postgres headers? > > kind regards, > > Antonios > > - > Access over 1 million songs - Ya

[GENERAL] [PL/pgSQL] Commit every N rows. Is it possible ?

Hello,I'm still trying to convert my PL/SQL stored procedures into PL/pgSQL. Now, I have problem with commiting transaction every N rows: loopfetch csr_ac into row_id;   if not FOUND then  exit;   end if;   counter := counter + 1;   delete from spm_audit where adt_id=row_id;   delete from spm_a

Re: [GENERAL] [PL/pgSQL] Commit every N rows. Is it possible ?

Jeremiasz Miedzinski wrote: I'm digging into postgresql documentation but maybe I'm just not smart enough to understand the way which transactions are being processed into pgSQL. Is it possible to port above code to PL/pgSQL ? All functions, including pl/pgsql functions take place within a tra

Re: [GENERAL] cannot connect anymore from a remote host

2006/11/9, Luca Ferrari <[EMAIL PROTECTED]>: Hi all,after a crash of my machine I restarted the pgsql as usual, and I can connectfrom the machine itself, but no more from a remote host. I checked thepg_hba.conf file and it's ok, but either from psql or pgadmin I cannot connect to the host. Nmapping

Re: [GENERAL] cannot connect anymore from a remote host

Luca Ferrari wrote: Hi all, after a crash of my machine I restarted the pgsql as usual, and I can connect from the machine itself, but no more from a remote host. I checked the pg_hba.conf file and it's ok, but either from psql or pgadmin I cannot connect to the host. Nmapping my host I cannot

Re: [GENERAL] Table design - unknown number of column

On 11/9/06, Richard Ollier <[EMAIL PROTECTED]> wrote: Hello, For a project I have a table containing products and flags. The columns of this table are of 2 kinds : - Not null data (id, column1, column2) - Flags (100 different flags set to 1 or 0) Over the time the number of flag will increase f

Re: [GENERAL] 8.1.2 postmaster died

On Thu, Nov 09, 2006 at 11:52:48AM +0500, Shoaib Mir wrote: > Have a look at "16.4.3. Linux Memory Overcommit" on > http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html If you look at the beginning of the thread you'dve seen we're talking about HPUX here... Maybe they have an OOM

Re: [GENERAL] cannot connect anymore from a remote host

On Thursday 09 November 2006 15:57 Jeremiasz Miedzinski's cat, walking on the keyboard, wrote: > Have You checked network interfaces on your machine ? Maybe some of them > doesn't start-up properly... Interfaces are ok, and in fact other services on such interface are running correctly. Luca

Re: [GENERAL] cannot connect anymore from a remote host

Richard Huxton writes: > Luca Ferrari wrote: >> I've started the daemon as: >> postmaster -D /mnt/data/database & > Is that how you normally start your DB server? Ditto. But if you are using a stock postgresql.conf then in fact the postmaster will *not* be listening to TCP with that command lin

Re: [GENERAL] Why overlaps is not working

Andrus wrote: I have a number of select statements (in 8.1 and 8.2beta) which assume that overlaps returns true for those cases. Which the best way to fix them ? Should I use AND, OR and date comparison operators instead of OVERLAPS ? Why not just subtract/add 1, so that the check includes t

Re: [GENERAL] authentication question

Craig White wrote: > logs say... > Nov 8 20:18:26 srv1 postgresql: Starting postgresql service: succeeded > Nov 8 20:18:39 srv1 postgres[21020]: PAM audit_open() failed: > Permission denied > Nov 8 20:18:39 srv1 postgres[21020]: [2-1] LOG: pam_authenticate > failed: System error > Nov 8 20:1

[GENERAL] ROWTYPE initialization question

'lo list, I have a plpgsql SP where I loop through a cursor. I have an internal variable that keeps the previous row, so that I can compare it with the current row in the cursor. Like so; DECLARE current table%ROWTYPE; previous table%ROWTYPE; BEGIN LOOP FETCH tableCur INTO

Re: [GENERAL] Why overlaps is not working

> Why not just subtract/add 1, so that the check includes the boundary > dates? > > Like so; > select 1 where ('2006-10-31'::date -1, '-12-31'::date +1) OVERLAPS >('2006-10-16'::DATE -1, '2006-10-31':: DATE +1) Alban, thank you. I use only dates as OVERLAPS arguments. I changed all my

Re: [GENERAL] 8.1.2 postmaster died

Look at the database server logs and see what actually was happening just before the server crashed... do you have the auto vacuuming running at backend? or was there some client making connection just when the server did crash? your db logs can actually help you here. Thank you,---Sho

Re: [GENERAL] cannot connect anymore from a remote host

You might want to check the ip tables as well if they have the required entries or not.Thanks,---Shoaib MirEnterpriseDB (www.enterprisedb.com) On 11/9/06, Richard Huxton wrote: Luca Ferrari wrote:> Hi all,> after a crash of my machine I restarted the pgsql as usual, and I can

Re: [GENERAL] authentication question

On Thu, 2006-11-09 at 12:34 -0300, Alvaro Herrera wrote: > Craig White wrote: > > > logs say... > > Nov 8 20:18:26 srv1 postgresql: Starting postgresql service: succeeded > > Nov 8 20:18:39 srv1 postgres[21020]: PAM audit_open() failed: > > Permission denied > > Nov 8 20:18:39 srv1 postgres[21

Re: [GENERAL] authentication question

Craig White <[EMAIL PROTECTED]> writes: > I haven't had to fool too much with pam for authenticating other > services so I'm a little bit out of my knowledge base but I know that it > was simple to add netatalk into the pam authentication and expected that > postgresql would be similar. FWIW, we s

[GENERAL] Datum problem

Hi, I'm newbie of programming postgresql sever side, I write the function below and when I execute SELECT (anag_art, 150) AS esistenza from anag_art order by 1; result is something about this: | esistenza | ("(""002 "",""ARTICOLO PREZ. VEND. "",,PZ,32,1,20,""1

[GENERAL] Mac OS X

I run an older version of psql (7.2.4) on a Linux machine.I wish to migrate psql to an Intel Mac running OS x 10.4.8.The supported platforms table in postgresql-8.1-US.pdf (page 259) lists Mac OS X with a PPC cpu as being supported, but not Mac OS X with a Intel cpu.Can psql run on an Intel Mac?Hav

Re: [GENERAL] Why overlaps is not working

"Andrus" <[EMAIL PROTECTED]> writes: >> Why not just subtract/add 1, so that the check includes the boundary >> dates? >> >> Like so; >> select 1 where ('2006-10-31'::date -1, '-12-31'::date +1) OVERLAPS >>('2006-10-16'::DATE -1, '2006-10-31':: DATE +1) > > Alban, > > thank you. I use on

Re: [GENERAL] Mac OS X

On Thu, 2006-11-09 at 10:10 -0700, Nathan Leon Pace, MD, MStat wrote: > I run an older version of psql (7.2.4) on a Linux machine. > > > I wish to migrate psql to an Intel Mac running OS x 10.4.8. > > > The supported platforms table in postgresql-8.1-US.pdf (page 259) > lists Mac OS X with a PP

Re: [GENERAL] autovacuum blues

On 09/11/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Anton Melser wrote: > Hi, > I just can't understand why autovacuum is not working. I have a test > db/table which I insert values into (by the thousands) and can't work > out why my stats don't get updated. Could someone have a quick look at

Re: [GENERAL] Mac OS X

On Nov 9, 2006, at 9:10 AM, Nathan Leon Pace, MD, MStat wrote: I run an older version of psql (7.2.4) on a Linux machine. I wish to migrate psql to an Intel Mac running OS x 10.4.8. The supported platforms table in postgresql-8.1-US.pdf (page 259) lists Mac OS X with a PPC cpu as being supp

Re: [GENERAL] Datum problem

On Thu, Nov 09, 2006 at 06:12:08PM +0100, Enrico wrote: > Hi, > I'm newbie of programming postgresql sever side, > I write the function below and when I execute > > SELECT (anag_art, 150) AS esistenza from anag_art order by 1; > > result is something about this: Well, you didn't actually call a

Re: [GENERAL] Datum problem

am Thu, dem 09.11.2006, um 18:12:08 +0100 mailte Enrico folgendes: > Hi, > I'm newbie of programming postgresql sever side, > I write the function below and when I execute > > SELECT (anag_art, 150) AS esistenza from anag_art order by 1; Try select *, 150 AS esistenza from anag_art order by 1;

Re: [GENERAL] Too many open cursors

On Thu, 2006-11-09 at 06:07, Sandeep Kumar Jakkaraju wrote: > > Hi All .. > > I have used Oracle 9i in the past ...currently working on postgres ... > > Oracle throws this SQLException ... Too many open cursors > when there are too many connections to the database. That may be the excep

Re: [GENERAL] authentication question

On Thu, 2006-11-09 at 11:51 -0500, Tom Lane wrote: > Craig White <[EMAIL PROTECTED]> writes: > > I haven't had to fool too much with pam for authenticating other > > services so I'm a little bit out of my knowledge base but I know that it > > was simple to add netatalk into the pam authentication a

[GENERAL] Problem with pg_dump

I seem to be having a problem with pg_dump in 8.1.2, it's not dumping indexes at all.  Is this a known problem, should I just do an upgrade?Thanks,Alex TurnerMint Pixels

Re: [GENERAL] Can PostgreSQL notify a client that a trigger has

On Thu, 2006-11-09 at 10:16 +, Richard Huxton wrote: > Bill wrote: > > Jorge Godoy wrote: > > > >> I have the impression that you're missing a lot of sections in the > >> manual... How about some time to re-read it? > > > > I don't know about you but for me a 1500 page manual is at least two

Re: [GENERAL] Problem with pg_dump

am Thu, dem 09.11.2006, um 13:04:31 -0500 mailte Alex Turner folgendes: > I seem to be having a problem with pg_dump in 8.1.2, it's not dumping indexes > at all. Is this a known problem, should I just do an upgrade? I can't see a necessity to dump a index. But, i hope, and i'm sure, pg_dump dump

Re: [GENERAL] Why overlaps is not working

>> WHERE (a,b) OVERLAPS (c,d) >> >> to >> >> WHERE (a-1,b+1) OVERLAPS (c-1,d+1) >> >> Will this give correct results ? > > It might give you false positives... > > 2006-11-30 -- 2006-12-05 AND2006-12-06 -- 2006-12-15 (original) -- > FALSE > 2006-11-29 -- 2006-12-06 AND2006-12-05 -- 200

Re: [GENERAL] Can PostgreSQL notify a client that a trigger has fired?

Jeff Davis wrote: On Thu, 2006-11-09 at 10:16 +, Richard Huxton wrote: Bill wrote: Jorge Godoy wrote: I have the impression that you're missing a lot of sections in the manual... How about some time to re-read it? I don't know about you but for me a 1500 page manual is at least two week

Re: [GENERAL] authentication question

Craig White <[EMAIL PROTECTED]> writes: > On Thu, 2006-11-09 at 11:51 -0500, Tom Lane wrote: >> FWIW, we ship this PAM config file in the Red Hat PG RPMs: > that doesn't work at all... /var/log/messages reports... Sorry, I should have mentioned that that was for recent Fedora branches. In RHEL4 I

[GENERAL] Installation Instruction For Window

Where I can find the instruction on-line? I only see the UNIX/LINUX instruction. I installed PG on window before, but can't remember how it is done right now. BTW, is the 8.2 beta good for a development environment? Thanks, vernon

Re: [GENERAL] authentication question

Tom Lane wrote: > Craig White <[EMAIL PROTECTED]> writes: > > I haven't had to fool too much with pam for authenticating other > > services so I'm a little bit out of my knowledge base but I know that it > > was simple to add netatalk into the pam authentication and expected that > > postgresql wou

Re: [GENERAL] autovacuum blues

On Thu, 2006-11-09 at 18:16 +0100, Anton Melser wrote: > On 09/11/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > Anton Melser wrote: > > > Hi, > > > I just can't understand why autovacuum is not working. I have a test > > > db/table which I insert values into (by the thousands) and can't work >

Re: [GENERAL] authentication question

On Thu, 2006-11-09 at 16:34 -0300, Alvaro Herrera wrote: > Tom Lane wrote: > > Craig White <[EMAIL PROTECTED]> writes: > > > I haven't had to fool too much with pam for authenticating other > > > services so I'm a little bit out of my knowledge base but I know that it > > > was simple to add netata

Re: [GENERAL] Why overlaps is not working

"Andrus" <[EMAIL PROTECTED]> writes: > Jorge, > > Thank you very much. Now I try to William Leite Araújo solution by replacing > > WHERE (a,b) OVERLAPS (c,d) > > with > > WHERE ( c BETWEEN a AND b ) OR ( d BETWEEN a AND b ) > > Is this OK ? From bare tests this looks OK. > This requires writi

Re: [GENERAL] autovacuum blues

Anton Melser wrote: > Thanks for that. Just a clarification, can someone tell me what the > "number of tuples" means in the context of the multipliers? I mean, > when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I > get the min val, but what are we multiplying by 0.4? The total nu

[GENERAL] Can non-superuser install c functions ?

I am transferring a database from a system where I am a pg superuser to one where I am not. The database uses tsearch2. I am unable to install any of the functions. For example: CREATE FUNCTION gtsvector_in(cstring) RETURNS gtsvector AS '$libdir/tsearch2', 'gtsvector_in' LANGUAGE c ST

Re: [GENERAL] Can non-superuser install c functions ?

On Thu, Nov 09, 2006 at 04:04:33PM -0500, Rick Schumeyer wrote: > I am transferring a database from a system where I am a pg superuser to > one where I am not. > results in: permission denied for language c > > Do I need to get the pg administrator to install my database ? Yes. Letting someon

Re: [GENERAL] autovacuum blues

On 09/11/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Anton Melser wrote: > Thanks for that. Just a clarification, can someone tell me what the > "number of tuples" means in the context of the multipliers? I mean, > when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I > get the

Re: [GENERAL] autovacuum blues

Anton Melser wrote: > On 09/11/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > >Anton Melser wrote: > > > >> Thanks for that. Just a clarification, can someone tell me what the > >> "number of tuples" means in the context of the multipliers? I mean, > >> when the vacuum min multiplier is at 0.4, we

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index) when adding an additional join

"Thomas H." <[EMAIL PROTECTED]> writes: > SELECT * FROM shop.dvds > LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id > LEFT JOIN shop.data_soundmedia ON sm_info_ean = dvd_ean > WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like > '%superman re%' OR lower(dvd_edition) LIKE '%super

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index) when adding an additional join

SELECT * FROM shop.dvds LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id LEFT JOIN shop.data_soundmedia ON sm_info_ean = dvd_ean WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like '%superman re%' OR lower(dvd_edition) LIKE '%superman re%') Um, what's the datatype of sm_info_ean

[GENERAL] zero values in statistic views

Hi all I would like to see what is going on in a database using the statistic views. I am superuser, stats_start_collector = on stats_row_level = on stats_block_level = on select * from pg_stat_user_tables; select * from pg_stat_user_indexes; select * from pg_statio_user_tables select * from

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index) when adding an additional join

"Thomas H." <[EMAIL PROTECTED]> writes: >> Um, what's the datatype of sm_info_ean and dvd_ean exactly? > varchar(15) and varchar(14) OK. I was wondering if you'd tried to use the new contrib/isn code and it was messing up the estimates somehow. Seems like a red herring. After looking more clos

[GENERAL] AutoVacuum on demand?

I would like a way to run the autovacuum daemon on demand periodically. Every night at 2 AM, for example. Anybody know if this is possible? If not, it's a feature request :-) -Glen ---(end of broadcast)--- TIP 1: if posting/reading through Use

[GENERAL] Remote tables infrastructure.

Hello. One nice feature of PostgreSQL's CREATE FUNCTION/RULE/VIEW system is ability to create objects which "may appear closer than they really are". Hence, all the dbi-link-like modules. The usual method is to create a view on a set returning function (if reasonable), create RULEs for managing

Re: [GENERAL] AutoVacuum on demand?

On Thu, 2006-11-09 at 17:00 -0800, Glen Parker wrote: > I would like a way to run the autovacuum daemon on demand periodically. >Every night at 2 AM, for example. > > Anybody know if this is possible? If not, it's a feature request :-) use vacuumdb and cron. Joshua D. Drake > > -Glen >

[GENERAL] blocking function in PL/Python

suppose we have a stored procedure written in PL/Python. What happens if the function blocks for a while? Does the server still works for the other clients? signature.asc Description: OpenPGP digital signature

Re: [GENERAL] AutoVacuum on demand?

Joshua D. Drake wrote: On Thu, 2006-11-09 at 17:00 -0800, Glen Parker wrote: I would like a way to run the autovacuum daemon on demand periodically. Every night at 2 AM, for example. Anybody know if this is possible? If not, it's a feature request :-) use vacuumdb and cron. Cron yes, v

  1   2   >