Re: [GENERAL] on delete cascade slowing down delete

Is it going to make things faster if I: delete from s; reindex table s; Why do you think this step would help you any? There's no index on p to begin with. You'd just be reindexing the auto-generated unique index on s (due to it being a PK). delete from p; And no, this would most lik

Re: [GENERAL] Pg dump Error

tuanhoanganh escribió: > On error computer i dump 2 databases. The first dump well but the second > dump fail. > My Postgres server & client version are 8.3.3-1 > I call it by command > "D:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe" -h192.168.0.1 -U postgres > -W -p 5433 -Fp -C -fsys.out sys -->

Re: [GENERAL] Pg dump Error

On error computer i dump 2 databases. The first dump well but the second dump fail. My Postgres server & client version are 8.3.3-1 I call it by command "D:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe" -h192.168.0.1 -U postgres -W -p 5433 -Fp -C -fsys.out sys --> OK "D:\Program Files\PostgreSQL\8.

Re: [GENERAL] WAL archiving to network drive

Greg Smith wrote: On Wed, 20 Aug 2008, Glen Parker wrote: The database will continue accumulating WAL segments it can't recycle if the archiver keeps failing, which can cause the size of the pg_xlog directory (often mounted into a separate, smaller partition or disk) to increase dramatically.

Re: [GENERAL] oracle rank() over partition by queries

Ruben Gouveia <[EMAIL PROTECTED]> writes: > Does postgreSQL have an equivalent of the Oracle's "rank() over by > partition"? No. There's some work in progress that might or might not be ready in time for 8.4 ... regards, tom lane -- Sent via pgsql-general mailing list (

Re: [GENERAL] Single character bitfields

-Original Message- From: Bill Moran [mailto:[EMAIL PROTECTED] Sent: Thursday, 21 August 2008 22:36 To: [EMAIL PROTECTED] Cc: Andrew Maclean; General Subject: Re: [GENERAL] Single character bitfields In response to "Andrew Maclean" <[EMAIL PROTECTED]>: > I have a large database and I wa

Re: [GENERAL] Single character bitfields

-Original Message- From: Tomasz Ostrowski [mailto:[EMAIL PROTECTED] Sent: Thursday, 21 August 2008 18:46 To: [EMAIL PROTECTED] Cc: General Subject: Re: Single character bitfields On 2008-08-21 05:29, Andrew Maclean wrote: > Is char(1) one byte in size? No. It will also depend on databas

[GENERAL] Text field truncated using ADO/ODBC?

I am new to PostgreSQL and running 8.3 on Windows. I am connecting using ADO and the ODBC driver. I have a table the contains a Varchar(80) column and a Text column as well as a couple of integer and datetime columns. My connection string contains TextAsLongVarchar=1 and MaxLongVarcharSize=655

[GENERAL] oracle rank() over partition by queries

Does postgreSQL have an equivalent of the Oracle's "rank() over by partition"? Here's what i have so far but it doesn't work: select id, sum(case when key = 5 then 1 else 0 end), sum(case when key = 6 then 1 when key = 7 then 1 else 0 end), sum(case when l.status = 6 then cos

Re: [GENERAL] [ADMIN] Query performance difference

My suggestion was based on your question .. >Why? What will be the difference? >Is it in their query execution methods, with/without indexes etc?. An explain plan would tell you what the execution path is. This should be the same independent of the front end you use. Things such as drivers, ne

Re: [GENERAL] New to postgres -' how to' tips needed

On Thursday 21 August 2008 15:16:29 Markova, Nina wrote: > Hi, > > > I'm completely new to postgres. My main job will be to install and > configure it properly and I'm not sure how much I can rely on the default > values. > > Are there any good articles howto install and configure postgres? I have

Re: [GENERAL] [ADMIN] Query performance difference

What do you want to say? Please give details. I got an explain from PGAdmin, as follows "GroupAggregate (cost=28927.37..35594.39 rows=156871 width=24)" " -> Sort (cost=28927.37..29319.55 rows=156871 width=24)" "Sort Key: docacctransactions.accgroupid, docs.docid, docs.docdate" "

Re: [GENERAL] [ADMIN] Query performance difference

It may be worth-wile to generate an explain plan http://www.postgresql.org/docs/8.3/interactive/sql-explain.html From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of c k Sent: Thursday, August 21, 2008 2:49 PM To: pgsql-general@postgresql.org; [EMAIL PROTECTED] Subject: [ADMIN] Quer

Re: [GENERAL] SERIAL datatype

Thanks. I thought it was a bit counter-intuitive to have a BIGSERIAL while I will only have a few thousands of entries, which are updated (by DELETE and INSERT) constantly. Thanks Scott, Peter Scott Marlowe schreef: On Thu, Aug 21, 2008 at 1:08 PM, Peter Billen <[EMAIL PROTECTED]> wrote:

Re: [GENERAL] SERIAL datatype

On Thu, Aug 21, 2008 at 1:51 PM, Peter Billen <[EMAIL PROTECTED]> wrote: > My concern is not that the table will become full, but that the sequence > will be exhausted. Doing INSERT, DELETE, INSERT, DELETE ... will exhaust the > sequence. What will happen then? Do I have to manually re-order my ser

Re: [GENERAL] SERIAL datatype

On Thu, Aug 21, 2008 at 1:08 PM, Peter Billen <[EMAIL PROTECTED]> wrote: > Hi all, > > I would like to ask a question about the serial datatype. Say I have a field > of type serial, and say for the sake of example that the range of a serial > goes from 1 to 5 (inclusive). I insert 5 (ed) entries in

Re: [GENERAL] SERIAL datatype

My concern is not that the table will become full, but that the sequence will be exhausted. Doing INSERT, DELETE, INSERT, DELETE ... will exhaust the sequence. What will happen then? Do I have to manually re-order my serial values and reset the start sequence ID to MAX() + 1? Thanks in advance

Re: [GENERAL] New to postgres -' how to' tips needed

On Thursday 21 August 2008, "Markova, Nina" <[EMAIL PROTECTED]> wrote: > Hi, > > > I'm completely new to postgres. My main job will be to install and > configure it properly and I'm not sure how much I can rely on the default > values. > > Are there any good articles howto install and configure po

[GENERAL] Query performance difference

Hello, I have a question regarding query performance from two pgsql applications. PGadmin III 1.8 and Navicat for postgresql 8. Both connected to same server on local machine 8.3 and runs a same query for appox. 1,60,000 rows with one min, one max and addition of the min and max values on two table

[GENERAL] New to postgres -' how to' tips needed

Hi, I'm completely new to postgres. My main job will be to install and configure it properly and I'm not sure how much I can rely on the default values. Are there any good articles howto install and configure postgres? I have found the online documentation but need something simple. Any sug

Re: [GENERAL] SERIAL datatype

On Aug 21, 2008, at 2:23 PM, Peter Billen wrote: Oops, my example was a bit incorrectly edited. I wanted to say that the range of a serial datatype goes from 1 to 5 (incluse) and I insert five entries (not 10). Peter Peter Billen schreef: Hi all, I would like to ask a question about the

Re: [GENERAL] SERIAL datatype

Oops, my example was a bit incorrectly edited. I wanted to say that the range of a serial datatype goes from 1 to 5 (incluse) and I insert five entries (not 10). Peter Peter Billen schreef: Hi all, I would like to ask a question about the serial datatype. Say I have a field of type serial,

[GENERAL] SERIAL datatype

Hi all, I would like to ask a question about the serial datatype. Say I have a field of type serial, and say for the sake of example that the range of a serial goes from 1 to 5 (inclusive). I insert 10 entries into the table, so the table is 'full': INSERT INTO my_table (my_serial) VALUES (D

Re: [GENERAL] on delete cascade slowing down delete

On Thu, 21 Aug 2008 10:19:44 -0700 Joshua Drake <[EMAIL PROTECTED]> wrote: > On Thu, 21 Aug 2008 10:16:21 -0700 > Alan Hodgson <[EMAIL PROTECTED]> wrote: > > > > > Any hint to track down the problem? > > 1) Define slow. still undefined anyway 20-30min compared to over 2h and still running. >

Re: [GENERAL] on delete cascade slowing down delete

On Thu, Aug 21, 2008 at 07:06:32PM +0200, Ivan Sergio Borgonovo wrote: > I've a large table with a pk and several smaller tables with fk > referencing to it. > > deleting from the first table is very slow. > > Not all the related fk have indexes but they are VERY small (0 to > 100 records) while

Re: [GENERAL] on delete cascade slowing down delete

On Thu, 21 Aug 2008 10:16:21 -0700 Alan Hodgson <[EMAIL PROTECTED]> wrote: > > Any hint to track down the problem? > > 1) Define slow. > > 2) Tell us what your disk subsystem consists of. > > 3) Are there any delete triggers on the tables? > And the EXPLAIN ANALYZE Joshua D. Drake -- The

Re: [GENERAL] on delete cascade slowing down delete

On Thursday 21 August 2008, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > I've a large table with a pk and several smaller tables with fk > referencing to it. > > deleting from the first table is very slow. > > Not all the related fk have indexes but they are VERY small (0 to > 100 records) w

Re: [GENERAL] What's size of your PostgreSQL Database?

On Thu, 2008-08-21 at 22:17 +0800, Amber wrote: > Another question, how many people are there maintaining this huge database. > We have about 2T of compressed SAS datasets, and now considering load them > into a RDBMS database, > according to your experience, it seems a single PostgreSQL instanc

[GENERAL] on delete cascade slowing down delete

I've a large table with a pk and several smaller tables with fk referencing to it. deleting from the first table is very slow. Not all the related fk have indexes but they are VERY small (0 to 100 records) while the main table contain 600-800K records. the explain delete p; doesn't give any clue

Re: [GENERAL] plpgsql - sorting result set

On Aug 21, 2008, at 6:21 AM, Merlin Moncure wrote: You can always pass the order by clause (or hardcode it) into the execute statement. Also if you are using 8.3 you may want to check out to the new improvements to 'execute'...using. Hi Merlin, I can't use an order by on the execute, because

[GENERAL] psql \df+ [pattern] with overloaded functions

Using psql, how can I use specify a pattern for \df+ to only show a single overloaded function. The following is a list of my overloaded functions, and following this is an example what what happens when I try to limit the list by including the function signature. instrumentation=> \df update_his

Re: [GENERAL] What's size of your PostgreSQL Database?

Another question, how many people are there maintaining this huge database. We have about 2T of compressed SAS datasets, and now considering load them into a RDBMS database, according to your experience, it seems a single PostgreSQL instance can't manage such size databases well, it that right?

Re: [GENERAL] What's size of your PostgreSQL Database?

> On Sat, 2008-08-16 at 11:42 +0800, Amber wrote: >> Dear all: >> We are currently considering using PostgreSQL to host a read only >> warehouse, we would like to get some experiences, best practices and >> performance metrics from the user community, following is the question list: >> 1.

Re: [GENERAL] Single character bitfields

In response to "Andrew Maclean" <[EMAIL PROTECTED]>: > I have a large database and I want to have several fields (among many) > that are single character fields (in fact they are bitfields). > > 1) Is char(1) the most efficient way to store these fields? If not > what is better? > 2) I need to te

Re: [GENERAL] function SETOF return type with variable columns?

On Wed, Aug 20, 2008 at 12:59 PM, James Neff <[EMAIL PROTECTED]> wrote: > Greetings, > > Is it possible to have a function with a return type of SETOF that has > variable number of return columns? On Wed, Aug 20, 2008 at 10:08 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Merlin Moncure" <[EMAIL PRO

Re: [GENERAL] plpgsql - sorting result set

On Thu, Aug 21, 2008 at 1:03 AM, Bob Gobeille <[EMAIL PROTECTED]> wrote: > On Aug 20, 2008, at 10:11 PM, Gobeille, Robert wrote: >> On Aug 20, 2008, at 7:37 PM, Merlin Moncure wrote: >>> On Wed, Aug 20, 2008 at 7:20 PM, Robert Gobeille Is it possible to sort a result set in plpgsql?

Re: [GENERAL] [ADMIN] Regarding access to a user

Shashwat_Nigam wrote: > Thanks for the help. But still the user is able to see all > the databases. I defined something like this > > # IPv4 local connections: > hostall all 127.0.0.1/32 md5 > hostHMRI_database hmri127.0.0.1/32 md5 > > > in the ab

Re: [GENERAL] Interval Formatting -> Convert to timestamp

On Thu, 2008-08-21 at 11:53 +0200, Tomasz Ostrowski wrote: > On 2008-08-21 11:09, Ow Mun Heng wrote: > > > I want to find out if there's a method to change this > > select to_char('1 day 09:18:42.37996'::interval,'HH24:MI:SS') > > to something like > > 24+9(hours) = 33:18:42 instead of returning I

Re: [GENERAL] Interval Formatting -> Convert to timestamp

On 2008-08-21 11:09, Ow Mun Heng wrote: > I want to find out if there's a method to change this > select to_char('1 day 09:18:42.37996'::interval,'HH24:MI:SS') > to something like > 24+9(hours) = 33:18:42 instead of returning It as 09:19:42 That's because 1 day doesn't always have 24 hours, becau

Re: [GENERAL] Interval Formatting -> Convert to timestamp

On 21/08/2008 10:09, Ow Mun Heng wrote: > I want to find out if there's a method to change this > > select to_char('1 day 09:18:42.37996'::interval,'HH24:MI:SS') > > to something like > > 24+9(hours) = 33:18:42 instead of returning It as 09:19:42 I had to do something like this recently when addi

Re: [GENERAL] [ADMIN] Regarding access to a user

Hi Vishal Ya you are right and the order is same and the command is like that only. I am opening it through pgAdmin only.. Thanks & Regards, Shashwat Nigam | Software Engineer | Geographical Information System Satyam Computer Services Limited, Hyd

Re: [GENERAL] [ADMIN] Regarding access to a user

On 2008-08-21 11:12, Shashwat_Nigam wrote: > I had done the same but still the problem is like that the user is > able to access all the databases in the current server. Even the > order is like same and if I comment the second line(all access) then > the error is there. You did issue "pg_ctl rel

Re: [GENERAL] [ADMIN] Regarding access to a user

Dear Tomasz I had done the same but still the problem is like that the user is able to access all the databases in the current server. Even the order is like same and if I comment the second line(all access) then the error is there. Please provide solution for the same. Thanks & Regards, Shashwa

[GENERAL] Interval Formatting -> Convert to timestamp

Hi, I want to find out if there's a method to change this select to_char('1 day 09:18:42.37996'::interval,'HH24:MI:SS') to something like 24+9(hours) = 33:18:42 instead of returning It as 09:19:42 I've not found a way to do this (yet) -- Sent via pgsql-general mailing list (pgsql-general@po

Re: [GENERAL] [ADMIN] Regarding access to a user

On 2008-08-21 10:53, Shashwat_Nigam wrote: > hostall all 127.0.0.1/32 md5 > hostHMRI_database hmri127.0.0.1/32 md5 > in the above case hmri is user and HMRI_database. But still when user > is log in as 'hmri' it is able to access all the databases.

Re: [GENERAL] [ADMIN] Regarding access to a user

Dear Vishal Thanks for the help but by doing this an error is generated at the time when the user is trying to log in with the following message: " Access to database denied The server doesn't grant access to the database: the server reports FATAL: no pg_hba.conf entry for host "127.0.0.1", user

Re: [GENERAL] [ADMIN] Regarding access to a user

Dear Kevin Thanks for the help. But still the user is able to see all the databases. I defined something like this # IPv4 local connections: hostall all 127.0.0.1/32 md5 hostHMRI_database hmri127.0.0.1/32 md5 in the above case hmri is user and HMR

Re: [GENERAL] Single character bitfields

On 2008-08-21 05:29, Andrew Maclean wrote: > Is char(1) one byte in size? No. It will also depend on database encoding, etc. I think you should go with smallint, which is exactly 2 bytes. You'll have 15 bits of storage (16 if you'd want to implement the special case of minus sign). IMHO the onl

[GENERAL] Re: pg_restore fails on Windows

Magnus Hagander wrote: > >>> 2.Our production PG version is 8.1.3. For some reasons it is not possible > to > >> upgrade to the LATEST; > >>> I tested the libpq also on this version and it worked. Is it OK? I mean, > did > >> it worked by chance or the library > >>> API & contracts didn't change b

Re: [GENERAL] Sequences using libpqxx

On 2008-08-20 12:13, Roshni Mani wrote: > Does the sequence commands like nextval().setval(),curval() exist > using libpqxx? You just run an ordinary query: select nextval('sequence_name') Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just